backup and recovery (2) -- recovery from rollback segment corruption when in noarchive mode

1) Backup the database while the database is closed.

THE INSTRUCTIONS HERE ARE DESTRUCTIVE. YOU ARE STRONGLY ADVISED TO BACKUP THE DATABASE BEFORE PROCEEDING. IF YOU DO NOT DO THIS YOU MAY LOSE THE CHANCE TO TRY OTHER OPTIONS.


2) If your datafiles are from different points in time, it is best to try to use system tablespace datafiles at a similar timestamp to the OLDEST files you have online in the database. This reduces the chance that you will get problems during the bootstrap phase of opening the database.

3) Edit your *init.ora file to change undo_management and add one parameter.

* Change UNDO_MANAGEMENT=AUTO to UNDO_MANAGEMENT=MANUAL
* Remove or comment out UNDO_TABLESPACE and UNDO_RETENTION.

* Add

_OFFLINE_ROLLBACK_SEGMENTS =(comma separated list of Automatic Undo segments)

Example:

_OFFLINE_ROLLBACK_SEGMENTS = (_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$)

(There are 25 system managed undo segments in OAICM)

Note, sometimes the alert log will tell you what Automatic Undo segments are in use. Search the alert log for SYSS. If the alert log does not contain that information then use _SYSSMU1$ through _SYSSMU10$ as shown in the example above.

In UNIX you can issue this command to get the undo segment names:

$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u

From the output of the strings command above, add a $ to end of each _SYSSMU undo segment name.

* If you only have a spfile available, you can from the closed, nomount or the mount stage create an init.ora file as follows:

SQL> CREATE PFILE FROM SPFILE;

Do NOT edit the SPFILE.

4) Invoke SQL*Plus, startup mount, check that correct init.ora was used and all datafiles are in the status of online or system.

$ sqlplus "/as sysdba"

SQL> startup mount pfile = (full path / file name to init.ora)

Example:
SQL> startup mount pfile=/db00/db04/oracle/oaicmdb/9.2.0/dbs/initOAICM.ora.forrecovery

Confirm that the hidden parameters from step 3 were used:

SQL> show parameters offline

You should see the hidden parameter listed. If not, the wrong init.ora may have been modified. Do not continue until "show parameters corrupt" shows the hidden parameter.

SQL> show parameters undo

You should see undo management set to manual. If not, the wrong init.ora may have been modified. Do not continue until "show parameters undo" shows undo management as manual.

5) Alter database datafile 'undo datafile' offline drop;

example: alter database datafile ‘/db00/db04/oracle/oaicmdata/undo01.dbf’ offline drop;

6) alter database open;

6) drop all rollback segment with status need recovery:

select owner,segment_name,status
from dba_rollback_segs;

drop rollback segment "_SYSSMU1$"; -- 10 system undo segments were dropped.


7) drop tablespace including contents and datafiles;

example: drop tablespace apps_undots1 including contents and datafiles;

8) create undo tablespace apps_undots1 datafile ‘/db00/db04/oracle/oaicmdata/undo01.dbf’ size 1000m resuse autoextend on next 100m maxsize 8000m.

9) shutdown the database

10) change the following parameters in the init.ora :
UNDO_MANAGEMENT=AUTO
#_OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, ...etc)

11) startup



12) best practice:
Put a copy of controlfile and online redo logs on local (internal) disk to prevent from database corruption due to temporary storage problems.