Oracle Flashback database

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

这里简单介绍下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的使用就介绍到这里