--闪回数据库

# 1.开启数据库闪回日志

sys@TESTDB12>alter database flashback on;

Database altered.

# 2. 创建还原点

sys@TESTDB12>create restore point rp_before_delete;

Restore point created.

# 3. 删除scott.emp表中的所有数据

sys@TESTDB12>delete from scott.emp;

14 rows deleted.

sys@TESTDB12>select * from scott.emp;

no rows selected

sys@TESTDB12>commit;

Commit complete.

# 4. 使数据库处于MOUNT状态

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

# 4. 开启RMAN连接到目标数据库

# 5. 执行闪回

# 5.1 闪回到指定的SCN，FLASHBACK DATABASE TO SCN 861150;

# 5.2 闪回到某个时间点, FLASHBACK DATABASE TO TIMESTAMP TO_DATE(04-DEC-2009  03:30:00','DD-MON-YYYY HH24:MI:SS');

# 5.3 闪回到指定的还原点rp_before_delete

RMAN> flashback database to restore point rp_before_delete;

Starting flashback at 12-JUL-13

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:03

Finished flashback at 12-JUL-13

# 6. 以只读方式打开数据库，再执行一个查询语句，验证数据库的内容

sys@TESTDB12>alter database open read only;

Database altered.

sys@TESTDB12>select * from scott.emp;

     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

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      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

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

# 7. 如果达到了要求,则执行下面的语句来启动数据库

sys@TESTDB12>shutdown immeidate;

SP2-0717: illegal SHUTDOWN option

sys@TESTDB12>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TESTDB12>startup mount;

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                  2233000 bytes

Variable Size             532680024 bytes

Database Buffers          301989888 bytes

Redo Buffers                2379776 bytes

Database mounted.

sys@TESTDB12>alter database open resetlogs;

Database altered.

# 8. 最后验证数据的正确性

sys@TESTDB12>select * from scott.emp;

     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

# 9. 最后将闪回日志关闭，避免产生过多的日志

sys@TESTDB12>alter database flashback off;