AIX paging issue



Issue:  High paging on AIX oracle DB server

Background/Observation:

                    i.  AIX server with version 6.1 TL 7 (6100-07-06-1241) running standalone Oracle DB 10.2.0.5 

                   ii.  Huge paging memory utilization found to be caused by db processes mainly

#lsps -s

Total Paging Space   Percent Used

      30720MB               40%



#ps -ealf | head -1 ; ps -ealf | sort -rn +9 | head

       F S      UID      PID     PPID   C PRI NI ADDR    SZ    WCHAN    STIME    TTY  TIME CMD

  240001 A ora10g  3736258    1   0  60 20 f60b76590 152524      Jul 31      -  4:40 ora_dbw0_ORADB

  240001 A ora10g  2883690    1   0  60 20 5c0bdc590 143288     Jul 31      -  4:29 ora_dbw1_ORADB

  240001 A ora10g  1835488    1   0  60 20 ad0c2d590 125636     Jul 31      -  8:33 ora_lgwr_ORADB

  240001 A ora10g 13041936   1   0  60 20 d95259590 118304      Jul 31      - 203:28 oracleORADB (LOCAL=NO)

  240001 A ora10g  5046804    1   0  60 20 4321c3590 118292      Jul 31      - 176:28 oracleORADB (LOCAL=NO)

  240001 A ora10g  9044626    1   5  62 20 1e5c9e590 118220      Jul 31      - 160:27 oracleORADB (LOCAL=NO)

  240001 A ora10g  2228980    1   0  60 20 5513d5590 117520      Jul 31      - 176:38 oracleORADB (LOCAL=NO)

  240001 A ora10g 11534512   1   0  60 20 5e7cde590 109812      Jul 31      - 185:14 oracleORADB (LOCAL=NO)

  240001 A ora10g  6619604   1   0  60 20 6e0bee590 109796      Jul 31      -  2:12 ora_arc1_ORADB

  240001 A ora10g  3801228   1   0  60 20 af0a2f590 109796        Jul 31      -  2:17 ora_arc0_ORADB



SQL2314W SQLSTATE=01650 runstats issue in db2


Error:

db2 "runstats on table <SCHEMA_NAME>.<TABLE_NAME>"

SQL2314W Some statistics are in an inconsistent state. The newly collected "TABLE" statistics are inconsistent with the existing "INDEX" statistics.SQLSTATE=01650




exec(): 0509-036 Cannot load program sqlplus

Error

1.


$sqlplus -v

exec(): 0509-036 Cannot load program sqlplus because of the following errors:

        0509-130 Symbol resolution failed for /usr/lib/libc.a[aio_64.o] because:

        0509-136   Symbol kaio_rdwr64 (number 1) is not exported from

                   dependent module /unix.
2.
$sudo ./rootpre.sh

+ sudo ./rootpre.sh

Required file ./pw-syscall32 is missing.

Aborting pre-installation procedure. Installations of Oracle may fail.


Background:

Oracle 9.2.0.4 binary copied from one to another server having same configuration for server migration as well as 9.2.0.8 upgrade.

Newly copied Oracle 9i binary has been relinked. 

Post which above errors observed

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