ORA-27090: Message 27090 not found on 10.2.0.4

Lately we have been noticing ORA-27090 on our 10.2.0.4 databases. Here is the complete error message, as appeared in the alert log file. This is observed on Suse Linux, Red hat and OEL 5.3:

===============================================================
Errors in file /tmp/test_dbw0_4400.trc:
ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA
Additional information: 3
Additional information: 128
Additional information: 1100 #--This is the value for the parameter aio-max-nr
===============================================================
Looks like this error condition is only encountered or reported in databases using 10.2.0.4 oracle version. On research found that this has something to do with the kernel setting fs.aio-max-nr. aio-max-nr sets the systemwide maximum number of AIO requests. Apparently, when the number of requests reach this number the error is reported. I guess the question would be, how can we determine where we are at currently in relation to the max limit, right? This is answered by aio-nr, which maintains a cumulative/running total of number of aio events used.

I was able to consistently reproduce the error condition by playing around with the settings for aio-max-nr parameter. As such, no apparent problems are noticed on the database side, but did did notice that the standby databases would fail to continue with the managed recovery process.

Here is how it looked like before starting up an 10.2.0.4 instance:

#/sbin/sysctl -a |grep aio
fs.aio-max-nr = 1100 #---I reset it to this value from default.
fs.aio-nr = 0

When the instance was started, the value looked like this:
#/sbin/sysctl -a |grep aio
fs.aio-max-nr = 1100 #---I reset it to this value from default.
fs.aio-nr = 1060

As soon as it hit the ceiling, the above mentioned error was reported in the alert log. When I raised the value, the error message disappeared.

As soon as the instances are shutdown the value for fs.aio-nr gets reset.

Hope this information would come in handy.

How to check if CRS is set to auto-start on reboots

Sometime back, some one asked me a question - "how to confirm that CRS is set to restart on reboot?"

CRS can be configured to either to restart or not to restart on reboots. Disable restarts would be typically useful for troubleshooting CRS reboots or validating any system level changes.

To enable restarts (default):
$CRS_HOME/bin/crsctl enable crs

To disable restarts of crs on reboots:
$CRS_HOME/bin/crsctl disable crs

Note that the above needs access to root.

Whenever the above commands are run, it modifies the following file (on linux), which indicates whether or not the CRS is set to auto-restart on reboots:

/etc/oracle/scls_scr/node_name/root/crsstart

It contains either "enable" or "disable" keywords.

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.