Oracle故障診斷

如何DUMP UNDO 

7.3 onwards only

- Connect as a DBA.
  Issue 'ALTER SYSTEM DUMP UNDO' command.
  Check session tracefile for output.

- ALTER SYSTEM DUMP UNDO
[ HEADER ]
[ BLOCK [ ] ];

  Filters:
XID      Eg: select XIDUSN,XIDSLOT,XIDSQN
         from v$transaction
               to get the XID and plug in the values.
       Dumps UNDO for the transaction in
       the order it would be applied if the TX
       rolled back.
        UBA MIN     Not implemented yet
UBA MAX

EXTENT MIN     Not tested.
EXTENT MAX

OBJNO     Not tested.

        LAYER     See Note 29733.1 for REDO layers/opcodes
OPCODE      "                      "

LEVEL

  NB: There must be a space between all parameters.

Examples:
~~~~~~~~

- Dumping the rollback segment header (transaction table + other info):

     SVRMGR> alter system dump undo header 'RBS01';

- Dumping an undo chain by transaction ID (XID)

  1. Identify the XID to dump:

     SVRMGR> select xidusn, xidslot, xidsqn from v$transaction;
     XIDUSN    XIDSLOT    XIDSQN
     --------- ---------- ----------
             3          3        834

  2. Identify the rollback segment name (from xidusn in (1)):

     SVRMGR> select name from v$rollname where usn=3;
     NAME
     ------------------------------
     R03

  3. Dump the undo chain:

     SVRMGR> alter system dump undo block 'R03' xid 3 3 834;

In both examples above, trace files will be written to user_dump_dest.

 
 

ADJUST_SCN在資料庫災難恢復的時候十分有用，特別使對於ORA-600[2662]或在資料庫啟動的時候發生ORA-1555/ORA-604的時候

******** 以下為隱藏資訊 回帖後才能顯示 ********
The ADJUST_SCN event is useful in some recovery situations where the
    current SCN needs to be incremented by a large value to ensure it
    is ahead of the highest SCN in the database. This is typically
    required if either:
      a. An ORA-600 [2662] error is signalled against database blocks
    or
      b. ORA-1555 errors keep occuring after forcing the database open
         or ORA-604 / ORA-1555 errors occur during database open.
         (Note: If startup reports ORA-704 & ORA-1555 errors together
                then the ADJUST_SCN event cannot be used to bump the
                SCN as the error is occuring during bootstrap.
                Repeated startup/shutdown attempts may help if the SCN
                mismatch is small)
    or
      c. If a database has been forced open used _ALLOW_RESETLOGS_CORRUPTION
         (See )

    The ADJUST_SCN event acts as described below.

  **NOTE: You can check that the ADJUST_SCN event has fired as it
    should write a message to the alert log in the form
  "Debugging event used to advance scn to %s".
  If this message is NOT present in the alert log the event
  has probably not fired.

  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  If the database will NOT open:
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Take a backup.
    You can use event 10015 to trigger an ADJUST_SCN on database open:

startup mount;

alter session set events '10015 trace name adjust_scn level 1';

        (NB: You can only use IMMEDIATE here on an OPEN database. If the
     database is only mounted use the 10015 trigger to adjust SCN,
     otherwise you get ORA 600 [2251], [65535], [4294967295] )

alter database open;

If you get an ORA 600:2256 shutdown, use a higher level and reopen.

    Do *NOT* set this event in init.ora or the instance will crash as soon
    as SMON or PMON try to do any clean up. Always use it with the
    "alter session" command.

  ~~~~~~~~~~~~~~~~~~~~~~~~~~
  If the database *IS* OPEN:
  ~~~~~~~~~~~~~~~~~~~~~~~~~~
    You can increase the SCN thus:

alter session set events 'IMMEDIATE trace name ADJUST_SCN level 1';

    LEVEL:  Level 1 is usually sufficient - it raises the SCN to 1 billion
            (1024*1024*1024)
    Level 2 raises it to 2 billion etc...

    If you try to raise the SCN to a level LESS THAN or EQUAL to its
    current setting you will get <2256>    - See below.
    Ie: The event steps the SCN to known levels. You cannot use
the same level twice.

  Calculating a Level from 600 errors:
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get a LEVEL for ADJUST_SCN:

a) Determine the TARGET scn:
    ora-600 [2662]    See <2662>  Use TARGET >= blocks SCN
    ora-600 [2256]    See <2256>  Use TARGET >= Current SCN

  b) Multiply the TARGET wrap number by 4. This will give you the level
   to use in the adjust_scn to get the correct wrap number.
c) Next, add the following value to the level to get the desired base
   value as well :

        Add to Level         Base
        ~~~~~~~~~~~~ ~~~~~~~~~~~~
                   0            0
                   1   1073741824
                   2   2147483648
                   3   3221225472

 
 
Solution Description:
=====================
The following is a checklist of usual approaches to fix the ORA-01545 error at
database startup:

[I] Check if any of the following applies:
   * there are offlined tablespaces that are taken OFFLINE IMMEDIATE before 
     the instance crashed
   * there are datafiles that are manually taken offline by the DBA
     (notice that datafiles may also go offline by DBWR due to an I/O error)

Making files online after mounting the database does no harm provided that they
are available on disk.  To do so, execute the following commands from SQL*DBA:
      SQLDBA> connect internal
      SQLDBA> startup mount
      SQLDBA> ALTER DATABASE DATAFILE '' ONLINE;

Bringing the tablespace online is not possible since the database is not open
yet (an ORA-01109 would then be returned).

This step usually fixes the ORA-01545 error, unless the rollback segment got
corrupted.  In this case, you should try [II] below.  

[II] THE FOLLOWING STEPS SHOULD ONLY BE ATTEMPTED AFTER THE FIRST SOLUTION 
     ABOVE FAILS, AND THEY SHOULD BE ATTEMPTED AFTER DISCUSSING THE ISSUE WITH
     ORACLE WORLDWIDE SUPPORT. 

1. set the following events in your init.ora file:
     event = "10013 trace name context forever, level 10"
     event = "10015 trace name context forever, level 10"

The event 10013 traces instance recovery at startup and prints the names of the
rollback segments involved in the rollback phase of recovery.
The event 10015 traces and dumps the headers of the rollback segments involved
in the rollback phase of recovery (the header contains the transaction table).

THE EVENTS WILL DUMP A TRACE FILE IN YOUR background_dump_dest DIRECTORY.

2. If the two criteria (see [I]) have been checked and the rollback segment is
still labeled as "NEEDS RECOVERY", then there is a problem applying undo at the
rollback phase of recovery.  Here is where the event 10015 becomes useful,
since it performs the following:

   * It dumps the transaction table before and after recovery
   * For the rollback segment having problems applying undo, it also dumps the
     undo data block.  Notice that for this rollback segment, there will be a 
     dump of the transaction table before recovery but none for an image after 
     recovery, so it is easy to identify the rollback segment that "NEEDS 
     RECOVERY".  

3. Scan the transaction table of the rollback segment that "NEEDS RECOVERY" and
look for an uba (in place of an scn) and flg=4 ("dead transaction" flag). 
Committed transactions should have a scn value in lieu of an uba and flg=2. 
For example, the following transaction entry may appear in the trace file:
        ...
        44   : flg: 4 wrp: 354   uel: 1   uba: 18004bd1.08.08
        ... 

This entry is followed by a stack trace, process state and block dump of dba
18004bd1, which is part of the rollback segment that "NEEDS RECOVERY".  Look
for undo record number 8 by scanning the list of records in the dump.  Such an
undo record would look like

        undo record
        KTB REDO, op: C uba: 18004bd1.08.06
        KTB REDO, op: C uba: 18004bd1.08.06
        code=QMD       xtype=KA   bdba=10001cb2              ;;[*]
        itli=1 ispac=0 maxfr=1177
        tabn=0 lock=0  nrow: 1
        slot[0]: 51
          mgc: 0x1234 fst: 0 slt:44 obj: 2680 opc:0x0b01
          uba:18004bd1.08.07 scn: 1234.00

In the case of a corrupted rollback segment, the code entry (see [*] above)
would be garbage (some invalid string).  A message "unknown op code" is also
shown on that same line.

In the case that there is an offline datafile, an ORA-01135 error also appears
after the dump of the transaction table.

In order to ease identification of the [potentially] corrupted rollback
segment, the trace file also records the rollback segment number, the slot
number and the object that instance recovery was trying to recover.  For
instance,

         Error in recovery transaction (, ) 
         on bject=(

4. To fix the problem in the case of rollback segment corruption, do the
following:

   a. Remove the rollback segment name from the ROLLBACK_SEGMENTS parameter
list in the init.ora file and reboot the database.  The database should be able
to open since the rollback segments themselves are not available.

   b. Find out the object (based on

   NOTE:  There is no way to export the object on the spot since the DROP
command will fail as soon as it finds that there is an itl entry in the
block(s) of the object that is referencing the corrupted rollback segment.

   c. Replace the rollback segment name in the ROLLBACK_SEGMENTS parameter list
in the init.ora and reboot the database.  Since the object is already dropped,
the undo entry in the transaction table is irrelevant and is thus removed.
  
   At this point, it may be a good idea to drop and recreate the rollback
segment, so that everything is started clean.

5. If it is not possible to perform. step 4 above, you can have SMON generate a
deferred undo by doing the following:

   a. Remove the rollback segment name from the ROLLBACK_SEGMENTS parameter
list and reboot the database.

   b. Leave the database open for some time (say 30 minutes or so), long enough
for SMON to detect the rollback segment in "NEEDS RECOVERY" status.  Among the
many timeout action that SMON has to check and perform, it performs two actions:
* it copies the contents of the rollback segment that "NEEDS RECOVERY" 
          into a deferred rollback segment or "saveundo", located in the 
          SYSTEM tablespace.  NOTE:  You will need extra space in the
          SYSTEM tablespace.
* it makes the rollback segment "AVAILABLE" (not used by any instance).
   
Saveundo remains in the SYSTEM tablespace until the tablespace in question is
ONLINE again.

NOTE:  If the ORA-01545 refers to the SYSTEM rollback segment itself, then the 
       only solution is to restore and recover from a prior backup of the 
       database.