Oracle Flashback database

This section briefly introduces flashback database, which can be executed in RMAN or SQL * PLUS. Sometimes it is quite practical.
 
Prerequisites:
 
1: The Archive mode is required.
 
2: The flash recovery area must be specified.
 
SQL> show parameter db_recovery
 

 

NAME TYPE VALUE
 
--------------------------------------------------------------------------------------------------
 
Db_recovery_file_dest string/app/Oracle/flash_recovery_area -- the path of the flash back area. If it is RAC, it is stored in the shared storage.
 
Db_recovery_file_dest_size big integer 10G -- the size of the flash back area. We recommend that you store this space in all database files.
 
We believe that all of the above parameter settings will be alter system set xxxxxx = ''; Next we will introduce how to enable the flash back function:
 
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
 
---------------------
 
YES
 
I believe everyone understands that the mount mode must be enabled, and the archive must be enabled, and the database should be forced 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
 
We can see that both TB1 and TB2 are back. We will introduce the use of flashback here.