oracle闪回 分区,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

以上为闪回查询和闪回恢复的过程简单测试。