Oracle配置和使用闪回

2023-08-27 22:44:28

环境：RHEL 6.4 + Oracle 11.2.0.4

目录：

一、闪回查询

1.1 闪回查询举例
1.2 闪回版本查询举例

二、闪回事物

2.1 闪回事物查询的先决条件
2.2 闪回事物查询

三、闪回表

四、Flashback Data Archive

五、闪回数据库

5.1 配置闪回数据库
5.2 使用闪回数据库
5.3 监视闪回数据库

Reference

一、闪回查询

```
-- 初始化参数undo_retention
undo_retention=1800 (1800s=0.5h)，若条件允许，可以适当调大此参数。
-- 是否强制保留undo_retention的时间
select tablespace_name, retention from dba_tablespaces where tablespace_name like 'UNDO%';
alter tablespace undotbs1 retention guarantee;
```

1.1 闪回查询举例

select count(1) from t1 as of timestamp systimestamp - interval '5' minute;
```
--操作T1表删除一条记录
SQL> select count(1) from t1;
COUNT(1)
----------
100
SQL> delete from t1 where id=1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from t1;
COUNT(1)
----------
99
--闪回查询5分钟之前T1的状态，重新插入误删除的那条记录。
SQL> select count(1) from t1 as of timestamp systimestamp - interval '5' minute;
COUNT(1)
----------
100
SQL> insert into t1 select * from t1 as of timestamp systimestamp - interval '5' minute where id = 1;
1 row created.
SQL> commit;
Commit complete.
SQL> select count(1) from t1;
COUNT(1)
----------
100
```

1.2 闪回版本查询举例

```
--得到当前的SCN
select dbms_flashback.get_system_change_number from dual;
--Get Current SCN：3218516
--更新操作
update t1 set contents = 'AAA' where id = 1;
commit;
update t1 set contents = 'BBB' where id = 1;
commit;
select dbms_flashback.get_system_change_number from dual;
--Get Current SCN：3218522
--删除，插入，更新操作
delete from t1 where id = 1;
commit;
insert into t1 values(1,1,'CCC');
commit;
update t1 set contents = 'DDD' where id = 1;
commit;
select dbms_flashback.get_system_change_number from dual;
--Get Current SCN：3218530
```
查询SCN在3218516 and 3218530之间的行id=1的完整历史：
```
select versions_startscn, versions_endscn, versions_xid, versions_operation, id, contents
from t1
versions between scn 3218516 and 3218530
where id = 1;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ID CONTENTS

      3218529                 01001700D0090000 U          1 DDD

      3218526         3218529 09000500AD0A0000 I          1 CCC

      3218524                 0A001C00D3090000 D          1 BBB

      3218521         3218524 02002100B60A0000 U          1 BBB

      3218518         3218521 08001800B60A0000 U          1 AAA

                      3218518                             1 rMLTDXxxqXOZnqYRJwInlGfGBTxNkAszBGEUGELqTSRnFjRGbi

6 rows selected.

<h1 id="2"> 二、闪回事物 </h1>

flashback_transaction_query包含对数据库执行的所有更改，包括DDL操作。

由于undo表空间有限，因此flashback_transaction_query中只包含一部分事物。

<h2 id="2.1">2.1 闪回事物查询的先决条件</h2>必须先启用重做日志流的其他日志记录。闪回事物查询既需要增强的重做信息，也需要撤销信息。

--开启重做日志流的其他日志记录

alter database add supplemental log data;

alter database add supplemental log data(primary key) columns;

--赋予相关权限给操作闪回事物的用户

grant execute on dbms_flashback to jingyu;

grant select any transaction to jingyu;

<h2 id="2.2">2.2 闪回事物查询</h2>

select start_scn, commit_scn, logon_user, operation, table_name, undo_sql

from flashback_transaction_query

where xid = hextoraw('05000E00C70B0000');

START_SCN COMMIT_SCN LOGON_USER OPERATION TABLE_NAME UNDO_SQL

3528038 3528039 JINGYU INSERT T1 delete from "JINGYU"."T1" where ROWID = 'AAAVvOAAFAAAACHAAA';

3528038 3528039 JINGYU BEGIN

<h1 id="3"> 三、闪回表 </h1>

核心命令：

--删除表记录

delete from t1;

commit;

--需要启用row movement

alter table t1 enable row movement;

--闪回表T1

flashback table t1 to timestamp systimestamp - interval '3' minute;

实际操作过程如下：

SQL> delete from t1;

100 rows deleted.

SQL> commit;

Commit complete.

--由于未启用row movement,闪回表会报错ORA-08189：

SQL> flashback table t1 to timestamp systimestamp - interval '3' minute;

flashback table t1 to timestamp systimestamp - interval '3' minute

*

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

SQL> alter table t1 enable row movement;

Table altered.

SQL> flashback table t1 to timestamp systimestamp - interval '3' minute;

Flashback complete.

SQL> select count(1) from t1;

COUNT(1)

SQL> alter table t1 disable row movement;

Table altered.

<h1 id="4"> 四、Flashback Data Archive </h1>

如果数据库中的一部分表需要长的多的保留期。

Flashback Data Archive仅记录UPDATE和DELETE语句，不记录INSERT语句。

--创建表空间（可以使用现有表空间，但Oracle建议最好使用专用表空间）

create tablespace fda1;

create tablespace fda2;

create tablespace fda3;

--创建闪回归档

create flashback archive fa_config tablespace fda1 retention 10 year;

create flashback archive fa_data tablespace fda1 retention 1 year;

--数据字典视图dba_flashback_archive, dba_flashback_archive_ts查看闪回归档、闪回归档和表空间的关系

set linesize 1000

col FLASHBACK_ARCHIVE_NAME for a20

col CREATE_TIME for a34

col LAST_PURGE_TIME for a34

col OWNER_NAME for a20

select * from dba_flashback_archive;

select * from dba_flashback_archive_ts;

--权限 flashback archive administer（有这个系统权限才可以创建和修改FDA）,flashback archive（有这个对象权限才能启用对表的跟踪）

--给闪回归档增加表空间

alter flashback archive fa_data add tablespace fda3 quota 50M;

--清除归档fa_data归档中2015年11月17日前的所有行：

alter flashback archive fa_data purge before timestamp to_timestamp('2015-11-16 00:00:00','YYYY-MM-DD HH24:MI:SS');

--表分配到闪回归档

alter table T1 flashback archive fa_config;

alter table T2 flashback archive fa_data;

--查询DBA_FLASHBACK_ARCHIVE_TABLES分析使用FDA的表

select * from dba_flashback_archive_tables;

--查询5年前T1表的数据量(对用户而言，AS OF查询使用的是FDA还是UNDO表空间是完全透明的)

select count(1) from t1 as of timestamp systimestamp - interval '5' year;

<h1 id="5"> 五、闪回数据库 </h1>

<h2 id="5.1">5.1 配置闪回数据库</h2>

5.1.1 开启闪回数据库：

--必须配置闪回恢复区

show parameter db_recovery

--必须归档模式

shutdown immediate;

startup mount exclusive;

alter database archivelog;

--设置闪回保留的目标时间（只是target,2880分钟=两天）

alter system set db_flashback_retention_target=2880;

--开启数据库闪回

alter database flashback on;

--打开数据库

alter database open;

5.1.2 关闭闪回数据库：

startup mount exclusive;

alter database flashback off;

alter database open;

<h2 id="5.2">5.2 使用闪回数据库</h2>

RMAN和SQL>提示符下都可以执行闪回。但二者有些细微差别。

例如RMAN可以基于线程和特定日志号进行闪回。

--RMAN基于线程和特定日志号进行闪回举例

RMAN> flashback database to sequence=307 thread=2;

--在SQL>提示符下，闪回到一小时前的状态

shutdown immediate;

startup mount exclusive;

flashback database to timestamp sysdate-(1/24);

--read only打开检查确定是要恢复的状态

alter database open read only;

--最后resetlogs打开

alter database open resetlogs;

--从闪回数据库排除表空间(创建时指定或后期修改flashback off)

alter tablespace dbs_d_test flashback off;

--若重新启用表空间闪回，需要打开flashback后，重新启动数据库。

alter tablespace dbs_d_test flashback on;

--使用担保还原点fla_init

create restore point fla_init guarantee flashback database;

flashback database to restore point fla_init;

<h2 id="5.3">5.3 监视闪回数据库</h2>

--查询可以将数据库闪回到多久之前

select * from v$flashback_database_log;

--查询数据库的闪回状态

select current_scn, flashback_on from v$database;

--监视每小时生成闪回数据的速率

select * from v$flashback_database_stat;

<h1 id="6">Reference</h1>

- OCP 认证考试指南 (1Z0-053)[M]. 清华大学出版社, 2010.

码农公寓

一、闪回查询

1.1 闪回查询举例

1.2 闪回版本查询举例

SQL> select count(1) from t1; COUNT(1)

相关文章

SQL> select count(1) from t1;

COUNT(1)