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