Oracle 11g enables the flash back function Flashback

Oracle 11g enables the flash back function Flashback

During the Oracle database creation process, you are usually prompted whether to enable the flash back and specify the size of the flash back recovery area. I usually choose not to enable it. In this way, you need to manually enable the flash back function.

1. Prepare the environment
We perform a test on Oracle11g.

Click (here) to fold or open

SQL> select * from v $ version;
 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

SQL>

2. Check whether the flash back function is enabled
You can query the flashback_on field in the v $ database view to obtain the status of the flash back function. This field is of the boolean type. YES indicates that the flash back function is enabled, and NO indicates that the flash back function is disabled.
Click (here) to fold or open

SQL>
 
SQL> select flashback_on from v $ database;

FLASHBACK_ON

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

NO

SQL>

3. Enable the archiving function before enabling the flash back function, we must first enable database archiving, otherwise the database will report an error.

Click (here) to fold or open

SQL>
 
SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 16

Current log sequence 20

SQL>

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>

ORA-38706 and ORA-38707 two errors remind us to open the database archive, steps are as follows;

Click (here) to fold or open

SQL>
 
SQL> alter database archive;

Alter database archive

*

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database archivelog;

Alter database archivelog

*

ERROR at line 1:

ORA-01126: database must be mounted in this instance and not open in any

Instance

SQL>
SQL>

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 536873732 bytes

Database Buffers 398458880 bytes

Redo Buffers 4919296 bytes

Database mounted.

SQL>

SQL> alter database archivelog;

Database altered.

SQL>

SQL> alter database open;

Database altered.

SQL>

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 16

Next log sequence to archive 20

Current log sequence 20

SQL>

SQL>

4. Enable the flash back function

4.1 set parameters
The flash back function is related to two initialization parameters. Here, db_recovery_file_dest_size indicates the size of the flash back recovery area, and db_recovery_file_dest indicates the path of the flash back recovery area.

Click (here) to fold or open

SQL>
 
SQL> show parameter db_recovery

NAME TYPE VALUE

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

Db_recovery_file_dest string

Db_recovery_file_dest_size big integer 0

SQL>
Before you enable the flash back function, you must set these two parameters, and the two parameters are sorted in order. If the order is wrong, the system reports an error and provides a prompt.

Click (here) to fold or open

SQL>
 
SQL> alter system set db_recovery_file_dest = \ '/home/oracle/flashback \';

Alter system set db_recovery_file_dest = \ '/home/oracle/flashback \'

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified the value is invalid

ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

SQL>

SQL> alter system set db_recovery_file_dest_size = 2g;

System altered.

SQL>

SQL> alter system set db_recovery_file_dest = \ '/home/oracle/flashback \';

System altered.

SQL>

SQL> show parameter db_recovery

NAME TYPE VALUE

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

Db_recovery_file_dest string/home/oracle/flashback

Db_recovery_file_dest_size big integer 2G

SQL>

4.2 enable the flash back function
Note that in 10 Gb, if you want to enable the database-level flash back, you need to set relevant parameters and make the database in archive mode, and then enable the flash back in the MOUNT state. In 11G, if you have set related parameters and enabled the archive, you can enable the flash back in the OPEN state. This is also a new feature of Oracle 11g.

Click (here) to fold or open

SQL>
 
SQL> select status from v $ instance;

STATUS

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

OPEN

SQL>

SQL> alter database flashback on;

Database altered.

SQL>

SQL> select flashback_on from v $ database;

FLASHBACK_ON

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

YES

SQL>
In this way, we have enabled the database flash back function to implement flash back to the database and other functions.

5. Disable the flash back function
It is very easy to disable the flash back function, and a statement can be done.

Click (here) to fold or open

SQL>
 
SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from v $ database;

FLASHBACK_ON

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

NO

SQL>

6. Summary
From the process above, pay attention to the following points:

1. Oracle11g supports enabling the flash back feature in the Open state. This is a new feature, unlike earlier versions such as Oracle 10g;
2. The prerequisite for enabling database flashback is to enable database archiving;
3. set two initialization parameters: the size of the flash recovery area db_recovery_file_dest_size and the path db_recovery_file_dest of the flash recovery area. The two parameters are sequential. You must set the sequence first and then set db_recovery_file_dest, otherwise, an error is reported and a prompt is displayed;