Unable to start sqlplus after OS patching in AIX with Could not load module libha_gs_r.a(shr_64.o) message

Noticed following issue after OS patching on a set of RAC standby servers which is running on AIX

 

Issue details

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 21 16:41:36 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Could not load program oraclepcen2:

        Dependent module libha_gs_r.a(shr_64.o) could not be loaded.

Could not load module libha_gs_r.a(shr_64.o).

System error: No such file or directory

ERROR:

ORA-12547: TNS:lost contact

Also noticed following error from oracle relink log output

ld: 0706-006 Cannot find or open library file: -l ha_gs_r

        ld:open(): No such file or directory

make: The error code from the last command is 255.

Oracle running on AIX needs fileset: rsct.basic.rte and probably that fileset is missing in this host and that is the reason for this error.

This package is part of the OS Patch IY98287 which is one of the mandatory required patches to be installed on AIX if we are configuring a 11g RAC environment.

prdxxxx001:oracle:pcen1:/orasw/pcen/app/oracle/product/11.2.0/db/bin: lslpp -l rsct.basic.rte

lslpp: Fileset rsct.basic.rte not installed.

Finally worked with AIX Unix Admin to install missing fileset and we are able to start oracle binary

prdxxxx001:oracle:pcen1:/home/oracle: lslpp -l rsct.basic.rte

  Fileset                      Level  State      Description        

  ----------------------------------------------------------------------------

Path: /usr/lib/objrepos

  rsct.basic.rte             3.2.0.4  COMMITTED  RSCT Basic Function

Path: /etc/objrepos

  rsct.basic.rte             3.2.0.4  COMMITTED  RSCT Basic Function

ORA-39700: database must be opened with UPGRADE option when upgrading standby instance

You might see error “ORA-39700: database must be opened with UPGRADE option “  in standby instance after Oracle version upgrade even if standby binary is also upgraded to same version as of primary

[oracle@admdbbk001:/orasw/app/oracle/product/11.2.0.4/db]# echo $ORACLE_HOME

/orasw/app/oracle/product/11.2.0.4/db

[oracle@admdbbk001:/orasw/app/oracle/product/11.2.0.4/db]# sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 9 10:14:01 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size                  2248080 bytes

Variable Size             822084208 bytes

Database Buffers         1291845632 bytes

Redo Buffers               21708800 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 17039506

Session ID: 588 Serial number: 3

Fix is to start standby instance in mount mode, start listener and wait for logs to be transferred and applied to standby.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size                  2248080 bytes

Variable Size             822084208 bytes

Database Buffers         1291845632 bytes

Redo Buffers               21708800 bytes

Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select name,open_mode,database_role,switchover_status from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS

--------- -------------------- ---------------- --------------------

TEST11G   MOUNTED              PHYSICAL STANDBY NOT ALLOWED

After few minutes once it has applied all logs, we can shutdown and start database and error will disappear.

LOCKED vs LOCKED(TIMED) in ACCOUNT_STATUS in DBA_USERS

We use ACCOUNT_STATUS column value: LOCKED(TIMED) in DBA_USERS dictionary view to monitor cases where ACCOUNTS getting locked due to wrong password attempts.  When we deliberately lock a user account, status is set to LOCKED and it works for most of the cases.

I Started seeing a new behavior after 11g upgrade where ACCOUNT_STATUS for database users changed from LOCKED(TIMED) to LOCKED when user accounts is locked after too many wrong password attempts.

SQL> select username,account_status,LOCK_DATE,profile from dba_users where username='MAD_APP';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE           PROFILE

------------------------------ -------------------------------- ------------------- ------------------------------

MAD_APP                       LOCKED                           2015-02-03 12:18:28 APPLICATION_PROFILE

SQL> select username,account_status,LOCK_DATE,profile from dba_users where username='MAD_TOOL_ADM';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE           PROFILE

------------------------------ -------------------------------- ------------------- ------------------------------

MAD_TOOL_ADM                  LOCKED                           2015-02-03 12:19:33 SCHEMA_PROFILE

SQL> alter user MAD_APP account unlock;

User altered.

SQL> alter user MAD_TOOL_ADM account unlock;

User altered.

After checking at DBA_PROFILES settings for these user profiles, noticed that  PASSWORD_LOCK_TIME  is set to UNLIMITED. With this value set to unlimited, it’s an expected behavior to see only LOCKED status instead of LOCKED(TIMED). As per our organization standards it should be set to 24855.

PROFILE                               RESOURCE_NAME               RESOURCE_TYPE        LIMIT

--------------------------------- ---------------------- ---------------------- -------------

APPLICATION_PROFILE                PASSWORD_LOCK_TIME              PASSWORD        UNLIMITED

SCHEMA_PROFILE                     PASSWORD_LOCK_TIME              PASSWORD        UNLIMITED

Expected behaviour is
1. Oracle release is <= 11.1.0.7.
DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

2. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME = unlimited:
DBA_USERS.ACCOUNT_STATUS = LOCKED whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

3. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME =
DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

Note that 10.2.0.5 displays the same behavior as 11.2, because the fix that changed the behavior in 11.2 was introduced in 10.2.0.5.

So finally resolved the issue by following command

SQL> alter profile system_profile limit PASSWORD_LOCK_TIME 24855;

SQL> alter profile application_profile limit PASSWORD_LOCK_TIME 24855;

Now I could see that status changed to LOCKED(TIMED)

SQL> select username,account_status from dba_users where username in ('MAD_APP','MAD_TOOL_ADM');

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
MAD_APP                       LOCKED(TIMED)
MAD_TOOL_ADM                  LOCKED(TIMED)

Troubleshooting sudden tablespace growth

I started noticing that my application is inserting very high volume of data compared with previous day when we started researching why we are adding more data files to a particular tablespace.

Here is how I did the troubleshooting. First Find biggest segments consuming this TABLESPACE

SQL> col segment_name format A35

SQL> col partition_name format A35

SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/(1024*1024) M_bytes from dba_Segments where TABLESPACE_NAME= 'ELF_TIBCO_DATA' order by 3;

OWNER                          SEGMENT_NAME                        PARTITION_NAME                      SEGMENT_TYPE          M_BYTES

------------------------------ ----------------------------------- ----------------------------------- ------------------ ----------

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48333                   LOB SUBPARTITION         1099

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48334                   LOB SUBPARTITION         1155

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48335                   LOB SUBPARTITION         1151

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48336                   LOB SUBPARTITION         1117

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48337                   LOB SUBPARTITION         1159

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48338                   LOB SUBPARTITION         1045

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48339                   LOB SUBPARTITION         1156

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48392                   LOB SUBPARTITION         1199

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48393                   LOB SUBPARTITION         1176

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48394                   LOB SUBPARTITION         1143

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48395                   LOB SUBPARTITION         1227

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48396                   LOB SUBPARTITION         1177

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48397                   LOB SUBPARTITION         1169

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48398                   LOB SUBPARTITION         1173

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48399                   LOB SUBPARTITION         1203

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48452                   LOB SUBPARTITION        22516

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48453                   LOB SUBPARTITION        22617

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48454                   LOB SUBPARTITION        22130

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48455                   LOB SUBPARTITION        22297

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48456                   LOB SUBPARTITION        22632

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48457                   LOB SUBPARTITION        22397

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48458                   LOB SUBPARTITION        22341

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48459                   LOB SUBPARTITION        22341

We can easily correlate SEGMENT_NAME to TABLE_NAME if SEGMENT_TYPE is TABLE and to partition if it has partitions. But if SEGMENT_TYPE is LOB_PARTITION we need to find the table and partition associated with this lob segment.

To do that, try

SQL> col column_name format A30

SQL> select owner,TABLE_NAME , COLUMN_NAME ,SEGMENT_NAME,TABLESPACE_NAME  from dba_lobs where segment_name='SYS_LOB0000101461C00004$$';

OWNER                          TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                        TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------ ----------------------------------- ------------------------------

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$           ELF_TIBCO_DATA

SQL> select TABLE_OWNER,TABLE_NAME ,COLUMN_NAME,LOB_NAME, PARTITION_NAME, LOB_PARTITION_NAME,TABLESPACE_NAME from dba_lob_partitions where table_name='TIBCO_EVENT_LOG_ATTACHMENTS';

TABLE_OWNER                    TABLE_NAME                     COLUMN_NAME                    LOB_NAME                       PARTITION_NAME                      LOB_PARTITION_NAME             TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------- ------------------------------ ------------------------------

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131017            SYS_LOB_P48091

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131018            SYS_LOB_P48151

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131019            SYS_LOB_P48211

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131020            SYS_LOB_P48271

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131021            SYS_LOB_P48331

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131022            SYS_LOB_P48391

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131023            SYS_LOB_P48451

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131024            SYS_LOB_P48511

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131025            SYS_LOB_P48571

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131026            SYS_LOB_P48631

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131027            SYS_LOB_P48691

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131028            SYS_LOB_P48751

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131029            SYS_LOB_P48811

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131030            SYS_LOB_P48871

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131031            SYS_LOB_P48931

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131101            SYS_LOB_P48991

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131102            SYS_LOB_P49051

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131103            SYS_LOB_P49111

Now we know that it is related to ELF_ADMIN.TIBCO_EVENT_LOG_ATTACHMENTS and is having big LOB attachments associated with ATTACHMENT_DATA column. As we started seeing this issue on 2013/10/23, we can

Check the details of SYS_LOB_P48451

SQL> select TABLE_OWNER,TABLE_NAME , COLUMN_NAME,LOB_NAME  , LOB_PARTITION_NAME,SUBPARTITION_NAME, LOB_SUBPARTITION_NAME from dba_lob_subpartitions where  TABLE_NAME ='TIBCO_EVENT_LOG_ATTACHMENTS' and LOB_PARTITION_NAME='SYS_LOB_P48451';

TABLE_OWNER                    TABLE_NAME                     COLUMN_NAME                    LOB_NAME                       LOB_PARTITION_NAME             SUBPARTITION_NAME              LOB_SUBPARTITION_NAME

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB1  SYS_LOB_SUBP48452

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB2  SYS_LOB_SUBP48453

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB3  SYS_LOB_SUBP48454

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB4  SYS_LOB_SUBP48455

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB5  SYS_LOB_SUBP48456

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB6  SYS_LOB_SUBP48457

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB7  SYS_LOB_SUBP48458

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB8  SYS_LOB_SUBP48459

It looks like there are 8 sub partitions associated with this LOB SEGMENT and compared with previous day we are inserting 22 times more attachments (error messages).

Now I approached app support and started investigating into the contents of this table.