Surprising Little change in 11g NetCA

I was working on Oracle 11g trying to troubleshoot some weird x-windows issues that is totally unrelated to the actual project I was involved in.

The issue was that, the Oracle binaries were moved from LOC-A to LOC-B. Things were working fine as usual as this is no big deal. But after a few days we wanted to run NetCA and the GUI would not start. Initially since the environment is completed handled via remote vncservers, it was thought that perhaps the issue had to do with DISPLAY setup and such. Troubleshooting DISPLAY settings came out with nothing.

Then came the idea to look into the netca script itself.

Here is where I found an interesting change.

In 11g, the ORACLE_HOME variable is hard coded based on the initial installation of the binaries. Like this:

vi..Netca file and you will see...

ORACLE_HOME=/apps/ora11rac/product/11.1/db_1

However, I checked a 10g binary installation and this one seems to go after the
$ORACLE_HOME environment variable, which is good.


10g


case $ORACLE_HOME in
"") echo "****ORACLE_HOME environment variable not set!"
echo " ORACLE_HOME should be set to the main"
echo " directory that contains Oracle products."
echo " Set and export ORACLE_HOME, then re-run."
exit 1;;
esac

Row Exclusive in Oracle Database 11g. Interesting Changes.

While doing some random testing in 11g (11.1.0.6), I came across an interesting situation. I am still investigating and trying to look for answers but I thought it is also worth discussing here. Perhaps, someone might shed some light on this change.

We know how row exclusive mode works up until 10g; Just to illustrate I have a sample test case below.


SID 136 is the blocker that holds table "my_all_table" in exclusive mode.

SID 132 and 130 are requesting exclusive access and are waiting for 136 to release its lock.

1* lock table my_all_table in row exclusive mode
SQL> /

Table(s) Locked.

SQL> select sys_context('USERENV','SID'),sys_context('USERENV','session_user') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
136
GVAIDHYA

SQL> select * from v$lock where sid in (132,136,130);


ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
53132010 53132028 136 TM 52553 0 6 0 2797 1
531320BC 531320D4 132 TM 52553 0 0 3 2614 0
53132168 53132180 130 TM 52553 0 0 3 81 0

SQL> select sid,serial#,blocking_session,username from v$session where blocking_session is not null;


SID SERIAL# BLOCKING_SESSION USERNAME
---------- ---------- ---------------- ------------------------------
130 68 137 GVAIDHYA
132 362 137 GVAIDHYA


The output for the above SQL stating that the blocking session is 137, (One higher than the actual blocker (136) is due to the bug in 10R2 which is fixed in 10.2.0.4. My test instance for 10g is a 2-node RAC. The bug details are here.


Now, the same scenario was played in a 11g database, This is a single instance database but that is irrelevant to our discussion.


SQL> @mysid

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
132

SQL> lock table my_all_table in row exclusive mode;

Table(s) Locked.

SQL> sho user
USER is "GVAIDHYA"
SQL> select sysdate from dual;

SYSDATE
---------
20-FEB-09


SQL> lock table my_all_table in row exclusive mode;

Table(s) Locked.

SQL> @mysid

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
129


My SIDS; blocker and the waiter, are 132 and 129 respectively. However, the difference in 11g compared to 10g is that the session is not waiting!!. In other words, you could not tell which one is a blocker and which one is a waiter as the two sessions are moving along just fine.


SQL> select * from v$Lock where sid in (select sid from v$session where username='GVAIDHYA');

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6A7CA568 6A7CA594 129 AE 99 0 4 0 886 0
6A7CA9F0 6A7CAA1C 132 AE 99 0 4 0 850 0
B7E2464C B7E2467C 132 TM 57448 0 3 0 822 0
B7E2464C B7E2467C 129 TM 57448 0 3 0 871 0

SQL> select sid,serial#,blocking_session from v$session where username = 'GVAIDHYA';

SID SERIAL# BLOCKING_SESSION
---------- ---------- ----------------
129 18
132 38

1* select object_name, object_id from dba_objects where object_id in (99,57448)
SQL> /

OBJECT_NAME OBJECT_ID
------------------------------ ----------
ORA$BASE 99
MY_ALL_TABLE 57448


A look at the v$lock shows that both sessions have locked the object (my_all_table , object_id 57448) in Row-X mode (LMODE=3). Big change from 10g when the blocker had it in (LOMODE=6) and the waiters were just waiting (LMODE=0). Interestingly, there is a new twist. We also notice that there are two more entries for the same SIDs in question. The two entries point to an object_id 99, which is held in shared mode. Also notice that the "BLOCK" column is all zeroes.

What is this change and why?

There is a new TYPE called "AE". I could not find much details on this. However this is associated to an object ID 99. Upon looking at this object name, it points to a new object called ORA$BASE. Which raises an interesting question. What is this ORA$BASE? Based on an article at Alex's blog, it seems like this is an object that will maintain version/patchset related data dictionary details. Which sounds intriguing. I was wondering how Oracle was able to pull of an "online patching" though it is said that this only applied to one-off and diagnostic patches. Based on the hint from Alex's blog, It seems like Oracle is trying to maintain multiple versions (probably 2) of dictionary views one for the pre-patch state and one for the post-patch.

Though OBJECT_TYPE lists same material as it did in the previous release documentation, these is a new entry called "EDITION" for the ORA$BASE. There is a new columns called "EDITION_NAME" is said to be reserved for future use.

Yet, the questions remains as to why my row exclusive is not showing up as "lock_mode 6" in v$lock and how that it allow
changes to happen from both sessions to the same object while I clearly had them locked in exclusive mode? I am still trying to find the answer. If you have any ideas please share it with me.