实验flashback database

最新推荐文章于 2023-08-03 13:52:47 发布

转载最新推荐文章于 2023-08-03 13:52:47 发布 · 84 阅读

本文记录了一次使用Oracle Flashback Database功能进行数据恢复的实验过程。通过对比Flashback Database与传统备份恢复的区别，发现前者操作更加快捷高效。实验中还探讨了如何在不同情况下正确打开数据库，包括使用NORESETLOGS和RESETLOGS选项的情况，并记录了在恢复过程中遇到的问题及解决方法。

今天作了个10.2.0.1 flashback database的实验，深刻体会到flashback database和restore database from backup
没有区别，就是快，flashback后需要resetlogs，也可以用recover把它再恢复到最后的状态，
本次代价是我的standby废了（之前应该把standby早点停掉，使他的scn小于flashback到的scn就好了，测试库就没有设置standby delay了）。
之前也做过flashback table，standby是可以保持一致的，所以flashback table应该和不完全恢复无关。

各位前辈我的总结有问题吗？

以下试验数据

SQL> select count(*) from mytable007;

  COUNT(*)
----------
      11

SQL> delete from mytable007;

11 rows deleted.

SQL> select count(*) from mytable007;

  COUNT(*)
----------
      0

SQL> commit;

Commit complete.

Thread 1 advanced to log sequence 1342
  Current log# 5 seq# 1342 mem# 0: /u02/oradata/terrydb1/redo05a.log
  Current log# 5 seq# 1342 mem# 1: /u03/oradata/terrydb1/redo05b.log

Media Recovery Log /u03/oradata/terrydb1/archivelogs/1_1341_652101693.dbf
Media Recovery Waiting for thread 1 sequence 1342

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                1219880 bytes
Variable Size          113246936 bytes
Database Buffers       322961408 bytes
Redo Buffers             7168000 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from mytable007;

  COUNT(*)
----------
      0

SQL> select ''||current_scn from v$database;

''||CURRENT_SCN
----------------------------------------
14454841476

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                1219880 bytes
Variable Size          109052632 bytes
Database Buffers       327155712 bytes
Redo Buffers             7168000 bytes
Database mounted.
SQL> flashback database to timestamp sysdate-1/24;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open NORESETLOGS;
alter database open NORESETLOGS
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database;
ORA-00279: change 14454839020 generated at 06/12/2008 14:27:39 needed for
thread 1
ORA-00289: suggestion : /u04/oradata/terrydb1/archivelogs/1_1337_652101693.dbf
ORA-00280: change 14454839020 for thread 1 is in sequence #1337

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 14454840230 generated at 06/12/2008 15:02:25 needed for
thread 1
ORA-00289: suggestion : /u04/oradata/terrydb1/archivelogs/1_1338_652101693.dbf
ORA-00280: change 14454840230 for thread 1 is in sequence #1338
ORA-00278: log file '/u04/oradata/terrydb1/archivelogs/1_1337_652101693.dbf' no
longer needed for this recovery

ORA-00279: change 14454840241 generated at 06/12/2008 15:02:58 needed for
thread 1
ORA-00289: suggestion : /u04/oradata/terrydb1/archivelogs/1_1339_652101693.dbf
ORA-00280: change 14454840241 for thread 1 is in sequence #1339
ORA-00278: log file '/u04/oradata/terrydb1/archivelogs/1_1338_652101693.dbf' no
longer needed for this recovery

ORA-00279: change 14454840298 generated at 06/12/2008 15:03:40 needed for
thread 1
ORA-00289: suggestion : /u04/oradata/terrydb1/archivelogs/1_1340_652101693.dbf
ORA-00280: change 14454840298 for thread 1 is in sequence #1340
ORA-00278: log file '/u04/oradata/terrydb1/archivelogs/1_1339_652101693.dbf' no
longer needed for this recovery

Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT COUNT(*) FROM mytable007;

  COUNT(*)
----------
      0

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                1219880 bytes
Variable Size          109052632 bytes
Database Buffers       327155712 bytes
Redo Buffers             7168000 bytes
Database mounted.
Database opened.

SQL> select ''||current_scn from v$database;

''||CURRENT_SCN
----------------------------------------
14454842515

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                1219880 bytes
Variable Size          109052632 bytes
Database Buffers       327155712 bytes
Redo Buffers             7168000 bytes
Database mounted.
SQL> flashback database to timestamp sysdate-1/24;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from mytable007;

  COUNT(*)
----------
      11

SQL> select ''||current_scn from v$database;

''||CURRENT_SCN
----------------------------------------
14454840148

Thread 1 opened at log sequence 1
  Current log# 4 seq# 1 mem# 0: /u01/oradata/terrydb1/redo04a.log
  Current log# 4 seq# 1 mem# 1: /u02/oradata/terrydb1/redo04b.log

Completed: alter database recover managed standby database disconnect from session
Thu Jun 12 15:23:42 2008
RFS[1]: Physical Standby in the future of Branch(resetlogs_id) 657214335
RFS[1]: Standby database SCN: 3:1569938705  Primary branch SCN: 3:1569937279
RFS[1]: New Archival REDO Branch(resetlogs_id): 657214335  Prior: 652101693
RFS[1]: Archival Activation ID: 0xf487541b Current: 0xf43965fd
RFS[1]: Effect of primary database OPEN RESETLOGS
RFS[1]: Managed Standby Recovery process is active
RFS[1]: No standby redo logfiles created
RFS[1]: Archived Log: '/u03/oradata/terrydb1/archivelogs/1_1_657214335.dbf'
New incarnation branch detected in ArchiveLog, filename

/u03/oradata/terrydb1/archivelogs/1_1_657214335.dbf
Inspection of file changed rdi from 3 to 4
Setting recovery target incarnation to 4
Thu Jun 12 15:23:47 2008
MRP0: Incarnation has changed! Retry recovery...
Thu Jun 12 15:23:47 2008
Errors in file /opt/app/oracle/admin/terrydb1/bdump/terrydb1_mrp0_28813.trc:
ORA-19906: recovery target incarnation changed during recovery
Recovery interrupted!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10341434/viewspace-344538/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10341434/viewspace-344538/