flashback技术之---flashback database

flashback database可以实现基于时间点的不完全恢复，将数据库闪回到某个时间点，而不需要漫长的restore操作

1. flashback database的一些限制：

--只能用来undo changes to a datafile made by an oracle database，不能用来修复media failures或者是数据文件被删除的情况

--如果控制文件是restore的或重建过的，那么所有的flashback信息将丢失。

--对于nologging操作，flashback无法恢复，比如direct-path insert操作，恢复后相关的数据块状态是corruption的。

2.开启flashback database的需求

--数据库运行在归档模式

--配置flash recovery area

--对于rac，flash recovery area必须配置在 a clustered file system or in ASM.

3.启用flashback database

--确认数据库目前未开启flashback

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

NO

--配置闪回区

SQL> alter system set db_recovery_file_dest_size=1g;

System altered.

SQL> alter system set db_recovery_file_dest='/home/db/oracle/recovery/';

System altered.

--启动数据库到mount状态

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              92276980 bytes

Database Buffers           71303168 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

System altered.

SQL>  ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> alter database open;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

4.使用flashback database恢复被误删除的表

SQL> select count(*) from test; 

  COUNT(*)

----------

     44936

SQL>  select sysdate from dual;

SYSDATE

-------------------

2012-07-18 03:26:59

SQL> drop table test;

Table dropped.

RMAN> shutdown immediate

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

[oracle@rhel bin]$ ./rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 18 03:34:16 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: MBS (DBID=1440741932)

RMAN> STARTUP MOUNT;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     167772160 bytes

Fixed Size                     1218316 bytes

Variable Size                 92276980 bytes

Database Buffers              71303168 bytes

Redo Buffers                   2973696 bytes

RMAN> FLASHBACK DATABASE TO TIME    "TO_DATE('2012-07-18 03:26:59','yyyy-mm-dd hh24:mi:ss')";

Starting flashback at 18-JUL-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished flashback at 18-JUL-12

RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';  --启动数据库到只读状态

sql statement: ALTER DATABASE OPEN READ ONLY

--登陆到数据库，查看test表是否存在及其记录数

[oracle@rhel bin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 18 03:37:48 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from test;

  COUNT(*)

----------

     44936

--确认闪回没有问题后，数据库需要以OPEN RESETLOGS 的方式打开：

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              92276980 bytes

Database Buffers           71303168 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

--如果需要将数据库恢复到最近的状态，同时恢复test表，则先导出test表

[oracle@rhel ~]$ exp \'/ as sysdba\' tables=test file=test.dmp statistics=none

Export: Release 10.2.0.1.0 - Production on Wed Jul 18 03:44:04 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table                           TEST      44936 rows exported

Export terminated successfully without warnings.

--导出表后，可以将数据库重新恢复到最近的状态， 然后将表test导入，这样就完成了表test的数据恢复。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-738202/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-738202/