Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 对fllashback一点疑问
发新帖
saup007

133

积分

0

好友

17

主题

[SQL调优] 对fllashback一点疑问

1#
发表于 2013-1-16 18:06:56 | 查看: 2307| 回复: 1
flashback database后，还可以recover database到flashback前，原理是什么呢，我做了一下实验，貌似不需要使用归档，只使用current redo，难道是因为数据文件没有被覆盖，还是使用recover 了flashback database的操作。

但我创建一个新的表空间后，这时，闪回后，需要归档了
  1. --测试flashback database到前一scn后，recover database并不需要归档!

  3. 17:10:59 sys@TEST10G> archive log list
  4. Database log mode             Archive Mode
  5. Automatic archival            Enabled
  6. Archive destination            USE_DB_RECOVERY_FILE_DEST
  7. Oldest online log sequence     1
  8. Next log sequence to archive   3
  9. Current log sequence            3
  10. 17:11:00 sys@TEST10G>
  11. 17:11:01 sys@TEST10G> create table saup.tes_flashback4 as select * from dba_objects;

  13. Table created.

  15. 17:11:31 sys@TEST10G> alter system switch logfile;

  17. System altered.

  19. saup@TEST10G> select * from tab;

  21. TNAME                      TABTYPE     CLUSTERID
  22. ------------------------------ ------- ----------
  23. TES_FLASHBACK2                 TABLE
  24. TES_FLASHBACK4                 TABLE
  25. TES_FLASHBACK3                 TABLE

  27. --此时将归档文件移动他处（使用mv，并不是使用rman删除）

  29. 17:30:55 saup@TEST10G> col name for a90
  30. 17:30:58 saup@TEST10G> select sequence#,name from v$archived_log;

  32. SEQUENCE# NAME
  33. ---------- ------------------------------------------------------------------------------------------
  34.      2 /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_2_8hdtx1fk_.arc
  35.      3 /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_3_8hdvphsb_.arc

  37. 17:11:44 sys@TEST10G> shutdown immediate;
  38. Database closed.
  39. Database dismounted.
  40. ORACLE instance shut down.
  41. 17:12:24 sys@TEST10G> startup mount
  42. ORACLE instance started.

  44. Total System Global Area  599785472 bytes
  45. Fixed Size              2085776 bytes
  46. Variable Size            163581040 bytes
  47. Database Buffers       427819008 bytes
  48. Redo Buffers              6299648 bytes
  49. Database mounted.

  51. --相对早一点一个scn值

  53. 17:12:32 sys@TEST10G> flashback database to scn 615764;

  55. Flashback complete.

  57. 17:12:37 sys@TEST10G> alter database open read only;

  59. Database altered.

  61. 17:12:48 sys@TEST10G> conn saup/saup
  62. Connected.
  63. 17:12:53 saup@TEST10G> select * from tab;

  65. TNAME                      TABTYPE     CLUSTERID
  66. ------------------------------ ------- ----------
  67. TES_FLASHBACK                 TABLE


  70. 17:12:56 saup@TEST10G> conn / as sysdba
  71. Connected.
  72. 17:13:00 sys@TEST10G> recover database;
  73. ORA-00283: recovery session canceled due to errors
  74. ORA-01124: cannot recover data file 1 - file is in use or recovery
  75. ORA-01110: data file 1: '/u01/oradata/TEST10G/system01.dbf'


  78. 17:13:03 sys@TEST10G>
  79. 17:13:03 sys@TEST10G> shutdown immediate;
  80. Database closed.
  81. Database dismounted.
  82. ORACLE instance shut down.
  83. 17:13:13 sys@TEST10G>
  84. 17:13:14 sys@TEST10G>
  85. 17:13:14 sys@TEST10G> startup mount
  86. ORACLE instance started.

  88. Total System Global Area  599785472 bytes
  89. Fixed Size              2085776 bytes
  90. Variable Size            163581040 bytes
  91. Database Buffers       427819008 bytes
  92. Redo Buffers              6299648 bytes
  93. Database mounted.
  94. 17:13:20 sys@TEST10G> recover database;
  95. Media recovery complete.
  96. 17:13:25 sys@TEST10G>
  97. 17:13:30 sys@TEST10G>
  98. 17:13:30 sys@TEST10G> alter database open;

  100. Database altered.

  102. 17:14:09 sys@TEST10G> conn saup/saup
  103. Connected.
  104. 17:14:18 saup@TEST10G>
  105. 17:14:18 saup@TEST10G>
  106. 17:14:18 saup@TEST10G> select * from tab;

  108. TNAME                      TABTYPE     CLUSTERID
  109. ------------------------------ ------- ----------
  110. TES_FLASHBACK2                 TABLE
  111. TES_FLASHBACK4                 TABLE
  112. TES_FLASHBACK3                 TABLE
复制代码
创建表空间，并在其上创建表等操作
  1. 17:47:09 sys@TEST10G> create tablespace test_flashback datafile '/u01/oradata/TEST10G/test_flashback.dbf' size 1M ;

  3. Tablespace created.

  5. 17:48:59 sys@TEST10G> create table saup.flashback_recover1 tablespace test_flashback as select * from dba_objects where rownum<8000;

  7. Table created.

  9. 17:49:03 sys@TEST10G> select * from dba_free_space where tablespace_name='TEST_FLASHBACK';

  11. TABLESPACE_NAME                   FILE_ID   BLOCK_ID          BYTES     BLOCKS RELATIVE_FNO
  12. ------------------------------ ---------- ---------- ---------- ---------- ------------
  13. TEST_FLASHBACK                                6         121          65536          8              6

  15. 17:50:18 sys@TEST10G>
  16. 17:50:19 sys@TEST10G> select current_scn from  v$database;

  18. CURRENT_SCN
  19. -----------
  20.      621278

  22. 17:50:53 sys@TEST10G>
  23. 17:50:53 sys@TEST10G> alter system switch logfile;

  25. System altered.

  27. 17:50:59 sys@TEST10G> drop table  saup.flashback_recover1 purge;

  29. Table dropped.

  31. 17:51:10 sys@TEST10G> select * from dba_free_space where tablespace_name='TEST_FLASHBACK';

  33. TABLESPACE_NAME                   FILE_ID   BLOCK_ID          BYTES     BLOCKS RELATIVE_FNO
  34. ------------------------------ ---------- ---------- ---------- ---------- ------------
  35. TEST_FLASHBACK                                6           9         983040        120              6

  37. 17:51:12 sys@TEST10G> select current_scn from  v$database;

  39. CURRENT_SCN
  40. -----------
  41.      621379

  43. 17:51:39 sys@TEST10G> alter system switch logfile;

  45. System altered.

  47. 17:51:46 sys@TEST10G> create table saup.flashback_recover2 tablespace test_flashback as select * from dba_objects where rownum<8000;

  49. Table created.

  51. 17:51:55 sys@TEST10G> select * from dba_free_space where tablespace_name='TEST_FLASHBACK';

  53. TABLESPACE_NAME                   FILE_ID   BLOCK_ID          BYTES     BLOCKS RELATIVE_FNO
  54. ------------------------------ ---------- ---------- ---------- ---------- ------------
  55. TEST_FLASHBACK                                6         121          65536          8              6

  57. 17:51:59 sys@TEST10G> select current_scn from  v$database;

  59. CURRENT_SCN
  60. -----------
  61.      621452

  63. 17:52:02 sys@TEST10G> alter system switch logfile;

  65. System altered.

  67. 17:52:07 sys@TEST10G> select * from v$log;

  69.     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS            FIRST_CHANGE# FIRST_TIME
  70. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
  71.          1            1               7   52428800             1 NO  CURRENT                   621456 2013-01-16 17:52:07
  72.          2            1               5   52428800             1 YES INACTIVE                   621282 2013-01-16 17:50:59
  73.          3            1               6   52428800             1 YES ACTIVE                   621383 2013-01-16 17:51:46

  75. 17:53:21 sys@TEST10G> shutdown immediate;
  76. Database closed.
  77. Database dismounted.
  78. ORACLE instance shut down.
  79. 17:53:43 sys@TEST10G> startup mount
  80. ORACLE instance started.

  82. Total System Global Area  599785472 bytes
  83. Fixed Size                    2085776 bytes
  84. Variable Size                  163581040 bytes
  85. Database Buffers          427819008 bytes
  86. Redo Buffers                    6299648 bytes
  87. Database mounted.
  88. 17:53:52 sys@TEST10G> flashback database to scn 615764;
  89. flashback database to scn 615764
  90. *
  91. ERROR at line 1:
  92. ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
  93. ORA-38761: redo log sequence 2 in thread 1, incarnation 2 could not be accessed


  96. 17:53:55 sys@TEST10G> select * from v$log;

  98.     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS            FIRST_CHANGE# FIRST_TIME
  99. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
  100.          1            1               7   52428800             1 NO  CURRENT                   621456 2013-01-16 17:52:07
  101.          3            1               6   52428800             1 YES INACTIVE                   621383 2013-01-16 17:51:46
  102.          2            1               5   52428800             1 YES INACTIVE                   621282 2013-01-16 17:50:59

  104. 17:55:12 sys@TEST10G> select * from v$flashback_database_log;

  106. OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
  107. -------------------- ------------------- ---------------- -------------- ------------------------
  108.               615690 2013-01-16 16:23:23             1440        24576000                        0

  110. 将mv的归档全移回来，只需要最早sequence@为2的归档

  112. 17:55:47 sys@TEST10G> flashback database to scn 615764;

  114. Flashback complete.

  116. 闪回时，alter日志如下：

  118. Wed Jan 16 17:57:05 2013
  119. flashback database to scn 615764
  120. Wed Jan 16 17:57:05 2013
  121. Flashback Restore Start
  122. Deleted file /u01/oradata/TEST10G/test_flashback.dbf
  123. Flashback: deleted datafile #6 in tablespace #7 from control file.
  124. Flashback: dropped tablespace #7: 'TEST_FLASHBACK' from the control file.
  125. Flashback Restore Complete
  126. Flashback Media Recovery Start
  127. parallel recovery started with 2 processes
  128. Flashback Media Recovery Log /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_2_8hdtx1fk_.arc
  129. Wed Jan 16 17:57:06 2013
  130. Incomplete Recovery applied until change 615774
  131. Flashback Media Recovery Complete
  132. Completed: flashback database to scn 615764

复制代码
2#
发表于 2013-1-16 18:34:05
本帖最后由 saup007 于 2013-1-16 18:36 编辑

没有备份，这个还能恢复不，除了使用闪回。
  1. 18:25:09 sys@TEST10G>  select file#,name,status from v$datafile_header;

  3.      FILE# NAME                                                         STATUS
  4. ---------- ------------------------------------------------------------------------------------------ -------
  5.      1 /u01/oradata/TEST10G/system01.dbf                                         ONLINE
  6.      2 /u01/oradata/TEST10G/undotbs01.dbf                                         ONLINE
  7.      3 /u01/oradata/TEST10G/sysaux01.dbf                                         ONLINE
  8.      4 /u01/oradata/TEST10G/users01.dbf                                         ONLINE
  9.      5 /u01/oradata/TEST10G/ts_dba.dbf                                         ONLINE

  11. 18:30:50 sys@TEST10G>  select file#,name,status from v$datafile;

  13.      FILE# NAME                                                         STATUS
  14. ---------- ------------------------------------------------------------------------------------------ -------
  15.      1 /u01/oradata/TEST10G/system01.dbf                                         SYSTEM
  16.      2 /u01/oradata/TEST10G/undotbs01.dbf                                         RECOVER
  17.      3 /u01/oradata/TEST10G/sysaux01.dbf                                         RECOVER
  18.      4 /u01/oradata/TEST10G/users01.dbf                                         RECOVER
  19.      5 /u01/oradata/TEST10G/ts_dba.dbf                                         RECOVER18:31:42 sys@TEST10G> select sequence#,name from v$archived_log;

  21. SEQUENCE# NAME
  22. ---------- ------------------------------------------------------------------------------------------
  23.      2 /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_2_8hdtx1fk_.arc
  24.      3 /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_3_8hdvphsb_.arc
  25.      4 /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_4_8hdy03wk_.arc
  26.      5 /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_5_8hdy1ln6_.arc
  27.      6 /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_6_8hdy27vh_.arc

  29. 18:32:01 sys@TEST10G> recover database ;
  30. ORA-00279: change 615774 generated at 01/16/2013 16:26:14 needed for thread 1
  31. ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_2_%u_.arc
  32. ORA-00280: change 615774 for thread 1 is in sequence #2


  35. 18:32:09 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  36. auto
  37. ORA-00279: change 617592 generated at 01/16/2013 16:58:09 needed for thread 1
  38. ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_3_%u_.arc
  39. ORA-00280: change 617592 for thread 1 is in sequence #3
  40. ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_2_8hdtx1fk_.arc' no longer needed for this recovery


  43. ORA-00279: change 618495 generated at 01/16/2013 17:11:43 needed for thread 1
  44. ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_4_%u_.arc
  45. ORA-00280: change 618495 for thread 1 is in sequence #4
  46. ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST10G/archivelog/2013_01_16/o1_mf_1_3_8hdvphsb_.arc' no longer needed for this recovery


  49. ORA-00283: recovery session canceled due to errors
  50. ORA-01244: unnamed datafile(s) added to control file by media recovery
  51. ORA-01110: data file 6: '/u01/oradata/TEST10G/test_flashback.dbf'


  54. ORA-01112: media recovery not started


  57. 18:32:15 sys@TEST10G>  select file#,name,status from v$datafile;

  59.      FILE# NAME                                                         STATUS
  60. ---------- ------------------------------------------------------------------------------------------ -------
  61.      1 /u01/oradata/TEST10G/system01.dbf                                         SYSTEM
  62.      2 /u01/oradata/TEST10G/undotbs01.dbf                                         RECOVER
  63.      3 /u01/oradata/TEST10G/sysaux01.dbf                                         RECOVER
  64.      4 /u01/oradata/TEST10G/users01.dbf                                         RECOVER
  65.      5 /u01/oradata/TEST10G/ts_dba.dbf                                         RECOVER
  66.      6 /u01/app/oracle/product/10.2.0/test_10g/dbs/UNNAMED00006                          RECOVER

  68. 6 rows selected.

  70. 18:32:29 sys@TEST10G>  select file#,name,status from v$datafile_header;

  72.      FILE# NAME                                                         STATUS
  73. ---------- ------------------------------------------------------------------------------------------ -------
  74.      1 /u01/oradata/TEST10G/system01.dbf                                         ONLINE
  75.      2 /u01/oradata/TEST10G/undotbs01.dbf                                         ONLINE
  76.      3 /u01/oradata/TEST10G/sysaux01.dbf                                         ONLINE
  77.      4 /u01/oradata/TEST10G/users01.dbf                                         ONLINE
  78.      5 /u01/oradata/TEST10G/ts_dba.dbf                                         ONLINE
  79.      6                                                             ONLINE

  81. 6 rows selected.

  83. 17:57:08 sys@TEST10G> alter database open read only;

  85. Database altered.

  87. 18:12:59 sys@TEST10G> conn saup/saup
  88. Connected.
  89. 18:13:02 saup@TEST10G> select * from tab;

  91. TNAME                      TABTYPE     CLUSTERID
  92. ------------------------------ ------- ----------
  93. TES_FLASHBACK                 TABLE

  95. 18:13:08 saup@TEST10G> conn / as sysdba
  96. Connected.
  97. 18:13:10 sys@TEST10G> shutdown immediate;
  98. Database closed.
  99. Database dismounted.
  100. ORACLE instance shut down.
  101. 18:13:17 sys@TEST10G> startup mount
  102. ORACLE instance started.

  104. Total System Global Area  599785472 bytes
  105. Fixed Size              2085776 bytes
  106. Variable Size            163581040 bytes
  107. Database Buffers       427819008 bytes
  108. Redo Buffers              6299648 bytes
  109. Database mounted.
复制代码
使用闪回。
  1. 18:36:58 sys@TEST10G>
  2. select * from
  3. 18:37:16   2  v$flashback_database_log;

  5. OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
  6. -------------------- ------------------- ---------------- -------------- ------------------------
  7.               615690 2013-01-16 16:23:23             1440        24576000                 25681920

  9. 18:37:18 sys@TEST10G>
  10. 18:37:18 sys@TEST10G> flashback database to scn 621452;

  12. Flashback complete.

  14. 18:37:48 sys@TEST10G> alter database open read only;

  16. Database altered.

  18. 18:38:02 sys@TEST10G> conn saup/saup
  19. Connected.
  20. 18:38:05 saup@TEST10G> select * from tab;

  22. TNAME                               TABTYPE        CLUSTERID
  23. ------------------------------ ------- ----------
  24. TES_FLASHBACK2                       TABLE
  25. TES_FLASHBACK4                       TABLE
  26. TES_FLASHBACK3                       TABLE
  27. FLASHBACK_RECOVER2               TABLE

  29. 18:38:09 saup@TEST10G>  select file#,name,status from v$datafile;

  31.      FILE# NAME                                                                                       STATUS
  32. ---------- ------------------------------------------------------------------------------------------ -------
  33.          1 /u01/oradata/TEST10G/system01.dbf                                                              SYSTEM
  34.          2 /u01/oradata/TEST10G/undotbs01.dbf                                                              RECOVER
  35.          3 /u01/oradata/TEST10G/sysaux01.dbf                                                              RECOVER
  36.          4 /u01/oradata/TEST10G/users01.dbf                                                              RECOVER
  37.          5 /u01/oradata/TEST10G/ts_dba.dbf                                                              RECOVER
  38.          6 /u01/oradata/TEST10G/test_flashback.dbf                                                      RECOVER

  40. 6 rows selected.

  42. 18:38:19 saup@TEST10G>  select file#,name,status from v$datafile_header;

  44.      FILE# NAME                                                                                       STATUS
  45. ---------- ------------------------------------------------------------------------------------------ -------
  46.          1 /u01/oradata/TEST10G/system01.dbf                                                              ONLINE
  47.          2 /u01/oradata/TEST10G/undotbs01.dbf                                                              ONLINE
  48.          3 /u01/oradata/TEST10G/sysaux01.dbf                                                              ONLINE
  49.          4 /u01/oradata/TEST10G/users01.dbf                                                              ONLINE
  50.          5 /u01/oradata/TEST10G/ts_dba.dbf                                                              ONLINE
  51.          6 /u01/oradata/TEST10G/test_flashback.dbf                                                      ONLINE

  53. 6 rows selected.
复制代码

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-16 04:16 , Processed in 0.046532 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569