Overview of Flashback Database, Restore Points and Guaranteed Restore Points in 19c

 

Overview of Flashback Database, Restore Points and Guaranteed Restore Points in 19c

Oracle Flashback Database and restore points are related data protection features that enable you to rewind data back in time to correct any problems caused by logical data corruption or user errors within a designated time window.

Restore points provide capabilities related to Flashback Database and other media recovery operations. In particular, a guaranteed restore point created at a system change number (SCN) ensures that you can use Flashback Database to rewind the database to this SCN. You can use restore points and Flashback Database independently or together.

Flashback Database

Flashback Database is similar to conventional point-in-time recovery in its effects. It enables you to return a database to its state at a time in the recent past. Flashback Database is much faster than point-in-time recovery because it does not require restoring data files from backup and requires applying fewer changes from the archived redo logs.

To enable Flashback Database, you configure a fast recovery area and set a flashback retention target. This retention target specifies how far back you can rewind a database with Flashback Database.

Flashback Database Window

The range of SCNs for which there is currently enough flashback log data to support the FLASHBACK DATABASE command is called the flashback database window. The flashback database window cannot extend further back than the earliest SCN in the available flashback logs.

You cannot back up flashback logs to locations outside the fast recovery area. To increase the likelihood that enough flashback logs are retained to meet the flashback database window

The flashback database window can be shorter than the flashback retention target, depending on the size of the fast recovery area, other backups that must be retained, and how much flashback logging data is needed. The flashback retention target is a target, not a guarantee that Flashback Database is available.

 

 

Limitations of Flashback Database

Because Flashback Database works by undoing changes to the data files that exist at the moment when you run the command, it has certain limitations.

 

Following are the limitations of Flashback Database:

 

1)      Flashback Database can only undo changes to a data file made by Oracle Database. It cannot be used to repair media failures, or to recover from accidental deletion of data files.

 

2)      You cannot use Flashback Database to undo a shrink data file operation. However, you can take the shrunken file offline, flash back the rest of the database, and then later restore and recover the shrunken data file.

 

3)      3)You cannot use Flashback Database alone to retrieve a dropped data file. If you flash back a database to a time when a dropped data file existed in the database, only the data file entry is added to the control file. You can only recover the dropped data file by using RMAN to fully restore and recover the data file.

 

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

 

5)      When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and data files affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later use Flashback Database to return to the target time 09:07 on that date, the objects and data files updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes.

 

Overview of Restore Points in a Multitenant Environment

You can create both normal and guaranteed restore points in a multitenant environment.

The basic concepts of restore points for databases are also applicable to restore points in a multitenant environment. You can create the following types of restore points in a multitenant environment:

 

1)CDB restore point

2)PDB restore point

3)Clean PDB restore point

 

CDB Restore Points

A CDB restore point serves as an alias for an SCN or a point in time in a multitenant container database (CDB). It can be a normal restore point or a guaranteed restore point.CDB restore points are similar to restore points in a non-CDB

CDB restore points are useful in the following scenarios:

1) The whole CDB needs to be recovered to a particular point in time

2) Multiple PDBs in a CDB need to be recovered to a particular point in time

 

Restore Points in PDBs

You can create normal and guaranteed restore points in a pluggable database (PDB). PDB restore points are accessible only to the PDB in which they are defined.

PDB Restore Points

A PDB restore point is a bookmark to a point in time or an SCN in a particular pluggable database (PDB). It pertains only to the PDB for which it is created and is only usable for operations on that PDB. A PDB restore point represents the PDB sub-incarnation of the point in time at which it was created.

PDB restore points can be normal restore points or guaranteed restore points. A guaranteed PDB restore point guarantees that you can perform a flashback operation for the PDB to this restore point.

A PDB restore point can be used to perform Flashback Database operations or point-in-time recovery only for the PDB in which it was create

 

Clean PDB Restore Points

A clean PDB restore point is a PDB restore point that is created when the PDB is closed and when there are no outstanding transactions for that PDB. Clean PDB restore points are only applicable to CDBs that use shared undo.

Clean PDB restore points can be normal or guaranteed restore points. Use the CREATE CLEAN RESTORE POINT command to explicitly create a clean PDB restore point. For a CDB that uses shared undo, if a PDB is closed and it has no outstanding transactions, any PDB restore point created is marked as a clean PDB restore point.

If you anticipate that you may need to rewind a PDB to a particular point in time, for example, to a state just before an application upgrade, then it is recommended that you create a clean PDB guaranteed restore point.

 

Prerequisites for Flashback Database and Restore Points

To ensure successful operation of Flashback Database and guaranteed restore points, you must first set some key database options.

Flashback Database

Configure the following database settings before enabling Flashback Database:

 

1)Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.

2) You must have a fast recovery area enabled, because flashback logs can only be stored in the fast recovery area.

3) For Oracle Real Application Clusters (Oracle RAC) databases, the fast recovery area must be in a clustered file system or in ASM.

4) For creating restore points in CDBs, the COMPATIBLE initialization parameter must be set to 12.1.0 or higher.

 

Creating CDB Restore Points

The CREATE RESTORE POINT SQL command enables you to create normal and guaranteed restore points in a multitenant container database (CDB).

 

To create a CDB restore point:

 

1)    Ensure that the prerequisites described in Prerequisites for Flashback Database and Restore Points are satisfied.

2)    Connect SQL*Plus to the root as a common user with the SYSDBA or SYSBACKUP privilege.

3)    Ensure that the CDB is open or mounted. If the CDB is mounted, then it must have been shut down cleanly (unless it is a physical standby database).

 

Run the CREATE RESTORE POINT statement to create a CDB restore point.

 

SQL> CREATE RESTORE POINT cdb_before_upgrade;

 

The following command creates a guaranteed CDB restore point:

 

SQL> CREATE RESTORE POINT cdb_grp_before_upgrade GUARANTEE FLASHBACK

Creating PDB Restore Points

You use the CREATE RESTORE POINT SQL statement to create restore points in a pluggable database (PDB).

 

The restore point can be a normal PDB restore point, guaranteed PDB restore point, or clean PDB restore point. You can create PDB restore points either when connected to the PDB or to the root.

When a PDB uses shared undo, you can create a clean restore point only if the PDB does not have any outstanding transactions.

To create a PDB restore point when connected to the PDB:

Prerequisites for Flashback Database and Restore Points

 

1)      Connect SQL*Plus to the PDB as a common user or local user with the SYSDBA or SYSBACKUP privilege.

2)      If you are creating a clean PDB restore point in a CDB that uses shared undo, then the PDB must be closed.

The following command displays the state of the PDB:

SQL> SELECT name, open_mode FROM V$PDBs;

Use the following command to close the PDB:

SQL> ALTER PLUGGABLE DATABASE CLOSE;

 

3)      If the multitenant container database (CDB) is in mounted state, then it must have been shut down consistently (unless it is a physical standby database).

4)      Set the current container to the PDB.

The following command sets to current container to the PDB my_pdb:

SQL> ALTER SESSION SET CONTAINER=my_pdb;

Create a PDB restore point by using the CREATE RESTORE POINT command.

The following command creates a normal PDB restore point:

SQL> CREATE RESTORE POINT before_patching;

The following command creates a guaranteed PDB restore point:

SQL> CREATE RESTORE POINT before_upgrade GUARENTEE FLASHBACK DATABASE

 

The following command explicitly creates a clean PDB restore point. If a clean restore point cannot be created, then an error is returned.

SQL> CREATE CLEAN RESTORE POINT before_patching;

To create a PDB restore point when connected to the CDB:

1)Ensure that the prerequisites described in Prerequisites for Flashback Database and Restore Points are met.

2)Connect SQL*Plus to the root as a common user with the SYSDBA or SYSBACKUP privilege.

3)If you are creating a clean PDB restore point in a CDB that uses shared undo, then the PDB must be closed.

The following command closes the PDB my_pdb:

SQL> ALTER PLUGGABLE DATABASE my_pdb CLOSE;

4)The CDB that contains the PDB can be open or mounted. If the CDB is mounted, then it must have been shut down consistently (unless it is a physical standby database).

The following commands place the CDB in a mounted state:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

5)Set the current container to the root.

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

6) Create a PDB restore point by using the CREATE RESTORE POINT command with the FOR PLUGGABLE DATABASE clause.

The following command creates a normal PDB restore point:

SQL> CREATE RESTORE POINT mypdb_before_patching FOR PLUGGABLE DATABASE my_pdb;

The following command creates a guaranteed PDB restore point:

SQL> CREATE RESTORE POINT mypdb_grp_before_upgrade FOR PLUGGABLE DATABASE my_pdb GUARANTEE FLASHBACK DATABASE;

The following command explicitly creates a clean PDB restore point (when the PDB is closed and has no pending transactions). If the restore point cannot be created, then an error is displayed.

SQL> CREATE CLEAN RESTORE POINT mypdb_crp_before_patching FOR PLUGGABLE DATABASE my_pdb;