This chapter contains the following topics: Overview of Flashback Database_ Restore Points and Guaranteed Restore Points, About Logging for Flashback Database and Guaranteed Restore Points, Prerequisites for Flashback Database and Guaranteed Restore Points, Using Normal and Guaranteed Restore Points. Alternatively, you can query USER_RECYCLEBIN or DBA_RECYCLEBIN to obtain the table name. Like a normal restore point, a guaranteed restore point serves as an alias for an SCN in recovery operations. The database restores the version of each block that is immediately before the target time. Unlike SCNs, time expressions and restore points are not ambiguous. The root is still open. Use Oracle Data Pump Export to make a logical backup of the objects whose state was corrupted. You may want to use Flashback Database to rewind the database to an SCN in the parent incarnation that is later than the SCN of the OPEN RESETLOGS operation at which the current incarnation path branched from the old incarnation. To use the Flashback Table feature on one or more tables, use the FLASHBACK TABLE SQL statement with a target time or SCN. The database incarnation is automatically reset to the incarnation that was current at the specified time or when the restore point was created. Instead, the table is renamed and, along with any associated objects, placed in the recycle bin. Use the FLASHBACK DATABASE command to rewind the CDB. The following Data Definition Language (DDL) operations change the structure of a table: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (except adding a range partition). Export one or more objects from your database with Data Pump Export. However, to recover PDBs, you must connect to the root as a user with SYSDBA or SYSBACKUP privilege. Enable the Flashback Database feature for the whole database: Optionally, disable flashback logging for specific tablespaces. Example 18-3 Tracking Flashback Database Progress - Restore Phase. The following example performs DBPITR on the target database until SCN 1000: As shown in the following examples, you can also use time expressions, restore points, or log sequence numbers to specify the SET UNTIL time: If the operation completes without errors, then DBPITR has succeeded. Reset the target database to the incarnation obtained in Step 2. You can query the V$PDB_INCARNATION view for the incarnation number as described in "About Incarnations of PDBs". Configure enough disk spindles for the file system that holds the fast recovery area. Normal restore points are lightweight. You must have a fast recovery area enabled, because flashback logs can only be stored in the fast recovery area. To perform a flashback of temp_employees: You cannot roll back a FLASHBACK TABLE statement, but you can issue another FLASHBACK TABLE statement and specify a time just before the current time. In many circumstances, this causes your database to halt. Use the RESTORE PLUGGABLE DATABASE and RECOVER PLUGGABLE DATABASE commands. Consult "Responding to a Full Fast Recovery Area" if your fast recovery area becomes full. You can obtain it by querying V$DATABASE as follows: If you have created restore points, then you can list available restore points by executing the following query: If the UNDO_RETENTION intialization parameter is set, and the undo retention guarantee is on, then you can use the following query to determine how long undo data is being retained: You can enable row movement for a table with the following SQL statement, where table is the name of the table that you are rewinding: You can issue the following SQL query to determine the dependencies, where schema_name is the schema for the table to be flashed back and table_name is the name of the table: The following SQL statement returns the hr.temp_employees table to the restore point named temp_employees_update: The following SQL statement rewinds the hr.temp_employees table to its state when the database was at the time specified by the SCN: As shown in the following example, you can also specify the target point in time with TO_TIMESTAMP: The mapping of time stamps to SCNs is not always exact.
Rewinding an OPEN RESETLOGS Operation with Flashback Database, Rewinding the Database to an SCN in an Abandoned Incarnation Branch, Recovering the Database to an Ancestor Incarnation. If flashback logging is disabled, then you cannot FLASHBACK DATABASE directly to SCNs between the guaranteed restore points and the current time. Some flashback logging data is generated when the database is shut down. For example, the file is outside the DB_FLASHBACK_RETENTION_TARGET parameters. In this case, the first time a data file block is modified after the time of the guaranteed restore point, the database stores an image of the block before the modification in the flashback logs. The following example recovers a PDB named PDB5 up to SCN 1066, and then opens it for read/write access. In such cases, the reclaimed dependent objects are not retrievable from the recycle bin. Connect to the root and enter the following commands: This example assumes that a fast recovery area is being used. You can create, and then drop, several objects with the same original name.
Oracle Data Guard Concepts and Administration. 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. The following examples return the database to a specified SCN or restore point: Flashback Database is similar to conventional point-in-time recovery in its effects. Recovering Tables and Table Partitions from RMAN Backups for information about recovering tables using previously-created RMAN backups. When flashback is enabled or when there are guaranteed restore points, the background process RVWR writes flashback data to flashback database logs in the fast recovery area. This section assumes a scenario in which you drop the wrong table. If the retrieved table had referential constraints before it was placed in the recycle bin, then re-create them. If the SCN to which you are rewinding is in the direct ancestral path, or if you are rewinding the database to a restore point, then an explicit RESET DATABASE command is not necessary for Flashback Database. Ensure that row movement is enabled for all objects that you are rewinding with Flashback Table. Depending upon the pattern of activity on your database, it can also cause significant space pressure in the fast recovery area. Following a FLASHBACK DATABASE operation, in order to have write access to the flashed back database, you must reopen it with an ALTER DATABASE OPEN RESETLOGS statement. If the fast recovery area is full, then an archived redo log that is reclaimable according to the fast recovery area rules may be automatically deleted by the fast recovery area to make space for other files. The following example shows how to create a normal restore point in SQL*Plus: This example shows how to create a guaranteed restore point: Oracle Database SQL Language Reference for reference information about the SQL CREATE RESTORE POINT statement, "Listing Restore Points" to learn how to list restore point, "Dropping Restore Points" to learn how to delete restore points. All dropped objects are stored in the recycle bin. If automatic channels are not configured, then manually allocate disk and tape channels as needed. Open the database read-only in SQL*Plus and run some queries to verify the database contents. RMAN provides support for point-in-time recovery for one or more pluggable databases (PDBs). When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. This method of logging has the following important consequences: FLASHBACK DATABASE can re-create the data file contents at the time of a guaranteed restore point by using the block images. To resolve the issue, you can issue either a SHUTDOWN ABORT or an ALTER DATABASE FLASHBACK OFF command. Figure 14-1 shows how SCNs can be generated in an incarnation branch even after an OPEN RESETLOGS operation creates a new incarnation. In this case, any flashback logs that require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted. You could create a guaranteed restore point at the start of the upgrade. This retention target specifies how far back you can rewind a database with Flashback Database. For example, run the following query to list the logs in the current database incarnation (sample output included): For example, if you discover that a user accidentally dropped a tablespace at 9:02 a.m., then you can recover to 9 a.m., just before the drop occurred. See "Limitations of Flashback Database" for details. Use the FLASHBACK DATABASE statement to return the database to a past time or system change number (SCN). The column SOFAR in Example 18-3 lists the number of megabytes that have been currently read. Thus, if flashback logging is enabled, you can rewind the database to any SCN in the continuum rather than to a single SCN only. The database must have been put in FLASHBACK mode with an ALTER DATABASE FLASHBACK ON statement unless you are flashing the database back to a guaranteed restore point. RMAN DBPITR restores the database from backups before the target time for recovery, then uses incremental backups and redo to roll the database forward to the target time. If your chosen target time was not far enough in the past, then use another FLASHBACK DATABASE command to rewind the database further back in time: If you chose a target SCN that is too far in the past, then use RECOVER DATABASE UNTIL to wind the database forward in time to the desired SCN: If you want to completely undo the effect of the FLASHBACK DATABASE command, then you can perform complete recovery of the database by using the RECOVER DATABASE command without an UNTIL clause or SET UNTIL command: The RECOVER DATABASE command reapplies all changes to the database, returning it to the most recent SCN. To create normal or guaranteed restore points, use the CREATE RESTORE POINT SQL statement, providing a name for the restore point and specifying whether it is to be a guaranteed restore point or a normal one (the default). Flashback Database of standby databases has some applications in the Data Guard environment. When you perform DBPITR of a PDB, all the data files for this PDB are recovered in-place. Any user having DROP TABLE, DROP ANY TABLE, or PURGE DBA_RECYCLE_BIN privileges can purge the objects from the recycle bin. The database then uses redo logs to reapply changes that were made after these blocks were written to the flashback logs. You can specify either the name of the table in the recycle bin or the original table name. If you are not satisfied with the state of the database, skip to Step 10.
When you perform a Flashback Database operation, the database uses flashback logs to access past versions of data blocks and also uses some data in the archived redo logs. It contains the following topics: Overview of Oracle Flashback Technology and Database Point-in-Time Recovery, Rewinding a DROP TABLE Operation with Flashback Drop, Rewinding a Database with Flashback Database, Performing Database Point-in-Time Recovery, Flashback and Database Point-in-Time Recovery Scenarios.
If the database is not a standby database, then the database returns an error. 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. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file. The flashback database window cannot extend further back than the earliest SCN in the available flashback logs. To enable logging for Flashback Database, you must set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. They remain until they are explicitly dropped. Determine the time, SCN, restore point, or log sequence that ends recovery. However, archived redo logs required to satisfy a guaranteed restore point may be deleted after they are backed up to disk or tape. Open the database read-only in SQL*Plus and perform queries as needed to ensure that the effects of the logical corruption have been reversed. TO BEFORE TIMESTAMP reverts the database back to its state one second before the specified timestamp. You must have backups of all data files from before the target SCN for DBPITR and archived logs for the period between the SCN of the backups and the target SCN. If unwanted database changes are limited to specific tables or table partitions, then you can use a previously created RMAN backup to return only these objects to a point in time before the unwanted changes occurred. Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead. Consequently, 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. 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. The procedure makes the following assumptions: You are performing DBPITR within the current database incarnation. The logical features documented in this chapter are as follows: You can recover a table or set of tables to a specified earlier point in time without taking any part of the database offline.
This restriction exists because flashback database is currently unable to reverse the effects of increasing the database version with the compatibility initialization parameter. You cannot back up flashback logs to locations outside the fast recovery area. To retrieve changes in abandoned incarnations, see "Rewinding the Database to an SCN in an Abandoned Incarnation Branch". For large databases, set the initialization parameter LOG_BUFFER to at least 8 MB. This section contains the following topics: Oracle Database Administrator's Guide for more information about Automatic Undo Management. The database automatically creates, deletes, and resizes flashback logs in the recovery area. This employee was included in the table at 14:00, the last time the report was run. You can use the LIST command to list either a specific restore point or all restore points known to the RMAN repository. You can also retrieve any table from the recycle bin, regardless of any collisions among original names, by using the unique recycle bin name of the table. You can determine how far back you can flash back the database by querying the V$FLASHBACK_DATABASE_LOG view. After the operation, the database returns the triggers to the state they were in before the operation (enabled or disabled). Configure the recovery area as described in. Oracle Database Development Guide to learn how to use Flashback Query. "Rewinding a Database with Flashback Database" explains how to use the FLASHBACK DATABASE command to reverse database changes. Flashback Database and restore points are not only effective in traditional database recovery situations but can also be useful during database upgrades, application deployments and testing scenarios when test databases must be quickly created and re-created. If you have a flashback data archive enabled for a table, then you can query data that existed far in the past. This time is before the beginning of the current incarnation. In general, you can use a guaranteed restore point as an alias for an SCN with any command that works with a normal restore point. If you are satisfied with the state of the database, then end the procedure with Step 9. In this way, a target SCN specified in FLASHBACK DATABASE can be ambiguous. 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. If you are using a target time expression instead of a target SCN, then ensure that the time format environment variables are appropriate before invoking RMAN. From that time onwards, at regular intervals, the database copies images of each altered block in every data file into the flashback logs. You can query objects in the recycle bin, just as you can query other objects. The flashback operation uses undo to re-create the original data. In this case, the database reverts to its role (primary or standby) at the target time for the Flashback Database operation. According to fast recovery area rules, a file is reclaimable when one of the following criteria is true: The file is reported as obsolete and not needed by the flashback database. The following example queries the RECYCLEBIN view to determine the original names of dropped objects: If you plan to manually restore original names for dependent objects, then ensure that you make note of each dependent object's system-generated recycle bin name before you restore the table. If no guaranteed restore points are defined, then the instance remains unaffected when RVWR encounters I/O errors. If the fast recovery area is not large enough to hold the flashback logs and files such as archived redo logs and other backups needed for the retention policy, then the database may delete flashback logs from the earliest SCNs forward to make room for other files. Before performing any operation that you may have to reverse, you can create a normal restore point. For Oracle Real Application Clusters (Oracle RAC) databases, the fast recovery area must be in a clustered file system or in ASM. The OPEN RESETLOGS operation after that DBPITR started a new incarnation. LOGGING_CLAUSE in Oracle Database SQL Language Reference for more information about operations that support NOLOGGING mode. Under the column TOTALWORK is the number of megabytes of flashback logs that must be read. 
