Oracle data recovery through incarnation

After Incomplete recovery, the database may not meet the recovery requirements. At this time, the database has been opened in the resetlogs mode. If the database is not fully backed up before Incomplete recovery, at this time, I want to restore some data of the last incarnation. In this scenario, I need to perform incarnation traversal. The following is a demonstration of this scenario!
I. Prepare basic lab data
1. SQL> create table t043_incarnation (a varchar2 (20) tablespace example;
2. Table created.
3.
4. SQL> insert into t043_incarnation values ('corss successfu ');
5. 1 row created.
6.
7. SQL> commit;
8. Commit complete.
9.
10. SQL> create table t043_other (a number) tablespace example;
11. Table created.
12.
13. SQL> insert into t043_other values (1 );
14. 1 row created.
15.
16. SQL> insert into t043_other values (2 );
17. 1 row created.
18.
19. SQL> commit;
20. Commit complete.
21.
22. SQL> alter system switch logfile;
23. System altered.
24.
25. SQL> insert into t043_other values (3 );
26. 1 row created.
27.
28. SQL> alter system switch logfile;
29. System altered.
30.
31. SQL> select sysdate from dual;
32.
33. SYSDATE
34 .-------------------
35. 2011-07-17-21:22:30
36.
37. SQL> truncate table t043_incarnation;
38. Table truncated.
39.
40. SQL> archive log list;
41. Database log mode Archive Mode
42. Automatic archival Enabled
43. Archive destination USE_DB_RECOVERY_FILE_DEST
44. Oldest online log sequence 64
45. Next log sequence to archive 66
46. Current log sequence 66
47. SQL> insert into t043_other values (4 );
48. 1 row created.
49.
50. SQL> commit;
51. Commit complete.
52.
53. SQL> alter system switch logfile;
54. System altered.
55.
56. SQL> insert into t043_other values (5 );
57. 1 row created.
58.
59. SQL> alter system switch logfile;
60. System altered.
61.
62. SQL> commit;
63. Commit complete.
64.
65. SQL> alter system switch logfile;
66. System altered.
Ii. Delete the archive log file No. 67th of all control files. Incomplete recovery is required during restoration.
1. [oracle @ rhel6 20151107_17] $ pwd
2./u01/app/flash_recovery_area/ORA10G/archivelog/20151107_17
3. [oracle @ rhel6 20151107_17] $ rm-I o1_mf_1_67_725rmcx1 _. arc
4. rm: remove regular file 'o1 _ mf_000067_725rmcx1 _. arc '? Y
5. [oracle @ rhel6 20151107_17] $ rm-rf/u01/app/oradata/ora10g/control0 *
6.
7. SQL> shutdown abort;
8. ORACLE instance shut down.
9. SQL> startup
10. ORACLE instance started.
11.
12. Total System Global Area 629145600 bytes
13. Fixed Size 2022824 bytes
14. Variable Size 205521496 bytes
15. Database Buffers 415236096 bytes
16. Redo Buffers 6365184 bytes
17. ORA-00205: error in identifying control file, check alert log for more info
Iii. Use the binary automatic backup of the control file for recovery, or use the trace script. Incomplete recovery is required due to the loss of archive logs.
1. RMAN> restore controlfile from autobackup;
2. Starting restore at 2011-07-17-21:28:29
3. using target database control file instead of recovery catalog
4. allocated channel: ORA_DISK_1
5. channel ORA_DISK_1: sid = 154 devtype = DISK
6.
7. recovery area destination:/u01/app/flash_recovery_area
8. database name (or database unique name) used for search: ORA10G
9. channel ORA_DISK_1: autobackup found in the recovery area
10. channel ORA_DISK_1: autobackup found:/u01/app/flash_recovery_area/ORA10G/autobackup/20151107_17/o1_mf_s_75676812316725rhvkf _. bkp
11. channel ORA_DISK_1: control file restore from autobackup complete
12. output filename =/u01/app/oradata/ora10g/control01.ctl
13. output filename =/u01/app/oradata/ora10g/control02.ctl
14. output filename =/u01/app/oradata/ora10g/control03.ctl
15. Finished restore at 2011-07-17-21:28:34
16.
17. RMAN> alter database mount;
18. database mounted
19. released channel: ORA_DISK_1
20.
21. RMAN> list incarnation;
22.
23. List of Database Incarnations
24. DB Key Inc Key DB Name db id status Reset SCN Reset Time
25 .-------------------------------------------------------------
26. 1 1 ORA10G 4061806388 PARENT 1 2005-10-22-21:44:08
27. 2 2 ORA10G 4061806388 PARENT 525876 2011-02-12-18:37:43
28. 3 3 ORA10G 4061806388 PARENT 7781117 2011-05-23-13:24:09
29. 4 4 ORA10G 4061806388 CURRENT 7787669 2011-05-23-15:51:11
30.
31.
32. RMAN> restore database;
33. Starting restore at 2011-07-17-21:29:21
34. Starting implicit crosscheck backup at 2011-07-17-21:29:21
35. allocated channel: ORA_DISK_1
36. channel ORA_DISK_1: sid = 154 devtype = DISK
37. Crosschecked 1 objects
38. ---------- other outputs are omitted ----------
39.
40. SQL> recover database using backup controlfile until cancel;
41. ORA-00279: change 9022073 generated at 07/17/2011 21:20:26 needed for thread 1
42. ORA-00289: suggestion:
43./u01/app/flash_recovery_area/ORA10G/archivelog/20151107_17/o2017mf_000064 _ % u _. arc
44. ORA-00280: change 9022073 for thread 1 is in sequence #64
45.
46. Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
47.
48. ORA-00279: change 9022129 generated at 07/17/2011 21:21:35 needed for thread 1
49. ORA-00289: suggestion:
50./u01/app/flash_recovery_area/ORA10G/archivelog/20151107_17/o2017mf_000065 _ % u _. arc
51. ORA-00280: change 9022129 for thread 1 is in sequence #65
52. ORA-00278: log file
53. '/u01/app/flash_recovery_area/ORA10G/archivelog/201%07_17/o%mf_%64_725rh0wy _.
54. arc' no longer needed for this recovery
55.
56. Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
57.
58. ORA-00279: change 9022148 generated at 07/17/2011 21:22:01 needed for thread 1
59. ORA-00289: suggestion:
60./u01/app/flash_recovery_area/ORA10G/archivelog/20151107_17/o2017mf_000066 _ % u _. arc
61. ORA-00280: change 9022148 for thread 1 is in sequence #66
62. ORA-00278: log file
63. '/u01/app/flash_recovery_area/ORA10G/archivelog/201%07_17/o%mf_%65_725rhtt9 _.
64. arc' no longer needed for this recovery
65.
66. Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
67.
68. ORA-00279: change 9022403 generated at 07/17/2011 21:23:46 needed for thread 1
69. ORA-00289: suggestion:
70./u01/app/flash_recovery_area/ORA10G/archivelog/20151107_17/o1_mf_1_67 _ % u _. arc
71. ORA-00280: change 9022403 for thread 1 is in sequence #67
72. ORA-00278: log file
73. '/u01/app/flash_recovery_area/ORA10G/archivelog/201%07_17/o%mf_%66_725rm33n _.
74. arc' no longer needed for this recovery
75.
76. Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
77.
78. ORA-00308: cannot open archived log
79. '/u01/app/flash_recovery_area/ORA10G/archivelog/20151107_17/o1_mf_1_67 _ % u _. arc'
80. ORA-27037: unable to obtain file status
81. Linux-x86_64 Error: 2: No such file or directory
82. Additional information: 3
83.
84. SQL> recover database using backup controlfile until cancel;
85. ORA-00279: change 9022403 generated at 07/17/2011 21:23:46 needed for thread 1
86. ORA-00289: suggestion:
87./u01/app/flash_recovery_area/ORA10G/archivelog/20151107_17/o2017mf_000067 _ % u _. arc
88. ORA-00280: change 9022403 for thread 1 is in sequence #67
89.
90. Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
91. cancel
92. Media recovery canceled.
4. Open the database after recovery and find that the table previously truncate has not been successfully recovered. At this time, incarnation traversal is required.
1. SQL> alter database open resetlogs;
2. Database altered.
3.
4. SQL> select * from t043_other;
5.
6.
7 .----------
8. 1
9. 2
10. 3
11. 4
12.
13.
14. SQL> select * from t043_incarnation;
15. no rows selected
5. After the database is closed, the database is started in the mount state. The incarnation traversal is performed using rman to restore the table t043_incarnation.
1. SQL> shutdown immediate
2. Database closed.
3. Database dismounted.
4. ORACLE instance shut down.
5. SQL> startup mount
6. ORACLE instance started.
7.
8. Total System Global Area 629145600 bytes
9. Fixed Size 2022824 bytes
10. Variable Size 209715800 bytes
11. Database Buffers 411041792 bytes
12. Redo Buffers 6365184 bytes
13. Database mounted.
14.
15. [oracle @ rhel6 ~] $ Rman target/
16. Recovery Manager: Release 10.2.0.1.0-Production on Sun Jul 17 21:34:14 2011
17. Copyright (c) 1982,200 5, Oracle. All rights reserved.
18. connected to target database: ORA10G (DBID = 4061806388)
19.
20. RMAN> list incarnation;
21.
22. using target database control file instead of recovery catalog
23.
24. List of Database Incarnations
25. DB Key Inc Key DB Name db id status Reset SCN Reset Time
26 .-------------------------------------------------------------
27. 1 1 ORA10G 4061806388 PARENT 1 2005-10-22-21:44:08
28. 2 2 ORA10G 4061806388 PARENT 525876 2011-02-12-18:37:43
29. 3 3 ORA10G 4061806388 PARENT 7781117 2011-05-23-13:24:09
30. 4 4 ORA10G 4061806388 PARENT 7787669 2011-05-23-15:51:11
31. 5 5 ORA10G 4061806388 CURRENT 9022404 2011-07-17-21:32:32
32.
33. RMAN> reset database to incarnation 4;
34. database reset to incarnation 4
35.
36. RMAN> list incarnation;
37. List of Database Incarnations
38. DB Key Inc Key DB Name db id status Reset SCN Reset Time
39 .-------------------------------------------------------------
40. 1 1 ORA10G 4061806388 PARENT 1 2005-10-22-21:44:08
41. 2 2 ORA10G 4061806388 PARENT 525876 2011-02-12-18:37:43
42. 3 3 ORA10G 4061806388 PARENT 7781117 2011-05-23-13:24:09
43. 4 4 ORA10G 4061806388 CURRENT 7787669 2011-05-23-15:51:11
44. 5 5 ORA10G 4061806388 ORPHAN 9022404 2011-07-17-21:32:32
45.
46.
47. RMAN> run {
48. 2> set until time '2017-07-17-21:22:30 ';
49. 3> restore database;
50. 4> recover database;
51. 5>}
52.
53. RMAN> alter database open resetlogs;
54. database opened
55.
56.
57. SQL> select * from t043_incarnation;
58.
59.
60 .------------------------------------------------------------
61. corss successful
62.
63. SQL> select * from t043_other;
64.
65.
66 .----------
67. 1
68. 2
Conclusion: After incarnation, the orphan incarnation (orphan) will be generated. It is best to back up the database again.
This article is from the "yueda tianchong" blog