Backup And Recovery User's Guide-從RMAN開始-使用閃回資料庫來回退資料庫

--閃回資料庫

# 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;