Thursday, September 27, 2007

RMAN Duplication to a new host using Veritas Netbackup in Oracle 9i

We use Veritas Netbackup 5.1 as the Media Management Software for creating our production tape backups. Yesterday, I successfully created a RMAN DUPLICATE database on a different host using these tape backups .I am documenting the procedure below


1)RMAN target = source database of the RMAN backups
2)duplicate database = destination database that needs to be created from the source database backup
3)auxiliary database = duplicate database
6)RMAN repository = separate schema (RMAN catalog) in a separate database that contains source database’s backup details
7)dev7 is the name of the duplicate database to be created
8)Media Manager Server = Netbackup Media Manager Server.
9)Media Manager Client = source database host
10)PROD = source database name
11)DEV = duplicate database name
12)RMANCT = RMAN catalog database name
13)RMANPROD = RMAN catalog repository schema name


One of the requirements of RMAN duplication using VERITAS NETBACKUP is that the destination database's osuser and group should be similar to what it is in the source database node

e.g If source database ORACLE_HOME owner is oracle:dba, the destination database ORACLE_HOME owner should also be oracle:dba


1)Contact your NETBACKUP system administrators to the source database BACKUP TAPES accessible to DESTINATION HOST

2)Verify by the following command

a) Login oracle@destination_db_host
b) /usr/openv/netbackup/bin/bplist -C source_db_host -t 4 -l -R /

The above bplist command should list all the backup sets that are in the RMAN catalog

Prepare Duplicate database

1)Login oracle@destination_db_host
2)Copy the source database's initSID.ora to the destination host and add a new parameter DB_FILE_NAME_CONVERT. For e.g if the source database files are in /oracle/prod/d1 and /oracle/prod/d2 directories and if the corresponding restoration paths in the destination host are to be /oracle/dev/d1 and /oracle/dev/d2 respectively,


3)sqlplus '/ as sysdba'

4)startup nomount

5)Edit the $TNS_ADMIN/tnsnames.ora and add connection descriptors for the source database PROD and its catalog database RMANCT

Run the DUPLICATE command

RMAN always restores from the latest tape backups by default to create new database with the DUPLICATE command.

1)Login to oracle@destination_host

export NB_ORA_CLIENT=source_host

2)Connect to RMAN

rman LOG=/tmp/client/dev.log
connect target sys/***@prod
connect catalog rmanprod/****@rmanct
connect auxiliary /

3)Issue the Duplicate commands

allocate auxiliary channel t1 type 'SBT_TAPE';
allocate auxiliary channel t2 type 'SBT_TAPE';
SEND 'NB_ORA_SERV=media manager server, NB_ORA_CLIENT=source_host';
target database to dev
'/oracle/dev/logs/dev_log1.dbf' size 256M,
'/oracle/dev/logs/dev_log2.dbf' size 256M,
'/oracle/dev/logs/dev_log3.dbf' size 256M

4)Verify the dev alert log and the RMAN log /tmp/client/dev.log
5) The whole restore process will happen over the network depending on the network speed and the source database size

Restoration of only archive logs from RMAN

1) Login oracle@destination_host
2) select * from v$instance ( it should show ‘MOUNTED’ status)
3) shutdown immediate
4) Login oracle@source_host
5) Restore the archivelog files with a separate RMAN command.

connect target /
connect catalog rmanprod/****@rmanct
allocate channel t1 type 'SBT_TAPE';
set archivelog destination to '/home/oracle/';
restore archivelog
from time "to_date('09/24/07 8:00:00', 'mm/dd/yy hh24:mi:ss')" until time "to_date('09/24/07 13:00:00', 'mm/dd/yy hh24:mi:ss')";

7)Verify the restored archive logfiles in /home/oracle/
8)Copy them to destination node using rcp or scp command
9)Login to destination node oracle@destination_host
10)Mount the destination database dev and recover manually using the 'recover database until cancel using backup controlfile' command
11)Type ‘cancel’ after applying the necessary archive logfiles to make the destination database consistent
12) alter database open resetlogs
13) Create the temporary tablespace

How to create a duplicate database until a specific time in the RMAN repository

The procedure remains the same except one more ‘set until’ command. The set until time command has to be put in the run block just before the duplicate command to create the duplicate database upto a specific point in time.

set until time= "to_date('mm/dd/yyyy hh24:mi:ss', 'MM/DD/YYYY HH24:MI:SS')";

Affect of Duplicate command on Recovery Catalog

There is no affect of duplication on the recovery catalog. RMAN generates a new DBID for the duplicate database. Unless this DBID is registered in the catalog, RMAN does not create or update any records for the newly created duplicate database.

It is not necessary to register the newly created duplicate database in the RMAN catalog unless the newly created duplicate database wants to use RMAN for its own backups

Friday, September 21, 2007

How to verify whether a patch has been applied in 11i

There are several known ways for checking whether a patch (simple patch, patchset, family pack or minipack or maintenance pack) has been applied in the Oracle 11i E-Business Suite.

However in these "several" ways, what is the best method which is also suppored by Oracle Support ?

Yesterday i was doing patch analysis for installing Oracle Support Diagnostics 2.5 product (IZU) in our production system. It listed 11i.ATG_PF.H.4 (Rollup 4) as the prequisite. So i downloaded (the Oracle Patch Comparision utility) and ran the script to find the current Running Versions in our system for patchsets and family packs.

It listed 11i.ATG_PF.H(3438354) as the Running Version and 11i.ATG_PF.H.5(5473858) (Rel-By_Metalink) as the 'Latest Available' one in the Report_11i.txt output file.
So as per the tool, there is no 11i.ATG_PF.H.4 (4676589) in our system. I went ahead with my patch analysis only to know later that '11i.ATG_PF.H.4' has indeed been applied to our system.

AD_PATCH_DRIVERS shows 4676589, whereas does not show this is applied !! though Metalink Note 162524.1 shows either method is OK.

4 Different methods as per Metalink for finding patch information in 11i
1) (Patch Comparision Tool)
3) Two reports adphrept.sql (patch history) and adfhrept.sql(file
history) in $AD_TOP directory
4) Login to Oracle Applications Manager (OAM) => Applied Patches => Simple Search by 'Patch ID)

What is the best method out of the 4 above ?
Finding patch history through OAM is the best method. It is easy to use and it has a web-based interface. AD_PATCH_DRIVERS too is good if you want to write a SQL statement.

What does the Documentation say ?
According to the 'AD Maintenance Utilities' PDF for , OAM is the best method for finding patch history.

Wednesday, September 19, 2007

Adding Datafile with same name but different directory

Yesterday, one of my colleagues added a datafile with the same filename but different directory by mistake through OEM. He quickly realized it but suprised to find that OEM did not complain and created the datafile successfully.

We checked the alertlog and there were no errors either. We tried the same thing in a different test database and no issues there too. So this proves that Oracle treats the filename in its entirety (including the directory path) and hence the new datafile is still unique.

However this is not a great practice and hence we are going to use our next scheduled downtime to rename this newly added datafile.

Renaming a Datafile when the database is shutdown

1) Login to all of the RAC database nodes and issue 'shutdown immediate'
2) cp existing_datafile_name new_datafile_name
3) Login to the master RAC node and issue 'startup mount'
4) alter database rename file 'old_directory/old_datafilename'
5) shutdown immediate
6) Login to all the of the RAC database nodes and issue 'startup'
7) Verify the new name in v$datafile and dba_data_files.
8) Remove the datafile with the wrong name from the operating system using the OS command.

Tuesday, September 18, 2007

Working on RMAN Duplication to a different host

Since a week i am working on changing our Oracle 11i E-Business Suite Database cloning procedure. In the current method the source database is put in hotbackup mode, data files and the needed archive logfiles are copied to the destination host and the clone/test database is created.

But the problem with this current method is we are not really testing the source database backups. A good DBA should always create test/clone databases from the source database backups instead of copying datafiles in backup mode. Our production database has a full biweekly RMAN tape backup. Though these backups are successful as per their "log files", they have never been used as a source to create for cloning purposes.

The right procedure now is to use these RMAN tape backups and then perform database cloning using the RMAN DUPLICATION feature.

We use VERITAS NETBACKUP as the Media Management Software for the RMAN tape backups. I am working with the Unix system administrator, NETBACKUP administrator but encountering some NETBACKUP errors. We opened a ticket with Veritas and i will blog again once the issue is resolved. I will also detail the issue rootcause and the solution for it.
Related Posts Plugin for WordPress, Blogger...