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	6×2GB SCSI Disks	We 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 0×0000 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 ~]# ls –la /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 ~]# df –ha

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

	[oracle@localhost ~]$ unzip linux_11gR2_grid.zip [oracle@localhost ~]$ cd grid [oracle@localhost grid]$ ./runInstaller
	Choose Install and Configure Grid Infrastructure for a Standalone Server option and click on Next > button.
	Set Selected Language language to English and click on Next > button.
	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.
	Set Disk Discovery Path to /dev/sd?1 and click on OK
	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.
	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.
	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 >.
	Set Oracle Base to /u01/app/oracle and Software Location to /u01/app/oracle/product/11.2.0/grid. Then click on Next >.
	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 >
	In this step installer will check the installation prerequisites as it does in previous releases.
	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.
	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.
	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.
	On the summary screen confirm that everything is ok and click on Finish to start installation.
	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.
	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 .
	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.

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:

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

“How to Achieve All in One with Oracle 11g” Material

Here is the content of my first presentation in Open World 2009:

How to Achieve All in One with Oracle 11g

My Comments on Voltaire InfiniBand (IB)

As you all may know Voltaire is one of the most prominent IB technology vendors and investors had the partnership with Oracle in building Exadata Version 1. Yet another beautiful thing about Voltaire is that it is one of the companies invest on Open Source technologies and HPC (High Performance Computing) a lot, like OFED stack development on Linux.

You can find my comments about Voltaire 20Gbit IB solution which we have already used in our large (25TB) RAC consolidation project on Oracle 11g Release 1.

Business Worldwide Choose Voltaire InfiniBand to Enhance  Performance and Scalability of Oracle RAC

DBA of the Year

Look Inside >> November/December 2009

Another recent title I have taken is that I have been chosen to be the DBA of the Year 2009 by Oracle Magazine. It seems that after being the youngest Oracle ACE Director, I also became the youngest DBA nominated with this award at the age of 26:) Thanks to everybody, especially The Master Tonguc Yilmaz, for all their support.

My Second Session in Oracle Open World 2009

My second session will take place on 13th of October in Moscone South Room 104 at 14:30, and with session ID# S311394.

In this presentation we will be presenting together with my friend Timothy Chien who is a principal product manager in HA group (RMAN, data guard,etc) at Oracle.

For my part, I will be presenting the backup & recovery best practices of a newly implemented cluster environment (“all-in-one” RAC grid to be discussed in my other presentation) over 11g Release 1 in the first part. In the second part, I will be sharing my initial experiences about backup & recovery in 11g Release 2 over real Telco data warehouse data.

If you wish to learn more about real-life B&R experiences of a DBA who is responsible with +200 TB of data warehouse environment, join us at OOW.

My First Session in Oracle Open World 2009

This year I will be presenting in OOW again. It will be my first presentation in OOW as an Oracle ACED. My first session will be on using Oracle 11g RAC, Linux, and Infiniband technologies in consolidating your data center. This year, one of the largest telecommunication technologies companies of Europe has implemented a 6 node RAC (144 Intel cores, 768 GB memory, with ~25TB storage) in order to increase its overall data center utilization and decrease its administration costs.

In this session of “allinone” cluster, you will be hearing how we have achieved more with less effort by moving more than 17 different single instance databases over this commodity Oracle RAC environment. Here are some questions I will be trying to answer throughout the session :

• Why do you need consolidation (Do you ?) ?
• How to architect such a system ?
• Why to bother about Infiniband for such a large implementation ?
• Why ASM is a must for such a large implementation ?
• How to standardize backups, init.ora, and grid environment management ?
• Why separation of Linux users for CRS,ASM,etc is critical ?
• And many others…

Let’s meet with the youngest Oracle ACE Director in Open World @ session S307853.

For other sessions given by other ACEDs refer to OTN TechBlog.

_pga_max_size = 200M : Maximum size of the PGA memory for one process ?!?

For almost two years, I know a lot of  people like to play UNSUPPORTED Oracle parameters for managing sql workarea size policy of Oracle. Although Mr Lewis has published tens of illustrations proving that it is impossible to put general arguments about those parameters, people are insisting on playing with those parameters and hoping that their proposals on the nature of those parameters are valid.

In this post I will be discussing PGA_AGGREGATE_TARGET (“PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance”) and  _PGA_MAX_SIZE (“Maximum size of the PGA memory for one process) parameters from the perspective of PL/SQL development by examplifying things. At the bottomline you will understand that _PGA_MAX_SIZE is not the maximum PGA memory for one process but it is the turn point for SQL workarea content to be purged on to a temporary tablespace. Moreover although PL/SQL local memory structures are also allocated within PGA, those parameters have nothing to do with the memory allocation of PL/SQL codes. They are for SQL workarea requirements like hash join, group by, sort, and direct path load buffers.

Filling up the World’s Simplest Collection

The machine I did those tests was a 4 CPU Sun Sparc machine with 64 GB memory and running Sun Solaris. The database version was 10.2.0.4 and PGA_AGGREGATE_TARGET parameter was 24M. All UNSUPPORTED parameters had their default values. So _PGA_MAX_SIZE was 200M for that instance. So once you read the definition you think that it is not allowed for a single Oracle process to exceed 200M of allocated PGA memory (or used PGA memory if you like). Look at the following demonstration:

declare

type myarray_t is table of varchar2(32767);

myarray myarray_t := myarray_t();

i       pls_integer := 1;

begin

dbms_application_info.set_module(‘PGA USAGE TEST’,'SIMPLEST COLLECTION : 0′);

loop

myarray.extend;

myarray(i) := dbms_random.string(‘u’, 32767);

dbms_application_info.set_module(‘PGA USAGE TEST’,'SIMPLEST COLLECTION : ‘||to_char(i));

i := i + 1;

end loop;

end;

/

declare
  type myarray_t is table of varchar2(4000);
  myarray myarray_t := myarray_t();
  i       pls_integer := 1;
begin

  dbms_application_info.set_module('PGA USAGE TEST','SIMPLEST COLLECTION : 0');

  loop
    myarray.extend;
    /**
    Longest string that dbms_random.string can generate is 4000 even if you set len parameter to higher values.
    */
    myarray(i) := dbms_random.string('u', 4000);
    dbms_application_info.set_module('PGA USAGE TEST','SIMPLEST COLLECTION : '||to_char(i));
    i := i + 1;
  end loop;
end;
/

While executing this script from one  session , monitor the PGA usage from v$process view periodically by using

set linesize 500

column display_value format a5

select * from v$sql_workarea_active;

select p.PGA_USED_MEM / 1024 / 1024 pga_used,

       p.PGA_ALLOC_MEM / 1024 / 1024 pga_alloc,

       p.PGA_FREEABLE_MEM / 1024 / 1024 pga_freeable,

       s.MODULE,

       s.ACTION

  from v$process p, v$session s

 where p.ADDR = s.PADDR

   and s.MODULE = 'PGA USAGE TEST'

 order by p.PGA_ALLOC_MEM desc;

If there is no other running task on the system, you won’t see any kind of workarea allocation by the server. So all those PGA parameters have nothing to do with your execution. And if you plot the all over PGA memory allocation for our execution you will see something similar to:

As you see the PGA usage of a single process can be much larger than magic 200M when it comes to PL/SQL. Those parameters have nothing to do with PL/SQL collections’ memory usage. More dramatically if you continue to extend the collection, at some point your OS starts to swap the Oracle memory onto disk. So this means an evil developer can lead in a system crash by just doing some careless memory allocation.

One final thing to be mentioned is the ratio between PGA_ALLOCATED and PGA_USED. I have noticed that as the size of collection increases in time, Oracle starts to converge to a fix ratio (in may case it was ~2.65) for PGA_ALLOCATED/PGA_USED. It is good in the sense that Oracle keeps a constant correlation between used PGA and allocated PGA for PL/SQL collections.

 Conclusion

Sorry but Oracle doesn’t promise for anything it has not documented. Although x$ksppi.ksppdesc column describes _PGA_MAX_SIZE  parameter as the maximum PGA allocated for an Oracle process, it doesn’t say anything specific about PL/SQL memory usage. So you should ensure that your code is not using TOO MUCH MEMORY. For example, you should guarantee that you are not trying to bulk fetch a very large table at a time in your code, or you are not assuming that  the table you are fetching will not grow in time.

APEX Roadmap : APEX 4.0

Since 2006 I haven’t been developing by using Oracle APEX. As far as I remember version was 1.5 or 1.6 at that time. In time, I heard lots of great things about APEX from many people (maybe the most important one is Tonguc). So I have decided to attend Oracle Application Express 4.0 sessions at ODTUG this year. As Oracle guys have mentioned in their presentations, I will also stress that the things written in this post is just reflecting a direction. No promise that you will find all in the production release. So, here are the list of a few things thought to be available by APEX 4.0

Easy Way to Develope : Web Sheets

Even for simple form & report based applications (acutally it is a much general problem) the major problem is that users always ask for something different from what developers put on.It seems that Oracle APEX development will be possible by the actual users of the applications in the near future. With Web Sheets, Oracle’s aim is to let user to produce his/her own applications on APEX without bothering any developers. Builder and Run Time components of APEX are being merged within Web Sheets to let the development of dirty quick applications by users themselves.

For example, instead of a marketing guy sending a report to a sales guy in an Excel sheet (including some tabular data, charting, may be some input fields, etc in it) attached to an e-mail, he can simply put the report on intranet via an application easily developed on APEX. He or others can add some attachments, tags, comments on some data. Moreover, if the producer of the application or anyone else changes any piece of data, all chages will be audited. Even more they can add some notifications for their reports so that reports can be automatically sent to their e-mail boxes.

Obviously there are some differences in developing a classical APEX application and a Web Sheet. Here are just three differences between an APEX application and a Web Sheet:

	APEX Application	Web Sheets
Primary Key Maintenance	Trigger or Sequence	Automatically Managed
Page Flow	Controlled by branches	Limited
Look & Feel	Themes & templates	Basic Control

Declarative Way of Scripting : Dynamic Actions

One of the most popular things on web technologies is to put some intelligence on client-side by using cool AJAX stuff. The major problem in here is for such database geeks like me who doesn’t know anything about writing a piece of javascript. The basic idea behind dynamic actions is to allow developer to create client side AJAX actions in a declarative fashion without writing a piece of code.

Better Charts : Integration with AnyChart 5.1

When I gave up developing APEX, the only charting option you had was SVG (Support Vector Graphics). It seems that in time Oracle put another option of charting, namely flash charts into APEX. APEX uses the flash charts developed by an Oracle Partner company AnyChart. In version 4.0 the latest version of this charting module (currently 5.1) will be embedded into APEX.

Integration with Other Applications : REST Web Services

It seems that currently APEX does support SOAP Web Service calls from the applications you have built with APEX. By APEX 4.0, Oracle will also allow you to call REST Web services. Besides the architectural differences between a SOAP web service and a REST web service, the list of a few REST web services seems to be very attractive for many applications:

• Yahoo Map
• Flicker
• Google Picasa
• Amazon Products

And in the demo, Oracle guys show that how it is easy to embed a map into your application.

Extensibility : Plug-ins

Due to the fact that it is impossible /impractical to satisfy all individual customer needs, by APEX 4.0 Oracle introduces the concept of plug-in. You will be designing your own plug-ins by using bare PL/SQL APIs and you will be able to use it as an item in your applications and hopefully you will share that item for me (or the rest of the community) to use it. I think this is one of the best features and we will be using very fancy APEX plug-ins developed by many people just like the plug-ins developed for Firefox. In the presentation one classical example of these plugins was demonstrated. That was infamous star rating plugin

Finally, I am pretty sure that there will be many more in APEX 4.0 when it becomes available. So hope to return back to development some day and meet with APEX 10.0 at that time J