Today, I’m gonna share with you an article I wrote about some of my favorite tips and tricks regarding Oracle Data Guard.
I have recently been asked by a customer for help with creating an Oracle Data Guard configuration to protect some Oracle data. Here are the steps I took to create such a configuration with Oracle Flashback Database, using Oracle 11gR2 on a couple of Linux VMs.
The Flashback Database feature is one of the most useful features of the RAC (RACf) feature of Oracle 11g. A database in a RAC environment is basically split into 2 servers, called Primary and Secondary. The Flashback database is basically a snapshot of the Primary database on secondary. It is used to restore the database (even after it is crashed) to the state before it was crashed. The idea is really simple, but can be achieved in many different ways.
Flashback Database provides the following benefits when configuring Data Guard:
- Provides an alternative to the deferred reading application to protect against user error or logical corruption. Using the Flashback database in this context better synchronizes the backup databases with the primary database, reducing resiliency and failover time.
- Eliminates the need to completely rebuild the original primary database after a failover. The failed primary database can be put back into flash memory until before failover and converted into a backup database for the new primary database.
The Flashback database is used in the Data Guard configuration for the following functions:
- Fast recovery after a breakdown: You must enable the flashback database and configure the fast recovery zone on the primary database and the target backup database before enabling fast failover. (For more information, see the lesson on enabling Fast Failover).
- Snapshot Waiting Mode : To convert the physical standby database to an instant standby database, you must configure the fast recovery zone and its size. If Flashback Database is not enabled, it is enabled when a physical backup database is converted to a backup database; it is enabled when a physical backup database is converted to a snapshot database. (For more information, see Creating and Managing a Backup Snapshot Database).
Flashback database overview
The Flashback database allows you to quickly go back to an earlier time and undo any changes made since then. This process is fast because you don’t have to restore the backups. This function allows you to undo the changes that caused the logical data to be corrupted. When you use the Flashback database, Oracle Database uses images of previous blocks to store changes in the database. During normal database use, Oracle Database periodically captures these block images in flashback logs that are written sequentially and not archived. Oracle Database automatically creates, deletes, and resizes flashback logs in the fast recovery area. You only need to know the flashback logs to check the performance and decide how much space to allocate to the flashback logs fast recovery area.
The time required to rewind the database with Flashback Database is proportional to the distance in time and activity of the database after the target date. The time it takes to restore the entire database can be much longer. Pre-flashback log images are only used to restore the database to a point in the past, while forward restore is used to bring the database to a consistent state at a point in the past. Oracle Database resets data files to an earlier point in time – but not auxiliary files such as. B. Initialization parameter files. Flashback Database can also be used to complement Data Guard and Recovery Advisor and to synchronize cloned databases.
Flashback database configuration
The database can be opened when the flashback function is activated. To activate the memory function : 1. Set up the Fast Recovery Zone. 2. Specify the storage target with the initialization parameter DB_FLASHBACK_RETENTION_TARGET. SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH ; You can specify an upper limit in minutes for the database rollback. The example uses 2880 minutes, which is equivalent to two days.
This parameter is only a target parameter and does not provide any guarantee. The time interval for flash recovery depends on the amount of flash data stored in the fast recovery area. 3. Activate the Flashback database with the following command: SQL> MODIFY FLASHBACK DATABASE ON ; The database must be configured for archiving before the Flashback Database enable command is executed. Use the following query to determine if the Flashback database is enabled: SQL> SELECT flashback on FROM v$database ; Disable the flashback database with the ALTER DATABASE FLASHBACK OFF command. This will automatically remove all existing logs from the Flashback database.
Flashback database configuration with Enterprise Manager
1. On the main page of the database, select Availability > Backup and Restore > Restore Settings. 2. Make sure your database is in ARCHIVELOG mode. Otherwise, select the ARCHIVELOG mode. 3. Check the location of the Quick Recovery Zone. The Quick Recovery Zone is a single location where all files and activities related to recovery in the Oracle database are stored. All files required to fully restore the database after a media failure are included in the quick restore section. Recovery-related files that can be created in the Quick Recovery Zone include redo log files, administrative files, backups created by Recovery Manager (RMAN), flashback logs, and a track changes file.
By allocating disk space and consolidating recovery-related files into a specific zone, the Oracle Database Server frees the database administrator to manage the disk files created by these components. If you want to place it in a different location, specify the location in the Quick Restore Zone Location field. 4. Set the size of the fast food area, in the Size fast food area field. 5. Select Activate Flashback Database. You can also set the flashback memory time and view important information in the flashback database window. 6. Scroll down to the Recovery Options page and click Apply.
Use of flashback database instead of transmission delay
As an alternative to the Apply Delay configuration option, you can use Flashback Database to protect against corrupted or erroneous data being applied to the backup database. The Apply Delay configuration uses the DELAY attribute of the LOG_ARCHIVE_DEST_n parameter so that redo data is sent from the primary database to the backup database, but the application of the redos is delayed. This provides a delay so that in case of logical errors, such as. B. If a row is accidentally deleted in the primary database, the data can be exported from the backup database before the SQL command is executed in the backup database. Flashback Database allows you to quickly and easily return to a backup database at any point in the past. You can set up a single backup database with Flashback Database to get the same benefits as multiple backup databases with different timeframes.
Use of flashback database and real-time application
Oracle Data Guard’s real-time enforcement feature reduces failover time. Flashback Database protects the physical backup database from logical data corruption and user error. The Data Guard Broker enables automatic activation of applications in real time.
Using the flashback database after a reset
Physical spare configuration
For a physical backup database, the Redo Apply service may not stop if it encounters the OPEN RESETLOGS command in the redo information. If the SCN (System Change Number) of the pending physical database is far enough away from the SCN of the primary database, the Redo Apply service can interpret the OPEN RESETLOGS command without stopping. This function is called RESETLOGS recovery. Use the following procedure to prevent the backup database from being recreated after running the OPEN RESETLOGS command on the primary database and stopping the managed recovery process on the physical backup database: 1. In the primary database, identify the SNA that is at least two SNAs ahead of the SNA when the OPEN RESETLOGS command was issued.
This is required to correctly restore the backup computer via OPEN RESETLOGS. Use the following query to find SCN before RESETLOGS: SQL> SELECT TO_CHAR(resetlogs_change# – 2) FROM v$database ; 2. Determine the current SNA in the backup database with the following query: SQL> SELECT TO_CHAR(current_scn) FROM v$database ; 3. Reset the backup database to the SNA before RESETLOGS that you requested in step 1: SQL> FLASHBACK STANDBY DATABASE TO SCN ; 4. Restart the managed restore on the backup database. The backup database is now ready to receive and apply logs from the main database. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT ;
Logical backup configuration
For a logical standby database, the SQL Apply service may not stop when it encounters the OPEN RESETLOGS command in the redo information. If the SCN of the standby logical database is far enough away from the SCN of the primary database, the SQL Apply service can interpret the OPEN RESETLOGS command without stopping. Use the following procedure to prevent the backup database from being recreated after OPEN RESETLOGS has been performed on the primary database and the SQL Apply process on the logical backup database has been stopped: 1. In the primary database, identify the SNA that is at least two SNAs ahead of the SNA when the OPEN RESETLOGS command was issued.
This is required to correctly restore the backup computer via OPEN RESETLOGS. Use the following query to find SCN before RESETLOGS: SQL> SELECT TO_CHAR(resetlogs_change# – 2) FROM v$database ; 2. Determine the current SNA in the backup database with the following query: SQL> SELECT TO_CHAR(current_scn) FROM v$database ; 3. Reset the backup database to the SNA before RESETLOGS that you requested in step 1: SQL> FLASHBACK STANDBY DATABASE TO SCN ; 4. Restart SQL Apply on the backup database. The backup database is now ready to receive and apply logs from the main database. SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE ;
Backup Database Role Transitions via Flashback
Use Flashback Database to restore the database to a point in time before a switchover or failover to the backup physical database. The role of the database does not change when switching, failover or activating the physical backup. In a standby logical database, the FLASHBACK DATABASE command resets the database to the system change number (SCN) or pre-transfer or failover timestamp. For a logical backup database, the database returns to its original role at the flashback SNA or flashback time. You can also use the flashback database to replace the activation of physical backup storage.
Using the flashback database after a failover
You invoke a failover operation when a catastrophic failure of the primary database occurs and there is no way to recover the primary database in time. After a failover operation, the old standby database becomes the new primary database and the old primary database goes into a recovery state. Use the Flashback Database feature to convert the old primary database to a new backup database without recreating the database.
You can create a flash backup of the old master database so that it contains only the changes already applied to the old standby database. Allows you to convert the old primary database to a new backup database without restoring the old primary database. The STANDBY_BECAME_PRIMARY_SCN column of V$DATABASE can provide the SCN number corresponding to the switchover. The old primary database must use the FLASHBACK DATABASE TO SCN … command to rewind to this point. This can then be converted to a backup database using the SQL command ALTER DATABASE CONVERT TO PHYSICAL STANDBY.
Message: Before the switch, the Flashback Database function must be activated on the old master database.Flashback Database is a technology used in the Oracle Advanced Security configuration. The technology is used to provide users with a consistent user experience when they work on the same system. In most cases, this experience is provided by the Oracle Identity Management Server, which is a software component that handles user authentication. It is an integral part of the Oracle Data Guard configuration. Each user needs the right set of credentials to be able to access the data that is stored on the database on which they are working. Now, in order for the same set of credentials to be available on different database nodes, Flashback Database must be configured.. Read more about downgrade database using restore point and let us know what you think.