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 ‘
SQL> ALTER DATABASE OPEN
SQL> DROP TABLESPACE
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 = (
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
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 ‘
(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 ‘
SQL> ALTER DATABASE OPEN;
Assuming that the database is up and it is in ARCHIVELOG mode:
SQL> ALTER DATABASE DATAFILE ‘
SQL> — Restore it from your backup.
SQL> RECOVER DATAFILE ‘
SQL> ALTER DATABASE DATAFILE ‘
-OR-
SQL> ALTER SYSTEM KILL SESSION ‘ , ‘ — The transaction in RBS involved.
SQL> ALTER ROLLBACK SEGMENT
SQL> DROP ROLLBACK SEGMENT
SQL> DROP TABLESPACE
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;
Listener Tracing DBMS_STATS commands to use for gathering statistics
Comments are currently closed.