Flashback Database 闪回数据库功能测试
1.使用Flashback Database的前提条件
1)启用了flashback database
2)必须打开flash recovery area,若为RAC,flash recovery area必须位于共享存储中。
3)必须处于archivelog模式,开启FORCE LOGGING
2.确认上面的前提条件是否满足
1)验证是否启用了flashback database并确认FORCE LOGGIN是否开启
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON FORCE_LOGGING
------------------ ---------------------------------------
YES YES
若flashback_on为“NO”,请开启flashback database,具体步骤如下:
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=3g SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET db_recovery_file_dest='/data/oradata/ocrl/flashback ' SCOPE=BOTH;
System altered.
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
Oracle 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 849530880 bytes
Fixed Size 1377896 bytes
Variable Size 637536664 bytes
Database Buffers 205520896 bytes
Redo Buffers 5095424 bytes
数据库装载完毕。
SQL> alter database flashback on;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
若force_logging为“NO”,请使如下SQL语句开启
SQL>alter database force logging;
2)数据库是否处于archivelog模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oradata/ocrl/archivelog
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
3.确认数据库可以前滚到的SCN和Time的方法
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
9356807 2017-02-13 14:38:59
4.闪回数据功能测试
1)创建测试表:flash1,flash2,flash3;
SQL> create table flash1 as select * from dba_objects;
Table created.
SQL> create table flash2 as select * from flash1;
Table created.
SQL> create table flash3 as select * from flash1;
Table created.
SQL> select count(*) from flash1;
COUNT(*)
----------
90687
SQL> select count(*) from flash2;
COUNT(*)
----------
90687
SQL> select count(*) from flash3;
COUNT(*)
----------
90687
QL> set time on
09:53:19 SQL> select sysdate from dual;
SYSDATE
-------------------
2017-02-14 09:53:30
2)truncate表flash2、drop掉表flash3
09:53:30 SQL> truncate table flash2;
Table truncated.
09:56:14 SQL> drop table flash3;
Table dropped.
3)使用Flashback Database功能进行恢复到删除前的时间点2017-02-14 09:53:30
9:56:28 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
09:57:01 SQL> startup mount exclusive;
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
Database mounted.
09:57:24 SQL> flashback database to timestamp(to_date('2017-02-14 09:53:30','yyyy-mm-dd hh24:mi:ss'));
Flashback complete.
4)4)闪回后修复数据库两种方式之一:open read only
推荐使用这样的方法进行恢复,因为在read only方式打开之后,将需要恢复的表EXP导出,然后通过recover database将数据库恢复到原状态,再将缺失的数据IMP到数据库中。这样操作对数据库的影响可以降低到最小,可以保证其他表没有数据的丢失。
09:59:45 SQL> alter database open read only;
Database altered.
10:00:08 SQL> select count(*) from flash1;
COUNT(*)
----------
90687
10:00:20 SQL> select count(*) from flash2;
COUNT(*)
----------
90687
10:00:32 SQL> select count(*) from flash3;
COUNT(*)
----------
90687
取消闪回结果,恢复到闪回前状态的方法:
0:00:45 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:01:22 SQL> startup mount
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
Database mounted.
10:01:35 SQL> recover database;
Media recovery complete.
10:01:44 SQL> alter database open;
Database altered.
10:01:52 SQL> select count(*) from flash1;
COUNT(*)
----------
90687
10:02:04 SQL> select count(*) from flash2;
COUNT(*)
----------
0
10:02:14 SQL> select count(*) from flash3;
select count(*) from flash3
*
ERROR at line 1:
ORA-00942: table or view does not exist
可见,通过上面的recover后,数据库恢复到了闪回前的状态。
5)闪回后修复数据库两种方式之二:open resetlogs
通过open resetlogs方式打开数据库后,很显然,闪回到时间点之后的数据将全部丢失,慎用!
10:02:21 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:03:04 SQL> startup mount
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
Database mounted.
10:05:05 SQL> flashback database to timestamp(to_date('2017-02-14 09:53:30','yyyy-mm-dd hh24:mi:ss'));
Flashback complete.
10:06:01 SQL> alter database open resetlogs;
Database altered.
10:06:25 SQL> select count(*) from flash1;
COUNT(*)
----------
90687
10:06:48 SQL> select count(*) from flash2;
COUNT(*)
----------
90687
10:06:55 SQL> select count(*) from flash3;
COUNT(*)
----------
90687
5.小结
这里对Flashback Database闪回数据库的语法进行总结。闪回数据库可以在SQL*Plus环境和RMAN环境下使用。
基于时间戳进行闪回数据库操作方法:
Flashback Database to timestamp(to_date('2017-02-14 09:53:30','yyyy-mm-dd hh24:mi:ss'));
Flashback Database to timestamp(sysdate-1/24);
基于SCN进行闪回数据库操作方法:
Flashback Database to 1321427;
1.使用Flashback Database的前提条件
1)启用了flashback database
2)必须打开flash recovery area,若为RAC,flash recovery area必须位于共享存储中。
3)必须处于archivelog模式,开启FORCE LOGGING
2.确认上面的前提条件是否满足
1)验证是否启用了flashback database并确认FORCE LOGGIN是否开启
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON FORCE_LOGGING
------------------ ---------------------------------------
YES YES
若flashback_on为“NO”,请开启flashback database,具体步骤如下:
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=3g SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET db_recovery_file_dest='/data/oradata/ocrl/flashback ' SCOPE=BOTH;
System altered.
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
Oracle 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 849530880 bytes
Fixed Size 1377896 bytes
Variable Size 637536664 bytes
Database Buffers 205520896 bytes
Redo Buffers 5095424 bytes
数据库装载完毕。
SQL> alter database flashback on;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
若force_logging为“NO”,请使如下SQL语句开启
SQL>alter database force logging;
2)数据库是否处于archivelog模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oradata/ocrl/archivelog
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
3.确认数据库可以前滚到的SCN和Time的方法
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
9356807 2017-02-13 14:38:59
4.闪回数据功能测试
1)创建测试表:flash1,flash2,flash3;
SQL> create table flash1 as select * from dba_objects;
Table created.
SQL> create table flash2 as select * from flash1;
Table created.
SQL> create table flash3 as select * from flash1;
Table created.
SQL> select count(*) from flash1;
COUNT(*)
----------
90687
SQL> select count(*) from flash2;
COUNT(*)
----------
90687
SQL> select count(*) from flash3;
COUNT(*)
----------
90687
QL> set time on
09:53:19 SQL> select sysdate from dual;
SYSDATE
-------------------
2017-02-14 09:53:30
2)truncate表flash2、drop掉表flash3
09:53:30 SQL> truncate table flash2;
Table truncated.
09:56:14 SQL> drop table flash3;
Table dropped.
3)使用Flashback Database功能进行恢复到删除前的时间点2017-02-14 09:53:30
9:56:28 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
09:57:01 SQL> startup mount exclusive;
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
Database mounted.
09:57:24 SQL> flashback database to timestamp(to_date('2017-02-14 09:53:30','yyyy-mm-dd hh24:mi:ss'));
Flashback complete.
4)4)闪回后修复数据库两种方式之一:open read only
推荐使用这样的方法进行恢复,因为在read only方式打开之后,将需要恢复的表EXP导出,然后通过recover database将数据库恢复到原状态,再将缺失的数据IMP到数据库中。这样操作对数据库的影响可以降低到最小,可以保证其他表没有数据的丢失。
09:59:45 SQL> alter database open read only;
Database altered.
10:00:08 SQL> select count(*) from flash1;
COUNT(*)
----------
90687
10:00:20 SQL> select count(*) from flash2;
COUNT(*)
----------
90687
10:00:32 SQL> select count(*) from flash3;
COUNT(*)
----------
90687
取消闪回结果,恢复到闪回前状态的方法:
0:00:45 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:01:22 SQL> startup mount
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
Database mounted.
10:01:35 SQL> recover database;
Media recovery complete.
10:01:44 SQL> alter database open;
Database altered.
10:01:52 SQL> select count(*) from flash1;
COUNT(*)
----------
90687
10:02:04 SQL> select count(*) from flash2;
COUNT(*)
----------
0
10:02:14 SQL> select count(*) from flash3;
select count(*) from flash3
*
ERROR at line 1:
ORA-00942: table or view does not exist
可见,通过上面的recover后,数据库恢复到了闪回前的状态。
5)闪回后修复数据库两种方式之二:open resetlogs
通过open resetlogs方式打开数据库后,很显然,闪回到时间点之后的数据将全部丢失,慎用!
10:02:21 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:03:04 SQL> startup mount
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
Database mounted.
10:05:05 SQL> flashback database to timestamp(to_date('2017-02-14 09:53:30','yyyy-mm-dd hh24:mi:ss'));
Flashback complete.
10:06:01 SQL> alter database open resetlogs;
Database altered.
10:06:25 SQL> select count(*) from flash1;
COUNT(*)
----------
90687
10:06:48 SQL> select count(*) from flash2;
COUNT(*)
----------
90687
10:06:55 SQL> select count(*) from flash3;
COUNT(*)
----------
90687
5.小结
这里对Flashback Database闪回数据库的语法进行总结。闪回数据库可以在SQL*Plus环境和RMAN环境下使用。
基于时间戳进行闪回数据库操作方法:
Flashback Database to timestamp(to_date('2017-02-14 09:53:30','yyyy-mm-dd hh24:mi:ss'));
Flashback Database to timestamp(sysdate-1/24);
基于SCN进行闪回数据库操作方法:
Flashback Database to 1321427;