Oracle RMAN 11g Backup and Recovery (124 page)

Chapter 14: RMAN Advanced Recovery Topics
359

recovery catalog (if you use one) and issue the
recover tablespace
command. RMAN will do the rest of the work for you.

In the following code snippet, we provide an example of using the
recover tablespace
command to recover the TEST_RECOVER tablespace. In this example, we use the optional parameter
auxiliary destination
to indicate where RMAN and Oracle should create the files associated with the auxiliary database. Using this parameter makes this recovery a
customized
TSPITR with an automatic instance.
If you do not use this parameter, the TSPITR is known as a
fully automated TSPITR recovery.

Note that if you use the
auxiliary destination
parameter, the destination directory should already be created, and Oracle must be able to write to that destination. Also note that there is no trailing slash (\ or /, depending on your OS) in the destination pathname. Including a slash will cause TSPITR to fail (and the message you get isn’t exactly all that descriptive). Here is an example of the
recover tablespace
command that we used to successfully perform TSPITR in Oracle Database 10
g
Release 2:

recover tablespace test recover

until time "to date('02/21/2006 00:18:00','mm/dd/yyyy hh24:mi:ss')"

auxiliary destination 'c:\oracle\auxdest';

NOTE

To do automatic TSPITR, you must have configured channels on the

target. That way, channels used in the auxiliary instance will be

the same as those on the target.

Once the TSPITR has been completed, you should be able to look at the objects in the recovered tablespace and find that they have been recovered to the point in time you requested.

You need to bring the tablespaces recovered back online to use them. From RMAN, you can issue the command

Sql 'alter tablespace test recover online';

If an error occurs, Oracle leaves the auxiliary instance and its related datafiles intact. You can try to correct the problem and restart the recovery. In this case, you would restart RMAN using the
auxiliary
parameter, connecting to the auxiliary instance (as we demonstrate later, in the section

“Manual TSPITR”).

If the auxiliary instance creation is not entirely successful, it may be easier to just remove the auxiliary instance and its service than to restart the recovery using the manual TSPITR process. First, figure out what failed, and then remove the auxiliary instance/service and restart the automated TSPITR process. You can remove the auxiliary instance/service by issuing the following command from SQL*Plus when logged in as SYSDBA:

exec dbms backup restore.manageauxinstance('auxiliary sid name',1);

Note that you need to put the SID that Oracle assigned to the auxiliary instance in the place of the
auxiliary_sid_name
placeholder. The name of the SID will be listed in the RMAN output.

This will clean up any old auxiliary instances before you start your TSPITR recovery. You will want to go to the auxiliary destination directory after you execute this command and remove any files that are in that directory.

360
Part III: Using RMAN Effectively

Customized Automated TSPITR with an Automatic Instance

We already mentioned that you can customize some aspects of the automatic instance creation when performing TSPITR. We demonstrated the use of the
auxiliary destination
parameter to indicate where the recovery set should be created. Other ways of customizing the creation of the TSPITR, and still allowing Oracle to create the instance for you, include the following:

■ Using the
set newname
command to indicate the location of the individual datafiles of the recovery set.

■ Using the
configure auxname
command to define the name of the auxiliary instance.

■ Creating your own parameter file for the auxiliary instance and supplying parameters such as
db_file_name_convert
in that parameter file. This can be done by creating a file called parms_auxint.ora in $ORACLE_HOME/rdbms/admin (this filename and location are OS dependent). Optionally, you can use the RMAN command
set auxiliary instance
parameter file
to indicate the path on the client where the auxiliary instance parameter file resides. See the next section for more information on parameters that apply when configuring an instance for TSPITR.

Once you have customized your auxiliary instance, you can have RMAN create it for you by issuing the
recover tablespace
command, as shown earlier in the section “Performing Automated TSPITR.”

Manual TSPITR

Oracle supports the creation of your own auxiliary instance for TSPITR. You can also use manual TSPITR to complete a failed automatic TSPITR. The steps listed earlier in the preparation phase still apply. You then must prepare the auxiliary instance and execute the TSPITR process. We discuss these steps in the next few sections.

Prepare the Auxiliary Instance

The first thing we need to do is get an auxiliary instance up and running. As already defined, an auxiliary instance is just a temporary instance that RMAN uses to perform TSPITR. The auxiliary instance must reside on the same box as the target database, and you will never be able to execute any type of DML on the auxiliary instance.

Before we can start TSPITR, we need to get the auxiliary instance ready. This is a manual process that RMAN has no part in, but it’s not unlike creating a normal database instance. To create the auxiliary instance, perform these actions:

1.
Create the password file.

2.
Create the database parameter file if you are running the restore from OEM.

3.
If you are running Oracle on Windows NT, add the database service with the
oradim
program (for example,
oradim –new –sid recover
).

4.
Start the auxiliary instance and check to make sure that you have network connectivity.

You should be familiar in general with each of these steps, as these are typical DBA tasks.

Basically, it’s a lot like creating an instance in preparation for issuing the
create database
command. We need to note a few issues specific to TSPITR, however, so let’s do that quickly.

Chapter 14: RMAN Advanced Recovery Topics
361

Configure the Auxiliary Parameter File
The parameter file of the auxiliary database is a separate parameter file from the one used on the target database. You configure the parameter file for the auxiliary instance in much the same way you configure a normal database parameter file. You need to include a few additional parameters in this parameter file, which are listed in Table 14-1.

Parameter Name

Optional/Required

Comment

db_name

Optional

The same name as the target database.

lock_name_space

Required

Should be a name that is unique among all

other databases on the system that you are

creating your auxiliary database on. In our

examples, we will use aux1.

db_file_name_convert

Optional

Can be used to define a set of file-naming

conversion patterns for datafiles in the

auxiliary database as they are restored by

RMAN. This is an alternative to using the

configure auxname
RMAN command.

log_file_name_convert

Optional

Can be used to define a set of file-naming

conversion patterns for redo log files in the

auxiliary database as they are restored by

RMAN. This is an alternative to using the

set newname
RMAN command.

control_files

Required

The control file parameter defines the

names and locations of the auxiliary

instance control files. The control files

should be unique in name from any existing

control file in the location(s) you intend on

creating the control files in.

remote_login_

Optional/

Used to allow RMAN to connect to the

passwordfile

Required

auxiliary database via Oracle Networking

services. Requires the presence of a current

password file. If you will be connecting

to the auxiliary database locally, then this

doesn’t need to be set.

Compatible

Required

Must be the same as the target database’s

setting.

db_block_size

Optional/

If set on the target database, must be set in

Required

the auxiliary database to the same value.

TABLE 14-1
Auxiliary Instance Parameter File Parameters of Interest