Oracle RMAN 11g Backup and Recovery (125 page)

362
Part III: Using RMAN Effectively

Each of these parameters is further defined in the Oracle reference manual, if you need further information on how to configure them. For our example, we simply copied the target instance parameter file and made a few changes to it. Here is a copy of the resulting auxiliary database instance parameter file:

# These parameters already exist in the target database parameter file and

# require no changes.

db name rob10r2

db block size 8192

db cache size 8388608

timed statistics TRUE

shared pool size 110M

large pool size 1M

# Note that remote login passwordfile must be set to exclusive

# if we want to connect to the aux database through RMAN via Oracle Net.

# remote login passwordfile EXCLUSIVE

compatible 10.2.0.1

# These parameters required changes to directory locations

# to reflect the new database instance.

background dump dest c:\oracle\auxdest

core dump dest c:\oracle\auxdest

user dump dest c:\oracle\auxdest

control files c:\oracle\auxdest\control01.ctl

# These are new parameters that are exclusive to the aux database

db unique name recover

db create file dest c:\oracle\auxdest

log file name convert

('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2','d:\oracle\auxdest')

NOTE

We dump everything into c:\oracle\auxdest in the parameter file

here. This kind of goes with the very temporary nature of the auxiliary
database. You can, of course, create more sophisticated directory

structures if you like.

Start the Auxiliary Instance and Check Network Connectivity
Now that we have everything in place, we can start our auxiliary instance. To do so, we simply use the
startup nomount
command, as shown in this example:

sqlplus "sys as sysdba"

startup nomount pfile c:\oracle\auxdest\initrecover.ora

Once you have started the instance, connect to it using Oracle Net to ensure your network connectivity is correct.

Perform TSPITR with a Manual Auxiliary Instance
To perform the actual recovery, we need to connect to RMAN. The connection string we are going to use is a bit different because we need to connect to the target database, the auxiliary database, and the recovery catalog (if we are using

Chapter 14: RMAN Advanced Recovery Topics
363

one) all at the same time. To connect to the auxiliary database, we use the
auxiliary
command-line parameter, along with the
target
and
catalog
parameters, as shown in these examples: C:\Oracle\auxdest>rman target sys/robert@rob10r2 auxiliary /

Recovery Manager:Release 10.2.0.1.0 Production on Thu Feb 23 10:25:49 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ROB10R2 (DBID 3753137102)

connected to auxiliary database: RECOVER (not mounted)

Now that we have connected to RMAN, we need to be able to mount the auxiliary (clone) database. To do this, we restore a clone control file and then open the auxiliary database. Note that the
alter system archive log current
command is important here to make sure that we have all the redo we need available for recovery of the clone database. The following is an example of using the
restore clone controlfile
command to restore the control file, and then an example of mounting the clone database. Note that these commands are contained inside of a
run
block.

Also note the use of the
sql clone
command to indicate that the SQL commands executed should be on the clone database, as opposed to using the
sql
command without the
clone
parameter, which implies that the command is executed on the target database.

Run {

Set until time "to date(' 02/23/2006 11:42:55','mm/dd/yyyy hh24:mi:ss')"; Restore clone controlfile;

sql clone 'alter database mount clone database';

sql 'alter system archive log current';

}

Now, we need to prepare for our TSPITR. First, we need to know which datafiles we need to restore. We need the following tablespace-related datafiles:

■ The SYSTEM tablespace datafiles. The SYSTEM tablespace datafile is always at least datafile number 1. (There may be others if you added datafiles to SYSTEM, of course.)

■ The UNDO tablespace datafiles.

■ The temporary tablespace (TEMP) tempfiles. These can also be manually created if they are not available.

■ The datafiles of the tablespace to be restored (in our case, TEST_RESTORE).

NOTE

We don’t have to restore the SYSAUX tablespace in this case.

So, let’s look up these locations in our database using SQL*Plus. We use the DBA_DATA_FILE

view to find the FILE_IDs associated with these tablespace datafiles. We also need the filename for the tablespace (or tablespaces) that we will be actually performing the TSPITR on. In our case, here is what the output looks like (yours might be a bit different, of course): SQL> select tablespace name, file id

2> from dba data files

3> where tablespace name in

4> ('SYSTEM','UNDOTBS1','TEST RECOVER');

364
Part III: Using RMAN Effectively

TABLESPACE NAME FILE ID

------------------------------ ----------

UNDOTBS1 2

SYSTEM 1

TEST RECOVER 7

SQL> select file name from dba data files where file id 7;

FILE NAME

------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\TEST RECOVER 01.DBF

SQL> select tablespace name, file id

2> from dba data files

TABLESPACE NAME FILE ID

------------------------------ ----------

TEMP 1

Before we start creating the auxiliary database, we need to offline the tablespaces we are going to perform recovery on. For each tablespace, issue the SQL command
alter tablespace
tablespace_name offline for recover;
as shown in this example:

Sql 'Alter tablespace test recover offline for recover';

We are ready to actually create the auxiliary (clone) database that will be used for the recovery.

First, we use the
until time
parameter again, since this is a new
run
block. This way, we are sure to get the correct datafiles. Next, we use the
set newname for clone
command to make sure these files are correctly named during the restore. In this example, we are using OMF, so it’s easy to do using the
new
parameter. We also issue a
set newname
command for datafile 7, in preparation for its restore. We then issue the
switch clone tempfile all
command to rename the temp files. Finally, we restore the datafiles and get the clone database running.

Run {

Set until time "to date('02/23/2006 11:42:55','mm/dd/yyyy hh24:mi:ss')"; Set newname for clone datafile 1 to new;

Set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

Set newname for datafile 7 to

"C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\TEST RECOVER 01.DBF"; Switch clone tempfile all;

restore clone datafile 1, 2, 7;

switch clone datafile all;

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 2 online";

sql clone "alter database datafile 7 online";

sql clone "alter database mount clone database";

}

Now, open the clone database in preparation to do the TSPITR: