The purpose of this article is to get acquianted with some of the recovery scenarios , this topic is useful for the folks who is having less experience and looking for good DBA exposure and preparing for interview .
I am trying to jotting down both RMAN and normal database technique :-
1.Complete Closed database Recovery. System tablespace is missing In this case complete recovery is performed, only the system tablespace is missing , so the database can be opened without resetting the redologs.
- rman target/
- startup nomount;
- restore database ;
- recover database ;
- alter database open;
If the system tablespace is missing or corrupted the database can not be started up so a complete closed database recovery must be performed.
Pre – requisites: A closed or open database backup and archive logs.
a. Use OS command to restore the missing or corrupted system datafile to its original location , i.e. :
cp –p /fh01/backup/uman/system01.dbf /fh02/oradata/system01.dbf
b. startup mount;
c. recover datafile 1 ;
d. 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 remains open.Pre – requisites: A closed or open database backup and archive logs.a . cp –p /fh01/backup/uman/user01.dbf /fh02/oradata/user01.dbfb. alter tablespace <tablespace_name> offline immediate ;c. recover tablespace <tablespace_name> ;
d. alter tablespace <tablespace_name> online ;
a. rman target /
b. sql ‘alter tablespace <tablespace_name> offline immediate ;
c. restore datafile 3;
d. recover datafile 3;
e. sql ‘ alter tablespace <tablespace_name> online’;
3. Complete Open Database Recovery .(when database is initially closed).Non system tablespace is missing .
A user datafile is missing when trying to startup the database. The datafile can be turned offline and the database started up. Restore and recover are performed using Rman and without Rman .After recovery is preformed the datafile can be turned online again .
a. sqlplus “ as/ sysdba”
b. startup mount
c. alter database datafile ‘<datafile_name>’ offline ;
d. alter database open ;
e. exit ;
f. rman target /
g. restore datafile ‘<datafile_name>’;
h. recover datafile ‘<datafile_name>’;
i. sql ‘alter tablespace <tablespace_name> online’;
If a non system datafile that was not backed up since last backup is missing, recovery can be perform if all archived logs since the creation of the missing datafile exist. Since database is up you can check the tablespace .
If a non system tablespace is missing or corrupted and the database is crashed ,recovery can be performed after the database is open .
Pre requisites: – A closed or open database backup and archived logs . a. startup ; (You ill get Ora – 1157 ora – 1110 and the name of the missing datafile , the database ill remain mounted )
b. Use OS commands to restore the missing or corrupted datafile to its original location i.e
cp –p /fh01/backup/uman/user01.dbf /fh02/oradata/user01.dbf
c. Alter database datafile3 offline; (Tablespace cannot be used because database is not open )
d. Alter database open ;
e. Recover datafile 3;
f. Alter tabelspace <tablespace_name> online;
4. Recovery of missing datafile that has no backup (database is open)If a non system datafile that was not backed up since last backup is missing, recovery can be perform if all archived logs since the creation of the missing datafile exist.
Pre requisites : All relevant archived logs.
a. Alter tablespace <tablespace_name> offline immediate ;
b. Alter tablespace create datafile ‘/fh02/oradata/newdata.dbf’ ;
c. Recover tablespace <tablespace_name> ;
d. Alter tablespace <tablespace_name> online ;
If the datafile command needs to be executed to place the datafile on a location different than the original use :
Alter database create datafile ‘/fh01/backup/uman/newdata.dbf’ as ‘‘/fh02/oradata/rajat/newdata.dbf’ ;
1. sqlplus “/as sysdba “
2. Alter tablespace <tablespace_name> offline immediate ;
3. Alter tablespace create datafile ‘/fh01/backup/uman/newdata.dbf’ ;
4. exit
5. rman target /
6. Recover tablespace <tablespace_name> ;
7. Alter tablespace <tablespace_name> online ;
If the datafile command needs to be executed to place the datafile on a location different than the original use :
Alter database create datafile ‘/fh01/backup/uman/newdata.dbf’ as ‘‘/fh02/oradata/rajat/newdata.dbf’ ;
5.Restore and Recovery of a datafile to a different location , Database is up.
If a non – system datafile is missing and its original location is not available , restore can be made to different location and recovery performed.
Pre Requisites: – All relevant archived logs , complete cold or hot backup.
a. Use OS commands to restore the missing or corrupted datafile to its original location i.e
cp –p /fh01/backup/uman/user01.dbf /fh02/oradata/user01.dbf
b. alter tablespace <tablespace_name> offline immediate ;
c. alter tablespace <tablespace_name> rename datafile /fh01/oradata/user01.dbf’ to ‘ /fh02/oradata/rajat/user01.dbf’
d. rman target /
e. recover tablespace <tablespace_name>
f. sql ‘ alter tablespace <tablespace_name> online ‘ ;
If a non – system datafile is missing and its original location is not available , restore can be made to different location and recovery performed.
Pre Requisites: – All relevant archived logs , complete cold or hot backup.
a. Use OS commands to restore the missing or corrupted datafile to its original location i.e
cp –p /fh01/backup/uman/user01.dbf /fh02/oradata/user01.dbf
b. alter tablespace <tablespace_name> offline immediate ;
c. alter tablespace <tablespace_name> rename datafile /fh01/oradata/user01.dbf’ to ‘ /fh02/oradata/rajat/user01.dbf’
d. recover tablespace <tablespace_name>
e. alter tablespace <tablespace_name> online
6. Control file recovery
Always multiplex your control files. Control files are missing , database crash.
Pre Requisites: A backup of your controlfile , and all relevant archived log files .
a. startup;(You get ora – 205 , missing control file, instance start but database is not mounted )
b. Use OS command to copy the missing controlfile to its original location
cp –p /fh01/backup/uman/control101.dbf /fh02/oradata/control101.dbf
c. alter database mount ;
d. recover automatic database using backup controlfile ;
e. alter database open resetlogs ;
f. make a new complete backup , as the database is open in a new incarnation and previous archived log are not relevant.
Always multiplex your control files. Control files are missing , database crash.
Pre Requisites: A backup of your controlfile , and all relevant archived log files.When
Using Rman always set configuration parameter.
Autobackup of controlfile to ON . You will need the dbid to restore the controlfile.
Get it from the name of the backup controlfile.
It is the number following the ‘c-‘ at the start of the same.
a. rman target/
b. set dbid <dbid>
c. startup nomount ;
d. restore controlfile from autobackup ;
e. alter database mount ;
f. recover database;
g. alter database open resetlogs ;
h. 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 necessary when the database crash and needs to be recovered, and in recovery process you find that an archived log is missing .In this case recovery can only be made until the sequence before the one that is missing.
Another scenario for incomplete recovery occurs when an important object was dropped or incorrect data was committed on it.
In this case recovery needs to be performed until before the object was dropped.
Pre requisites: A full closed or open database backup and archived logs , the time or sequence that the ‘until’ recovery needs to be performed .
a. If the database is open , shutdown it to perform full restore.
b. Rman target \
c. Startup mount ;
d. Restore database ;
e. Recover database until sequence 8 thread 1 ;(You must pass the thread , if a single instance ill be always be 1 )
a. If the database is open , shutdown abort
b. Use OS commands to restore all the datafiles to its original locations :
Cp –p /user/backup/*.dbf /u01/oradata/
c. startup mount ;
d. recover automatic database until time ‘2007-01-01:10:10:10’ ;
e. alter database open resetlogs ;
f. 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 ;
Recover automatic database until sequence 120 thread 1 ;or
Recover database until cancel ;
