Oracle Database Timezone Issue

Problem:

Post changes in Linux system timezone (in /etc/sysconfig/clock file ) from ZONE="America/New York" to ZONE="Asia/Kolkata" systimetamp over the DB link or TNS alias started reflecting old/wrong timezone 

sqlplus <username>@<TNS_Alias>/<password>;

SQL> select systimestamp,current_timestamp from dual;

SYSTIMESTAMP                            CURRENT_TIMESTAMP
-----------------------------------     -------------------------------------------
22-05-13 06:11:17.900646 AM -05:30      22-05-13 05:11:17.900654 PM +05:30 


Here , +05:30 was the correct time-stamp for Timezone "Asia/Kolkata"

Analysis:

It was identified that time zone of the database server was not matching with one of the Data guard setup in Linux zone configuration file /etc/sysconfig/clock

Required ZONE="Asia/Kolkata" 
But found ZONE="America/New York"

statspack report hangs

Problem: Statspack report hangs 

Executing @?/rdbms/admin/spreport.sql report hangs in midway & unable to proceed further until cancelled

Even if we try to keep it for Hours after undo_retention limit it will throw "ORA-01555 snapshot too old" & come out

Such issue is observed in pre-10g databases where there is no other substitute to stats-pack & its due to huge db object count

Analysis:

Initial analysis,  i kept the report running for over night & it kicked me out with ORA-01555 error after 9 Hours

Further , PERFSTAT.STATS$SQL_SUMMARY table found to be the problem causing table which was doing "db file sequential read" infinitely 

ORA-12520: TNS:listener could not find available handler for requested type of server

Error:

From Alert Log:

FAL[client, MRP0]: Error 12520 connecting to MISC for fetching gap sequence
Mon May 27 12:35:03 2013
Errors in file /opt/oracle/admin/ISC/bdump/isc_mrp0_1513.trc:
ORA-12520: TNS:listener could not find available handler for requested type of server
Mon May 27 12:35:33 2013
FAL[client, MRP0]: Error 12520 connecting to MISC for fetching gap sequence
Mon May 27 12:35:33 2013
Errors in file /opt/oracle/admin/ISC/bdump/isc_mrp0_1513.trc:
ORA-12520: TNS:listener could not find available handler for requested type of server
Mon May 27 12:36:37 2013
FAL[client, MRP0]: Error 12520 connecting to MISC for fetching gap sequence
Mon May 27 12:36:37 2013
Errors in file /opt/oracle/admin/ISC/bdump/isc_mrp0_1513.trc:
ORA-12520: TNS:listener could not find available handler for requested type of server
Mon May 27 12:37:37 2013
FAL[client]: Failed to request gap sequence

Unable to discover any voting files, retrying discovery in 15 seconds

Errors:

 
From: $GRID_HOME/log/<hostname>/alert<<hostname>>.log

[ohasd(13154)]CRS-2112:The OLR service started on node indbprd1.

[ohasd(13154)]CRS-1301:Oracle High Availability Service started on node indbprd1.
[ohasd(13154)]CRS-8017:location: /etc/oracle/lastgasp has 2 reboot advisory log files, 0 were announced and 0 errors occurred
[/oravl01/ora11g/112_GRID/bin/orarootagent.bin(14076)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
[ohasd(13154)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
[gpnpd(14181)]CRS-2328:GPNPD started on node indbprd1.



[/oravl01/ora11g/112_GRID/bin/orarootagent.bin(14196)]CRS-5013:Agent "/oravl01/ora11g/112_GRID/bin/orarootagent.bin" failed to start process "/oravl01/ora11g/112_GRID/bin/osysmond" for action "start": details at "(:CLSN00008:)" in "/oravl01/ora11g/112_GRID/log/indbprd1/agent/ohasd/orarootagent_root/orarootagent_root.log"
[cssd(14283)]CRS-1713:CSSD daemon is started in clustered mode
[ohasd(13154)]CRS-2767:Resource state recovery not attempted for 'ora.diskmon' as its target state is OFFLINE
[cssd(14283)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /oravl01/ora11g/112_GRID/log/indbprd1/cssd/ocssd.log
 
From: $GRID_HOME/log/<hostname>/cssd/ocssd.log

[    CSSD][4290766592]clssgmExecuteClientRequest(): type(37) size(80) only connect and exit messages are allowed before lease acquisition proc(0x7fc1f0045110) client((nil))

[    CSSD][4290766592]clssgmDeadProc: proc 0x7fc1f0045110
[    CSSD][4290766592]clssgmDestroyProc: cleaning up proc(0x7fc1f0045110) con(0x1025) skgpid  ospid 14199 with 0 clients, refcount 0
[    CSSD][4290766592]clssgmDiscEndpcl: gipcDestroy 0x1025
[    GPNP][4288517888]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2104] get-profile call to url "ipc://GPNPD_indbprd1" disco "" [f=0 claimed- host: cname: seq: auth:]
[    GPNP][4288517888]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2234] Result: (0) CLSGPNP_OK. Successful get-profile CALL to remote "ipc://GPNPD_indbprd1" disco ""
[    CSSD][4288517888]clssnmReadDiscoveryProfile: voting file discovery string()
[    CSSD][4288517888]clssnmvDDiscThread: using discovery string  for initial discovery
[   SKGFD][4288517888]Discovery with str::
[   SKGFD][4288517888]UFS discovery with ::



[   SKGFD][4288517888]Fetching UFS disk :/dev/raw/rawctl:
[   SKGFD][4288517888]OSS discovery with ::
[    CSSD][4288517888]clssnmvDiskVerify: Successful discovery of 0 disks
[    CSSD][4288517888]clssnmCompleteInitVFDiscovery: Completing initial voting file discovery
[    CSSD][4288517888]clssnmvFindInitialConfigs: No voting files found
[    CSSD][4288517888](:CSSNM00070:)clssnmCompleteInitVFDiscovery: Voting file not found. Retrying discovery in 15 seconds
[    CSSD][4290766592]clssscSelect: cookie accept request 0x2130390
[    CSSD][4290766592]clssgmAllocProc: (0x7fc1f005eeb0) allocated
[    CSSD][4290766592]clssgmClientConnectMsg: properties of cmProc 0x7fc1f005eeb0 - 1,2,3,4,5
[    CSSD][4290766592]clssgmClientConnectMsg: Connect from con(0x10b1) proc(0x7fc1f005eeb0) pid(14199) version 11:2:1:4, properties: 1,2,3,4,5
[    CSSD][4290766592]clssgmClientConnectMsg: msg flags 0x0000
[    CSSD][4290766592]clssgmExecuteClientRequest(): type(37) size(80) only connect and exit messages are allowed before lease acquisition proc(0x7fc1f005eeb0) client((nil))
 

OCIException OCI error 1034

Error:

2013-04-26 22:43:38.154: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::connectInt (2) Exception OCIException

2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection:connect:excp OCIException OCI error 1034

2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: connect1 errcode 1034

2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: connect2 oracleHome /oravl01/ora11g/112_GRID oracleSid CRAMADG2

2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::connectInt: server not attached

2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::shutdown mode 4

2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] ORA-01031: insufficient privileges

2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: shutdown errcode 1031

2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: oci error try shutdown abort

2013-04-26 22:43:39.182: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::connectInt: server not attached

2013-04-26 22:43:39.210: [ora.cramadg.db][3523208960] {2:35163:2} [start] ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Process ID: 0

Session ID: 0 Serial number: 0

Background: