Oakies Blog Aggregator

A reader of this blog, VijayS, asked me to share the script I use to demo ITL deadlocks that I mentioned in this comment. Since other readers might be interested, here is the script.

SET TERMOUT ON FEEDBACK ON VERIFY OFF SCAN ON ECHO ON

@connect

SELECT * FROM v$version WHERE rownum = 1;

REM
REM cleanup
REM

DROP TABLE t PURGE;

PAUSE

REM
REM create a test table with three completely full data blocks
REM (default tablespace is expected to have 8KB blocks)
REM

CREATE TABLE t (n NUMBER, c VARCHAR2(4000)) PCTFREE 0;

INSERT INTO t SELECT rownum, rpad('*',2679,'*') FROM dual CONNECT BY level <= 9;
COMMIT;

SELECT dbms_rowid.rowid_block_number(rowid), n
FROM t
ORDER BY n;

PAUSE

REM
REM in this session modify two rows: one in the first block and one in the
REM third block
REM

SET SQLPROMPT " 1 > "

SELECT sid FROM v$session WHERE audsid = userenv('sessionid');

UPDATE t SET n=n*10 WHERE n IN (1,7);

PAUSE

REM
REM in another session (called session #2) modify two rows: one in the first
REM block and one in the second block
REM
REM @connect
REM SET SQLPROMPT " 2 > "
REM
REM SELECT sid FROM v$session WHERE audsid = userenv('sessionid');
REM UPDATE t SET n=n*10 WHERE n IN (2,5);
REM

PAUSE

REM
REM in another session (called session #3) try to modify one row per block;
REM since all ITL slots of the first block are taken, wait...
REM
REM @connect
REM SET SQLPROMPT " 3 > "
REM
REM SELECT sid FROM v$session WHERE audsid = userenv('sessionid');
REM UPDATE t SET n=n*10 WHERE n = 9;
REM UPDATE t SET n=n*10 WHERE n = 6;
REM UPDATE t SET n=n*10 WHERE n = 3;
REM

PAUSE

REM
REM in this session try to modify one row in the second block and, in addition,
REM in session #2, try to modify one row in the third block; since all ITL slots
REM of the second and third block are taken, wait..., deadlock...
REM

REM run "UPDATE t SET n=n*10 WHERE n = 8;" in session #2

UPDATE t SET n=n*10 WHERE n = 4;

Notice that:

• the remarks contained in the script provide important information for understanding what the script does and, as a result, for being able to produce a ITL deadlock
• to run the script three sessions are required (when running the script I copy/paste the commands from the output of the terminal running the script to the other two terminals)

To give you an example of what it looks like when you run it, here is a recording:

The deadlock graph generated by the run that I recorded is the following one. Notice that the rows waited on are set to “no row”.

                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090013-00001b44        25     140     X             23     147           S
TX-0007001d-000019a9        23     147     X             25     140           S

session 140: DID 0001-0019-00000D23     session 147: DID 0001-0017-000004BC
session 147: DID 0001-0017-000004BC     session 140: DID 0001-0019-00000D23

Rows waited on:
  Session 140: no row
  Session 147: no row

Following on from last weeks book competition, the kind people at Packt have offered another prize. This time 2 e-book copies of
Oracle E-Business Suite R12 Integration and OA Framework Development and Extension Cookbook by Andy Penver.

The competition will run for about two weeks, so I’ll be selecting the winners on 10th May 2013. To enter, comment on this post, telling me why you think you need this book and what you hope to gain from it. The two lucky winners will be selected based on their comments on this post (not Facebook, Google+ or Twitter), so try and be creative or you won’t stand out from the crowd. The winners will be contacted via email, so please use your real email address when posting your comment.

You can read more about the contents of the book and the author on the Packt website, but here is a little marketing message about the book.

“Oracle E-Business Suite R12 Integration and OA Framework Development and Extension Cookbook is an
extensive guide for both beginners and intermediate users that describes how to develop extensions in Oracle
E-Business Suite. The book also discusses some of Oracle E-Business Suite’s vital features, such as creating and
distributing BI Publisher template files to the file system, authoring, managing, delivering highly-formatted
documents using BI Publisher and configuring a browser.

This is a practical hands-on guide, designed to demonstrate the newest features in Oracle E-Business Suite
Release 12. With an in-depth knowledge of personalizing OA Framework pages in Oracle E-Business Suite,
readers will not only be able to install JDeveloper with OA Extension and set up a development environment,
but also deploy an OA Framework page within Oracle E-Business Suite Release 12.”

Good Luck!

Cheers

Tim…

PS. Connor, death threats will not work this time.

---

Win A free copy of the Oracle E-Business Suite R12 Integration and OA Framework Development and Extension Cookbook was first posted on April 30, 2013 at 10:59 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

 

Delphix has been selected as one of Gartner’s Cool Vendors in Storage for 2013!

Gartner uses the Cool Vendor awards to highlight innovative and disruptive new companies that are rapidly changing how enterprises do business.

Delphix 2013 HIGHLIGHTS TO DATE

Delphix recently engaged with Oracle guru Jonathan Lewis who spent a week putting Delphix through its paces and responding to community-driven requests / questions. A recording of the first webinar is available, informally answering the top community questions.

 

Many organizations have adopted agile development processes to accelerate their application projects. However, the database underlying these applications is often a major obstacle to success with agile development.  Register for this live webinar on May 8 to listen to the 451 Research and Delphix discuss the role of databases in agile development .

 

Delphix completed a performance driven test with IBM, focusing on how to architect a high performance environment for virtual environments. The results may surprise you, as Delphix powered virtual databases performed over 500% faster in our joint tests.

 

The number of ways Delphix customers continue to leverage Delphix is impressive. Delphix recently worked with customers who were using Delphix to dramatically reduce their load on production databases and their SAN. These discussions lead our professional services team to document Delphix’s impact on production from the data we’ve jointly collected with our customers.

 

Delphix is now a SAP Endorsed Business Solution. Delphix’s data virtualization technologytransforms the economics of data and application management, allowing accelerated deployments of SAP® solutions with reduced total cost of ownership (TCO) and effort.

 

 

Some days are just too good to be true :) I ran into an interesting problem trying to install 11.2.0.3.0 Grid Infrastructure for a two node cluster. The storage was presented via iSCSI which turned out to be a blessing and inspiration for this blog post. So far I haven’t found out yet how to create “shareable” LUNs in KVM the same way I did successfully with Xen. I wouldn’t recommend general purpose iSCSI for anything besides lab setups though. If you want network based storage, go and use 10GBit/s Ethernet and either use FCoE or (direct) NFS.

Here is my setup. Storage is presented in 3 targets using tgtd on the host:

1. Target 1 contains 3×2 GB LUNs for OCR and voting disks in normal redundancy.
2. Target 2 contains 3×10 GB LUNs for +DATA
3. Target 2 contains 3×10 GB LUNs for +RECO

iSCSI initiators are Oracle Linux 6.4 on KVM with the host running OpenSuSE 12.3 providing the iSCSI targets. Yes, I know I’m probably the only Oracle DBA running SuSE, but to my defence I have a similar system with Oracle Linux 6.4 throughout and both work.

So besides the weird host OS there is nothing special. Since I’m lazy sometimes and don’t particularly like udev I decided to use ASMLib for device name persistence on the iSCSI LUNs. This turned out to be crucial, otherwise I’d never had written this post.

So much for the introduction

And here’s the problem. While installing Grid Infrastructure OUI allowed me to fill out all wizard interfaces and proceeded to install the binaries on all hosts. If you have installed RAC before that’s not the interesting part of the installation. It gets far more interesting when you run root.sh! Normally root.sh simply completes if you paid attention to the pre-requisites. I _have_ paid attention to them yet still the script failed on node 1! I don’t have the exact output on screen any more but the script bailed out trying to create the voting files.

Whenever something goes wrong with the installation of Grid Infrastructure you can turn to $GRID_HOME/cfgtoollogs/crsconfig/rootcrs_$(hostname).log. The relevant section in the file contained this:

2013-04-23 14:11:12: Creating voting files
2013-04-23 14:11:12: Creating voting files in ASM diskgroup OCR
2013-04-23 14:11:12: Executing crsctl replace votedisk '+OCR'
2013-04-23 14:11:12: Executing /u01/app/11.2.0/grid/bin/crsctl replace votedisk '+OCR'
2013-04-23 14:11:12: Executing cmd: /u01/app/11.2.0/grid/bin/crsctl replace votedisk '+OCR'
2013-04-23 14:11:12: Command output:
>  Failed to create voting files on disk group OCR.
>  Change to configuration failed, but was successfully rolled back.
>  CRS-4000: Command Replace failed, or completed with errors.
>End Command output
2013-04-23 14:11:12: Voting file add failed

Ooops. Not good-why would that fail? The ASM instance was up, the OCR has already been created. Most Clusterware commands leave a trace in $GRID_HOME/log/$(hostname)/client. I checked the last file in there but it didn’t help much:

2013-04-23 15:07:49.079: [  CRSCTL][3108575008]crsctl_format_diskgroup: diskgroup OCR creation with status 1. Please check the alert log file for ASM
2013-04-23 15:07:49.082: [ CSSCLNT][3108575008]clsssConfigLockTrace: === clsssConfigUnlock ===
2013-04-23 15:07:49.082: [ CSSCLNT][3108575008]clsssConfigLockTrace: lock(0x1cb70d0), version(1), size(1088)
2013-04-23 15:07:49.082: [ CSSCLNT][3108575008]clsssConfigLockTrace: offsets(24), activever(186647296)
2013-04-23 15:07:49.082: [ CSSCLNT][3108575008]clsssConfigLockTrace: id(14), instantiation(12), incarn(1)
2013-04-23 15:07:49.082: [ CSSCLNT][3108575008]clsssConfigLockTrace: mapoff(28), configoff(548), mapsize(512)
2013-04-23 15:07:49.082: [ CSSCLNT][3108575008]clsssConfigLockTrace: state(0), holders(0), waiters(0)
2013-04-23 15:07:49.082: [ CSSCLNT][3108575008]clsssConfigLockTrace: committimestamp(0), commitstate(0)
2013-04-23 15:07:49.082: [ CSSCLNT][3108575008]clsssConfigLockTrace: === Map (of first 7 entries) ===
2013-04-23 15:07:49.082: [ CSSCLNT][3108575008]clsssConfigLockTrace: 000 000 000 000 000 000 000

Except for line 1-that is a very obvious pointer. Maybe there is something in the ASM instance’s alert.log?

2013-04-23 15:07:48.733000 +01:00
NOTE: [crsctl.bin@rac11gr2node1.example.com (TNS V1-V3) 13092] opening OCR file
NOTE: updated gpnp profile ASM diskstring:
NOTE: Creating voting files in diskgroup OCR
NOTE: Voting File refresh pending for group 1/0x20cf9032 (OCR)
NOTE: Attempting voting file creation in diskgroup OCR
ERROR: Could not create voting files. It spans across 161 AUs (max supported is 64 AUs)
ERROR: Voting file allocation failed for group OCR
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_13100.trc:
ORA-15303: Voting files could not be created in diskgroup OCR due to small Allocation Unit size
2013-04-23 15:07:51.278000 +01:00
NOTE: Attempting voting file refresh on diskgroup OCR

That was it! But what’s so special about the disk group? Something started to dawn on me…I have recently spent quite some time on 4k sector size disks and their implications. And my hard disks have recently been replaced … here’s what I got from ASM:

SQL> select name,sector_size from v$asm_diskgroup;

NAME                           SECTOR_SIZE
------------------------------ -----------
OCR                                   4096

So sure enough, my disk group uses 4k sectors, even though at no point I asked it to do so. In fact up to today I struggled to create a disk group with a 4k sector size for lack of supporting hardware! So this is the first time I see those, my hard disks in the lab server must be pretty new then. There are many ways to check for the block size of your LUN, this time I chose fdisk (this only works for LUNs that use the MBR format, if the LUN has been initialised with a GPT you need to install parted instead)

[root@rac11gr2node1 client]# fdisk -lu /dev/sda

Disk /dev/sda: 2147 MB, 2147483648 bytes
67 heads, 62 sectors/track, 1009 cylinders, total 4194304 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 4096 bytes
Disk identifier: 0x00000000

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1          531712     4191385     1829837   83  Linux
[root@rac11gr2node1 client]#

Notice this line:

Sector size (logical/physical): 512 bytes / 4096 bytes

And there exactly lies the problem. ASMLib in my version cannot deal with a 4k sector size properly it seems. The problem is known, just search for ORA-15303 in My Oracle Support. It seems particular to LUNs/disks with different logical and physical block sizes though-refer to the MOS note for more information. For your reference, here are my versions:

[oracle@rac11gr2node1 ~]$ rpm -qa| grep oracleasm
oracleasmlib-2.0.4-1.el6.x86_64
oracleasm-support-2.1.8-1.el6.x86_64
[oracle@rac11gr2node1 ~]$

The strange thing is that the ASM instance with the single disk group has been created successfully, as shown by the log (note the disk list in line 1):

2013-04-23 14:10:54: Executing as oracle: /u01/app/11.2.0/grid/bin/asmca -silent -diskGroupName OCR -diskList 'ORCL:OCR1,ORCL:OCR2,ORCL:O
CR3' -redundancy NORMAL -configureLocalASM -au_size 1
2013-04-23 14:10:54: Running as user oracle: /u01/app/11.2.0/grid/bin/asmca -silent -diskGroupName OCR -diskList 'ORCL:OCR1,ORCL:OCR2,ORCL:OCR3' -redundancy NORMAL -configureLocalASM -au_size 1
2013-04-23 14:10:54:   Invoking "/u01/app/11.2.0/grid/bin/asmca -silent -diskGroupName OCR -diskList 'ORCL:OCR1,ORCL:OCR2,ORCL:OCR3' -redundancy NORMAL -configureLocalASM -au_size 1" as user "oracle"
2013-04-23 14:10:54: Executing /bin/su oracle -c "/u01/app/11.2.0/grid/bin/asmca -silent -diskGroupName OCR -diskList 'ORCL:OCR1,ORCL:OCR2,ORCL:OCR3' -redundancy NORMAL -configureLocalASM -au_size 1"
2013-04-23 14:10:54: Executing cmd: /bin/su oracle -c "/u01/app/11.2.0/grid/bin/asmca -silent -diskGroupName OCR -diskList 'ORCL:OCR1,ORCL:OCR2,ORCL:OCR3' -redundancy NORMAL -configureLocalASM -au_size 1"
2013-04-23 14:11:09: Command output:
>
>  ASM created and started successfully.
>
>  Disk Group OCR created successfully.
>
>End Command output

After spending a little while thinking what to do I decided to bite the bullet and start from scratch. The deinstall scripts again worked really well in my case.

Instead of ASMLib I used udev for the second attempt. The trick with udev is to find something to map. The scsi_id command for example is a great help in determining disk attributes, but there are others too. In my case, all I wanted to achieve is to change permissions of the iSCSI disks to oracle:dba and 0660. In my lab environment I didn't use multiple paths to the iSCSI target, and I didn't care about symlinks. You get it, this isn't 100% realistic ... Note that In KVM disks are named vd*, like /dev/vda for the first one etc. if you are using virtio, the para-virtualised drivers. The iSCSI disks ended up being called /dev/sd* which makes it easy to define an asm_diskstring.
[root@rac11gr2node1 sys]# /sbin/scsi_id --whitelisted --replace-whitespace --page=0x80 --device=/dev/sdg --export
ID_SCSI=1
ID_VENDOR=IET
ID_VENDOR_ENC=IET\x20\x20\x20\x20\x20
ID_MODEL=VIRTUAL-DISK
ID_MODEL_ENC=VIRTUAL-DISK
ID_REVISION=0001
ID_TYPE=disk
ID_SERIAL_RAW="SIET     VIRTUAL-DISK"
ID_SERIAL=SIET_VIRTUAL-DISK
ID_SERIAL_SHORT=
So using the vendor string and the model attribute from SYSFS will most likely work. Please note that this is a rather simplistic model and wouldn't necessarily work with Fibre Channel attached disks or multipathing. The resulting udev rule in /etc/udev/rules/99-asm.rules was:
KERNEL=="sd[a-z]*", BUS=="scsi", SYSFS{vendor}=="IET", SYSFS{model}=="VIRTUAL-DISK", OWNER="oracle", GROUP="dba" MODE="0660"
I also deconfigured ASMLib on all nodes just to be sure:
[root@rac11gr2node1 u01]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface [oracle]:
Default group to own the driver interface [dba]:
Start Oracle ASM library driver on boot (y/n) [y]: n
Scan for Oracle ASM disks on boot (y/n) [y]: n
Writing Oracle ASM library driver configuration: done
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]
[root@rac11gr2node1 u01]#
Running udevadm trigger loaded the new rules and indeed, the /dev/sd*1 devices now were owned by oracle:dba.
Then I ran through OUI again and wanted to assign the previously used disks again, but they didn't appear as candidates :( OK so the deconfig didn't zero out the disk headers. Not a problem, oracleasm has a "deletedisk" command that can be used for this exact purpose. You just need to be really sure it's the correct disk you are zeroing out, otherwise someone else will surely complain (you have been warned!).
The rest was simple. The ASM instance has again been created successfully, note the difference in disk names this time (my asm_diskstring was set to /dev/sd*1):
2013-04-23 16:29:29: Executing as oracle: /u01/app/11.2.0/grid/bin/asmca -silent -diskGroupName OCR -diskList '/dev/sda1,/dev/sdb1,/dev/sdc1' -redundancy NORMAL -diskString '/dev/sd*1' -configureLocalASM -au_size 1
2013-04-23 16:29:29: Running as user oracle: /u01/app/11.2.0/grid/bin/asmca -silent -diskGroupName OCR -diskList '/dev/sda1,/dev/sdb1,/dev/sdc1' -redundancy NORMAL -diskString '/dev/sd*1' -configureLocalASM -au_size 1
2013-04-23 16:29:29:   Invoking "/u01/app/11.2.0/grid/bin/asmca -silent -diskGroupName OCR -diskList '/dev/sda1,/dev/sdb1,/dev/sdc1' -redundancy NORMAL -diskString '/dev/sd*1' -configureLocalASM -au_size 1" as user "oracle"
2013-04-23 16:29:29: Executing /bin/su oracle -c "/u01/app/11.2.0/grid/bin/asmca -silent -diskGroupName OCR -diskList '/dev/sda1,/dev/sdb1,/dev/sdc1' -redundancy NORMAL -diskString '/dev/sd*1' -configureLocalASM -au_size 1"
2013-04-23 16:29:29: Executing cmd: /bin/su oracle -c "/u01/app/11.2.0/grid/bin/asmca -silent -diskGroupName OCR -diskList '/dev/sda1,/dev/sdb1,/dev/sdc1' -redundancy NORMAL -diskString '/dev/sd*1' -configureLocalASM -au_size 1"
And this time the voting disks were created as well:
CRS-2676: Start of 'ora.diskmon' on 'rac11gr2node2' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac11gr2node2' succeeded

ASM created and started successfully.

Disk Group OCR created successfully.

clscfg: -install mode specified
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4256: Updating the profile
Successful addition of voting disk 9d9a9577a66a4f6fbf267b125dc7f4a3.
Successful addition of voting disk 26e5ff2544084ff0bfb9e753b090ec22.
Successful addition of voting disk 38a820cd4b424f4ebf514370168fa499.
Successfully replaced voting disk group with +OCR.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   9d9a9577a66a4f6fbf267b125dc7f4a3 (/dev/sda1) [OCR]
 2. ONLINE   26e5ff2544084ff0bfb9e753b090ec22 (/dev/sdb1) [OCR]
 3. ONLINE   38a820cd4b424f4ebf514370168fa499 (/dev/sdc1) [OCR]
Located 3 voting disk(s).
Very nice-job done. You should refer back to the relevant MOS note to read more about the problem but for now be advised that 4k sector sizes can cause surprises.