This is the most complete dbora script i came up with, i used linux subsystem and pid file for complete status workings… please have a look and n point out if there is anything to be done further in it… #!/bin/sh # chkconfig: 345 55 25 # /etc/init.d/dbora # Description: Starts and stops the Oracle database and listener # Author: ConArtist # Date: 21/04/14 # source function library .
Here is a simple sql query which will show which query is misbehaving and consuming the most CPU usage SELECT ss.username, ss.OSUSER, ss.TERMINAL, se.SID, VALUE / 100 cpu, sq.SQL_TEXT FROM v$session ss, v$sesstat se, v$statname sn, v$sql sq WHERE se.STATISTIC# = sn.STATISTIC# AND NAME LIKE '%CPU used by this session%' AND se.SID = ss.SID AND ss.STATUS = 'ACTIVE' AND ss.username IS NOT NULL AND ss.SQL_ID = sq.SQL_ID AND VALUE / 100 >= 1 ORDER BY VALUE DESC; And when you see the top CPU using process, monitor its activity through this query: SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME, SID, MACHINE, REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, ltrim(to_char(FLOOR(SES.LAST_CALL_ET/3600), '09')) || ':' || ltrim(to_char(FLOOR(MOD(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
Just when I was thinking how boring my job as DBA was (doing the same routine work and nothing new to implement until you convince everyone and take approvals from them!!) I was made to feel that DBA work is not at all boring if you are at the receiving end 🙂 .
This is for oracle database 10g, in 11g the table is prebuilt with another name SELECT METRIC_LABEL, T.COLUMN_LABEL, T.ALERT_STATE, T.MESSAGE, T.COLLECTION_TIMESTAMP FROM SYSMAN.MGMT$ALERT_CURRENT T
Ever spin up a database for some quick and dirty work, and you JUST WANT TO CONNECT (!!) without going through the whole ‘add the service to your local TNSNames.ORA’ rigmarole (Oracle Net Configuration Assistant)?
If you want to find out what happens when you run Oracle export or Oracle import utility is to set ON the Oracle trace event 10046. This will generate a trace file that can be used to find out the actual happenings behind the scene.
This is how you can get 10046 Trace for Export and Import Utilities
1] Run the Oracle export command export and let the program prompt you for the options.
Enter user and password as below when prompted
2] Open another window to the database server and login using sqlplus.
$ sqlplus system/manager
3] Now find out the SID of exp session
sql> select sid,program from
v$session where username = ‘SYSTEM’;
788 exp@SERVER01 (TNS V1-V3)
4] Now find the PID and SPID for that session
sql> select s.sid, p.pid, p.spid
from v$session s, v$process p
where s.paddr = p.addr and s.sid = 10;
SID PID SPID
———- ———- ———
788 189 1076
SPID from the previous query is equivalent to OSPID (operating System process). This is the process that will be traced
5] Now exit from this session
6] Generate a trace file for Procces ID 1076. To do that login as sys using sqlplus and run the commands (in bold)
$ sqlplus / as sysdba
SQL> oradebug setospid 1076
Oracle pid: 189, Unix process pid: 1076, image: oracle@SERVER01 (TNS V1-V3)
SQL> oradebug unlimit
SQL> oradebug tracefile_name
This gives the name of the trace file
SQL> oradebug Event 10046 trace name context forever, level 12;
7] From the window where “exp” command was run, now export a table
8] From the SQL prompt of the window where logged in as “sys” user
Set the Trace off once you get the Required information or the error.
SQL> oradebug Event 10046 trace name context off;
ORA-00072: process “Unix process pid: 17370, image: oracle@ SERVER01″ is not active
Now you have got the trace file which is