Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, July 19, 2007

E-biz instance hung, low values of sga_max_size, ORA-600 in alert log

During 1 o' clock today, I got a call. The instance which had gone live had come to a standstill. I had already received some ORA-4031 (Refer metalink note 396940.1 for details of this error) while applying a diagnostic patch and had managed to complete it by doing "alter system flush shared_pool" multiple times.

The situation was so bad that E-business suite was not allowing any new logins. While connected to db if you gave the command show parameter shared_pool it would hang. Finally it was not allowing to do sqlplus /nolog connect / as sysdba also. We stopped all the application services and killed all the LOCAL=NO DB processes, but were unable to connect / as sysdba. The alert log showed these:

Tue Jul 10 13:36:28 2007
Waited too long for library cache load lock. More info in file $ORACLE_HOME/admin/$CONTEXT_NAME/udump/$ORACLE_SID_ora_6094.trc.
Tue Jul 10 13:39:06 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/udump/$ORACLE_SID_ora_926.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:06 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/$ORACLE_SID_cjq0_29067.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:06 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/$ORACLE_SID_smon_29063.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:07 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/$ORACLE_SID_cjq0_29067.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:08 2007
Non-fatal internal error happenned while SMON was doing work on sort segment.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Tue Jul 10 13:39:50 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/udump/$ORACLE_SID_ora_1544.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:50 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/$ORACLE_SID_cjq0_29067.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:50 2007

Since we could not login with sqlplus, we had to kill all oracle processes on OS level and started the db.

The parameters which had low values were:

sga_max_size = 600 M
shared_pool_size=300M
processes=2654
sessions=800

We increased these to:

sga_max_size=4G
shared_pool_size=1G
processes=3000
sessions=6000

Restarted the DB. Warning about oradism appeared in the alert log (described in a previous post). Shutdown the DB and fixed the permissions of oradism binary. Restarted.

No issues after this.

No comments: