发现如果人品不好做试验都是问题很多,晚上又把fstab给写错了,导致系统不能起来,因为当时处理该故障未截图,后续在网上找了几张图片,大体说明处理思路
系统启动报 filesystems 失败,输入root密码进入repair filesystem模式
尝试修改 /etc/fstab 发现系统是read-only模式
重新mount -n -o remount,rw /重新mount文件系统
重新修改/etc/fstab,除掉错误记录,然后使用init 6/reboot命令重启系统
Monthly Archives: 九月 2012
安装ORACLE db /tmp空间不足解决办法
因测试需要装一个ORACLE 11G,在安装检测阶段报下图错误
详细信息
Free Space: xifenfei:/tmp - This is a prerequisite condition to test whether sufficient free space is available in the file system. Error: - PRVF-7501 : Sufficient space is not available at location "/tmp" on node "xifenfei" [Required space = 1GB ] - Cause: Not enough free space at location specified. - Action: Free up additional space or select another location. Expected Value : 1GB Actual Value : 238MB
错误提示很明显ORACLE安装过程需要1G的临时空间，但是现在/tmp只有238M,空间明显不足,是的oracle检测失败,为了安装过程不出意外,决定分析并解决该问题
磁盘空间使用情况
[ora11g@xifenfei ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
3.9G 3.3G 238M 93% /
/dev/sda1 99M 24M 71M 25% /boot
tmpfs 1002M 0 1002M 0% /dev/shm
/dev/sdb1 20G 7.8G 11G 42% /u01
这里可以看出来/tmp没有另外的分配分区,而是挂载在/下面,也就是说,/tmp最多使用的空间就是/dev/mapper/VolGroup00-LogVol00分区能够使用的最大空间,也就是238M,证明ORACLE的检查程序说的是事实。
解决该问题
1.建立新tmp目录
[root@xifenfei ora11g]# mkdir /u01/tmp [root@xifenfei ora11g]# chown root:root /u01/tmp [root@xifenfei ora11g]# chmod 1777 /u01/tmp
2.设置数据库用户变量
vi db_home/.bash_profile export TEMP=/u01/tmp export TMPDIR=/u01/tmp [ora11g@xifenfei ~]$ env|grep TMP TMPDIR=/u01/tmp [ora11g@xifenfei ~]$ env|grep TEMP TEMP=/u01/tmp
3.重新运行runInstaller
4.安装完成清理相关/u01/tmp 和相关环境变了,让数据库使用系统默认(根据实际情况处理)
ORA-600和ORA-7445错误文章汇总
ORA-600和ORA-7445错误都是很多dba忌讳的错误,这里对本blog截止于:2012年09月15日的相关类此错误进行一个简单汇总,方便大家查阅.同时感谢“广州-紫恒”朋友在百忙中帮我整理出来.
ORA-600错误集合
ORA-600 [12235]
ORA-00600 [2662]
ORA-00600[4454]
ORA-00600[KSSADP1]
重现ORA-600[4000]异常
ORA-00600[4194]故障解决
ORA-00600[ktspNextL1:4]
ORA-600[4194]/[4193]解决
TOAD导致ORA-00600[17281]
ORA-00600[729]分析和处理方法
使用bbed解决ORA-00600[2662]
记录一次ORA-600[13013]处理过程
因为高版本引起ORA-00600[17059]
记录另一起ORA-00600[13013]处理
异常断电导致current redo损坏处理
记录一次ORA-00600[kdsgrp1]分析
异常断电导致current redo损坏处理
记录一次ORA-00600[2252]故障解决
因使用OEM引起ORA-00600[12761]
10.2.0.5出现ORA-00600[kcblasm_1]
ORA-00600[kgscLogOff-notempty]
通过bbed解决ORA-00600[4000]案例
ORA-00600 [ktbdchk1: bad dscn] 解决
11G RAC库 ORA-00600[ktubko_1]错误
收集统计信息出现ORA-00600[ksxprqfre3]
ORA-607/ORA-600[4194]不一定是重大灾难
创建控制文件遭遇ORA-00600[3753]故障解决
控制文件异常导致ORA-00600[kccsbck_first]
数据库报ORA-00607/ORA-00600[4194]错误
ORA-00600[kcratr_nab_less_than_odr]故障解决
PL/SQL Developer编译过程引起ora-600[15419]
ORA-07445[kslgetl()+120]/ORA-00108错误解决
双机mount数据库出现ORA-00600[kccsbck_first]
使用bbed解决ORA-00607/ORA-00600[4194]故障
通过bbed模拟ORA-00607/ORA-00600[4194]故障
truncate table强制终止导致ORA-00600[ktspfundo-2]
客户端版本导致ORA-00600[kssadd_stage: null parent]
动态修改PGA_AGGREGATE_TARGET 导致ORA-600[723]
ORA-00600[kcratr1_lostwrt]/ORA-00600[3020]错误恢复
ORA-600 [LibraryCacheNotEmptyOnClose] on shutdown
ORA-600[6749] 发生在 SYSMAN.MGMT_METRICS_RAW表
老版本PL/SQL Developer操作数据库导致ORA-00600[17113]
ASMM表空间强制终止DML操作导致ORA-600 [ktspfupdst-1]
表空间online出现ORA-00600[kcbz_check_objd_typ]处理过程
spfile被覆盖导致ORA-600[kmgs_parameter_update_timeout_1]
ORA-7445错误集合
ORA-7445[__milli_memcpy]分析
8i升级到9i出现ORA-07445[pevm_MOVC_i()+18]
ORA-07445[kslgetl()+120]/ORA-00108错误解决
遭遇ORA-07445[kkdliac()+346]使用odu抢救数据
ORA-07445[dbgrmqmqpk_query_pick_key()+0f88]
ORA-07445 [ACCESS_VIOLATION] [UNABLE_TO_READ] []
ORA-30013导致RAC 节点down掉
今天一朋友让我帮忙分析他们的9.2.0.2 rac 节点2异常down掉原因,相关信息如下:
前提信息
OS:HP-UX B.11.31 DB:9.2.0.2.0 RAC
节点2alert日志信息
Fri Sep 7 13:13:49 2012 ARC0: Completed archiving log 11 thread 2 sequence 11651 Fri Sep 7 13:31:56 2012 Errors in file /oracle/admin/agent/udump/agent2_ora_797.trc: ORA-00600: internal error code, arguments: [kgavsd_3], [0], [], [], [], [], [], [] ORA-00028: your session has been killed Fri Sep 7 13:31:58 2012 Errors in file /oracle/admin/agent/bdump/agent2_pmon_5938.trc: ORA-30013: undo tablespace 'UNDOTBS1' is currently in use Fri Sep 7 13:31:58 2012 PMON: terminating instance due to error 30013 Fri Sep 7 13:31:58 2012 Errors in file /oracle/admin/agent/bdump/agent2_lms7_6033.trc: ORA-30013: undo tablespace '' is currently in use Fri Sep 7 13:31:58 2012 ………… Errors in file /oracle/admin/agent/bdump/agent2_lms0_6027.trc: ORA-30013: undo tablespace '' is currently in use Fri Sep 7 13:31:58 2012 System state dump is made for local instance Fri Sep 7 13:32:03 2012 Instance terminated by PMON, pid = 5938 Fri Sep 7 14:34:35 2012
这里可以看到因为ORA-30013的错误使得pmon进程异常,从而使得该rac的节点2 down掉.同时这里还发现了ORA-00600[kgavsd_3]错误,是否是因为该ORA-600导致了数据库异常down还是一个偶然机会,我们继续分析
查看ORA-600[kgavsd_3]相关trace文件
*********START PLSQL RUNTIME DUMP************
***Got ORA-28 while running PLSQL***
***********END PLSQL RUNTIME DUMP************
*** 2012-09-07 13:31:56.740
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kgavsd_3], [0], [], [], [], [], [], []
ORA-00028: your session has been killed
Current SQL statement for this session:
--用户补档
DECLARE
OUT_ERR_CODE NUMBER;
OUT_ERR_MSG VARCHAR2(1000);
V_COUNT NUMBER;
BEGIN
WHILE TRUE LOOP
SELECT COUNT(*) INTO V_COUNT FROM amc_stat_log where proc_name in('pRunOdsChannelWareData')
and run_param=201208 AND STATE='A';
IF V_COUNT>0 THEN
dbms_output.put_line('exit loop '|| sysdate);
EXIT;
END IF;
sys.Dbms_Lock.sleep(600);
dbms_output.put_line('wake up '|| sysdate);
END LOOP;
PKG_AME_ODS_DATA.P_Add_TO_AgentServ(201208,OUT_ERR_CODE,OUT_ERR_MSG);
PKG_AME_ODS_DATA.P_Update_Serv_Ware_ID(201208, OUT_ERR_CODE, OUT_ERR_MSG);
PKG_AMS_SETTLE.P_COMMISION_51PRE_FLAG(201208,OUT_ERR_CODE, OUT_ERR_MSG);
END;
----- PL/SQL Call Stack -----
object line object
handle number name
c000000e84ec59d0 14 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
Cannot find symbol in .
Cannot find symbol in .
Cannot find symbol in .
ksedmp()+512 call 9fffffffffff3940 000000000 ?
C000000000000A17 ?
40000000025B6540 ?
ksfdmp()+64 call 9fffffffffff3940 000000003 ?
kgerinv()+352 call 9fffffffffff3940 60000000000466B0 ?
000000003 ?
C000000000000714 ?
4000000004EBA6A0 ?
00001821B ?
6000000000468EA8 ?
kgesinv()+48 call 9fffffffffff3940 60000000000466B0 ?
600000000059BD98 ?
600000000046B070 ?
60000000000179C0 ?
6000000000017950 ?
kgesin()+112 call 9fffffffffff3940 60000000000466B0 ?
600000000059BD98 ?
4000000000B44C10 ?
000000001 ?
9FFFFFFFFFFF4310 ?
$cold_kgavsd_stackl call 9fffffffffff3940 60000000000466B0 ?
et_done()+1184 600000000059BD98 ?
4000000000B44C10 ?
000000001 ? 000000000 ?
000000000 ? 000000000 ?
00001FE19 ?
pbesd_stacklet_done call 9fffffffffff3940 60000000000466B0 ?
()+96 000000002 ? 000000000 ?
9FFFFFFFBEC6AE68 ?
pfrrun()+3328 call 9fffffffffff3940 9FFFFFFFBEC6AE68 ?
C000000000001D42 ?
4000000001ABBDA0 ?
9FFFFFFFBEC6B06C ?
9FFFFFFFFFFF64F0 ?
6000000000468EA8 ?
000000000 ? 000000000 ?
peicnt()+480 call 0000000000000000 9FFFFFFFBEC6AEE8 ?
C000000000000996 ?
40000000019F3680 ?
kkxexe()+816 call 0000000000000000 9FFFFFFFFFFF64F0 ?
9FFFFFFFBEC6AE68 ?
C00000000000099B ?
4000000001DD90F0 ?
00000FE4F ?
9FFFFFFFFFFF5F00 ?
60000000000467F0 ?
4000000000B603F0 ?
opiexe()+11168 call 0000000000000000 000000000 ?
C000000000002C60 ?
4000000001BEF980 ?
00000800F ?
9FFFFFFFFFFF6470 ?
9FFFFFFFBEC6AEB2 ?
6000000000040900 ?
9FFFFFFFBEC6B534 ?
opiall0()+3184 call _etext_f()+23058430 000000004 ? 0000000C4 ?
09110686928 9FFFFFFFFFFF7B40 ?
C000000000002BDF ?
4000000001B26CD0 ?
000000000 ? 00000C893 ?
9FFFFFFFFFFF6690 ?
Cannot find symbol in .
kpoal8()+2064 call 9fffffffffff7ad0 000000001 ?
9FFFFFFFFFFF8304 ?
FFFFFFFFBFFFFFFF ?
9FFFFFFFFFFF83E4 ?
FFFFFFFFFFE7FBDF ?
9FFFFFFFFFFF7B88 ?
000000000 ?
6000000000474528 ?
opiodr()+3584 call 9fffffffffff81fc 6000000000040950 ?
000000000 ? 000000000 ?
C000000000002C60 ?
4000000001C09FE0 ?
00000C50B ?
9FFFFFFFFFFF81F0 ?
9FFFFFFFFFFF81D8 ?
ttcpip()+3776 call _etext_f()+23058430 00000005E ? 000000014 ?
09114957288 9FFFFFFFFFFFA5F0 ?
6000000000040918 ?
C000000000001ABD ?
4000000001AB3BA0 ?
000000000 ? 00000C59B ?
opitsk()+1872 call 9fffffffffffa200 6000000000049FC0 ?
000000001 ?
9FFFFFFFFFFFA5F0 ?
000000001 ?
9FFFFFFFFFFFA740 ?
9FFFFFFFFFFFA564 ?
9FFFFFFFBF780058 ?
000000000 ?
opiino()+3184 call 000000000000057b 000000000 ? 000000000 ?
C00000000000132B ?
4000000001F78730 ?
000008001 ?
opiodr()+3584 call 0000000000000000 6000000000548A38 ?
4000000000B606F0 ?
6000000000548A38 ?
C000000000002C60 ?
4000000001C09FE0 ?
00000A201 ?
9FFFFFFFFFFFBC90 ?
4000000000B606F0 ?
opidrv()+976 call _etext_f()+23058430 00000003C ? 000000004 ?
09114957288 9FFFFFFFFFFFEFB0 ?
6000000000040918 ?
sou2o()+80 call _etext_f()+23058430 000000004 ? 000000004 ?
09114957288 9FFFFFFFFFFFEFB0 ?
main()+352 call _etext_f()+23058430 9FFFFFFFFFFFEFD0 ?
09114957288 9FFFFFFFFFFFEFD4 ?
60000000004744F0 ?
9FFFFFFFFFFFEFB0 ?
main_opd_entry()+80 call _etext_f()+23058430 000000000 ?
09114957288 9FFFFFFFFFFFF498 ?
C000000000000004 ?
C00000000002BE30 ?
--------------------- Binary Stack Dump ---------------------
Process global information:
process: c000000d6428c0c0, call: c000000e46e772a8, xact: 0000000000000000,
curses: c000000d6437d020, usrses: c000000d6437d020
----------------------------------------
SO: c000000d6428c0c0, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=282, calls cur/top: c000000e46e772a8/c000000e46e772a8, flag: (0) -
int error: 28, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 0
last post received-location: No post
last process to post me: c000000d64234f18 1 6
last post sent: 0 0 104
last post sent-location: kglpsl: in loop
last process posted by me: c000000d6428e900 23 0
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: c000000d62234ee0
O/S info: user: oracle, term: UNKNOWN, ospid: 797
OSD pid info: Unix process pid: 797, image: oracle@gzagent2 (TNS V1-V3)
----------------------------------------
SO: c000000d6437d020, type: 4, owner: c000000d6428c0c0, flag: INIT/-/-/0x00
(session) trans: 0000000000000000, creator: c000000d6428c0c0, flag: (41) USR/- BSY/-/-/-/KIL/-
DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
txn branch: 0000000000000000
oct: 0, prv: 0, sql: c000000e76b03f10, psql: 0000000000000000, user: 31/CUSTOM
O/S info: user: huangqianhai_lc, term: SVCTAG-D1MLV2X, ospid: 11124:11796, machine: WORKGROUP\SVCTAG-D1MLV2X
program: plsqldev.exe
application name: PL/SQL Developer, hash value=1190136663
action name: 测试窗口 - 新建, hash value=3604520210
last wait for 'null event' blocking sess=0x0 seq=142 wait_time=567341620
=ea60, =0, =0
temporary object counter: 0
----------------------------------------
通过这里可以看出来是因为pl/sql dev进行一个plsql的操作导致该错误发生,查询MOS[ID 403575.1]发现
Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.7 and later [Release: 9.2 and later ] Information in this document applies to any platform. ***Checked for relevance on 27-Oct-2010*** Symptoms The following errors appears in the alert log file : Probe:read_pipe: receive failed, status 3 Probe:S:debug_loop: timeout. Action 1 *********START PLSQL RUNTIME DUMP************ ***Got ORA-604 while running PLSQL*** ***********END PLSQL RUNTIME DUMP************ ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [kgavsd_3], [0], [], [], [], [], [], [] ORA-00604: error occurred at recursive SQL level 2 Current SQL statement for this session: begin :id := sys.dbms_transaction.local_transaction_id; end; . Cause The ora-600 kgavsd_3 appears when calling kgavsd_stacklet_done function which is related to PLSQL DEBUG. From traces, dbms_debug package is being used during trace generation. The return code of 3 further indicates that the dbms_pipe message was interrupted. Probably user cancelled a plsql program, so the appeared while trying to dump the stack Solution There is no data corruption over here. The error appears to be due to abnormal termination of aPL/SQL Developer application while executing a PL/SQL block. Changing the PL/SQL and/or the procedure code could help in avoiding this error message. Hence, this error can be safely ignored.
查找trace文件确实发现有name=SYS.DBMS_DEBUG,进一步表明该错误是由于plsql dev工具使用debug模式运行上面的plsql而引起该错误的发生,但是因为mos中记录和错误不是完全的一致,所以不能十分确定是该错误导致数据库down掉
继续分析ORA-30013
Error: ORA-30013 (ORA-30013) Text: undo tablespace '%s' is currently in use --------------------------------------------------------------------------- Cause: the specified undo tablespace is currently used by another instance. Action: Wait for the undo tablespace to become available or change to another name and reissue the statement.
这个说明是没有疑问的:因为2节点配置的当前undo是UNDOTBS2,而UNDOTBS1是1节点使用的,证明这里的undo确实发生了错误,继续查询mos发现Bug 3368552
Hdr: 3368552 9.2.0.3 RDBMS 9.2.0.3 RAC PRODID-5 PORTID-23 Abstract: RAC: ORA-30013 WHEN INSTANCE 2 ATTEMPTS TO ACCESS UNDO TABLESPACE OF INSTANCE 1 *** 01/12/04 06:21 am *** TAR: ---- 3554549.995 PROBLEM: -------- The RAC database has been stable, but experienced an instance termination due to ORA-30031 error in the alert log (instance 2): ... Tue Dec 23 03:01:46 2003 ARC1: Evaluating archive log 4 thread 2 sequence 1116 ARC1: Beginning to archive log 4 thread 2 sequence 1116 Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/oradata/VLDB/logs/archives/VLDBN2/VLDB_0000001116_0002.arc' ARC1: Completed archiving log 4 thread 2 sequence 1116 Tue Dec 23 08:14:09 2003 Errors in file /oracle/admin/VLDB/bdump/vldbn2_pmon_22860.trc: ORA-30013: undo tablespace 'UNDOTBS1' is currently in use Tue Dec 23 08:14:09 2003 PMON: terminating instance due to error 30013 Tue Dec 23 08:14:10 2003 System state dump is made for local instance Tue Dec 23 08:14:12 2003 Trace dumping is performing id=[cdmp_20031223081410] Tue Dec 23 08:14:14 2003 Instance terminated by PMON, pid = 22860 <eof> Instance 1 alert log shows only the reconfiguration and the cdump info: .. Tue Dec 23 03:54:13 2003 Errors in file /oracle/admin/VLDB/udump/vldbn1_ora_13564.trc: ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 4 Tue Dec 23 08:14:10 2003 Trace dumping is performing id=[cdmp_20031223081410] Tue Dec 23 08:14:12 2003 Reconfiguration started List of nodes: 0, Global Resource Directory frozen one node partition Communication channels reestablished ...
因为在9.2.0.3的RAC中有着该bug,那么我们可以大胆猜测在9.2.0.2中应该存在该bug,那么结合上面的ORA-00600[kgavsd_3]错误,我们大概还原该事故的全部:
1.节点1 dml操作了程序中报错的plsql中要范围的部分表对象,但是未提交(或者正在执行)
2.节点2 有用户使用pl/sql dev去执行程序中的plsql,因为是debug模式执行,需要UNDOTBS1的块来构建cr,从而使得节点2去访问UNDOTBS1,引发了Bug 3368552 从而使得数据库直接kill掉该plsql dev会话,进而出现ORA-00600[kgavsd_3]错误和pmon进程异常使得节点2 down掉
TXChecker初试
当我们对数据库进行异常恢复,很多时候要选择屏蔽回滚段,但是我们有没有办法来评估屏蔽回滚段到底会对我们的数据库的数据产生多大影响呢？其实我们可以通过TXChecker工具来评估数据库undo异常时候受到影响的数据对象有哪些,从而进一步确定是否真的需要对其undo下手处理.
模拟数据异常事务为提交情况
[oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 20:32:27 2012 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 318767104 bytes Fixed Size 1267236 bytes Variable Size 100665820 bytes Database Buffers 209715200 bytes Redo Buffers 7118848 bytes Database mounted. Database opened. SQL> create table t_xifenfei tablespace system 2 as 3 select * from dba_objects; Table created. SQL> create table chf.t_xifenfei tablespace users 2 as 3 select * from dba_objects; Table created. SQL> delete from t_xifenfei where rownum<10; 9 rows deleted. SQL> delete from chf.t_xifenfei where rownum<100; 99 rows deleted. SQL> alter system checkpoint; System altered. SQL> shutdow abort; ORACLE instance shut down.
在system和users表空间分别模拟了sys和chf的t_xifenfei表含事务未提交情况
TXChecker用法说明
[oracle@xifenfei txchecker]$ ./TXChecker
TXChecker - v1.4 by Center Of Expertise (COE), Oracle Corporation (build 10/16/07)
Usage is: TXChecker [options]
Options:
-a When scanning datafiles (with -d/-f/-l/-t options) report objects using any of the undo
segments (not just those with errors) (OPTIONAL)
-b For objects found, print the datablock addresses. See readme for further details (OPTIONAL)
-c<controlfile_name> Fully qualified controlfile name to read (MANDATORY)
-d Scan database for active TXs (use when undo not available) (OPTIONAL)
-f<filename> Scan the named datafile for active TXs (OPTIONAL)
-g Indicates you want to find all blocks taking part in transactions with
a USN > than the USN supplied in -x<XID> parameter (same constraints as -w) (OPTIONAL)
-l<listfile> Scan all the datafiles listed in the listfile for active TXs (OPTIONAL)
-m<minutes> Number of minutes used to consider a TX as active (1-120) (DEFAULTS TO 60 MINUTES)
-p Show the names and last known status of the UNDO segments (OPTIONAL)
-s Skip read-only or offline normal datafiles (OPTIONAL)
-t<tablespace> Scan all the datafiles for this tablespace (OPTIONAL)
-u Report ITL entries active if marked with an upper bound ('U' flag) fast commit SCN
instead if active transactions (OPTIONAL)
-w<wrap#> Wrap# for XID in ITL entry to report blocks where wrap# > this one (OPTIONAL)
Must use -x with this option. See the readme for details
-x<XID> XID for transaction wanting to search for (OPTIONAL)
Use format rrrr.ssss.wwwwwwww using Hexadecimal numbers
See the readme for full instructions on using -x, -w and -g options
NOTE: Options -d/-f/-l/-t are exclusive, and only one should be specified.
[/sql]
<strong>TXChecker初始</strong>
1
[oracle@xifenfei txchecker]$ ./TXChecker -c/u01/oracle/oradata/XFF/control01.ctl -d -a
TXChecker - v1.4 by Center Of Expertise (COE), Oracle Corporation (build 10/16/07)
Database Name: XFF Version: 10.2.0
*** Database last checkpointed at 08/30/2012 20:34:43 (SCN: 0xa.0x1e142)
*** Using 60 minutes to find most active transactions (-m60)
*** Undo Segments:
USN: 0 Name: SYSTEM TBS#: 0 File: 1 Block: 9 Instance: 0 SMU: N Status: 3 - Online
SCN: 0000.00000000 XactSQN: 0x00000000 UndoSQN: 0x00000000
USN: 1 Name: _SYSSMU1$ TBS#: 1 File: 2 Block: 9 Instance: 0 SMU: Y Status: 3 - Online
SCN: 000a.000191c3 XactSQN: 0x000000d2 UndoSQN: 0x0000013c
…………省略
USN: 9 Name: _SYSSMU9$ TBS#: 1 File: 2 Block: 137 Instance: 0 SMU: Y Status: 3 - Online
SCN: 000a.000191d8 XactSQN: 0x0000011a UndoSQN: 0x000000dc
USN: 10 Name: _SYSSMU10$ TBS#: 1 File: 2 Block: 153 Instance: 0 SMU: Y Status: 3 - Online
SCN: 000a.000191d7 XactSQN: 0x000000c1 UndoSQN: 0x000000d9
…………省略
USN: 20 Name: _SYSSMU20$ TBS#: 5 File: 5 Block: 153 Instance: 0 SMU: Y Status: 1 - Invalid / Dropped
SCN: 0000.00070ec6 XactSQN: 0x00000002 UndoSQN: 0x00000001
*** Active Transactions:
USN: 6 Name: _SYSSMU6$ File: 2 Block: 89 Instance: 0 Status: 3 - Online
* Active TX at slot 6 #undo blocks: 3 Last bk: 2.90
Obj#: 51938 Name: CHF.T_XIFENFEI Type: TABLE Undo recs: 99
Used undo segment IDs: 6
Obj#: 51937 Name: SYS.T_XIFENFEI Type: TABLE Undo recs: 9
Used undo segment IDs: 6
File (validate_objects.sql) being created for object validattion already exists
and will be overwritten!!!
Do you want to continue overwriting [Y/N]?y
*** Undo segments (headers) that encountered errors preventing Active TX scan:
USN: 11 Name: _SYSSMU11$ File: 5 Block: 9 Instance: 0 Error: 27 - Undo segment was dropped
…………省略
USN: 20 Name: _SYSSMU20$ File: 5 Block: 153 Instance: 0 Error: 27 - Undo segment was dropped
WARNING: Analyzing the full database for active transactions will take some time!
Are you sure you want to analyze the full database [Y/N]?
Are you sure you want to analyze the full database [Y/N]?y
*** Scanning database for datablocks that may require undo (PLEASE WAIT...):
*** Asterisk ('*') denotes blocks being updated since 08/08/2012 03:30:32 (SCN: 0x0.0x79607)
Scanning datafile: 3 - /u01/oracle/oradata/XFF/sysaux01.dbf (SYSAUX) - Active TX blocks: 147 *
Scanning datafile: 1 - /u01/oracle/oradata/XFF/system01.dbf (SYSTEM) - Active TX blocks: 11597 *
--undo表空间跳过
Undo datafile (/u01/oracle/oradata/XFF/undotbs01.dbf) - SKIPPING
Scanning datafile: 4 - /u01/oracle/oradata/XFF/users01.dbf (USERS) - Active TX blocks: 2 *
--因为数据文件丢失控制文件中offline的跳过(其实只要数据文件丢失就会跳过)
Cannot access datafile (/u01/oracle/oradata/XFF/xifenfei01.dbf) (error 2 - No such file or directory) - SKIPPING
Cannot access datafile (/u01/oracle/oradata/XFF/xifenfei02.dbf) (error 2 - No such file or directory) - SKIPPING
Scanning datafile: 7 - /u01/oracle/oradata/XFF/xifenfei03.dbf (XIFENFEI2) - Active TX blocks: 0
*** Objects that may require undo data:
*** Asterisk ('*') denotes blocks being updated since 08/08/2012 03:30:32 (SCN: 0x0.0x79607)
DataObj#: 51938 Name: CHF.T_XIFENFEI Type: TABLE Datablocks: 2 *
Used undo segment IDs: 6
DataObj#: 46434 Name: MDSYS.SYS_IL0000046432C00006$$ Type: INDEX Datablocks: 4
Used undo segment IDs: 2
DataObj#: 124 Name: SYS.I_ACCESS1 Type: INDEX Datablocks: 27
Used undo segment IDs: 1 2 3 4 5 6 7 8 9 10
…………省略
DataObj#: 8824 Name: SYS.SYS_IL0000008822C00008$$ Type: INDEX Datablocks: 1 *
Used undo segment IDs: 4
DataObj#: 51937 Name: SYS.T_XIFENFEI Type: TABLE Datablocks: 1 *
Used undo segment IDs: 6
DataObj#: 51557 Name: SYS.UTL_RECOMP_COMPILED Type: TABLE Datablocks: 1
Used undo segment IDs: 8
…………省略
DataObj#: 42131 Name: XDB.XDB$ELEMENT_PROPNUMBER Type: INDEX Datablocks: 1
Used undo segment IDs: 2
*** Use validate_objects.sql script file to validate the structure of possibly corrupt objects
if the undo required is not available.
Undo Segment Usage Summary
**************************
*** Undo segments identified in use by active transaction datablocks AFTER 08/08/2012 03:30:32 (SCN: 0x0.0x79607):
USN: 1 Name: _SYSSMU1$
USN: 2 Name: _SYSSMU2$
USN: 3 Name: _SYSSMU3$
USN: 4 Name: _SYSSMU4$
USN: 5 Name: _SYSSMU5$
USN: 6 Name: _SYSSMU6$
USN: 9 Name: _SYSSMU9$
*** Undo segments identified in use by active transacation datablocks BEFORE 08/08/2012 03:30:32 (SCN: 0x0.0x79607):
USN: 1 Name: _SYSSMU1$
USN: 2 Name: _SYSSMU2$
USN: 3 Name: _SYSSMU3$
USN: 4 Name: _SYSSMU4$
USN: 5 Name: _SYSSMU5$
USN: 7 Name: _SYSSMU7$
USN: 8 Name: _SYSSMU8$
USN: 9 Name: _SYSSMU9$
USN: 10 Name: _SYSSMU10$
NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!
*************************************************************************************
Upload the logfile (TXChecker_083012_2042_XFF.log) to Oracle Support Services for analysis.
Do NOT attempt to force the database open until the logfile has been analyzed.
验证对象脚本
[oracle@xifenfei txchecker]$ more validate_objects.sql rem validate_objects.sql - checks strcuture of objects needing unavailable undo data rem Created by findtxns program, Oracle Corporation set echo on ANALYZE TABLE CHF.T_XIFENFEI VALIDATE STRUCTURE CASCADE; ANALYZE INDEX MDSYS.SYS_IL0000046432C00006$$ VALIDATE STRUCTURE; ANALYZE INDEX SYS.I_ACCESS1 VALIDATE STRUCTURE; …………省略 ANALYZE INDEX XDB.SYS_C003167 VALIDATE STRUCTURE; ANALYZE INDEX XDB.XDB$ELEMENT_PROPNAME VALIDATE STRUCTURE; ANALYZE INDEX XDB.XDB$ELEMENT_PROPNUMBER VALIDATE STRUCTURE;
