Retaking Oracle Flashback Technology (Part III)
It is strongly believed and so proven that Oracle Flashback features are consistently more efficient than media recovery in most circumstances where available.
During usual operation, the database periodically writes old images of data file blocks to the flashback logs. Flashback logs are written sequentially and often in bulk. Those logs are not archived.
The rest of flashback features operate at the logical level. The logical features documented in this here are, namely:
The DBA can recover a table or set of tables to a specified point in time in the past without taking any part of the database offline. Flashback Drop
With flashback table, a DBA can also reverse the effects of a DROP TABLE statement. And all logical flashback features except Flashback Drop rely on undo data.
Flashback Table uses information in the undo tablespace rather than restored backups to retrieve the table. Therefore, when a Flashback Table operation occurs, new rows are deleted and old rows are reinserted. The rest of the database remains available while the flashback of the table takes place
In order to utilize the Flashback Table feature on one or more tables, use the FLASHBACK TABLE SQL statement with a target time or SCN.
The DBA must have the following privileges to use the Flashback Table feature:
ñ FLASHBACK ANY TABLE system privilege or FLASHBACK object privilege on the table.
ñ SELECT, INSERT, DELETE, and ALTER privileges on the table.
ñ SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privileges or the SELECT_CATALOG_ROLE role in order to flash back a table to a restore point.
In order for an object to be ready to be flashed back, the following is necessary:
ñ The object cannot be part of the following categories: tables that are part of a cluster, materialized views, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions or Advanced Queuing (AQ) tables.
ñ The structure of the table must not have been changed between the current time and the target flash back time.The following 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).
ñ Row movement must be enabled on the table, which indicates that rowids will change after the flashback occurs. If the application depends on rowids, then flashback table cannot be used.
ñ The undo data in the undo tablespace must extend far enough back in time to satisfy the flashback target time or SCN.To attain minimum flashback guarantee for Flashback Table operations, Oracle suggests setting the UNDO_RETENTION parameter to 86400 seconds (24 hours) or greater for the undo tablespace.
In this example, the DBA plans to perform a flashback of the t_personnel table after a user made some incorrect updates.
1. The DBA connects to the target database and identify the current SCN.
The DBA must remember that a FLASHBACK TABLE statement cannot be rolled back, but a another FLASHBACK TABLE statement is possible to reverse it, by specifying a time just before the current time and SCN, which can be obtained by querying the V$DATABASE view as shown below:
2. The DBA identifies the time, SCN, or desirable restore point in the table.
When creating restore points, a DBA can list available restore points by executing the following query:
SELECT NAME, SCN, TIME
Or from the RMAN CLI interface using:
RMAN> list restore point all;
3. The DBA verifies that there is enough undo data to rewind the table to the specified target.
If the UNDO_RETENTION intialization parameter is set, and the undo retention guarantee is on, then the DBA is able to use the following query to determine how long undo data is being retained:
VALUE/60 AS RETAINED_MINUTES
WHERE UPPER(NAME) = UPPER('undo_retention');
4. The DBA ensures that row movement is enabled for all objects being rewinded with Flashback Table.
ALTER TABLE t_personnel ENABLE ROW MOVEMENT;
5. The DBA analyzes whether the table that intended for flash back has dependencies on other tables. If dependencies exist, then he should decide whether to flash back these tables as well.
The DBA issues a 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:
FROM sys.all_constraints this,
WHERE this.owner = others.schema_name
AND this.table_name = others.table_name
AND this.r_owner = others.owner
AND this.r_constraint_name = others.constraint_name
The next SQL statement returns the t_personnel table to a restore point named t_personnel_prev:
FLASHBACK TABLE t_personnel TO RESTORE POINT t_personnel_prev;
The following SQL statement rewinds the t_personnel table to its state when the database was at the time specified by the SCN:
FLASHBACK TABLE t_personnel TO SCN 9170576;
As shown below, The DBA can also specify the target point in time with TO_TIMESTAMP, which may have a variation of up to 3 seconds:
FLASHBACK TABLE t_personnel
TO TIMESTAMP TO_TIMESTAMP('2010-08-10 07:16:12', 'YYYY-MM-DD HH:MI:SS');
7. Optionally, query the table to check the data.
Normally, the Oracle disables triggers on the table involved before performing a FLASHBACK TABLE operation, whose usual state is set back after the operation is completed. However, the DBA can keep triggers enabled as shown below:
FLASHBACK TABLE t_personnel
TO TIMESTAMP TO_TIMESTAMP('2010-08-10 07:16:12', 'YYYY-MM-DD HH:MI:SS');
This section explains how to retrieve objects from the recycle bin with the FLASHBACK TABLE ... TO BEFORE DROP statement.
Flashback Drop reverses the outcome of a DROP TABLE operation and it is faster than other recovery mechanisms that can be used in this scenario, such as point-in-time recovery, without any downtime or loss of recent transactions.
When the DBA drops a table, Oracle does not immediately remove the space associated with the table. Instead, the table is renamed and, along with any associated objects, and placed in the recycle bin with unique system-generated object names, which the DBA can query.
When the DBA drops a table, the table and all of its dependent objects go into the recycle bin together.
The following list summarizes the user privileges required for the operations related to Flashback Drop and the recycle bin:
Any user with drop privileges over an object can drop the object, placing it in the recycle bin.
ñ FLASHBACK TABLE ... TO BEFORE DROP
Privileges for this statement match to the privileges for DROP. That is, any user who can drop an object can perform Flashback Drop to retrieve the dropped object from the recycle bin.
Privileges for a purge of the recycle bin match the DROP privileges. Any user having DROP TABLE or DROP ANY TABLE privileges can purge the objects from the recycle bin.
ñ SELECT for objects in the Recycle Bin
Users must have SELECT and FLASHBACK privileges over an object in the recycle bin to query the object in the recycle bin. Similarly, a user who had the SELECT privilege over an object before it was dropped continue to have the SELECT privilege over the object in the recycle bin. However, the user must have FLASHBACK privilege as well. Objects must match the following prerequisites to be eligible for retrieval from the recycle bin:
ñ The recycle bin is only available for non-system, locally managed tablespaces, so they are protected by the recycle bin, including mixed dependencies.
ñ Tables that have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
ñ Partitioned index-organized tables are not protected by the recycle bin.
ñ The table must not have been purged, either by a user or by Oracle Database because of a space reclamation operation.
A DBA can use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin, by specifying either the name of the table in the recycle bin or the original table name.
1. The DBA connects to the target database via SQL*Plus and obtains the name of the dropped table in the recycle bin and issues the command.
ORIGINAL NAME RECYCLEBIN NAME TYPE DROP TIME
---------------- --------------------------------- -------- ---------------------
PERSONNEL_TST BIN$gk4pris/3akk5hg3j38kl5j4d==$0 TABLE 2010-08-11:19:09:50
It is also possible to query USER_RECYCLEBIN or DBA_RECYCLEBIN to obtain the table name.
SELECT object_name AS r_name,
R_NAME OR_NAME TYPE
-------------------------------- --------------------- ----------
BIN$gk4pris/3akk5hg3j38kl5j4d==$0 PERSONNEL_TST TABLE
BIN$SGKS97293K1scar4z/I249==$0 NDX_PERSONNEL_T INDEX
2. Optionally, the DBA can query the table in the recycle bin.
As shown in the example below:
3. Retrieve the dropped table.
The DBA can use the FLASHBACK TABLE ... TO BEFORE DROP statement.
FLASHBACK TABLE "BIN$gk4pris/3akk5hg3j38kl5j4d==$0"; TO BEFORE DROP;
Similarly, the DBA can also use the original name of the table:
FLASHBACK TABLE PERSONNEL_TST TO BEFORE DROP;
The DBA can also assign a new name to the restored table by specifying the RENAME TO clause. For example:
FLASHBACK TABLE "BIN$gk4pris/3akk5hg3j38kl5j4d==$0" TO BEFORE DROP
RENAME TO personnel_sample;
4. Optionally, verify that all dependent objects retained their system-generated recycle bin names.
The following query determines the names of the indexes of the retrieved personnel_tst table:
WHERE TABLE_NAME = 'PERSONNEL_TST;
5. Optionally, rename the retrieved indexes to their original names.
The following statement renames the index to its original name of NDX_PERSONNEL_T:
ALTER INDEX "BIN$JKS983293M1dsab4gsz/I249==$0" RENAME TO NDX_PERSONNEL_T;
6. If the retrieved table had referential constraints before it was placed in the recycle bin, then re-manually create them, since the recycle bin does not preserve them.
Exhibit 1. Creating a restore point using PL/SQLlto be embedded in an RMAN script
I hereby discuss how the commonest scenario for using Flashback Database is used to reverse unwanted changes to an Oracle database.
Flashback login is required in order to use FLASHBACK DATABASE with restore points and guaranteed restore points.
Flashback Database works by undoing changes to the data files that exist at the moment when the command is run requiring with the following prerequisites:
ñ No current data files are lost or damaged.
ñ The DBA should not be attempting to recover from accidental deletion of data files, undo a shrink data file operation, or undo a change to the database name.
ñ The DBA should not be attempting to use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file. Otherwise, all accumulated flashback log information is discarded.
ñ The DBA should not attempt to use FLASHBACK DATABASE to undo a compatibility change.
1. The DBA can connect to the target database using SQL*Plus and obtain the specific SCN, restore point, or point in time for the FLASHBACK DATABASE command.
And the most recent SCN that can be reached with Flashback Database as the current SCN of the database:
The DBA can also query available guaranteed restore points as follows (sample output included):
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
NAME SCN TIME DATABASE_INCARNATION# GUA
--------------- ---------- --------------------- --------------------- ---
MONTHLY_RSP 9177534577 11-AUG-11 01.44.49 AM 2 YES
2. The Shuts down the database consistently, ensure that it is not opened by any instance, and then mount it:
3. The DBA must repeat the query in Step 1 of this procedure.
Certain flashback log data is generated when the database is shut down. If flashback logs were deleted due to space pressure in the fast recovery area, then the target SCN may not be reachable.
4. The DBA starts RMAN and connects to the target database.
5. The DBA runs the SHOW ALL command to see the entire RMAN configuration for the database, in particular the channels to be used.
Exhibit 2. A sample RMAN configuration.
If the necessary devices and channels are already configured, then no action is necessary. Otherwise, use the CONFIGURE command to configure automatic channels, or include ALLOCATE CHANNEL commands within a RUN block.
6. The DBA will run the RMAN FLASHBACK DATABASE command.
Likewise, the DBA can specify the target time by using one of the forms of the command shown in the following examples:
FLASHBACK DATABASE TO SCN 645971612;
TO RESTORE POINT MONTHLY_RSP;
FLASHBACK DATABASE TO TIME
When the FLASHBACK DATABASE command completes, the database is left mounted and recovered to the specified target time.
7. The DBA can open the database in read-only mode in SQL*Plus and run some queries to verify the database contents.
ALTER DATABASE OPEN READ ONLY;
If the database state is consistent with the task, then the procedure ends with Step 8. Otherwise, the next step is number 9.
8. When satisfied with the results, the DBA then performs either of the following mutually exclusive actions:
ñ Make the database available for updates by opening the database with the RESETLOGS option. If the database is currently open read-only, then execute the following commands in SQL*Plus:
ALTER DATABASE OPEN RESETLOGS;
After the OPEN RESETLOGS operation, all changes to the database after the target SCN for FLASHBACK DATABASE are abandoned. Nevertheless, the DBA can use a special technique in order to rewind the database to an SCN in an abandoned incarnation branch to return the database to that range of SCNs while they remain in the flashback window.
ñ Otherwise, the DBA can use Oracle Data Pump Export to make a logical backup of the objects whose state was corrupted. After this, the DBA can use RMAN to recover the database to the present time:
Indeed, this step will undo the effect of the Flashback Database by re-applying all changes in the redo logs to the database, returning it to the most recent SCN.
After re-opening the database read/write, it is possible to import the exported objects with the Data Pump Import utility.
9. If a DBA finds that the wrong restore point , time or SCN, was used for the flashback, then the DBA must mount the database and perform one of the following mutually exclusive options:
ñ When a target time not far enough in the past has been chosen, then the DBA can use another FLASHBACK DATABASE command to rewind the database further back in time:
FLASHBACK DATABASE TO SCN 91772529; #before current SCN
ñ When a target SCN was too far in the past, then the DBA can use RECOVER DATABASE UNTIL to wind the database forward in time to the desired SCN:
RECOVER DATABASE UNTIL SCN 91776539; #after current SCN
ñ If the DBA needs to completely undo the effect of the FLASHBACK DATABASE command, then he can perform a complete recovery of the database by using the RECOVER DATABASE command without using the UNTIL clause or SET UNTIL command:
Thus, the RECOVER DATABASE command reapplies all transactions to the database, returning it to the most recent SCN.
The DBA uses the Flashback restore phase in order to rewind a database to a past target time; Oracle determines which blocks changed after the target time and restores them from the flashback logs. Once this phase is complete, Flashback Database enters the recovery phase, where it uses redo logs to reapply changes that were made after these blocks were written to the flashback logs.
A DBA can monitor Flashback Database, by querying the V$SESSION_LONGOPS view.
SQL> select sofar,
where upper(opname) = upper('Flashback Database');
SOFAR TOTALWORK UNITS
----- ---------- --------------------------------
117 195 Megabytes
The progress of Flashback Database during the recovery phase can be monitored by querying the view V$RECOVERY_PROGRESS.