ITPub博客

首页 > Linux操作系统 > Linux操作系统 > flashback database测试

flashback database测试

原创 Linux操作系统 作者:YallonKing 时间:2012-04-04 16:23:44 0 删除 编辑

情景描述:用户对表误进行了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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-07

  • 博文量
    72
  • 访问量
    256965