Release 2 (8.1.6)
Part Number A76993-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Backup and Recovery Guide Release 2 (8.1.6) Part Number A76993-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 major difference between the two methods is that performing TSPITR through transportable tablespaces relaxes some of O/S TSPITR's special procedures. For example, if you restore backups to a different host separate from the primary database, then you can start the clone database as if it were the primary database using the normal database MOUNT statement instead of the clone database MOUNT statement.
|
See Also:
Oracle8i Administrator's Guide for more information about the transportable tablespace feature. |
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 into 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 do the following:
If any of these objects are included, you must drop them 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 cannot successfully complete unless you manage these relationships, either by removing or suspending the relationship or by including the related object within the recovery set.
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).
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 are 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 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 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;
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.
You must do the following, or TS_PITR_CHECK view returns rows:
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.
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:
Oracle8i SQL Reference for more information about the ALTER SYSTEM and ALTER ROLLBACK SEGMENT statements. |
Create a brand 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:
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 the clone initialization parameter 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, for example, = 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 fails and generates 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, then:
Perform the export phase of TSPITR as the user SYS, otherwise the export fails.
Shut down the clone database after a successful export:
SHUTDOWN IMMEDIATE
If any recovery set tablespaces are read-only on the primary database, then you should take them offline. Use an operating system 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 statement:
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.
To prepare the primary database for use, follow these steps:
After TSPITR on a tablespace is complete, use an operating system 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 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 are 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, then you 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 statement:
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 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 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 fails 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 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, issue the following statement 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 fails 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 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.
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 results 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 have to perform ordinary export/import recovery. Specifically, you 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.
The 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 appears when 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 statements 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, Oracle issues ORA-01152 file 1 was not restored from a sufficiently old backup at open time, not because file 1 is too recent (because it is synchronized 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 through transportable tablespaces is that for the former you must follow the special procedures for creating clone initialization parameter 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 through 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 statement instead of the clone database MOUNT statement. 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 through 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;
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|