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

Oracle Date and Time comparison

There are several ways to do date and time comparison in Oracle.
Here are a couple of examples how to do it (examples are using v$sqlarea view last_active_time column which data type is DATE) :

1. Comparison as date with using TO_DATE function:

SQL> select count(*) from v$sqlarea sqlst where sqlst.last_active_time > to_date('07/10/2013 13:44:10', 'mm/dd/yyyy hh24:mi:ss' );

  COUNT(*)
----------
         7

2. Comparison as string with using TO_CHAR function (Note different comparison >=  ): 

SQL> select count(*) from v$sqlarea sqlst where TO_CHAR(sqlst.last_active_time, 'YYYYMMDD-HH24MISS') >= '20130710-134410';

  COUNT(*)
----------
         8

Oracle Get database DDL's with dbms_metadata.get_ddl function

Since Oracle 9i you can get database DDL clauses from sqlplus with dbms_metadata.get_ddl utility. Data is fetch from data dictionary. Here is the list of possible object_types to fetch with dbms_metadata.get_ddl:
For Oracle 9i:
http://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_metad2.htm#1031458

For Oracle 11:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm#BGBIEDIA


To get all one schema tables and indexes creation clauses out of the database you can do it with following SQL:

SQL> set pages 120
SQL> set lines 120 
SQL> set long 5000

SQL> spool test_schema_name_ddl.txt

SQL> select dbms_metadata.get_ddl('TABLE',dt.table_name,dt.owner)||';' from dba_tables dt where dt.owner='<TEST_SCHEMA_NAME>';

SQL> select dbms_metadata.get_ddl('INDEX',di.index_name,di.table_owner)||';' from dba_indexes di where di.table_owner='<TEST_SCHEMA_NAME>';

SQL> spool off;

Same way you can add more clauses inside spool. For example sequences, synonyms, db_links etc...

If you want to cleaner output you can get it with sqlplus settings (thought select clauses are added in the spool file still):
set heading off
set feedback off
set verify off

Oracle CRS is not starting "has a disk HB, but no network HB, DHB has rcfg..." in ocssd log

Network problems in interconnect network or problems with interconnect interface can prevent CRS for starting.

If you look CRS check you'll see following:
[root@<node2> <node2>]# /u01/app/11.2.0/grid/bin/crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager


CRS alert log shows following:
(/u01/app/11.2.0/grid/log/<node2>/alert<node2>.log):
.
.
.
2013-06-03 06:56:50.778
[/u01/app/11.2.0/grid/bin/cssdagent(13124)]CRS-5818:Aborted command 'start' for resource 'ora.cssd'.
Details at (:CRSAGF00113:) {0:28:4} in /u01/app/11.2.0/grid/log/<node2>/agent/ohasd/oracssdagent_root/oracssdagent_root.log.
2013-06-03 06:56:50.779
[cssd(13138)]CRS-1656:The CSS daemon is terminating due to a fatal error; Details at (:CSSSC00012:) in /u01/app/11.2.0/grid/log/<node2>/cssd/ocssd.log
.
.
.


ocssd log shows following:
 (/u01/app/11.2.0/grid/log/<node2>/cssd/ocssd.log) (this is complaining about node1 interconnect) :


2013-06-03 06:56:50.814: [    CSSD][3190012224]clssnmvDHBValidateNCopy: node 1, <node1>, has a disk HB, but no network HB, DHB has rcfg 216823918, wrtcnt, 48655493,
LATS 5387994, lastSeqNo 48655492, uniqueness 1365009957, timestamp 1370231810/927159338

2013-06-03 06:56:51.822: [    CSSD][3190012224]clssnmvDHBValidateNCopy: node 1, <node1>, has a disk HB, but no network HB, DHB has rcfg 216823918, wrtcnt, 48655494,
LATS 5389004, lastSeqNo 48655493, uniqueness 1365009957, timestamp 1370231811/927160338

2013-06-03 06:56:52.862: [    CSSD][3190012224]clssnmvDHBValidateNCopy: node 1, <node1>, has a disk HB, but no network HB, DHB has rcfg 216823918, wrtcnt, 48655495,
LATS 5390044, lastSeqNo 48655494, uniqueness 1365009957, timestamp 1370231812/927161338
.


You can check Ping and SSH between nodes via interconnect interface.
If they are not working then there is problem in network connection between cluster nodes. Fix the problem and CRS will start correctly. 

But if Ping and SSH did work between nodes via interconnect interface and still ocssd log did complain about interconnect HeartBeat (no network HB) then interconnect interface is jammed. You can try to restart it to get it fixed (NOTE! It is usually the working node interconnect interface that is needed to restart (like error message is saying in ocssd.log (it is complaining node1)). For example if node2 CRS is not starting then restart node1 interconnect interface ) :
[root@<node1> <node1>]# ifdown eth1
[root@<node1> <node1>]# ifup eth1
And check that eth1 is looking ok:
[root@<node1> <node1>]# ifconfig


After interface restart or network problem fix check that <node2> clusterware is starting again:
[root@<node2> <node2>]# /u01/app/11.2.0/grid/bin/crsctl check crs

NOTE: If clusterware is trying to connect long enough via interconnect without success it will give this message in its alert log:
[ohasd(7773)]CRS-2771:Maximum restart attempts reached for resource 'ora.cssd'; will not restart.

If this error occurs then you need to kill CRS processes manually or reboot <node2> to get it trying again the clusterware start (cssd start).

Oracle cannot allocate new log "Private strand flush not complete" or "Checkpoint not complete" in alert log.

It's not unusual to get "Private strand flush not complete" or "Checkpoint not complete" messages with cannot allocate new log in alert log. Both are relating redo log writing and they are not errors. But if you get these a lot then it is better to try to fix them.

1. Usually easiest way to fix these is add more Redo Logs and Redo Log groups. 

2. Other way is increase log files size.

3. Increasing the value for db_writer_processes (and use ASYNC I/O with Redo Logs ) can help with  these. These are increasing Redo writing speed.

4. You can also try to tune checkpoint to fix "Checkpoint not complete". Here you can find parameters to tune: Checkpoint Tuning and Troubleshooting Guide [ID 147468.1]

5. And also moving log files to faster disk is possible fix.


If you want more detailed info about these alert log messages read following MY Oracle Support (MOS) documents:

Alert Log Messages: Private Strand Flush Not Complete [ID 372557.1]

Can Not Allocate Log [ID 1265962.1]
Checkpoint Tuning and Troubleshooting Guide [ID 147468.1]

Oracle sequence last number check and creation clauses.

Sometimes for example when you are updating database you might need to check that sequences last used value does not change. This can be done running following SQL before and after update and then comparing the result sets (this show only given schema sequences):

SQL> spool test_schema_name_sequences_last_num_before.txt

SQL> set pages 120
SQL> set lines 120

SQL> select SEQUENCE_OWNER,SEQUENCE_NAME,LAST_NUMBER from dba_sequences where SEQUENCE_OWNER = '<TEST_SCHEMA_NAME'>) order by 2;

SQL> spool off;


# Again after changes:

SQL> spool test_schema_name_sequences_last_num_after.txt

SQL> set pages 120
SQL> set lines 120
SQL> select SEQUENCE_OWNER,SEQUENCE_NAME,LAST_NUMBER from dba_sequences where SEQUENCE_OWNER = '<TEST_SCHEMA_NAME'>) order by 2;
SQL> spool off;


NOTE! Remember that last_number column value is aware of cache. If you have cache 20 last_number is increased 20 every time next cache set is taken.  And because of this you can't depend only this column when cache is used you might need also check current_value from sequence. If cache is not used at all then last_number value is increased only when next value is taken from sequence and last_number is same as current_value.


If you want to get schema sequences creation clauses out of the database you can do it  with following SQL:
SQL> spool test_schema_name_sequences_ddl.txt

SQL> set pages 120
SQL> set lines 120 
SQL> select dbms_metadata.get_ddl('SEQUENCE',ds.sequence_name,ds.sequence_owner) from dba_sequences ds where ds.sequence_owner='<TEST_SCHEMA_NAME>';

SQL> spool off;

Oracle Statspack usage

Statspack is tool for performance monitoring and reporting.
New versions of Oracle (10 and 11) provides AWR (Automatic Workload Repository) reports with more detailed statistics than statspack.
But sometimes you might still need statspack for example with Oracle SE version databases.

Here is how you can get statspack report out of your database (First create tablespace for statspack before start install):

1. Install statspack (this creates PERFSTAT schema and this asks you to give tablespace name for statspack)(Run as oracle user (and SYS)):
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spcreate.sql   


2. Take snapshots for statspack report (you need at least 2 snapshots to generate report):
sqlplus perfstat/<password>
exec statspack.snap;   

You can also give detail levels for snapshots (Default level is 5). Levels vary between Oracle versions.
Oracle 11.2 gives following levels:
SQL> select * from stats$level_description;

SNAP_LEVEL
----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
         0
This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information

         5
This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels

         6
This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels

         7
This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels

        10
This level includes capturing Child Latch statistics, along with all data captured by lower levels
----------------------------------

Take snapshot with certain level:
sqlplus perfstat/<password>
exec statspack.snap(i_snap_level=>10);

It is also possible to collect snapshots automatically via dbms_jobs (spauto.sql script) or with your own cron script.


3. Generate statspack report (This list available snapshots and asks you to give begin and end snapshot for report):
sqlplus perfstat/<password>
@?/rdbms/admin/spreport

You can also check available snapshots from here:
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

If you need a help for statspack report analyzing check these:
statspackanalyzer.com
 http://filebank.orapub.com/cgi-bin/quickUOWTBA.cgi


4. Purge statspack snapshots (You can purge old or all statspack snapshots from database):
Purge 10 days older snapshots:
sqlplus perfstat/<password>
exec statspack.purge(sysdate-10);



5. If you want to remove statspack schema and data from your database you can do it this way (Run as oracle user (and SYS)):
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql