Oracle穿越incarnation恢复数据
当进行不完全恢复后，可能没达到恢复的要求，这个时候数据库已经被resetlogs方式打开过了，如果在进行不完全恢复前没有对数据库进行全库备份，这个时候又想恢复上一个incarnation的某些数据，在这种场景下就需要进行incarnation穿越，下面来演示下这个场景！
一：准备实验基础数据
1. SQL> create table t043_incarnation(a varchar2(20)) tablespace example;
2. Table created.
3.
4. SQL> insert into t043_incarnation values ('corss successful');
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.
二：删除全部控制文件第67号归档日志文件，这样进行恢复的时候就必须进行不完全恢复
1. [oracle@rhel6 2011_07_17]$ pwd
2. /u01/app/flash_recovery_area/ORA10G/archivelog/2011_07_17
3. [oracle@rhel6 2011_07_17]$ rm -i o1_mf_1_67_725rmcx1_.arc
4. rm: remove regular file `o1_mf_1_67_725rmcx1_.arc'? y
5. [oracle@rhel6 2011_07_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
三：使用控制文件二进制自动备份进行恢复，也可以使用trace脚本，由于归档日志丢失的原因，都需要进行不完全恢复
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/2011_07_17/o1_mf_s_756768121_725rhvkf_.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. ——————————其他输出省略——————————
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/2011_07_17/o1_mf_1_64_%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/2011_07_17/o1_mf_1_65_%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/2011_07_17/o1_mf_1_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/2011_07_17/o1_mf_1_66_%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/2011_07_17/o1_mf_1_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/2011_07_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/2011_07_17/o1_mf_1_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/2011_07_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/2011_07_17/o1_mf_1_67_%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 cancelled.
四：恢复完后打开数据库，发现之前被truncate的表没有被成功恢复，这个时候就需要进行incarnation穿越
1. SQL> alter database open resetlogs;
2. Database altered.
3.
4. SQL> select * from t043_other;
5.
6. A
7. ----------
8. 1
9. 2
10. 3
11. 4
12.
13.
14. SQL> select * from t043_incarnation;
15. no rows selected
五：关闭数据库后将数据库启动在mount状态，利用rman进行incarnation穿越，恢复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, 2005, 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 '2011-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. A
60. ------------------------------------------------------------
61. corss successful
62.
63. SQL> select * from t043_other;
64.
65. A
66. ----------
67. 1
68. 2
总结：穿越incarnation后，将会产生孤儿incarnation(orphan)，这个时候最好重新备份下数据库
本文出自 “月牙天冲” 博客
http://www.2cto.com/database/201107/96545.html
