Oracle RAC cluster NIC bonding.

You need to do following steps to start using NIC bonding with Oracle RAC (this add both public and private interconnect bond interfaces):

NOTE! This operation needs full downtime from cluster databases. And depending your environment you might need server reboots during this settings.

1. Add new bond0 and bond1 interfaces for RAC cluster globally via oifcfg. public interface is bond0 and cluster_interconnect interface bond1:

Get current network interface configuration being used by cluster as oracle user:
oifcfg getif




Set new bond interfaces – Updates OCR (-global make these changes to all nodes on cluster) run these in one node as oracle user (IPs can be same as before but interface name is changing):
oifcfg setif -global bond0/10.77.5.0:public
oifcfg setif -global bond1/10.37.24.0:cluster_interconnect
oifcfg getif

2. Stop databases and disable + stop crs on all nodes:

Do this on one node as oracle user (and all databases in cluster):
srvctl status database -d <database_name>  
srvctl stop database -d <database_name>
srvctl status database -d <database_name>  

Do these on both nodes as root user:
crsctl disable crs
crsctl check crs
crstcl stop crs
crsctl check crs
 

3.Change OS network interfaces to use NIC bonding like this (this guide only add bond0 but it is better to make bond0 (eth0 and eth1) for public and bond1 (eth2 and eth3 ) for interconnect. And you don't need to make alias interfaces.): 

http://www.oracle-base.com/articles/linux/nic-channel-bonding.php



If you need to reboot nodes because of your environment then do it now.

4. Enable and start crs on all nodes:

Do these on both nodes as root user:
crsctl enable crs
crsctl check crs
crstcl start crs
crsctl check crs
 check that crs started on both nodes run as root user on one node:
crsctl stat res -t

 

5. Remove old interfaces from RAC cluster via oifcfg:

Get current pvt. interconnect info as oracle user in one node:
oifcfg getif
 
Delete old interfaces eth0 and eth1 as oracle user in one node:
oifcfg delif -global eth0/10.77.5.0
oifcfg delif -global eth1/10.37.24.0

Check that only new bond interfaces are visible as oracle user in one node:
oifcfg getif

6. Repair scan- and vip- addresses using bond0 instead of eth0 :

Check cluster current network status as oracle user:
srvctl status nodeapps
Check current VIP settings. run this on all nodes as oracle user:
srvctl config vip -n <node_name>

Check current SCAN settings. run this on one node as oracle user:
srvctl config scan

Stop cluster nodeapps as oracle user:
srvctl stop nodeapps

Change SCAN/VIP settings. run this on all nodes as root user (set correct IP for all nodes):
srvctl modify nodeapps -n <node_name> -A 10.77.5.129/255.255.255.0/bond0

Check current VIP settings. run this on all nodes as oracle user:
srvctl config vip -n <node_name>

Start cluster nodeapps as oracle user:
srvctl stop nodeapps
Check cluster current network status as oracle user:
srvctl status nodeapps

Check current SCAN settings. run this on one node as oracle user:
srvctl config scan


 

7. Restart crs to see that it is starting correctly:

Do these on both nodes as root user: 

crsctl check crs
crstcl stop crs
crsctl check crs
crstcl start crs
crsctl check crs

8. Restart databases:

Do this on one node as oracle user (and all databases in cluster):
srvctl status database -d <database_name> 
srvctl start database -d <database_name> 
srvctl status database -d <database_name>

9. Test that you can connect into databases via all SCAN/VIP IPs. You can do this for example via sqlplus.

 

Oracle 11.2.0.3.0 DATABASE CRASHED DUE TO ORA-240 AND ORA-15064

There is a bug in Oracle 11.2.0.3.0 which can make your database instance restarting itself.
If you get following errors in database alert.log you know this bug is affecting your database:
ORA-00240: control file enqueue held for more than 120 seconds
ORA-29770: global enqueue process LCK0 (OSID 12329) is hung for more than 70 seconds
ORA-15064: communication failure with ASM instance

There is bugfix for this problem and you can download it from My Oracle Support (MOS) patch:
13914613

Other way to fix this is to update your database to newest version where this is also fixed

More info about this can be find from MOS documents:
Database Instance Crashes with ORA-15064 ORA-03135 ORA-00240 on 11.2 (Doc ID 1487108.1)

Bug 13914613 - Excessive time holding shared pool latch in kghfrunp with auto memory management (Doc ID 13914613.8) 


 

Oracle ORA-21561 : OID generation failed

If Oracle client connection is giving "ORA-21561 : OID generation failed" error.
Like this:


sqlplus <test_user>/<test_user_password>@<database_name>

.
.
.

ERROR:
ORA-21561 : OID generation failed

Enter user-name: <username>/<password> @ <tns connect string>


Then the problem is most likely in the client machine hosts file.
Check that there is client machine fully qualified name and short name in the client machine hosts file. If these are missing you'll get ORA-21561 errors when trying to connect server.

Oracle 11.2.0.3 GCR0 trace files continuously created in trace directory.

Some environments with Oracle 11.2.0.3 you can see a lot of GCR0 trace files in trace directory.
Inside these trace files there is "kjgcr_DeleteSO" messages.
For Example:
---
.
.
.
kjgcr_DeleteSO: Processing SO at 0x13068a58
----------------------------------------
SO: 0xa13068a58, type: 36, owner: 0xa30d19230, flag: -/-/-/0x00 if: 0x1 c: 0x1
 proc=0xa30d19230, name=GCR, file=kjgcr.h LINE:652, pg=0
(gcr) state=0x0 hold=0x0
.
.
.
---


These trace files are coming because of Bug 13385346.
You can find more about this Bug from MOS (My Oracle Support) ID: 13385346.8

But main thing is that as a workaround you can ignore these traces (they are small files and does not affect the database. This bug is fixed in 11.2.0.3.4 Database Patch Set Update and in the Windows platforms 11.2.0.3 Patch 6 .

Oracle SQLArea Most Expensive SQLs.

From v$sqlarea view you can check most expensive SQL clauses in your Oracle database. You can for example make checks about CPU time, Elapsed time, Executions, Buffer gets, Disk reads etc...

Here is the example SQL for CPU time (seconds) per execution fetching (run this as user who have permission to select from v$sqlarea) (this will fetch other values too but the order is made by CPU time per execution):
----

SQL> set pages 120
SQL> set lines 120

SQL> spool CPU_Time_seconds_per_exec.txt

SQL> select rownum as sorting, a.* from (
SELECT sqlst.parsing_schema_name, sqlst.sql_id, sqlst.last_active_time,
(sqlst.buffer_gets/sqlst.executions) "buffer_gets per exec",
(sqlst.disk_reads/sqlst.executions) "disk_reads per exec",
(sqlst.cpu_time/1000000) "cpu Time (s)",
(sqlst.elapsed_time/1000000) "Elapsed Time (s)",
(sqlst.user_io_wait_time/sqlst.executions) "iowait per exec",
sqlst.executions "ex",
sqlst.module,
substr(sqlst.sql_text,1,250) || '|' FROM
v$sqlarea sqlst
WHERE
sqlst.executions > 0 AND sqlst.parsing_schema_name <> 'ANONYMOUS' AND sqlst.parsing_schema_name <> 'APEX_030200'
AND sqlst.parsing_schema_name <> 'APEX_PUBLIC_USER' AND sqlst.parsing_schema_name <> 'APPQOSSYS'
AND sqlst.parsing_schema_name <> 'DBSNMP' AND sqlst.parsing_schema_name <> 'DIP' AND sqlst.parsing_schema_name <> 'EXFSYS'
AND sqlst.parsing_schema_name <> 'FLOWS_FILES' AND sqlst.parsing_schema_name <> 'MDSYS' AND sqlst.parsing_schema_name <> 'ORACCT'
AND sqlst.parsing_schema_name <> 'ORACLE_OCM' AND  sqlst.parsing_schema_name <> 'ORDDATA' AND sqlst.parsing_schema_name <> 'ORDPLUGINS'
AND sqlst.parsing_schema_name <> 'ORDSYS' AND sqlst.parsing_schema_name <> 'OUTLN' AND sqlst.parsing_schema_name <> 'PUBLIC'
AND sqlst.parsing_schema_name <> 'SI_INFORMTN_SCHEMA' AND sqlst.parsing_schema_name <> 'SYS' AND sqlst.parsing_schema_name <> 'SYSTEM'
AND sqlst.parsing_schema_name <> 'WMSYS' AND sqlst.parsing_schema_name <> 'XDB' AND sqlst.parsing_schema_name <> 'XS$NULL' AND
TO_CHAR(sqlst.last_active_time, 'YYYYMMDD')=TO_CHAR(SYSDATE, 'YYYYMMDD')
ORDER BY "cpu Time (s)" desc ) a
where rownum < 11



SQL> spool off;

----

In this example SQL fetch only this day rows. You can make more accurate fetch if you make changes where clause "last_active_time" section. And with rownum you can limit number of rows that fetch will list.
And changing sort (ORDER BY) you'll get different most expensive lists like sqls that used most time (elapsed_time) . parsing_schema_name clauses in where will exclude Oracle own schemas from result set so that you can see only user made schemas.

Oracle Connectivity test with shell script.

Sometimes you might need a script which make connectivity check for database.

Here is example shell script that makes connectivity checks for selected Oracle database.
Add following text in text file with .sh extension (connectivity_test_shell.sh). And change username , password and database name. You can also change numbers in loop (how many connection tests you want to run). Script creates conn_test.txt file where connection informations are printed (so user must have write permissions in the directory where this script is.).

----


#!/bin/bash

　

for x in {0..10};

do

echo quit | sqlplus testuser/<testuser_password>@<database_name> >> conn_test.txt;

done


----

Give permissions to scripts:
chmod 755 connectivity_test_shell.sh

Run script:
./connectivity_test_shell.sh

Oracle RAC connectivity test with shell script and sql script.

If you need to make connectivity check for Oracle RAC cluster database here is sample scripts for that.
With this you can check connections for all cluster database instances. 

First create sql script connectivity_test_shell.sql (text file with .sql extension.) and add following into it:
----

select instance_name from v$instance;

----




Here is example shell script that makes connectivity checks for selected Oracle database.
Add following text in text file with .sh extension ( connectivity_test_shell.sh ). And change username , password and database name. You can also change numbers in loop (how many connection tests you want to run). Database user you are using should have select permission for v$instance view used in connectivity_test_shell.sql script.

Script creates conn_test_testdb.txt file where connection information is printed (so user must have write permissions in the directory where this script is.). In this log you find also information of instance which you are connected. This way you can check that you can connect all instances of cluster database.

----

#!/bin/bash

for x in {0..100};
  do
    echo quit | sqlplus testuser/<testuser_password>@<cluster_database_name> @connectivity_test_shell.sql >> conn_test_testdb.txt;
  done

----

Give permissions to scripts:
chmod 755 connectivity_test_shell*

Run script:
./connectivity_test_shell.sh