Flashback Database闪回数据库功能极大的降低了由于用户错误导致的数据丢失的恢复成本。这是一种以空间换取缩短恢复时间的解决方案,这是值得的。
1.使用Flashback Database的前提条件
1)启用了flashback database
2)必须打开flash recovery area,若为rac,flash recovery area必须位于共享存储中。
3)必须处于archivelog模式,开启force logging
2.检查数据库是否满足上述条件
1)验证是否开启了flashback功能和force logging
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON FOR
------------------ ---
YES YES
如果flashback_on为no状态,修改方法:http://blog.csdn.net/u011364306/article/details/49249187
如果force_logging为no状态,修改方法如下:
--这里已经开启过,所以重复开启会报错
SQL> alter database force logging;
alter database force logging
*
ERROR at line 1:
ORA-12920: database is already in force logging mode
2)验证是否打开flash recovery area
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 10G
3)验证是否打开归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 25
Next log sequence to archive 27
Current log sequence 27
3.验证数据库可以回滚的最早SCN和TIME
查看v$flasback_database_log,如果数据库要恢复的数据比这个时间还早,则闪回无能为力
--时间格式化
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
--通过v$flasback_database_log查询可以闪回的最早的scn号和时间
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
1372033 2015-10-19 13:36:13
4.创建测试数据
1)创建测试表test_1,test_2,test_3
SQL> create table test_1 as select * from dba_objects;
Table created.
SQL> create table test_2 as select * from test_1;
Table created.
SQL> create table test_3 as select * from test_1;
Table created.
SQL> select count(*) from test_1;
COUNT(*)
----------
86610
SQL> select count(*) from test_2;
COUNT(*)
----------
86610
SQL> select count(*) from test_3;
COUNT(*)
----------
86610
SQL> set time on
2)将test_2给truncate掉,将test_3给drop掉
14:24:38 SQL> truncate table test_2;
Table truncated.
14:24:57 SQL> drop table test_3;
Table dropped.
3)使用闪回数据库功能恢复到时间14:24:38
14:25:05 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--将数据库启动到mount exclusive模式
14:25:42 SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 478154296 bytes
Database Buffers 301989888 bytes
Redo Buffers 2596864 bytes
Database mounted.
--闪回数据库语句
14:26:02 SQL> flashback database to timestamp(to_date('2015-10-19 14:24:38','yyyy-mm-dd hh24:mi:ss'));
Flashback complete.
4)闪回数据库后有2中方法可以修复数据库:
使用open read only一般推荐使用该方法,在以只读模式打开后,可以将需要恢复的表单独exp出来,再通过recover database恢复数据库到原来的状态,再将缺失的数据筛选重新imp到数据库中。这样可以保证对数据库的影响降到最低,保证对数据库的其他表没有影响。
read only打开后查看三张表的状态:
14:27:19 SQL> alter database open read only;
Database altered.
14:27:29 SQL> select count(*) from test_1;
COUNT(*)
----------
86610
14:27:37 SQL> select count(*) from test_2;
COUNT(*)
----------
86610
14:27:39 SQL> select count(*) from test_3;
COUNT(*)
----------
86610
取消闪回的结果,恢复数据库到原来的状态
14:28:16 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
14:28:43 SQL> startup mount
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 478154296 bytes
Database Buffers 301989888 bytes
Redo Buffers 2596864 bytes
Database mounted.
--恢复数据库语句
14:29:05 SQL> recover database;
Media recovery complete.
14:29:10 SQL> alter database open;
Database altered.
14:29:20 SQL> select count(*) from test_1;
COUNT(*)
----------
86610
14:29:30 SQL> select count(*) from test_2;
COUNT(*)
----------
0
14:29:31 SQL> select count(*) from test_3;
select count(*) from test_3
*
ERROR at line 1:
ORA-00942: table or view does not exist
通过上面结果可以查看数据库已经恢复到原来的状态
5)使用open resetlogs,但是通过open resetlogs方式打开数据库后,闪回到时间点之后的数据将全部丢失,很多表都会因此受到影响,因此慎重使用
继续创建test_4表
14:45:40 SQL> create table test_4 as select * from test_1;
Table created.
闪回数据库
14:46:00 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
14:46:15 SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 478154296 bytes
Database Buffers 301989888 bytes
Redo Buffers 2596864 bytes
Database mounted.
14:46:31 SQL> flashback database to timestamp(to_date('2015-10-19 14:24:38','yyyy-mm-dd hh24:mi:ss'));
Flashback complete.
使用open restlogs打开数据库
14:47:28 SQL> alter database open resetlogs;
Database altered.
查看test_1到test_4表
14:47:51 SQL> select count(*) from test_1;
COUNT(*)
----------
86610
14:48:01 SQL> select count(*) from test_2;
COUNT(*)
----------
86610
14:48:03 SQL> select count(*) from test_3;
COUNT(*)
----------
86610
--发现后来创建的test_4表被丢弃
14:48:05 SQL> select count(*) from test_4;
select count(*) from test_4
*
ERROR at line 1:
ORA-00942: table or view does not exist
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29812844/viewspace-1988812/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29812844/viewspace-1988812/