Recovery Scenarios Part-2

Part 2 for the recovery scenarios

B) Lost control File or Media Failure:
      (Disk lost with all the files w/o SYSTEM tablespace)

SVRMGRL> shutdown abort;
SVRMGRL> STARTUP NOMOUNT;

RMAN>
run {
allocate channel c1 type disk;
restore controlfile to '/usr1/ora817/test/dbs/control01.ctl';
replicate controlfile from '/usr1/ora817/test/dbs/control01.ctl';
restore database;
sql "alter database mount";
recover database;
sql "alter database open resetlogs";
release channel c1;
}
RMAN> Reset database

(C) Table recovery: (Incomplete recovery)

This
happens when some one drops table by mistake, and by the time he/she
informs the DBA, other people are still working on the database. So the
DBA has to recover the database until time when the table was dropped.
That causes other people to re-work from that point onwards, and hence
it is an "Incomplete recovery"

When something like this happens, try to find out the exact time of accident.

SVRMGRL> shutdown immediate;
SVRMGRL> STARTUP MOUNT;

RMAN>
run {                                 
allocate channel c1 type disk;           
set until time "to_date('2004-01-16:13:17:00','YYYY-MM-DD:HH24:MI:SS')";  
restore database;                        
recover database;                        
sql "alter database open resetlogs";     
}  
RMAN> Reset database
 
 

(D) If both the ACTIVE redolog files are gone:

SVRMGRL> startup nomount;

RMAN>
run {
allocate channel c1 type disk;
set until logseq=7 thread=1;
restore controlfile to '/usr1/ora817/test/dbs/control01.ctl';
replicate controlfile from '/usr1/ora817/test/dbs/control01.ctl';
restore database;
sql "alter database mount";
recover database;
sql "alter database open resetlogs";
release channel c1;
}

If for some reason this attempt fails,

SVRMGRL> alter database mount;  (equivalent of startup mount;)
SVRMGRL> alter database drop logfile group 2;  (optional)
SVRMGRL> recover database using backup controlfile until cancel;  

(Recover to the point till you have good archivelogs, and then Type CANCEL at the prompt)
 
SVRMGRL> alter database open resetlogs;

RMAN> Reset database

Run the following script to find out the status of backup process:

alter session set NLS_DATE_FORMAT = 'MM/DD/YY (HH:MIAM)';

col start_time format a20 heading "Backup of this|Database started at"
col a format 999.99 heading "% Complete|so far"
col b format a20 heading "Time taken so far|for this backup"

select start_time,round(sofar/totalwork*100,2) a ,
mod(floor((sysdate-start_time)*24),24)||'
Hrs and
'||floor(((mod(round((sysdate-start_time)*24,2),24))-(mod(floor((sysdate-start_time)*24),24)))*60)||'
Mins' as b
from v$session_longops
 where substr(opname,1,4)='RMAN'
 and (round(sofar/totalwork*100,2))<100
 and (round(sofar/totalwork*100,2))>0
 and totalwork !=0;
-- -----------------------------------------

Comments:

For scenario C, if you are using Enterprise Edition, you can issue flashback table mytable to before drop; In standard edition, you can recreate the table, then issue insert into mytable (select * from mytable as of timestamp (systimestamp-1/24); Both are a lot easier and less disruptive than PITR.

Posted by Alistair Wall on June 10, 2007 at 09:27 PM PDT #

Yeap you are correct , but the aim of this blog entry was to use RMAN for recovery , as i specified in 1st entry "I will be covering some recovery scenarios via RMAN in coming posts" ......but i totally agree that above way is easier :) -pankaj

Posted by Pankaj Chandiramani on June 10, 2007 at 10:08 PM PDT #

I agree with Alistair, but his suggestion is valid only for Oracle10g database. In the example "C", Pankaj used SVRMGRL (Server Manager) which shows that he was using Oracle8i database or older. :-) Cheers!

Posted by Mihajlo Tekic on June 13, 2007 at 07:41 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

This blog is authored by Pankaj Chandiramani , Product Manager for Enterprise Manager . Main Focus of the blog will be on Enterprise Manager 12c with deep coverage of Database Life Cycle Management that includes Database Provisioning , Database Patching , Config , Compliance etc .

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today