-- 运行于归档模式且打开闪回日志的数据库,可以进行任意方向的闪回(向回或向前)

-- 查看数据库运行在归档模式

sys@TESTDB11>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /archive2

Oldest online log sequence     97

Next log sequence to archive   99

Current log sequence           99

-- 开启闪回日志

sys@TESTDB11>alter database flashback on;

Database altered.

-- 创建表

scott@TESTDB11>create table emp1 as select * from emp;

Table created.

-- 创建还原点并删除数据

scott@TESTDB11>create restore point rp_bd_analyst;

Restore point created.

-- 删除analyst

scott@TESTDB11>delete from emp1 where job = 'ANALYST';

2 rows deleted.

-- 删除clerk

scott@TESTDB11>create restore point rp_bd_clerk;

Restore point created.

scott@TESTDB11>delete from emp1 where job = 'CLERK';

4 rows deleted.

-- 删除manager

scott@TESTDB11>create restore point rp_bd_manager;

Restore point created.

scott@TESTDB11>delete from emp1 where job = 'MANAGER';

3 rows deleted.

-- 删除president

scott@TESTDB11>create restore point rp_bd_president;

Restore point created.

scott@TESTDB11>delete from emp1 where job = 'PRESIDENT';

1 row deleted.

sys@TESTDB11>shutdown immediate;

idle>startup mount;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             645924344 bytes

Database Buffers          205520896 bytes

Redo Buffers                2306048 bytes

Database mounted.

-- 闪回到第2个还原点

idle>flashback database to restore point rp_bd_clerk;

Flashback complete.

idle>alter database open read only;

Database altered.

idle>conn scott/scott

Connected.

scott@TESTDB11>select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

12 rows selected.

scott@TESTDB11>select * from emp1 order by job;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

12 rows selected.

scott@TESTDB11>conn / as sysdba

Connected.

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TESTDB11>startup mount;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             645924344 bytes

Database Buffers          205520896 bytes

Redo Buffers                2306048 bytes

Database mounted.

-- 闪回到第1个还原点

sys@TESTDB11>flashback database to restore point rp_bd_analyst;

Flashback complete.

sys@TESTDB11>alter database open read only;

Database altered.

sys@TESTDB11>select * from scott.emp1 order by job;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

14 rows selected.

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TESTDB11>startup mount;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             645924344 bytes

Database Buffers          205520896 bytes

Redo Buffers                2306048 bytes

Database mounted.

-- 闪回到第4个还原点

sys@TESTDB11>flashback database to restore point rp_bd_president;

Flashback complete.

sys@TESTDB11>alter database open read only;

Database altered.

sys@TESTDB11>select * from scott.emp1 order by job;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TESTDB11>startup mount;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             645924344 bytes

Database Buffers          205520896 bytes

Redo Buffers                2306048 bytes

Database mounted.

-- 闪回到第3个还原点

sys@TESTDB11>flashback database to restore point rp_bd_manager;

Flashback complete.

sys@TESTDB11>alter database open read only;

Database altered.

sys@TESTDB11>select * from scott.emp1 order by job;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

8 rows selected.

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--恢复到第4个还原点

sys@TESTDB11>startup mount;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             645924344 bytes

Database Buffers          205520896 bytes

Redo Buffers                2306048 bytes

Database mounted.

-- 在rman中执行recover

RMAN> recover database to restore point rp_bd_president;

Starting recover at 25-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 25-FEB-14

sys@TESTDB11>alter database open read only;

Database altered.

sys@TESTDB11>select * from scott.emp1 order by job;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

-- 结论: 可以向回，也可以向前来回进行闪回操作. 向前闪回等价于recover操作