CRSCTL CheatSheet

You can find below various commands which can be used to administer Oracle Clusterware using crsctl. This is for purpose of easy reference.

Start Oracle Clusterware

#crsctl start crs

Stop Oracle Clusterware

#crsctl stop crs

Enable Oracle Clusterware

#crsctl enable crs

It enables automatic startup of Clusterware daemons

Disable Oracle Clusterware

#crsctl disable crs

It disables automatic startup of Clusterware daemons. This is useful when you are performing some
operations like OS patching and does not want clusterware to start the daemons automatically.

Checking Voting disk Location

$crsctl query css votedisk

0. 0 /dev/sda3
1. 0 /dev/sda5
2. 0 /dev/sda6
Located 3 voting disk(s).

Note: -Any command which just needs to query information can be run using oracle user. But anything which alters Oracle Clusterware requires root privileges.

Add Voting disk

#crsctl add css votedisk path

Remove Voting disk

#crsctl delete css votedisk path

Check CRS Status

$crsctl check crs

Cluster Synchronization Services appears healthy

Cluster Ready Services appears healthy

Event Manager appears healthy

You can also see particular daemon status

$crsctl check cssd

Cluster Synchronization Services appears healthy

$crsctl check crsd

Cluster Ready Services appears healthy

$crsctl check evmd

Event Manager appears healthy

You can also check Clusterware status on both the nodes using

$crsctl check cluster

prod01 ONLINE

prod02 ONLINE

Checking Oracle Clusterware Version

To determine software version (binary version of the software on a particular cluster node) use

$crsctl query crs softwareversion

Oracle Clusterware version on node [prod01] is [11.1.0.6.0]

For checking active version on cluster, use

$ crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.1.0.6.0]

As per documentation, multiple versions are used while upgrading.

There are other options for CRSCTL too which can be seen using

$crsctl

Or

$crsctl help

11.2 Reference

11.2 introduced few changes to crsctl usage. Most important is clusterized commands which allows you to perform remote operations. They are

  • crsctl check cluster
  • crsctl start cluster
  • crsctl stop cluster

All these commands allow following usage

Default Stop local server
-all Stop all servers
-n Stop named servers
server […] One or more blank-separated server names
-f Force option

Let’s see usage

% crsctl check cluster -all
**************************************************************
prod01:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
prod02:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

crsctl pin css is used to associate node name with node number. i.e if olsnodes shows prod01 as 1, then it should persist. This is helpful if you intend to run pre 11.2 database

#crsctl pin css -n prod01
#crsctl pin css -n prod02

To check daemon status, following commands need to be used

Check crsd – crsctl check crs

Check cssd – crsctl check crs

check evmd – crsctl check evm

crs_unregister is replaced by crsctl delete resource <resource_name>

crs_stat has been deprecated (though still works) and you need to use

$crsctl stat res -t
e.g

--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       prod01
               ONLINE  ONLINE       prod02
ora.FLASH.dg
               ONLINE  ONLINE       prod01
               ONLINE  ONLINE       prod02
ora.LISTENER.lsnr
               ONLINE  ONLINE       prod01
               ONLINE  ONLINE       prod02
ora.asm
               ONLINE  ONLINE       prod01               Started
               ONLINE  ONLINE       prod02               Started
ora.gsd
               OFFLINE OFFLINE      prod01
               OFFLINE OFFLINE      prod02
ora.net1.network
               ONLINE  ONLINE       prod01
               ONLINE  ONLINE       prod02
ora.ons
               ONLINE  ONLINE       prod01
               ONLINE  ONLINE       prod02
ora.registry.acfs
               ONLINE  ONLINE       prod01
               ONLINE  ONLINE       prod02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       prod01
ora.cvu
      1        ONLINE  ONLINE       prod01
ora.oc4j
      1        ONLINE  ONLINE       prod01
ora.prod01.vip
      1        ONLINE  ONLINE       prod01
ora.prod02.vip
      1        ONLINE  ONLINE       prod02
ora.scan1.vip
      1        ONLINE  ONLINE       prod01
ora.tintin.db
      1        ONLINE  ONLINE       prod01               Open
      2        ONLINE  ONLINE       prod02               Open

Wrote following awk command to give output on one line

crsctl status res |grep -v "^$"|awk -F "=" 'BEGIN {print " "} {printf("%s",NR%4 ? $2"|" : $2"\n")}'|sed -e 's/  *, /,/g' -e 's/, /,/g'|\
awk -F "|" 'BEGIN { printf "%-40s%-35s%-20s%-50s\n","Resource Name","Resource Type","Target ","State" }{ split ($3,trg,",") split ($4,st,",")}{for (i in trg) {printf "%-40s%-35s%-20s%-50s\n",$1,$2,trg[i],st[i]}}'

output

Resource Name                           Resource Type                      Target              State                                             

ora.DATA.dg                             ora.diskgroup.type                 ONLINE              ONLINE on prod01                              
ora.DATA.dg                             ora.diskgroup.type                 ONLINE              ONLINE on prod02                              
ora.FLASH.dg                            ora.diskgroup.type                 ONLINE              ONLINE on prod01                              
ora.FLASH.dg                            ora.diskgroup.type                 ONLINE              ONLINE on prod02                              
ora.LISTENER.lsnr                       ora.listener.type                  ONLINE              ONLINE on prod01                              
ora.LISTENER.lsnr                       ora.listener.type                  ONLINE              ONLINE on prod02                              
ora.LISTENER_SCAN1.lsnr                 ora.scan_listener.type             ONLINE              ONLINE on prod01                              
ora.asm                                 ora.asm.type                       ONLINE              ONLINE on prod01                              
ora.asm                                 ora.asm.type                       ONLINE              ONLINE on prod02                              
ora.cvu                                 ora.cvu.type                       ONLINE              ONLINE on prod01                              
ora.gsd                                 ora.gsd.type                       OFFLINE             OFFLINE                                           
ora.gsd                                 ora.gsd.type                       OFFLINE             OFFLINE                                           
ora.net1.network                        ora.network.type                   ONLINE              ONLINE on prod01                              
ora.net1.network                        ora.network.type                   ONLINE              ONLINE on prod02                              
ora.oc4j                                ora.oc4j.type                      ONLINE              ONLINE on prod01                              
ora.ons                                 ora.ons.type                       ONLINE              ONLINE on prod01                              
ora.ons                                 ora.ons.type                       ONLINE              ONLINE on prod02                              
ora.prod01.vip                      ora.cluster_vip_net1.type          ONLINE              ONLINE on prod01                              
ora.prod02.vip                      ora.cluster_vip_net1.type          ONLINE              ONLINE on prod02                              
ora.registry.acfs                       ora.registry.acfs.type             ONLINE              ONLINE on prod01                              
ora.registry.acfs                       ora.registry.acfs.type             ONLINE              ONLINE on prod02                              
ora.scan1.vip                           ora.scan_vip.type                  ONLINE              ONLINE on prod01                              
ora.snowy.db                            ora.database.type                  OFFLINE             OFFLINE                                           
ora.snowy.db                            ora.database.type                  ONLINE              OFFLINE                                           
ora.tintin.db                           ora.database.type                  ONLINE              ONLINE on prod01                              
ora.tintin.db                           ora.database.type                  ONLINE              ONLINE on prod02                              
ora.tintin.tintin_db_svc.svc            ora.service.type                   ONLINE              ONLINE on prod02                              
ora.tintin.tintin_ggate_svc.svc         ora.service.type                   ONLINE              ONLINE on prod01
21 May, 2008 by 10g 11g 2

ORA-00600 [12333]

ORA-00600 with [12333] is an error that I have encountered on multiple occasions. This post is to shed some light on what this error is all about.

To start with let us try to understand when does oracle report ora-00600 error. An ora-00600 error is raised in the exception handler section of Oracle’s c-program code. i.e. Coders have forseen certain exceptional situation that are potential threats to data integrity or memory integrity and written appropriate exceptional handlers to report a ora-00600 error with appropriate information about the exception condition.

Details like [12333] indicate or provide hints as to what caused the exception. ora-00600 [12333] is reported when the server recieves data from a client and the server cannot recognize the data format. This error mostly is because of issues with network and does not indicate any data corruption.

Few Common Causes & Suggestions:
1. An incompatible client software can cause such an internal error. Metalink document 207303.1 explains the supported combinations of clients and Server release.

2. An incompatible NLS settings on Client. Check NLS settings especially ORA_NLS33 for 9i, ORA_NLS10 for 10g.

3. Apart from this, it could be because of network issues, client software, TIMEOUT setting, etc. Try to narrow down the error to a particular client machine/ client software.

4. This can be because of an Oracle bug also. Refer to metalink document 428629.1 for list of known bugs with ora-00600 [12333].

As a last resort you can check this with Oracle Support for any new issues.

20 May, 2008 by 10g 0

Allocation Unit and Extents In ASM

ASM was introduced with Oracle 10g and is used as a Volume Manager and a file system. It provides both mirroring and striping of the database files. To use ASM you need to create a Diskgroup and add disks/raw devices to the Diskgroup.Data is allocated in disks in terms of Extents. As per documentation

Every ASM disk is divided into allocation units (AU). An AU is the fundamental unit of allocation within a disk group. A file extent consists of one or more AU. An ASM file consists of one or more file extents.

In case of 10g,ASM divides files into 1 MB extents/AU’s and spreads each file’s extents evenly across all disks in the disk group.

Starting from 11g, we can create Diskgroups of varying AU sizes ranging from 1 MB to 64 MB in powers of two, such as, 1, 2, 4, 8, 16, 32, or 64. Each Allocation Unit consumes some amount of memory in ASM SGA for storing the metadata information. Very Large Databases (VLDB) which can have sizes in TeraBytes will have too many AU’s allocated and also memory requirement for ASM instance will also increase. This will also impact the performance of the application. As a result Variable Size extents concept has been introduced in 11g.

Extents and Allocation Units

Number of Extents Size
0 – 19999 1*AU
20000 – 39999 8*AU
40000 – 59999 64*AU

As shown in table, for first 20000 extents Extent size is always equal to AU. This keeps on increasing gradually.

The ASM coarse striping is always equal to the disk group AU size, but fine striping size always remains 128KB in any configuration . The AU size is determined at creation time with the allocation unit size (AU_SIZE>) disk group attribute. The values can be 1, 2, 4, 8, 16, 32, and 64 MB.

 CREATE DISKGROUP DATA1 EXTERNAL REDUNDANCY DISK '/dev/sda1' ATTRIBUTE 'au_size'='10M';

You can query ALLOCATION_UNIT_SIZE column in V$ASM_DISKGROUP.

Now all this is documented and if I do not mention anything else you will be angry at me 🙂

Now we know this is implemented from 11g. But does that mean it is not available in 10g??

No. It is available in 10g, but we need to play around with some Hidden Parameters.

Warning – You should try this out in Development Environment before trying in Production database. Also contact Oracle Support to validate if this can be implemented in your system and ensure that there are no reported issues/bugs.

<span style="color: #000099;"><span style="color: #3333ff;">SQL&gt; select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and nam.ksppinm like '%asm%';</span></span>

<span style="color: #000099;"><span style="color: #3333ff;">NAME                           VALUE
------------------------------ --------------------
asm_diskstring                 /dev/sda7*
_asm_disk_repair_time          14400
asm_diskgroups                 DATA
asm_power_limit                1
<strong>_asm_ausize                    1048576</strong>
_asm_blksize                   4096
_asm_acd_chunks                1
_asm_libraries                 ufs
_asm_maxio                     1048576
_asm_allow_only_raw_disks      TRUE
_asmlib_test                   0
_asm_allow_resilver_corruption FALSE
_asmsid                        asm
_asm_wait_time                 18
_asm_skip_resize_check         FALSE
_asm_stripewidth               8
<strong>_asm_stripesize                131072</strong>
_asm_droptimeout               60
_asm_emulmax                   10000
_asm_emultimeout               0
_asm_kfdpevent                 0</span>
</span>

We need to look at parameters _asm_ausize and _asm_stripesize which are set to 1 M and 128 K respectively.

In case you wish to create Diskgroup of say 10M Allocation Unit and want to increase the Stripe size to 1M,then set following parameter in pfile

<span style="color: #ff0000;"><strong>_asm_ausize=</strong></span><span style="color: #ff0000;"><strong>10485760
</strong></span><span style="color: #ff0000;"><strong>_asm_stripesize=</strong></span><span style="color: #ff0000;"><strong>1048576</strong></span>

Restart the ASM instance. Please note that these settings will be applicable only for new Diskgroups and will not modify the existing Diskgroups.

You will be required to change the template for the Diskgroup else all the datafiles will use COARSE attribute and will have stripe of 10M. You can find details in 10g SQL reference Guide

20 May, 2008 by 10g 11g 8

ORA-15063 – ASM Discovered Insufficient amount of Disks

Looking at the response generated by my earlier post on ASM, I thought of writing one more article.

Many of us (Working on ASM) would have encountered following errors during startup of ASM instance

ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA1"

Corresponding ASM alert log shows

ERROR: no PST quorum in group 1: required 2, found 1
NOTE: cache dismounting group 1/0x94F1292B (DATA1)
NOTE: dbwr not being msg'd to dismount
ERROR: diskgroup DATA1 was not mounted

Like any other error , first approach to solving any “ORA” error should be look at its description

<span style="color: #ff0000;">/home/oracle&gt;oerr ora 15063
15063, 00000, "ASM discovered an insufficient number of disks for diskgroup \"%s\""
// *Cause:  ASM was unable to find a sufficient number of disks belonging to the
//          diskgroup to continue the operation.
// *Action: Check that the disks in the diskgroup are present and functioning,
//          that the owner of the ORACLE binary has read/write permission to
//          the disks, and that the ASM_DISKSTRING initialization parameter
//          has been set correctly.  Verify that ASM discovers the appropriate
//          disks by querying V$ASM_DISK from the ASM instance.
</span>

Now if you see the explanation, it clearly mentions that there are some disks missing in diskgroup which is not allowing the diskgroup to be mounted. It also gives the Action plan to be taken

1)Check that the disks in the diskgroup are present and functioning


To check this you need to query V$ASM_DISK and see if all the disks are visible and also MOUNT_STATUS shows as Member

SQL> SELECT GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,STATE,NAME,PATH FROM V$ASM_DISK;

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE    NAME       PATH
------------ ----------- ------- ------------ -------- ---------- --------------------
1           0 CACHED  MEMBER       NORMAL   DATA_0000  /dev/sda7

In case the disks are not member, you will most likely see the GROUP_NUMBER corresponding to that disk as 0.

2)Check that Owner of the ORACLE binary has read/write permission to the disks, and that the ASM_DISKSTRING initialization parameter has been set correctly.


You need to check the permissions for the disk and ensure that Oracle is owner

<span style="color: #3333ff;">$ls -ltr /dev/sda7
brw-rw----  1 <strong>oracle</strong> oinstall 8, 7 May  4 18:43 /dev/sda7</span>

You can also use dd command to see if the disk is accessible by Oracle user.e.g

$ id
uid=100(oracle) gid=100(oinstall)
$ dd if=/dev/rdsk/1 of=/dev/null bs=1024 count=100
dd: /dev/rdsk/1: open: Invalid argument

It should have shown something like

100+0 in
100+0 out

In case you are using ASMLIB . then you can use following command to see if disks are visible

$/etc/init.d/oracleasm listdisks

If it does not display any disk, then check if Oracle is having correct permissions

ls -ltr /dev/oracleasm/disks/*


To Check ASM_DISKSTRING parameter you can check previous post on ASM Disk Discovery

3)Verify that ASM discovers the appropriate disks by querying V$ASM_DISK from the ASM instance.

This is again similar to point 2 as in case the permissions are not set or ASM_DISKSTRING is not correctly set, it will not recognize the disk in V$ASM_DISK.

In addition to this , we can also use Oracle utility called kfed to check the problematic disk and verify if disk is part of ASM diskgroup. Kfed is not part of standard oracle installation and has to be generated. Please find below steps for same

For 10.2 above 

<span style="color: #3333ff;"><strong>$cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk ikfed</strong> </span>

<span style="color: #3333ff;">
</span> <span style="color: #3333ff;">Linking KFED utility (kfed)
rm -f /u01/app/oracle/product/asm10.2/rdbms/lib/kfed
gcc -o /u01/app/oracle/product/asm10.2/rdbms/lib/kfed -L/u01/app/oracle/product/asm10.2/rdbms/lib/ -L/u01/app/oracle/product/asm10.2/lib/ -L/u01/app/oracle/product/asm10.2/lib/stubs/ -L/usr/lib -lirc /u01/app/oracle/product/asm10.2/lib/s0main.o /u01/app/oracle/product/asm10.2/rdbms/lib/sskfeded.o /u01/app/oracle/product/asm10.2/rdbms/lib/skfedpt.o /u01/app/oracle/product/asm10.2/rdbms/lib/defopt.o -ldbtools10 -lclntsh `cat /u01/app/oracle/product/asm10.2/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /u01/app/oracle/product/asm10.2/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lmm -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /u01/app/oracle/product/asm10.2/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /u01/app/oracle/product/asm10.2/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /u01/app/oracle/product/asm10.2/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/asm10.2/lib -lm `cat /u01/app/oracle/product/asm10.2/lib/sysliblist` -ldl -lm -L/u01/app/oracle/product/asm10.2/lib
mv -f /u01/app/oracle/product/asm10.2/bin/kfed /u01/app/oracle/product/asm10.2/bin/kfedO
mv: cannot stat `/u01/app/oracle/product/asm10.2/bin/kfed': No such file or directory
make: [ikfed] Error 1 (ignored)
mv /u01/app/oracle/product/asm10.2/rdbms/lib/kfed /u01/app/oracle/product/asm10.2/bin/kfed
chmod 751 /u01/app/oracle/product/asm10.2/bin/kfed</span>

Now to use kfed, we need to use synatx as kfed read devicename 

<span style="color: #3333ff;">$ kfed read /dev/sda8
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj:              2147483648 ; 0x008: TYPE=0x8 NUMB=0x0
kfbh.check:                  2083552713 ; 0x00c: 0x7c307dc9
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                168820736 ; 0x020: 0x0a100000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
<strong>kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER</strong>
</span> <strong><span style="color: #3333ff;">kfdhdb.dskname:               DATA_0000 ; 0x028: length=9
kfdhdb.grpname:                    DATA ; 0x048: length=4
kfdhdb.fgname:                DATA_0000 ; 0x068: length=9</span>
</strong>

I have made the important things as Bold (You can also say, things which I know. For rest need to check with Oracle Support 🙂 )
Lets verify the result with output from V$ASM_DISK and V$ASM_DISKGROUP

<span style="color: #3333ff;">select DG.GROUP_NUMBER "G.NO",DG.NAME,D.DISK_NUMBER,D.MOUNT_STATUS,
D.HEADER_STATUS,DG.TYPE,D.NAME,D.PATH FROM V$ASM_DISK D,V$ASM_DISKGROUP DG 
where DG.GROUP_NUMBER=D.GROUP_NUMBER;</span>

<span style="color: #3333ff;">
</span> <span style="color: #3333ff;"> G.NO NAME       DISK_NUMBER MOUNT_S HEADER_STATU TYPE   NAME       PATH
---------- ---------- ----------- ------- ------------ ------ ---------- ----------
1 DATA                 0 CACHED  MEMBER       EXTERN DATA_0000  /dev/sda8</span>

kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL ->This indicates Redundancy for Group.Check TYPE in query output.


kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER – > This indicates Disk Header status. Here it indicates it is member of Group.

kfdhdb.dskname: DATA_0000 ; 0x028: length=9 -> This indicates Disk Name
kfdhdb.grpname: DATA ; 0x048: length=4 ->This indicates the Group Name for the disk.

kfdhdb.fgname: DATA_0000 ; 0x068: length=9 ->This indicates the Failure Group Name.


Going back to error, we saw

ERROR: no PST quorum in group 1: required 2, found 1

Here PST means Partnership Status Table. PST contains list of disks(partners) which are required to mount the diskgroup. In case you are using External Redundancy and ASM is not able to find one disk , then the diskgroup will fail to mount.

You can read more about PST in book Oracle Automatic Storage Management: Under-the-Hood & Practical Deployment Guide (Oracle)
by Nitin Vengurlekar (Author), Murali Vallath (Author), Rich Long

11g Update on Kfed

You are not required to generate kfed on 11g installation. It is part of the default oracle installation.
18 May, 2008 by 10g 5

10.2.0.4 on AIX5L (64-Bit) is Out

As usual Laurent has been first to locate that 10.2.0.4 on AIX5L (64-Bit) is Out.

Patchset size is whopping 1.9G which I believe is the maximum size for Oracle Patchset till date. Looks like 10.2.0.3 on AIX had lot of bugs or it could be that DATABASE REPLAY feature needed more lines of code in AIX 🙂

I am not sure why Oracle has named this feature as DATABASE REPLAY on 10.2.0.4. It should have been named as DATABASE CAPTURE.

Anyways I was trying to be the first person to post release of 10.2.0.4 Patchset , but I guess Laurent has written some Package which sends a SMS that patchset is out 🙂 I need to start learning PL/SQL.

17 May, 2008 by 10g 3

Tracing Sessions with Event 10046

10046 Event is used to trace SQL statements (similar to sql_trace=true) with additional details depending on the tracing level which is specified.

10046 EVENT levels:

1 – Enable standard SQL_TRACE functionality (Default)
4 – As Level 1 PLUS trace bind values
8 – As Level 1 PLUS trace waits
12 – As Level 1 PLUS both trace bind values and waits

There are various ways to enable the tracing

1) Using Alter session command

ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;

To Turn it off
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;

2) Using DBMS_SESSION

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);


The TRUE argument specifies that wait information will be present in the trace. The FALSE argument specifies that bind information will not be present in the trace.
The SESSION_TRACE_DISABLE procedure disables the trace for the invoking session.

For example:
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();

3) Using DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE);

Where SID and SERIAL# points to the session being traced and can be retrieved from V$SESSION

To turn it off
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,FALSE);

4) Using DBMS_MONITOR

Starting from Oracle 10g, there is a new package which can be used to enable tracing.

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>sid,SERIAL_NUM=>serial#,WAITS=>TRUE,BINDS=>TRUE)

To turn it off

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => sid, serial_num => serial#);

Please refer to following documentation link for more details

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#i8759

5) Using ORADEBUG

connect / as sysdba
Sql>oradebug setospid 1234
Sql>oradebug unlimit
Sql>oradebug event 10046 trace name context forever,level 12

Here 1234 is the OS process Id for the session to be traced.

Sql>oradebug tracefile_name

Above command will show the location of the trace file

To disable oradebug tracing

Sql> oradebug event 10046 trace name context off

Whichever method you use, Oracle will create the trace files in the server’s user_dump_dest directory.

17 May, 2008 by Performance 0

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers