Blog Archives

How to Disable Automatic SGA Management on 11g Release 2

Login database using

sqlplus / as sysdba

Dump the pfile using existing spfile

SQL> create pfile='/home/oracle/Desktop/init.ora' from spfile;

File created.

Oracle will generate implicit parameters defining current sizing of the memory regions in SGA and write them at the top of generated pfile

orcl.__db_cache_size=79691776
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=142606336
orcl.__sga_target=272629760
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=167772160
orcl.__streams_pool_size=4194304
.
.
.
*.memory_target=414187520
.
.
.

Edit the file to disable AMM and enable static SGA configuration by simply removing memory_target, oracle_base and shared_io_pool_size parameters and doing some make up

*.db_cache_size=79691776
*.java_pool_size=4194304
*.large_pool_size=4194304
*.pga_aggregate_target=142606336
*.sga_target=272629760
*.shared_pool_size=167772160
*.streams_pool_size=4194304
.
.
.

Rebounce database instance by using modified pfile.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile='/home/oracle/Desktop/init.ora';
ORACLE instance started.

Total System Global Area  271400960 bytes
Fixed Size                  2227544 bytes
Variable Size             180355752 bytes
Database Buffers           83886080 bytes
Redo Buffers                4931584 bytes
Database mounted.
Database opened.

Once you ensure that new pfile works, recreate new spfile using this pfile (refer to How to move ASM spfile to a different disk group [ID 1082943.1] if your target is ASM diskgroup)

SQL> create spfile using pfile='/home/oracle/Desktop/init.ora';

File created.

Finally rebounce the database by using new spfile

SQL> startup force;
ORACLE instance started.

Total System Global Area  271400960 bytes
Fixed Size                  2227544 bytes
Variable Size             180355752 bytes
Database Buffers           83886080 bytes
Redo Buffers                4931584 bytes
Database mounted.
Database opened.
Advertisements

Oracle R Enterprise Configuration on Oracle Linux

Before starting to deal with large volumes of data problems on Oracle R Enterprise (ORE) you need to perform a couple of configurations over your Oracle Linux and Oracle Database systems. Here is the recipe:

  • Ensure that you have the following lines in your oracle users .bash_profile file
    export R_HOME=/usr/lib64/R
    export PATH=/usr/bin:$PATH
    
  • Ensure that you have already installed libpng.x86_64 and libpng-devel.x86_64 packages on your Oracle Linux otherwise issue to install them.
    yum install libpng.x86_64 libpng-devel.x86_64
    
  • Switch to root and issue R. Once you are in R session, install two prerequisites of ORE:
    install.packages("DBI")
    install.packages("png")
    
  • Ensure that your database is 11.2.0.3 otherwise refer you need to apply several database patches:
  • Go to Oracle R Enterprise Download Page and download Oracle R Enterprise Server Install for Oracle Database on Linux 64-bit (91M) and Oracle R Enterprise Client Supporting Packages for Linux 64-bit Platform (1M)  (ore-server-linux-x86-64-1.1.zip and ore-supporting-linux-x86-64-1.1.zip) under Oracle R Enterprise Downloads (v1.1) section
  • Unzip the file by issuing
    unzip ore-server-linux-x86-64-1.1.zip ore-supporting-linux-x86-64-1.1.zip
    
  • At this point ensure that your database to support Oracle R Enterprise is up and running
  • Execute install.sh in order to create ORE libraries and database objects into SYS and RQSYS schemas.
    cd server
    ./install.sh
    


    Oracle R Enterprise 1.1 Server Installation.

    Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.

    Do you wish to proceed? [yes]

    Checking R ................... Pass
    Checking R libraries ......... Pass
    Checking ORACLE_HOME ......... Pass
    Checking ORACLE_SID .......... Pass
    Checking sqlplus ............. Pass
    Checking ORE ................. Pass

    Choosing RQSYS tablespaces
    PERMANENT tablespace to use for RQSYS [SYSAUX]:
    TEMPORARY tablespace to use for RQSYS [TEMP]:

    Current configuration
    R_HOME = /usr/lib64/R
    R_LIBS_USER = /u01/app/oracle/product/11.2.0/dbhome_1/R/library
    ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
    ORACLE_SID = orcl
    PERMANENT tablespace = SYSAUX
    TEMPORARY tablespace = TEMP

    Installing libraries ......... Pass
    Installing RQSYS ............. Pass
    Installing ORE packages ...... Pass
    Creating ORE script .......... Pass

    NOTE: To use ORE functionality, a database user with RQROLE role,
    a few more grants and synonyms is required. A complete list of
    requirements is available in rquser.sql. There is also a demo
    script demo_user.sh creating a new user RQUSER.

    To use embedded R functionality, an RQADMIN role is required.
    Please, consult the documentation for more information on various
    roles.

    Done

  • Finally install some required R libraries/packages by using install.packages command in R. Ensure that user (root will do that) you will start R has a write permission on /usr/lib64/R/library
    install.packages("/home/oracle/Desktop/server/ORE_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL)
    install.packages("/home/oracle/Desktop/server/OREbase_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL)
    install.packages("/home/oracle/Desktop/server/OREeda_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL)
    install.packages("/home/oracle/Desktop/server/OREgraphics_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL)
    install.packages("/home/oracle/Desktop/server/OREstats_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL)
    install.packages("/home/oracle/Desktop/server/ORExml_1.1_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL)
    install.packages("/home/oracle/Desktop/supporting/ROracle_1.1-2_R_x86_64-unknown-linux-gnu.tar.gz", repos = NULL)
    
  • Finally start a R session (ensure that $ORACLE_HOME/lib is in your LD_LIBRARY_PATH before starting R session) and load ORE library
    library(ORE)
    Loading required package: OREbase
    Loading required package: ROracle
    Loading required package: DBI
    
    Attaching package: 'OREbase'
    
    The following object(s) are masked from 'package:base':
    
        cbind, data.frame, eval, interaction, order, paste, pmax, pmin,
        rbind, table
    
    Loading required package: OREstats
    Loading required package: MASS
    Loading required package: OREgraphics
    Loading required package: OREeda
    Loading required package: ORExml
    

Book Review: Oracle Warehouse Builder 11gR2: Getting Started 2011

As being the embedded option of Oracle relational database engine, OWB is still my favourite ETL (or to correct ELT) tool. In addition, although Oracle positions ODI as their strategic tool for this purpose, they still keep investing on OWB  at least in 11g Release 2.

I have recently had the chance to review a new book by Packt Publishing, namely Oracle Warehouse Builder 11gR2: Getting Started 2011. Book is  a good introductory book for newbies willingness to learn more on OWB by playing with it in a playground.

Book is organised to let you build your own OWB environment and work on a toy problem namely ACME Toy and Gizmos. You design and create your source and target structures and implement you ELT.

The book will help those who wish to learn more on OWB and look for a practical guide.

Oracle Sweden User Group (ORCAN) Event

I have been in Sweden (in a japanese spa hotel near Stockholm City) between Monday and Wednesday to join ORCAN event. Thanks to Patrik Norlander and his friends, the event was really perfect. I had two presentations and joined presentations of other ACEs and experts.

I spent my time in talking with Dan Morgan on a possible Turkey Oracle User Group Event, with Jose Senegacnik on Oracle and planes, with Dimitri Gielis whether APEX 4.0 is sufficiently mature to grow large scale applicaitons, and finally with Luca Canali about recent Oracle Streams projects in CERN.

Thanks guys,

it was a perfect time for me

My Presentations

How to Backup & Recover Enormous Databases ?

Hi there,

I am finally back from UKOUG and I am sharing my presentation on backup & recovery of enourmous databases.

How to Install Oracle 11g Release 2 on OEL 5.4 on VirtualBox: Managing ASM with ASMCA

In the previous post, I have tried to explain how to install Grid Infrastructure on your VirtualBox instance. In this post, before moving to the next post in which I will be explaining RDBMS 11g Release 2 configuration, I wish to look at a new tool shipped with Grid Infrastructure, namely ASMCA(ASM Configuration Assistant).

We will not be going deep with ASMCA in this post but rather it will be just like a first contact with aliens. ASMCA is a very capable and accompanying tool with the new capabilities of ASM like ADVM or ACFS.

Starting with ASMCA

init Starting ASMCA is simple. Go to your GRID_HOME set ORACLE_SID to +ASM and run asmca binary.

[oracle@localhost ~]$ export ORACLE_HOME =/u01/app/oracle/product/11.2.0/grid
[oracle@localhost ~]$ export ORACLE_SID = +ASM
[oracle@localhost ~]$ $ORACLE_HOME/bin/asmca
 

As you will notice there is already one disk group namely DATA listed under Disk Gruops pane. This is the one created during Grid Infrastructure installation.
Now click on Create to create our FRA diskgroup.

creatediskgroup As you click on Create button a new dialog to create your new disk group will appear. 
Enter FRA for Disk Group Name textbox.
Choose External(None) radio button to choose external ASM redundancy.
Notice that not all disks (asm disks) are available for your use (this controlled by two radio buttons in Select Member Disks pane) Select all three disks for FRA.
Now click on Show Advanced Options button to see RDBMS and ASM compatibilities.
advancedoptions Notice that ASM and Database Compatibility fields are both set to 11.2.0.0.0
This means ASM instance will allow you to use all new features introduced in 11.2 for this diskgroup but the minimum RDMS instance can use this diskgroup should be in 11.2 release.
Click on Hide Advance Options button and then OK to complete diskgroup creation.
creatediskgroup2 ASM will do the necessary initializations to create FRA diskgroup and switch back to initial screen after it is done.
rightclickondiskgruop As you see FRA is created and mounted by your ASM instance.
rightclickonDATA Now right click on your DATA diskgroup. In the list ASMCA allows you to

  • Add Disks into the diskgroup
  • Edit Attributes of the diskgroup
  • Manage Templates for the diskgroup
  • Mount/Dismount the diskgroup
  • Drop the diskgroup

Choose Edit Attributes from the list to see attributes of DATA diskgroup.

diskgroupProperties As you see ASMCA enables the attribute fields those are possible to modify and disables the static ones (the ones fixed in creation of diskgroup).
Click X to close this window.
exit Click on Exit button to leave ASMCA.

ASMCA is the youngest member of Oracle *CA family. It seems that ASM users will be using it actively for management by the new features in 11g Release 2. Such as,

  • ASM Volume (ADVM) Management
  • ASM File System (ACFS) Management
  • Read-only snapshot management

How to Install Oracle 11g Release 2 on OEL 5.4 on VirtualBox: Installing Grid Infrastructure

In Oracle 11g Release 2 you will find that things have changed even for single instance database installation. I will try to illustrate in this series of posts how to install a single instance Oracle 11g Release 2 database to your Linux machines.

As the first part of our installation series, we will start by installing brand new Grid Infrastructure which you might think to be a fancy name for CRS+ASM but you will find out later that it is a bit more.

VirtualBox Configuration

Here is the sufficient VirtualBox virtual hardware configuration for your 11g Release 2 playground (Keep in mind that this is the bare minimum configuration to have a painless installation. More resource is obviously better):

Hardware Amount Description
Memory 512MB Although minimum memory requirement for Oracle 11g Release 2 in a real production environment is documented to be 1024M, for all practical requirements of your playground 512MB will be sufficient.
Root Disk 16GB 16 GB root disk for OS+SWAP+Oracle Binary space will be sufficient
ASM Disks 6x2GB SCSI Disks Oracle 11g Release 2 Beta 2 AyarlarWe will be doing an ASM based installation so 6 disks over SCSI interface will be enough to simulate a real life experience with ASM.

You are now ready to start your VirtualBox for Oracle 11g Release 2 installation.

Install Grid Infrastructure

Pre-work

There are a few important tasks in OS level we should complete before starting grid infrastructure installation.

Physical Partition Creation

The first thing is to create physical partitions over your virtual SCSI devices. Actually this is not crucial for ASM installation because ASM can use physical disk as a whole without any partition. However if you wish to create ASMLIB you will need those partitions.

[root@localhost ~]# fdisk /dev/sda
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.Warning: invalid flag 0x0000 of partition table will be corrected by w(rite)Command (m for help): n
Command action
e     extended
p     primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-261, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-261, default 261):
Using default value 261Command (m for help): w
The partition table has been altered!

 

Calling ioctl() to re-read partition table.
Synching disks.

Changing ASM devices ownership to oracle

Physical partitions we have created are owned by root. They should be changed to be owned by ASM user (oracle in our case) in order to make them visible for ASM discovery.

[root@localhost ~]# lsla /dev/sd?1
brw-r—– 1 root disk 8,  1 Sep 21 05:03 /dev/sda1
brw-r—– 1 root disk 8, 17 Sep 21 05:03 /dev/sdb1
brw-r—– 1 root disk 8, 33 Sep 21 05:03 /dev/sdc1
brw-r—– 1 root disk 8, 49 Sep 21 05:03 /dev/sdd1
brw-r—– 1 root disk 8, 65 Sep 21 05:03 /dev/sde1
brw-r—– 1 root disk 8, 81 Sep 21 05:03 /dev/sdf1[root@localhost ~]# chown oracle:dba /dev/sd?1[root@localhost ~]# ls –la /dev/sd?1
brw-r—– 1 oracle dba 8,  1 Sep 21 05:03 /dev/sda1
brw-r—– 1 oracle dba 8, 17 Sep 21 05:03 /dev/sdb1
brw-r—– 1 oracle dba 8, 33 Sep 21 05:03 /dev/sdc1
brw-r—– 1 oracle dba 8, 49 Sep 21 05:03 /dev/sdd1
brw-r—– 1 oracle dba 8, 65 Sep 21 05:03 /dev/sde1
brw-r—– 1 oracle dba 8, 81 Sep 21 05:03 /dev/sdf1

In order to make those changes permanent (if you reboot the system Linux will set all device owners back to root otherwise) you should create a udev permission file

[root@localhost ~]# more /etc/udev/rules.d/99-oracle.rules
#ASM disks
KERNEL==”sda”, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″
KERNEL==”sdb”, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″
KERNEL==”sdc”, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″
KERNEL==”sdd”, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″
KERNEL==”sde”, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″
KERNEL==”sdf”, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

Ensure /dev/shm is sufficiently sized

You should ensure that /dev/shm is minimum 256MB for a successful ASM installation (and 512-750M for RDBMS installation) with MEMORY_TARGET parameter.

[root@localhost ~]# dfha
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00 15G 9.9G 3.9G 72% /
proc 0 0 0 /proc
sysfs 0 0 0 /sys
devpts 0 0 0 /dev/pts
/dev/hda1 99M 32M 63M 34% /boot
tempfs 125M 0 125M 0% /dev/shm
none 0 0 0 /proc/sys/fs/binfmt_misc
sunrpc 0 0 0 /var/lib/nfs/rpc_pipefs
oracleasmfs 0 0 0 /dev/oracleasm

You can resize tempfs online by using

[root@localhost ~]# mount /dev/shm –o size=750M,remount

In order to make this mount operation persistent you should modify the tempfs line in /etc/fstab file as follows:

[root@localhost ~]# cat /etc/fstab | grep tempfs
tmpfs                   /dev/shm                tmpfs   size=750m        0 0

Notice that although we have VirtualBox instance with 512M memory, Linux allows us to mount tempfs with a size of 750M. This most probably due to lazy allocation of memory over tempfs.

Create installation directory and set its ownership

Final step is to create our software directory and set the required ownership to it.

[root@localhost ~]# mkdir -p /u01/app
[root@localhost ~]# chown -R oracle:oinstall /u01/app/oracle

Installation

Camtasia Studio (5)
[oracle@localhost ~]$ unzip linux_11gR2_grid.zip
[oracle@localhost ~]$ cd grid
[oracle@localhost grid]$ ./runInstaller
Camtasia Studio (7) Choose Install and Configure Grid Infrastructure for a Standalone Server option and click on Next > button.
Camtasia Studio (14) Set Selected Language language to English and click on Next > button.
Camtasia Studio (9) Next step is to perform ASM configuration for your grid. Choose External as the redundancy of DATA diskgroup. Now click on Change Discovery Path… button to define asm_diskstring parameter.
Camtasia Studio (15) Set Disk Discovery Path to /dev/sd?1 and click on OK
Camtasia Studio (16) Since /dev/sd?1 matches all six SCSI partitions and they are not members of any other diskgroup, installer will list all of them as Candidate disks.Check-out /dev/sda1,/dev/sdb1,/dev/sdc1 devices as members disks of DATA diskgroup then click on Next >Other disks will be used for Flash Recovery Area later on.
Camtasia Studio (17) Choose Use same passwords for these account for a simple configuration then set Specify Password and Confirm Password fields to same password strings and click on Next >.I will be using sysadm throughout the post for any Oracle password required.
Camtasia Studio (18) One of the security enhancements introduced in Release 2 is the separation of different levels of ASM access. This defines different roles for “Who can start/stop ASM instance ?”, “Who can add/drop disks to/from diskgroups?” or “Who can use those diskgroups at RDBMS level ?”For the simplicity of installation we will be setting all roles to dba group.Now set all three select lists to dba and click on Next >.
Camtasia Studio (52) Set Oracle Base to /u01/app/oracle and Software Location to /u01/app/oracle/product/11.2.0/grid. Then click on Next >.
Camtasia Studio (22) Next step is unique to first Oracle software installation as you all may know. You should set Inventory Directory (if it is not already set by installer) to /u01/app/oraInventory. Keep oraInventory Group Name as oinstall and click on Next >
Camtasia Studio (23) In this step installer will check the installation prerequisites as it does in previous releases.
Camtasia Studio (26) By 11g Release 2, if any of the prerequisites fail it will be reported in a tree structure with different categories. In my case majority of the kernel settings are automatically managed since I have installed oracle-validated-configuration rpm during OEL installation.Only problem seems to be insufficiently sized core.net.wmem_max which is defined to be the maximum socket send buffer size.When you click on Fix & Check Again button, installer will generate a single shell script for you to correct all fixable errors and after its execution it will recheck for any possible problems left.
Camtasia Studio (27) Run the generated script as root user

[root@localhost ~]# /tmp/CVU_11.2.0.0.2_oracle/runfixup.sh
Response file being used is :/tmp/CVU_11.2.0.0.2_oracle/fixup.response
Enable file being used is :/tmp/CVU_11.2.0.0.2_oracle/fixup.enable
Log file location: /tmp/CVU_11.2.0.0.2_oracle/orarun.log
Setting Kernel Parameters…
net.core.wmem_max = 262144
net.core.wmem_max = 1048576

When the script is executed click OK to restart the prerequisite check process.

Camtasia Studio (31) As you see kernel parameter problem has gone. Other three errors can not be corrected by installer automatically, but we know that those are not critical ones. First one is PhysicalMemory error due to our VirtualBox 512 MB memory size. The second one is insufficient SwapSize that can be by-passed also for a play ground. And the final problem is RunLevel of Linux which is also not a great deal for us.Now check Ignore All and click on Next > (button will be enabled after checking out Ignore All) to continue.
Camtasia Studio (32) On the summary screen confirm that everything is ok and click on Finish to start installation.
Camtasia Studio (35) After installer successfully completes copy,install,link,etc steps it will pop-up a root.sh execution dialog. Run the required scripts as root.

[root@localhost ~]# /u01/app/oraInventory/orainstRoot.sh
[root@localhost ~]# /u01/app/oracle/product/11.2.0/grid/root.sh

Then switch back to installation dialog and click OK in Execute Configuration scripts dialog to proceed.

Camtasia Studio (45) Final tasks for installer will be to configure new HA service for ASM, diskgroup (DATA) and the default listener (on port 1521) which will be automatically configured also .
Camtasia Studio (48) Finally we are done 🙂 Your ASM, default listener, and HA service is ready to be used.

Post Installation Checks

Login ASM

[oracle@localhost ~]$ export ORACLE_HOME = /u01/app/oracle/product/11.2.0/grid [oracle@localhost ~]$ export ORACLE_SID = +ASM
[oracle@localhost ~]$ export PATH = $ORACLE_HOME/bin:$PATH
[oracle@localhost ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.0.2 Beta on Mon Sep 21 05:45:58 2009 Copyright © 1982, 2009, Oracle. All right reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.0.2 – Beta
With the Automatic Storage Management option

 

SQL> select name from v$asm_diskgroup;

NAME
——————————
DATA

Check Default Listener Status

[oracle@localhost ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 31-OCT-2009 12:05:36 Copyright (c) 1991, 2009, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 – Production
Start Date                31-OCT-2009 11:51:00
Uptime                    0 days 0 hr. 14 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary…
Service “+ASM” has 1 instance(s).
Instance “+ASM”, status READY, has 1 handler(s) for this service…
The command completed successfully

Check HA Targets

[oracle@localhost ~]$ crs_stat -t
Name           Type           Target    State     Host
————————————————————
ora.DATA.dg    ora….up.type ONLINE    ONLINE    localhost
ora….ER.lsnr ora….er.type ONLINE    ONLINE    localhost
ora.asm        ora.asm.type   ONLINE    ONLINE    localhost
ora.cssd       ora.cssd.type  ONLINE    ONLINE    localhost
ora.diskmon    ora….on.type ONLINE    ONLINE    localhost

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

$ export PATH = $ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

$ 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

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

“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)