Restore - Recover Some Practices ( part 2 )

===========SOLUTION 3
SUTIATION SUMMARY:
old scn controlfiles + different incarnation in controlfile&datafile
SCENARIO CREATION
1-db is open;
2-rman is used for online backup, ## rman backup
3-shutdown database ;
4-after rman hot backup a file system cold backup is done. ## file system backup --> control files are used as catalog. so rman backup will be avaliable in following steps.See registering backups to catalog.!
5-start database ;
6-switch log file 5 times; ## different SCN
7-alter database backup controlfile to trace;
8-shutdown db ;
9-startup nomount;
10-re-create controlfile from trace;
11-alter database open resetlogs; ## different incarnation creation,
12-switch log file 5 times;
13-shutdown db ;
14-copy controlfiles from file system backup; ## control files differ by SCN and incarnation
15-copy some datafiles from cold backup; ## some datafiles differ by SCN and incarnation
SCENARIO READY



status of database after step 7 ;

RMAN backup created in step 2

RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 PARENT 1 19-DEC-06
2 2 SID2 384861248 PARENT 564488 20-MAR-09
3 3 SID2 384861248 CURRENT 1903057 17-APR-09



RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
3 B F A DISK 22-APR-09 1 1 NO TAG20090422T093549 ## hot backup
4 B F A DISK 22-APR-09 1 1 NO TAG20090422T093655 ## controlfile spfile autobackup

RMAN> list backupset 3 ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.80G DISK 00:00:55 22-APR-09
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20090422T093549
Piece Name: /oracle/oradata/FRA/SID2/backupset/2009_04_22/o1_mf_nnndf_TAG20090422T093549_4yxgp6b7_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2041786 22-APR-09 /oracle/oradata/SID2/system01.dbf
2 Full 2041786 22-APR-09 /oracle/oradata/SID2/undotbs01.dbf
3 Full 2041786 22-APR-09 /oracle/oradata/SID2/sysaux01.dbf
4 Full 2041786 22-APR-09 /oracle/oradata/SID2/users01.dbf
5 Full 2041786 22-APR-09 /oracle/oradata/SID2/example01.dbf
6 Full 2041786 22-APR-09 /oracle/oradata/SID2/drop01.dbf
7 Full 2041786 22-APR-09 /oracle/oradata/SID2/drop02.dbf
8 Full 2041786 22-APR-09 /oracle/oradata/SID2/drop03.dbf
9 Full 2041786 22-APR-09 /oracle/oradata/SID2/adtest01.dbf
10 Full 2041786 22-APR-09 /oracle/oradata/SID2/UKS001.dbf

RMAN> list backupset 4 ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 6.83M DISK 00:00:00 22-APR-09
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20090422T093655
Piece Name: /oracle/oradata/FRA/SID2/autobackup/2009_04_22/o1_mf_s_684841015_4yxgr7mq_.bkp
Control File Included: Ckp SCN: 2041812 Ckp time: 22-APR-09
SPFILE Included: Modification time: 22-APR-09

File system backup created in step 4
drwxr-xr-x 5 oracle dba 4096 Mar 20 13:19 ..
drwxr-x--- 3 oracle dba 4096 Mar 20 13:40 FRA
drwxr-x--- 2 oracle dba 4096 Apr 17 10:24 SID2
drwxr-xr-x 5 oracle dba 4096 Apr 22 09:46 .
drwxr-x--- 2 oracle dba 4096 Apr 22 09:47 SID2_backup

SQL> shutdown immediate;
SQL> startup nomount;
CREATE CONTROLFILE REUSE DATABASE "SID2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/SID2/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/SID2/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/SID2/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/SID2/system01.dbf',
'/oracle/oradata/SID2/undotbs01.dbf',
'/oracle/oradata/SID2/sysaux01.dbf',
'/oracle/oradata/SID2/users01.dbf',
'/oracle/oradata/SID2/example01.dbf',
'/oracle/oradata/SID2/drop01.dbf',
'/oracle/oradata/SID2/drop02.dbf',
'/oracle/oradata/SID2/drop03.dbf',
'/oracle/oradata/SID2/adtest01.dbf',
'/oracle/oradata/SID2/UKS001.dbf'
CHARACTER SET WE8ISO8859P9
;
Control file created.
SQL>

## HINT when create controlfile is issued db is mounted; you do not explicity mount it.

RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 CURRENT 1903057 17-APR-09

SQL> alter database open resetlogs; ## new incarnation created..!!
Database altered.


RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 PARENT 1903057 17-APR-09
2 2 SID2 384861248 CURRENT 2043251 22-APR-09
RMAN>

## Now we have a different incarnation number

SQL> alter system switch logfile; -- execute this a few more times ..
System altered.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.
SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
------------------ ------------- ------------ ----------
22-APR-09 09:57:26 2042410 2042413 8
22-APR-09 09:57:31 2042413 2042415 9
22-APR-09 09:57:32 2042415 2043251 10
22-APR-09 10:27:48 2043251 2043388 1
22-APR-09 10:29:17 2043388 2043390 2
22-APR-09 10:29:18 2043390 2043393 3
22-APR-09 10:29:21 2043393 2043395 4
22-APR-09 10:29:21 2043395 2043397 5

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

[oracle@gulcan oradata]$ cp SID2_backup/control0* SID2/ ## all controlfiles have old snc and incarnation
[oracle@gulcan oradata]$ cp SID2_backup/users01.dbf SID2/ ## users01.dbf datafile has old scn and incarnation.

Scenario now ready .
MAYDAY MAYDAY .. :)



SQL> startup;
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.
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'


RMAN> recover database;
Starting recover at 22-APR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=56 devtype=DISK
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/22/2009 10:53:36
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'
RMAN>

as you see it seems we are stuck
there may be some possible ways; I will try
restore from last rman backup + register archived logs to catalog + apply archived logs

SOLUTION 1
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.
SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
------------------ ------------- ------------ ----------
20-MAR-09 13:40:19 564488 595962 1
.......
14-APR-09 21:02:40 1749055 1791086 59
15-APR-09 20:00:19 1791086 1824816 60
16-APR-09 12:42:26 1824816 1824818 61
16-APR-09 12:42:29 1824818 1824820 62
16-APR-09 12:42:32 1824820 1824822 63
16-APR-09 12:42:34 1824822 1862796 64
17-APR-09 08:48:27 1862796 1862798 65
17-APR-09 08:48:28 1862798 1862801 66
17-APR-09 08:48:30 1862801 1862803 67
17-APR-09 08:48:30 1862803 1882805 68
17-APR-09 08:58:27 1882805 1882948 69
17-APR-09 09:00:10 1882948 1882950 70
17-APR-09 09:00:11 1882950 1882953 71
17-APR-09 09:00:13 1882953 1882955 72
17-APR-09 09:00:13 1882955 1902957 73
17-APR-09 09:03:47 1902957 1903057 74
17-APR-09 10:33:01 1903057 1940815 1
18-APR-09 07:00:09 1940815 1998639 2
19-APR-09 21:00:25 1998639 2041019 3
77 rows selected.

controlfile is lack of archied log information normally. we will try to register newly created archived logs.

alter database register logfile '/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_10_4yxkqnpr_.arc';
....
alter database register logfile '/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_9_4yxkqnl6_.arc';
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


RMAN> restore database ;
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/oradata/SID2/system01.dbf
restoring datafile 00002 to /oracle/oradata/SID2/undotbs01.dbf
restoring datafile 00003 to /oracle/oradata/SID2/sysaux01.dbf
restoring datafile 00004 to /oracle/oradata/SID2/users01.dbf
restoring datafile 00005 to /oracle/oradata/SID2/example01.dbf
restoring datafile 00006 to /oracle/oradata/SID2/drop01.dbf
restoring datafile 00007 to /oracle/oradata/SID2/drop02.dbf
restoring datafile 00008 to /oracle/oradata/SID2/drop03.dbf
restoring datafile 00009 to /oracle/oradata/SID2/adtest01.dbf
restoring datafile 00010 to /oracle/oradata/SID2/UKS001.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 22-APR-09

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2041786 generated at 04/22/2009 09:35:50 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_4_%u_.arc
ORA-00280: change 2041786 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
RETURN PRESSED

ORA-00283: recovery session canceled due to errors
ORA-19906: recovery target incarnation changed during recovery
ORA-01112: media recovery not started


RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 PARENT 1 19-DEC-06
2 2 SID2 384861248 PARENT 564488 20-MAR-09
3 3 SID2 384861248 PARENT 1903057 17-APR-09
4 4 SID2 384861248 CURRENT 2043251 22-APR-09

RMAN> RESET DATABASE TO INCARNATION 4 ;
database reset to incarnation 4

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2041786 generated at 04/22/2009 09:35:50 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_4_%u_.arc
ORA-00280: change 2041786 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
... return pressed
Log applied.
Media recovery complete.

SQL> alter database open RESETLOGS ;
Database altered.

DONE..!!
That was really cool..!

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>