Oracle闪回查询/闪回恢复
闪回查询以及闪回恢复
开启闪回查询的先决条件
1.启用归档
2.闪回开启
1，查看数据库归档是否开启，闪回是否开启，如下均没有开启，则归档需要在mount模式下开启。
SQL> select log_mode,flashback_on from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
NOARCHIVELOG NO
Elapsed: 00:00:00.00
2.启动到mount模式
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
Elapsed: 00:00:00.00
2.检查归档是否开启，闪回是否开启
SQL> select log_mode,flashback_on from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
NOARCHIVELOG NO
Elapsed: 00:00:00.00
3.启用归档
SQL> alter database archivelog;
Database altered.
Elapsed: 00:00:00.01
4.启用闪回
SQL> alter database flashback on;
Database altered.
Elapsed: 00:00:00.30
5.确认先决条件状况，两个均已开启
SQL> select log_mode,flashback_on from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG YES
Elapsed: 00:00:00.00
6.打开数据库
SQL> alter database open;
Database altered.
Elapsed: 00:00:01.49
7.查看表空间，这次测试就使用SCOTT帐号测试
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/DB01/users01.dbf USERS
/u01/app/oracle/oradata/DB01/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/DB01/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/DB01/system01.dbf SYSTEM
/u01/app/oracle/oradata/DB01/tbs_scott.dbf TBS_SCOTT
Elapsed: 00:00:00.02
SQL> select owner,tablespace_name,table_name from dba_tables where tablespace_name = 'TBS_SCOTT';
OWNER TABLESPACE_NAME TABLE_NAME
---------------------- -------------------------- ------------------------------
SCOTT TBS_SCOTT TB02
Elapsed: 00:00:00.07
8.创建tb03表，做测试用，数据量不需要太大，方便对比.
SQL> create table scott.tb03 as select empno,ename,sal from scott.tb02 where rownum <5;
Table created.
Elapsed: 00:00:00.22
SQL> select * from scott.tb03;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
Elapsed: 00:00:00.00
9.打开时间，方便计算闪回时间
SQL> set time on;
21:39:29 SQL> !date
Sun May 22 21:39:33 CST 2016
10.更新tb03表，每行sal+1并提交
21:40:17 SQL> update scott.tb03 set sal=sal+1;
4 rows updated.
Elapsed: 00:00:00.00
21:40:30 SQL> select * from scott.tb03;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 801
7499 ALLEN 1601
7521 WARD 1251
7566 JONES 2976
Elapsed: 00:00:00.00
21:41:35 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
21:43:54 SQL> select * from scott.tb03;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 801
7499 ALLEN 1601
7521 WARD 1251
7566 JONES 2976
Elapsed: 00:00:00.00
11.使用闪回查询，查询在更新时间之前的数据依旧为没有+1的值
21:44:03 SQL> select * from scott.tb03
21:44:38 2 as of timestamp
21:44:50 3 to_timestamp('2016-05-22 21:40:17','yyyy-mm-dd hh24:mi:ss');
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
Elapsed: 00:00:00.01
12.使用闪回恢复，更新并提交旧值。
21:45:58 SQL> update scott.tb03 new_tb03 set sal=(select sal from scott.tb03 as of timestamp to_timestamp('2016-05-22 21:40:17','yyyy-mm-dd hh24:mi:ss')old_tb03 where new_tb03.empno=old_tb03.empno);
4 rows updated.
Elapsed: 00:00:00.01
21:47:28 SQL> select * from scott.tb03;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
Elapsed: 00:00:00.00
21:47:45 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
21:47:58 SQL> select * from scott.tb03;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
Elapsed: 00:00:00.00
以上为闪回查询和闪回恢复的过程简单测试。
报歉！评论已关闭！