1.创建测试环境并获取当前SCN
SYS@PROD1>conn scott/tiger;
Connected.
SCOTT@PROD1>create table fbdb_scn as select 1 as id,dbms_flashback.get_system_change_number as scn,sysdate as dd from dual;
Table created.
SCOTT@PROD1>select * from fbdb_scn;
ID SCN DD
---------- ---------- -------------------
1 1228382 2016-12-26 15:36:32
得到此时的SCN，本次测试将数据库倒带到此处，也就是最终的结果，fbdb_scn表中只有一条记录
SCOTT@PROD1>select dbms_flashback.get_system_change_number as scn from dual;
SCN
----------
1228438
(或者在sys用户select current_scn from v$database;)
SCOTT@PROD1>insert into fbdb_scn select 2 as id,dbms_flashback.get_system_change_number as scn,sysdate as dd from dual;
1 row created.
SCOTT@PROD1>commit;
Commit complete.
SCOTT@PROD1>select * from fbdb_scn;
ID SCN DD
---------- ---------- -------------------
1 1228382 2016-12-26 15:36:32
2 1228467 2016-12-26 15:38:56
2.删除scott用户
SYS@PROD1>drop user scott cascade;
User dropped.
3.数据库启动到mount模式，为闪回数据库做准备
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
正常关库，一致性状态保存。启动mount，利用控制文件找到闪回日志，并应用闪回日志。
4.执行基于 SCN 闪回数据库操作
SYS@PROD1>flashback database to scn 1228438;
Flashback complete.
5.以 RESETLOGS 选项打开数据库
先以read only方式打开数据库，看是否满足预期的结果。
SYS@PROD1>alter database open read only;
Database altered.
SYS@PROD1>select username from dba_users where username='SCOTT';
USERNAME
------------------------------
SCOTT
SYS@PROD1>select * from scott.fbdb_scn;
ID SCN DD
---------- ---------- -------------------
1 1228382 2016-12-26 15:36:32
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>startup;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@PROD1>alter database open resetlogs;
Database altered.
SYS@PROD1>select * from scott.fbdb_scn;
ID SCN DD
---------- ---------- -------------------
1 1228382 2016-12-26 15:36:32
SYS@PROD1>conn scott/tiger;
Connected.
SCOTT@PROD1>create table fbdb_scn as select 1 as id,dbms_flashback.get_system_change_number as scn,sysdate as dd from dual;
Table created.
SCOTT@PROD1>select * from fbdb_scn;
ID SCN DD
---------- ---------- -------------------
1 1228382 2016-12-26 15:36:32
得到此时的SCN，本次测试将数据库倒带到此处，也就是最终的结果，fbdb_scn表中只有一条记录
SCOTT@PROD1>select dbms_flashback.get_system_change_number as scn from dual;
SCN
----------
1228438
(或者在sys用户select current_scn from v$database;)
SCOTT@PROD1>insert into fbdb_scn select 2 as id,dbms_flashback.get_system_change_number as scn,sysdate as dd from dual;
1 row created.
SCOTT@PROD1>commit;
Commit complete.
SCOTT@PROD1>select * from fbdb_scn;
ID SCN DD
---------- ---------- -------------------
1 1228382 2016-12-26 15:36:32
2 1228467 2016-12-26 15:38:56
2.删除scott用户
SYS@PROD1>drop user scott cascade;
User dropped.
3.数据库启动到mount模式，为闪回数据库做准备
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
正常关库，一致性状态保存。启动mount，利用控制文件找到闪回日志，并应用闪回日志。
4.执行基于 SCN 闪回数据库操作
SYS@PROD1>flashback database to scn 1228438;
Flashback complete.
5.以 RESETLOGS 选项打开数据库
先以read only方式打开数据库，看是否满足预期的结果。
SYS@PROD1>alter database open read only;
Database altered.
SYS@PROD1>select username from dba_users where username='SCOTT';
USERNAME
------------------------------
SCOTT
SYS@PROD1>select * from scott.fbdb_scn;
ID SCN DD
---------- ---------- -------------------
1 1228382 2016-12-26 15:36:32
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>startup;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@PROD1>alter database open resetlogs;
Database altered.
SYS@PROD1>select * from scott.fbdb_scn;
ID SCN DD
---------- ---------- -------------------
1 1228382 2016-12-26 15:36:32
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31400681/viewspace-2131404/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
