闪回的开启与关闭

Oracle version：11.2.0.3.0

设置 db_recovery_file_dest 的路径和大小

SQL> alter system set db_recovery_file_dest='/u01/fast_recovery_area'scope=both;

System altered.

SQL> alter system set db_recovery_file_dest_size=2G scope=both;

System altered.

查看对应参数值
SQL> show parameter db_recovery_file_dest

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /u01/fast_recovery_area
db_recovery_file_dest_size	     big integer 2G

检查闪回是否开启
SQL> select flashback_on from V$database;

FLASHBACK_ON
------------------
NO

打开闪回

关闭数据库后，进入mount 状态，以开启闪回
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  480182272 bytes
Fixed Size		    2229544 bytes
Variable Size		  314575576 bytes
Database Buffers	  159383552 bytes
Redo Buffers		    3993600 bytes
Database mounted.

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
此报错是因为未开启归档

SQL> archive log list;
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Current log sequence	       12
SQL> alter database archivelog;

Database altered.
打开归档后，成功开启闪回
SQL> alter database flashback on;

Database altered.

打开数据库
SQL> alter database open;

Database altered.
查看闪回状态
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

闪回测试

查询每个版本对应的SCN
SQL> SELECT versions_xid XID, versions_startscn START_SCN,
  versions_endscn END_SCN, versions_operation OPERATION,
  USER_ID, USERNAME
FROM WNM_USERS
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;  2    3    4    5  

XID		  START_SCN    END_SCN O    USER_ID USERNAME
---------------- ---------- ---------- - ---------- ------------------------------
						  9 OUTLN
						  0 SYS
						  5 SYSTEM
						 32 WNM
						 31 APPQOSSYS
						 30 DBSNMP
						 14 DIP
						 21 ORACLE_OCM

8 rows selected.

删除一条数据
SQL> delete from WNM_USERS where USER_ID=9;

1 row deleted.

SQL> commit;

Commit complete.

查询每个版本对应的SCN
SQL> SELECT versions_xid XID, versions_startscn START_SCN,
  versions_endscn END_SCN, versions_operation OPERATION,
  USER_ID, USERNAME
FROM WNM_USERS
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;  2    3    4    5  

XID		  START_SCN    END_SCN O    USER_ID USERNAME
---------------- ---------- ---------- - ---------- ------------------------------
010012000F010000     393498	       D	  9 OUTLN
				393498		  9 OUTLN
						  0 SYS
						  5 SYSTEM
						 32 WNM
						 31 APPQOSSYS
						 30 DBSNMP
						 14 DIP
						 21 ORACLE_OCM

9 rows selected.

查询表中数据
SQL>  select USER_ID, USERNAME FROM WNM_USERS  ;

   USER_ID USERNAME
---------- ------------------------------
	 0 SYS
	 5 SYSTEM
	32 WNM
	31 APPQOSSYS
	30 DBSNMP
	14 DIP
	21 ORACLE_OCM

7 rows selected.

闪回查询定位SCN
SQL> select USER_ID, USERNAME from WNM_USERS as of scn 393498;

   USER_ID USERNAME
---------- ------------------------------
	 0 SYS
	 5 SYSTEM
	32 WNM
	31 APPQOSSYS
	30 DBSNMP
	14 DIP
	21 ORACLE_OCM

7 rows selected.

闪回查询定位SCN
SQL> select USER_ID, USERNAME from WNM_USERS as of scn 393497;

   USER_ID USERNAME
---------- ------------------------------
	 9 OUTLN
	 0 SYS
	 5 SYSTEM
	32 WNM
	31 APPQOSSYS
	30 DBSNMP
	14 DIP
	21 ORACLE_OCM

8 rows selected.

启用表的行迁移
SQL> alter table WNM_USERS enable row movement;

Table altered.
闪回表到指定SCN
SQL> flashback table WNM_USERS to scn 393497;

Flashback complete.
验证结果
SQL> select USER_ID, USERNAME from WNM_USERS;

   USER_ID USERNAME
---------- ------------------------------
	 9 OUTLN
	 0 SYS
	 5 SYSTEM
	32 WNM
	31 APPQOSSYS
	30 DBSNMP
	14 DIP
	21 ORACLE_OCM

8 rows selected.    

关闭闪回

关闭数据库，并启动到mount状态
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  480182272 bytes
Fixed Size		    2229544 bytes
Variable Size		  314575576 bytes
Database Buffers	  159383552 bytes
Redo Buffers		    3993600 bytes
Database mounted.

此处注意，在闪回打开的时候，无法关闭归档
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled

关闭闪回
SQL> alter database  flashback off;

Database altered.
关闭归档
SQL> alter database noarchivelog;

Database altered.
打开数据库
SQL> alter database open;

Database altered.
查看数据库闪回状态
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO