Jagjeet's Oracle Blog!

December 20, 2013

SQL Dashboard – V2

Filed under: Exadata, RAC — Jagjeet Singh @ 12:58 am

Quick Post – I had some fun last weekend with SQL Dashboard, thought it deserves a post. As you can see in attached video now dashboard supports wait graph in colors directly from SQL*Plus  :)  wait a minute

Well, we all know sql*plus does not support colors, However, I could simulate that using Iterm2 (Mac terminal client) by assigning colors to specific legends.

I have received several emails to make code compatible with 10g, unfortunately it is not possible due to dependency on 11g dictionaries. I have fixed several bugs and publishing 11g R2 and 11g R1 version.

Only noticeable enhancement is graph with wait events.

May 13, 2012

Sqlplus Dashboard For RAC

I have been passionate to work with CLI due to flexibility, lightweight and easy access. Long back, I had an idea to write a sqlplus script to provide a dashboard kind of utility but could never figure out the auto refresh functionality until unless Tanel & Adrian provided moats.

I got the Idea and solution of auto refresh functionality in sqlplus. Big thanks to great Tanel & Adrian. Majority of my time I spent doing bench-marking on Exadata, so I decided to write a sqlplus dashboard kind of utility for RAC, idea was to get some critical metrics for performance benchmarks on one screen and have a automatic refresh capability ( similar EM “TOP Activity” page )

It took almost 3 weeks for me to write this utility from scratch, It tells you info about Exadata smart scan/storage index saving/Flash cache/ CPU usage/TOP sqls/waits/Cluster interconnect traffic/Top segments by GC* waits and a lot more info. which can be useful to monitor system performance. Based on current formatting, It can support up to 8 node cluster information easily.
I tried my best to make this utility lightweight by reading data from gv$ views once and store in plsql/collections for subsequent requests, Could not find session in EM top activity page running this utility. As of today, it will only work with 11g RAC instances or single instance with gv$* views [catclust.sql].

here is video link… video quality isn’t that great. Must be seen in HD mode.

I have tried to explain about metrics below.


############################################
-- Below section shows metrics instance wise
############################################


+Inst-------+CPUIDL%--IO%-USR%--SYS%+--Tprse/s--+Hprse/s+--PhyWIO/s-+-PhyWMB/s-+--PhyRIO/s--+-PhyRMB/s-+-SessLIO/s--+---Exec/s-+RedoMB/s+Commit/s+-ExSSMB/s-+-ExSIMB/s+-ExFCRh/s+
|AIA1       | 67.8     0  31.2    .7|       2714|      0|       2623|        21|        1183|         9|      370001|      8471|      11|    2001|         0|        0|      980|
|AIA2       | 68.2     0  31.1    .6|       2709|      0|       2491|        22|         928|         7|      340040|      8609|      11|    2004|         0|        0|      703|
+-----------+-----------------------+-----------+-------+-----------+----------+------------+----------+------------+----------+--------+--------+----------+---------+---------+


+Inst-------+ <-- Instance name     +CPUIDL%--IO%-USR%--SYS%+ 
|AIA1       |                       | 67.8     0  31.2    .7| -- This sections shows CPU usage IDLE% IO%, USER% SYS% 
|AIA2       |                       | 68.2     0  31.1    .6| -- For Exadata, we may not see IO Usage as Grid disks exposed to ASM only 
+-----------+                       +-----------------------+




+--Tprse/s--+Hprse/s+--PhyWIO/s-+-PhyWMB/s-+--PhyRIO/s--+-PhyRMB/s-+-SessLIO/s--+
|       2714|      0|       2623|        21|        1183|         9|      370001|
|       2709|      0|       2491|        22|         928|         7|      340040|
+-----------+-------+-----------+----------+------------+----------+------------+

--  Tprse/s   : Total parse per second
--  Hprse/s   : Hard parse per second
--  PhyWIO/s  : Physical Write IO per second
--  PhyWMB/s  : Physical Write MB per second                                                         
--  PhyRIO/s  : Physical Read IO per second								
--  PhyRMB/s  : Physical Read MB per second								
--  SessLIO/s : Session Logical IO per second								
		


+---Exec/s-+RedoMB/s+Commit/s+
|      8471|      11|    2001|
|      8609|      11|    2004|
+----------+--------+--------+

-- Exec/s   : Total Executions for the Instance per second
-- RedoMB/s : Redo Generation MB per second 
-- Commit/s : User commit per second






-- Exadata Related Stats
+-ExSSMB/s-+-ExSIMB/s+-ExFCRh/s+
|         0|        0|      980|
|         0|        0|      703|
+----------+---------+---------+

-- ExSSMB/s  : cell physical IO interconnect bytes returned by smart scan MB per second 
-- ExSIMB/s  : cell physical IO bytes saved by storage index MB per second
-- ExFCRh/s  : cell flash cache read hits per second 

--     Note : Above snapshot was taken for OLTP kind of workload, hence no 
--            smartscan/storage index was in picture, only Flash Cache

 
#########################################
-- Below section shows Cluster Level info
#########################################

+IMPACT%-+--TOP WAIT EVENTS-----------------------+-WAIT CLASS--------+ 
|  51.2% | transaction                            | Other             | 
|  28.2% | enq: TX - index contention             | Concurrency       | 
|   8.7% | gc buffer busy acquire                 | Cluster           | 
|     5% | cell single block physical read        | User I/O          | 
|   2.2% | gc current grant busy                  | Cluster           | 
+--------+----------------------------------------+-------------------+ 

 Above sections shows TOP 5 wait events for the last sample [ default 6 second ] 	

-- IMPACT%    :  Impact was calulcated based on AWR formula, Time spent on individual wait event / Total Wait Time * 100
-- WAIT EVENT :  Name of the Wait Event 
-- WAIT CLASS :  Name of the Wait Class

  
 * * Cluster Level info * *
+IMPACT%-+ TOP SQLS -(child)-+-TOP SESSIONS-------INST:SID----------------------------------------+
|  26.5% | bk2mk4dh179y7(2)  | 1:1043, 1:1174, 1:1239, 1:1304, ......                             |
|  17.6% | bk2mk4dh179y7(0)  | 1:1045, 1:1109, 1:1110, 1:1174, .......                            |
|  11.8% | 7jjp97nb9h2up(1)  | 2:1048, 2:1109, 2:1112, 2:1174,                                    |
|   7.5% | 0r5xv5d42p3p6(2)  | 1:653, 1:979, 2:1047, 2:1501, 2:588, 2:68  .....                   |
|   7.3% | apgu0yan9pb6m(5)  | 1:653, 1:979, 2:1047, 2:1501, 2:588, 2:68  ......                  |
+--------+-------------------+--------------------------------------------------------------------+

 Above sections shows TOP 5 SQLs for the last sample [ default 6 second ] 	

-- IMPACT%    :  Impact was calculated based on AWR formula, Time spent on individual sql / Total Wait Time * 100
-- TOP SQL - (child) : Sql id along with child info
-- INST:SID   :  Instance ID and SID for the session running sql, due to space limitation, only few sessions will shown






   * * Cluster Level info * *

           -------+                                 ACTIVE SESSIONS GRAPH                              +----
     Active   375 |                                                                                    | 375
   Sessions   349 |                                                                                    | 349
              322 |                            #                    #                                  | 322
              295 |                            #   # ##       #   # #                                  | 295
              268 |                            #   # ##       #   # #                  #          #_288| 268
              242 |                            #   # ###  #   #   # #                 ### #       #    | 242
              215 |                            #   # ###  #   #   # #                 #####       #    | 215
              188 |                            #   # ###  #   #   # #                 #####  #    #    | 188
              161 |                           ##################  #########     ##    #####  ######    | 161
              134 |                           #############################     #### ##############    | 134
              109 |                           ##############################    ###################    | 109
     	       81 |                           ##############################  #####################    | 81
               24 |             -MAX CPUs [24]-####################################################    | 24
                1 |                           #####################################################    | 1
                0 +------------------------------------------------------------------------------------+ 0
		  ^ 01:42:22                     01:49:02 ^                             01:55:42 ^



-- Above sections shows total active sessions across the cluster, time window calculated based on sample time [6 second default ]
-- total 80 samples are printed in graph, one line represents one sample time. Active Session range will change automatically
-- based on active sessions and graph will adjust accordingly. Idea was to have some trend about the active session history.
-- MAX CPU will also be shown to have some idea about Active sessions Vs CPUs available.

-- Note : if value falls in between 2 range buckets then it will be shown in front of lower value, for example - in above screenshot 
--        current active sessions were 288 and it was displayed in front of 268, same applies to MAX CPU info as well
 

   * * Instance level info * *

    |  Global  |  Global  | Estd.     | 
    |  Cache   |  Cache   | Intercnt  | 
Inst|  Blocks  |  Blocks  | Traffic   | 
  ID|  Sent/s  |  Rcvd/s  | MB/s      | 
+---+----------+----------+-----------+ 
  1 |     10831|     10188|      173.3| 
  2 |     10650|     10586|        175| 
                                        

-- Above sections shows Global Cache info instance wise

-- InstID : Instance ID no.
-- GC Blocks Sents/s :  Global cache blocks sent by instance per second
-- GC Blocks Rcvd/s  :  Global cache blocks received by instance per second
-- Estd. Interconnect Traffic MB :  Estimated traffic MB for interconnect by instance





 * * Cluster Level info * *
                             
 | TOP Segments by GC*     |    
 | Waits                   |    
 | IMPACT% [Type:Segment]  |   
 +-------------------------+    
 | 40.8% IND:REFERENCE_INSTANCE 
 | 23.3% IND:MEDIATOR_INSTANCE  
 | 17.4% IND:REFERENCE_INSTANCE 
 |  5.8% IND:S_DOCK_TXN_LOG_P1  
    4.2% IND:COMPOSITE_INSTANCE                                                                         

-- TOP Segments based on all Global Cache* events, Impact is calculated based on GC* waits on individual
-- Segment / Total GC* waits * 100





  * * Cluster level info * * 

+--SqlID--------+--SqlText----------------------------------------------------------------------------+-LongstDur-+-InstCnt-+-Cnt-+--CPU%--+--CONC%--+--CLUS%--+-IO%-+---ReadMb-+
| 0r5xv5d42p3p6 | INSERT INTO REFERENCE_INSTANCE (ID, BINDING_TYPE, REFERENCE_NAME, UPDATED_TIME, PRO |           |    2    |  12 |      0 |      0  |      0  |    0|         0|
+---------------+-------------------------------------------------------------------------------------+-----------+---------+-----+--------+---------+---------+-----+----------+
| bk2mk4dh179y7 | select VALUE from XREF_DATA  where XREF_COLUMN_NAME = :1  and XREF_TABLE_NAME = :2  | 00:00:09  |    2    |  46 |    59% |     3%  |      0  |   4%|       227|
+---------------+-------------------------------------------------------------------------------------+-----------+---------+-----+--------+---------+---------+-----+----------+
| 7jjp97nb9h2up | INSERT INTO COMPOSITE_INSTANCE (ID, COMPOSITE_DN, CONVERSATION_ID, UPDATED_BY, PARE |           |    2    |  14 |      0 |      0  |      0  |    0|         0|
+---------------+-------------------------------------------------------------------------------------|-----------+---------+-----+--------+---------+---------+-----+----------+
| apgu0yan9pb6m | insert into  XREF_DATA  ( XREF_TABLE_NAME, XREF_COLUMN_NAME, ROW_NUMBER, VALUE, IS  |           |    2    |  14 |      0 |      0  |      0  |    0|         0|
+---------------+-------------------------------------------------------------------------------------|-----------+---------+-----+--------+---------+---------+-----+----------+

-- Above sections shows cummulative info for the TOP Sqls 

-- LongstDur : if multiple sessions are running same sql, this column would tell timing for the first query, longest duration.
-- InstCnt   : Total no. of Instance running same sql at time of sample [ including parallel and non-parallel query ]
-- Cnt       : Total sessions running same query across all the instances [ including parallel and non-parallel ] 

-- CPU%  : %time spent on CPU
-- CONC% : %time spent on Concurrency
-- CLU%  : %time spent on Cluster waits
-- IO%   : %time spent on IO

--ReadMB : Total physical read mb by the query, cummulative for all running queries across the cluster

-- Note: Most of the columns are fetched from gV$sql_Monitor, will only be populated if query running more than 6 seconds on CPU/IO


Code can be downloaded here.

Please do not forget to share your suggestions/feedback. Thanks –

Note: This utility uses ASH and v$sql_monitor which requires diagnostics and tuning pack !

December 12, 2011

Library cache: mutex X

Filed under: Oracle 11g — Tags: , , — Jagjeet Singh @ 5:24 pm

Recently, I was engaged in Exadata POV for benchmarking for Ebiz R12 on Exa* boxes. We had quarter rack running 11.2.0 db with 2 node RAC Cluster (24 CPU) and all other components/Middle Teir including Form, Web, CM were configured on Exalogic Server. Objective was to achieve best possible scalability numbers on Exa* boxes for few Ebiz modules.

Both boxes were connected with Infiniband network, Test scenarios were given for Order2Cash and Self-services modules. Idea was to utilized Load runner to generate the load using few hundred users. After struggling a while we could streamlined our testing scripts and now were ready to focus on performance.

We had several hiccups while running performance tests but issues were eroded by following generic guidelines on performance tuning, we were doing good progress until we stuck with “library cache: mutex X” issues which was kind of road block for us. Each time we touch 600 concurrent users for Order 2 Cash, we had high waits on mutex issue, CPU utilization were constantly touching 100%.  Have a look to below screenshot, most of the sessions were waiting on concurrency.

One of my colleague immediately pointed out about known bug in Oracle 11g for mutex issues, We could find several hits for the bug but apply a patch was not an option due to conflict with

Exadata Bundle Patch. Latest BP did not solve the issue and we were left out to do something by own ( if we can ).

Plan B – as always, Google is the only option for people like me ( may not be the good solution for production like instances ), I was lucky to came across with http://andreynikolaev.wordpress.com/2011/05/01/divide-and-conquer-the-true-mutex-contention/    Andrey S. Nikolaev has a great post sharing his experience to handle mutex issues. I was excited to see if out database is also suffering with same issues.

I used script from his post “library_cache_objects_heavily_accessed.sql” and below was output from our DB.

Select * from (
Select case when (kglhdadr = kglhdpar) then 'Parent' else 'Child '||kglobt09 end cursor,
kglhdadr ADDRESS,substr(kglnaobj,1,20) name, kglnahsh hash_value,kglobtyd type,kglobt23 LOCKED_TOTAL,kglobt24 PINNED_TOTAL,kglhdexc EXECUTIONS,kglhdnsp NAMESPACE
from x$kglob
order by kglobt24 desc)
where rownum <= 20
/



ADDRESS          NAME                 HASH_VALUE TYPE    Locked    Pinned  Executed  Nsp
---------------- -------------------- ---------- ------- ------ --------- --------- ----
0000000547522910 IGI_GEN_VERT          729966110 PACKAGE ######  10501405         0    1
0000000557C98F28 MO_GLOBAL            3732637996 PACKAGE ######   7413354     30388    1
000000054FF4ABA8 IGI_GEN_VERT         1541933742 PACKAGE   2617   5250401         0    2
                                                  BODY

000000054F798C58 MO_GLOBAL            3743427754 PACKAGE  13501   5069333  31299942    2
                                                  BODY

000000054B903E78 OE_MSG_PUB            247466199 PACKAGE ######   1338812      2692    1
000000054BBBFD70 OE_SYS_PARAMETERS    2653012139 PACKAGE ######   1132706     71580    1
000000054F381F10 OE_OE_FORM_LINE      3689477926 PACKAGE ######   1015546         0    1
000000054BE04E50 DBMS_UTILITY         1023687597 PACKAGE  12098   1011637         0    1
000000055773BBA0 SELECT PROCESS_ENABL 1617001630 CURSOR     326   1007630   1007478    0
000000055B047128 DBMS_UTILITY         1295727999 PACKAGE   3748   1003292  45896957    2
                                                  BODY

000000054B904C58 OE_DEBUG_PUB         2263617911 PACKAGE ######    958636         0    1
000000054B904A18 OE_SYS_PARAMETERS_PV 2314508377 PACKAGE    953    950874         0    1
00000005472CDE20 OE_SYS_PARAMETERS    2476839672 PACKAGE    953    950874   8680985    2
                                                  BODY

00000005472CD068 OE_SYS_PARAMETERS_PV 2626471498 PACKAGE    953    950874   8862791    2
                                                  BODY

00000005472C2DC8 OE_MSG_PUB           1210870744 PACKAGE    635    937875   3267991    2
                                                  BODY

I was interested to *PINNED* column, it tells high number of calls for IGI_GEN_VERT and MO_GLOBAL packages.By default, Oracle creates multiple copies of object based on CPU Core. But still this could lead to contention if you have highly concurrent users using same package. I came to know about undocumented dbms_shared_pool.markhot() procedure Markhot procedure creates multiple copies of object in shared pool which can eliminate the contention.

Since, it was not an production instance, I decided to give it a try by marking both packages as hot.

exec dbms_shared_pool.markhot('APPS','MO_GLOBAL',1);
exec dbms_shared_pool.markhot('APPS','MO_GLOBAL',2);
exec dbms_shared_pool.markhot(hash=>3732637996,NAMESPACE=>0);

exec dbms_shared_pool.markhot('APPS','MO_GLOBAL',1);
exec dbms_shared_pool.markhot('APPS','MO_GLOBAL',2);
exec dbms_shared_pool.markhot(hash=>3743427754,NAMESPACE=>0);

exec dbms_shared_pool.markhot('APPS','IGI_GEN_VERT',1);
exec dbms_shared_pool.markhot('APPS','IGI_GEN_VERT',2);
exec dbms_shared_pool.markhot(hash=>729966110,NAMESPACE=>0);

exec dbms_shared_pool.markhot('APPS','IGI_GEN_VERT',1);
exec dbms_shared_pool.markhot('APPS','IGI_GEN_VERT',2);
exec dbms_shared_pool.markhot(hash=>1541933742,NAMESPACE=>0);

Markhot procedure needs to be called for Package and Package body individually, and after that

Wolla .. mutex X waits were reduced to 3.3%..


Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                           33,055          84.1
library cache: mutex X              209,094       1,307      6    3.3 Concurrenc
SQL*Net more data from client     5,516,878       1,220      0    3.1 Network
enq: UL - contention                     97         521   5367    1.3 Applicatio
log file sync                        72,433         514      7    1.3 Commit

For me- that was a lifesaver and it reduced the contetntion, Thanks again to Andrey S. Nikolaev

July 13, 2011

Exadata Storage Expansion Rack

Filed under: Exadata — Tags: , — Jagjeet Singh @ 10:59 pm

Oracle has announced Exadata Storage Expansion Rack, a cost effective way to add the extreme performance storage servers to an Oracle Exadata Database Machine. Good news for large Oracle shops who want to run very large databases. Earlier, we had option to add non-exadata storage to Database Machine but at the cost of compromise with unique features of Exadata like Smart Scan Technology, Storage Index, Hybrid Columnar Compression, offloading and few others.   It comes in 3 sizes with high-capacity 2TB SAS disks  ( 7200K RPM ).

Full rack includes 18 storage cells which can hold upto 194 TB of usable space and more than 400 TB of raw disk capacity.  Equipped with 216 CPU Cores and 6.75 Exadata Smart Flash Cache.Half rack includes 9 storage cells which can hold upto 216 TB of raw disk capacity with 108 CPU Cores, It has 108 CPU Cores and 3.4 TB Exadata Flash Cache, Quarter rack comes with 4 Cells, 96 TB of raw disk capacity, 48 CPU core and 1.5 TB Exadata Flash Cache.

Storage can be added to existing Exadata Database Machines without any downtime, only need to connect the new Exadata storage with spine switch and that’s all. We are ready to configure the new storage.

Datasheet Link  :  exadata-storage-exp-rack-ds-v1-425092.pdf

December 24, 2010

Exp. with Oracle VM

Filed under: Oracle VM — Tags: , — Jagjeet Singh @ 8:23 am

 

It’s more than a year I have been using Oracle VM 2.2.0 for my personal use on intel x86. It’s a perfect solution for testing lab as well. Before using Oracle VM I had
to setup two Boxes with Lacie Storage connected with 2 Firewire cards just for testing the 2 node RAC Setup. It was a costly solution but still worked fine for me.

But I did not have option to test a DG on RAC or a node addition. Oracle VM has given solution of all these problems. You can add as many as machines as your hardware can scale. My motherboard supports 8 GB ram which is sufficient at-least for 4/5 machines (or based on your configuration).Performance is much better than VMware running on your desktop. Along with solution of your lab it gives you exposure of a world-class product which you can expect soon to capture market in Oracle world.

Oracle VM should be installed on bare-metal, below screen-shot tells about the VMs I have configured to play with RAC, DG, Grid 10 & 11i Apps. It does not require any
extra shared drive. It uses OCFS and can be utilized as shared storage between VMs.

I usually configure VNC to get full resolution but VM also has console option

My ISP provides public IP which I have configured to access Oracle VM over internet. Now, on the move I can access all my machines without any problem.
In short – I carry all my set-up with me all the time. Even I can manage VMs through my Iphone using GPRS/Edge

December 23, 2010

Wget option to download patches from Metalink

Filed under: Uncategorized — Tags: , , — Jagjeet Singh @ 8:57 am

Recently I pathced Grid control to 10.2.0.5, while downloading patch from metalink I noticed WGET option to download patch directly on server.

It gives option to download a script or copy to clipboard.

After minor modifications related to password (location/proxy if required ) script is ready to execute.

Started in nohup

Not a bad option for small patches, resumable neither an option nor required.

June 30, 2010

Recover Corrupt OCR with no backup

Filed under: RAC — Tags: , , — Jagjeet Singh @ 7:43 am

While working with OCR backup and recovery scenarios, one question came to my mind what if my OCR gets corrupted and there is no backup available to recover. This is rare but still it happens as Oracle takes backups after every 4 hours. I wanted to explore if there is any other way we can get business back and running without installing whole clusterware which could be a lengthy process.

If you are on 10g R2 and later version then this can be done without re-installing Clusterware ( if you have backup of root.sh or it’s not overwritten by any
subsequent patch ) I simulated this on my test machines (OracleVM).

Current Voting Disk

[root@rac1 ~]# crsctl query css votedisk
 0. 0 /OCFS/VOT

located 1 votedisk(s).

 

Current OCR Files location

[root@rac1 ~]# ocrcheck

Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 4344
Available space (kbytes) : 257800
ID : 601339441

Device/File Name : /OCFS/OCR
Device/File integrity check succeeded

Device/File Name : /OCFS/OCR2
Device/File integrity check succeeded

Cluster registry integrity check succeeded

 

Output of CRS_STAT

[root@rac1 ~]# crs_stat -t

Name Type Target State Host
————————————————————
ora….SM1.asm application ONLINE ONLINE rac1
ora….C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd   application ONLINE ONLINE rac1
ora.rac1.ons   application ONLINE ONLINE rac1
ora.rac1.vip   application ONLINE ONLINE rac1
ora….SM2.asm application ONLINE ONLINE rac2
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd   application ONLINE ONLINE rac2
ora.rac2.ons   application ONLINE ONLINE rac2
ora.rac2.vip   application ONLINE ONLINE rac2
ora.test.AP.cs application ONLINE ONLINE rac1
ora….st1.srv application ONLINE ONLINE rac1
ora.test.db    application ONLINE ONLINE rac2
ora….t1.inst application ONLINE ONLINE rac1
ora….t2.inst application ONLINE ONLINE rac2

I stopped clusterware on both nodes and removed OCR & Voting Disks.

[root@rac1 ~]# ls -lrt /OCFS/*

-rw-r–r– 1 root root 399507456 Jun 29 14:05 /OCFS/OCR2
-rw-r—– 1 root oinstall 10485760 Jun 29 14:05 /OCFS/OCR
-rw-r–r– 1 oracle oinstall 10240000 Jun 29 14:05 /OCFS/VOT

[root@rac1 ~]# rm -fr /OCFS/*

Tried again to start Cluster

[root@rac1 ~]# crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly

 

Clusterware could not startup.

[root@rac1 ~]# crsctl check crs

Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM

Thrown error in /tmp/crsct.* file about OCR

[root@rac1 ~]# cat /tmp/crsc*

OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]

 

Here, I lost all my OCR & Voting disk.

Below procedure can be used for recovery.

1) Execute rootdelete.sh script from All Nodes.
2) Execute rootdeinstall.sh from Primary Node.
3) Run root.sh from Primary node.
4) Run root.sh from all remaining nodes.
5) Execute remaining configurations (ONS,netca,register required resources)

1) Executing rootdelete.sh on all nodes, this script can be found under $ORA_CRS_HOME/install/

[root@rac1 ~]# /u01/app/oracle/product/crs/install/rootdelete.sh
Shutting down Oracle Cluster Ready Services (CRS):
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down…
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in ‘/etc/oracle/scls_scr’

[root@rac2 ~]# /u01/app/oracle/product/crs/install/rootdelete.sh
Shutting down Oracle Cluster Ready Services (CRS):
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down…
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in ‘/etc/oracle/scls_scr’

OCR initialization error can be safely ignored.

2) Execute rootdeinstall.sh on Primary Node, this script can also be found under $ORA_CRS_HOME/install

[root@rac1 ~]# /u01/app/oracle/product/crs/install/rootdeinstall.sh
Removing contents from OCR mirror device
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.031627 seconds, 332 MB/s
Removing contents from OCR device
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.029947 seconds, 350 MB/s

3) Run root.sh on Primary node, this will create VOT & OCR files.

[root@rac1 ~]# $ORA_CRS_HOME/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
“/OCFS/VOT” does not exist. Create it before proceeding.
Make sure that this file is shared across cluster nodes.
1

I had to touch this file to proceed

[root@rac1 ~]# touch /OCFS/VOT
[root@rac1 ~]# $ORA_CRS_HOME/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
assigning default hostname rac1 for node 1.
assigning default hostname rac2 for node 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: rac1 rac1-priv rac1
node 2: rac2 rac2-priv rac2
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Now formatting voting device: /OCFS/VOT
Format of 1 voting devices complete.
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
rac1
CSS is inactive on these nodes.
rac2
Local node checking complete.
Run root.sh on remaining nodes to start CRS daemons.

4) Run root.sh from all remaining nodes.

[root@rac2 crs]# ./root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
assigning default hostname rac1 for node 1.
assigning default hostname rac2 for node 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: rac1 rac1-priv rac1
node 2: rac2 rac2-priv rac2
clscfg: Arguments check out successfully.

NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
rac1
rac2
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps

Creating VIP application resource on (2) nodes…
Creating GSD application resource on (2) nodes…
Creating ONS application resource on (2) nodes…
Starting VIP application resource on (2) nodes…
Starting GSD application resource on (2) nodes…
Starting ONS application resource on (2) nodes…

Done.

Clusterware is up and running

[root@rac2 crs]# crs_stat -t

Name Type Target State Host
————————————————————
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2

5) Remaining Configuration

       a) Configuring Server side ONS

[root@rac1 crs]# $ORA_CRS_HOME/bin/racgons add_config rac1:6200 rac2:6200

         b) Listener Configuration usint netca

you might want to remove listener.ora from both nodes as entries may exist already. Take backup or orignial listener.ora  and use netca to configure &
register with OCR. Till 10g, we can not register listener using srvctl

Renaming orginal listener.ora

[oracle@rac1 ~]$ mv $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora.orig
[oracle@rac1 ~]$ ssh rac2 mv $ORACLE_HOME/network/admin/lstener.ora $ORACLE_HOME/network/admin/listener.ora.orig

          c)Adding ASM, Instance, Database

[oracle@rac1 ~]$ srvctl add asm -i +ASM1 -n rac1 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@rac1 ~]$ srvctl add asm -i +ASM2 -n rac2 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@rac1 ~]$ srvctl add database -d test -o /u01/app/oracle/product/10.2.0/db_1
[oracle@rac1 ~]$ srvctl add instance -d test -i test1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d test -i test2 -n rac2

I restarted both nodes, got everything back. Yes, Services can be re-created.

[oracle@rac1 ~]$ crs_stat -t

Name Type Target State Host
————————————————————
ora….SM1.asm application ONLINE ONLINE rac1
ora….C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd   application ONLINE ONLINE rac1
ora.rac1.ons   application ONLINE ONLINE rac1
ora.rac1.vip   application ONLINE ONLINE rac1
ora….SM2.asm application ONLINE ONLINE rac2
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd   application ONLINE ONLINE rac2
ora.rac2.ons   application ONLINE ONLINE rac2
ora.rac2.vip   application ONLINE ONLINE rac2
ora.test.db    application ONLINE ONLINE rac2
ora….t1.inst application ONLINE ONLINE rac1
ora….t2.inst application ONLINE ONLINE rac2

Note : That’s why it’s recommended to take backup of root.sh after fresh install. Subsequent patches can
overwrite root.sh script.

This is described on Metalink Note: 399482.1

April 26, 2010

ASM Migration Project

Filed under: ASM — Tags: — Jagjeet Singh @ 5:00 am


For the last month I have been working on ASM migration project for one of our client in United States.


lient wanted to move couple of environments (including Production, QA, and Dev/Patch) to ASM, critical production environment running EBusiness Suit 11.5.10.2 on raw devices utilizing 5.1 TB storage, other instances are utilizing cooked file systems.


I really enjoy working with ASM, the day it was released I got passionate about this feature and have been playing in my lab.  My manager considered my interest with ASM and assigned me to this project (thanks to him). I am very much excited for this project, let’s see how this goes.


This project includes OATM (Oracle Application Tablespace Model) conversion, re-write solution for Cluster Failover (HP Service Guard is configured for Cluster Failover), DR is implemented using EMC Mirror View, BCV & backup methodology.


I would like share my experience after first migration to ASM. This migration was done using fndtsmig.pl (Oracle given utility to migrate segments). This utility migrate (move/rebuild) segments based on pre-defined rules to choose appropriate tablespaces.


We just finished with our first Iteration, I was more interested to see equal distribution of I/O across the Luns.  I pulled below report from 10g grid control for %usage of one diskgroup.

Disk Usage %


It shows well balanced equal usage distribution (it’s awesome, isn’t it),


let’s have a look for IO usage



As soon as I saw this page, I noticed 3 things.


1-      IO distributions are quit close/equal for all the Luns of DGOAPSFC. Other columns Total Read Calls & Total write Calls are almost equal for all the Luns. That is why I like ASM for this capability.


2-      BUT this is not the case with DGOLTPFC diskgroup , this is really strange that 4 disks out of 10 Luns got 40% more high I/O. 


3-      I noticed there is huge difference in IO response time for 2 disks in DGOLTPFC


Point no. 1 is quite obvious and expected, for second point I tried to find the cause but still this is an open issue for me. I am working on this and would post more if I could get something.


For 3rd point, I immediately made call to my storage administrator and asked for the same if something wired at storage end for specifically for 2 disks as there is huge difference in IO response time. Other disks avg. response time is near 5.5 ms where as LUN 43 and LUN 46 it’s more 9 ms.


Storage guy asked me to give some time,  after some time he told me that because of space constraint he could not give me all FC disks, those 2 disks are SATA. J


For final implementation we would be provided FC and SSD disks only so we started using descriptive naming convention.


Again, I am thrilled to work in this project and would share my experience.

December 13, 2009

How to convert RAC Enabled binaries for Non-RAC usage

Filed under: Uncategorized — Tags: , , — Jagjeet Singh @ 7:46 am


I have been using Oracle on Virtualized Environment, hence it’s very frequent for me to build a new virtual machine to test out new features.


I had to copy Oracle 10g binaries from one of my RAC instance to a Non-RAC Server. Normal practice I follow is to copy  the Oracle binaries and relink again with “relink all” option. if required, we can attach copied oracle home with central  inventory using “./runInstaller -silent -attachHome” option.

After copying and relinking, when I tried to start my instance I got below error.

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Oct 17 16:30:46 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORA-29702: error occurred in Cluster Group Service operation

SQL> ho oerr ora 29702

29702, 00000, “error occurred in Cluster Group Service operation”
// *Cause: An unexpected error occurred while performing a CGS operation.
// *Action: Verify that the LMON process is still active. Also,
// check the Oracle LMON trace files for errors.


This error message indicates that it can’t start the instance since CRS is not available. I realized this is because of RAC-Enabled binaries.

Below option can be used to check if software binaries are RAC-Enabled or not. Output as 1 indicate RAC-Enabled binaries.

[oracle@js lib]$ nm -r $ORACLE_HOME/rdbms/lib/libknlopt.a | grep -c kcsm.o
1

To turn off RAC


$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_off
# Relink Oracle
$ make -f ins_rdbms.mk ioracle

[oracle@js lib]$ make -f ins_rdbms.mk rac_off

rm -f /u01/app/oracle/product/10g/lib/libskgxp10.so
cp /u01/app/oracle/product/10g/lib//libskgxpd.so /u01/app/oracle/product/10g/lib/libskgxp10.so
rm -f /u01/app/oracle/product/10g/lib/libskgxn2.so
cp /u01/app/oracle/product/10g/lib//libskgxns.so \
/u01/app/oracle/product/10g/lib/libskgxn2.so
/usr/bin/ar d /u01/app/oracle/product/10g/rdbms/lib/libknlopt.a kcsm.o
/usr/bin/ar cr /u01/app/oracle/product/10g/rdbms/lib/libknlopt.a /u01/app/oracle/product/10g/rdbms/lib/ksnkcs.o


[oracle@js lib]$ make -f ins_rdbms.mk ioracle
chmod 755 /u01/app/oracle/product/10g/bin
- Linking Oracle
………


Now it’s returning 0 which indicates it’s not RAC-Enabled binaries.

oracle@js lib]$ nm -r libknlopt.a | grep -c kcsm.o
0


There could be another situation where because of network problem ( or any other .. ) you are not able to start-up the CRS (this does happen !!).  Your first priority is to make database up & available for critical applications irrespective of it’s mode (RAC or Non-RAC).

This option can be used to make database up quickly. you also need to set Cluster_Database=false in parameter file.

To turn On RAC

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
# Relink Oracle
$ make -f ins_rdbms.mk ioracle

October 3, 2009

How to identify tracefile in 11g

Filed under: Oracle 11g — Tags: , — Jagjeet Singh @ 11:36 am


It’s quite often that DBA needs to enable tracing for a particular session to diagnose the performance or any other issue, which create trace file(s) in user_dump_directory. Usually this directory contains hundreds of files on a medium/large user base system, DBA needs to identify the OS process id to identify the trace file.


Naming Convention of user trace file is <<instance_name>>_ora_<<process id>>.trc under user_dump_directory. Oracle also provide option to use tracefile_identifire which can be utilized to make file name unique to identify easily.


Tracefile identifire will be appended in the name of file.In 11g, It has become very easy to identify the trace file directly from v$process view. Column named “Tracefile” has been added to this view.


SQL> select tracefile from v$process join v$session on (addr=paddr)
2 where sid = 76;

TRACEFILE
——————————————————————-
/u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817.trc

SID 76 is my session id.

After setting Tracefile Identifier.

SQL> alter session set tracefile_identifier='JS';

Session altered.

SQL> select tracefile from v$process join v$session on (addr=paddr)
2 where sys_context(‘userenv’,’sessionid’) = audsid
3 /

TRACEFILE
——————————————————————-
/u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc


Note – I have removed SID and using sys_context… function to identify the current session. SID is useful to identify end-user’s sessions.


This column will always be populated with name of trace file even there has been no file generated.

SQL> ! ls -l /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc
ls: /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc: No such file or directory


After enabling trace.

SQL> alter session set sql_trace=true;

Session altered.

SQL> !ls -l /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc
-rw-r----- 1 oracle dba 1101 Oct 3 07:10 /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc


before 11g, below query can be utilized to identify the trace file.


SQL> select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid
|| nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File"
from V$PARAMETER u_dump
cross join V$PARAMETER instance
cross join V$PROCESS
join V$SESSION on v$process.addr = V$SESSION.paddr
where u_dump.name = 'user_dump_dest'
and instance.name = 'instance_name'
and V$SESSION.audsid=sys_context('userenv','sessionid');

Older Posts »

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 41 other followers