Oracle Flashback database

这里简单介绍下flashback database，这个既可以在RMAN中执行，也可以再SQL*PLUS执行，有时候还是挺实用的

必备条件：

1：必须是归档模式

2：必须指定flash recovery area

SQL> show parameter db_recovery
  NAME TYPE VALUE

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

db_recovery_file_dest string /app/Oracle/flash_recovery_area --闪回区路径，如果是RAC,放在共享存储中

db_recovery_file_dest_size big integer 10G --闪回区大小，该空间大小建议可以放入所有数据库文件

以上参数的设置相信大家都会alter system set xxxxxx="";接下来介绍下打开闪回功能：

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-38759: Database must be mounted by only one instance and not open.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.
  Total System Global Area 5049942016 bytes

Fixed Size 2090880 bytes

Variable Size 1375733888 bytes

Database Buffers 3657433088 bytes

Redo Buffers 14684160 bytes

Database mounted.

SQL> alter database flashback on;

alter database flashback on

*ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38707: Media recovery is not enabled.

SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> SELECT FLASHBACK_ON,FORCE_LOGGING FROM V$DATABASE;

FLASHBACK_ON FOR

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

YES YES

相信大家看的很明白了，一定是要在mount模式，而且归档一定要打开，数据库要force logging。
  SQL> set num 16

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
  GET_SYSTEM_CHANGE_NUMBER

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

122693676204
  SQL> conn test/test

Connected.

SQL> select table_name from user_tables;
  TABLE_NAME

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

TB2

FLASH_VERSION

TB1

TBL_ORACLE_FDW

SQL> drop table tb1 purge;

Table dropped.

SQL> drop table tb2 purge;

Table dropped.

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 5049942016 bytes

Fixed Size 2090880 bytes

Variable Size 1375733888 bytes

Database Buffers 3657433088 bytes

Redo Buffers 14684160 bytes

Database mounted.

SQL> FLASHBACK DATABASE TO SCN 122693676204 ;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> conn test/test

Connected.

SQL> select table_name from user_tables;

TABLE_NAME

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

TB2

FLASH_VERSION

TB1

TBL_ORACLE_FDW

可以看到TB1和TB2都回来了，好了flashback的使用就介绍到这里更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle 备份恢复概要介绍Oracle 数据库模拟数据文件损坏恢复相关资讯 Flashback Oracle flashback flashback database

闪回事务查询Flashback Transactio （08/22/2015 09:42:46）
使用Flashback Database进行数据表（08/22/2015 09:38:06）
使用dbms_flashback工具包实现闪回（08/22/2015 09:14:51）

闪回版本查询Flashback Version （08/22/2015 09:41:05）
使用Flashback Transaction方法来（08/22/2015 09:19:56）
Oracle闪回技术flashback （08/20/2015 10:45:10）

本文评论查看全部评论（0）