Archive for the ‘Oracle10g/11g’ Category

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 (11gR2) installation error message “File not found WFMLRSVCApp.ear”   1 comment

11gr2 Installation error, When i was Installing i was shocked to see this error. Even after performing all the pre-reqs i encountered this issue.



The issue is due to not unzipping the and properly.

Once I unzipped the files again, i am able  to finish the installation successfully




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

Oracle Session Related Queries   Leave a comment

Session Related Queries

1. Show all connected users
set lines 100 pages 999
col ID format a15
select username
, sid ‘,’ serial# “ID”
, status
, last_call_et “Last Activity”
from v$session
where username is not null
order by status desc
, last_call_et desc

2. Time since last user activity
set lines 100 pages 999
select username, floor(last_call_et / 60) “Minutes”, statusfrom v$sessionwhere username is not nullorder by last_call_et

3. Sessions sorted by logon time
set lines 100 pages 999
col ID format a15col osuser format a15
col login_time format a14
select username, osuser, sid ‘,’ serial# “ID”, status, to_char(logon_time, ‘hh24:mi dd/mm/yy’) login_time, last_call_etfrom v$sessionwhere username is not nullorder by login_time

4. Show user info including os pid
col “SID/SERIAL” format a10
col username format a15col
osuser format a15col
program format a40
select s.sid ‘,’ s.serial# “SID/SERIAL”, s.username, s.osuser, p.spid “OS PID”, s.programfrom v$session s, v$process pWhere s.paddr = p.addrorder by to_number(p.spid)

5. Show a users current sql
Select sql_textfrom v$sqlareawhere (address, hash_value) in(select sql_address, sql_hash_value from v$session where username like ‘&username’)

6. Session status associated with the specified os process id
select s.username, s.sid, s.serial#, p.spid, last_call_et, statusfrom V$SESSION s, V$PROCESS pwhere s.PADDR = p.ADDRand p.spid=’&pid’

7. All active sql
set feedback offset
serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
for x in
(select username'(‘sid’,’serial#’) ospid = ‘ process ‘ program = ‘ program username,
to_char(LOGON_TIME,’ Day HH24:MI’) logon_time,
to_char(sysdate,’ Day HH24:MI’) current_time,
sql_hash_value from v$session
where status = ‘ACTIVE’ and rawtohex(sql_address) <> ’00’
and username is not null ) loop
for y in
(select sql_text from v$sqlarea where address = x.sql_address ) loop
if ( y.sql_text not like ‘%listener.get_cmd%’ and
y.sql_text not like ‘%RAWTOHEX(SQL_ADDRESS)%’ )
then dbms_output.put_line( ‘——————–‘ );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time ‘ ‘ x.current_time ‘ SQL#=’ x.sql_hash_value); dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
8. Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username, to_char(start_time, ‘hh24:mi:ss dd/mm/yy’) started, time_remaining remaining, messagefrom v$session_longops
where time_remaining = 0
order by time_remaining desc

9. List open cursors per user
set pages 999
select sess.username, sess.sid, sess.serial#, stat.value cursors
from v$sesstat stat, v$statname sn, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and = ‘opened cursors current’
order by value


Posted October 29, 2013 by in Oracle10g/11g