Archive

Posts Tagged ‘RAC’

dba_locks does not show for both instances

October 25, 2017 Leave a comment

Problem: While working on blocking session script below, I figured out that blocking sessions are not displayed in both nodes of the rac, exadata.

SELECT
 decode (s2.sid,s1.FINAL_BLOCKING_SESSION,'*'||s2.sid,' '||s2.sid) Blocker_SID
 ,s2.serial# blocker_serial
 ,s2.inst_id blocker_inst_id
 ,s2.con_id Blocker_con_id
 ,s2.status Blocker_Status
 ,s2.username Blocker_User
 ,s2.osuser Blocker_OS_User
 ,s2.process Blocker_OS_PID
 ,s2.sql_id Blocked_sql_id
 ,o.owner Object_owner
 ,o.object_name Locked_Object
 ,o.object_type Locked_Object_Type
 ,l2.lock_type Blocking_Lock_Type
 ,substr(s2.program,1,10) Blocker_Program
 ,s2.client_info Blocker_Client
 ,s1.sid blocked_sid
 ,s1.inst_id blocked_inst_id
 ,s1.con_id Blocker_con_id
 ,s1.status Blocked_Status
 ,s1.username Blocked_User
 ,s1.osuser Blocked_OS_User
 ,s1.process Blocked_OS_PID
 ,s1.sql_id Blocked_sql_id
 ,s1.last_call_et Blocked_Age
 ,substr(s1.program,1,10) Blocked_Program
 ,s1.client_info Blocked_Client
 FROM gv$session s2
 join gv$session s1 ON (s1.blocking_session=s2.sid)
 join dba_objects o on (o.object_id=s1.ROW_WAIT_OBJ#)
 join dba_locks l2 on (s2.sid=l2.session_id)
 left join dba_locks l1 on (s1.sid=l1.session_id and l1.lock_type = l2.lock_type AND l1.lock_id1 = l2.lock_id1 AND l1.lock_id2 = l2.lock_id2)
 where s1.last_call_et>10 and upper(l2.MODE_HELD)='EXCLUSIVE';

Cause: Usual suspect in the from clause are dba_objects, gv$session, and dba_locks. I just look into the dba_locks, because dba_objects is just related with database physical structure which should be common in RAC. gv$session also cannot be, because it is gv$, it must surely include common information among instances again. So, it must be for dba_locks, and there it is,

desc DBA_LOCKS
SESSION_ID NUMBER
LOCK_TYPE VARCHAR2(26)
MODE_HELD VARCHAR2(40)
MODE_REQUESTED VARCHAR2(40)
LOCK_ID1 VARCHAR2(40)
LOCK_ID2 VARCHAR2(40)
LAST_CONVERT NUMBER
BLOCKING_OTHERS VARCHAR2(40)

Solution:

There must be alternative real RAC aware version of it. It is GV$LOCK, some small differences in column names, and values, it has same information, and it shows more for both nodes.

desc GV$LOCK
INST_ID NUMBER
ADDR RAW(8)
KADDR RAW(8)
SID NUMBER
TYPE VARCHAR2(2)
ID1 NUMBER
ID2 NUMBER
LMODE NUMBER
REQUEST NUMBER
CTIME NUMBER
BLOCK NUMBER
CON_ID

Before my script looks like this:

SELECT
 decode (s2.sid,s1.FINAL_BLOCKING_SESSION,'*'||s2.sid,' '||s2.sid) Blocker_SID
 ,s2.serial# blocker_serial
 ,s2.inst_id blocker_inst_id
 ,s2.con_id Blocker_con_id
 ,s2.status Blocker_Status
 ,s2.username Blocker_User
 ,s2.osuser Blocker_OS_User
 ,s2.process Blocker_OS_PID
 ,s2.sql_id Blocked_sql_id
 ,o.owner Object_owner
 ,o.object_name Locked_Object
 ,o.object_type Locked_Object_Type
 ,l2.lock_type Blocking_Lock_Type
 ,substr(s2.program,1,10) Blocker_Program
 ,s2.client_info Blocker_Client
 ,s1.sid blocked_sid
 ,s1.inst_id blocked_inst_id
 ,s1.con_id Blocker_con_id
 ,s1.status Blocked_Status
 ,s1.username Blocked_User
 ,s1.osuser Blocked_OS_User
 ,s1.process Blocked_OS_PID
 ,s1.sql_id Blocked_sql_id
 ,s1.last_call_et Blocked_Age
 ,substr(s1.program,1,10) Blocked_Program
 ,s1.client_info Blocked_Client
 FROM gv$session s2
 join gv$session s1 ON (s1.blocking_session=s2.sid)
 join dba_objects o on (o.object_id=s1.ROW_WAIT_OBJ#)
 join dba_locks l2 on (s2.sid=l2.session_id)
 left join dba_locks l1 on (s1.sid=l1.session_id and l1.lock_type = l2.lock_type AND l1.lock_id1 = l2.lock_id1 AND l1.lock_id2 = l2.lock_id2)
 where s1.last_call_et>10 and upper(l2.MODE_HELD)='EXCLUSIVE';

After making modifications on column names and, associated values, my script turns out to be as follows:

SELECT
 decode (s2.sid,s1.FINAL_BLOCKING_SESSION,'*'||s2.sid,' '||s2.sid) Blocker_SID
 ,s2.serial# blocker_serial
 ,s2.inst_id blocker_inst_id
 ,s2.con_id Blocker_con_id
 ,s2.status Blocker_Status
 ,s2.username Blocker_User
 ,s2.osuser Blocker_OS_User
 ,s2.process Blocker_OS_PID
 ,s2.sql_id Blocked_sql_id
 ,o.owner Object_owner
 ,o.object_name Locked_Object
 ,o.object_type Locked_Object_Type
 ,l2.type Blocking_Lock_Type
 ,substr(s2.program,1,10) Blocker_Program
 ,s2.client_info Blocker_Client
 ,s1.sid blocked_sid
 ,s1.inst_id blocked_inst_id
 ,s1.con_id Blocker_con_id
 ,s1.status Blocked_Status
 ,s1.username Blocked_User
 ,s1.osuser Blocked_OS_User
 ,s1.process Blocked_OS_PID
 ,s1.sql_id Blocked_sql_id
 ,s1.last_call_et Blocked_Age
 ,substr(s1.program,1,10) Blocked_Program
 ,s1.client_info Blocked_Client
 FROM gv$session s2
 join gv$session s1 ON (s1.blocking_session=s2.sid)
 join dba_objects o on (o.object_id=s1.ROW_WAIT_OBJ#)
 join gv$lock l2 on (s2.sid=l2.sid)
 left join gv$lock l1 on (s1.sid=l1.sid and l1.type = l2.type AND l1.id1 = l2.id1 AND l1.id2 = l2.id2)
 where s1.last_call_et>10 and l2.LMODE=6;

 

 

Advertisements

Oracle RAC 11.2.0.3 PSU4 Installation Notes

June 20, 2013 Leave a comment

Last month we had installed Oracle RAC 11.2.0.3 PSU4 to Red Hat 6.4 (Santiago).

You could find below documentation in PDF format for each part of installation:

There you can find cases, may be specific to our environment.  In order not to re-install several times, clean OS side etc., I had preferred, first ensuring our OS and network environment is ready for installation by using Oracle cluster verification utility.

This environment is in production and working for more than 2 weeks.

Hope this helps to anyone installing Oracle RAC 🙂

Oracle RACSIG subscription

December 25, 2012 Leave a comment

While looking for something in Oracle discussion forum, I bumped into webinar link, which lead me to subscribe Oracle RAC SIG.

It will enable me to get enthusiast with RAC live webinars, documents from now on.

You may subcribe from http://www.oracleracsig.org/pls/apex/f?p=105:51:0::NO:51

Enjoy it !racsig

Categories: tips Tags: , , , ,

Why parallel expdp gives unable to write error

June 7, 2012 4 comments

Problem

Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object “MYSCHEMA”.”MYTABLE” failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file “/gecici/expdp/MYSCHEMAS04.expdp” for write
ORA-19505: failed to identify file “/gecici/expdp/MYSCHEMAS04.expdp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Cause

Error triggered, because it is RAC and consists of two nodes, and the filesystem mounted only on one of the nodes.

I thought that , if datapump had started without parallel option, it would not go to other db node, and everything would go well.
Because it is not the case… Datapump is started in parallel mode, and it tries to start parallel processes in ALL nodes of the cluster.

Solution

In order to prevent to start the parallel process in other node, I changed the parameter parallel_force_local value as true.

And also ensure my TNS entry will allow to go to only to desired node, by giving SID instead of SERVICE_NAME in the connection description as below:

MYDB1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydb1.turkcell.tgc)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = MYDB1)))

But it still continue to give the same error.

Here comes the solution:
In 11.2, CLUSTER parameter has come. It will enable expdp to work properly in RAC as below:

expdp system@MYDB1 SCHEMAS=MYSCHEMA1,MYSCHEMA2,MYSCHEMA3 DIRECTORY=DATAPUMPDIR LOGFILE=MYDB.log DUMPFILE=MYDB%U.expdp COMPRESSION=ALL EXCLUDE=STATISTICS PARALLEL=8 CLUSTER=N

11G R2 RAC ORA-12514 error

September 14, 2010 Leave a comment

Problem

Gettting OA-12514 from client after fresh installation of 11gR2 RAC on OEL5.

Cause

Service name mismatch between listener and tnsnames.ora.

Solution

After investigation lsnrctl services output for listeners. Understood that while the the listener contains fully qualified domain name as service name, tnsnames.ora does not. Changing the client’s tnsnames entry as follows solved the problem.

from:

MYTESTDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=mytestdb-scan.mycompany.com)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=mytestdb)))

to:

MYTESTDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=mytestdb-scan.mycompany.com)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=mytestdb.mycompany.com)))
Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.