Archive for the ‘Oracle10g/11g’ Category
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
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.
- Log in to the system as the Oracle RDBMS software installation owner
- 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.
- Change the name of the executable, eg.
$ 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:
$ chmod 755 oratop
- Configure the execution environment as follows (if not already set accordingly)
$ export TERM=xterm #or vt100
$ export ORACLE_HOME=<11.2 database home>
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ 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
- Invoke the tool to monitor a local database as follows (interval can be any interval in seconds):
$ ./oratop -i 10 / as sysdba
- Non-dba privileged user requires specific grants to use the tool. See User Guide for grants required.
- Databases can be monitored remotely using a tns alias as described in the oratop Users Guide.
$ ./oratop -i 10 username/password@tns_alias
$ ./oratop -i 10 system/manager@tns_alias
- 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
In this post i will list out the steps to clone existing Oracle 11Gr2 Oracle Home.
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/
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 clone.pl ORACLE_HOME=”/u01/oracle/product/11.2.0″ ORACLE_HOME_NAME=”Oracle_target” ORACLE_BASE=”/u01/oracle/product/” OSDBA_GROUP=dba OSOPER_GROUP=dba
Run the roor.sh script as below
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
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;
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;
I have installed Single node Grid Infrastructure with asm configured.
As Grid Infrastructure daemon diskmon.bin is used for Exadata fencing, started from 126.96.36.199, resource ora.diskmon will be offline in non-Exadata environment. This is expected behaviour change.
This is as per metalink note:188.8.131.52 Grid Infrastructure diskmon Will be Offline by Default in Non-Exadata Environment (Doc ID 1346881.1)
Prior to 184.108.40.206:
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
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.
11gr2 Installation error, When i was Installing 220.127.116.11 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 p10404530_112030_Linux-x86-64_1of7.zip and p10404530_112030_Linux-x86-64_2of7.zip properly.
Once I unzipped the files again, i am able to finish the installation successfully
Session Related Queries
1. Show all connected users
set lines 100 pages 999
col ID format a15
, sid ‘,’ serial# “ID”
, last_call_et “Last Activity”
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 ) );
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 sn.name = ‘opened cursors current’
order by value