Oracle Database Startup Hangs or taking long time On Solaris SPARC Operating System

Issue:

Seeing slow SGA allocation on database startup (even in startup nomount taking long time appears hung/hang)

Alert Log stuck at "DISM Started" Line as shown below:

TRUSS output shows startup nomount is sleeping... looping and Err#11 EAGAIN at semtimedop  and sleeping... at pollsys

When truss ( truss -o connect.out -f sqlplus / as sysdba ) the startup or shutdown, or truss the SQL*Plus connection, they all will show spinning on the same semtimedop command.

This is OS level issues.

Cause:

The underlying problem in this specific case was an OS Bug.

If DISM is enabled in a Local Domain / Local Zone configuration the SGA is put into a memory segment which is larger than the actual size of the SGA.

Solution #1:

Disable the use of DISM by the database on Oracle Solaris in one of two ways:

1.Unset the SGA_MAX_SIZE / MEMORY_MAX_TARGET / MEMORY_TARGET parameters.
2.Ensure SGA_MAX_SIZE is set to the same value as SGA_TARGET parameter or equal to the sum of all SGA components in the instance.

Solution #2:

Need to check with OS admin to determine the underlying cause of the semtimedop call failure.

Please apply official patch 150400-02 or later at OS Level.

Workaround is available by setting the following in /etc/system:

set disable_ism_large_pages=0x74

A reboot is required to make the change effective.

u6678700.drv: ORA-06512: at "APPS.HR_API_USER_HOOKS_UTILITY" error

Issue:

ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "APPS.HR_API_USER_HOOKS_UTILITY", line 891
ORA-06512: at line 1


Solution:

Skip all hooks related jobs/workers and proceed


Reference:

-APPS.HR_API_USER_HOOKS_UTILITY [ID 1174964.1]
-User Hook errors during migration from 11.5.10.2 to 12.1.3 [ID 1413917.1]

u6678700.drv: FAILED: file ademusr.sql

Action plan to avoid ademusr.sql  job issue:

Issue:

AD Worker error:
The following ORACLE error:
ORA-01720: grant option does not exist for 'SYS.DBA_USERS'
occurred while executing the SQL statement:
GRANT select on FND_OAM_DBAUSERS_VL to em_oam_monitor_role

AD Worker error:
The following ORACLE error:

ORA-01720: grant option does not exist for 'SYS.GV_$SESSION'


Cause:

-APPS user do not have select privileges on 'SYS.DBA_USERS' table.

-Missing select with grant option privilege on sys.GV_$SESSION.


Reference:

-Patch 11i.ATG_PF.H.RUP7 (6241631) Failed With ORA-01720 While Running ademusr.sql [ID 1056816.1]

-ORA-01720: grant option does not exist for 'SYS.GV_$SESSION' [ID 1292658.1]


Solution:

To implement the solution, execute the following steps:

Grant select privileges to APPS user on 'SYS.DBA_USERS' object.

1. On Database Tier connect to SQLPlus as sysdba : sqlplus "/ as sysdba"

2. Execute the following on SQLPlus:

SQL> GRANT SELECT ON DBA_USERS TO APPS WITH GRANT OPTION;

3. As SYSTEM user, run the following from SQL Plus:


SQL> grant select on sys.GV_$SESSION to apps with grant option;


Restart the failed Worker using the adctrl utility in a second Shell session and continue.

u6678700.drv: FAILED: file biv_b_age_h_sum_mv.xdf

Action plan to avoid biv_b_age_h_sum_mv.xdf issue

Issue:

Exception occured  ORA-54015: Duplicate column expression was specified
ORA-06512: at "APPS.AD_MV", line 131
ORA-06512: at "APPS.AD_MV", line 289
ORA-06512: at line 1

java.sql.SQLException: ORA-54015: Duplicate column expression was specified
ORA-06512: at "APPS.AD_MV", line 131
ORA-06512: at "APPS.AD_MV", line 289
ORA-06512: at line 1

Reference:

-Error - ORA-54015 : biv_b_age_h_sum_mv.xdf Failes with Duplicate Column Expression was Specified [ID 1322144.1]
-Patch 10163753: APPSST11202: BIV_B_AGE_H_SUM_MV FAILS DURING INDEX CREATION

Solution:

Perform below steps on admin and form node

In the case the Upgrade itself could not be stopped, copy the file biv_b_age_h_sum_mv.xdf from Patch 10163753 into the filesystem Directory (after you have backed up the original file)

cp $BIV_TOP/patch/115/xdf/biv_b_age_h_sum_mv.xdf $BIV_TOP/patch/115/xdf/biv_b_age_h_sum_mv.xdf.org                

then

cp [PATCH_DIR]/biv_b_age_h_sum_mv.xdf $BIV_TOP/patch/115/xdf

Restart the failed Worker using the adctrl utility in a second Shell session and continue.

u6678700.drv: FAILED: file cskbctxp.sql

Action Plan to avoid cskbctxp.sql job issue:

Issue:

declare
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist
ORA-06512: at "SYSTEM.AD_DDL", line 165
ORA-06512: at line 45

Cause:

Below objects were missing :

SQL>select owner,object_name,object_type,last_ddl_time,status from dba_objects
where object_name like upper('%cs_kb_f_ctx_pkg%');

Solution:

To implement the solution, please execute the following steps:

Run the following sql to create the missing objects

sqlplus apps/paswd @$CS_TOP/patch/115/sql/cskfctxs.pls APPS
sqlplus apps/paswd @$CS_TOP/patch/115/sql/cskfctxb.pls APPS

Restart the failed Worker using the adctrl utility in a second Shell session and continue.

u6678700.drv: FAILED: file zpbremcwm.sql

Action plan to avoid zpbremcwm.sql job issue:

Issue:

ERROR at line 235:
ORA-06550: line 235, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_CUBE
ORA-06550: line 235, column 6:
PL/SQL: Statement ignored
ORA-06550: line 239, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_DIMENSION
ORA-06550: line 239, column 6:
PL/SQL: Statement ignored
ORA-06550: line 243, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_CATALOG
ORA-06550: line 243, column 6:
PL/SQL: Statement ignored
ORA-06550: line 247, column 4:
PLS-00904: insufficient privilege to access object
OLAPSYS.CWM2_OLAP_METADATA_REFRESH
ORA-06550: line 247, column 4:
PL/SQL: Statement ignored

Cause:

The APPS user does not have execute privileges on the olapsys objects mentioned in the error message.


Solution:

The APPS user does not have execute privileges on the olapsys objects mentioned in the error message.

SQL> grant execute on OLAPSYS.CWM2_OLAP_METADATA_REFRESH to apps;
SQL> grant execute on OLAPSYS.CWM2_OLAP_CATALOG to apps;
SQL> grant execute on OLAPSYS.CWM2_OLAP_DIMENSION to apps;
SQL> grant execute on OLAPSYS.CWM2_OLAP_CUBE to apps;

Reference:

ZPBREMCWM.SQL Returns Error: PLS-00904: Insufficient Privilege To Access Object [ID 744973.1]

Restart the failed Worker using the adctrl utility in a second Shell session and continue.

u6678700.drv: FAILED: file MSDODPCODE.sql

Action plan to avoid MSDODPCODE.sql job issue:

Issue:

begin
*
ERROR at line 1:
ORA-33292: Insufficient permissions to access analytic workspace APPS.ODPCODE
using the specified access mode.
ORA-06512: at "SYS.DBMS_AW", line 67
ORA-06512: at "SYS.DBMS_AW", line 131
ORA-06512: at "SYS.DBMS_AW", line 977
ORA-06512: at "SYS.DBMS_AW", line 930
ORA-33262: Analytic workspace APPS.ODPCODE does not exist.
ORA-06512: at "SYS.DBMS_AW", line 901
ORA-06512: at "APPS.MSD_AW_LOADER", line 10
ORA-06512: at line 3

Cause:

•The OLAP user account is locked.
•The Analytic Workspace (AW) odpcode does not yet exist

Solution:

To implement the solution, please execute the following steps:

1.Verify the status for the account OLAPSYS, checking that the account is not EXPIRED or LOCKED:

SQL> select username ,account_status from dba_users where username like '%OLAP%';

2.If the above query showed that the user is locked, unlock using the following sql command:

SQL> ALTER USER OLAPSYS IDENTIFIED BY "olapsys" ACCOUNT UNLOCK;

3.Please run the following as APPS User in SQL*Plus to create the analytic workspace (AW) odpcode:

begin
dbms_aw.execute('aw create odpcode');
end; 

4.Stop Database listener and restart the database.

Restart the failed Worker using the adctrl utility in a second Shell session and continue.