ORA-04030 means machine hosting database instance has run out of memory because database instance is requesting memory for PGA.
In some cases allocated PGA can exceed PGA_AGGREGATE_TARGET parameter because untunable PGA may grow very large due to PL/SQL collections variables for example.
You can check maximum PGA allocated in GB with:
SQL> select name, value/(1024*1024*1024) GB from v$pgastat where name like '%max
%allocated%';
NAME GB
---------------------------------------------------------------- ----------
maximum PGA allocated ,080384254
Note: Thanks to Forstmann, the above was originally published at http://forums.oracle.com/forums/thread.jspa?threadID=1120120&tstart=15. I published above from my old notes but the reference was missing from there.
Friday, December 31, 2010
Saturday, December 18, 2010
srvctl in 11.2.0.2 to stop and start database
Lots and lots of things have changed in 11.2.0.2. It's like a whole new major release.
Even some of the common tasks have changed like starting and stopping the database.
srvctl status database -d orcl
srvctl start database -d orcl
srvctl stop database -d orcl
Even some of the common tasks have changed like starting and stopping the database.
srvctl status database -d orcl
srvctl start database -d orcl
srvctl stop database -d orcl
Installing Oracle 11.2.0.2 on OEL 5 Update 5 using Virtualbox 3.2.12 with ASM
Here are some notes regarding the installation:
Create a VM.
Install OEL5.
Shutdown VM.
Add a new disk to VM. Settings, and select SATA controller and right click and Add new Disk.
start the VM.
As root, check ls sd*, the new disk should be like sdb.
fdisk /dev/sdb, sequence should be n,p,1,return,return,p,w
Then configure oracleasm utility.
#oracleasm configure -i
then input should be oracle,dba,y,y
/usr/sbin/oracleasm init
/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1
/usr/sbin/oracleasm scandisks
/usr/sbin/oracleasm listdisks
Now first install grid infrastructure. Its in zipfile 3 of 7.
Start grid infrastucture installation.
On screen create ASM DISK Group, if you dont see any candidate disk,
then click on Change Discovery Path, and set the path like:
/dev/* or /dev/sd?1 and set the permissions to your device /dev/sdb to
775, and it should show your candidate disk like /dev/sdb1.
If you get physcial memory error during pre requiste check, just click ignore all and click next.
Then install the Oracle database in separate home.
Here are some useful links in this regard:
Check this one to see the full installation process, though it doesn't describe the pre-installation steps with clarity, but all in all a good start.
http://www.ora600.be/news/how-install-oracle-11g-release-2-oel-54-virtualbox-installing-grid-infrastructure
Check this link to create ASM disks:
http://www.oracle-base.com/articles/11g/OracleDB11gR2RACInstallationOnOEL5UsingVirtualBox.php
Create a VM.
Install OEL5.
Shutdown VM.
Add a new disk to VM. Settings, and select SATA controller and right click and Add new Disk.
start the VM.
As root, check ls sd*, the new disk should be like sdb.
fdisk /dev/sdb, sequence should be n,p,1,return,return,p,w
Then configure oracleasm utility.
#oracleasm configure -i
then input should be oracle,dba,y,y
/usr/sbin/oracleasm init
/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1
/usr/sbin/oracleasm scandisks
/usr/sbin/oracleasm listdisks
Now first install grid infrastructure. Its in zipfile 3 of 7.
Start grid infrastucture installation.
On screen create ASM DISK Group, if you dont see any candidate disk,
then click on Change Discovery Path, and set the path like:
/dev/* or /dev/sd?1 and set the permissions to your device /dev/sdb to
775, and it should show your candidate disk like /dev/sdb1.
If you get physcial memory error during pre requiste check, just click ignore all and click next.
Then install the Oracle database in separate home.
Here are some useful links in this regard:
Check this one to see the full installation process, though it doesn't describe the pre-installation steps with clarity, but all in all a good start.
http://www.ora600.be/news/how-install-oracle-11g-release-2-oel-54-virtualbox-installing-grid-infrastructure
Check this link to create ASM disks:
http://www.oracle-base.com/articles/11g/OracleDB11gR2RACInstallationOnOEL5UsingVirtualBox.php
Friday, December 17, 2010
11.2.0.2 for Linux zip files Contents
Installation Types and Associated Zip Files of 11.2.0.2:
Courtesy MOS.
Oracle Database (includes Oracle Database and Oracle RAC) Note: you must download both zip files to install Oracle Database. | p10098816_112020_ platform _1of7.zip p10098816_112020_ platform _2of7.zip |
Oracle Grid Infrastructure (includes Oracle ASM, Oracle Clusterware, and Oracle Restart) | p10098816_112020_ platform _3of7.zip |
Oracle Database Client | p10098816_112020_ platform _4of7.zip |
Oracle Gateways | p10098816_112020_ platform _5of7.zip |
Oracle Examples | p10098816_112020_ platform _6of7.zip |
Deinstall | p10098816_112020_ platform _7of7.zip |
Courtesy MOS.
Sunday, December 12, 2010
Oracle Grid Control Book By Porus Homi Havewala; Maestro at Work
Porus Homi Havewala has come up with a gem of a book, which was most needed in the days of resource mayhem when the DBAs are striving hard to bring method to the madness of managing every facet of many databases, application servers, Exadata, E-Business suites and much more.
An Oracle Employee ACE, previously an Oracle ACE Director, a frequent contributor to the community, and a frequent writer at OTN, Porus is giving you this elephant, his book, a very useful weapon to conquer the challenges. This books discusses the bleeding edge 11g Grid Control technology and enables you to utilize the features of EM like a breeze. With the help of this book, you get more free time to focus on strategic things which is the true purpose of the Grid.
You can get this gem at a throw away price here.
An Oracle Employee ACE, previously an Oracle ACE Director, a frequent contributor to the community, and a frequent writer at OTN, Porus is giving you this elephant, his book, a very useful weapon to conquer the challenges. This books discusses the bleeding edge 11g Grid Control technology and enables you to utilize the features of EM like a breeze. With the help of this book, you get more free time to focus on strategic things which is the true purpose of the Grid.
You can get this gem at a throw away price here.
Saturday, December 4, 2010
sysasm in 11gR2
This is more of a point to remember for myself.
In 11g, you don't perform the sys operations on the ASM instance with the sysdba privileges.
You have to use the sysasm privileges.
You can either assign a user sysasm privileges through the sqlplus or include a user at OS level in OSASM group.
To start an ASM instance:
export ORACLE_SID+ASM
sqlplus '/ as sysasm'
In 11g, you don't perform the sys operations on the ASM instance with the sysdba privileges.
You have to use the sysasm privileges.
You can either assign a user sysasm privileges through the sqlplus or include a user at OS level in OSASM group.
To start an ASM instance:
export ORACLE_SID+ASM
sqlplus '/ as sysasm'
Subscribe to:
Posts (Atom)