Oracle 闪回技术

167次阅读

共计 16365 个字符，预计需要花费 41 分钟才能阅读完成。

一、配置闪回数据库
1、数据库处于归档日志模式
SQL> select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG
2、创建闪回恢复区
SQL> alter system set db_recovery_file_dest_size=10G;
System altered.
SQL> alter system set db_recovery_file_dest=’D:\Oracle\product\11.2.0\dbhome_1\RDBMS’;
System altered.
3、设置闪回保留目标时间
SQL> alter system set db_flashback_retention_target=240;
System altered.
–DB_FLASHBACK_RETENTION_TARGET 参数控制保留时间，单位是分钟，默认值是 1 天。闪回日志空间以循环的方式重用，更新的数据将覆盖旧的数据。(本例为保留 4 小时)
4、关闭数据库并启动到 MOUNT 状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3373858816 bytes
Fixed Size 2180424 bytes
Variable Size 1845496504 bytes
Database Buffers 1509949440 bytes
Redo Buffers 16232448 bytes
Database mounted.
5、启用闪回日志记录并打开数据库
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
6、查看是否启用闪回日志记录
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
YES
二、使用 sqlplus 闪回数据库
1、创建一个临时表
SQL> create table t as select * from dba_objects;
Table created.
2、查询当前系统时间表
SQL> select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;
TO_CHAR(SYSDATE,’YY
——————-
2016-03-15 11:34:21
3、对 t 表做一些 DML 操作
SQL> delete from t;
72464 rows deleted.
SQL> commit;
Commit complete.
SQL> insert into t select * from dba_objects where rownum<=100;
100 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
———-
100
4、把数据库闪回到步骤 2 查询出的时间
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3373858816 bytes
Fixed Size 2180424 bytes
Variable Size 1845496504 bytes
Database Buffers 1509949440 bytes
Redo Buffers 16232448 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp(‘2016-03-15 11:34:21′,’yyyy-mm-dd hh24:mi:ss’);– 可以接受时间戳或系统变更号(SCN) 参数，不接受日期或日志世界的序列号
Flashback complete.
5、以只读方式打开数据库查询是否闪回成功 – 注意使用 read only 方式打开数据库，验证闪回情况。
SQL> alter database open read only;
Database altered.
SQL> select count(*) from t;
COUNT(*)
———-
72464
6、闪回成功后，以 resetlogs 方式打开数据库
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3373858816 bytes
Fixed Size 2180424 bytes
Variable Size 1845496504 bytes
Database Buffers 1509949440 bytes
Redo Buffers 16232448 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from t;
COUNT(*)
———-
72464
数据库闪回成功
注：如果 flashback 闪回的数据库时间太早，可以使用 RECOVER DATABASE UNTILE TIME times 恢复到后边的时间点。
– 也可以使用 RMAN 进行闪回
RMAN>flashback database to time = to_date(‘2016-03-15 11:34:21′,’yyyy-mm-dd hh24:mi:ss’);
RMAN>flashback database to scn = 2728665;
RMAN>flash backup to sequence = 2123 thread = 1;
三、限制生成的闪回数据量
1、关闭表空间闪回属性
ALTER TABLESPACE tablespace_name FLASHBACK OFF;
2、开启表空间闪回属性
ALTER TABLESPACE tablespace_name FLASHBACK ON;– 只能在 mount 状态下执行
3、查看表空间的闪回属性
select name,flashback_on from v$tablespace;
四、设置 sqlplus 提示符
SQL> set sqlprompt “_user’@’_connect_identifier>”
SYS@ocp>
– 为了对所有的 sqlplus 会话自动设置 sqlprompt。将上面的命令放置在 ORACLE_HOME/sqlplus/admin 目录中的 glogin.sql 文件中
五、闪回删除
1、创建一个练习用户
SYS@ocp>create user dropper identified by dropper;
User created.
SYS@ocp>grant create session,resource to dropper;
Grant succeeded.
SYS@ocp>connect dropper/dropper;
Connected.
2、创建一个带有索引和约束的表，并插入一行
DROPPER@ocp>create table names(name varchar2(10));
Table created.
DROPPER@ocp>create index name_idx on names(name);
Index created.
DROPPER@ocp>alter table names add constraint name_u unique (name);
Table altered.
DROPPER@ocp>insert into names values(‘John’);
1 row created.
DROPPER@ocp>commit;
Commit complete.
3、确认模式的内容
DROPPER@ocp>select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
————————————-
NAME_IDX INDEX
NAMES TABLE
DROPPER@ocp>select constraint_name,constraint_type,table_name from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
—————————— – ——————————
NAME_U U NAMES
4、删除该表
DROPPER@ocp>drop table names;
Table dropped.
5、查询回收站查看原始名称到回收站名称的映射
DROPPER@ocp>select object_name,original_name,type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE
—————————— —————— ————————-
BIN$q+6VgWdBRGOykqOJfCyZNg==$0 NAME_IDX INDEX
BIN$u1TeIIlLS3isIPDvpSTblQ==$0 NAMES TABLE
– 注：视图并没有显示约束
6、可以使用回收站的对象名进行查询，但不可做 DML 语句
DROPPER@ocp>select * from “BIN$u1TeIIlLS3isIPDvpSTblQ==$0”;
NAME
———-
John
DROPPER@ocp>insert into “BIN$u1TeIIlLS3isIPDvpSTblQ==$0” values(‘Root’);
insert into “BIN$u1TeIIlLS3isIPDvpSTblQ==$0” values(‘Root’)
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
7、使用 FLASHBACK drop 恢复表
DROPPER@ocp>flashback table names to before drop;
Flashback complete.
8、查询模式中的内容
DROPPER@ocp>select * from names;
NAME
———-
John
DROPPER@ocp>select object_name,original_name,type from user_recyclebin;
no rows selected
DROPPER@ocp>select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
————————————————-
BIN$q+6VgWdBRGOykqOJfCyZNg==$0 INDEX
NAMES TABLE
DROPPER@ocp>select constraint_name,constraint_type,table_name from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
—————————— – ——————————
BIN$cLAR1tu9Toi3u5qKdAbvIw==$0 U NAMES
9、将索引和约束重命名回原先的名称
DROPPER@ocp>alter index “BIN$q+6VgWdBRGOykqOJfCyZNg==$0” rename to name_idx;
Index altered.
DROPPER@ocp>alter table names rename constraint “BIN$cLAR1tu9Toi3u5qKdAbvIw==$0” to name_u;
Table altered.
DROPPER@ocp>select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
———————————-
NAME_IDX INDEX
NAMES TABLE
DROPPER@ocp>select constraint_name,constraint_type,table_name from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
—————————— – ——————————
NAME_U U NAMES
10、使用 SYS 用户删除 DROPPER 模式
SYS@ocp>drop user dropper cascade;
User dropped.
11、查询 DBA_RECYCLEBIN 视图来证实确实删除了用户 DROPPER 拥有的所有对象。
SYS@ocp>select count(*) from dba_recyclebin where owner=’DROPPER’;
COUNT(*)
———-
0
注：如果回收站中两两个表的原始名称相同，默认情况下，Flashback Drop 命令总是恢复最新版本的表，但如果不是想要的版本，可以指定希望恢复的版本的回收站名称，而不是原先的名称。
SQL>flashback table “BIN$q+6VgWdBRGOykqOJfCyZNg==$0” to before drop;
六、管理回收站
SQL>show recyclebin;
user_recyclebin
dba_recyclebin
注：Flashback Drop 不适用于存储在 SYSTEM 表空间中的表，直接将它们删掉并清除了
永久清除删掉的对象
DROP TABLE table_name PURGE;– 删除表并不将它转移到回收站
PURGE TABLE table_name;– 从回收站中清除表。如果存在多个具有相同原始名称的对象，清除时间最久的对象。也可以通过指定回收站名称来避免这种混淆。
PURGE INDEX index_name;– 从回收站中消除索引。同样可以指定原始名称或回收站名称。
PURGE TABLESPACE tablespace_name;– 从表空间中清除所有删除的对象。
PURGE TABLESPACE tablespace_name USER user_name;– 从表空间中清除属于一个用户的所有删除的对象。
PURGE USER_RECYCLEBIN;– 清除用户删除文件的所有对象。
PURGE DBA_RECYCLEBIN;– 清除所有删除的对象，需要 dba 权限。
七、闪回查询
– 所有形式的闪回查询依赖撤销数据来重构它在过去某个时间点的数据。
1、基本的闪回查询
1.1 创建测试表并插入测试数据
USER1@mydb>create table regions (region_id number,region_name varchar2(20));
Table created.
USER1@mydb>insert into regions values(1,’Europe’);
1 row created.
USER1@mydb>insert into regions values(2,’Americas’);
1 row created.
USER1@mydb>insert into regions values(3,’Asia’);
1 row created.
USER1@mydb>insert into regions values(4,’Middle East’);
1 row created.
USER1@mydb>commit;
Commit complete.
1.2 查询系统当前时间，删除部分数据并确认
USER1@mydb>select sysdate from dual;
SYSDATE
——————-
2016-03-15 19:08:16
USER1@mydb>delete from regions where region_name like ‘A%’;
2 rows deleted.
USER1@mydb>commit;
Commit complete.
USER1@mydb>select * from regions;
REGION_ID REGION_NAME
———- ——————–
1 Europe
4 Middle East
1.3 进行闪回查询
USER1@mydb>select * from regions as of timestamp to_timestamp(‘2016-03-15 19:08:16′,’yyyy-mm-dd hh24:mi:ss’);
REGION_ID REGION_NAME
———- ——————–
1 Europe
2 Americas
3 Asia
4 Middle East
1.4 查询闪回时间点到现在的差异
USER1@mydb>select * from regions as of timestamp to_timestamp(‘2016-03-15 19:08:16′,’yyyy-mm-dd hh24:mi:ss’) minus select * from regions;
REGION_ID REGION_NAME
———- ——————–
2 Americas
3 Asia
可以使用 DBMS_FLASHBACK 程序包将整个会话回退到过去某个时间，对其他会话没有影响。处于闪回模式中不支持 DMS 语句。
USER1@mydb>execute dbms_flashback.enable_at_time(to_timestamp(‘2016-03-15 19:08:16′,’yyyy-mm-dd hh24:mi:ss’));
PL/SQL procedure successfully completed.
USER1@mydb>select * from regions;
REGION_ID REGION_NAME
———- ——————–
1 Europe
2 Americas
3 Asia
4 Middle East
USER1@mydb>execute dbms_flashback.disable;
PL/SQL procedure successfully completed.
select * from table_name as of timestamp(systimestamp-interval ’30’ minute);
select * from table_name as of timestamp(systimestamp-interval ‘1’ hour);
2、闪回表查询 – 启用表闪回的第一步是在表上支持行移动。
2.1 创建测试表插入测试数据
HR@mydb>create table dept (dept_id number,dept_name varchar2(20));
Table created.
HR@mydb>alter table dept add constraint pk_dept primary key (dept_id);
Table altered.
HR@mydb>create table emp (emp_id number,name varchar2(20),dept_id number);
Table created.
HR@mydb>alter table emp add constraint fk_emp foreign key (dept_id) references dept(dept_id);
Table altered.
HR@mydb>insert into dept values(1,’SUPPORT’);
1 row created.
HR@mydb>select * from dept;
DEPT_ID DEPT_NAME
———- ——————–
1 SUPPORT
HR@mydb>commit;
Commit complete.
HR@mydb>insert into emp values(101,’John’,1);
1 row created.
HR@mydb>commit;
Commit complete.
HR@mydb>select * from emp;
EMP_ID NAME DEPT_ID
———- ——————– ———-
101 John 1
2.2、查看当前系统时间
HR@mydb>select sysdate from dual;
SYSDATE
——————-
2016-03-15 21:37:36
2.3、删除测试表数据
HR@mydb>delete from emp where emp_id=101;
1 row deleted.
HR@mydb>delete from dept where dept_id=1;
1 row deleted.
HR@mydb>commit;
Commit complete.
2.4、开始闪回表
flashback table table_name to timestamp to_timestamp(systimestamp-interval ’30’ minute);
HR@mydb>flashback table emp to timestamp to_timestamp(‘2016-03-15 21:37:36′,’yyyy-mm-dd hh24:mi:ss’);
flashback table emp to timestamp to_timestamp(‘2016-03-15 21:37:36′,’yyyy-mm-dd hh24:mi:ss’)
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
– 提示需要打开表的 row movement
HR@mydb>alter table emp enable row movement;
Table altered.
HR@mydb>alter table dept enable row movement;
Table altered.
HR@mydb>flashback table emp to timestamp to_timestamp(‘2016-03-15 21:37:36′,’yyyy-mm-dd hh24:mi:ss’);
flashback table emp to timestamp to_timestamp(‘2016-03-15 21:37:36′,’yyyy-mm-dd hh24:mi:ss’)
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (HR.FK_EMP) violated – parent key not found
– 提示有外键约束
– 两个表同时闪回避免约束问题
HR@mydb>flashback table emp,dept to timestamp to_timestamp(‘2016-03-15 21:37:36′,’yyyy-mm-dd hh24:mi:ss’);
Flashback complete.
2.5、检查闪回结果
HR@mydb>select * from dept;
DEPT_ID DEPT_NAME
———- ——————–
1 SUPPORT
HR@mydb>select * from emp;
EMP_ID NAME DEPT_ID
———- ——————– ———-
101 John 1
– 语法的变型允许闪回到一个系统变更号并在操作期间激活 DML 触发器。
flashback table emp,dept to scn 6539425 enable triggers;
3、闪回版本查询
– 使用 VERSIONS BETWEEN 关键字
– 根据 scn 的返回版本
select emp_id, versions_xid,versions_startscn,versions_endscn,versions_operation from emp versions between scn minvalue and maxvalue where emp_id=101;
– 根据时间戳的返回版本
select emp_id, versions_xid,versions_starttime,versions_endtime,versions_operation from emp versions between timestamp (systimestamp – 25/1440) and systimestamp where emp_id=101;
4、闪回事务
– 使用闪回事务需要开启库的最小附加日志 alter database add supplemental log data;
4.1 创建测试表并插入数据
USER1@mydb>create table countries(name varchar2(10));
Table created.
USER1@mydb>alter table countries enable row movement;
Table altered.
USER1@mydb>insert into countries values(‘Zambia’);
1 row created.
USER1@mydb>insert into countries values(‘Zimbabwe’);
1 row created.
USER1@mydb>insert into countries values(‘Zamibia’);
1 row created.
USER1@mydb>commit;
Commit complete.
USER1@mydb>select * from countries;
NAME
———-
Zambia
Zimbabwe
Zamibia
4.2 对测试表进行更新
USER1@mydb>update countries set name=’Namibia’;
3 rows updated.
USER1@mydb>commit;
Commit complete.
USER1@mydb>select * from countries;
NAME
———-
Namibia
Namibia
Namibia
4.3 查询行的所有版本，确定错误的事务 ID
USER1@mydb>select name,versions_xid,versions_operation from countries versions between scn minvalue and maxvalue ;
NAME VERSIONS_XID V
———- —————- –
Namibia 080007000F040000 U
Namibia 080007000F040000 U
Namibia 080007000F040000 U
Zamibia 040009003C030000 I
Zimbabwe 040009003C030000 I
Zambia 040009003C030000 I
6 rows selected.
4.4 查询 FLASHBACK_TARNSACTION_QUERY 视图看到该事务影响的行，并给出如何取消影响的 SQL 语句。
–XID 列是 RAW 类型的，VERSIONS_XID 伪列是十六进制的，需要使用类型强制转换函数。
SYS@mydb>select operation,undo_sql from flashback_transaction_query where xid=hextoraw(‘080007000F040000’);
OPERATIO UNDO_SQL
——– ————————————————————————————————————————
UPDATE update “USER1″.”COUNTRIES” set “NAME” = ‘Zamibia’ where ROWID = ‘AAASjKAAEAAAACVAAC’;
UPDATE update “USER1″.”COUNTRIES” set “NAME” = ‘Zimbabwe’ where ROWID = ‘AAASjKAAEAAAACVAAB’;
UPDATE update “USER1″.”COUNTRIES” set “NAME” = ‘Zambia’ where ROWID = ‘AAASjKAAEAAAACVAAA’;
– 还可以使用 DBMS_FLASHBACK 包进行闪回事务
execute sys.dbms_flashback.transaction_backout(numtxns=>2,xids=>sys.xid_array(‘080007000F040000’),options=>dbms_flashback.cascade);
5、闪回数据归档 – 保证将表闪回到过去任何时间。
可以在预先存在的表空间中创建归档，但在新表空间中更加明智。
create flashback archive default hrarch tablespace fbda1 quota 10g retention 5 year;
–default 关键字表示除非另行说明将用作所有表的归档。
alter flashback archive hrarch set default;
–quota 显示归档在表空间中占用的空间。可以在原有的表空间或另一个表空间中添加更多的空间。
alter flashback archive hrarch add tablespace fbda2 quota 10g;
– 可以调整保留时间
alter flashback archive hrarch modify retention 7 year;
– 数据超过了指定的保留期限，FBDA 进程自动从归档中删除。在过期之前也可以手动进行删除
alter flashback archive hrarch purge before timestamp to_timestamp(’01-01-2009′,’dd-mm-yyyy’);
– 只有 FLASHBACK ARCHIVE ADMINISTER 系统权限能够创建、修改或删除归档以及控制归档的保留和清除。
grant flashback archive administer to fbdaadmin;
– 必须授予用户归档的 FLASHBACK ARCHIVE 权限以便能够对表进行归档。
grant flashback archive on hrarch to hr;
– 启用表的归档保护
alter table hr.employees flashback archive hrarch;
– 删除表的归档保护
alter table hr.employees no flashback archive;
– 删除闪回数据归档
drop flashback archive hrarch;