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
标签:闪回,SCN,Database,database,开启,WNM,关闭,SQL
From: https://www.cnblogs.com/Miac/p/18333974