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: {=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: {=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: {=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: {=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. -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: {=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, . 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
一：準備實驗基礎資料
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: {
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: {
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: {
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: {
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. -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: {
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, . 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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/90618/viewspace-763189/，如需轉載，請註明出處，否則將追究法律責任。
請登入後發表評論
登入
全部評論
相關文章
- 在rman恢復中incarnation的概念
- Oracle恢復誤刪資料Oracle
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- 【資料庫資料恢復】Oracle資料庫誤truncate table的資料恢復案例資料庫資料恢復Oracle
- 【資料庫資料恢復】誤truncate table的Oracle資料庫資料恢復方案資料庫資料恢復Oracle
- 【資料庫資料恢復】oracle資料庫誤truncate table怎麼恢復資料？資料庫資料恢復Oracle
- Oracle資料庫恢復之resetlogsOracle資料庫
- 【資料庫資料恢復】ORACLE常見資料災難&資料恢復可能性資料庫資料恢復Oracle
- 【資料庫資料恢復】Oracle資料庫ASM磁碟組掉線如何恢復資料？資料庫資料恢復OracleASM
- 【北亞資料恢復】異常斷電導致Oracle資料庫報錯的oracle資料恢復資料恢復Oracle資料庫
- [Oracle]Oracle資料庫資料被修改或者刪除恢復資料Oracle資料庫
- 【資料庫資料恢復】ASM磁碟組掉線的Oracle資料庫資料恢復案例資料庫資料恢復ASMOracle
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- Oracle資料庫冷備和恢復Oracle資料庫
- Oracle & MySQL & PostgreSQL資料庫恢復支援OracleMySql資料庫
- oracle asm 資料塊重構恢復OracleASM
- 【北亞資料恢復】oracle資料庫執行truncate table命令怎麼恢復資料？資料恢復Oracle資料庫
- 【資料庫資料恢復】Oracle ASM例項無法掛載的資料恢復案例資料庫資料恢復OracleASM
- 【資料庫資料恢復】ASM例項不能掛載的Oracle資料庫資料恢復案例資料庫資料恢復ASMOracle
- 資料庫資料恢復-oracle資料庫報錯無法開啟的如何恢復資料？資料庫資料恢復Oracle
- 【資料庫資料恢復】LINUX環境下ORACLE資料庫誤刪除的資料恢復資料庫資料恢復LinuxOracle
- 【資料庫資料恢復】斷電導致Oracle資料庫資料丟失的資料恢復案例資料庫資料恢復Oracle
- RMAN深入解析之--Incarnation應用（不完全恢復）
- 資料恢復：AMDU資料抽取恢復資料恢復
- Oracle ASM故障資料恢復解決方案OracleASM資料恢復
- Oracle 業務資料unload恢復過程Oracle
- 【oracle資料庫資料恢復】誤操作導致的資料庫誤刪除的資料恢復案例Oracle資料庫資料恢復
- 【資料庫資料恢復】HP-UX系統ORACLE資料庫被誤刪除的資料恢復資料庫資料恢復UXOracle
- 【資料庫資料恢復】Oracle資料庫檔案出現壞塊報錯的資料恢復案例資料庫資料恢復Oracle
- Vsan資料恢復—Vsan資料恢復案例資料恢復
- 【Vsan資料恢復】Vsan資料恢復案例資料恢復
- 伺服器資料恢復-ext3檔案系統下oracle資料庫資料恢復案例伺服器資料恢復Oracle資料庫
- 恢復Oracle資料庫誤刪除資料的語句Oracle資料庫
- 資料庫資料恢復-ORACLE資料庫的常見故障&各種故障下的資料恢復可能性資料庫資料恢復Oracle
- 【北亞資料恢復】伺服器斷電導致Oracle資料庫報錯的資料恢復案例資料恢復伺服器Oracle資料庫
- 【資料庫資料恢復】SAP資料庫資料恢復案例資料庫資料恢復
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- Sybase ASE資料庫恢復，Sybase資料恢復，資料誤刪除恢復工具READSYBDEVICE資料庫資料恢復dev
- 【伺服器資料恢復】ORACLE-SUN-ZFS檔案系統伺服器資料恢復案例伺服器資料恢復Oracle