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