PART IV:
Database
Point-in-Time Recovery Using Oracle Flashback
With RMAN DBPITR the DBA has the
ability to restore the database from backups before the target time for
recovery, then uses incremental backups and redo to roll the database forward
to a specific target time, but it is possible to recover to an SCN, time, log
sequence number, or restore point, in particular.
Oracle recommends for DBAs to perform Flashback Database
rather than database point-in-time recovery whenever possible. Media recovery
with backups should be the last option in lieu of flashback technologies in
order to undo the most recent changes. But I have personally tested that
creating guaranteed restore points at the beginning of periodic full backups is
a good policy to adopt.
ñ The
database must be running in ARCHIVELOG mode.
ñ All
backups of all data files from before the target SCN for DBPITR must be
available and so archived logs for the period between the SCN of the backups
and the target SCN.
ñ The current
control file is being used.
ñ The
database is using the current server parameter file.
Otherwise, a need to set the database back to the
appropriate incarnation, or restore the controlfile or spfile may be necessary.
Likewise, the DBA can avoid errors using the SET UNTIL command to set
the target time at the beginning of the procedure, rather than specifying the UNTIL clause on the RESTORE
and RECOVER commands individually.
1.
The DBA must determine the time, SCN, restore
point, or log sequence that should end recovery.
Then, the DBA can use the
Flashback Query features in order to identify when the logical corruption
occurred and use the alert log to attempt to determine the time of the event from
which to perform recover.
Likewise, the DBA can use a
SQL query to determine the log sequence number that contains the target SCN and
then recover using this log. For example, the DBA can run:
SELECT RECID,
STAMP,
THREAD#,
SEQUENCE#,
FIRST_CHANGE#
FIRST_TIME,
NEXT_CHANGE#
FROM V$ARCHIVED_LOG
WHERE RESETLOGS_CHANGE# =
( SELECT RESETLOGS_CHANGE#
FROM
V$DATABASE_INCARNATION
WHERE
UPPER(STATUS) = 'CURRENT');
RECID STAMP
THREAD# SEQUENCE# FIRST_CHAN FIRST_TIM NEXT_CHANG
---------- ----------
---------- ---------- ---------- --------- ----------
1
347455711 1 1
31025 10-AUG-07 31031
2
347455716 1 2
31031 10-AUG-07 31033
3 347455720 1 3
31033 10-AUG-07 31036
For instance, the DBA finds
out that a user accidentally dropped a tablespace at 10:02 PM., then he can
recover to 10:00PM., just before the drop occurred. All changes to the database
made after this time are automatically lost.
2.
When using a target time expression instead of a
target SCN, the DBA makes sure the time format environment variables are
appropriate before invoking RMAN.
The following are examples of
specific Globalization Support settings useful to localization as well:
NLS_LANG =
american_america.us7ascii
NLS_DATE_FORMAT=DD-MON-RRRR HH24:MI:SS
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
4.
Then DBA executes the following operations
within a RUN block:
1.
Use SET UNTIL to specify the target time, restore point, SCN,
or log sequence number for DBPITR. If specifying a time, it should use the date
formats specified in the NLS_LANG and NLS_DATE_FORMAT environment variables.
2.
When automatic channels are not configured, the
DBA must manually allocate disk and tape channels required.
3.
Finally, the DBA should restore and recover the
database.
The following example performs
DBPITR on the target database until SCN 1000:
RUN
{
SET UNTIL SCN 9717050810;
RESTORE DATABASE;
RECOVER DATABASE;
}
As discussed in the following scenarios,
the DBA may also use time expressions, restore points, or log sequence numbers
to specify the SET UNTIL
time:
SET UNTIL TIME 'Dec 17 2012
07:00:00';
SET UNTIL SEQUENCE 9717050716;
SET UNTIL RESTORE POINT before_OrclFullBkp;
Indeed, when the operation
completes without errors, then DBPITR has succeeded.
5.
Later, the DBA can open the database read-only using
SQL*Plus and perform queries as needed to ensure that the effects of the
logical corruption have been finally reversed.
So, the DBA should open the
database in read-only mode as follows:
ALTER DATABASE OPEN READ ONLY;
6.
When satisfied with the results, the DBA can
perform either of the following mutually exclusive actions:
1.
Open the database in read/write mode, thus abandoning
all changes after the target SCN. In this case, the DBA must shut down the
database, mount it, and then execute the following command:
ALTER DATABASE OPEN RESETLOGS;
2.
Export one or more objects from the database using
Oracle Data Pump Export. Then the DBA can then recover the database to the
current point in time and re-import the exported objects, thus allowing for these
objects to return to their state before the undesired change without abandoning
all other changes.
Flashback and Database Point-in-Time Recovery
Case Studies
Case I. Returning to an OPEN RESETLOGS Operation with Flashback Database
Rather than specifying a particular
SCN or point in time for the FLASHBACK DATABASE command, however, the DBA can use FLASHBACK DATABASE TO BEFORE RESETLOGS.
The DBA will perform the
following actions, namely:
1.
Connect to the target database utilizing
SQL*Plus and verify that the beginning of the flashback window is earlier than
the time of the most recent OPEN RESETLOGS, by running the following queries:
SELECT RESETLOGS_CHANGE#
FROM V$DATABASE;
SELECT
OLDEST_FLASHBACK_SCN
FROM
V$FLASHBACK_DATABASE_LOG;
When V$DATABASE.RESETLOGS_CHANGE#
is greater than V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN,
then the DBA can use Flashback Database to reverse the OPEN
RESETLOGS.
2.
The, the DBA will shut down the database, mount
it, and recheck the flashback window. When the resetlogs SCN is still within
the flashback window, then he will proceed to the next step.
3.
Then DBA will then connect to the target
database utilizing RMAN and perform a flashback to the SCN just before the RESETLOGS, by using the following command:
FLASHBACK DATABASE TO BEFORE RESETLOGS;
As with other uses of FLASHBACK DATABASE, when
the target SCN is before the beginning of the flashback database window, an
error is returned and the database is not modified. Notwithstanding, if the
command completes successfully, otherwise, then the database is left mounted
and recovered to the most recent SCN before the OPENRESETLOGS
in the previous incarnation.
4.
The DBA can now 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.
Open the database read-only as
follows:
ALTER DATABASE OPEN READ ONLY;
5.
In order to make the database available for
updates once more, the DBA will shut down the database, mount it, and then
execute the following command:
ALTER DATABASE OPEN RESETLOGS;
A DBA can use Flashback Database
across OPEN RESETLOGS in order to perform the
following functions in a Data Guard environment:
ñ Flashback
to undo logical standby switchovers
In this scenario, the database
reverts to its role (primary or standby) at the target time for the Flashback
Database operation.
ñ Undo of a
physical standby activation
The DBA may temporarily
activate a physical standby database, use it for testing or reporting purposes,
and then use Flashback Database to return it to its role as a physical standby.
This technique can be use to implement Active Data Guard, among others, and in
many instances to improve business processes, by repeatedly reutilizing a
standby environment for testing, quality assurance or business continuity, in
many self-improvement production support environments.
ñ Ongoing utilization
of a standby database for testing
The use of Flashback Database
means that there is no need to rely on the use of storage snapshots.
In order to perform a reversal of a database to an SCN in
a rather complex abandoned incarnation scenario, the DBA must perform the
following tasks:
1.
Connect to the target database using SQL*Plus
and verify that the flashback logs contain enough information to flash back to that
particular SCN, by running the query:
SELECT
OLDEST_FLASHBACK_SCN AS OLD_FBSC
FROM
V$FLASHBACK_DATABASE_LOG;
2.
Then, determine the target incarnation number
for the Flashback Database operation, i.e., the incarnation key for the parent
incarnation, by running the query:
SELECT PRIOR_INCARNATION#
AS PRIOR_ENCARNATION
FROM V$DATABASE_INCARNATION
WHERE UPPER(STATUS)
= 'CURRENT';
3.
Later, use RMAN to connect to the target
database.
4.
Then, shut down the database, and then mount it with
the following options:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
5.
Afterwards, he can set the database incarnation
to the parent incarnation, by running:
RESET DATABASE TO INCARNATION 1;
6.
Then, run the FLASHBACK
DATABASE, specifying the target SCN.
FLASHBACK DATABASE TO SCN 9717050716;
7.
Can open the database in read-only mode
utilizing SQL*Plus to perform queries as necessary to ensure that the logical
corruption outcome has been reversed.
ALTER DATABASE OPEN READ ONLY;
8.
Finally, in order to make the database available
for updates again, shut down the database, mount it, and then run the following
command:
ALTER DATABASE OPEN RESETLOGS;
No comments:
Post a Comment