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.
ñ
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:
SELECT CURRENT_SCN
FROM
V$DATABASE;
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
FROM V$RESTORE_POINT;
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:
SELECT NAME,
VALUE/60 AS RETAINED_MINUTES
FROM V$PARAMETER
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:
SELECT others.owner,
others.table_name
FROM sys.all_constraints this,
sys.all_constraints others
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
AND
this.constraint_type='R';
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:
ñ DROP
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.
ñ PURGE
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.
SHOW RECYCLEBIN;
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,
or_name,
or_name,
type
FROM recyclebin;
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:
SELECT *
FROM
"BIN$gk4pris/3akk5hg3j38kl5j4d==$0";
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:
SELECT INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = 'PERSONNEL_TST;
INDEX_NAME
------------------------------
BIN$SGKS97293K1scar4z/I249==$0
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.
Prerequisites to Flashback 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.
SELECT
OLDEST_FLASHBACK_SCN,
OLDEST_FLASHBACK_TIME
OLDEST_FLASHBACK_TIME
FROM
V$FLASHBACK_DATABASE_LOG;
And the most recent SCN that
can be reached with Flashback Database as the current SCN of the database:
SELECT CURRENT_SCN
FROM
V$DATABASE;
The DBA can also query
available guaranteed restore points as follows (sample output included):
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT
WHERE
GUARANTEE_FLASHBACK_DATABASE='YES';
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:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
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.
RMAN> show
all;
RMAN
configuration parameters for database with db_unque_name ADNIBMSX
are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP
OPTIMIZATION ON;
CONFIGURE
DEFAULT DEVICE TYPE TO DISK;
CONFIGURE
CONTROLFILE AUTOBACKUP ON;
CONFIGURE
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE
TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE
DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE
ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE
CHANNEL 1 DEVICE TYPE DISK FORMAT
'C:\app\oracle\flash_recovery_area\ADNIBMSX\BACKUPSET\%Y_%M_%D\ADNIBMSX_%
CONFIGURE
CHANNEL 2 DEVICE TYPE DISK FORMAT
'C:\app\oracle\flash_recovery_area\ADNIBMSX\BACKUPSET\%Y_%M_%D\ADNIBMSX_%
CONFIGURE
CHANNEL 3 DEVICE TYPE DISK FORMAT
'C:\app\oracle\flash_recovery_area\ADNIBMSX\BACKUPSET\%Y_%M_%D\ADNIBMSX_%
CONFIGURE
CHANNEL 4 DEVICE TYPE DISK FORMAT
'C:\app\oracle\flash_recovery_area\ADNIBMSX\BACKUPSET\%Y_%M_%D\ADNIBMSX_%
CONFIGURE
MAXSETSIZE TO UNLIMITED;
CONFIGURE
ENCRYPTION FOR DATABASE ON;
CONFIGURE
ENCRYPTION ALGORITHM 'AES256';
CONFIGURE
COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE
ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE
SNAPSHOT CONTROLFILE NAME TO
'C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_4\DATABASE\SNCFADNIBMSX.ORA';
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;
FLASHBACK DATABASE
TO RESTORE POINT MONTHLY_RSP;
FLASHBACK DATABASE TO
TIME
"TO_DATE('07/14/12','MM/DD/YY')";
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:
SHUTDOWN IMMEDIATE
STARTUP MOUNT
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:
RECOVER DATABASE;
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:
RECOVER DATABASE;
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,
totalwork,
units
from
v$session_longops
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.
No comments:
Post a Comment