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..!