Earlier this week (10-April), I had done a Switchover from the 2node RAC database to a SingleInstance database.
Continuing the series of posts on the RAC/ASM to SingleInstance/FileSystem DataGuard configuration … With both instances RAC1 and RAC2 running, I have redo shipping from both instances to the single instance STBY.(In my test server with 3 VMs, it takes a number of minutes to have all 3 instances running and communicating with each other … so there are spurious ORA and TNS errors until the 3 instances have stabilized.
Continuing with the 220.127.116.11 non-RAC MultiTenant environment and two services demonstrated earlier, I have restarted the environment today : [grid@ora12102 ~]$ lsnrctl servicesLSNRCTL for Linux: Version 18.104.22.168.0 - Production on 16-JUN-2016 22:57:17Copyright (c) 1991, 2014, Oracle.
Here is a very clean and simple script which will monitor and check the status of listener for oracle database.
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 🙂 .
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
Sometime you may find that your SQL session is hanging or some application that accesses your database is hanging. The application that access an Oracle database basically creates a session to the database. And it is that session that will be hanging which to normal usr looks like the application is hanging.
Whether it is the SQL session/statement or an application that is hanging sometime it becomes difficult to find out what is causing the database to hang.
You may use the usual tools such as AWR (automatic workload repository) and ADDM (automatic database diagnostic monitor) to find out what is causing the database to hang. But many time the information you receive from this tool is not enough to determine what is causing it or to identify which is causing it.
Moreover if your database instance is hanging then you can not run AWR and ADDM.
In such case you can use a tool called hanganalyze. This tool is provided by oracle and is part of oradebug utility. This is very handy to find out exactly which session is causing the hang situation.
Here are the steps how you to you oracle hanganalyze tool:
Login as sysdba and run the following:
sqlplus / as sysdba
create user joe identified by joe;
grant create session to joe;
grant resource to joe;
Simulate a session hang (in this case row locking):
Login as user joe from two sessions. Lets refer them session 1 and session 2.
From session 1 do the following:
create table dept (
insert into dept values (10, ‘Finance’,’London’);
insert into dept values (20, ‘HR’,’London’);
SQL> select * from dept;
DEPTNO DNAME LOCATION
———- ——————– ——————–
10 Finance London
20 HR London
set location=’New York’
where deptno = 10;
Now from session 2 try to update the same data by executing the code below:
where deptno = 10;
You will notice that the session is hanging. It is because the first update in session 1 has updated the same tow and the operation is not yet committed. So provide data consistency session 1 has locked the row and hence now session 2 needs to wait until session 1 commits or rollback;
Now run hanganalyze to find out exactly which session is blocking whom.
Login as sys user and find out the spid of the session that is hanged.
sqlplus / as sysdba
select a.sid, a.serial#, b.spid ospid, to_char(logon_time,’dd-Mon-rr hh24:mi’) Logintime
from gv$session a, gv$process b
where a.inst_id = b.inst_id and a.paddr = b.addr and status = ‘ACTIVE’;
Note down the ospid of the hanged session. Then run the following:
SQL> oradebug setospid [spid]
SQL> oradebug unlimit;
SQL> oradebug hanganalyze 3
This will generate a trace file which will contain the detail why the oracle database session is hanging. Even though the trace file will contain loads of information still it is quite easy to find out the real culprit.
Open the trace file and find out the session marked as “Chains most likely to have caused the hang:”. There you will see Chain 1 and Chain 2 which are basically session operations.
Going further down where these Chains are defined, you will see information such as session, serial#, blocking session, current SQL etc.
Soem extract of my trace file is as below:
Chains most likely to have caused the hang:
[a] Chain 1 Signature: ‘SQL*Net message from client’
Chain 1 Signature Hash: 0x38c48850
[b] Chain 2 Signature: ‘LNS ASYNC end of log’
Chain 2 Signature Hash: 0x8ceed34f
and section that provide chain detail
Oracle session identified by:
instance: 1 (dtcnmh.dtcnmh)
os id: 2653
process id: 36, oracle@SERV01A (TNS V1-V3)
session id: 770
session serial #: 11739
is waiting for ‘enq: TX – row lock contention’ with wait info:
time in wait: 59.699862 sec
timeout after: never
wait id: 26
blocking: 0 sessions
current sql: update dept
I hope this helps.