当我们对数据库进行异常恢复,很多时候要选择屏蔽回滚段,但是我们有没有办法来评估屏蔽回滚段到底会对我们的数据库的数据产生多大影响呢？其实我们可以通过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>
[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;
