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
Post a Comment
0 Comments