Read Oracle RMAN 11g Backup and Recovery Online
Authors: Robert Freeman
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: