Oracle Recovery Cases – User Managed Recovery

User Managed Recovery Scenarios And Configuration

1. Complete Closed Database Recovery. System tablespace is missing

2. Complete Open Database Recovery. Non system tablespace is missing

3. Complete Open Database Recovery (when the database is initially closed). Non system tablespace is missing

4. Recovery of a Missing Datafile that has no backups.

5. Restore and Recovery of a Datafile to a different location.

6. Control File Recovery

7. Incomplete Recovery, Until Time/Sequence/Cancel

1. Complete Closed Database Recovery. System tablespace is missing

User managed recovery scenarios do require that the database is in archive log mode, and that backups of all datafiles and control files are made with the tablespaces set to begin backup, if the database is open while the

copy is made. At the end of the copy of each tablespace it is necessaire to take it out of backup mode. Alternatively complete backups can be made with the database shutdown. Online redologs can

be optionally backed up.

Files to be copied:

select name from v$datafile;

select member from v$logfile; # optional

select name from v$controlfile;

go up

Complete Closed Database Recovery. System tablespace is missing

If the system tablespace is missing or corrupted the database cannot be started up

so a complete closed database recovery must be performed.

Pre requisites: A closed or open database backup and archived logs.

1. Use OS commands to restore the missing or corrupted system datafile to its

original location, ie:

cp -p /user/backup/uman/system01.dbf

/user/oradata/u01/dbtst/system01.dbf

2. startup mount;

3. recover datafile 1;

4. alter database open;

2. Complete Open Database Recovery. Non system tablespace is missing

If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open.

Pre requisites: A closed or open database backup and archived logs.

1. Use OS commands to restore the missing or corrupted datafile to its original location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf

2. alter tablespace <tablespace_name> offline immediate;

3. recover tablespace <tablespace_name>;

4. alter tablespace <tablespace_name> online;

3. Complete Open Database Recovery (when the database is initially closed).

Non system tablespace is missing

If a non system tablespace is missing or corrupted and the database crashed, recovery can be performed after the database is open.

Pre requisites: A closed or open database backup and archived logs.

1. startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)

2. Use OS commands to restore the missing or corrupted datafile to its original location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf

alter database datafile3 offline; (tablespace cannot be used because the database is not open)

3. alter database open;

4. recover datafile 3;

5. alter tablespace <tablespace_name> online;

4. Recovery of a Missing Datafile that has no backups (database is open).

If a non system datafile that was not backed up since the last backup is missing,

recovery can be performed if all archived logs since the creation

of the missing datafile exist.

Pre requisites: All relevant archived logs.

1. alter tablespace <tablespace_name> offline immediate;

2. alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf’;

3. recover tablespace <tablespace_name>;

4. alter tablespace <tablespace_name> online;

If the create datafile command needs to be executed to place the datafile on a location different than the original use:

alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf’ as ‘/user/oradata/u02/dbtst/newdata01.dbf’

5. Restore and Recovery of a Datafile to a different location.

If a non system datafile is missing and its original location not available, restore

can be made to a different location and recovery performed.

Pre requisites: All relevant archived logs.

1. Use OS commands to restore the missing or corrupted datafile to the new location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf

2. alter tablespace <tablespace_name> offline immediate;

3. alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/dbtst/user01.dbf’ to ‘/user/oradata/u02/dbtst/user01.dbf’;

4. recover tablespace <tablespace_name>;

5. alter tablespace <tablespace_name> online;

6. Control File Recovery

Always multiplex your controlfiles. Controlfiles are missing, database crash.

Pre requisites: A backup of your controlfile and all relevant archived logs.

startup; (you get ora-205, missing controlfile, instance start but database is not

mounted)

1. Use OS commands to restore the missing controlfile to its original location:

cp -p /user/backup/uman/control01.dbf /user/oradata/u01/dbtst/control01.dbf

cp -p /user/backup/uman/control02.dbf /user/oradata/u01/dbtst/control02.dbf

2. alter database mount;

3. recover automatic database using backup controlfile;

4. alter database open resetlogs;

make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

7. Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when an archived log is missing, so recovery can only be made until the previous sequence, or when an important object was dropped, and recovery needs to be made until

before the object was dropped.

Pre requisites: A closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.

1. If the database is open, shutdown abort

Use OS commands to restore all datafiles to its original locations:

cp -p /user/backup/uman/u01/*.dbf /user/oradata/u01/dbtst/

cp -p /user/backup/uman/u02/*.dbf /user/oradata/u01/dbtst/

cp -p /user/backup/uman/u03/*.dbf /user/oradata/u01/dbtst/

cp -p /user/backup/uman/u04/*.dbf /user/oradata/u01/dbtst/

etc…

2. startup mount;

3. recover automatic database until time ‘2004-03-31:14:40:45’;

4. alter database open resetlogs;

make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Alternatively you may use instead of until time, until sequence or until cancel:

SQL> recover automatic database until sequence 120 thread 1; OR

SQL> recover database until cancel;

5. SQL> alter database open;