flashback database測試

YallonKing發表於2012-04-04
Database

情景描述：使用者對錶誤進行了truncate操作。（需資料庫不完全恢復）
--檢視測試表及資料
SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 afterchange

6 rows selected.
--對錶truncate
SQL> truncate table test;

Table truncated.
-嘗試閃回truncate的表
SQL> flashback table test to before drop;
flashback table test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
--檢視當前時間
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2012/04/03 23:40:52
--將資料庫重啟動至mount階段
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             318769536 bytes
Database Buffers           96468992 bytes
Redo Buffers                6094848 bytes
Database mounted.
--將資料庫閃回至合適時間點
SQL> flashback database to timestamp to_timestamp('2012/04/03 23:35:52','yyyy/mm/dd hh24:mi:ss');

Flashback complete.

SQL> alter database open read only;

Database altered.
--如果沒有返回到正確時間點，需要重複以上操作
SQL> select * from test.test;

no rows selected

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             318769536 bytes
Database Buffers           96468992 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2012/04/03 23:30:52','yyyy/mm/dd hh24:mi:ss');

Flashback complete.

SQL> alter database open read only;

Database altered.
--當發現資料庫已經正確閃回到合適時間點則重新以resetlogs開啟資料庫開始對外服務
SQL> select * from test.test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 afterchange

6 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             318769536 bytes
Database Buffers           96468992 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.
--檢視相關使用者下的相關表及資料
SQL> conn test/test
Connected.
SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 afterchange

6 rows selected.
--建議立即進行資料庫的全備
[oracle@test ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 3 23:47:54 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2055832488)

RMAN> backup database;

Starting backup at 03-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
… …
dqv_.bkp tag=TAG20120403T234819 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:02:19
Finished backup at 03-APR-12

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26143577/viewspace-720308/，如需轉載，請註明出處，否則將追究法律責任。

相關文章