Restore - Recover Some Practices ( part 1 )

Backup is one of the primary tasks of a dba.But how about restore and recover ?I know many sites that take regular backups and never test if they could restore,recover.In the post I created scenario some of which I have encountered.Because of many examples I will have a few posts about this subject.

===========SOLUTION 1
SUTIATION SUMMARY:
rman or sqlplus to recover ; one of controlfiles and some of datafile has old scn
SCENARIO CREATION
SQL> shutdown immediate;
#cp -R /oracle/oradata/SID2 /oracle/oradata/SID2_backup
SQL> startup;
SQL> alter system switch log file ; -- X5 times
SQL> shutdown abort;
#cp /oracle/oradata/SID2_backup/control01.ctl /oracle/oradata/SID2/control01.ctl ## controlfile01 has old scn
#cp /oracle/oradata/SID2_backup/system01.dbf /oracle/oradata/SID2/system01.dbf ## datafile01 has old scn
SQL> shutdown abort;
SCENARIO READY

SQL> startup mount;
ORACLE instance started.
ORA-00214: control file '/oracle/oradata/SID2/control02.ctl' version 1352
inconsistent with file '/oracle/oradata/SID2/control01.ctl' version 1327

cp /oracle/oradata/SID2/control02.ctl /oracle/oradata/SID2/control01.ctl
SQL> alter database mount;
Database altered.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'

HINT ..!
if you try recovering from sqlplus it does not know about archived logs..!!
Although both candidates can recover database rman is strongly recommended to use here ..!!

### ************************* SQLPLUS DOES NOT KNOW ABOUT ARCHIVED LOGS
SQL> recover datafile 1 ;
ORA-00279: change 1862553 generated at 04/17/2009 08:45:42 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_%u_.arc
ORA-00280: change 1862553 for thread 1 is in sequence #64
Specify log: {=suggested | filename | AUTO | CANCEL}
--RETURN PRESSED TO ACCEPT suggested archived logs..!
Log applied.
Media recovery complete.

SQL> alter database open ;
Database altered.


### ************************* RMAN KNOWS ABOUT ARCHIVED LOGS
RMAN> recover datafile 1 ;
Starting recover at 17-APR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=53 devtype=DISK
starting media recovery
archive log thread 1 sequence 64 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_4yj61c6n_.arc
archive log thread 1 sequence 65 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_65_4yj61d9g_.arc
archive log thread 1 sequence 66 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_66_4yj61g6m_.arc
archive log thread 1 sequence 67 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_67_4yj61gx8_.arc
archive log filename=/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_4yj61c6n_.arc thread=1 sequence=64
archive log filename=/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_65_4yj61d9g_.arc thread=1 sequence=65
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-APR-09


DB IS NOW READY TO OPEN
SQL> alter database open ;
Database altered.


===========SOLUTION 2
SUTIATION SUMMARY:
rman to recover ; old controlfile is used from cold backup..!
SCENARIO CREATION
SQL>shutdown immediate;
# copy all datafiles as filesystem cold backup

[oracle@gulcan oradata]# cp SID2_backup/control0* SID2/
cp: overwrite `SID2/control01.ctl'? y
cp: overwrite `SID2/control02.ctl'? y
cp: overwrite `SID2/control03.ctl'? y
SCENARIO READY

SQL> startup mount ;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2071256 bytes
Variable Size 117441832 bytes
Database Buffers 41943040 bytes
Redo Buffers 6316032 bytes
Database mounted.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/oracle/oradata/SID2/undotbs01.dbf'
ORA-01207: file is more recent than control file - old control file

### ****************************** RMAN is not using backup controlfile statement..
RMAN> recover database using backup controlfile until cancel;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "using": expecting one of: "archivelog, auxiliary, allow, check, delete, from, high, noredo, noparallel, parallel, ;, skip, tablespace, test, until, undo"
RMAN-01007: at line 1 column 18 file: standard input
RMAN>

### ****************************** SQLPLUS is using backupcontrolfile ..
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1862553 generated at 04/17/2009 08:45:42 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_%u_.arc
ORA-00280: change 1862553 for thread 1 is in sequence #64
Specify log: {=suggested | filename | AUTO | CANCEL}

all suggested recovery was done ..!!

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/oracle/oradata/SID2/undotbs01.dbf'

..... HINT ..!! when warning declared "RECOVER succeeded but OPEN RESETLOGS would get error below.."
it means we need more recovery but we have no archived log remaining unapplied.
so the redo data should be in redo log files which are the source of archived log files .!!
re-apply restore operation using redolog files as input files.
see member,groups..etc for redo log files.
SQL> select * from v$logfile;
/oracle/oradata/SID2/redo01.log
/oracle/oradata/SID2/redo02.log
/oracle/oradata/SID2/redo03.log

SQL> recover database using backup controlfile until cancel;
Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/oradata/SID2/redo01.log
Log applied.
Media recovery complete.

SQL> alter database open RESETLOGS;
Database altered.
SQL>