Archive

Archive for the ‘tips’ Category

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 🙂

expdp with sys without knowing password

February 19, 2013 6 comments

Simple but very handy way of taking exports without knowing database password.

expdp \’/ as sysdba\’ DIRECTORY=DATA_PUMP_DIR DUMPFILE=example.dmp LOGFILE=example.log SCHEMAS=MYSCHEMA

Categories: tips Tags: , ,

Using Index Unusable For Gaining Disk Space In Partitioned Tables

February 9, 2013 Leave a comment

Why dedicate disk space for indexes that you are not using for data access in older partitions.

–index partition usable

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date(‘31.10.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’)

Plan
SELECT STATEMENT ALL_ROWS Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2
3 PARTITION RANGE SINGLE Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2 Partition #: 1 Partitions accessed #1
2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2 Partition #: 2 Partitions accessed #1
1 INDEX RANGE SCAN INDEX (UNIQUE) ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK Search Columns: 1 Access Predicates: “DATETIME”=TO_DATE(‘ 2011-10-31 23:45:00’, ‘syyyy-mm-dd hh24:mi:ss’) Cost: 159 Cardinality: 79.677 CPU Cost: 17.926.550 IO Cost: 158 Time: 1 Partition #: 3 Partitions accessed #1

–index partition unusable, after

ALTER INDEX ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK

MODIFY PARTITION P20111031

UNUSABLE;

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date(‘31.10.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’)

Plan
SELECT STATEMENT ALL_ROWS Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9
2 PARTITION RANGE SINGLE Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9 Partition #: 1 Partitions accessed #1
1 TABLE ACCESS FULL TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Filter Predicates: “DATETIME”=TO_DATE(‘ 2011-10-31 23:45:00’, ‘syyyy-mm-dd hh24:mi:ss’) Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9 Partition #: 2 Partitions accessed #1

–other partitions goes over indexes (usable index partitions) Beware that, if you are using bind variables, you may go with FTS over indexed partitions. You may use hints for a solution for access path, like INDEX(CELLHANDOVERS CELLHANDOVERS_PK).

 

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date(‘01.11.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’);

Plan
SELECT STATEMENT ALL_ROWS Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1
3 PARTITION RANGE SINGLE Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1 Partition #: 1 Partitions accessed #2
2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1 Partition #: 2 Partitions accessed #2
1 INDEX RANGE SCAN INDEX (UNIQUE) ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK Search Columns: 1 Access Predicates: “DATETIME”=TO_DATE(‘ 2011-11-01 23:45:00’, ‘syyyy-mm-dd hh24:mi:ss’) Cost: 7 Cardinality: 2.920 CPU Cost: 749.500 IO Cost: 7 Time: 1 Partition #: 3 Partitions accessed #2

Unique constraint also worked for usable index partition.       

Inserting same row in the range usable index triggered below error:

ORA-00001: unique constraint (ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK) violated

Only thing to remember is: DML activity on unusable partitions is impossible, without rebuilding the index!

Inserting existing row, which must trigger unique constraint, gives unusable state error:

insert into ADMIN1.RNC_CELLSTATS_DERYA_CFO(datetime,rnc,cell) values (to_date(‘31.10.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’),’KRP1R01′,’YUNUP32′);

ORA-01502: index ‘ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK’ or partition of such index is in unusable state

Inserting non-existent row, which must not trigger unique constraint, also gives  unusable state error:

insert into ADMIN1.RNC_CELLSTATS_DERYA_CFO(datetime,rnc,cell) values (to_date(‘31.10.2011 23:44:00′,’dd.mm.yyyy hh24:mi:ss’),’KRP1R01′,’YUNUP32′);

ORA-01502: index ‘ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK’ or partition of such index is in unusable state

In a 1TB sized table RNC_CELLSTATS, making unusable frees 15 GB. Thus, you may not gain big spaces from big tables. While selecting targets  focus on index segments having huge sizes.

SEGMENT_NAME GB # partitions
RNC_CELLSTATS

980

91

RNC_CELLSTATS_PK

15

91

Below, focusing on index segments sizes over 100G, you may earn 6 TB disk space. Data is extracted by using DBA_SEGMENTS dictionary view.

SEGMENT_NAME Size GB # partitions Earned GB
CELLHANDOVERS_PK

2.666

1.100

2132

CELLHANDOVERS_IND1

859

1.100

686

CELLHANDOVERS_IND3

819

1.100

654

CELLHANDOVERS_IND2

811

1.100

648

SGSN_CDR_NDX03

725

65

362

RNC_CELLHO_PK

311

91

155

MGW_E1TTP_PK

271

91

216

RNC_GSMRELATION_PK

245

91

122

CELLIDENTITY_NDX

228

65

114

RNC_CELLHO_PK2

208

91

103

RECORDOPENINGDATE_NDX

193

65

96

INTERNAL_SOHO_DS_MISSING_NE_IX

188

31

93

ENUM_FULL_IND01

161

180

128

MGW_E1TTP_D_PK

146

91

72

NEIGHBOUR_CELL_PK

128

100

63

CELLHANDOVERS_CL_PK

123

1.100

98

CCNPERFCOUNTER_PK

122

31

60

MGW_TDMTERMGRP_PK

109

91

54

CELLHANDOVERS_CL_IND2

102

1.100

81

6,280

How to kill, get rid of sticky RMAN processes

February 9, 2013 1 comment

Killing RMAN processes with alter system kill session… will not have an effect on them.They will marked as killed but never killed.

You have no time to wait, but those silly RMAN processes have much of that time. So, how to deal with these guys?

Finding the SPID of the process and killing from OS side is a solution, which most people recommend.

I have a practical, handy solution for this case, don’t wait them:) Use disconnect session clause with immediate keyword with alter system disconnect session. For example, you can disconnect a session having SID 1122 and serial# 222 like below:

alter system disconnect session ‘1122,222’ immediate;

Below is a script which could be used to generate disconnect session sqls for all of the RMAN sessions.

SELECT 'alter system disconnect session '''||sid||','||serial#||''' immediate;' 
FROM V$SESSION s 
 WHERE 
(
UPPER( s.PROGRAM )LIKE 'RMAN%'
)

 

Remember that there are differences between disconnect and kill session clauses.
Most remarkable is ability to give inst_id or not.

In case of disconnect session, You cannot issue disconnect session to sessions spread on cluster instances from one session. Thus, you must directly connect to sessions’ instance first, and then issue the alter system disconnect session statement.

In case of kill session, you have a chance to give third parameter “inst_id” in kill session clause. This allows you to kill sessons spread on cluster instances from one session.

It woths referring to SQL Language Reference for details of disconnect session and kill session clauses.

Hope this helps!

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: , , , ,

Which rights must be granted for displaying TOAD session browser

November 22, 2012 1 comment

Following rights must be given to specific user, here MYUSER, in order to display sessions in TOAD session browser.

GRANT SELECT ON GV_$SESSION TO MYUSER;
GRANT SELECT ON GV_$PROCESS TO MYUSER;
GRANT SELECT ON GV_$SESS_IO TO MYUSER;
GRANT SELECT ON GV_$SESSION_WAIT TO MYUSER;
GRANT SELECT ON GV_$SESSION_EVENT TO MYUSER;
GRANT SELECT ON GV_$ACCESS TO MYUSER;
GRANT SELECT ON GV_$SESSTAT TO MYUSER;
GRANT SELECT ON GV_$SQL_PLAN TO MYUSER;
GRANT SELECT ON GV_$SQLTEXT_WITH_NEWLINES TO MYUSER;

Categories: tips Tags: , , , ,
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.