oracle txchecker用途

    大家有些人可能听说过txchecker，有人可能没有听说过，因为这个工具用的不多，一般只有在数据库无法打开，数据库由于undo或redo损坏的时候又没有数据库备份，数据库需要强行打开的时候才会用到，这个工具是oracle开发人员开发的，和dul工具类似，都是oracle内部工具。我写过一篇文中数据库强行打开，里边没有提及这个工具，这里单独介绍txchecker

    如果数据库没有备份，又无法打开，只能强行打开的时候，数据库可能会有一些事物没有完成，如果强行打开，那么数据库就出现了不一致现象，那么这些事物是哪些对象，我们是不知道的，txchecker这个工具检查undo表空间中到底哪些事物没有完成。分几种情况：

     1. 检查事物全部完成，那么强行打开就可以继续使用此数据库;

     2. 如果发现是应用的表，那么打开后，把表的数据导出，然后再导入一下，数据库也可以继续使用;

    3. 如果是应用的index，那么把索引重新创建一下，数据库也可以继续使用；

     4. 如果是数据库的书籍字典，那么就没有办法了，只能把应用数据导出来，重新创建数据库，然后导入数据了。

 记住：这个工具只检查undo，不会检查redo。

   下面把txchecker给大家介绍一下，这个是转自oracle知识库中的一片文章，使用还是比较简单的

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_CORRUPTION parameter. 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. 




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: mailto:[e-mail]%20target= 


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:
-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) 
-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 mailto:[e-mail]%20target=. 


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
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
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#: 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.


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.