ORA-29702 - Starting RAC instance in non-rac mode

Assume the following situation: Due to some OS or network related problem you are not able to start-up the CRS (this does happen!) before it impacts the database availability and subsequently your business. Your primary goal now is to make the database available as soon as possible irrespective of its mode (RAC or Non-RAC). The reason being you would like to reduce the impact to your business. If you attempt to startup the instance without CRS, you get the following error:

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

This error message indicates that it can't start the instance since CRS is not available. Here is the quick work-around to startup the instance. The option is to remove RAC option from your binaries and start the instance. Once you have resolved the CRS related issues, you can turn-on the RAC option back (of course, your instance needs to be down while to turn-on or turn-off the options). Here is how we turn-off:

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

Set cluster_database=false

You should now be able to startup the instance without CRS stack being up and running.

SQL> Select * from v$option where parameter like 'Real%';

PARAMETER VALUE
---------------------------------------- ----------
Real Application Clusters FALSE
Real Application Testing TRUE

Turn-on the RAC option with:

make -f ins_rdbms.mk rac_on ioracle

Alternatively, you can install a non-rac oracle binaries to quickly start the instance.


By the way, you turn off other options in similar way such as Partitioning (part_on/part_off), DB Vault (dv_on/dv_off) etc..

Oracle11g - Killing session in RAC (in remote instance)

Prior to Oracle11g, whenever you want to kill a session connected to a non-local (remote) instance, you had to make an explicit connection to that particular instance and then attempt to kill.

Example:

SQL> Select instance_number from v$instance;

INSTANCE_NUMBER
---------------
1 ---> I am connected to instance 1 (Local Instance)

1 row selected.

SQL> Select inst_id, sid, serial# from gv$session where username='CHANDRA';

INST_ID SID SERIAL#
---------- ---------- ----------
2 125 7526 ---> CHANDRA is connected to Inst# 2 (Non-local/remote Instance)

1 row selected.

With 11g, you could kill the session which is connected to instance 2, while you are connected to instance 1:

SQL> Alter system kill session '125,7526,@2' immediate; #--@2 indicates remote instance.

System altered.

SQL> Select inst_id, sid, serial# from gv$session where username='CHANDRA';

no rows selected

This really makes life easy - especially you are using SQL*Plus and not any other front-end tool.

Errors while installing Oracle9i in Redhat 5.3 (libstdc++-libc6.1-1.so.2)

Today, I was trying to install Oracle9i on Redhat 5.3 (2.6.18 kernel), got the following error when runInstaller was involved:

$ Initializing Java Virtual Machine from /tmp/OraInstall2009-05-09_06-44-18AM/jre/bin/java. Please wait...
/tmp/OraInstall2009-05-09_06-44-18AM/jre/bin/i386/native_threads/java: error while loading shared libraries: libstdc++-libc6.1-1.so.2: cannot open shared object file: No such file or directory

When check for libstdc++- library, I found the following to be installed by default on RH 5.3:

[root@alps tmp]# rpm -qa |grep libstdc++-
libstdc++-4.1.2-44.el5
libstdc++-devel-4.1.2-44.el5
compat-libstdc++-296-2.96-138
compat-libstdc++-33-3.2.3-61
[root@alps tmp]#

Looks like it is looking for libstdc++-lib6.1-1.

When I did the following the initial error message disappeared:

# cd /usr/lib
# ln -s libstdc++-3-libc6.2-2-2.10.0.so libstdc++-libc6.1-1.so.2

Then I got the following error:

$ ./runInstaller
$ Initializing Java Virtual Machine from /tmp/OraInstall2009-05-09_06-50-11AM/jre/bin/java. Please wait...
Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2009-05-09_06-50-11AM/jre/lib/i386/libjava.so: symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference


When applied the oracle patch: 3006854 - it fixed it:

[root@alps 3006854]# sh rhel3_pre_install.sh
Applying patch...
Ensuring permissions are correctly set...
Done.
Patch successfully applied
[root@alps 3006854]#

Adaptive/Intelligent Cursor Sharing in 11g - turning-off

Here is how the cursor sharing works at a high level with bind variables, especially in conjunction with Histograms (prior to oracle 11g):

• Optimizer peeks value for the bind on initial parse
• Initial value of the bind determines the plan
• Same plan is used/shared regardless of future bind values
• One plan not always appropriate and optimal for all bind values and results in following problems or side-effects:

- Performance problems
- Instability issues
- Unhappy end users

Adaptive Cursor Sharing address this problem. it shares execution plans ONLY when bind values are "equivalent" - means if it doesn't result in performance degradation. This is the default behavior in Oracle11g.

More information on adaptive cursor sharing can be found at: http://optimizermagic.blogspot.com/2009/04/update-on-adaptive-cursor-sharing.html - The intention of this post is not to talk about this feature - but how to turn-off this default behavior. I can't really think of a reason why anyone would like to turn this feature off, but it is possible, if at all we need it for some reason. This can be achieved by using NO_BIND_AWARE hint.

I believe this hint is meant to be an alternative for using the hidden parameter - _optim_peek_user_binds=false which is used to turn-off bind peeking.

Here is the example:

The default behavior:

SQL> variable lv_id number;

SQL> exec :lv_id :=100;

PL/SQL procedure successfully completed.

SQL> Select count(*) from sh.sales where prod_id = :lv_id;

COUNT(*)
----------
0

----if the bind values are peeked, we would see records in V$SQL_CS_STATISTICS:

SQL> Select address, child_number, peeked, executions from v$sql_cs_statistics;

ADDRESS CHILD_NUMBER P EXECUTIONS
-------- ------------ - ----------
34C54884 0 Y 1


Now, let's turn off bind peeking.
======================

SQL> alter system flush shared_pool;

System altered.

SQL> exec :lv_id :=1000;

PL/SQL procedure successfully completed.

SQL> Select /*+ NO_BIND_AWARE */ count(*) from sh.sales where prod_id = :lv_id;

COUNT(*)
----------
0
----With the above hint, the sql is not bind aware, therefore we don't see any records in v$SQL_CS_STATISTICS view.

SQL> Select address, child_number, peeked, executions from v$sql_cs_statistics;

no rows selected

SQL>

I guess, this is a better and elegant way to turn-off bind-peeking at the SQL level instead of turning-off bind-peeking at the instance level using the hidden parameter - _optim_peek_user_binds. Again, this parameter is typically used to prevent the side/ill-effects of bind-peeking and the same is now (with 11g) obviated with the introduction of Adaptive Cursor Sharing feature in 11g.

BTW, this hint is available in 11.1.0.7.

Oracle11g CRS new command

Just found out that the functionality of the "crsctl" command has been expanded in Oracle11g. Prior to Oracle11g, we did not have the ability to check the status of the cluster on other (non-local) nodes in the cluster using "crsctl" - but oracle11g makes it now possible:

[root@racnode01 bin]# ./crsctl check cluster
racnode01 ONLINE
racnode02 ONLINE
[root@racnode01 bin]#

Of course, we could very well use "crsstat" command to determine the status of CRS on non-local nodes - but not straight-forward and simple.

Oracle Support Bug Status Codes...

When you have an SR open with Oracle needing a bug fix, most of the times the status would be indicated in codes. Here is the list of codes and what it means...thought would come in handy:

10 - Description Phase: Development is requesting more information.
16 - Support bug screening: Bug is being reviewed by our Bug Diagnostics group.
11 - Code Bug (Response/Resolution): Bug is being worked by Development.
30 - Additional Information Requested: Bug is being worked by Support and/or more information was requested by Development.
37 - To Filer for Review/Merge Required: Bug has been fixed but the patch will be merged into the next patchset.
80 - Development to Q/A: Bug is being regression tested for future release.
81 - Q/A to Dev/Patch or Workaround Avble: Patch released via Metalink.
90 - Closed, Verified by Filer: Bug has been fixed and is closed.
91 - Closed, Could Not Reproduce: Bug is closed as not reproducible.
92 - Closed, Not a Bug: Bug is closed as not a bug (not reproducible or setup issue).
93 - Closed, Not Verified by Filer: Bug has been fixed and is closed.
95 - Closed, Vendor OS Problem: Bug is closed as an OS problem.
96 - Closed, Duplicate Bug: Bug is closed as a duplicate bug.

ORA-38856 - while opening a cloned RAC database

I was attempting to open a cloned RAC database and got the following error:

SYS@NETFRD1> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

The fix was to use the undocumented parameter - _no_recovery_through_resetlogs=TRUE to open the database. I guess this is one of those scenarios where Oracle suggests using this undocumented/unsupported parameter. Here is what I did to get pass the problem:

1. Shutdown;
2. Startup mount (with the parameter _no_recovery_through_resetlogs=TRUE set in init.ora)
3. Open the database with RESETLOGS;

SYS@NETFRD1> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled


SYS@NETFRD1> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SYS@NETFRD1> !vi /tmp/init$ORACLE_SID.ora

SYS@NETFRD1> startup mount pfile=/tmp/init$ORACLE_SID.ora
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size 2084232 bytes
Variable Size 385876600 bytes
Database Buffers 1174405120 bytes
Redo Buffers 14692352 bytes
Database mounted.
SYS@NETFRD1> alter database open resetlogs;

Database altered.