Release 8.1.5
A67773-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Backup and Recovery Guide Release 8.1.5 A67773-01 |
|
This chapter describes how to perform O/S tablespace point-in-time recovery (TSPITR), and includes the following topics:
Tablespace Point-in-Time Recovery (TSPITR) enables you to quickly recover one or more non-SYSTEM tablespaces to a time that is different from that of the rest of the database. Like a table export, TSPITR enables you to recover a consistent data set; however, the data set is the entire tablespace rather than just one object.
TSPITR is most useful for recovering:
This section contains the following topics:
Prior to Oracle8, point-in-time recovery could only be performed on a subset of a database by:
There was a performance overhead associated with exporting and importing large objects, however, which created a need for a new method. TSPITR enables you to do the following:
You can perform O/S TSPITR in two different ways:
The one major difference between the two methods is that performing TSPITR via transportable tablespaces relaxes some of O/S TSPITR's special procedures. If you restore backups to a different host separate from the primary database, you can start the clone database as if it were the primary database using the normal database MOUNT command instead of the clone database MOUNT command.
See Also: For more information about the transportable tablespace feature, see the Oracle8i Administrator's Guide.
Familiarize yourself with the following terms and abbreviations, which are used throughout this chapter:
Tablespace point-in-time recovery
The copied database used for recovery in TSPITR. It has various substantive differences from a regular database.
Tablespaces that require point-in-time recovery to be performed on them.
Any other items required for TSPITR, including:
A small amount of space is required by export for sort operations. If a copy of the temporary tablespace is not included in the auxiliary set, then provide sort space either by creating a new temporary tablespace after the clone has been started or by setting AUTOEXTEND to ON on the SYSTEM tablespace files.
A feature that enables you to take a tablespace from one database and plug it in to another database. For more information, see "Recovering Transported Tablespaces". For a detailed account, see the Oracle8i Administrator's Guide.
TSPITR is a complicated procedure and requires careful planning. Before proceeding you should read this chapter thoroughly.
The primary issue you should consider is the possibility of application-level inconsistencies between tables in recovered and unrecovered tablespaces due to implicit rather than explicit referential dependencies. Understand these dependencies and find the means to resolve any possible inconsistencies before proceeding.
This section deals with the following topics:
TSPITR has several restrictions. You cannot use TSPITR to do the following:
If any of these objects are included, they will have to be dropped before TSPITR.
TSPITR provides views that can detect any data relationships between objects that are in the tablespaces being recovered and objects in the rest of the database. TSPITR will not successfully complete unless you manage these relationships, either by removing or suspending the relationship or by including the related object within the recovery set.
See Also: For more information see "Step 2: Research and Resolve Dependencies on the Primary Database".
Satisfy the following requirements before performing TSPITR.
ALTER DATABASE BACKUP CONTROLFILE TO 'controlfile_name';
This control file backup must be created at a later time than the backup that is being used. If it is not, then you may encounter an error message (ORA-01152, file 1 was not restored from a sufficiently old backup).
See Also: For more information, see "Step 4: Prepare the Clone Parameter Files".
This section describes how to prepare the clone database for TSPITR, and includes the following steps:
When TSPITR is performed on a tablespace, any objects created after the recovery time will be lost. To see 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 16-3:
| Column Name | Null? | Type |
|---|---|---|
|
OWNER |
NOT NULL |
VARCHAR2(30) |
|
NAME |
NOT NULL |
VARCHAR2(30) |
|
CREATION_TIME |
NOT NULL |
DATE |
|
TABLESPACE_NAME |
|
VARCHAR2(30) |
When querying this view, supply all the elements of the date field, otherwise the default setting will be used. Also, use the TO_CHAR and TO_DATE functions. For example, with a recovery set consisting of TS1 and TS2, and a recovery point in time of '1997-06-02:07:03:11', issue the following:
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 ('TS1','TS2') AND creation_time > to_date('97-JUN-02:07:03:11','YY-MON- DD:HH24:MI:SS') ORDER BY tablespace_name, creation_time;
See Also: For more information about the TS_PITR_OBJECTS_TO_BE_DROPPED view, see the Oracle8i Reference.
Use the TS_PITR_CHECK view to identify relationships between objects that overlap the recovery set boundaries. If this view returns rows when queried, investigate and correct the problem. Proceed with TSPITR only when TS_PITR_CHECK view returns no rows. Record all actions performed during this step so that you can retrace these relationships after completing TSPITR.
The TS_PITR_CHECK view will return rows unless you:
Supply a four-line predicate detailing the recovery set tablespace to query the TS_PITR_CHECK view. For example, with a recovery set consisting of TS1 and TS2, the SELECT statement against TS_PITR_CHECK would be as follows:
SELECT * FROM sys.ts_pitr_check WHERE (ts1_name IN ('TS1','TS2') AND ts2_name NOT IN ('TS1','TS2')) OR (ts1_name NOT IN ('TS1','TS2') AND ts2_name IN ('TS1','TS2'));
Because of the number and width of the columns in the TS_PITR_CHECK view, you may want to format the columns as follows:
column OBJ1_OWNER heading "own1" column OBJ1_OWNER format a4 column OBJ1_NAME heading "name1" column OBJ1_NAME format a5 column OBJ1_SUBNAME heading "subname1" column OBJ1_SUBNAME format a8 column OBJ1_TYPE heading "obj1type" column OBJ1_TYPE format a8 word_wrapped column TS1_NAME heading "ts1_name" column TS1_NAME format a8 column OBJ2_NAME heading "name2" column OBJ2_NAME format a5 column OBJ2_SUBNAME heading "subname2" column OBJ2_SUBNAME format a8 column OBJ2_TYPE heading "obj2type" column OBJ2_TYPE format a8 word_wrapped column OBJ2_OWNER heading "own2" column OBJ2_OWNER format a4 column TS2_NAME heading "ts2_name" column TS2_NAME format a8 column CONSTRAINT_NAME heading "cname" column CONSTRAINT_NAME format a5 column REASON heading "reason" column REASON format a57 word_wrapped
If the partitioned table TP has two partitions, P1 and P2, which exist in tablespaces TS1 and TS2 respectively, and there is a partitioned index defined on TP called TPIND, which has two partitions ID1 and ID2 (that exist in tablespaces ID1 and ID2 respectively) you would get the following output when TS_PITR_CHECK is queried against tablespaces TS1 and TS2 (assuming appropriate formatting):
own1 name1 subname1 obj1type ts1_name name2 subname2 obj2type own2 ts2_name cname reason --- ---- ----- ------ ------- ---- ------ -------- --- -------- --- ------ SYSTEM TP P1 TABLE TS1 TPIND IP1 INDEX PARTITION PARTITION SYS ID1 Partitioned Objects not fully contained in the recovery set SYSTEM TP P1 TABLE TS1 TPIND IP2 INDEX PARTITION PARTITION SYS ID2 Partitioned Objects not fully contained in the recovery set
The table SYSTEM.TP has a partitioned index TPIND that consists of two partitions, IP1 in tablespace ID1 and IP2 in tablespace ID2. Either drop TPIND or include ID1 and ID2 in the recovery set.
See Also: For more information about the TS_PITR_CHECK view, see the Oracle8i Reference.
Perform the following tasks:
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER ROLLBACK SEGMENT segment_name OFFLINE;
ALTER TABLESPACE tablespace_name OFFLINE FOR RECOVER;
This statement prevents changes being made to the recovery set before TSPITR is complete.
|
Note: If there is a subset of data that is not physically or logically corrupt that you want to query within the recovery set tablespaces, alter the recovery set tablespaces on the primary database as READ ONLY for the duration of the recovery of the clone. Take the recovery set tablespaces offline before integrating the clone files with the primary database (see "Step 5: Copy the Recovery Set Clone Files to the Primary Database"). |
See Also: For more information about the ALTER SYSTEM and ALTER ROLLBACK SEGMENT statements, see the Oracle8i SQL Reference.
Create the parameter file from a new init.ora file rather than using the production database init.ora file. Save memory by using low settings for parameters such as DB_BLOCK_BUFFERS, SHARED_POOL_SIZE, or LARGE_POOL_SIZE. If the production parameter files are used for the clone database, however, reducing these parameters can prevent the clone database from starting when other parameters are set too high--for example, the parameter ENQUEUE_RESOURCES, which allocates memory from within the shared pool.
Set the following parameters in your clone init.ora file:
| Parameter | Purpose |
|---|---|
|
CONTROL_FILES |
Identifies clone control files. Set to the name and location of the clone control files. |
|
LOCK_NAME_SPACE |
Allows the clone database to start even though it has the same name as the primary database. Set to a unique value, e.g., = CLONE. Note: Do not change the DB_NAME parameter. |
|
DB_FILE_NAME_CONVERT |
Converts datafile filenames. Set to new values if necessary. |
|
LOG_FILE_NAME_CONVERT |
Renames redo logs files. For example, if the datafiles of the primary database reside in the directory Note: You can also rename the redo logs with the ALTER DATABASE RENAME FILE statement. See "Step 5: Prepare the Clone Database". |
Perform the following tasks to prepare the clone database for TSPITR:
It is possible, although not recommended, to place the recovery set files over their corresponding files on the primary database. For more information see "Performing Partial TSPITR of Partitioned Tables".
Note:
STARTUP NOMOUNT PFILE=/path/initCLONE.ora;
ALTER DATABASE MOUNT CLONE DATABASE;
At this point, the database is automatically taken out of ARCHIVELOG mode because it is a clone. All files are offline.
ALTER DATABASE RENAME FILE 'name_of_file_in_primary_location' TO 'name_of_corresponding_file_in_clone_location';
If you did set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT but there are files that have been restored to different locations, then rename them.
ALTER DATABASE DATAFILE 'datafile_name' ONLINE;;
This section describes how to execute TSPITR, and includes the following steps:
Recover the clone database to the desired point by specifying the USING BACKUP CONTROLFILE option. Use any form of incomplete recovery as follows:
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL TIME 'YYYY-MM-DD:HH24:MI:SS'; RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
If the clone database files are not online, Oracle issues an error message.
Open the clone database with the RESETLOGS option using the following statement:
ALTER DATABASE OPEN RESETLOGS;
Because the database is a clone database, only the SYSTEM rollback segment is brought online at this point, which prevents you from executing DML statements against any user tablespace. Any attempt to bring a user rollback segment online will fail and generate an error message.
Prepare the clone database for export using the TS_PITR_CHECK view and resolving the dependencies just as you did for the primary database (see "Step 2: Research and Resolve Dependencies on the Primary Database"). Only when TS_PITR_CHECK returns no rows will the export phase of TSPITR complete.
Export the metadata for the recovery set tablespaces using the following statement:
exp sys/password point_in_time_recover=y recovery_tablespaces=tablespace_1,tablespace_2,tablespace_n
If the export phase fails and generates an error message, re-query TS_PITR_CHECK, resolve the problem, and re-run the export. Perform the export phase of TSPITR as the user SYS, otherwise the export will fail.
Shut down the clone database after a successful export:
SHUTDOWN IMMEDIATE;
If any recovery set tablespaces are read-only on the primary database, you should take them offline. Use an O/S utility to copy the recovery set files from the clone database to the primary database, taking care not to overwrite any auxiliary set files on the primary database.
Import the recovery set metadata into the primary database using the following command:
imp sys/password point_in_time_recover=true;
This import also updates the copied file's file headers and integrates them with the primary database.
See Also: For more information about Export, see Oracle8i Tuning.
To prepare the primary database for use, follow these steps:
After TSPITR on a tablespace is complete, use an O/S utility to back up the tablespace.
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 will have to recover the dropped partition along with recovering a partition whose range has expanded. See "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 as follows:
CREATE TABLE new_table AS SELECT * FROM partitioned_table WHERE 1=2;
These tables will be used to swap each recovery set partition (see "Step 3: Exchange Partitions with Stand-Alone 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 you will also need to drop them on the clone 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 stand-alone table (created in Step 1) by issuing the following command:
ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
On the primary database, take each recovery set tablespace offline:
ALTER TABLESPACE tablespace_name OFFLINE IMMEDIATE;
This prevents any further changes to the recovery set tablespaces on the primary database.
After recovering the clone and opening it with the RESETLOGS option, create a table that has the same column names and column data types as the partitioned table you are recovering. Create a table for each partition you wish 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, non-partitioned indexes that exist on the partition you wish to recover (on the table created in Step 1).
For each partition in the clone database recovery set, exchange the partitions with the stand-alone tables (created in Step 5) by issuing the following statement:
ALTER TABLE partitioned_table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
Execute export against the clone database for the recovery set tablespaces using the following statement:
exp sys/password point_in_time_recover=y recovery_tablespaces=tablespace_1,tablespace_2,tablespace_n
If the export phase fails (with the error message "ORA 29308 view TS_PITR_CHECK failure"), re-query TS_PITR_CHECK, resolve the problem, and re-run the export. Perform the export phase of TSPITR as the user SYS, otherwise the export will fail (with the error message "ORA-29303: user does not login as SYS"). Shut down the clone database after a successful export.
If any recovery set tablespaces are READ ONLY on the primary database, change them to OFFLINE. Copy the recovery set datafiles from the clone database to the primary database, taking care not to overwrite auxiliary set files on the primary database.
Import the recovery set metadata into the primary database using the following command:
imp sys/password point_in_time_recover=true
This import also updates the copied file's file headers and integrates them with the primary database.
At the primary database, bring each recovery set tablespace online:
ALTER TABLESPACE tablespace_name ONLINE;
For each recovered partition on the primary database, swap its associated stand-alone table using the following statement:
ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
If the associated indexes have been dropped, re-create them.
Back up the recovered tablespaces on the primary database. Failure to do so will result 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, issue the following command at the primary database:
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, 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.
At the primary database, take each recovery set tablespace offline:
ALTER TABLESPACE tablespace_name OFFLINE IMMEDIATE;
After opening the clone with the RESETLOGS option, create a table that has the exact same column names and column datatypes as the partitioned table you are recovering. Create a table for each partition you wish 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, non-partitioned indexes that exist on the partition you wish to recover.
For each partition in the clone recovery set, exchange the partitions into the stand-alone tables created in Step 5 by issuing the following statement:
ALTER TABLE partitioned_table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
Execute export against the clone database for the recovery set tablespaces using the following statement:
exp sys/password point_in_time_recover=y recovery_tablespaces=tablespace_1,tablespace_2,tablespace_n
If the export phase fails (with the error message "ORA 29308 view TS_PITR_CHECK failure"), re-query TS_PITR_CHECK, resolve the problem, and re-run the export. You need to perform the export phase of TSPITR as the user SYS, otherwise the export will fail (with the error message "ORA-29303: user does not login as SYS"). Shut down the clone database after a successful export.
If any recovery set tablespaces are READ ONLY on the primary database, you should change them to OFFLINE. Copy the recovery set datafiles from the clone database to the primary database, taking care not to overwrite any auxiliary set files on the primary database.
Import the recovery set meta-data into the primary database using the following command:
imp sys/password point_in_time_recover=true;
This import also updates the copied file's file headers and integrates them with the primary database.
Online each recovery set tablespace at the primary database by issuing the following statement:
ALTER TABLESPACE tablespace_name ONLINE;
At this point you must insert the stand-alone tables into the partitioned tables; you can do this by first issuing the following statement:
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 stand-alone table into the partition:
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 will result in loss of data in the event of media failure.
|
Note: As described in "TSPITR Limitations", TSPITR cannot be used to recover a tablespace that has been dropped. Therefore, if the associated tablespace of the partition has been dropped as well as the partition, you cannot recover that partition using TSPITR. You will have to perform ordinary export/import recovery. Specifically, you will have to:
Import the table into the primary database and insert it into the partitioned table using the ALTER TABLE SPLIT PARTITION or ALTER TABLE ADD PARTITION statements. |
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:
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 steps 2-13 in the procedure for "Performing Partial TSPITR of Partitioned Tables".
This section describes tuning issues relevant to TSPITR, and includes the following topics:
If space is at a premium, it is possible to recover the recovery set files "in place." In other words, recover them over their corresponding files on the primary database. Note that the recommended practice is to restore the files to a separate location and then copy across before the import phase of TSPITR is complete (see "Step 6: Import the Metadata into the Primary Database").
The advantages of recovering to a separate location are:
The disadvantage of recovering to a separate location is that more space is required for the clone database.
An advantage of recovering in place is that the amount of space taken up by the recovery set files is saved. After recovery of the clone is complete, there is no need to copy the recovery set files over to the primary database.
The disadvantage is that if the recovery is abandoned at a point before integrating the recovery set with the primary database (see "Step 6: Import the Metadata into the Primary Database" ), then you must restore the overwritten recovery set files of the primary database from a backup and recover by normal means, prolonging data unavailability. You cannot query any undamaged data within the recovery set tablespaces during recovery.
The error "ORA-01152 file 1 was not restored from a sufficiently old backup" will be encountered in the situation where no recovery is performed on the clone before grafting it to the primary. For example, if a backup is taken at time A, and a database at time B requires TSPITR to be done on a particular tablespace to take that tablespace to time A, what actually happens is that the clone database is opened RESETLOGS without any recovery having been done. When recovering the clone, the SQL*Plus commands would be:
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; CANCEL; OPEN DATABASE RESETLOGS;
At this point no redo logs have been applied, but we wish to open the database. However, since we save checkpoints to the control file in Oracle 8, it is a requirement for clone and standby databases that the backup control files need to be taken at a point after the rest of the backup was taken. Unless this is the case, "ORA-01152 file 1 was not restored from a sufficiently old backup" will be encountered on open, not because file 1 is too recent (because it is in sync with the rest of the database), but because it is more recent than the control file.
You can use the transportable tablespace feature to perform tablespace point-in-time recovery. This method is similar to the O/S TSPITR described in previous sections, except you use the transportable tablespace feature to move recovered tablespaces from the clone database to the primary database. To learn how to transport tablespaces between databases, see the Oracle8i Administrator's Guide.
The major difference between O/S TSPITR and TSPITR via transportable tablespaces is that for the former you must follow the special procedures for creating clone init.ora files, mounting the clone database, etc. O/S TSPITR assumes that the user may place the clone database on the same computer as the primary database; the special clone database commands provide error checks to prevent the corruption of the primary database on the same computer while recovering the clone database.
Performing TSPITR via transportable tablespaces relaxes this requirement. If you restore backups to a different computer separate from the primary database, you can start the clone database as if it were the primary database, using the normal database MOUNT command instead of the clone database MOUNT command. If you restore backups on the same computer as the primary database, however, follow the special procedure to create the clone database as described in O/S TSPITR, since this procedure helps prevent accidental corruption of the primary database while recovering the clone database on the same computer.
TSPITR via transportable tablespaces provides basically the same functionality as O/S TSPITR, but is more flexible since:
ALTER DATABASE DATAFILE 'datafile_name' ONLINE;
If you create the clone database as a normal database (on a computer different from the primary database), take all datafiles not in the recovery and auxiliary set offline:
ALTER DATABASE DATAFILE 'datafile_name' OFFLINE;
