Oracle9i Recovery Manager User's Guide Release 1 (9.0.1) Part Number A90135-01 |
|
This chapter describes how to use Recovery Manager (RMAN) to perform tablespace point-in-time recovery (TSPITR). This chapter contains these topics:
Recovery Manager (RMAN) automated tablespace point-in-time recovery (TSPITR) enables you to quickly recover one or more tablespaces to a time that is different from that of the rest of the database.
RMAN TSPITR is most useful for recovering the following:
DROP
TABLE
or TRUNCATE
TABLE
operation
Like a table export, RMAN TSPITR enables you to recover a consistent data set; however, the data set is the entire tablespace rather than one object. As Figure 11-1 illustrates, Recovery Manager does the following:
Familiarize yourself with the following terms and abbreviations, which are used throughout this chapter:
Tablespace point-in-time recovery
The auxiliary instance used to recover the backup tablespaces. The database created by TSPITR never has independent existence: it is only an intermediate work area.
Tablespaces in the target database requiring TSPITR to be performed on them.
Any other items required for TSPITR, including:
SYSTEM
tablespace
Recovery Manager TSPITR requires careful planning. Before proceeding, read this chapter thoroughly.
This section covers the following topics:
Many of the limitations and planning steps in this chapter can also be found in Oracle9i User-Managed Backup and Recovery Guide; however, differences in limitations and planning exist. These differences are explicitly noted.
Note:
You can perform RMAN TSPITR either with or without a recovery catalog. If you do not use a recovery catalog, then note these restrictions:
When performing RMAN TSPITR, you cannot:
The primary issue for RMAN TSPITR is the possibility of application-level inconsistencies between tables in recovered and unrecovered tablespaces due to implicit rather than explicit referential dependencies. Note the following issues and have the means to resolve possible inconsistencies before proceeding.
This section contains these topics:
RMAN TSPITR only supports recovery sets that contain whole tables. For example, if you perform RMAN TSPITR on partitioned tables and spread partitions across multiple tablespaces, then RMAN returns an error message during the export phase. Recovery sets that contain either tables without their constraints or the constraints without the table also result in errors.
If you are performing TSPITR with transportable tablespaces, then you can take rollback segments in the recovery set offline--thus preventing changes being made to the recovery set before recovery is complete. RMAN TSPITR does not support recovery of tablespaces containing rollback segments. For more information about TSPITR and rollback segments, refer to Oracle9i User-Managed Backup and Recovery Guide.
The TS_PITR_CHECK
view provides information on dependencies and restrictions that can prevent TSPITR from proceeding. TS_PITR_CHECK
does not provide information, however, about dependencies and restrictions for objects owned by SYS
.
If any objects--including rollback segments--owned by SYS
are in the recovery set, then there is no guarantee that you can successfully recover these objects. TSPITR utilizes the Export and Import utilities, which do not support objects owned by SYS
. To find out which recovery set objects are owned by SYS
, issue the following statement:
SELECT OBJECT_NAME, OBJECT_TYPE FROM SYS.DBA_OBJECTS WHERE OWNER = 'SYS';
This query shows only the rollback segments owned by SYS
:
SELECT SEGMENT_NAME, SEGMENT_TYPE FROM SYS.DBA_EXTENTS WHERE SEGMENT_NAME IN ('segment_name_1','segment_name_2', 'segment_name_n') AND OWNER = 'SYS';
See Also:
Oracle9i User-Managed Backup and Recovery Guide. for more details about the |
TSPITR provides views that can detect any data relationships between objects in the recovery set and objects in the rest of the database. TSPITR cannot successfully complete unless these relationships are managed, either by removing or suspending the relationship or by including the related object within the recovery set.
See Also:
Oracle9i User-Managed Backup and Recovery Guide. to learn how to research and resolve dependency issues. |
Satisfy the following requirements discussed in this section before performing RMAN TSPITR:
For instructions on how to create and maintain Oracle password files, refer to the Oracle9i Database Administrator's Guide.
Create an init.ora
file for the auxiliary instance and set the parameters described in the following table.
Set other parameters as needed, including the parameters that allow you to connect as SYSDBA
through Oracle Net.
Following are examples of the init.ora
parameter settings for the auxiliary instance.
DB_NAME=prod1 LOCK_NAME_SPACE=_prod1 CONTROL_FILES=/oracle/aux/cf/aux_prod_cf.f DB_FILE_NAME_CONVERT=("/oracle/prod/datafile","/oracle/aux/datafile") LOG_FILE_NAME_CONVERT=("/oracle/prod/redo_log","/oracle/aux/redo_log")
See Also:
"Specifying Datafile Filenames During RMAN TSPITR" for details about |
Before beginning RMAN TSPITR, use SQL*Plus to connect to the auxiliary instance and start it in NOMOUNT
mode (specifying a parameter file if necessary):
SQL> CONNECT SYS/aux_pwd@aux_str AS SYSDBA SQL> STARTUP NOMOUNT PFILE='/oracle/aux/dbs/initAUX.ora'
Because the auxiliary instance does not yet have a control file, you can only start the instance in NOMOUNT
mode. Do not create a control file or try to mount or open the auxiliary instance for TSPITR.
The auxiliary instance must have a valid net service name. Before proceeding, use SQL*Plus to ensure that you can establish a connection to the auxiliary instance.
Use one of the following methods discussed in this section to start the RMAN command-line interface:
To connect to the auxiliary instance, target instance, and optional recovery catalog, supply the following information when starting Recovery Manager:
% rman TARGET SYS/target_pwd@target_str CATALOG rman/cat_pwd@cat_str AUXILIARY \ > SYS/aux_pwd@aux_str
where:
You can start the RMAN command-line interface without a connection to the auxiliary instance, and then use the CONNECT
command at the RMAN prompt. This example connects in the default NOCATALOG
mode:
% rman RMAN> CONNECT AUXILIARY SYS/aux_pwd@aux_str RMAN> CONNECT TARGET SYS/target_pwd@target_str
To connect to a catalog, run the CONNECT
CATALOG
command:
RMAN> CONNECT CATALOG rman/cat_pwd@cat_str
After you have completed all planning requirements, perform RMAN TSPITR. Run the following commands, where tablespace_list
is the list of tablespace names in the recovery set and recovery_end_time
is the point to which you want to recover. The following example assumes that you have configured automatic channels:
RECOVER TABLESPACE tablespace_list UNTIL recovery_end_time;
Note that if no auxiliary device configuration is specified, and if RMAN needs to automatically allocate auxiliary channels, then RMAN uses the target database device configuration. It is not necessary to specify configuration information for auxiliary channels unless they require different parameters from the target channels.
The following example assumes that you do not have automatic channels configured and so must manually allocate auxiliary channels:
# manually allocate at least one auxiliary channel RUN { ALLOCATE AUXILIARY CHANNEL . . . RECOVER TABLESPACE tablespace_list UNTIL recovery_end_time; }
Note that the format for recovery_end_time
should use the same format as the NLS_DATE_FORMAT
parameter. Also, the tablespace recovery set should not contain the SYSTEM
tablespace or any tablespace with rollback segments.
The following example performs TSPITR on tablespaces tbs_2
and tbs_3
to 8:00 p.m. on January 10, 2000:
RECOVER TABLESPACE tbs_2, tbs_3 UNTIL TIME 'JAN 10 2000 20:00:00';
Recovery Manager automatically performs the following steps during TSPITR:
RESETLOGS
option.
SWITCH
commands so that the target control file now points to the datafiles in the recovery set that were just recovered at the auxiliary database.
The tablespaces in the recovery set remain offline until after RMAN TSPITR completes successfully.
To prepare the target database for reuse after TSPITR:
% rman TARGET SYS/target_pwd@target_str
tbs_4
:
BACKUP TABLESPACE tbs_4;
SQL "ALTER TABLESPACE tbs_4 ONLINE";
% sqlplus 'SYS/aux_pwd@aux_str AS SYSDBA'
V$
views to determine the filenames of auxiliary files to be deleted. Query V$DATAFILE
of the auxiliary database to determine which datafiles are online, V$LOGFILE
for online logs, and V$CONTROLFILE
for control files. For example, run:
SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE; SQL> SELECT MEMBER FROM V$LOGFILE; SQL> SELECT NAME FROM V$CONTROLFILE;
SQL> SHUTDOWN ABORT
A variety of problems can cause TSPITR to abort. For example, if there is a conflict between the target database and the converted filename, then you have to shut down the auxiliary instance, correct the converted datafile name, issue a STARTUP
NOMOUNT
, and then run RMAN TSPITR again.
Another possible cause for failure is a lack of sufficient sort space for the Export utility. In this case, you need to edit the recover.bsq
file (on UNIX, it is located in $ORACLE_HOME/rdbms/admin
). This file contains the following:
# # tsiptr_7: do the incomplete recovery and resetlogs. This member is used once. # define tspitr_7 <<< # make the control file point at the restored datafiles, then recover them recover clone database tablespace &1&; alter clone database open resetlogs; # PLUG HERE the creation of a temporary tablespace if export fails due to lack of # temporary space. # For example in Unix these two lines would do that: #sql clone "create tablespace aux_tspitr_tmp # datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K"; } >>>
Remove the '#' symbols from the last two lines of comments and modify the statement to create a temporary tablespace. Retry the TSPITR operation, increasing the size of the tablespace until the export operation succeeds.
If TSPITR is unsuccessful for some reason, then follow the procedure below.
To respond to unsuccessful TSPITR:
SHUTDOWN ABORT;
STARTUP NOMOUNT PFILE=initAUX.ora;
You may need to tune the performance of RMAN TSPITR. This section contains these topics:
Recovery Manager restores and recovers all datafiles belonging to the tablespaces in the recovery set and auxiliary set at the auxiliary instance. Note that the auxiliary set includes the SYSTEM
tablespace plus all the tablespaces with rollback segments.
Specify a new name for any datafiles in the auxiliary set tablespace using the SET
NEWNAME
command. RMAN uses this new name as the temporary location in which to restore and recover the datafile. This new name also overrides the setting in the DB_FILE_NAME_CONVERT
parameter in the initialization parameter file. For example, to rename datafile 2
to new_df_name.f
enter:
RUN { SET NEWNAME FOR DATAFILE 2 TO '/oracle/dbs/new_df_name.f'; RECOVER ...; }
You can specify new filenames for any datafiles in recovery set tablespaces. If you specify a new name, then the new filenames replace the original filenames in the target control file.
When setting new filenames, RMAN does not check for conflicts between datafile names at the auxiliary and target databases. Any conflicts result in an RMAN error during TSPITR.
Using a datafile copy on disk is much faster than restoring a datafile. Hence, you may wish to use an appropriate copy of a datafile in the recovery or auxiliary set instead of restoring and recovering a datafile.
Recovery Manager uses a datafile copy if the following conditions are met:
filename
is the datafile name or number, and aux_datafile_name
is the datafile auxiliary name):
CONFIGURE AUXNAME FOR DATAFILE FILENAME TO aux_datafile_name;
UNTIL
clause with the following RMAN command (where 'filename'
is the datafile filename):
RUN { COPY DATAFILE 'filename' TO AUXNAME; . . . }
The following commands are examples of the conditions required by Recovery Manager:
CONFIGURE AUXNAME FOR DATAFILE '/df_1_1.dbf' TO '/backup/df_1_1.dbf'; COPY DATAFILE '/df_1_1.dbf' TO AUXNAME;
RMAN does not use a datafile copy if you use SET
NEWNAME
for the same datafile.
If RMAN uses a datafile copy and TSPITR completes successfully, then the auxiliary_datafile_name
is removed from the recovery catalog, and updated to status DELETED
in the control file. The original datafile at the target is replaced by this datafile copy after RMAN TSPITR completes.
If neither a new name nor auxiliary name is set for a datafile in an auxiliary set tablespace, then RMAN can use the converted filename specified in the auxiliary database control file to perform the restore and recovery. Recovery Manager checks for conflicts between datafile names at the auxiliary and target databases. Any conflicts result in an error.
If neither a new name or auxiliary name is set for a datafile in a recovery set tablespace, or if the file at the auxiliary name is unusable, then RMAN uses the original location of the datafile.
The following commands and parameters are used to name datafiles in the auxiliary and recovery sets during TSPITR. The order of precedence in the following table goes top to bottom, so SET
NEWNAME
takes precedence over CONFIGURE
AUXNAME
and DB_FILE_NAME_CONVERT
.
Order of Precedence | Command/Parameter | Auxiliary Set | Recovery Set |
---|---|---|---|
1 |
|
X |
X |
2 |
|
X |
X |
3 |
|
X |
|
If filenames are not converted in the auxiliary set, then RMAN signals an error during TSPITR.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|