How to Enable Flashback in Oracle 19c Database?

@ What is Flashback Database?

Use the FLASHBACK DATABASE command to rewind the database to a target time, SCN or log sequence number. FLASHBACK DATABASE is usually much faster than a RESTORE operation followed by point-in-time recovery, because the time needed to perform FLASHBACK DATABASE depends on the number of changes made to the database since the desired flashback time. On the other hand, the time needed to do a traditional point-in-time recovery from restored backups depends on the size of the database.

You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file. If the database control file is restored from backup or re-created, then all existing flashback log information is discarded.

The fast recovery area must be configured to enable flashback logging. Flashback logs are stored as Oracle-managed files in the fast recovery area and cannot be created if no fast recovery area is configured. You must have enabled the flashback logging before the target time for flashback using the SQL statement ALTER DATABASE ... FLASHBACK ON.

After FLASHBACK DATABASE completes, you may want to open the database read-only and run queries to ensure that you achieved the intended result. If you are not satisfied, then you can use RECOVER DATABASE to recover the database to its state when you started the flashback. You can then rerun FLASHBACK DATABASE.

If you are satisfied with the results of the flashback, then you can OPEN RESETLOGS to abandon all changes after the target time. Alternatively, you can use Data Pump to export lost data, use RECOVER DATABASE to return the database to its state before the flashback operation, and then use Data Pump to reimport the lost data.

@ To enable Flashback Database, do the following:

Step 1: Shut down the database and start up the database in mount mode in SQL*Plus:

SQL> SHUTDOWN IMMEDIATE;

 Database closed.

Database dismounted.

ORACLE instance shut down.


SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 1275068016 bytes

Fixed Size                  8896112 bytes

Variable Size             469762048 bytes

Database Buffers          788529152 bytes

Redo Buffers                7880704 bytes

Database mounted.

Step 2: Confirm that the database is in ARCHIVELOG mode, which is required for Flashback Database, and enable ARCHIVELOG mode if needed.

SQL> ARCHIVE LOG LIST;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     11

Current log sequence           13


SQL> ALTER DATABASE ARCHIVELOG;

Database altered.


Now check again archive log mode and we see that Database log mode is Archive Mode.


SQL> ARCHIVE LOG LIST;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     11

Next log sequence to archive   13

Current log sequence           13


Step 3: Set the flashback retention target to the desired value (in minutes). The following example value is for a 24-hour flashback target

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

System altered.


Step 4: Set or change the relevant FRA parameters for Flashback Database, as noted in the following example (these commands assume the FRA disk group is named +FRA and that you are adding 20GB to the FRA size suggested earlier, which is more than enough to accommodate the flashback logs that will be generated):

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G;

System altered.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/fast_recovery_area';

System altered.

Now check the db_recovery_file_dest and size using following query

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u01/oracle/fast_recovery_area

db_recovery_file_dest_size           big integer 20G


Note:

If DB_RECOVERY_FILE_DEST is previously unset and you want to dynamically set it, you must first set DB_RECOVERY_FILE_DEST_SIZE. If these parameters are already set, increase the FRA size to allocate space for flashback logs. The volume and rate of flashback log generation is approximately the same order of magnitude as those of redo log generation.

Step 5: Set the associated undo retention, required for certain flashback features. Here, we set a 12-hour undo retention (in seconds), equivalent to half the DB_FLASHBACK_RETENTION_TARGET, as in Step 3.

SQL> ALTER SYSTEM SET UNDO_RETENTION=43200;

System altered.

Step 6: Finally, enable Flashback Database and FORCE LOGGING while the database is mounted, then open the database:

Check the database flashback status 

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

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

NO

Enabled flashback database.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

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

YES


Keep learning… Have a great day!!!


Post a Comment

0 Comments

3