Archive for the ‘Oracle DBA’ Category

Oracle CPU patches for April-2014 released   Leave a comment

Oracle Has released CPU for April-2014 released

Here is the link




Posted April 16, 2014 by in Oracle DBA

Oracle Released Critical patch update for Jan-2014   Leave a comment

Posted January 15, 2014 by in Oracle DBA

What is oratop   2 comments

OS: OEL 6.4

Oracle Software: 11gR2.


oratop is a utility similar to the unix OS utility top which gives a overview of database performance and can be used in combination with top to get a more complete overview of system performance

Linux x86-64

NOTE: oratop can be used to monitor databases on other platforms but the executable must run on Linux with an Oracle client.

To monitor databases on other platforms simply define an alias in tnsnames.ora of the linux client and connect to the databases remotely as you would with sqlplus

Download the oratop executable for 11gR2 on Linux x86-64 here.

Download the oratop executable for 12c on Linux x86-64 for use with 12c clients here.

Download the oratop executable for 11gR2 on Linux x86-32 here.

Download the oratop executable for 12c on Linux x86-32 for use with 12c clients here.

Please download and review the oratop Users Guide for specific instructions on how to configure and run oratop.  A Quick Start Guide is provided in the Instructions section of this Note.

Running oratop

  1. Log in to the system as the Oracle RDBMS software installation owner
  2. Stage the oratop executable on the server on which the tool will be executed.  On a RAC system it is only necessary to stage the executable on one node as it is RAC aware.
  3. Change the name of the executable, eg.
  4. $ mv oratop* oratop
     Validate the permissions for oratop are 755 (-rwxr-xr-x). If the permissions are not currently set to 755, set the permissions on oratop as follows: 
  5. $ chmod 755 oratop
  6. Configure the execution environment as follows (if not already set accordingly)
  7. $ export TERM=xterm  #or vt100

    $ export ORACLE_HOME=<11.2 database home>
    $ export PATH=$ORACLE_HOME/bin:$PATH
    $ export ORACLE_SID=<local 11.2 database SID to be monitored>  #only needed if connecting to a local database
  8. Invoke the tool to monitor a local database as follows (interval can be any interval in seconds):
    $ ./oratop -i 10 / as sysdba
  9. Non-dba privileged user requires specific grants to use the tool.  See User Guide for grants required.
  10. Databases can be monitored remotely using a tns alias as described in the oratop Users Guide.
  11. $ ./oratop -i 10 username/password@tns_alias
    $ ./oratop -i 10 system/manager@tns_alias
  12. To exit the program simply press the keyboard key “q”.  To abort, CTRL-C.




In my case nothing is running so i got below screen






Posted December 17, 2013 by in 12cDatabase, Oracle DBA, Oracle10g/11g, OracleRAC

Cloning an Existing Oracle11g Release 2 RDBMS Installation   3 comments

In this post i will list out the steps to clone existing Oracle 11Gr2 Oracle Home.

OS.:-OEL 6.4

Oracle Version:

Step1: I have a database running on host oh11gr2 with DB OH location as /d01/oracle/product/11.2.0 now what I am planning is to clone the Oracle Home Binaries to new server say oh11grr2-clone


Compress the Oracle Home using tar.


The intention of using root to compress the OH is to make sure that the ownership of the files is preserved correctly. Some files in $ORACLE_HOME/bin are owned by root and have the SUID / SGID set:

-rws–x—   1 root     oinstall   20872 Apr 24 12:00 nmb

-rws–x—   1 root     oinstall   28720 Apr 24 11:59 nmo

-rwsr-x—   1 root     oinstall 1340408 Sep 25  2011 oradism

so if you try to copy them as a non-root possibility of following errors occur:

cp: cannot open `$ORACLE_HOME/bin/nmb’ for reading: Permission denied

cp: cannot open `$ORACLE_HOME/bin/nmo’ for reading: Permission denied

cp: cannot open `$ORACLE_HOME/bin/oradism’ for reading: Permission denied

scp the compressed Oracle Home to target i.e oh11grr2-clone


Once tar is finished login to target server and create location to hold our Oracle Binaries

In my case i have created mkdir /u01/oracle/product.

Once this is done. I started scp of 11.2.0.tar.gz from source to target.


*NOTE: Make sure you fulfill all the pre-req’s on the Target Server. (Installing RPM’s etc.)

Once scp is finished login into the target node change the and untar the compressed 11.2.0.tar.gz file as follows

Once this is done change the ownership to owner as oracle and group as dba

chown -R oracle:dba 11.2.0



Make sure you create inventory location in /etc/

vi /etc/oraInst.loc

add inventory_loc=/u01/app/oracle/oraInventory

this has to be created by root and change the permissions as 755

chmod 755 /etc/oraInst.loc

Once this is done go to below location

cd /u01/oracle/product/11.2.0/clone/bin and excute as below

perl ORACLE_HOME=”/u01/oracle/product/11.2.0″ ORACLE_HOME_NAME=”Oracle_target” ORACLE_BASE=”/u01/oracle/product/” OSDBA_GROUP=dba OSOPER_GROUP=dba


8 9

Run the script as below


Posted December 16, 2013 by in Oracle DBA, Oracle10g/11g

ORA-01652: unable to extend temp error-Viewing SID and SERIAL# That Is Consuming Temporary Space   Leave a comment

When Oracle throws the ORA-01652: unable to extend temp error, this may be an indication that your temporary
tablespace is too small. However, Oracle may throw this error if it runs out of space because of a one-time event, such
as a large index build. You’ll have to decide whether a one-time index build or a query that consumes large amounts
of sort space in the temporary tablespace warrants adding space.

To view the space a session is using in the temporary tablespace, run this query:

set linesize 1000
set pagesize 1000
col USERNAME format a20
col SPID format a10
col MODULE format a30
col PROGRAM format a30
SELECT s.sid, s.serial#, s.username
,p.spid, s.module, p.program
,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used
FROM v$sort_usage su
,v$session s
,dba_tablespaces tbsp
,v$process p
WHERE su.session_addr = s.saddr
AND su.tablespace = tbsp.tablespace_name
AND s.paddr = p.addr
s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,
p.program, tbsp.block_size, su.tablespace
ORDER BY s.sid;

Sample Output
You will find the temp consumption accordingly in MB_USED column. if any process is consuming more temp and if others are facing this error frequently then increase TEMP tablespace.
If you determine that you need to add space, you can either resize an existing data file or add a new one. To resize a temporary tablespace data file, use the ALTER DATABASE TEMPFILE...RESIZE statement. The following command resizes a temporary data file to 12GB:
SQL> alter database tempfile '/u01/dbfile/o12c/temp02.dbf' resize 12g;
You can add a data file to a temporary tablespace, as follows:
SQL> alter tablespace temp add tempfile '/u02/dbfile/o12c/temp04.dbf' size 2g;

Posted November 11, 2013 by in Oracle DBA, Oracle10g/11g

Why ora.diskmon is offline in   Leave a comment

I have installed Single node Grid Infrastructure with asm configured.
As Grid Infrastructure daemon diskmon.bin is used for Exadata fencing, started from, resource ora.diskmon will be offline in non-Exadata environment. This is expected behaviour change.

This is as per metalink note: Grid Infrastructure diskmon Will be Offline by Default in Non-Exadata Environment (Doc ID 1346881.1)

Prior to
ps -ef| grep diskmon.bin

grid      3361  3166  0 22:57 ?        00:00:00 /ocw/grid/bin/diskmon.bin -d -f

ps -ef| grep diskmon.bin

>> no more diskmon.bin

Posted November 10, 2013 by in Oracle DBA, Oracle10g/11g

Whats is Oracle Restart?   Leave a comment

In this post i will go through what an Oracle Restart is……

Traditionally the database instance and related services needed to be restarted manually in the event that a failure occurred or a system reboot was performed. This became an increasingly difficult task as more and more services related to the database were created, with each one having its own startup procedure. Furthermore, the order in which these various services needed to be started and shutdown was sometimes critical.

A legacy solution was to create system scripts which executed upon system startup or which could be called upon in the event of a failure. However, such scripts were platform-specific and version-dependent.

About Oracle Restart

Oracle Restart is a facility included within the Grid Infrastructure installation for standalone database installations. It will automatically restart all database instances and services which have been registered with the facility when the system is started.

If a system is shutdown, Oracle Restart activates and shuts down all database services in an orderly manner and in the proper sequence.

Additionally, when the system is running Oracle Restart will issue periodic check to ensure that the registered facilities are running. If the check fails, the service is shutdown and restarted. As of this writing these are the services which may be managed by Oracle Restart:

  • Database instance(s)
  • Oracle Net listener
  • ASM instance
  • ASM disk groups
  • Oracle Notification Services (ONS) within a Data Guard environment

The Oracle Restart facility is part of the High Availability features of the database.

Its intent is to ensure that the database and related facilities operate on a non-stop basis, or at least recover as quickly as possible when a failure does occur.

Posted November 10, 2013 by in Oracle DBA, Oracle10g/11g