COMMON Oracle Database Recoveries...

Summary Steps

COMMON Oracle Database Recoveries:

1.        Loss of  data file(s) of SYSTEM tablespace

Solution:

SQL> SHUTDOWN IMMEDIATE;

SQL> host cp backupfile file  

           (Restore your data backup files from backup to related loss system’s datafiles)

SQL> CONNECT / AS SYSDBA

SQL> STARTUP MOUNT

SQL> RECOVER DATABASE;

SQL> ALTER DATABASE OPEN;

2.        Loss of data file(s) of Non-System tablespace.

Solution:

>Don’t need to shutdown database.

SQL> ALTER TABLESPACE … OFFLINE;

SQL> host cp backupfile file  

           (Restore your data backup files from backup to related loss system’s datafiles)

SQL> RECOVER TABLESPACE your_tablespace_name;

SQL> ALTER TABLESPACE your_tablespace_name ONLINE;

Or recover by using datafile(s).

SQL> ALTER DATAFILE  ‘/u01/…’ OFFLINE;

SQL> host cp backupfile file  

           (Restore your data backup files from backup to related loss system’s datafiles)

SQL> RECOVER DATAFILE ‘/u01…’;

SQL> ALTER DATAFIL ‘/u01/…’ ONLINE;

Or shutdown database.

SQL> SHUTDOWN IMMDIATE;

SQL>  (Restore your data backup files from backup to related loss system’s datafiles)

SQL> STARTUP MOUNT;

SQL> RECOVER DATABASE;

SQL> ALTER DATABASE OPEN;

3.        Loss of data file(s) of rollback OR undo segments tablespace.

A datafile in a rollback segment tablespace has been lost or damaged to a point that 

Oracle cannot recognize it anymore.

If you try to startup the database will result in ORA-1157, ORA-1110, and possibly 

an operating system level error such as ORA-7360. Trying to shut down the database 

in normal or immediate mode will result in ORA-1116, ORA-1110, and possibly

an operating system level error such as ORA-7368.  

PLEASE NOTE that before using _corrupted_rollback_segments parameter, you should 

explore all other options. This will require extra caution. Call Oracle Customer Support 

if you have any questions or need any assistance. 

The Database is down: 

Assuming that your database was cleanly shutdown (check your alert.log)

SQL> SHUTDOWN IMMEDIATE/NORMAL

SQL> STARTUP RESTRICT MOUNT

SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' OFFLINE DROP; 

SQL> ALTER DATABASE OPEN

SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;

SQL> -- Recreate the rollback tablespace with all its rollback segments. 

   Remember to bring the rollbacks online after you create them. 

Assuming that you cann't open your database and you get the following error 

messages: ORA-604, ORA-376, and ORA-1110.

-- Comment out the ROLLBACK_SEGMENTS parameter and add the following line:

_corrupted_rollback_segments = ( <rollback1>,...., <rollbackN> ) 

with exact the same rollbacks.

WARNING: Use this parameter ONLY IN THIS SPECIFIC SCENARIO or as

instructed by Oracle Customer Support. 

SQL> STARTUP RESTRICT

SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;

SQL> Recreate the rollback tablespace with all its rollback segments. 

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- put back your ROLLBACK_SEGMENTS parameter in the init.ora file.

-- remove _CORRUPTED_ROLLBACK_SEGMENTS PARAMETER

SQL> -- Rebuild the database.

Assuming that database was not cleanly shutdown:

-- Restore the lost file from a backup.

SQL> STARTUP MOUNT

SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;

SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' ONLINE;

     (if datafile is offline)

SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE# 

        FROM V$LOG V1, V$LOGFILE V2 

        WHERE V1.GROUP# = V2.GROUP# ; 

SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE; 

If NOARCHIVELOG Mode and the CHANGE# is GREATER than the minimum FIRST_CHANGE#

of your logs, the datafile can be recovered.

SQL> RECOVER DATAFILE '<full_path_file_name>';

SQL> ALTER DATABASE OPEN;

Assuming that the database is up and it is in ARCHIVELOG mode:

SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' OFFLINE;

SQL> -- Restore it from your backup.

SQL> RECOVER DATAFILE '<full_path_file_name>';

SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' ONLINE;

-OR-

SQL> ALTER SYSTEM KILL SESSION ' , ' -- The transaction in RBS involved.

SQL> ALTER ROLLBACK SEGMENT <rollback_segment> OFFLINE;

SQL> DROP ROLLBACK SEGMENT <rollback_segment>;

SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;   

SQL> CREATE TABLESPACE rbs ...;

SQL> CREATE ROLLBACK SEGMENT rbs01 ...;

SQL> ALTER ROLLBACK SEGMENT ... ONLINE;

In this case to access to data you will get a message like this:

ORA-00376: file 20 connot be read at this time

File 2 happens to be one of the data files that belongs to the rollback segment tablespace.

Solution:

SQL> select segment_name, status from dba_rollback_segs;

           (If status are in “NEED RECOVERY”)

SQL> SHOW PARAMETER undo

SQL> CREATE UNDO TABLESPACE  myundotbs DATAFILE ‘/u01/…’ SIZE 50M;

SQL> ALTER SYSTEM SET undo_tablespace=’myundotbs’;

SQL> DROP TABLESPACE undotbs;

           (Remove all related OS datafiles)

4.        Loss of Rollback segments while there were transaction activities;

For example:

SQL> CREATE TABLE mytest (col1 varchar2(20));

SQL> BEGIN

                        FOR this IN 1..100 LOOP

                                        INSERT INTO mytest VALUSE (this);

                        END LOOP;

            END;

            /

SQL> /* Notice that we didn’t commit any of above transaction */

SQL> /* shutdown abort or any crash on rollback datafiles will cause

The following error messages if we start the database. */

ORA-01157: cannot identify/lock data file 20 – see DBWR trace file

ORA-01110: data file 20: ‘/u01/…’

Solution:

If you shutdown the server then you will get a message like this:

ORA-01157: cannot identify/lock data file 8 - see DBWR trace file

ORA-01110: data file 8: 'C:\MYUNDOTBS_01.DBF'

SQL> STARTUP MOUNT

SQL> ALTER DATABASE DATAFILE 'C:\MYUNDOTBS_01.DBF' OFFLINE;

SQL> ALTER DATABASE OPEN;

SQL> /* remember you can not access to that table with transaction */

SQL> SELECT segment_name, status FROM dba_rollback_segs;

           (You will see that the segments are NEEDS RECOVERY)

SQL> /* replace your backup file and recover the tablespace */

SQL> RECOVER TABLESPACE your_tablespace_name;

SQL> ALTER TABLESPACE your_tablespace_name ONLINE;

SQL> ALTER ROLLBACK SEGMENT … ONLINE;

           (Repeat this for all of your rollback segments)

If you are not shutdown:

SQL> ALTER DATABASE DATAFILE 'C:\MYUNDOTBS_01.DBF' OFFLINE;

SQL> /* remember you can not access to that table with transaction */

SQL> SELECT tablespace_name, segment_name, status FROM dba_rollback_segs;

           (You will see that the segments are NEEDS RECOVERY)

SQL> /* replace your backup file and recover the tablespace */

SQL> RECOVER TABLESPACE your_tablespace_name;

SQL> ALTER TABLESPACE your_tablespace_name ONLINE;

SQL> ALTER ROLLBACK SEGMENT … ONLINE;

           (Repeat this for all of your rollback segments)

5.        Loss of an UN-archived Online log file.

Solution:

SQL> /* Your database is done. */

SQL> /* Replace all your datafiles and online redo logfile, but not controlfiles. */

SQL> host cp … …

SQL> STARTUP MOUNT

SQL> RECOVER DATABASE UNTIL CANCEL;  --  UNTIL TIME …

SQL> ALTER DATABASE OPEN RESETLOGS;

           /* Since you did incomplete recovery, you must use ‘RESETLOGS’ option.

SQL> SHUTDOWN IMMEDIATE

           /* Do your cold backup immediately */

Loss of your Online Redo-log files.

Solution:

SQL> /* If you loss your online redo-log files, your instance will hung or crash.

                If not, try to shutdown abort. Restore all files from your cold backup

                with exception of your control files. */

SQL> STARTUP MOUNT

SQL> /* Now do an incomplete recovery recover using CANCEL or TIME option */

SQL> RECOVER DATABASE UNTIL CANCEL;

          /* you should apply up to your last archive and then cancel the recovery process */

SQL> ALTER DATABASE OPEN RESETLOGS;

           /* Since you did incomplete recovery, you must use ‘RESETLOGS’ option.

SQL> SHUTDOWN IMMEDIATE

           /* Do your cold backup immediately */

6.        Loss of your control file.

Solution:

SQL> /* Assuming you have already backup controlfile to trace */

SQL> ALTER DATABASE BACKUP CONTROLFILE TO trace;

SQL> /* modify and run your trace file and your control file is up to date */

SQL> /* if not, then copy a old control file */

SQL> STARTUP MOUNT;

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

SQL> ALTER DATABASE OPEN;