RMAN
automatic tablespace point-in-time recovery (TSPITR) enables you to quickly
recover one or more tablespaces in an Oracle database to an earlier time,
without affecting the state of the other tablespaces and objects in the
database.
Tablespace
Point-in-Time Recovery (TSPITR): Terminology
The
following terminology is used when discussing TSPITR:
•
Target
time: The point in
time or system change number (SCN) that the tablespace will be recovered to
during TSPITR
•
Recovery
set: Data files
composing the tablespaces to be recovered
•
Auxiliary
set: Data files
required for TSPITR of the recovery set that are not themselves part of the
recovery set. The auxiliary set typically includes:
•
A
copy of the SYSTEM tablespace
•
Data
files containing undo segments from the target instance
•
In
some cases, a temporary tablespace, used during the export of database objects
from the auxiliary instance
•
Auxiliary
destination: A location
on disk that can be used to store any of the auxiliary set data files, control
files, and online logs of the auxiliary instance during TSPITR. Files stored in
the auxiliary destination can be deleted after TSPITR is complete.
Tablespace
Point-in-Time Recovery: Architecture
•
Target
database: Contains
the tablespace to be recovered
•
Control
file: Provides
backup information to RMAN
•
Backup
sets: Come from the
target database and are the source of the reconstructed tablespace
•
Archived
redo logs: Come from
the target database and are the source of the reconstructed tablespace
•
Auxiliary
instance: Is the
Oracle database instance used during the recovery process to perform the
recovery
RMAN
performs the following steps during tablespace point-in-time recovery:
1. Restores a backup control file from a
point in time before the target time to the auxiliary instance. It restores the
data files for the recovery set to the target database and the data
files for the auxiliary set to the auxiliary instance.
2. Recovers the restored data files to the
specified point in time
3. Exports the dictionary metadata about
objects in the recovered tablespace to the target database
4. Issues
SWITCH commands on the target database so that the target database control file
points to the data files in the recovery set that were recovered on the
auxiliary instance
5. Imports the dictionary metadata from the
auxiliary instance to the target instance, allowing the recovered objects to be
accessed
When to
Use TSPITR
RMAN TSPITR
can be used to:
•
Recover
data lost after an erroneous TRUNCATE TABLE statement
•
Recover
from logical corruption of a table
•
Undo
the effects of an incorrect batch job or another data manipulation language
(DML) statement that has affected only a subset of the database
•
Recover
a logical schema to a different point in time than other parts of the physical
database
Preparing
for TSPITR
•
Before
performing TSPITR, you need to determine the correct target time for your
recovery. You need to determine whether you need additional tablespaces in your
recovery set. You should evaluate what objects will be lost as a result of the
TSPITR operation and determine how you want to preserve those objects.
Determining
the Correct Target Time
It is
extremely important that you choose the right target time or SCN for TSPITR.
After you perform TSPITR and bring a tablespace online, you cannot use any
backup from a time earlier than the moment you brought the tablespace online. In
practice, this means that you cannot make a second attempt at TSPITR if you
choose the wrong target time the first time, unless you are using a recovery
catalog. However, if you have a recovery catalog, you can perform repeated
TSPITR operations to different target times.
The current
control file does not contain a record of an older incarnation of the recovered
tablespace if you do not use a recovery catalog. Recovery with a current
control file that involves the tablespace cannot use a backup taken prior to
the time when you brought the tablespace online. However, you can perform
incomplete recovery of the whole database to any time prior to or equal to the
time when you brought the tablespace online if you can restore a backup control
file from before that time.
You can use
Oracle Flashback Query, Oracle Flashback Transaction Query, and Oracle
Flashback Version Query to investigate changes to your database and to help
determine the correct target time for TSPITR.
•
You
cannot perform TSPITR a second time unless you are using a recovery catalog.
•
After
you perform TSPITR and bring the tablespace online, you cannot use a backup
from an earlier time.
•
Use
the following methods to determine the correct target time:
•
Flashback
Query
•
Flashback
Transaction Query
•
Flashback
Version Query
Determining
the Tablespaces for the Recovery Set
If you are
unsure whether you have accounted for all objects that have relationships with
the objects in the tablespaces you are performing the TSPITR operation for, you
can use the TS_PITR_CHECK view to identify any additional objects. When you
query this view, information about any objects that will prevent you from
proceeding with TSPITR is displayed. The reason why tablespace point-in-time
recovery cannot proceed is displayed in the REASON column of the TS_PITR_CHECK
view.
As an
example, if you are planning to perform TSPITR for the USERS and EXAMPLE
tablespaces, execute the following query to determine whether there are any
relationships with objects in other tablespaces that are not accounted for:
SELECT * FROM
SYS.TS_PITR_CHECK
WHERE (TS1_NAME IN
('USERS','EXAMPLE')
AND TS2_NAME NOT IN ('USERS','EXAMPLE'))
OR (TS1_NAME NOT IN
('USERS','EXAMPLE')
AND TS2_NAME IN
('USERS','EXAMPLE'));
Identifying
Objects That Will be Lost
Query the
TS_PITR_OBJECTS_TO_BE_DROPPED view to determine whether there are any objects
that will be lost as a result of performing tablespace point-in-time recovery.
As an
example, you are performing TSPITR for the USERS and EXAMPLE tablespaces to the
target time of April 3, 2011 at 8:30:00 AM. Issue the following query to
determine whether there are any objects that will be lost following your
TSPITR:
SELECT OWNER, NAME,
TABLESPACE_NAME,
TO_CHAR(CREATION_TIME,
'YYYY-MM-DD:HH24:MI:SS')
FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN
('USERS','EXAMPLE')
AND CREATION_TIME >
TO_DATE('2011-APR-03:08:30:00','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME,
CREATION_TIME;
Performing
Basic RMAN TSPITR
You have
the following options when performing TSPITR:
•
Fully
automated TSPITR:
Specify an auxiliary destination, and RMAN manages all aspects of the TSPITR
operation. This is the simplest way to perform TSPITR, and is recommended
unless you specifically need more control over the location of recovery set
files after TSPITR or auxiliary set files during TSPITR, or control over the
channel configurations or some other aspect of your auxiliary instance.
•
Customized
TSPITR with an automatic auxiliary instance: TSPITR is based on the behavior of fully automated
TSPITR, possibly still using an auxiliary destination. You can customize one or
more aspects of the behavior, such as the location of auxiliary set or recovery
set files. You can specify initialization parameters or channel configurations
for the auxiliary instance created and managed by RMAN.
•
TSPITR
with your own auxiliary instance:
Set up, start, stop, and clean up the auxiliary instance used in TSPITR. In
addition, you can manage the TSPITR process by using some of the methods
available in customized TSPITR with an automatic auxiliary instance.
Performing
Fully Automated TSPITR
you must:
•
Configure
any channels required for TSPITR on the target instance
•
Specify
a destination for RMAN to use for the auxiliary set of data files and other
auxiliary instance files
After
TSPITR has completed, back up the recovered tablespaces and bring them online.
You cannot use backups of any tablespaces that participate in TSPITR taken
prior to TSPITR after you perform TSPITR.
Note: This time format assumes that NLS_DATE_FORMAT
is set to 'yyyy-mm-dd:hh24:mi:ss' and NLS_LANG is set to
AMERICAN_AMERICA.WE8MSWIN1252.
- Configure channels required
for TSPITR on the target instance.
- Specify the auxiliary
destination using the AUXILIARY DESTINATION option.
RMAN> CONNECT TARGET
RMAN> RECOVER TABLESPACE users, example
> UNTIL
TIME '2011-06-29:08:00:00'
>
AUXILIARY DESTINATION
>
'/u01/app/oracle/oradata/aux';
1.
Back up the recovered tablespaces and bring
them online.
