Blog Archives

Slides and Offline Recording of my NYOUG Webinar

I have announced my webinar on Oracle ZFS Storage Appliance for Exadata Backup & Recovery in NYOUG DBA webinar series. You can download webinar slides and voice recod from NYOUG web site

My Open World 2010 Session

Hi there,

You can find my joint Solidify RMAN Backup with Oracle Open Storage 7000 presentation with my esteemed friend Orhan Bıyıklıoglu held yesterday in Open World 2010.

RMAN Active Duplicate

All you may now that RMAN DUPLICATE command has two modes by 11g Release 1. The first mode is the good old Offline Duplicate which is based on making backups of the source system available to target system first.

offline Although it is a very useful feature, the basic problem with that  is to keep a staging are (either on tape or disk) to duplicate your database. When it comes to duplicate VLDBs it is always a problem.

The second mode is the so-called Active Duplicate which requires no staging area at all, but utilize the TCP/IP network (SQL*Net Layer) on the fly to ship what ever required to duplicate your database.

activeI think this feature is one of the best features introduced in 11g Release 1 for DBAs who are constantly duplicating their databases  (either full or partial) as test,poc, or/and development environments.

In this post you will find a playground example of this perfect tool to clone an Oracle 11g Release 2 database running on VirtualBox over another VirtualBox machine.

Create a Listener in RDBMS Home (Clone Site)

As you might notice by 11g Release 2, Oracle has standardized the default listener to be located in grid home. So we will not be using the default listener (on port 1521) for our clone database but create a new one on port 1525.

$ export ORACLE_HOME = /u01/app/oracle/product/11.1.0/rdbms

$ export ORACLE_SID = pgroundt


$ netca

Oracle Net Services Configuration:

image Choose “Listener configuration” option and click on “Next”
image Choose “Add” option and click on “Next”
image Choose LISTENERT as the “Listener name” and click on “Next”
image Choose TCP as the “Selected Protocols” and click on “Next”
image Set “Use another port number” field to “1521 and click on “Next”
image Choose “No”  and click on “Next”
image Click on “Next”
image Click on “Finish” to leave NETCA

Fine Tunes

There  are two  important fine tunes we should do. first one is to change listener HOST parameter to a valid IP address and then to modify the listener we have created in a way that listener can accept connections even when the clone instance is not up. So open listener.ora with your vi editor

$ cd $ORACLE/network/admin

$ vi listener.ora

# Generated by Oracle configuration tools.

ADR_BASE_LISTENERT = /u01/app/oracle




(ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomain) (PORT=1525))




First change the HOST parameter of the listener to an IP that can be accessible from the source machine (I have created a private network between to virtual machines, you can find how to do this on VirtualBox HOWTOs and tutorials page)

Then add SID_LIST for the LISTENERT:

# Generated by Oracle configuration tools.

ADR_BASE_LISTENERT = /u01/app/oracle




(GLOBAL_DBNAME = pgroundt)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/rdbms01)

(SID_NAME= pgroundt)










Now you are ready to restart your  listener LISTENERT.First stop the listener

$ lsnrctl stop LISTENERT

Then start it

$ lsnrctl start LISTENERT

At the bottom line of output if you see

Service “pgroundt” has 1 instance(s).

Instance “pgroundt”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

you are done with the listener configuration.

Create a Seed Parameter File and Start Your Clone Instance (Clone Site)

In the next step, we will be starting the clone instance with a very primitive server parameter file to let RMAN to connect it and restore SPFILE from source site.

$ cat pgroundt.ora

DB_NAME = pgroundt

diagnostic_dest = ’/u01/app/oracle’



You can now start the instance with this seed parameter file

$ sqlplus / as sysdba

SQL*Plus: Release Production on Thu Oct 22 13:58:18 2009

Copyright © 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile = pgroundt.ora

ORACLE instance started.

Create Password Files at Clone and Source Sites

Equality of passwords for SYS user is critical for DUPLICATE to work when clone and source instances are located on different hosts. I will be using SYSADM for SYS password.

Clone Site

$ cd $ORACLE_HOME/dbs

$ rm orapwpground

$ orapwd file = orapwpgroundt

Enter password for SYS:


Source Site

Switch to VirtualBox machine that contains the source database

$ cd $ORACLE_HOME/dbs

$ orapwd file = orapwpground force = y

Enter password for SYS:


Modify TNSNAMES.ORA at Source Site to Reach Clone Instance (Source Site)

Next step will be to add TNS entry for clone instance into the TNSNAMES.ora file of the source site:

$ cd $ORACLE_HOME/network/admin

$ vi tnsnames.ora

Add the following lines into file then save & exit:






(SERVICE_NAME = pgroundt)



Ensure that you can connect to the NOMOUNT state clone instance over TNS from source site.

$ sqlplus sys@pgroundt as sysdba

SQL*Plus: Release Production on Thu Oct 22 14:04:08 2009

Copyright © 1982, 2009, Oracle. All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release – Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options


Create ADUMP Destination at Clone (Clone Site)

Audit log directory will not be generated by Oracle automatically as opposed to diagnostic destination directory hierarchy. So you should manually create before starting DUPLICATE. Now switch back to clone instance and create adump directory

$ cd /u01/app/oracle

$ mkdir –p admin/pgroundt/adump

Start DUPLICATE (Source Site)

After all, you are ready to run DUPLICATE. Switch back to source site and start DUPLICATE

$ rman log = ’duplicate.log’

RMAN> connect target sys@pground

target database Password:

RMAN> connect auxiliary sys@pgroundt

auxiliary database Password:


TO pgroundt



PARAMETER_VALUE_CONVERT ‘/u01/app/oracle/admin/pground’,‘/u01/app/oracle/admin/pgroundt’

NOFILENAMECHECK;2> 3> 4> 5> 6>

You can find my duplicate.log file to see what RMAN is doing during DUPLICATE operation.

You are Done (Clone Site)

If duplication is completed without an error, your clone database will be up on the clone VirtualBox. Switch to clone VirtualBox instance and check it.

$ sqlplus / as sysdba

SQL*Plus: Release Production on Thu Oct 22 14:04:08 2009

Copyright © 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release – Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from dba_tables;





For More

“Oracle Database Backup-and-Recovery Best Practices and New Features” Material

In Open World 2009, my second presentation was a joint session with my friend Timothy Chien who works for Oracle as Principal Product Manager of Database HA products. The room was almost full (~900 people).Thanks to all attendees.  It seems this topic is still hot :). You can find Tim’s and my part below:

  • Part 1 (Timothy Chien’s Presentation)
  • Part 2 (Husnu Sensoy’s Presentation)

Oracle 11g RMAN: BZIP2 vs. ZLIB

Prior to 11g Oracle RMAN had a single compression algorithm, called BZIP2. The algorithm has a very satisfactory compression ratio in terms of decreasing the size of RMAN output. However, high CPU cost makes algorithm not suitable for many sites especially for sites having CPU bottleneck (Data warehouse DBAs?!?:)). As a result people still use hardware compression capabilities of tape drivers (ratios like 1:3) to decrease the backup time and increase the effective write speed of backup drivers. By 11g Oracle introduces a new compression algorithm that is announced to be less compressive but less aggressive in terms of CPU. In this paper you will find comparison of two algorithms with no compressed case.

Read the rest of paper…