Skip Headers
Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)

Part Number B14191-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

Preparing for User-Managed Tablespace Point-in-Time Recovery: Basic Steps

User-managed tablespace point-in-time recovery (TSPITR) requires careful planning. Before proceeding you should read this chapter thoroughly.

This section contains the following topics:

Step 1: Review TSPITR Requirements

Satisfy the following requirements before performing TSPITR:

  • Ensure that you have backups of all datafiles in the recovery and auxiliary set tablespaces. The datafile backups must have been created before the desired TSPITR time.

  • Ensure that you have a control file backup that is usable on the auxiliary database. To be usable, the control file must meet these requirements:

    • The control file must have been backed up before the desired TSPITR time.

    • The control file must have been backed up with the following SQL statement, where cf_name refers to the fully specified filename:

      ALTER DATABASE BACKUP CONTROLFILE TO 'cf_name';
      
  • Ensure that all files constituting the recovery set tablespaces are in the recovery set on the auxiliary database; otherwise, the export phase during tablespace transport fails.

  • Allocate enough disk space on the auxiliary host to accommodate the auxiliary database.

  • Provide enough real memory to start the auxiliary instance.

  • If the tablespace to be recovered has been renamed, ensure that the target SCN for TSPITR is after the time when the file was renamed. You cannot TSPITR a renamed tablespace to a point in time earlier than the rename. However, you can perform a DBPITR to an SCN before the rename. In this case, the tablespace reverts to its name as of the target SCN.

Step 2: Identify All of the Files in the Recovery and Auxiliary Set Tablespaces

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:

  • The filenames of the datafiles in the recovery set tablespaces

  • The filenames of the datafiles in the SYSTEM tablespace

  • The filenames of the datafiles in an undo tablespace or datafiles containing rollback segments

  • The filenames of the control files

The following useful query displays the filenames of all datafiles and control files in the database:

SELECT NAME FROM V$DATAFILE
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 (which is deprecated), 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 DISTINCT 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 DISTINCT 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;

Step 3: Determine Whether Objects Will Be Lost

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 20-1.

Table 20-1 TS_PITR_OBJECTS_TO_BE_DROPPED View

Column Name Meaning
OWNER Owner of the object to be dropped.
NAME The name of the object that will be lost as a result of TSPITR
CREATION_TIME Creation time stamp for the object.
TABLESPACE_NAME Name of the tablespace containing the object.

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 users and tools, and a recovery point in time of 19 October 2002, 15:34: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 ('users','tools') 
AND CREATION_TIME > TO_DATE('02-OCT-19:15:34:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

See Also:

Oracle Database Reference for more information about the TS_PITR_OBJECTS_TO_BE_DROPPED view

Step 4: Choose a Method for Connecting to the Auxiliary Instance

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 Oracle Database Net Services Administrator's Guide.

Step 5: Create an Oracle Password File for the Auxiliary Instance

For information about creating and maintaining Oracle password files, refer to the Oracle Database Administrator's Guide. If you do not use a password file, then you can skip this step.

Step 6: Create the Initialization Parameter File for the Auxiliary Instance

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:

  • DB_CACHE_SIZE

  • SHARED_POOL_SIZE

  • LARGE_POOL_SIZE

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 update the auxiliary control file to point to the locations to which the files were restored for the auxiliary database. 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. To view the names of the online redo log files of the primary database so that you can be sure to use unique names when creating the auxiliary, use this query on the primary database:

SELECT NAME FROM V$LOGFILE;

Caution:

If the auxiliary and primary database are on the same machine, then failing to rename the online redo log files may cause primary database corruption.

Set the parameters shown in Table 20-2 in the auxiliary initialization parameter file.

Table 20-2 Auxiliary Initialization Parameters

Parameter Purpose
DB_NAME Names the auxiliary database. Leave the name of the auxiliary database the same as the primary database.
CONTROL_FILES Identifies auxiliary control files. Set to the filename of the auxiliary control file. If the auxiliary database is on the same host as the primary database, make sure that the control file name is different from the primary database control file name.
DB_UNIQUE_NAME Allows the auxiliary database to start even though it has the same name as the primary database. Set to any unique value, for example, = AUX. This parameter is only needed if the auxiliary and primary database are on the same host.
DB_FILE_NAME_CONVERT Uses patterns to convert filenames for the datafiles of the auxiliary database. This parameter is only necessary if you are either restoring the auxiliary database on the same host as the primary host, or on a different host that uses different path names from the primary host.
LOG_FILE_NAME_CONVERT Uses patterns to convert filenames for the online redo logs of the auxiliary database. This parameter is mandatory.
LOG_ARCHIVE_DEST_1 Specifies the default directory containing the archived redo logs required for recovery. This parameter specifies the location on the auxiliary host in which the archived logs will be located.
LOG_ARCHIVE_FORMAT Specifies the format of the archived logs. You should use the same format setting used in the primary 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/control01.dbf
DB_UNIQUE_NAME = aux
DB_FILE_NAME_CONVERT=("/oracle/oradata/","/aux/")
LOG_FILE_NAME_CONVERT=("/oracle/oradata/","/aux/")
LOG_ARCHIVE_DEST_1 = 'LOCATION=/oracle/oradata/arch/'
LOG_ARCHIVE_FORMAT = arcr_%t_%s_%r.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
# you do not need to set CONTROL_FILES or DB_FILE_NAME_CONVERT because the file
# system structure on both hosts is identical
LOG_FILE_NAME_CONVERT=("/oracle/oradata/","/tmp/oradata/")
LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/arch/'
LOG_ARCHIVE_FORMAT = arcr_%t_%s_%r.arc