Tuesday, June 18, 2013

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"

Proceeding further to match up the zones on all OS as 
"Asia/Kolkata"  DB server zone modification was done in Linux configuration file.


However , It was inappropriate method for zone modification in DB server which lead to differences in timezone at db level  as mentioned in Problem:

Also timezone difference was not observed when connected from sys/system as sysdba or any other DB user from local db server.

It was only observed for any user connections over the DB link or TNS.

Solution:

With reference to below MOS Note,


How To Change Timezone for 11gR2 Grid Infrastructure [ID 1209444.1] 

$GRID_HOME/crs/install/s_crsconfig_<hostname>_env.txt

has been modified as
  
TZ=GMT+05:30 
to 
TZ=Asia/Kolkata
 

Followed by all cluster services restarts , Resolved the issue 

Thursday, June 6, 2013

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 

db file read wait event lead us towards indexes which were not found as there were constraints present on the table

SQL> select owner,CONSTRAINT_NAME,TABLE_NAME,STATUS from dba_constraints  where table_name='STATS$SQL_SUMMARY';

OWNER        CONSTRAINT_NAME      TABLE_NAME           STATUS
------------ -------------------- -------------------- --------
PERFSTAT     SYS_C0032056         STATS$SQL_SUMMARY    ENABLED
PERFSTAT     SYS_C0032057         STATS$SQL_SUMMARY    ENABLED
PERFSTAT     SYS_C0032058         STATS$SQL_SUMMARY    ENABLED
PERFSTAT     SYS_C0032059         STATS$SQL_SUMMARY    ENABLED
PERFSTAT     STATS$SQL_SUMMARY_PK STATS$SQL_SUMMARY    DISABLED
PERFSTAT     STATS$SQL_SUMMARY_FK STATS$SQL_SUMMARY    ENABLED

6 rows selected.



Primary key constraint found to be disabled.


Now , why was it disabled ?

Here it comes ..

ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 1361
ORA-06512: at "PERFSTAT.STATSPACK", line 2442
ORA-06512: at "PERFSTAT.STATSPACK", line 91



Cannot Run Statspack.Snap Ora-00001[Article ID 267244.1]


Workaround was to disable the constraint & we did it quickly without knowing impact

ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;



Solution:

Solution for spreport slowness is to enable constraint followed by statistics gathering so that table would be Index scanned to retrieve report faster. 

Enable constraint:

ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY ENABLE CONSTRAINT STATS$SQL_SUMMARY_PK ;

OR

ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY modify CONSTRAINT STATS$SQL_SUMMARY_PK ENABLE NOVALIDATE; 

Gather Stats:

exec dbms_stats.gather_table_stats('PERFSTAT','STATS$SQL_SUMMARY',estimate_percent => 100,cascade => true , no_invalidate => false);
OR


exec dbms_stats.gather_schema_stats('PERFSTAT',estimate_percent => 100,cascade => true , no_invalidate => false);

There are chances of ORA-02437 while enabling constraint again ,  so you may enable the constraints post @?/rdbms/admin/sptrunc.sql

Long term solution for ORA-02437/ORA-00001 would be to upgrade to 10g