Oracle TXChecker THE TXCHECKER USER GUIDE

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: [email protected]
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 [email protected].
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.