当前位置: 首页 > MOS > 正文

oracle rollback segments之一

This articles is best read in an HTML enabled browser / email client.
Click here to "Skip the Introduction".
IMPORTANT: If you have already forced the database open using unsupported methods (eg: _allow_resetlogs_corruption or _corrupted_rollback_segments) then this note is of limited use to you as you MUST then rebuild the database. See <[NOTE:41399.1]> for that scenario.
Introduction
This article describes how to recover from various rollback segment
related problems where there is a corruption or a lost datafile.
The focus is purely on identifying any problems and clearing them
step by step.

NOTE: Information here relates to Oracle7.3 , Oracle 8.0 and
Oracle 8.1 only. Prior to Oracle 7.3 transaction recovery
is performed before the database opens.

A separate article <[NOTE:105871.1]> uses a simple example to show
why rollback segment related corruptions can be very serious and often
require point in time recovery or database rebuild if the corruption
cannot be cleared. It helps if you understand the concepts in that
article before proceeding with this article.
Much of this article centres around a common set of selects which
determine which actions to take and shows the options available.
The article is layed out thus:

List of errors discussed in this article

Main Steps required to Recover a Rollback Segment
                Recovery queries and steps
                Dropping a rollback segment
                Last Options

Specific steps for various errors
                Startup errors
                Other errors

Advanced Notes
        What are _OFFLINE_ROLLBACK_SEGMENTS
        What are _CORRUPTED_ROLLBACK_SEGMENTS
        Differences between these Parameters
        List of Useful Events
        List of Common ORA-600’s

List of errors discussed in this article
The errors below each have their own section towards the END of this
article. If you have one of these errors go to the relevant section
first.
Startup Errors:
ORA-1578 on Startup
ORA-1545 during Startup
Errors after Startup:
DBA_ROLLBACK_SEGS entry of status "NEEDS RECOVERY"
DBA_ROLLBACK_SEGS entry of status "PARTLY AVAILABLE"
ORA-1545 during DROP ROLLBACK SEGMENT <rbsname>
ORA-1546: tablespace contains active rollback segment ‘<rbsname>’
Error 376 encountered while recovering transaction (A, B) on object YYYY.
Error 600 encountered while recovering transaction (A, B) on object YYYY.
Error XXXX encountered while recovering transaction (A, B). (no object YYYY listed)
ORA-1578 on a Rollback Segment Header
ORA-1578 on a Rollback Segment Block
ORA-1578 on a REQUIRED Rollback Segment Block

Recovering Rollback Segments
The actions here are common to many problems related to rollback
segments – you may return to this section several times while
sorting out a problem so it may be sensible to save the queries here
in a script.

Actions:
1. Oracle needs access to the rollback segment header in order to
know if a rollback segment contains active transactions or not.
What we can do next depends on the FILESTATUS and STATUS$ entries
returned from the following query:

(NB: The database must be OPEN to run the queries below)

Oracle8:
                SELECT u.us#,         u.name RBSNAME,
                        u.status$,
                        r.status ROLLSTAT,
                        f.status FILESTATUS,
                        f.name FILENAME
FROM undo$ u, v$datafile f , V$rollstat r
WHERE f.rfile#=u.file#
AND f.ts#=u.ts#
AND r.usn(+)=u.us#
AND u.name='<rbsname>’
                ;

Oracle7:
                SELECT u.us#,         u.name RBSNAME,
                        u.status$,
                        r.status ROLLSTAT,
                        f.status FILESTATUS,
                        f.name FILENAME
FROM undo$ u, v$datafile f , V$rollstat r
WHERE f.file#=u.file#
AND r.usn(+)=u.us#
AND u.name='<rbsname>’
                ;

Query Columns:
~~~~~~~~~~~~~~
US#          Is the Undo Segment number (known as USN, US# or SEGMENT_ID)
RBSNAME Is the rollback segment name
STATUS$ Is the numeric status of the rollback segment in UNDO$
This is visible in decoded text form in DBA_ROLLBACK_SEGS
ROLLSTAT Is the status in V$ROLLSTAT – this is for information only
and shows if a currently ONLINE rollback segment is
PENDING OFFLINE or if it is FULL
FILESTATUS Is the status of the FILE containing the rollback segment
header.
FILENAME Is the NAME of the file containing the rollback segment
header.

( If there are multiple rows returned for a given rollback segment
then all but one of them should have STATUS$ of "1". It is the row
        with STATUS$ != 1 that you are interested in )

2. If FILESTATUS is "RECOVER" then the file may need to be recovered
Go to "Rollback Segment Datafile needs recovery"
and choose the relevant STATUS$ sub-section:
         STATUS$ = "1" "2" "3"
"4" "5" "6"

3. If FILESTATUS is "OFFLINE" then the file may not be needed but it is
best to check it out.
Go to "Rollback Segment Datafile needs recovery"
and choose the relevant STATUS$ sub-section:
         STATUS$ = "1" "2" "3"
"4" "5" "6"

4. If FILESTATUS is "ONLINE" then the rollback segment header should be
accessible (unless the rollback segment header itself is corrupt).
Go to "RBS Header Accessible" and choose
the relevant subsection:
         STATUS$ = "1" "2" "3"
"4" "5" "6"

(Normally a corrupt rollback segment header would be noticed
at startup but if the file containing the corrupt RBS header is
OFFLINE at startup then it may subsequently be onlined
)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Rollback Segment Datafile needs recovery
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If FILESTATUS is "RECOVER" or "OFFLINE" then the rollback
segment header is not accessible and the next action depends
on the value of UNDO$.STATUS$:

SELECT * FROM undo$ WHERE name=<rbsname>;

STATUS$ = 1 (does not exist)
        STATUS$=1 means RBS does not exist.
        Go back and check the alert log message for the error as you
        cannot have the correct rollback segment name OR someone
        else is performing DBA operations on the database.

STATUS$ = 2 (Rollback segment is OFFLINE)
        STATUS$=2 means the RBS is OFFLINE

You have 2 options here:
                Drop the rollback segment
or
                Recover the lost datafile
        The rollback segment can probably be dropped if it is marked as OFFLINE
        (status$=2) in UNDO$ as it CANNOT contain any active transactions.
        Oracle will not mark a rollback segment as OFFLINE if it contains
        any active or in-doubt transactions.
        Go to "Dropping a Rollback Segment" to confirm it can be dropped.

STATUS$ = 3 (Rollback segment is ONLINE)
        STATUS$=3 means the RBS is ONLINE, but for some reason the
        file containing the rollback segment is offline.

Recover the datafile to the current point in time and online it.
        This is only possible if the database is in ARCHIVELOG mode.
        eg:         
                Restore it at OS level from a good backup,
                RECOVER DATAFILE ‘name_of_file’;
                ALTER DATABASE DATAFILE ‘name_of_file’ ONLINE;

If the file cannot be recovered or brought online you will have to
        revert to an old point-in-time or rebuild – see "Last Options".

Once the file is online then the RBS header should now be visible.
        Go back to "Recovering Rollback Segments" to check on the status
        of the rollback segment.

STATUS$ = 4 (invalid)
        STATUS$=4 is an invalid status.

STATUS$ = 5 (Rollback segment NEEDS RECOVERY)
        STATUS$=5 means the RBS is not fully recovered. Oracle MUST see the
RBS header to know if there are transactions to roll out.

a. Recover the datafile to the current point in time and online it.
This is only possible if the database is in ARCHIVELOG mode.
eg: Save the current copy of the file
Restore a good backup of the file
RECOVER DATAFILE ‘<name>’
                ALTER DATABASE DATAFILE ‘<name>’ ONLINE;
If the file cannot be recovered or brought online you will have
to revert to an old point-in-time or rebuild – see "Last Options".

b. Once the file is online then the RBS header should be visible.
However, you cannot assume that everything is now fine as it
is possible that there is a corruption in the file you have
just put online.

Go back to "Recovering Rollback Segments" to check the rollback
segment can now be recovered.
(If all is OK SMON will eventually clear up the rollback segment
status. eg: In the alert log you should see:
                        "SMON: about to recover undo segment 4
                         SMON: mark undo segment 4 as available"
)

STATUS$ = 6 (Rollback segment is PARTLY AVAILABLE)
        STATUS$=6 means PARTLY AVAILABLE. A "Partly Available" rollback
        segment will change to "NEEDS RECOVERY" if the datafile remains
        offline. Follow the steps in STATUS$=5 above.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
RBS Header Accessible
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If the rollback segment header is accessible we need to find out if
the rollback segment still needs to be recovered and if so why.
The next action depends on the value of UNDO$.STATUS$:

SELECT * FROM undo$ WHERE name=<rbsname>;

STATUS$ = 1 (does not exist)
        Go back and check the alert log message for the error as you
        cannot have the correct rollback segment name OR someone
        else is performing DBA operations on the database.

STATUS$ = 2 (Rollback segment is OFFLINE)
        Everything now looks fine for this rollback segment.
        The file containing the rollback segment is ONLINE and the
        rollback segment itself is OFFLINE.

STATUS$ = 3 (Rollback segment is ONLINE)
        The file containing the rollback segment is ONLINE and the
        rollback segment itself is ONLINE. However, it is possible
        for the rollback segment to contain active transactions
        that are blocked from being rolled out.

It is sensible to take this rollback segment offline to prevent
        Oracle starting any new transaction in the rollback segment
        until it has been cleaned up.
        eg: ALTER ROLLBACK SEGMENT <rbsname> OFFLINE;

Go to "Checking for active transactions on a rollback segment".

STATUS$ = 4 (invalid)
        STATUS$=4 is an invalid status.

STATUS$ = 5 (Rollback segment NEEDS RECOVERY)

a. Check the alert log and SMON trace file for errors
showing why the rollback segment has not been recovered.
eg: The alert log should show messages of the form:

SMON: about to recover undo segment 4
ORACLE Instance V734 (pid = 6) –
Error 376 encountered while recovering transaction (4, 2)
                on object 1357.

Errors in file /…/bdump/v734_smon_6793.trc:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: ‘/tmp/RPcor2.dbf’
SMON: mark undo segment 4 as needs recovery

These show us why the rollback segment cannot be recovered.
        In this example it is because file 9 is OFFLINE.

b. If it is not clear what the error is from the alert log then issue
        the command:

ALTER ROLLBACK SEGMENT <rbs-name> ONLINE;

This should cause the current session to attempt to put
        the rollback segment ONLINE and will signal errors to the
        alert log / user trace file if this is not possible.

Note: Altering the rollback segment online will only show an error if
the rollback segment is not currently shown as online in UNDO$
(ie: if status$!=3) and there is still a problem.

eg1:
If a file required for the rollback is OFFLINE then Oracle writes
messages to the alert log:
ORACLE Instance V734 (pid = 8) –
Error 376 encountered while recovering transaction (4,8)
on object 1379.
Errors in file /…/udump/v734_ora_6661.trc:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: ‘/tmp/RPcor2.dbf’
ORA-1608 signalled during: alter rollback segment rprbs online…

and signals an error to the user session:
ORA-01608: cannot bring rollback segment ‘<rbsname>’ online,
                        its status is (5)

Go to "Error 376 encountered while recovering transaction (A,B) on object YYYY"

eg2:
If the rollback segment header is CORRUPT then Oracle writes an
ORA-1578 to the alert log and signals an error to the user session:

ORA-01578: ORACLE data block corrupted (file # 11, block # 2)
ORA-01110: data file 1198: ‘/tmp/RPrbcor.dbf’

Go to "ORA-1578 on rollback segment header"

eg3:
If the rollback segment header is fine and all transactions can be
rolled out then onlining the rollback segment will succeed.
Go back to "Recovering Rollback Segments"
        eg4:
If the "ALTER ROLLBACK SEGMENT rprbs ONLINE;" returns
ORA-1608 but there is no additional error in the alert log then
check if you have an _OFFLINE_ROLLBACK_SEGMENTS parameter set:

SELECT c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppsv c
WHERE a.indx = c.indx
AND a.ksppinm = ‘_offline_rollback_segments’;

If there are any rollback segments listed shutdown, remove the
_OFFLINE_ROLLBACK_SEGMENTS information from the init.ora file,
startup and go to "Recovering Rollback Segments"

NB: Using the ‘_OFFLINE_ROLLBACK_SEGMENTS’ parameter is unsupported
so there should be none in this state.
See "What are _OFFLINE_ROLLBACK_SEGMENTS")

STATUS$ = 6 (Rollback segment is PARTLY AVAILABLE)

A "Partly Available" rollback segment either contains in doubt
distributed transactions or a DEAD transaction that is successfully
being rolled out. If an error is encountered during the rollback
the segment will change to status$=5 (NEEDS RECOVERY).
Go to "Checking for active transactions on a rollback segment".

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Checking for active transactions on a rollback segment
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To check for any active transactions on a rollback segment:

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
                 KTUXESTA Status,
                 KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!=’INACTIVE’
AND ktuxeusn=<SEGMENT_ID>
;

If this returns rows then these transactions must commit or roll back
before the rollback segment can be considered fully recovered.
Possible combinations of returned values include:

Status Flags                 Meaning
~~~~~~ ~~~~~                 ~~~~~~~
ACTIVE NONE (or null)        Transaction is currently active awaiting to
either commit or roll back.
ACTIVE DEAD                 Transaction is dead and needs to be rolled out.

PREPARED SCO|COL|REV|DEAD         This is part of a distributed transaction.
                                The DEAD indicates it is a failed distributed
                                transaction but as it is PREPARED it may need
                                to commit or roll back.

Note: If the select returns no rows do not immediately assume that the
rollback segment is clear of any transactions – if Oracle cannot
see the rollback segment header then the above select will show no
rows. If you are not sure if the rollback segment header is
         accessible then perform these steps:

ALTER ROLLBACK SEGMENT <rbsname> ONLINE;
                 ALTER ROLLBACK SEGMENT <rbsname> OFFLINE;
                SELECT status FROM dba_rollback_segs
                 WHERE segment_name='<rbsname>’;

Provided there are no errors and this returns OFFLINE as the
         status then this rollback segment is now clear.
( The information in <> is taken from the rollback segment
header when accessible. If the header is NOT accessible then there is
no point taking a blockdump of it as it cannot be guaranteed to be the
        correct current copy unless you can bring it on-line – in which case
        you might as well use X$KTUXE )

More detail on returned rows
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ACTIVE / DEAD
~~~~~~~~~~~~~
If the KTUXECFL column contains DEAD then the transaction
will be rolled back by SMON – monitor the alert log for problems.
Go to "List of Errors"

ACTIVE / NONE (or null)
~~~~~~~~~~~~~
This is a normal user transaction which must complete or roll back.
The session/s working on the active transaction/s can be found using this
SQL:

SELECT s.*, v.*
FROM v$session s, v$transaction v, x$ktuxe t
WHERE v.xidusn=ktuxeusn
AND v.xidslot=ktuxeslt
AND v.xidsqn=ktuxesqn
AND s.taddr=v.addr
AND t.ktuxesta=’ACTIVE’
AND t.ktuxeusn='<segment_id>’
;

PREPARED / DEAD
~~~~~~~~~~~~~~~
A failed distributed transaction which needs to either commit or
roll back. This note does not go into detail of how to correctly
resolve failed distributed transactions but if this rollback segment
is causing problems and needs this transaction removed one can
FORCE COMMIT or FORCE ROLLBACK the transaction.

eg: COMMIT FORCE ‘A.B.C’ ;

where A.B.C is the local transaction id from the columns
         "KTUXEUSN, KTUXESLT, KTUXESQN".

Dropping a Rollback Segment
The steps here are safety checks to perform prior to dropping a rollback
segment. Please check all steps:

1. Double check if you have an _OFFLINE_ROLLBACK_SEGMENTS parameter set:
(there should be none set)

SELECT c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppsv c
WHERE a.indx = c.indx
AND a.ksppinm = ‘_offline_rollback_segments’;

If the rollback segment you want to drop is listed here, or this list
is longer than about 60 characters check the parameter in the init.ora
file. It is VITAL that any rollback segment to be DROPPED is NOT listed
in an _OFFLINE_ROLLBACK_SEGMENTS parameter. See "What are _OFFLINE_ROLLBACK_SEGMENTS")
If the rollback segment is listed in this parameter:
        Shutdown the database
        Remove the rollback segment from the _OFFLINE_ROLLBACK_SEGMENTS list
        Startup
        Go to "Recovering Rollback Segments" and check out the rollback segment

2. Take the rollback segment offline to stop new transactions starting in
this segment:
                ALTER ROLLBACK SEGMENT <name> OFFLINE;

3. Check that the rollback segment has changed status to OFFLINE:

SELECT status FROM dba_rollback_segs
                 WHERE segment_name='<rbsname>’;

4. If the segment is listed as OFFLINE then DROP the rollback segment:

DROP ROLLBACK SEGMENT <name>;

If the DROP returns and ORA-1545 OR the STATUS above is NOT "OFFLINE"
then go back to "Recovering Rollback Segments" to check out this
rollback segment.

"Last Options"
This section outlines the absolute final options available for recovering
a database. If you have come here then one or more of the following
have happened:

You have lost a "vital" datafile (or have a corruption on it)
and have no useful backup of this file
and are either not in ARCHIVELOG mode OR do not have all archivelogs
         since the file was first created

Last chance:
Please note if you have lost all copies of a datafile but DO still have
the ARCHIVE logs from when the file was first created it is still possible
to recover the file.
Eg:
        ALTER DATABASE CREATE DATAFILE ‘….’ [as ‘…’] ;
        RECOVER DATAFILE ‘….’
        ALTER DATABASE DATAFILE ‘….’ ONLINE;

If you are in this scenario try to recover the datafile using these
steps before proceeding below.

If you have reached this line there are no options left to keep the
CURRENT version of the database up and running in a fully supported manner.

It is advisable to shutdown the instance and take a BACKUP of the
current database NOW in order to provide a fall-back position if the
chosen course of action below fails. (Eg: if you find your backup is bad)

The outline options available are:

Revert to an old COLD backup
        – eg: If in NOARCHIVELOG mode

Point in time recovery to an older point in time that is consistent
        – requires a good backup and any necessary archive logs
        – ALL files have to be restored and the whole DB rolled forward
to a suitable point in time.

Rebuild of DB from some logical export / copy
        – Requires there to already be a good logical backup of the database
        – NB: You have to RE-CREATE the database for this option.

Attempt to export the current database and rebuild a new database
        – using either export or DUL or some other option
        – If the database will not open unsupported means may be needed
to force the DB open. See <[NOTE:41399.1]> for details of
forcing a database open.

Let the system continue running in its current form until
        a more suitable period is available to rebuild
        – the database is not supportable in this form but the risk of
a failure MAY be very small and hence a customer may prefer
to schedule time for a proper rebuild.
**NOTE** It is important to stress that a database left running
with a corrupt or unrecovered rollback segment may run for only
a few minutes and may die at any time. If you are sure there are
only a few blocks affected by the transaction/s and that these
are not used by the main application then deferring a rebuild
may be an option but A REBUILD MUST BE PERFORMED.
– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Specific steps for various errors

~~~~~~~~~~~~~~
Startup Errors
~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~
ORA-1578 on Startup
~~~~~~~~~~~~~~~~~~
An ORA-1578 on startup is usually bad news and relates to either a corrupt
rollback segment header, or a corrupt block being referenced during
bootstraping of the instance.
eg:
        Database mounted.
        ORA-01578: ORACLE data block corrupted (file # 11, block # 2)
        ORA-01110: data file 1198: ‘/tmp/RPrbcor.dbf’
        SVRMGR>

( Recovery does not fail if a corrupt block is encountered – the block is
skipped over and recovery continues. Warnings are written to the user
trace file.
eg:
        Corrupt block dba: 0x20000003 file=8. blocknum=3. found during
media/instance recovery
        on disk type:6. ver:1. dba: 0x2000ffff inc:0x00000001 seq:0x00000007
incseq:0x00010007
        Reread of block=20000003 file=8. blocknum=3. found same corupted data
Actions:
1. Shutdown the instance (or you may get ORA-704/ORA-604/ORA-955 when you
next try to open the database)
eg: SHUTDOWN ABORT

2. Although it is possible to offline the affected file/s and double check
which object is involved it is better to first look at recovering the
corrupted file. This is only possible in ARCHIVELOG mode.
eg:
Take a SAFE copy of the existing problem file
Restore a good backup of the problem file
STARTUP MOUNT
ALTER DATABASE DATAFILE ‘name_of_file’ ONLINE;
RECOVER DATABASE
apply any required archive logs
ALTER DATABASE OPEN;

3. If the ORA-1578 persists or the file cannot be restored then:
a. If this is a SYSTEM tablespace datafile you are in trouble.
Go to "Last Options"

b. If this is not a SYSTEM tablespace datafile you MAY be able to
continue as below.

4. If the ORA-1578 is on a rollback segment header then it is possible
that the header is only being accessed because Oracle is trying to
online the rollback segment. To check for this we can comment out all
of the rollback segments in the init.ora file and attempt to start the
database.
eg: Comment out the ROLLBACK_SEGMENTS=… clause

If you are using PUBLIC rollback segments then also set the init.ora
parameter TRANSACTIONS to a small number (about 20) and
TRANSACTIONS_PER_ROLLBACK_SEGMENT to the same number . Additionally
try to find one rollback segment which is known to be good and set
this in the ROLLBACK_SEGMENTS parameter. This is done to try to stop
Oracle needing to online any PUBLIC rollback segment when the database
opens. If there are no rollback segments you know to be good you can
try this step several times using different named rollback segments.
eg: TRANSACTIONS=20
TRANSACTIONS_PER_ROLLBACK_SEGMENT=20
ROLLBACK_SEGMENTS=(OK_RBS)

Now try to start the database:
eg:
SHUTDOWN ABORT
STARTUP

If the database opens go to step 6

5. If the above has not allowed you to open the database then the next
step is to attempt to offline the problem file:
eg:
SHUTDOWN ABORT
STARTUP MOUNT
ALTER DATABASE DATAFILE ‘name_of_file’ OFFLINE;
ALTER DATABASE OPEN;

If the "ALTER DATABASE DATAFILE … OFFLINE" reports
"ORA-01145: offline immediate disallowed unless media recovery enabled"
go to "NOARCHIVELOG" below.

6. If the database opens check which object has the ORA-1578 error.
WARNING: On Oracle8 you need the file number from the accompanying
ORA-1110 error.
SELECT segment_type, owner, segment_name
FROM dba_extents
WHERE file_id=<file_num>
AND <block_num> BETWEEN block_id and block_id+blocks-1
;

If SEGMENT_TYPE is ROLLBACK SEGMENT go to "Recovering Rollback Segments".

If OWNER is SYS more detailed investigation is required to determine
whether the problem object can be rebuilt.

For any other object see:
         Oracle7 see <[NOTE:28814.1]>
         Oracle8 see <[NOTE:61685.1]>

~~~~~~~~~~~~
NOARCHIVELOG
~~~~~~~~~~~~
If an attempt to OFFLINE the problem file raises "ORA-01145: offline
immediate disallowed unless media recovery enabled" then the database is
not running in ARCHIVELOG mode. This SEVERELY limits the recovery options.

The fact you have an ORA-1578 during startup means we need to see the
corrupt file / block. It is most likely a corrupt rollback segment
block – it may be possible to check this using the following steps.

1. SHUTDOWN ABORT

2. Take a backup of the database NOW as the following steps could be
destructive.

3. Edit the INIT.ORA file and comment out the ROLLBACK_SEGMENTS clause

4. Add a new parameter "_OFFLINE_ROLLBACK_SEGMENTS" to the init.ora file.
Set this equal to a list of rollback segments which you think MAY
be in the problem file. You can add ALL rollback segments to this
list but it is preferred just to add those you suspect to be in
the problem file.

*** WARNING "_OFFLINE_ROLLBACK_SEGMENTS" is an UNSUPPORTED parameter
See "What are _OFFLINE_ROLLBACK_SEGMENTS")
5. Start the instance

6. If the database starts then the problem is almost certainly in one of
the listed rollback segments. Comment out the _OFFLINE_ROLLBACK_SEGMENTS
parameter NOW so you do not forget to do it later.

If the database still signals an ORA-1578 then add other rollback
segments to the _offline parameter or go to "Last Options".

7. Once the database is open you can query DBA_EXTENTS to check the exact
object:
WARNING: On Oracle8 you need the file number from the accompanying
ORA-1110 error.

SELECT segment_type, owner, segment_name
FROM dba_extents
WHERE file_id=<file_num>
AND <block_num> BETWEEN block_id and block_id+blocks-1
;

Assuming this is a rollback segment and you are in NOARCHIVELOG
mode then you may want to try and salvage any data from the database
now as you cannot run the instance with an _OFFLINE_ROLLBACK_SEGMENT.
Go to "Last Options" for the only other options.

If this is an object other than a rollback segment the action to
take depends on the object type and owner.

~~~~~~~~~~~~~~~~~~~~~~~
ORA-1545 during Startup
~~~~~~~~~~~~~~~~~~~~~~~
This means that the named rollback segment is listed in the
ROLLBACK_SEGMENTS init.ora parameter and that this rollback segment
is not available. This is usually because the datafile containing the
rollback segment header is offline.
eg: Database mounted
ORA-01545: rollback segment ‘<rbsname>’ specified not available

(Note: a corrupt rollback segment header would usually raise ORA-1578 at
startup and not an ORA-1545)

If you are running Oracle Parallel Server (OPS) then ORA-1545 can also be
signaled if another instance currently has the named rollback segment in use.

Actions:
1. Make a note of the rollback segment name in the error

2. Check for any offline files that may contain this rollback segment.
eg: SELECT name FROM v$datafile
WHERE status not in (‘ONLINE’,’SYSTEM’);

3. Shutdown the instance (or you will get ORA-704/ORA-604/ORA-955 when you
next try to open the database)
eg: SHUTDOWN ABORT

4. Take the rollback segment OUT of the ROLLBACK_SEGMENTS clause in the
init.ora file and startup MOUNT.
eg: STARTUP MOUNT

5. Restore, online and recover any file you suspect may contain the
missing rollback segment (as determined in step 2 above).
This is only possible if you are in ARCHIVELOG mode.
eg: Save any current copy of the file
Restore a good backup copy of the file
ALTER DATABASE DATAFILE ‘name_of_file’ ONLINE;
RECOVER DATABASE

If you cannot recover the file continue to step 6.

6. Attempt to OPEN the database
eg: ALTER DATABASE OPEN;

7. Assuming the database now starts up the you need to check if the
rollback segment needs recovering.
Go to "Recovering Rollback Segments"

~~~~~~~~~~~~
Other Errors
~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DBA_ROLLBACK_SEGS entry of status "NEEDS RECOVERY"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The DBA_ROLLBACK_SEGS view shows a rollback segment as "NEEDS RECOVERY"
if Oracle cannot see the rollback segment header or if there is a problem
encountered when attempting to roll out a transaction (eg: a tablespace or
datafile required to roll out a transaction is OFFLINE).

Actions:
1. Make a note of the SEGMENT_NAME for the DBA_ROLLBACK_SEGS row
which has STATUS set to "NEEDS RECOVERY"
eg:
SELECT segment_id, segment_name
FROM dba_rollback_segs
WHERE status=’NEEDS RECOVERY’;

2. Go to "Recovering Rollback Segments"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DBA_ROLLBACK_SEGS entry of status "PARTLY AVAILABLE"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The DBA_ROLLBACK_SEGS view shows a rollback segment as "PARTLY AVAILABLE"
if it is otherwise OFFLINE and either:

a. it contains active transactions that SMON has not yet finished
recovering
or
        b. it contains in-doubt transactions

In case ‘a’ SMON will continue to roll out any active transactions in the
rollback segment and finally mark it as ‘OFFLINE’. If an error is
encountered preventing a transaction from rolling out the status is changed
to "NEEDS RECOVERY".

In case ‘b’ the in-doubt transaction has to be resolved.

It is possible to ONLINE a "PARTLY AVAILABLE" rollback segment – doing so
will change its status to "ONLINE".

Go to "Recovering Rollback Segments"
Go to "Checking for active transactions on a rollback segment" to see
the state of transactions on this rollback segment.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-1545 during DROP ROLLBACK SEGMENT <rbsname>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-1545 may be signalled during a "DROP ROLLBACK SEGMENT" if the
rollback segment is currently online.
eg: SVRMGR> drop rollback segment rprbs;
        drop rollback segment rprbs
        *
        ORA-01545: rollback segment ‘<rbsname>’ specified not available

Actions:
1. Check the status of the rollback segment in DBA_ROLLBACK_SEGS.
eg: SELECT segment_id, status FROM dba_rollback_segs
WHERE segment_name = ‘<rbsname>’;

Note the SEGMENT_ID and STATUS for the steps below.

2. If the STATUS is ONLINE then:

ALTER ROLLBACK SEGMENT <rbsname> OFFLINE;
DROP ROLLBACK SEGMENT <rbsname>;

If this still reports ORA-1545 continue with step 3 below.

3. ORA-1545 may also be signalled if there is a problem reading
the rollback segment header information or if the rollback segment
contains active or in-doubt transactions. Note the SEGMENT_ID and
SEGMENT_NAME and go to "Recovering Rollback Segments"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-1546: tablespace contains active rollback segment ‘<rbsname>’
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The named tablespace contains an active rollback segment as named
in the error.
Actions:
1. Offline the named rollback segment:
eg: ALTER ROLLBACK SEGMENT <rbsname> OFFLINE;

2. Re-attempt to offline the tablespace

3. If this still errors then the rollback segment contains active
transactions or needs recovering.
Go to "Recovering Rollback Segments"

~~~~~~~~~~~~~~
Error 376 encountered while recovering transaction (A, B) on object YYYY.
~~~~~~~~~~~~~~
(A, B) shows the rollback segment id and slot number (USN , SLOT)
YYYY shows the object id (NOT the data object id)

If a file is being reported as offline (ORA-376) then there are
2 main options:

a. Restore and recover the offline file and bring it online.
This is only possible if the database is in ARCHIVELOG mode.
eg:         Restore it at OS level from a good backup,
                RECOVER DATAFILE ‘name_of_file’;
                ALTER DATABASE DATAFILE ‘name_of_file’ ONLINE;
The rollback can now continue.
Go back to "Recovering Rollback Segments"

b. If the file cannot be restored then it is possible to continue by
dropping object YYYY.

eg: Oracle8 and Oracle7:
                SELECT object_type, owner, object_name
FROM DBA_OBJECTS
                 WHERE object_id= <YYYY>
                ;

If you drop object YYYY SMON will notice that this object no longer
exists and so can skip any undo relating to that object.

NOTE-A: If you drop the object it is lost.
If the object spans several files and only one of them is lost
you may want to salvage data from the object (if possible)
before dropping it.

NOTE-B: If the undo touches other objects in the same offline datafile
then the rollback stops at the next inaccessible block.

~~~~~~~~~~~~~~
Error 600 encountered while recovering transaction (A, B) on object YYYY.
~~~~~~~~~~~~~~
(A, B) shows the rollback segment id and slot number (USN , SLOT)
YYYY shows the object id (NOT the data object id)

The fact that an object is listed in the error implies the ORA-600
is most likely related to the target block of the undo, and not the
undo block itself. If the ORA-600 is a block checking related
error then there is a strong chance that block level recovery will
re-try the operation and mark the block as corrupt thus resulting in
the transaction being recovered (as we skip over corrupt blocks as
described in "Discarding Undo in <[NOTE:105871.1]>"). If the block does
not get marked as corrupt then the ORA-600 will prevent the transaction
rollback from completing.

There are 2 main options:
        a. Recover the file containing the block causing the ORA-600
        b. Drop the object containing the block causing the ORA-600
The steps for these 2 options are described in "Error 376 encountered …"
and so are not repeated here .

Identifying the file requires you to look at the trace for the ORA-600
and hope that it indicates the problem block. How to do this depends
on the ORA-600 – see any reference note relating to the exact ORA-600
you are seeing.

The object is known from the YYYY in the error message.

Go to "Error 376 encountered … recovering (A,B) on object YYYY"

~~~~~~~~~~~~~~
Error XXXX encountered while recovering transaction (A, B).
~~~~~~~~~~~~~~
Note this error has no "on object …." clause in the error.
This is reported to the alert log when error XXXX is encountered on
a rollback segment block.

XXXX is the ORA-XXXX error encountered
(A, B) shows the rollback segment id and slot number (USN , SLOT) of the
transaction being recovered.

As we are recovering the transaction this is a block that we do need to
see:

If the error is 1578 go to "ORA-1578 on a REQUIRED Rollback Segment Block"

If the error is 600 then one can assume there is a corruption on a
required rollback segment block. Identifying the exact file, block
and problem requires you to look at the trace for the ORA-600 – see any
reference note relating to the exact ORA-600 you are seeing and then
go to "ORA-1578 on a REQUIRED Rollback Segment Block".

If the error is 376 then a file containing part of the rollback segment
is offline. Online the named file – there should be an addition error
similar to this in the alert log:
        ORA-00376: file 23 cannot be read at this time
        ORA-01110: data file 8: ‘/tmp/RPrbs2.dbf’

If the named file cannot be restored / recovered go to "Last Options"

~~~~~~~~~~~~~~
ORA-1578 on a Rollback Segment Header
~~~~~~~~~~~~~~
If there is an ORA-1578 on the rollback segment header Oracle cannot
know if there are any active transactions in that rollback segment.
What to do depends on whether we really do need the rollback segment
header. This scenario is the same as ORA-1578 on a rollback segment
block except that if segment header is needed there are more possible
side effects from the corruption.

If you are in ARCHIVELOG mode with a good backup then the best option
is to offline, restore and then recover the file and bring it back online.
This is only possible if the database is in ARCHIVELOG mode.
eg:         ALTER DATABASE DATAFILE ‘name_of_file’ OFFLINE;
                Save the current copy of the file
                Restore it at OS level from a good backup,
                RECOVER DATAFILE ‘name_of_file’;
                ALTER DATABASE DATAFILE ‘name_of_file’ ONLINE;

If the recovery has produced a good file then the rollback segment can
be recovered. Go to "Recovering Rollback Segments"

If the recovery is not possible (Eg: NOARCHIVELOG mode) or the same
ORA-1578 follow the steps in "ORA-1578 on a Rollback Segment Block" below.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-1578 on a Rollback Segment Block
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If an ORA-1578 occurs on a rollback segment block there are 2 main
scenarios:
a. The corruption is in an undo chain that needs to be rolled back
         (ie: the block is needed)
or
b. The corruption is in an undo block which is only being used
         for consistent read and is not needed to roll out a transaction.

It is important to determine which of these applies as one can be tackled
by dropping the rollback segment but the other requires the ORA-1578 to
be resolved. In the first instance attempt to recover the file as this
is always the best option.
eg:         ALTER DATABASE DATAFILE ‘name_of_file’ OFFLINE;
                Save the current copy of the file
                Restore it at OS level from a good backup,
                RECOVER DATAFILE ‘name_of_file’;
                ALTER DATABASE DATAFILE ‘name_of_file’ ONLINE;

If recovery of the file is not possible then:

1. Note the File and Block where the ORA-1578 occurs.
WARNING: On Oracle8 you need the file number from the accompanying
ORA-1110 error.

2. Determine which rollback segment contains the corrupt block:

SELECT segment_type, segment_name
FROM dba_extents
         WHERE file_id=<file_num>
AND <block_num> BETWEEN block_id and block_id+blocks-1
        ;

3. Find the SEGMENT_ID (USN) and STATUS of this rollback segment:

SELECT segment_id, status
FROM dba_rollback_segs
         WHERE segment_name='<rbsname>’
        ;

4. If the rollback segment is OFFLINE then it can probably be dropped as
it cannot contain any active transactions.
Go to "Dropping a Rollback Segment"

5. If the rollback segment is ONLINE then go to "Dropping a Rollback Segment"
NOTE: You MUST read the notes in that section before dropping the
rollback segment.

6. If the rollback segment is any other status you need to monitor
both the alert log for errors and check the rollback segment header for
active transactions.

Go to "Checking for active transactions" noting that:

For ACTIVE / NONE transactions try to get the users to commit
(as rolling back may require use of the corrupt block)

For in-doubt transactions it may be best to force commit them
(as rolling back may require use of the corrupt block)

For ACTIVE / DEAD transactions we have to wait to see if the
rollback of these needs the corrupt block.

If the rollout of a transaction hits a problem you will see an error in
the alert log. See the relevant section for the error in the alert log.
This will probably be
"Error 1578 encountered while recovering transaction (4, 1)."
as described next.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-1578 on a REQUIRED Rollback Segment Block
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For this scenario you typically will have an error of the form:

"ORACLE Instance XXXX (pid = 6) –
Error 1578 encountered while recovering transaction (4, 1)."

in the alert log. Note the lack of "on object …" in this message.
Basically this shows that a block in a rollback segment which is
required to roll out a transaction has been corrupted:

1. Note the File and Block where the ORA-1578 occurs.
WARNING: On Oracle8 you need the file number from the accompanying
ORA-1110 error.

2. Offline, restore and then recover the file and bring it back online.
This is only possible if the database is in ARCHIVELOG mode.

eg:         ALTER DATABASE DATAFILE ‘name_of_file’ OFFLINE;
                save the current copy of the file
                Restore it at OS level from a good backup,
                RECOVER DATAFILE ‘name_of_file’;
                ALTER DATABASE DATAFILE ‘name_of_file’ ONLINE;

If the recovery has produced a good file then the rollback segment
can be recovered.
Go to "Recovering Rollback Segments"

If the file cannot be restored / recovered go to "Last Options"

What are _OFFLINE_ROLLBACK_SEGMENTS
_OFFLINE_ROLLBACK_SEGMENTS is a very dangerous init.ora parameter
which can allow you to cause logical database corruption.
It is rarely required in Oracle7.3 onwards due to deferred transaction
recovery. However, it is occasionally useful to stop SMON from
recovering a rollback segment whilst you perform investigative selects.
When used it is set to a list of one or more rollback segments.

eg: If SMON keeps hitting a serious problem during transaction recovery
and crashes bringing down the database it may be difficult to
obtain all the information required to advise on the best course
of action. _OFFLINE_ROLLBACK_SEGMENTS can be useful in this case
to stop SMON from performing transaction recovery for specific
rollback segments.
When used, rollback segments listed in _OFFLINE_ROLLBACK_SEGMENTS are not
subject to all the the normal safeguards. In particular there is no check
for active transactions before dropping the rollback segment.

If you drop a rollback segment which contains active transactions then
you will have logical corruption. This corruption may be in the data
dictionary.

*********************************************************************
ORACLE will not support a database that has had a rollback segment
dropped while it was listed in _OFFLINE_ROLLBACK_SEGMENTS.
*********************************************************************

In many cases in Oracle7.3 onwards it is possible to achieve a similar
effect to _OFFLINE of a rollback segment by offlining the file containing
the rollback segment. This is only sensible if the database is in
ARCHIVELOG mode – if you are not in ARCHIVELOG mode you would cannot
cleanly offline the file . This is better than using _OFFLINE as Oracle
will NOT let you drop the rollback segment until you have addressed
any outstanding transactions.

What are _CORRUPTED_ROLLBACK_SEGMENTS
_CORRUPTED_ROLLBACK_SEGMENTS is an even more dangerous parameter
than _OFFLINE_ROLLBACK_SEGMENTS. It basically prevents access to the
listed rollback segments headers and assumes all transactions in them are
committed.

This can very easily cause logical database corruption.

Opening any database with any rollback segment listed in this
parameter instantly makes the database unsupported. This should
only EVER be used after a full backup has been taken as as part of
a last attempt to salvage data from a database.

*********************************************************************
ORACLE will not support a database that has used the
_CORRUPTED_ROLLBACK_SEGMENTS parameter.
*********************************************************************

Differences between these parameters
There are differences between these two parameters. If the rollback
segment is listed in the _offline parameter list, the transaction
table is still read accessible. This is important for delayed block
cleanout. If a select statement reads a data block with an open
ITL which points to the transaction table of the _offline rollback segment,
the table is still checked.
If the transaction is committed, delayed block clean out occurs.
If the transaction is uncommitted, a consistent read view of the
block is created. If an update of this block is required it will
never complete.
If the rollback segment is listed in the _corrupted parameter list,
the transaction table is not read accessible. All transactions are
assumed COMMITTED and delayed block cleanout will occur but there is
no consistent read view of uncommitted transactions (logical corruption).

List of Useful Events
These events may be useful to help trace unusual scenarios:

<>         show skipped undo
<>         show recovery actions
<>         show before / after TX table information if > 1
<>         trace transaction aborts
<>         show UBAs as applied

List of Common ORA-600’s
These are some common ORA-600 errors:

<>        Block is not an UNDO block
<>        XID on UNDO block does not match the XID we are
<>        XID on UNDO block does not match the XID we are
<>        undo block not undo type

本文固定链接: http://www.htz.pw/2014/06/14/oracle-rollback-segments%e4%b9%8b%e4%b8%80.html | 认真就输

该日志由 huangtingzhong 于2014年06月14日发表在 MOS 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: oracle rollback segments之一 | 认真就输
关键字: ,