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.
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.
I 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 $ export PATH = $ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH $ netca Oracle Net Services Configuration: |
 |
Choose “Listener configuration” option and click on “Next” |
 |
Choose “Add” option and click on “Next” |
 |
Choose LISTENERT as the “Listener name” and click on “Next” |
 |
Choose TCP as the “Selected Protocols” and click on “Next” |
 |
Set “Use another port number” field to “1521 and click on “Next” |
 |
Choose “No” and click on “Next” |
 |
Click on “Next” |
 |
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
LISTENERT = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomain) (PORT=1525)) ) )
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENERT = ON |
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 SID_LIST_LISTENERT = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = pgroundt) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/rdbms01) (SID_NAME= pgroundt) ) )
LISTENERT = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.2.0.6) (PORT=1525)) ) )
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENERT = ON |
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’ CONTROL_FILES = ’+DATA’,’+FRA’ COMPATIBLE = 11.2.0.0.0 |
You can now start the instance with this seed parameter file
| $ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 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:
| PGROUNDT =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.2.0.6) (PORT=1525)) (CONNECT_DATA = (SERVER = DEDICATED) (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 11.2.0.1.0 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 11.2.0.1.0 – Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
SQL> |
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: RMAN> DUPLICATE TARGET DATABASE TO pgroundt FROM ACTIVE DATABASE SPFILE 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 11.2.0.1.0 Production on Thu Oct 22 14:04:08 2009 Copyright © 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from dba_tables; COUNT(*) ———- 2719 SQL> |
For More
- Refer to Oracle My Support Note 452868.1
- Duplicate Tahiti Reference
“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:
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.
The great grandson of Husnu Sensoy 