本文深入探讨了闪回技术在RAC环境下的应用，包括闪回删除、闪回表和闪回数据库的操作，以及如何在不同场景下恢复数据。通过实操案例，详细解释了如何利用闪回功能解决数据错误问题，确保数据一致性。
模拟rac环境数据恢复
rac节点：192.168.100.201、192.168.100.202
确认：flashback已经开启
SQL> select name,current_scn,flashback_on from v$database;
查看flashb相关配置
SQL> col name format a32 heading 'parameter'
SQL> col value format a32 heading 'setting'
SQL> select name,value from v$parameter where name like '%flash%' or name like '%recovery%' order by name;
parameter setting
-------------------------------- --------------------------------
db_flashback_retention_target 1440
db_recovery_file_dest +RECOVERY
db_recovery_file_dest_size 19327352832
recovery_parallelism 0
确认flashback相关配置
创建测试数据和表
[oracle@rac2 ~]$ sqlplus jscn/jscn
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Dec 3 15:11:17 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> create table test as (select TABLE_NAME,TABLESPACE_NAME from user_tables);
SQL> select * from test;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SALE_CITY JSCNDATA
SATISFACTION JSCNDATA
SENSITIVE_CHAR JSCNDATA
SHIPPAY_RELATION JSCNDATA
SHIPTYPE JSCNDATA
SHOP_APP_RES JSCNDATA
SHOP_MANAGER_DATA JSCNDATA
SHOP_MANAGER_R_REL JSCNDATA
SHOP_RIGHT JSCNDATA
SHOP_ROLE JSCNDATA
SHOP_ROLE_DATA JSCNDATA
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SHOP_ROLE_R_REL JSCNDATA
SQL> purge recyclebin;
下面模式的是在rac环境下数据恢复的情况
1、flashback drop(闪回删除)
SQL> drop table test;
查看回收站：两个所有节点都能看到
SQL> select OBJECT_NAME,ORIGINAL_NAME from recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$syvRE6lEabHgQAB/AQBDjA==$0 TEST
闪回表（任何节点都能可以执行）
SQL> flashback table test to before drop;
Flashback complete.
验证
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
SQL> select * from test;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SALE_CITY JSCNDATA
SATISFACTION JSCNDATA
SENSITIVE_CHAR JSCNDATA
SHIPPAY_RELATION JSCNDATA
SHIPTYPE JSCNDATA
2. flashback table(闪回表)
有时，不小心，对表进行了错误的update，那么我们就可以通过这个feature来进行恢复，
flashback table testtable to timestamp to_date（(’2009-05-13 00:35:50′, ‘yyyy-mm-dd hh24:mi:ss’)）
这里是利用undo表做恢复的基准，所以有关undo的参数undo_management和undo_retention对这个有影响。具体可以看看这两个参数的说明文档。
注，flashback table是只能处理DML操作过后的语句的恢复，如果在期间有DDL语句操作到这个表上，做flashback table时将
报错ORA-01466: unable to read data - table definition has changed。在做flashback table时先要alter table t enable row movement。
以下为示例：
[oracle@rac2 ~]$ sqlplus jscn/jscn
SQL> create table test_del as ( select TABLE_NAME,TABLESPACE_NAME from user_tables where rownum<=127);
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
-------------------
2011-12-03 16:36:43
SQL> delete from test_del;
127 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test_del;
COUNT(*)
----------
0
SQL> select count(*) from test_del as of timestamp to_timestamp('2011-12-03 16:36:43','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
127
使用flashback table技术恢复数据
SQL> insert into test_del select * from test_del as of timestamp to_timestamp('2011-12-03 16:36:43','yyyy-mm-dd hh24:mi:ss');
127 rows created.
SQL> select count(*) from test_del;
COUNT(*)
----------
127
看到了吗，数据回来了
3、flashback database(闪回数据库)
对应flashback database来说，一般用来处理误删除了user或者一些错误的数据操作。
要使用flash database的特性。我们一定要启动flashback功能，
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
-------------------
2011-12-03 15:34:27
SQL> drop table test;
Table dropped.
SQL> purge recyclebin;
Recyclebin purged.
SQL> flashback table test to before drop;
flashback table test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
这个时候flashback drop就不能用了，那么我们要用flashback database
首先，查询flashback database所能退回到最早时间
SQL> desc v$flashback_database_log;
Name Null? Type
----------------------------------------- -------- ----------------------------
OLDEST_FLASHBACK_SCN NUMBER
OLDEST_FLASHBACK_TIME DATE
RETENTION_TARGET NUMBER
FLASHBACK_SIZE NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
col OLDEST_FLASHBACK_SCN for 999999999 heading 'oldest|flashback|scn#'
col OLDEST_FLASHBACK_TIME for a20 heading 'oldest|flashback|time#'
col RETENTION_TARGET for 999999999 heading 'RETENTION|TARGET'
col FLASHBACK_SIZE for 999999999 heading 'FLASHBACKt|SIZE'
col ESTIMATED_FLASHBACK_SIZE for 999999999 heading 'ESTIMATED|FLASHBACK_SIZE '
select OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'yyyy-mm-dd hh24:mi:ss') OLDEST_FLASHBACK_TIME,
RETENTION_TARGET,FLASHBACK_SIZE,ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;
oldest oldest
flashback flashback RETENTION FLASHBACKt ESTIMATED
scn# time# TARGET SIZE FLASHBACK_SIZE
---------- -------------------- ---------- ---------- ---------------
1471397 2011-12-03 15:04:43 1440 47824896 0
关闭数据库，两个节点执行就可以了
SQL>shutdown immediate;
在rac1执行,启动到mount状态
SQL> startup mount;
SQL> flashback database to timestamp to_date('2011-12-03 15:38:27', 'yyyy-mm-dd hh24:mi:ss');
以read only打开数据库，看表是否回来了
SQL> alter database open read only;
SQL> conn jscn/jscn
SQL> desc test;
ERROR:
ORA-04043: object test does not exist
没回来，再来一次
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to timestamp to_date('2011-12-03 15:34:27', 'yyyy-mm-dd hh24:mi:ss');
SQL> alter database open read only;
SQL> select count(*) from test;
COUNT(*)
----------
148
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open resetlogs;
为了保证数据的一致性，数据库要以resetlogs方式打开。
rac2上也开始实例
SQL> startup
或者基于scn号
SQL>flashback database to scn 5342420;
下同上
rac节点：192.168.100.201、192.168.100.202
确认：flashback已经开启
SQL> select name,current_scn,flashback_on from v$database;
查看flashb相关配置
SQL> col name format a32 heading 'parameter'
SQL> col value format a32 heading 'setting'
SQL> select name,value from v$parameter where name like '%flash%' or name like '%recovery%' order by name;
parameter setting
-------------------------------- --------------------------------
db_flashback_retention_target 1440
db_recovery_file_dest +RECOVERY
db_recovery_file_dest_size 19327352832
recovery_parallelism 0
确认flashback相关配置
创建测试数据和表
[oracle@rac2 ~]$ sqlplus jscn/jscn
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Dec 3 15:11:17 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> create table test as (select TABLE_NAME,TABLESPACE_NAME from user_tables);
SQL> select * from test;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SALE_CITY JSCNDATA
SATISFACTION JSCNDATA
SENSITIVE_CHAR JSCNDATA
SHIPPAY_RELATION JSCNDATA
SHIPTYPE JSCNDATA
SHOP_APP_RES JSCNDATA
SHOP_MANAGER_DATA JSCNDATA
SHOP_MANAGER_R_REL JSCNDATA
SHOP_RIGHT JSCNDATA
SHOP_ROLE JSCNDATA
SHOP_ROLE_DATA JSCNDATA
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SHOP_ROLE_R_REL JSCNDATA
SQL> purge recyclebin;
下面模式的是在rac环境下数据恢复的情况
1、flashback drop(闪回删除)
SQL> drop table test;
查看回收站：两个所有节点都能看到
SQL> select OBJECT_NAME,ORIGINAL_NAME from recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$syvRE6lEabHgQAB/AQBDjA==$0 TEST
闪回表（任何节点都能可以执行）
SQL> flashback table test to before drop;
Flashback complete.
验证
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
SQL> select * from test;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SALE_CITY JSCNDATA
SATISFACTION JSCNDATA
SENSITIVE_CHAR JSCNDATA
SHIPPAY_RELATION JSCNDATA
SHIPTYPE JSCNDATA
2. flashback table(闪回表)
有时，不小心，对表进行了错误的update，那么我们就可以通过这个feature来进行恢复，
flashback table testtable to timestamp to_date（(’2009-05-13 00:35:50′, ‘yyyy-mm-dd hh24:mi:ss’)）
这里是利用undo表做恢复的基准，所以有关undo的参数undo_management和undo_retention对这个有影响。具体可以看看这两个参数的说明文档。
注，flashback table是只能处理DML操作过后的语句的恢复，如果在期间有DDL语句操作到这个表上，做flashback table时将
报错ORA-01466: unable to read data - table definition has changed。在做flashback table时先要alter table t enable row movement。
以下为示例：
[oracle@rac2 ~]$ sqlplus jscn/jscn
SQL> create table test_del as ( select TABLE_NAME,TABLESPACE_NAME from user_tables where rownum<=127);
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
-------------------
2011-12-03 16:36:43
SQL> delete from test_del;
127 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test_del;
COUNT(*)
----------
0
SQL> select count(*) from test_del as of timestamp to_timestamp('2011-12-03 16:36:43','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
127
使用flashback table技术恢复数据
SQL> insert into test_del select * from test_del as of timestamp to_timestamp('2011-12-03 16:36:43','yyyy-mm-dd hh24:mi:ss');
127 rows created.
SQL> select count(*) from test_del;
COUNT(*)
----------
127
看到了吗，数据回来了
3、flashback database(闪回数据库)
对应flashback database来说，一般用来处理误删除了user或者一些错误的数据操作。
要使用flash database的特性。我们一定要启动flashback功能，
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
-------------------
2011-12-03 15:34:27
SQL> drop table test;
Table dropped.
SQL> purge recyclebin;
Recyclebin purged.
SQL> flashback table test to before drop;
flashback table test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
这个时候flashback drop就不能用了，那么我们要用flashback database
首先，查询flashback database所能退回到最早时间
SQL> desc v$flashback_database_log;
Name Null? Type
----------------------------------------- -------- ----------------------------
OLDEST_FLASHBACK_SCN NUMBER
OLDEST_FLASHBACK_TIME DATE
RETENTION_TARGET NUMBER
FLASHBACK_SIZE NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
col OLDEST_FLASHBACK_SCN for 999999999 heading 'oldest|flashback|scn#'
col OLDEST_FLASHBACK_TIME for a20 heading 'oldest|flashback|time#'
col RETENTION_TARGET for 999999999 heading 'RETENTION|TARGET'
col FLASHBACK_SIZE for 999999999 heading 'FLASHBACKt|SIZE'
col ESTIMATED_FLASHBACK_SIZE for 999999999 heading 'ESTIMATED|FLASHBACK_SIZE '
select OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'yyyy-mm-dd hh24:mi:ss') OLDEST_FLASHBACK_TIME,
RETENTION_TARGET,FLASHBACK_SIZE,ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;
oldest oldest
flashback flashback RETENTION FLASHBACKt ESTIMATED
scn# time# TARGET SIZE FLASHBACK_SIZE
---------- -------------------- ---------- ---------- ---------------
1471397 2011-12-03 15:04:43 1440 47824896 0
关闭数据库，两个节点执行就可以了
SQL>shutdown immediate;
在rac1执行,启动到mount状态
SQL> startup mount;
SQL> flashback database to timestamp to_date('2011-12-03 15:38:27', 'yyyy-mm-dd hh24:mi:ss');
以read only打开数据库，看表是否回来了
SQL> alter database open read only;
SQL> conn jscn/jscn
SQL> desc test;
ERROR:
ORA-04043: object test does not exist
没回来，再来一次
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to timestamp to_date('2011-12-03 15:34:27', 'yyyy-mm-dd hh24:mi:ss');
SQL> alter database open read only;
SQL> select count(*) from test;
COUNT(*)
----------
148
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open resetlogs;
为了保证数据的一致性，数据库要以resetlogs方式打开。
rac2上也开始实例
SQL> startup
或者基于scn号
SQL>flashback database to scn 5342420;
下同上
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
