Introduction TXChecker provides information useful for determining the nature and extent of corruption that may be introduced when forcing a database open with a combination of _CORRUPTED_ROLLBACK_SEGMENTS and _OFFLINE_ROLLBACK_SEGMENTS. When a database is opened with these parameters due to missing or corrupt undo datafiles, it is unknown how much data will be inaccessible (returning errors or hanging/spinning) or force-committed. Force-committing can cause logical data corruption and possible data dictionary corruption. This tool provides information necessary for the correction and repair of such corruption by identifying which objects will be effected. TXChecker MUST only be used under the advice of Oracle Support Services. Failure to do so and forcing the database open will render the database unsupported by Oracle. Back to Contents Overview In cases were a problem occurs with one or more undo datafiles preventing the database from opening, two hidden and unsupported init.ora parameters _OFFLINE_ROLLBACK_SEGMENTS and/or _CORRUPTED_ROLLBACK_SEGMENTS may be used by Oracle Support to aid in opening the database. These two parameters change the way Oracle handles active transactions when the undo required to roll them back is not available. They should only be used under the advice of Oracle Support due to the increased possibilities of introducing logical data corruptions throughout the database. Without using TXChecker the only way to know which objects are affected is by forcing the database open and then manually dump the active transaction undo chains. This method is very time consuming and can prove futile if after the database is opened you find out important objects are no longer accessible. TXChecker will NOT help identify objects that may be negatively affected by using the _ALLOW_RESETLOGS_CORRUPTIONparameter. TXChecker will only identify objects that may require undo information to rollback active transactions or clean out dirty blocks. It will NOT identify objects that are out of sync with each other or the data dictionary. Having datafiles from different points in time will produce inaccurate results from TXChecker. TXChecker will show the object name, owner and type of all objects involved in active transactions, assuming the undo datafiles can be read successfully. If the active transaction chains cannot be read TXChecker can scan one or more datafiles to identify objects that may require undo data from missing or corrupted undo segments. TXChecker results is easier for Oracle Support to decide the best course of action before forcing the database open. If all the affected objects can be rebuilt from other sources and the data dictionary is not affected, the database can be opened without the need to rebuild the entire database using export/import. TXChecker operates in a read-only manner and does NOT write to any Oracle datafiles, control files or log files. TXChecker will not damage the database. Back to Contents Features Version 1.4 of TXChecker provides the following features: Show you which undo segments were online the last time the database was open. If the database was shutdown cleanly they should all be offline, except for the system undo segment. If the database crashed, this information can then be used to set the relevant rollback segment parameters should they be needed to force the database open. Show you which undo segments contained active transactions at time of a crash, and which undo segments will report problems when Oracle tries to access them. The undo chains for active transactions are also checked to identify the objects involved or what problems are encountered when traversing them. Obviosly if the undo datafile cannot be read the undo segments can not be accessed. Browse | Subscriptions | Authoring Wizard | Documents In Progress | Query By Attribute Knowledge -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 (OPTIONAL) -ccontrolfile_name Fully qualified controlfile name to read (MANDATORY) For each active transaction, show the object owner, object name, partition name if a partitioned object and object type for all objects involved in that transaction. A count of undo records found for each object is also shown. Scan a single datafile, a list of datafiles, a tablespace or all datafiles in the database for datablocks that may need undo data for clean-out purposes, or datablocks that are involved in active transactions. This is useful for determining what objects will need attention after the database is forced open. For all objects identified as needing undo data, a SQL script is created which contains the commands to carry out an 'ANALYZE ... VALIDATE STRUCTURE' to check for possible corruption or access problems. Identify which undo segments were used to update each object. When the datablocks are listed in the report, the transactions IDs are also displayed. Lists which undo segments were used before and after determined SCN values. This is useful to find out which undo segments would need to be named in either of the underscore parameters to force the database open to allow active transactions to be force committed or dirty datablocks to be cleaned out. Allows you to search the database for active transactions using a particular undo segment, a transaction ID or an undo segment and slot number, or a transaction using an undo segment where the wrap number is greater than a determined value. When identifying each affected datablock address, TXChecker will create a script which can be run to dump those datablocks for further analysis. Will scan controlfiles and datafiles located on RAW devices. Back to Contents Supported Platforms 64-bit & 32-bit Sparc Solaris 64-bit & 32-bit Linux 64-bit & 32-bit HP-UX 64-bit AIX 32-bit MS Windows TXChecker has had initial testing against versions 8.0 through to 10.2 but only on a limited basis so feedback is encouraged to let us know how well it worked, what problems were encountered or what enhancements you would like to have added. Back to Contents Download TXChecker Current TXChecker Version: 1.4 October, 2007 x86 Linux (v1.4) Click here to download the file Sparc Solaris (v1.4) Click here to download the file HP/UX - RISC (v1.4) Click here to download the file AIX (v1.4) Click here to download the file MS Windows (v1.4) Click here to download the file If a file download dialog box does not appear when clicking on the above link, you may need to clear your web browser's cache and/or restart your web browser. If you are still unable to download the file, you may request that we email you a copy: Stephan.Haisley@oracle.com Back to Contents Installing TXChecker Download TXChecker using one of the links above. Copy the tar file to the directory where TXChecker is to be installed and issue the following commands. Unix/Linux % tar xvfp TXChecker_version_platform.tar % cd TXChecker % chmod u+x TXChecker Windows 1. Extract the files from the zip file into a directory: C:\TXC> unzip TXChecker_verson_MSWindows.zip 2. Leave the Microsoft.VC80.CRT directory where it is to allow TXChecker to find the DLLs. This User Guide is also uncompressed with full instructions on how to run the tool. Back to Contents Running TXChecker To see the list of program arguments: % TXChecker Usage is: TXChecker [options] Options: -ccontrolfile_name Fully qualified controlfile name to read (MANDATORY) -d Scan database for active TXs (use when undo not available) (OPTIONAL) -ffilename 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 parameter (same constraints as -w) (OPTIONAL) -llistfile Scan all the datafiles listed in the listfile for active TXs (OPTIONAL) -mminutes Number of minutes used to consider a TX as active (1-120) (DEFAULTS TO 15 MINUTES) -p Show the names and last known status of the UNDO segments (OPTIONAL) -s Skip read-only or offline normal datafiles (OPTIONAL) -ttablespace 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) -wwrap# Wrap# for XID in ITL entry to report blocks where wrap# > this one (OPTIONAL). Must use -x with this option -xXID 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. NOTES: Options -x (search for a transaction ID) and -a (search for all active transactions) are exclusive and will not work if specified together. Options -w (search for transactions with a wrap number greater than one specified) and -g (search for transactions using an undo segment with a USN greater than the number specified) are exclusive and should not be used together. The dump_datablocks.sql script containing the commands to dump the affected datablocks will only be produced when using the -b option. TXChecker will always create a logfile in the current directory called TXChecker_date_time_DBName.log. This MUST be reviewed and uploaded to Oracle Support Services for analysis. TXChecker uses the control file to gather the locations and names of all tablespaces and datafiles belonging to the database. Using a controlfile from a different point in time to the rest of the database may result in errors identifying missing datafiles. If this happens, use the listfile (-l), tablespace (-t) or datafile (-d) options to scan selected datafiles. If there is adequate CPU and I/O bandwidth available TXChecker could be run in parallel to scan different tablespaces or datafiles concurrently, reducing the time it takes to scan larger databases. To do this, simply run TXChecker from multiple sessions at the same time. NOTE: TXChecker will always create a logfile in the current directory called TXChecker_date_time_DBName.log. This MUST be uploaded to Oracle Support Services for analysis and for advice on how best to continue. Also upload a conttol file and file headers dump (alter session set events 'immediate trace name controlf level 12' and alter session set events 'immediate trace name file_hdrs level 10'). Common Examples Here are some common examples of using TXChecker along with links to the output created by using the commands. 1. Check the undo segments for active transactions: % ./TXChecker -c/u02/oradata/S102/control02.ctl Example output: TXChecker_ex1.out 2. Scan the database for objects which may require undo from troubled undo segments, showing datablock addresses: % ./TXChecker -c/u02/oradata/S102/control02.ctl -d -b Example output: TXChecker_ex2.out 3. Find all objects in a list of datafiles which may require undo from any undo segments: % ./TXChecker -c/u03/oradata/S901/control02.ctl -lfiles.S901 -a Example output: TXChecker_ex3.out See the Appendix B for details of creating a list file containing the names of the datafiles to be scanned. 4. Scan a single datafile for objects which may require undo from troubled undo segment with a particular transaction ID: % ./TXChecker -f/u02/oradata/S101/users01.dbf -c/u01/oradata/S101/control01.ctl -x0001.0004.00000125 -b Example output: TXChecker_ex4.out 5. Scan a tablespace for with non-committed transactions using a USN greater than a known USN: % ./TXChecker -c/u02/oradata/S102/control02.ctl -tsystem -x0009.FFFF.FFFFFFFF -g Example output: TXChecker_ex5.out Back to Contents Warnings and Restrictions When TXChecker is scanning datafiles it is reading and checking every data block for active ITL (Interested Transaction List) entries that may require access to undo information. To scan large datafiles or a large database TXChecker may take some time due to the amount of reads being carried out, so consider using the listfile or tablespace option to scan the most important datafiles. TXChecker has NOT been coded for OCFS or ASM files yet. This may be introduced in a later release. TXChecker will currently not handle a database where there are more than 250 rows in the X$KCCFE table. This restriction has been removed in version 1.4. Details of a workaround can be found in Appendix C. Back to Contents Future Release Features Features that may be introduced in future releases of TXChecker include: a. Add support for ASM, OCFS. b. Create a script using the datablock addresses to be used to select data around affected datablocks. Create a similar script to try selecting the data in the affected datablocks. c. Add the ability to carry out more internal related undo problem diagnostics. d. Add parallelism feature to scan datafiles in parallel. Back to Contents Reporting Feedback If you encounter problems running TXChecker or would like to provide feedback or enhancement requests, please send emails to Stephan.Haisley@oracle.com. I have done my best to test TXChecker on many different machines and conditions but if it does core dump or report unexpected errors, please send me the logfile, stack trace (from the core) and if possible a binary copy of the controlfile and system datafile. If the system datafile is too big to upload to Oracle, copy the first 3000 blocks using dd: % dd if=system_datafile of=system_datafile_copy bs=blocksize count=3000 Back to Contents Appendix A: Output Example The following logfiles were produced from some of the above common examples. TXChecker_ex1.out TXChecker started at: Thu May 3 12:06:17 2007 TXChecker - v1.3 by Center Of Expertise (COE), Oracle Corporation (build 04/13/07) Program command: TXChecker -c/u02/oradata/S102/control02.ctl Database Name: S102 Version: 10.2.0 *** Database last checkpointed at 05/03/2007 12:05:42 (SCN: 0x0.0xc5f24c) *** 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: 0000.00c59c2a XactSQN: 0x00000208 UndoSQN: 0x0000020b USN: 2 Name: _SYSSMU2$ TBS#: 1 File: 2 Block: 25 Instance: 0 SMU: Y Status: 3 - Online SCN: 0000.00c59cc7 XactSQN: 0x000002a6 UndoSQN: 0x00000220 USN: 3 Name: _SYSSMU3$ TBS#: 1 File: 2 Block: 41 Instance: 0 SMU: Y Status: 3 - Online SCN: 0000.00c59c50 XactSQN: 0x0000029c UndoSQN: 0x00000277 ... USN: 10 Name: _SYSSMU10$ TBS#: 1 File: 2 Block: 153 Instance: 0 SMU: Y Status: 3 - Online SCN: 0000.00c59c1c XactSQN: 0x000001f7 UndoSQN: 0x00000263 USN: 11 Name: _SYSSMU11$ TBS#: 1 File: 2 Block: 241 Instance: 0 SMU: Y Status: 2 - Offline SCN: 0000.0006f10a XactSQN: 0x00000003 UndoSQN: 0x00000002 USN: 12 Name: _SYSSMU12$ TBS#: 1 File: 2 Block: 289 Instance: 0 SMU: Y Status: 2 - Offline SCN: 0000.0006f0f0 XactSQN: 0x00000003 UndoSQN: 0x00000002 *** Active Transactions: USN: 2 Name: _SYSSMU2$ File: 2 Block: 25 Instance: 0 Status: 3 - Online * Active TX at slot 44 #undo blocks: 2 Last bk: 2.3103 USN: 6 Name: _SYSSMU6$ File: 2 Block: 89 Instance: 0 Status: 3 - Online * Active TX at slot 24 #undo blocks: 7 Last bk: 2.1301 *** Objects that may require undo data: Obj#: 51465 Name: OE.ITEM_ORDER_IX Type: INDEX Undo recs: 15 Used undo segment IDs: 2, Obj#: 51466 Name: OE.ITEM_PRODUCT_IX Type: INDEX Undo recs: 15 Used undo segment IDs: 2, Obj#: 51402 Name: OE.INVENTORIES Type: TABLE Undo recs: 95 Used undo segment IDs: 6, Obj#: 51400 Name: OE.ORDERS Type: TABLE Undo recs: 2 Used undo segment IDs: 2, Obj#: 51396 Name: OE.ORDER_ITEMS Type: TABLE Undo recs: 15 Obj#: 51396 Name: OE.ORDER_ITEMS Type: TABLE Undo recs: 15 Used undo segment IDs: 2, Obj#: 51397 Name: OE.ORDER_ITEMS_PK Type: INDEX Undo recs: 15 Used undo segment IDs: 2, Obj#: 51398 Name: OE.ORDER_ITEMS_UK Type: INDEX Undo recs: 15 Used undo segment IDs: 2, Obj#: 51401 Name: OE.ORDER_PK Type: INDEX Undo recs: 2 Used undo segment IDs: 2, Obj#: 51468 Name: OE.ORD_CUSTOMER_IX Type: INDEX Undo recs: 2 Used undo segment IDs: 2, Obj#: 51469 Name: OE.ORD_ORDER_DATE_IX Type: INDEX Undo recs: 2 Used undo segment IDs: 2, Obj#: 51467 Name: OE.ORD_SALES_REP_IX Type: INDEX Undo recs: 2 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. NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE! ************************************************************************************* Upload the logfile (TXChecker_050307_1206_S102.log) to Oracle Support Services for analysis. Do NOT attempt to force the database open until the logfile has been analyzed. TXChecker_ex2.out TXChecker started at: Thu May 3 12:32:26 2007 TXChecker - v1.3 by Center Of Expertise (COE), Oracle Corporation (build 04/13/07) Program command: TXChecker -c/u02/oradata/S102/control02.ctl -d -b Database Name: S102 Version: 10.2.0 *** Database last checkpointed at 05/03/2007 12:05:42 (SCN: 0x0.0xc5f24c) *** 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: 0000.00c59c2a XactSQN: 0x00000208 UndoSQN: 0x0000020b USN: 2 Name: _SYSSMU2$ TBS#: 1 File: 2 Block: 25 Instance: 0 SMU: Y Status: 3 - Online SCN: 0000.00c59cc7 XactSQN: 0x000002a6 UndoSQN: 0x00000220 USN: 3 Name: _SYSSMU3$ TBS#: 1 File: 2 Block: 41 Instance: 0 SMU: Y Status: 3 - Online SCN: 0000.00c59c50 XactSQN: 0x0000029c UndoSQN: 0x00000277 ... USN: 10 Name: _SYSSMU10$ TBS#: 1 File: 2 Block: 153 Instance: 0 SMU: Y Status: 3 - Online SCN: 0000.00c59c1c XactSQN: 0x000001f7 UndoSQN: 0x00000263 USN: 11 Name: _SYSSMU11$ TBS#: 1 File: 2 Block: 241 Instance: 0 SMU: Y Status: 2 - Offline SCN: 0000.0006f10a XactSQN: 0x00000003 UndoSQN: 0x00000002 USN: 12 Name: _SYSSMU12$ TBS#: 1 File: 2 Block: 289 Instance: 0 SMU: Y Status: 2 - Offline SCN: 0000.0006f0f0 XactSQN: 0x00000003 UndoSQN: 0x00000002 *** Active Transactions: USN: 2 Name: _SYSSMU2$ File: 2 Block: 25 Instance: 0 Status: 3 - Online * Active TX at slot 44 #undo blocks: 2 Last bk: 2.3103 USN: 6 Name: _SYSSMU6$ File: 2 Block: 89 Instance: 0 Status: 3 - Online * Active TX at slot 24 #undo blocks: 7 Last bk: 2.1301 *** Objects that may require undo data: Obj#: 51465 Name: OE.ITEM_ORDER_IX Type: INDEX Undo recs: 15 Used undo segment IDs: 2, Obj#: 51466 Name: OE.ITEM_PRODUCT_IX Type: INDEX Undo recs: 15 Used undo segment IDs: 2, Obj#: 51402 Name: OE.INVENTORIES Type: TABLE Undo recs: 95 Used undo segment IDs: 6, Obj#: 51400 Name: OE.ORDERS Type: TABLE Undo recs: 2 Used undo segment IDs: 2, Obj#: 51396 Name: OE.ORDER_ITEMS Type: TABLE Undo recs: 15 Used undo segment IDs: 2, Obj#: 51397 Name: OE.ORDER_ITEMS_PK Type: INDEX Undo recs: 15 Used undo segment IDs: 2, Obj#: 51398 Name: OE.ORDER_ITEMS_UK Type: INDEX Undo recs: 15 Used undo segment IDs: 2, Obj#: 51401 Name: OE.ORDER_PK Type: INDEX Undo recs: 2 Used undo segment IDs: 2, Obj#: 51468 Name: OE.ORD_CUSTOMER_IX Type: INDEX Undo recs: 2 Used undo segment IDs: 2, Obj#: 51469 Name: OE.ORD_ORDER_DATE_IX Type: INDEX Undo recs: 2 Used undo segment IDs: 2, Obj#: 51467 Name: OE.ORD_SALES_REP_IX Type: INDEX Undo recs: 2 Used undo segment IDs: 2, *** Undo segments (headers) that encountered errors preventing Active TX scan: USN: 5 Name: _SYSSMU5$ File: 2 Block: 73 Instance: 0 Error: 20 - Bad block type *** Scanning database for datablocks that may require undo: *** Asterisk ('*') denotes blocks being updated since 04/09/2007 00:00:58 (SCN: 0x0.0xc54fef) Scanning datafile#: 5 - /u03/oradata/S102/example01.dbf (EXAMPLE) - Active TX blocks: 0 Scanning datafile#: 9 - /u02/oradata/S102/logmnr01.dbf (LOGMNR) - Active TX blocks: 0 Scanning datafile#: 6 - /u03/oradata/S102/perfstat01.dbf (PERFSTAT) - Active TX blocks: 0 Scanning datafile#: 8 - /u03/oradata/S102/stradmin01.dbf (STRADMIN) - Active TX blocks: 0 Scanning datafile#: 3 - /u02/oradata/S102/sysaux01.dbf (SYSAUX) - Active TX blocks: 23 * Scanning datafile#: 1 - /u02/oradata/S102/system01.dbf (SYSTEM) - Active TX blocks: 486 Scanning datafile#: 1 - /u02/oradata/S102/system01.dbf (SYSTEM) - Active TX blocks: 486 Temporary datafile (/u03/oradata/S102/temp01.dbf) - SKIPPING Scanning datafile#: 7 - /u02/oradata/S102/test01.dbf (TEST) - Active TX blocks: 0 Undo datafile (/u02/oradata/S102/undotbs01.dbf) - SKIPPING Scanning datafile#: 4 - /u03/oradata/S102/users01.dbf (USERS) - Active TX blocks: 0 Scanning datafile#: 10 - /u03/oradata/S102/users02.dbf (USERS) - Active TX blocks: 0 *** Objects that may require undo data: *** Asterisk ('*') denotes blocks being updated since 04/09/2007 00:00:58 (SCN: 0x0.0xc54fef) DataObj#: 593 Name: SYS.I_AW_OBJ$ Type: INDEX Datablocks: 1 Used undo segment IDs: 5, DBAs: 0x00c04797 ( 3. 18327) XID: 0005.0021.000000c3 Rows: 229 ---- DataObj#: 254 Name: SYS.I_H_OBJ#_COL# Type: INDEX Datablocks: 1 Used undo segment IDs: 5, DBAs: 0x0040f320 ( 1. 62240) XID: 0005.002a.00000267 Rows: 393 ---- DataObj#: 586 Name: SYS.AW_OBJ$ Type: TABLE Datablocks: 20 Used undo segment IDs: 5, DBAs: 0x00c04373 ( 3. 17267) XID: 0005.0021.000000c3 Rows: 24 ---- 0x00c04374 ( 3. 17268) XID: 0005.0021.000000c3 Rows: 25 ---- 0x00c04376 ( 3. 17270) XID: 0005.0021.000000c3 Rows: 11 ---- 0x00c0478a ( 3. 18314) XID: 0005.0021.000000c3 Rows: 21 ---- ... *** 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 04/09/2007 00:00:58 (SCN: 0x0.0xc54fef): USN: 5 Name: _SYSSMU5$ *** Undo segments identified in use by active transaction datablocks BEFORE 04/09/2007 00:00:58 (SCN: 0x0.0xc54fef): USN: 5 Name: _SYSSMU5$ NOTE: The database was scanned for active transactions using the problemmatic undo segments ONLY. To scan the database for ALL active transactions (using good and bad undo segments) use '-a' option NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE! ************************************************************************************* Upload the logfile (TXChecker_050307_1232_S102.log) to Oracle Support Services for analysis. Do NOT attempt to force the database open until the logfile has been analyzed. TXChecker_ex3.out TXChecker started at: Thu May 3 13:19:01 2007 TXChecker - v1.3 by Center Of Expertise (COE), Oracle Corporation (build 04/13/07) Program command: TXChecker -c/u03/oradata/S901/control02.ctl -lfiles.S901 -a Database Name: S901 Version: 9.0.1 *** Database last checkpointed at 03/20/2007 02:27:18 (SCN: 0x0.0x452b955) *** Using 60 minutes to find most active transactions (-m60) *** Undo Segments: USN: 0 Name: SYSTEM TBS#: 0 File: 1 Block: 2 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: 2 - Offline SCN: 0000.0452b950 XactSQN: 0x00011078 UndoSQN: 0x00004a8b USN: 2 Name: _SYSSMU2$ TBS#: 1 File: 2 Block: 25 Instance: 0 SMU: Y Status: 2 - Offline SCN: 0000.0452b944 XactSQN: 0x00010b60 UndoSQN: 0x000008a9 USN: 3 Name: _SYSSMU3$ TBS#: 1 File: 2 Block: 41 Instance: 0 SMU: Y Status: 2 - Offline SCN: 0000.0452b94d XactSQN: 0x000110fe UndoSQN: 0x000008e2 ... USN: 9 Name: _SYSSMU9$ TBS#: 1 File: 2 Block: 137 Instance: 0 SMU: Y Status: 2 - Offline SCN: 0000.0452b94b XactSQN: 0x0001106e UndoSQN: 0x00004a8e USN: 10 Name: _SYSSMU10$ TBS#: 1 File: 2 Block: 153 Instance: 0 SMU: Y Status: 2 - Offline SCN: 0000.0452b946 XactSQN: 0x0001102f UndoSQN: 0x00004a80 *** Undo segments (headers) that encountered errors preventing Active TX scan: USN: 1 Name: _SYSSMU1$ File: 2 Block: 9 Instance: 0 Error: 1 - Datafile was not accessible USN: 2 Name: _SYSSMU2$ File: 2 Block: 25 Instance: 0 Error: 1 - Datafile was not accessible USN: 3 Name: _SYSSMU3$ File: 2 Block: 41 Instance: 0 Error: 1 - Datafile was not accessible NOTE: IT IS RECOMMENDED TO SCAN THE DATABASE FOR OBJECTS INVOLVED IN ACTIVE TXS USING THESE BAD UNDO SEGMENTS *** Scanning datafiles for datablocks that may require undo: *** Asterisk ('*') denotes blocks being updated since 03/20/2007 01:55:35 (SCN: 0x0.0x44fe999) Scanning datafile#: 1 - /u02/oradata/S901/system01.dbf (SYSTEM) - Active TX blocks: 12 * Scanning datafile#: 4 - /u03/oradata/S901/tools01.dbf (TOOLS) - Active TX blocks: 0 Scanning datafile#: 4 - /u03/oradata/S901/tools01.dbf (TOOLS) - Active TX blocks: 0 Scanning datafile#: 5 - /u03/oradata/S901/users01.dbf (USERS) - Active TX blocks: 0 *** Objects that may require undo data: *** Asterisk ('*') denotes blocks being updated since 03/20/2007 01:55:35 (SCN: 0x0.0x44fe999) DataObj#: 118 Name: SYS.I_ARGUMENT2 Type: INDEX Datablocks: 9 * Used undo segment IDs: 3,4,5,6,7,10, DataObj#: 45 Name: SYS.I_COL1 Type: INDEX Datablocks: 1 * Used undo segment IDs: 10, DataObj#: 119 Name: SYS.I_SOURCE1 Type: INDEX Datablocks: 2 * 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 03/20/2007 01:55:35 (SCN: 0x0.0x44fe999): USN: 2 Name: _SYSSMU2$ USN: 3 Name: _SYSSMU3$ USN: 4 Name: _SYSSMU4$ USN: 5 Name: _SYSSMU5$ USN: 6 Name: _SYSSMU6$ USN: 7 Name: _SYSSMU7$ 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_050307_1319_S901.log) to Oracle Support Services for analysis. Do NOT attempt to force the database open until the logfile has been analyzed. TXChecker_ex4.out TXChecker started at: Thu May 3 14:58:02 2007 TXChecker - v1.3 by Center Of Expertise (COE), Oracle Corporation (build 04/13/07) Program command: TXChecker -f/u02/oradata/S101/users01.dbf -c/u01/oradata/S101/control01.ctl -x0001.0004.00000125 -b Database Name: S101 Version: 10.1.0 *** Database last checkpointed at 04/03/2007 17:11:29 (SCN: 0x0.0x33286) *** 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: 0000.0003309e XactSQN: 0x00000124 UndoSQN: 0x00000045 USN: 2 Name: _SYSSMU2$ TBS#: 1 File: 2 Block: 25 Instance: 0 SMU: Y Status: 3 - Online SCN: 0000.000330a4 XactSQN: 0x00000121 UndoSQN: 0x0000003d USN: 3 Name: _SYSSMU3$ TBS#: 1 File: 2 Block: 41 Instance: 0 SMU: Y Status: 3 - Online SCN: 0000.00033098 XactSQN: 0x0000011c UndoSQN: 0x00000044 .... *** Active Transactions: USN: 1 Name: _SYSSMU1$ File: 2 Block: 9 Instance: 0 Status: 3 - Online * Active TX at slot 4 #undo blocks: 6 Last bk: 2.540 *** Objects that may require undo data: *** Using XID: 1.4.125 Obj#: 9375 Name: SCOTT.OBJ Type: TABLE Undo recs: 207 Used undo segment IDs: 1, *** Scanning datafiles for datablocks that may require undo: *** Asterisk ('*') denotes blocks being updated since 04/03/2007 17:04:41 (SCN: 0x0.0x33102) Scanning datafile#: 4 - /u02/oradata/S101/users01.dbf (USERS) - Active TX blocks: 112 * *** Objects that may require undo data: *** Asterisk ('*') denotes blocks being updated since 04/03/2007 17:04:41 (SCN: 0x0.0x33102) *** Using XID: 1.4.125 DataObj#: 9375 Name: SCOTT.OBJ Type: TABLE Datablocks: 112 * Used undo segment IDs: 1, DBAs: 0x0100000c ( 4. 12)* XID: 0001.0004.00000125 Rows: 81 ---- 0x0100000d ( 4. 13)* XID: 0001.0004.00000125 Rows: 84 ---- 0x0100000e ( 4. 14)* XID: 0001.0004.00000125 Rows: 79 ---- 0x0100000f ( 4. 15)* XID: 0001.0004.00000125 Rows: 79 ---- 0x01000010 ( 4. 16)* XID: 0001.0004.00000125 Rows: 80 ---- *** 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 04/03/2007 17:04:41 (SCN: 0x0.0x33102): USN: 1 Name: _SYSSMU1$ NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE! ************************************************************************************* Upload the logfile (TXChecker_050307_1458_S101.log) to Oracle Support Services for analysis. Do NOT attempt to force the database open until the logfile has been analyzed. TXChecker_ex5.out TXChecker started at: Thu May 3 15:18:27 2007 TXChecker - v1.3 by Center Of Expertise (COE), Oracle Corporation (build 04/13/07) Program command: TXChecker -c/u02/oradata/S102/control02.ctl -tsystem -x0009.FFFF.FFFFFFFF -g Database Name: S102 Version: 10.2.0 *** Database last checkpointed at 05/03/2007 13:13:06 (SCN: 0x0.0xc64144) *** 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: 2 - Offline SCN: 0000.00c641a5 XactSQN: 0x00000208 UndoSQN: 0x0000020b USN: 2 Name: _SYSSMU2$ TBS#: 1 File: 2 Block: 25 Instance: 0 SMU: Y Status: 2 - Offline SCN: 0000.00c641b5 XactSQN: 0x000002a7 UndoSQN: 0x0000022a USN: 3 Name: _SYSSMU3$ TBS#: 1 File: 2 Block: 41 Instance: 0 SMU: Y Status: 2 - Offline SCN: 0000.00c641b4 XactSQN: 0x0000029d UndoSQN: 0x0000027e ... *** Tablespace Scan => SYSTEM *** Scanning tablespace for datablocks that may require undo: *** Asterisk ('*') denotes blocks being updated since 05/03/2007 12:02:06 (SCN: 0x0.0xc5eafd) Scanning datafile#: 1 - /u02/oradata/S102/system01.dbf (SYSTEM) - Active TX blocks: 1541 *** Objects that may require undo data: *** Asterisk ('*') denotes blocks being updated since 05/03/2007 12:02:06 (SCN: 0x0.0xc5eafd) *** Using XID: 9.ffff.ffffffff DataObj#: 114 Name: SYS.I_IDL_UB11 Type: INDEX Datablocks: 3 Used undo segment IDs: 10, DataObj#: 73 Name: SYS.IDL_UB1$ Type: TABLE Datablocks: 1538 Used undo segment IDs: 10, *** Use validate_objects.sql script file to validate the structure of possibly corrupt objects if the undo required is not available. NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE! ************************************************************************************* Upload the logfile (TXChecker_050307_1518_S102.log) to Oracle Support Services for analysis. Do NOT attempt to force the database open until the logfile has been analyzed. Back to Contents Appendix B: Listfile Example The listfile should contain a list of fully qualified datafile names: /u02/oradata/S901/system01.dbf /u03/oradata/S901/tools01.dbf /u03/oradata/S901/users01.dbf Back to Contents Appendix C: Workaround to the 250 X$KCCFE rows limitation (version 1.3 ONLY) The current version of TXChecker has a limitation where it will not work with a database that has more than 250 rows in the X$KCCFE table. This table represents datafile entries contained in the controlfile and is the basis for the V$DATAFILE view. Even when datafiles are deleted, their entries will still exist in X$KCCFE and X$KCCFN (file name table) until the slot is reused when new datafiles are added to the database. It is possible after dropping a large number of datafiles there are a greater number of rows in x$kccfe compared to v$datafile, which as a consequence will slow down queries of v$datafile. The only way to remove this row count difference is to rebuild the controlfile. If there are genuinely a large number of datafiles in the database (more than 250) a new temporary controlfile can be created which will only contain subset of the database datafiles, allowing TXChecker to run succesfully. database datafiles, allowing TXChecker to run succesfully. The instructions below will detail how to create a temporary controlfile for use with TXChecker that will contain 250 datafiles or less, working around the current limitation. The controlfile should NOT be used to open the database. NOTE: It is recommended to make a backup of the current controlfile before you do this, just in case you overwrite the controlfile by accident. 1. Mount the database using the current controlfile: SQL> STARTUP MOUNT 2. Create the script to create the temporary controlfile: SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS; SQL> show parameters user_dump_dest -- This is where the trace file is created SQL> SHUTDOWN IMMEDIATE; 3. Edit the create controlfile script and remove the files that are not needed for scanning by TXChecker: NOTE: Make sure you include the SYSTEM and UNDO datafiles as these are needed by TXChecker to access the data dictionary. The location of the script to edit is located in the user_dump_dest directory. Remove all lines before the first STARTUP MOUNT command Remove any datafiles listed after 'DATAFILE' that you don't want to scan and will bring the datafile count under 250 Example: CREATE CONTROLFILE REUSE DATABASE "F102" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 500 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/oradata/F102/redo01.log' SIZE 50M, GROUP 2 '/u01/oradata/F102/redo02.log' SIZE 50M, GROUP 3 '/u01/oradata/F102/redo03.log' SIZE 50M DATAFILE '/u01/oradata/F102/system01.dbf', '/u01/oradata/F102/undotbs01.dbf', '/u01/oradata/F102/sysaux01.dbf', '/u01/oradata/F102/users01.dbf', '/u01/oradata/F102/example01.dbf', '/u01/oradata/F102/streams01.dbf', ...etc. '/u01/oradata/F920/tbs25_6.dbf', '/u01/oradata/F920/tbs25_7.dbf', '/u01/oradata/F920/tbs25_8.dbf', '/u01/oradata/F920/tbs25_9.dbf', '/u01/oradata/F920/tbs25_10.dbf' CHARACTER SET WE8ISO8859P1; Remove any more text after the create controlfile statement. NOTE: Make sure you remove any commands after the create controlfile statement!!! Save the edited trace file and rename it to cr_ctl.sql 4. Edit the init.ora file so the create controlfile script will create the temporary controlfile in a new location without touching the current controlfile: If you are using an spfile, you need to create a pfile first: SQL> STARTUP NOMOUNT SQL> CREATE PFILE FROM SPFILE; -- Created in the $ORACLE_HOME/dbs directory SQL> SHUTDOWN IMMEDIATE NOTE: If you are using an spfile move it out of the way before moving to step #5 so that it won't be read on startup. Edit the init.ora in the $ORACLE_HOME/dbs directory and change the control_files parameter: Change from (example): control_files='/u01/oradata/F102/control01.ctl','/u01/oradata/F102/control02.ctl' To: control_files='/u01/oradata/F102/TXChecker_ctlfile.tmp' 5. Create the temporary controlfile: SQL> @cr_ctl.sql --- DO NOT OPEN the database -- SQL> SHUTDOWN IMMEDIATE; 6. Run TXChecker against this new controlfile. You can use the -d to scan the database, and all of the datafiles contained in the temporary controlfile will be scanned. 7. Edit the init.ora file back to use correct controlfiles or use the old spfile. When the database is opened the correct controlfile will be used, like before. Disclaimer Oracle Support Services provides TXChecker for diagnostic purposes ONLY. It has been tested and appears to work as intended as a read-only tool against a problem database to provide diagnostic data to be used by Oracle Support Services to help in formulating the best course of action after certain undo segment related failures. TXChecker will not provide you direct advice guaranteed to fix your failure and the logfile created MUST be uploaded to Oracle Support for further analysis. You should always run new scripts on a test instance initially.
Oracle TXChecker THE TXCHECKER USER GUIDE
2013/01/02 by Leave a Comment
Comment