flashback database测试-CSDN博客

情景描述：用户对表误进行了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.

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26143577/viewspace-720308/