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

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.

  
  

  See Also: "Step 6: Create the Initialization Parameter File for the Auxiliary Instance".

  
  

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.

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;

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;

Set the parameters shown in Table 20-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/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