RMAN Recovery Scenarios

1. Complete Database Restore (All datafiles lost)

db name = prod

cd /u01/prod

rm *.dbf

whenever we loose system data file we must shut down the db.

shut abort;

startup mount;
(as we have spfile and control file)

rman target / nocatalog

run{
restore database;
recover database;
sql 'alter database open';}


2.  System01.dbf file is lost

go to the db folder and delete this file

rm system01.dbf

sqlplus / as sysdba

it gives error as there is no system file

ps -ef|grep -i pmon
kill -9 pid

sqlplus / as sysdba

startup mount;

rman target /

run{

restore datafile 1;

recover datafile 1;

sql 'alter database open';}

3. When a non-system datafile is lost (users01.dbf)

rm users01.dbf

shut abort;

startup 

will throw an error.  

alter database datafile 4 offline;

select file#, name, status from v$datafile;

here status will be recover for our file

rman target /

run{
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';}


4. users01.dbf is lost or damaged but we dont know which file

generally its difficult to find out which file is damaged in real time. until unless a

user comes to a dba telling i cant access a particular tables data.

one way is to shut the database and start again so that we find the error regarding the

damaged file.

in mount stage we can restore and recover a non-system datafile without making it

offline.

remember mount stage means whole database offline.

when we shut and start the db it stucks at mount stage as missing users01.dbf file.

here we can repeat the same steps to restore and recover the file.

run{
restore datafile 4;
recover datafile 4;}

sqlplus / as sysdba

alter database open;

5. recovering a logical part like a tablespace

sqlplus / as sysdba

select tablespace_name, status from dba_tablespaces where tablespace_name='USERS';

select ts#, name from v$tablespace where name='USERS';

to see the dbf file in the above tablespace

select file_name,file_id,tablespace_name,status from dba_data_files
where tablespace_name='USERS';

select file#,ts#,status from v$datafile where ts#=4;

exit

cd /u01/prod

rm users01.dbf

if we have multiple dbf files also we can delete more or all

rm users*

sqlplus / as sysdba

identification of the issue

try to execute the same set of commands above

all will show as if normal

now execute a query which is using the users tablespace

try to create or drop a table on users tablespace, will throw an error.

here if we have multiple files we dont know which one got damaged. so we can restore and 

recover complete tablespace users.

alter tablespace users offline;

this will not work

shut abort;

startup

started, mounted then error

in mount stage we cant bring our tablespace offline.

rman target /

run{

restore tablespace users;

recover tablespace users;

sql 'alter database open';}

6. how to get back a datafile which has no backups

after creating this datafile we have never done a backup.

to test this scenario we need to create a new tablespace

create tablespace test_ts datafile '/u01/primdb/test_ts01.dbf' size 5m;

create table test tablespace test_ts as select * from scott.emp;

select count(1) from test;

until now we didnt do any backup for this tablespace and datafile

rm test_ts01.dbf

sqlplus / as sysdba

select count(1) from test;

select * from test;

alter system flush shared_pool;

select * from test;

select segment_name,tablespace_name from dba_segments where segment_name='TEST';

try to create another table in the same tablespace

create table test1 tablespace test_ts as select * from scott.emp;

throws an error

while giving error it shows the datafile number

and we dont have backup

shut abort;

startup mount;

rman target / catalog rman/rman@torman

run{
sql 'alter database datafile 5 offline';
restore datafile 5;
recover datafile 5;
sql 'alter database datafile 5 onilne';}

alter database open;

try creating a table on this tablespace for testing

drop table test;
drop table test1;

drop tablespace test_ts including contents and datafiles;

purge recyclebin;



7. We lost a users01.dbf file and we want to restore it to a different location

rm users01.dbf

sqlplus / as sysdba

create table scott.test tablespace users as select * from scott.emp;

throws an error

alter database datafile 4 offline;

create a folder where we want to restore this file

rman target /

run{
set new name for datafile 4 to '/u01/test/users01.dbf';
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';}

go to the new location and confirm weather our file has been restored or not.

sqlplus / as sysdba


select count(1) from scott.emp;

resetlogs scenarios

whenever we start our database with resetlogs it starts with a new incarnation

whenever we do this then the backups we took previously become invalid.


8. if we loose controlfile

sqlplus / as sysdba

desc v$controlfile

select name,status from v$controlfile;

exit

create an issue manually

delete the controlfiles

cd /u01/prod

rm *.ctl

ll

i dont have those files anymore

see the status of the database

ps -ef|grep -i pmon

its up and running

sqlplus / as sysdba

create table scott.test tablespace users as select * from scott.emp;

this will be done as there is no checkpoint happened here

alter system switch logfile;

/

/

this is also happening without any issue.

so i have to shut the db and try to bring it to mount stage, then only it will look for controlfile.

shut abort

exit

clear

sqlplus / as sysdba

startup

it throws an error after started

it cannot identify the controlfile

rman target / catalog rman/rman@torman

here before starting restoration we need to identify the dbid

RMAN> set dbid=123456;

restore controlfile;
restore controlfile from autobackup;

alter database mount;

alter database open;

throws an error for resetlogs or noresetlogs

alter database open noresetlogs;

it will not work

even recover database option will not work at rman

sequence will be reset to 1

alter database open resetlogs;

archive log list;

will show new sequence number

list incarnation;

exit

sqlplus / as sysdba

select name,user,open_mode from v$database;

we need to go out and come back into the rman session so that 
the new incarnation is registered into the catalog

to check the other scenarios we must restore the complete database



9.  restoring online redo log files

create an issue by deleting an online redo log file

we have some transactions recorded inside the online redo log files, which we loose. so its an incomplete recovery. we must start the db in resetlogs.

sqlplus / as sysdba

desc v$log

select group#,thread#,members,status from v$log;

let us also see log files

desc v$logfile

select group#,member from v$logfile;

delete the online redo log files while the db is up and running

cd /u01/prod

ls *.log

rm *.log

ll *.log
no files

sqlplus / as sysdba

execute the previous queries

also see database status

select name, user, open_mode from v$database;

it shows read write mode

alter system switch logfile;

/

/

it fails to switch, cancel the operation

here in this case we need to go for an incomplete recovery

shut abort

startup

throws an error after started

query to identify the sequence to where we need to bring the db back

select thread#,resetlogs_change#,archived,sequence#,
to_char(completion_time, 'YYYY-MM-DD HH24:MI:SS') COMPLETION_TIME
from v$archived_log
where archived = 'YES' and completion_time = (select max(completion_time) from
v$archived_log where archived = 'YES');

there is another way to find out the SCN from RMAN

go to rman

list backup summary;

list backup of database;

gives the details of files in backup, and also check point time and scn number

also we get backup set name

with this info we can find out upto which scn number we can restore

we can go for point in time recovery based on time, scn and sequence

run{
restore database until scn 123456;
recover database until scn 123456;}

run{
restore database until sequence 161;
recover database until sequence 161;}  this is called time based recovery

Note:
there is a query i need to understand which shows checkpoint number

run{
restore databae until time "to_date('25/03/2013 23:00:00','DD/MM/YYYY HH24:MI:SS')";
recover databae until time "to_date('25/03/2013 23:00:00','DD/MM/YYYY HH24:MI:SS')";
}




10. restoring archivelogs