Oracle穿越incarnation恢复数据

转载

mb5fdb0a4002420 2013-06-06 11:27:00

文章标签 sql hive oracle 数据库完全恢复 文章分类 代码人生

当进行不完全恢复后，可能没达到恢复的要求，这个时候数据库已经被resetlogs方式打开过了，如果在进行不完全恢复前没有对数据库进行全库备份，这个时候又想恢复上一个incarnation的某些数据，在这种场景下就需要进行incarnation穿越，下面来演示下这个场景！

一：准备实验基础数据

1. SQL> create table t043_incarnation(a varchar2(20)) tablespace example;

2. Table created.

4. SQL> insert into t043_incarnation values ('corss successful');

5. 1 row created.

7. SQL> commit;

8. Commit complete.

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*

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

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.

4. SQL> select * from t043_other;

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.

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

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)，这个时候最好重新备份下数据库

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：git rebase 各种场景及现象

下一篇：AS2 BitmapData类的应用.

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

Oracle穿越incarnation恢复数据

Oracle穿越incarnation恢复数据

51CTO博客