Oracle9i User-Managed Backup and Recovery Guide Release 1 (9.0.1) Part Number A90134-01 |
|
This chapter describes how to perform user-managed tablespace point-in-time recovery (TSPITR) with the transportable tablespace feature.
This chapter includes the following topics:
Tablespace point-in-time recovery (TSPITR) with the transportable tablespace feature enables you to quickly recover one or more tablespaces (other than the SYSTEM
tablespace) to a time that is prior to the rest of the database.
User-managed TSPITR is most useful for recovering the following:
DROP
TABLE
or TRUNCATE
TABLE
operation
DROP
TABLESPACE
operation
Familiarize yourself with the following terms and abbreviations, which are used throughout this chapter:
Tablespace point-in-time recovery
The database containing the tablespace or tablespaces that you want to recover to a prior point in time.
A copy of the current database that is restored from a backup. It includes restored backups of the following:
SYSTEM
tablespace
All backups must be from a point in time prior to the desired recovery time.
All the tablespaces that require point-in-time recovery to be performed on them.
All objects that are part of the recovery set must be self-contained: there can be no dependencies on objects outside the recovery set. For example, if a table is part of the recovery set and its indexes are in a separate tablespace, then the recovery set must include the tablespace containing the index. Alternatively, the index can be dropped. The recovery set tablespaces can be checked for self-containment with the procedure DBMS_TTS.TRANSPORT_SET_CHECK
.
Any other items required for restoring the auxiliary database, including:
SYSTEM
tablespace
A rapid method of transporting tablespaces across databases by unplugging them from a source database and plugging them into a target database. The unplugging and plugging is done with the Export and Import utilities. Note that there is no actual export and import of the table data, but simply an export and import of internal metadata. During the procedure, the datafiles of the transported tablespaces are made part of the target database.
In releases prior to Oracle9i, you had the following two methods for performing user-managed TSPITR:
Oracle9i TSPITR should be performed by using the transportable tablespace feature. This procedure is relatively easy to use and is less error prone than the traditional method, which is currently deprecated (although not yet unsupported).
Conceptually, TSPITR is performed by dropping the tablespaces to be recovered from the primary database, restoring a copy of the database called an auxiliary database and recovering it to the desired point in time, then transporting the relevant tablespaces from the auxiliary database to the current version of the primary database.
For ease of use, it is highly recommended that you place the auxiliary and primary databases on different hosts. Nevertheless, you can also perform TSPITR when the databases are located on the same host.
The basic procedure for performing user-managed TSPITR is as follows:
Oracle9i Database Administrator's Guide for a complete account of how to use the transportable tablespace feature
See Also:
TSPITR requires careful planning. Before proceeding you should read this chapter thoroughly.
This section contains the following topics:
Satisfy the following requirements before performing TSPITR:
Before you create the auxiliary database, make sure that you connect to the primary database with administrator privileges and obtain all of the following information about the primary database:
SYSTEM
tablespace
The following useful query displays the filenames of all datafiles, control files, and online redo logs in the database:
SELECT NAME FROM V$DATAFILE UNION ALL SELECT MEMBER FROM V$LOGFILE UNION ALL SELECT NAME FROM V$CONTROLFILE;
To determine the filenames of the datafiles in the SYSTEM
and recovery set tablespaces, execute the following query and replace RECO_TBS_1
, RECO_TBS_2
, and so forth with the names of the recovery set tablespaces:
SELECT t.NAME AS "reco_tbs", d.NAME AS "dbf_name" FROM V$DATAFILE d, V$TABLESPACE t WHERE t.TS# = d.TS# AND t.NAME IN ('SYSTEM', 'RECO_TBS_1
', 'RECO_TBS
_2
');
If you run the database in manual undo management mode, then the following query displays the names of the tablespaces containing rollback segments as well as the names of the datafiles in the tablespaces:
SELECT r.TABLESPACE_NAME AS "rbs_tbs", d.FILE_NAME AS "dbf_name" FROM DBA_ROLLBACK_SEGS r, DBA_DATA_FILES d WHERE r.TABLESPACE_NAME=d.TABLESPACE_NAME;
If you run the database in automatic undo management mode, then the following query displays the names of the undo tablespaces as well as the names of the datafiles in the tablespaces:
SELECT u.TABLESPACE_NAME AS "undo_tbs", d.FILE_NAME AS "dbf_name" FROM DBA_UNDO_EXTENTS u, DBA_DATA_FILES d WHERE u.TABLESPACE_NAME=d.TABLESPACE_NAME;
When TSPITR is performed on a tablespace, any objects created after the recovery time are lost. To determine which objects will be lost, query the TS_PITR_OBJECTS_TO_BE_DROPPED
view on the primary database. The contents of the view are described in Table 7-1.
When querying this view, supply all the elements of the date field, otherwise the default setting is used. Also, use the TO_CHAR
and TO_DATE
functions. For example, with a recovery set consisting of sales_1
and sales_2
, and a recovery point in time of '2000-06-02:07:03:11'
, execute the following SQL script:
SELECT OWNER, NAME, TABLESPACE_NAME, TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS') FROM SYS.TS_PITR_OBJECTS_TO_BE_DROPPED WHERE TABLESPACE_NAME IN ('SALES_1','SALES_2') AND CREATION_TIME > TO_DATE('00-JUN-02:07:03:11','YY-MON-DD:HH24:MI:SS') ORDER BY TABLESPACE_NAME, CREATION_TIME;
See Also:
Oracle9i Database Reference for more information about the |
You must be able to connect to the auxiliary instance. You can either use Oracle Net or operating system authentication. To learn how to configure networking files, refer to Oracle9i Net Services Administrator's Guide.
For information about creating and maintaining Oracle password files, refer to the Oracle9i Database Administrator's Guide. If you do not use a password file, then you can skip this step.
Create a new initialization parameter file rather than copying and then editing the production database initialization parameter file. Save memory by using low settings for parameters such as the following:
Note that reducing the preceding parameter settings can prevent the auxiliary database from starting when other dependent parameters are set too high--for example, the initialization parameter ENQUEUE_RESOURCES
, which allocates memory from within the shared pool.
The auxiliary database can be either on the same host as the primary database or on a different host. Because the auxiliary database filenames are identical to the primary database filenames in the auxiliary control file, you must rename them in this control file so that they point to the restored locations. If the auxiliary database is on the same machine as the primary database, or if the auxiliary database is on a different machine that uses different path names, then you must rename the control files, datafiles, and online redo logs. If the auxiliary database is on a different machine with the same path names, then you can rename just the online redo logs.
Set the parameters shown in Table 7-2 in the auxiliary initialization parameter file.
Set other parameters as needed, including the parameters that allow you to connect as SYSDBA
through Oracle Net.
For example, the auxiliary parameter file for a database on the same host as the primary could look like the following:
DB_NAME = prod1 CONTROL_FILES = /oracle/aux/cf1.f LOCK_NAME_SPACE = aux DB_FILE_NAME_CONVERT=("/oracle/dbs/","/oracle/aux/") LOG_FILE_NAME_CONVERT=("/oracle/dbs/","/oracle/aux/") LOG_ARCHIVE_DEST_1 = 'LOCATION=/oracle/work/arc_dest/arc' LOG_ARCHIVE_FORMAT = r_%t_%s.arc
The auxiliary parameter file for a database on a different host with the same path names as the primary could look like the following:
DB_NAME = prod1 CONTROL_FILES = /oracle/aux/cf1.f LOG_FILE_NAME_CONVERT=("/oracle/dbs/","/oracle/aux/") LOG_ARCHIVE_DEST_1 = 'LOCATION=/oracle/work/arc_dest/arc' LOG_ARCHIVE_FORMAT = r_%t_%s.arc
The procedure for restore and recovery of the auxiliary database differs depending on whether the auxiliary database is on the same host as the primary database. The examples in this section assume:
prod1
located on host prim_host
.
sales_1
and sales_2
. Tablespace sales_1
contains datafile /oracle/dbs/sales_1.f
and tablespace sales_2
contains datafile /fs2/sales_2.f
.
SYSTEM
tablespace datafile /oracle/dbs/system.f
, the undo tablespace datafile /oracle/dbs/undo.f
, and the control file /oracle/dbs/cf1.f
.
/oracle/dbs/log1.f
and /oracle/dbs/log2.f
.
/oracle/dbs
The different cases are described in the following sections:
The following examples assume the case in which you restore the auxiliary database to the same host as the primary database. In this scenario, all of the primary database files are contained in /oracle/dbs
, and you want to restore the auxiliary database to /oracle/dbs/aux
. So, you set DB_FILE_NAME_CONVERT
and LOG_FILE_NAME_CONVERT
to convert the filenames from /oracle/dbs
to /oracle/dbs/aux
.
Perform the following tasks to restore and recover the auxiliary database:
/oracle/dbs/cf1.f # control file /oracle/dbs/undo.f # datafile in undo tablespace /oracle/dbs/system.f # datafile in SYSTEM tablespace
And the recovery set consists of the following datafiles:
/oracle/dbs/sales_1.f # datafile in sales_1 tablespace /oracle/dbs/sales_2.f # datafile in sales_2 tablespace
You can restore backups of the auxiliary set files and recovery set files to a new location as follows:
cp /backup/cf1.f /aux/cf1.f cp /backup/undo.f /aux/undo.f cp /backup/system.f /aux/system.f cp /backup/sales_1.f /aux/sales_1.f cp /backup/sales_2.f /aux/sales_2.f
STARTUP NOMOUNT PFILE=/aux/initAUX.ora
CLONE
keyword:
ALTER DATABASE MOUNT CLONE DATABASE;
The CLONE
keyword causes Oracle to take all datafiles offline automatically.
DB_FILE_NAME_CONVERT
and LOG_FILE_NAME_CONVERT
. In our scenario, all datafiles and online redo logs are renamed by initialization parameters, so no manual renaming is necessary.
SELECT NAME FROM V$DATAFILE UNION ALL SELECT MEMBER FROM V$LOGFILE UNION ALL SELECT NAME FROM V$CONTROLFILE /
If not, then rename the files manually as in the previous step.
ALTER DATABASE DATAFILE /oracle/dbs/aux/system.f ONLINE; ALTER DATABASE DATAFILE /oracle/dbs/aux/sales_1.f ONLINE; ALTER DATABASE DATAFILE /oracle/dbs/aux/sales_2.f ONLINE; ALTER DATABASE DATAFILE /oracle/dbs/aux/undo.f ONLINE;
At this point, the auxiliary database is mounted and ready for media recovery.
USING
BACKUP
CONTROLFILE
option. Use any form of incomplete recovery as described in "Performing Incomplete User-Managed Media Recovery". The following example uses cancel-based incomplete recovery:
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
RESETLOGS
option using the following statement:
ALTER DATABASE OPEN RESETLOGS;
The following example assumes that you create the auxiliary database on a different host called aux_host
. The auxiliary host has the same path names as the primary host. Hence, you do not need to rename the auxiliary database datafiles. So, you do not need to set DB_FILE_NAME_CONVERT
, although you should set LOG_FILE_NAME_CONVERT
.
To restore and recover the auxiliary database:
/oracle/dbs/cf1.f # control file /oracle/dbs/undo.f # datafile in undo tablespace /oracle/dbs/system.f # datafile in SYSTEM tablespace
And the recovery set consists of the following datafiles:
/oracle/dbs/sales_1.f # 1st datafile in sales_1 tablespace /oracle/dbs/sales_2.f # 2nd datafile in sales_2 tablespace
These files will occupy the same locations in the auxiliary host.
STARTUP NOMOUNT PFILE=/aux/initAUX.ora
CLONE
keyword:
ALTER DATABASE MOUNT CLONE DATABASE;
The CLONE
keyword causes Oracle to take all datafiles offline automatically.
DB_FILE_NAME_CONVERT
and LOG_FILE_NAME_CONVERT
. In our scenario, the datafiles do not require renaming, and the logs are converted with LOG_FILE_NAME_CONVERT
. So, no manual renaming is necessary.
SELECT NAME FROM V$DATAFILE UNION ALL SELECT MEMBER FROM V$LOGFILE UNION ALL SELECT NAME FROM V$CONTROLFILE /
If not, then rename them manually as in the previous step.
ALTER DATABASE DATAFILE /oracle/dbs/system.f ONLINE; ALTER DATABASE DATAFILE /oracle/dbs/sales_1.f ONLINE; ALTER DATABASE DATAFILE /oracle/dbs/sales_2.f ONLINE; ALTER DATABASE DATAFILE /oracle/dbs/undo.f ONLINE;
At this point, the auxiliary database is mounted and ready for media recovery.
USING
BACKUP
CONTROLFILE
option. Use any form of incomplete recovery as described in "Performing Incomplete User-Managed Media Recovery". The following example uses cancel-based incomplete recovery:
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
RESETLOGS
option using the following statement:
ALTER DATABASE OPEN RESETLOGS;
This case should be treated exactly like "Restoring and Recovering the Auxiliary Database on the Same Host". The same guidelines for renaming files apply in both cases.
After you have completed the preparation stage, begin the actual TSPITR procedure as described in Oracle9i Database Administrator's Guide. The procedure occurs in the following steps:
In this step, you recover the auxiliary database to the desired noncurrent time, then unplug the desired tablespaces.
To unplug the auxiliary database tablespaces:
ALTER
TABLESPACE
...
READ
ONLY
statement. For example, make sales_1
and sales_2
read-only as follows:
ALTER TABLESPACE sales_1 READ ONLY; ALTER TABLESPACE sales_2 READ ONLY;
EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2',TRUE,TRUE);
SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
This query should return no rows after all dependencies are managed. Refer to Oracle9i Database Administrator's Guide for more information about this table.
% exp SYS/pwd TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2) TTS_FULL_CHECK=y
This command generates an export file named expdat.dmp
.
In this step, you transport the recovery set tablespaces into the primary database.
To plug the recovery set tablespaces into the primary database:
DROP
TABLESPACE
statement. For example:
DROP TABLESPACE sales_1 INCLUDING CONTENTS; DROP TABLESPACE sales_2 INCLUDING CONTENTS;
% cp /net/aux_host/aux/sales_1.f /net/primary_host/oracle/dbs/sales_1.f % cp /net/aux_host/aux/sales_2.f /net/primary_host/oracle/dbs/sales_2.f
expdat.dmp
to the primary host. For example, enter:
% cp /net/aux_host/aux/expdat.dmp /net/primary_host/oracle/dbs/expdat.dmp
% imp TRANSPORT_TABLESPACE=y FILE=expat.dmp DATAFILES=('/oracle/dbs/sales_1.f','/oracle/dbs/sales_2.f')
ALTER
TABLESPACE
READ
WRITE
statement. For example:
ALTER TABLESPACE sales_1 READ WRITE; ALTER TABLESPACE sales_2 READ WRITE;
Partitioned tables can span multiple tablespaces. Follow this procedure only if the recovery set does not fully contain all of the partitions.
This section describes how to perform partial TSPITR of partitioned tables that have a range that has not changed or expanded, and includes the following steps:
Often you have to recover the dropped partition along with recovering a partition whose range has expanded. Refer to "Performing TSPITR of Partitioned Tables When a Partition Has Been Dropped".
Note:
This table should have the exact same column names and column datatypes as the partitioned table you are recovering. Create the table using the following template:
CREATE TABLE new_table AS SELECT * FROM partitioned_table WHERE 1=2;
These tables are used to swap each recovery set partition (see "Step 3: Exchange Partitions with Standalone Tables").
Drop the indexes on the partition you wish to recover, or create identical, non-partitioned indexes that exist on the partition you wish to recover. If you drop the indexes on the partition being recovered, then you need to drop them on the auxiliary database (see "Step 6: Drop Indexes on Partitions Being Recovered"). Rebuild the indexes after TSPITR is complete.
Exchange each partition in the recovery set with its associated standalone table (created in Step 1) by issuing the following statement, replacing the variables with the names of the appropriate objects:
ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
On the primary database, drop each tablespace in the recovery set. For example, enter the following, replacing tablespace_name
with the name of the tablespace:
DROP TABLESPACE tablespace_name INCLUDING CONTENTS;
After recovering the auxiliary database and opening it with the RESETLOGS
option, create a table in the SYSTEM
tablespace that has the same column names and column data types as the partitioned table you are recovering. You must create the table in the SYSTEM
tablespace: otherwise, Oracle issues the ORA-01552
error.
Create a table for each partition you wish to recover. These tables are used later to swap each recovery set partition.
Drop the indexes on the partition you wish to recover, or create identical, non-partitioned indexes that exist on the partition you wish to recover (on the table created in Step 1).
For each partition in the auxiliary database recovery set, exchange the partitions with the standalone tables (created in Step 5) by executing the following SQL script, replacing the variables with the appropriate object names:
ALTER TABLE partitioned_table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
Export the recovery set tablespaces from the auxiliary database and then import them into the primary database as described in "Performing TSPITR with Transportable Tablespaces".
For each recovered partition on the primary database, swap its associated standalone table using the following statement, replacing the variables with the appropriate object names:
ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
If the associated indexes have been dropped, then re-create them.
Back up the recovered tablespaces on the primary database. Failure to do so results in loss of data in the event of media failure.
This section describes how to perform TSPITR on partitioned tables when a partition has been dropped, and includes the following steps:
When a partition is dropped, the range of the partition above it expands downwards. Therefore, there may be records in the partition above that should actually be in the dropped partition after it has been recovered. To ascertain this, run the following SQL script at the primary database, replacing the variables with the appropriate values:
SELECT * FROM partitioned_table WHERE relevant_key BETWEEN low_range_of_partition_that_was_dropped AND high_range_of_partition_that_was_dropped;
If any records are returned, then create a temporary table in which to store these records so that if necessary they can be inserted into the recovered partition later.
Delete all the records stored in the temporary table from the partitioned table.
On the primary database, drop each tablespace in the recovery set. For example, enter the following, replacing tablespace_name
with the name of the tablespace:
DROP TABLESPACE tablespace_name INCLUDING CONTENTS;
After opening the auxiliary database with the RESETLOGS
option, create a table in the SYSTEM
tablespace that has the same column names and column data types as the partitioned table you are recovering. You must create the table in the SYSTEM
tablespace: otherwise, Oracle issues the ORA-01552
error. Create a table for each partition that you want to recover. These tables will be used later to swap each recovery set partition.
Drop the indexes on the partition you wish to recover, or create identical, nonpartitioned indexes that exist on the partition you wish to recover.
For each partition in the auxiliary recovery set, exchange the partitions into the standalone tables created in Step 5 by issuing the following statement, replacing the variables with the appropriate values:
ALTER TABLE partitioned_table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
Export the recovery set tablespaces from the auxiliary database and then import them into the primary database as described in "Performing TSPITR with Transportable Tablespaces".
At this point you must insert the standalone tables into the partitioned tables; you can do this by first issuing the following statement, replacing the variables with the appropriate values:
ALTER TABLE table_name SPLIT PARTITION partition_name AT (key_value) INTO (PARTITION partition_1_name TABLESPACE tablespace_name, PARTITION partition_2_name TABLESPACE tablespace_name);
Note that at this point, partition 2 is empty because keys in that range have already been deleted from the table.
Issue the following statement to swap the standalone table into the partition, replacing the variables with the appropriate values:
ALTER TABLE EXCHANGE PARTITION partition_name WITH TABLE table_name;
Now insert the records saved in Step 2 into the recovered partition (if desired).
Back up the recovered tablespaces in the primary database. Failure to do so results in loss of data in the event of media failure.
This section describes how to recover partitioned tables when a partition has been split, and includes the following sections:
For each partition you wish to recover whose range has been split, drop the lower of the two partitions so that the higher expands downwards. In other words, the higher partition has the same range as before the split. For example, if P1
was split into partitions P1A
and P1B
, then P1B
must be dropped, meaning that partition P1A
now has the same range as P1
.
For each partition that you wish to recover whose range has split, create a table that has exactly the same column names and column datatypes as the partitioned table you are recovering. For example, execute the following, replacing the variables with the appropriate values:
CREATE TABLE new_table AS SELECT * FROM partitioned_table WHERE 1=2;
These tables will be used to exchange each recovery set partition in Step 3.
Follow the same procedure as for "Performing Partial TSPITR of Partitioned Tables", but skip the first step of this procedure. In other words, start with "Step 2: Drop the Indexes on the Partition Being Recovered" and follow all subsequent steps.
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|