8 things about me

Fellow bloggers H.Tonguç Yılmaz and Freek D’Hooge blog-tagged me a while ago… I was in the process of transitioning to a new job and did not bother to play then. Here I am now, better late than never… a few things about me.

1. I am a transplant from India, living in the San Francisco Bay Area, California, United States for the past 11 years.

2. My affair with Oracle technologies started from version 5, I started as an Oracle developer programming on Oracle v5.x, SQL*Forms 2.x, SQL*Reportwriter 1.x and I still find her attractive

3. What I recall of Oracle V5 is pretty vague now.. a BI (before image) file was used for rollbacks, CCF (create contiguous file) to add a datafile.. by now you guessed that I am not that young

4. Between Oracle v5 and v6, I worked on Informix for some time. I never liked it, so I promptly came back to Oracle. If Oracle was a girl, I bet she must be a blonde!

5. Before coming to the U.S., I was in Kuwait for 3 years working on Unix, Oracle and Informix.

6. I have been driving the same car for 11 years - a Toyota Camry, it had some problems and repairs but it works fine as of now… and I am happy my insurance and registration costs are low…. so why bother buy a new one.

7. I started to learn Digital SLR photography a couple of years ago. I love photography and I would like to devote more time to this. You can see some of the photos I took at my picture trails.

8. I love skiing. For someone who has lived in a hot, tropical climate and never seen snow for the most of his life, I had a hard time learning to ski, let alone adjusting to the cold winter and snow. After several years of visiting the various ski resorts at Tahoe, I am now an avid intermediate skiier. I love the thrill of skiing on the slopes and enjoy the quiet scenery of the snow and mountains from the top… it is priceless.

Query to get the undocumented parameters and their values

I am posting this to have a handy reference, as x$ tables are greek to me and I am not aware of another way to do this other than query the x$ tables.

Here is a query to display all hidden or undocumented parameters and their default values.

SELECT
a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
a.ksppinm LIKE '/_%' escape '/'
/

When you run this query, expect to see hundreds of rows where the undocumented parameters start with an “_”. Hence you’d probably want to filter this by providing the parameter of interest in the LIKE clause of the query.

Oracle 10g RAC Cluster interconnects

Yet another post on … say, you’ve just joined a new client, you are in a new RAC environment and you are trying to figure out the different components of the cluster.

This time it is on the Cluster private interconnects.

The private interconnects used in a RAC are typically configured during the Clusterware install and would be registered in OCR. However this default config could thereafter be overrided using cluster_interconnects initialization parameter (not recommended though ) for various reasons.

In order to troubleshoot interconnect related issues, a starting point would be to find out what interconnects were originally configured and/or what interconnects are presently in use by the cluster. Here are a few ways to do this.

1. Using the dynamic view gv$cluster_interconnects:

select * from gv$cluster_interconnects ;

INST_ID    NAME            IP_ADDRESS       IS_ SOURCE
---------- --------------- ---------------- --- -------------------------------
1          eth0            192.168.10.1     NO  Oracle Cluster Repository
1          eth1            192.168.11.1     NO  Oracle Cluster Repository
2          eth0            192.168.10.2     NO  Oracle Cluster Repository
2          eth1            192.168.11.2     NO  Oracle Cluster Repository

In the above output, the column SOURCE indicates where the private interconnect info was derived from; this column could be one of OCR, OS dependent software or cluster_interconnects parameter.

2. Using the clusterware command oifcfg:

$oifcfg getif
eth2 10.104.95.0 global public
eth0 192.168.10.0 global cluster_interconnect
eth1 192.168.11.0 global cluster_interconnect

3. Using oradebug ipc:

sqlplus “/ as sysdba”

SQL>oradebug setmypid
Statement processed.
SQL>oradebug ipc
Information written to trace file.

The above command would dump a trace to user_dump_dest. The last few lines of the trace would indicate the IP of the cluster interconnect. Below is a sample output of those lines.

From the trace file on node1:

SSKGXPT 0×5edf558 flags SSKGXPT_READPENDING socket no 9 IP 192.168.11.1 UDP 18852

From the trace file on node2:

SSKGXPT 0×5edf558 flags SSKGXPT_READPENDING socket no 9 IP 192.168.10.2 UDP 38967

Oracle RAC Clusterware: Voting disks

Say, you’ve just joined a new client, you are in a new RAC environment and you are trying to figure out the different components of the cluster.

The voting disk is one such component in 10g RAC that is used to store the hearbeat information between nodes. Like OCR, the voting disk also is a shared disk component that is accessed by the nodes during the cluster operation. If a node is unable to ping the voting disk, the cluster immediately detects a communication failure and the node is evicted from the cluster to keep the cluster healthy.

crsctl command can be used to check out the voting disk info of a cluster. Here is the usage of crsctl to determine the voting disks.

$ crsctl query css votedisk
0. 0 /dev/vx/rdsk/ocrdg/vdvol00
1. 0 /dev/vx/rdsk/ocrdg/vdvol01
2. 0 /dev/vx/rdsk/ocrdg/vdvol02

Oracle RAC Clusterware: OCR Disks

Say, you’ve just joined a new client, you are in a new RAC environment and you are trying to figure out the different components of the cluster.

Oracle Cluster Registry (OCR) is one such component in 10g RAC used to store the cluster configuration information. It is a shared disk component, typically located in a shared raw volume that must be accessible to all nodes in the cluster. The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.

ocrcheck is the command to check out the OCR. Here is a sample output of ocrcheck.

$ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 1048296
Used space (kbytes) : 5116
Available space (kbytes) : 1043180
ID : 834229908
Device/File Name : /dev/vx/rdsk/ocrdg/ocrvol
Device/File integrity check succeeded
Device/File Name : /dev/vx/rdsk/ocrdg/ocrvol2
Device/File integrity check succeeded

Cluster registry integrity check succeeded

The file /etc/oracle/ocr.loc can also be used for this.

$cat /etc/oracle/ocr.loc
ocrconfig_loc=/dev/vx/rdsk/ocrdg/ocrvol
ocrmirrorconfig_loc=/dev/vx/rdsk/ocrdg/ocrvol2
local_only=FALSE

Oracle Undo size: 10g 10.2.0.3 vs 9i 9.2.0.6

Does 10g R2 generate more undo than 9i R2?

While trying to inspect the undo details for an UPDATE statement using v$transaction in my previous post (this was tested in Oracle 10.2.0.3) , I found something strange. While the UPDATE statement updated only 100,000 rows, the column USED_UREC from v$transaction displayed 200,000 rows. I was curious to see the behavior in 9i.

Here is what I saw from v$transaction in 9i:

SQL_TEXT                                    RECORDS     BLOCKS     KBYTES
---------------------------------------- ---------- ---------- ----------
update t1 set name = rpad('y', 30, 'y' )     100000       1516      12128

RECORDS field is the alias for column USED_UREC and it shows only 100,000 records. BLOCKS field is the alias for column USED_UBLK shows 1516 blocks, which is about half of what [3078 blocks] I saw in 10G (see previous post). Does this mean 10GR2 generates twice as much undo?

As a next step, I did a tkprof for both tests.

On 9.2.0.6, tkprof shows the following:

update t1 set name = rpad('y', 30, 'y' )       

call     count       cpu    elapsed    disk      query    current        rows
------- ------  -------- ---------- ------- ---------- ----------  ----------
Parse        1      0.00       0.00       0          0          0           0
Execute      1      1.23       4.84       0        675     103151      100000
Fetch        0      0.00       0.00       0          0          0           0
------- ------  -------- ---------- ------- ---------- ----------  ----------
total        2      1.23       4.84       0        675     103151      100000

There is nothing unusual here.

On 10.2.0.3, tkprof shows the following:

update t1 set name = rpad('a', 30, 'a' )                 

call     count       cpu    elapsed    disk      query    current        rows
------- ------  -------- ---------- ------- ---------- ----------  ----------
Parse        1      0.00       0.00       0          0          0           0
Execute      1      2.07       2.72       0        724     205112      100000
Fetch        0      0.00       0.00       0          0          0           0
------- ------  -------- ---------- ------- ---------- ----------  ----------
total        2      2.07       2.72       0        724     205112      100000

Here the value of current mode gets is twice that of 9.2.0.6. Why so?

For both cases 9i and 10g, table T1 did not have any indexes.

Digging further to check if the recursive sql’s had any effect, from the tkprof and the trace, notice another difference:

On 9.2.0.6

select file#
from
 file$ where ts#=:1                 

call     count       cpu    elapsed    disk      query    current        rows
------- ------  -------- ---------- ------- ---------- ----------  ----------
Parse       12      0.00       0.00       0          0          0           0
Execute     12      0.00       0.00       0          0          0           0
Fetch       36      0.00       0.00       0         48          0          24
------- ------  -------- ---------- ------- ---------- ----------  ----------
total       60      0.00       0.00       0         48          0          24       

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)                 

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS BY INDEX ROWID FILE$
      2   INDEX RANGE SCAN I_FILE2 (object id 42)

On 10.2.0.3

select file#
from
 file$ where ts#=:1                 

call     count       cpu    elapsed    disk      query    current        rows
------- ------  -------- ---------- ------- ---------- ----------  ----------
Parse       23      0.00       0.00       0          0          0           0
Execute     23      0.00       0.00       0          0          0           0
Fetch       46      0.00       0.00       0         92          0          23
------- ------  -------- ---------- ------- ---------- ----------  ----------
total       92      0.00       0.00       0         92          0          23       

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)                 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL FILE$ (cr=4 pr=0 pw=0 time=44 us)

Note the number of executions in 10G has doubled for this sql and it does a table scan on FILE$.

Does the change in the access path of this recursive SQL lend to the increase in the current mode gets? and does that impact the undo size? at this point, I am really puzzled

How much UNDO does a SQL use?

If you wish to know how much UNDO does a DML statement use, here is a method.

First, let’s create a test table to populate 100,000 rows.

SQL> create table t1 ( id number(6), name varchar2(30) );

Table created.

SQL> create sequence seq;

Sequence created.

SQL> insert into t1 select seq.nextval, rpad(’x',30, ‘x’) from test where rownum < 100001;

100000 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

COUNT(*)
———-
100000

The dynamic view v$transaction provides details of active transactions in the database ; the column USED_UBLK provides the number of undo blocks consumed by a transaction; the column USED_UREC provides the number of undo records used.

col sql_text format a40
set lines 130
select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t,
v$session s,
v$sql sq
where t.addr = s.taddr
and s.sql_id = sq.sql_id
and s.username = ‘<user>’
/

Let’s update the table using

SQL> update t1 set name = rpad(’y', 30, ‘y’) ;

100000 rows updated.

and run the v$ script from another window to get the output below:

SQL_TEXT                                    RECORDS     BLOCKS     KBYTES
---------------------------------------- ---------- ---------- ----------
update t1 set name = rpad('y', 30, 'y')      200000       3078      24624

Using v$transaction provides an insight to the active transactions on the system and the resources used by those transactions.

This tells the update dml used 3078 blocks or 24Mb of undo. But, look at USED_UREC column - it shows 200,000 records when I updated only 100,000 records.. this seems strange… this is not what I’ve seen before.. I need to check more on this .. stay tuned…

Weird RAC issue due to an incorrect setup

When setting up a 4-node Oracle 10.2.0.2 RAC on RHEL4, after installing Oracle Clusterware, Oracle RAC binaries and creating a clustered database, I went ahead to open the instance on the first node and and then the second node. The instance on the first node would come up without any problem, but the instance on the second node would take a while to come up. However, on further checking we realized that the first instance was down. It turned out that while the second instance was coming up the first instance would crash. It all seemed weird because one would think both instances were healthy, but just the fact of bringing up an instance would crash the already open instance.

The alert log had the following messages:

Interface type 1 eth0 192.168.10.0 configured from OCR for use as a cluster interconnect
Interface type 1 eth3 192.168.11.0 configured from OCR for use as a cluster interconnect
WARNING 192.168.11.0 could not be translated to a network address error 1
Interface type 1 eth2 1#.1##.145.0 configured from OCR for use as a public interface

I went ahead to check if both interconnects are healthy.

A ping command on the primary interconnect revealed it was fine.

$ping <Primary-NIC>
PING Primary-NIC (192.168.10.2) 56(84) bytes of data.
64 bytes from Primary-NIC (192.168.10.2): icmp_seq=0 ttl=64 time=0.035 ms
64 bytes from Primary-NIC (192.168.10.2): icmp_seq=1 ttl=64 time=0.045 ms

— Primary-NIC ping statistics —
2 packets transmitted, 2 received, 0% packet loss, time 999ms
rtt min/avg/max/mdev = 0.035/0.040/0.045/0.005 ms, pipe 2

A ping command on the secondary interconnect indicated there was a problem.

$ping <Secondary-NIC>
PING Secondary-NIC (192.168.11.2) 56(84) bytes of data.

— Secondary-NIC ping statistics —
28 packets transmitted, 0 received, 100% packet loss, time 27018ms

Once the SysAdmin fixed the secondary interconnect, both instances came up without any issues. This reinforces the importance of a functioning primary and secondary interconnect, if you specify both interconnects during the install - however, this does not provide redundancy or the ability to failover to the good interconnect if one interconnect fails.

The setup did not use NIC bonding, instead it used a primary and secondary interconnect for private interface (secondary interconnect for failover capability). I am positive both NIC cards should have been working during the install as the install pre-requisites were all met. But, it is evident from the alert log messages the second interconnect had some trouble.

Multiple interconnects allow Cache Fusion traffic to be distributed on all interconnects. However, if any one of the interconnects does not function, Oracle will assume the private network is down and will not open in cluster mode. Therefore it is highly recommended to use NIC bonding at the OS level for NIC failover and traffic sharing capability.

Bottom line: this incident clearly indicates the configuration of the interconnect plays a crucial role in RAC. Should I even make this statement?

P.S: for the dirty details of setting up NIC bonding in Linux check out this excellent link by Vivek.

RAC Hangs due to small cache size on SYS.AUDSES$

If you run Oracle RAC on 10.2.0.2 or 9i watch out for the following.

I’ve seen on a certain occasions when there is heavy system activity, all instances in RAC hang, even connecting as sysdba was terribly slow. It turned out this was due to the sequence SYS.AUDSES$ has its cache setting at default value of 20.  Metalink note: 395314.1 mentions that the some of the symptoms are:

- Checkpoint not completing on all RAC nodes
- Waits expire on row cache enqueue lock dc_sequences
- RAC hangs due to QMON deadlocking

It also mentions the fix to increase the cache size to a large value.

alter sequence sys.audses$ cache 10000;

Apparently this is fixed in 10.2.0.3.

ORA-04030

On seeing the ORA-04030 error reported on a Oracle 9.2.0.6 RAC database running on Solaris 9, VCS SFRAC, being somewhat familiar with this error message I started looking at PGA and ulimit related settings.

Before I delve any further, here is a bit of background:

oerr ora 4030
04030, 00000, “out of process memory when trying to allocate %s bytes (%s,%s)”
// *Cause: Operating system process private memory has been exhausted
// *Action:

Automatic PGA had been set on this database:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO
pga_aggregate_target                 big integer 1073741824

1Gb for pga_aggregate_target on a server with 16Gb memory for the nature of the database operations seemed quite reasonable.

ulimit settings were as follows:

ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 4096
vmemory(kbytes) unlimited

On some further checking, it was found processes and sessions were set as follows

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sessions                             integer     2500
processes                            integer     700

These settings were odd, on further checking it was found that another DBA had changed these values to test a change to VCS SFRAC, but the old values were not restored after the test.

On changing these parameters back to old values processes=1000 and sessions=1105, the problem disappeared.

The moral of the story here is, when troubleshooting issues we need to keep an eye on not so straightforward causes as well.