RMAN Tablespace Point in Time Recovery Example

The following is a working example of RMAN TSPITR and was performed using Oracle 8.1.7.0.0 on Microsoft Windows 2000.

SCOPE & APPLICATION
--------------------------
Intended audience is for DBA's using RMAN as a backup solution.

RMAN Tablespace Point in Time Recovery Example
-----------------------------------------------------
RMAN Tablespace Point in Time Recovery Example

The following is a working example of RMAN TSPITR and was performed using Oracle 8.1.7.0.0 on Microsoft Windows 2000.

This example assumes:
1. A tns alias is used to connect to the RMAN catalog and target databases only. We use a local connection for the auxiliary instance. You can use a tns alias to connect to the auxiliary database if you wish, but it’s not necessary.
2. The production instance database files are located on D:\ORACLE8I\ORA817\ORADATA\ORCL817.
3. The production instance log files are located on D:\ORACLE8I\ORA817\ORADATA\ORCL817.
4. The auxiliary instance files are located on D:\ORACLE8I\ORA817\ADMIN\AUX1. We have chosen to store all auxiliary files in one location to help simplify the process.
5. RMAN backups are on disk; so all channels are allocated to disk.

===========================
I. Create the auxiliary initSID.ora.
===========================
1. The auxiliary instance MUST reside on the same machine as the target database.
2. Precreate one directory for all of the auxiliary files to be stored in.
This example uses D:\ORACLE8I\ORA817\ADMIN\AUX1. 3. Copy the initSID.ora from the target database to D:\ORACLE8I\ORA817\ADMIN\AUX1\initAUX1.ora.
4. Modify the initAUX1.ora file to include the following:
LOCK_NAME_SPACE=AUX1
DB_FILE_NAME_CONVERT=("D:\ORACLE8I\ORA817\ORADATA\ORCL817", “D:\ORACLE8I\ORA817\ADMIN\AUX1")
LOG_FILE_NAME_CONVERT=("D:\ORACLE8I\ORA817\ORADATA\ORCL817","D:\ORACLE8I\ORA817\ADMIN\AUX1")

Note: Using LOCK_NAME_SPACE allows us to have two databases with the same name on the same NT machine.

Note: Using DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT converts one string to another. Database files in D:\ORACLE8I\ORA817\ORADATA\ORCL817 will retain their same name, but will be restored to D:\ORACLE8I\ORA817\ADMIN\AUX1. You could also use "configure auxname" in RMAN to perform the datafile conversions.
5. Modify the initAUX1.ora file to change the controlfile location:
control_files = ("d:\oracle8i\ora817\admin\AUX1\control01.ctl")
6. Modify the initAUX1.ora file to comment out the following:
#log_archive_start = true#log_archive_dest_1 = “location=…#log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

=================================
II. Create the auxiliary instance
=================================
1. From a command prompt type the following:
C:\> oradim -new -sid AUX1 -intpwd oracle -startmode manual -pfile D:\ORACLE8I\ORA817\ADMIN\AUX1\initAUX1.ora
2. Verify the instance has been created in “Services” and verify it is started.

=================================
III. Start the auxiliary instance
=================================
From a command prompt:
1. Set ORACLE_SID=AUX1
2. Start sqlplus and connect internal
3. Startup nomount pfile=$D:\ORACLE8I\ORA817\ADMIN\AUX1\initAUX1.ora
4. After the instance is successfully started, exit the session.

==================================================
IV. Connect to the catalog, target, and auxiliary databases using RMAN ==================================================
1. Make sure the target database is open.
2. Make sure the auxiliary instance is started and in no mount mode.

From a command prompt:

3. Set ORACLE_SID=AUX1
4. Set NLS_DATE_FORMAT=MON-DD-YYYY HH:MI:SS
5. Connect to RMAN:

rman target sys/password@target catalog rman/rman@rcat auxiliary / trace=d:\tspitr.log

(This will connect you to the target, catalog, and auxiliary database. It will also log the TSPITR session to "d:\tspitr.log")

===============
V. Start TSPITR
===============
run {
ALLOCATE AUXILIARY CHANNEL A1 TYPE DISK;
ALLOCATE CHANNEL c1 TYPE DISK;
recover tablespace users until time "TO_DATE('MAR-05-2002 11:27:00', 'MON-DD-YYYY HH:MI:SS')";
}

========================
VI. Testing the Recovery
========================
1. Connect as sys
2. Put the recovered tablespace online
3. Verify the object(s) exists.