Managing Oracle wallets and certificates using orapki utility

Below are the commands to manage oracle wallets and certificates using orapki utility

To create an Oracle wallet:

$ orapki wallet create -wallet wallet_location [-pwd password]

To create an Oracle wallet with auto login enabled:

$ orapki wallet create -wallet wallet_location -auto_login [-pwd password]

To view an Oracle Wallet:
Go To the Wallet Location where cwallet.sso and ewallet.p12 is created

$ orapki wallet display -wallet wallet_location

To view the autologin or password protected oracle wallet:

$ orapki wallet display -wallet wallet_location -pwd <password>

To add a trusted certificate to an Oracle wallet:

$ orapki wallet add -wallet wallet_location -trusted_cert -cert certificate_location -pwd <password>

To add a user certificate to an Oracle wallet:

$ orapki wallet add -wallet wallet_location -user_cert -cert certificate_location -pwd <password>

To remove trusted certificate from an Oracle Wallet:

$ orapki wallet remove -wallet wallet_location -alias 'CN=*.h2hdba.blogspot.com' -trusted_cert -pwd "Welcome1"

To Display Certificate: 

$ orapki cert display -cert <Certificate>

If your oracle wallet is not password protected i.e auto login enabled then do not provide the password or do not pass the password parameter in above commands.


Once the certificates are installed, The "UTL_HTTP" package is been used to make callouts from SQL and PL/SQL and access access data on the Internet:

Run UTL.HTTP test:

SQL> select utl_http.request('https://your_site.com',null,'file:<wallet_location>','<wallet_password>') from dual;

This function returns up to the first 2000 bytes of data retrieved from the given URL.
It can be used directly in SQL queries.

Script to Monitor Current Database Size and its Growth Since Last Run

Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to Monitor Current Database Size and its Growth Since Last Run. 

Download(MonDBSizeGrowth.sh)

Download MonDBSizeGrowth.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 MonDBSizeGrowth.sh

#!/bin/bash

###################################################################################
# Script Name :  MonDBSizeGrowth.sh                                               #
#                                                                                 #
# Description:                                                                    #
# Script to Monitor Current Database Size and its Growth Since Last Run           #
#                                                                                 #
# Usage : sh <script_name> <ORACLE_SID>                                           #
# For example : sh MonDBSizeGrowth.sh ORCL                                        #
#                                                                                 #
# Note : Initially Run this script for 2 Times                                    #
#                                                                                 #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)                       #
###################################################################################

# Initialize variables

INSTANCE=$1
HOST_NAME=`hostname| cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
PREV_PHY_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.prevphy
PREV_LOGI_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.prevlogi
CURR_PHY_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.currphy
CURR_LOGI_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.currlogi
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`
LAST_CAPTURED_DATE_TIME=`grep "Current Database Size" $OUT_FILE |cut -d'-' -f2`

# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   #cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 #cat $LOG_FILE
fi

if [ -s $CURR_PHY_SIZE ]; then
        echo "$LOG_DATE" > $LOG_FILE
        echo "Moving $CURR_PHY_SIZE to Previous $PREV_PHY_SIZE" >> $LOG_FILE
        mv $CURR_PHY_SIZE $PREV_PHY_SIZE
        #cat $LOG_FILE
fi

if [ -s $CURR_LOGI_SIZE ]; then
        echo "$LOG_DATE" > $LOG_FILE
        echo "Moving $CURR_LOGI_SIZE to Previous $PREV_LOGI_SIZE" >> $LOG_FILE
        mv $CURR_LOGI_SIZE $PREV_LOGI_SIZE
        #cat $LOG_FILE
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 #cat $LOG_FILE
 exit
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
    echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 #cat $LOG_FILE
exit
fi

usage()
{
 echo "$LOG_DATE" > $LOG_FILE
    echo "Script To Monitor Current Database Size and its Growth Since Last Run"  >> $LOG_FILE
    echo "Usage   : ksh <script_name> <ORACLE_SID> " >> $LOG_FILE
 echo "For example : ksh $PROGRAM $ORACLE_SID" >> $LOG_FILE
    echo
}

if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 #cat $LOG_FILE
    exit
fi


sqlplus -s '/as sysdba' <<EOF

 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180

 set heading off;
 spool $CURR_PHY_SIZE

 SELECT round(SUM (BYTES / (1014 * 1024 ))) "PHYSICAL_SIZE(MB)" FROM dba_data_files;

 SPOOL OFF;

 spool $CURR_LOGI_SIZE

 SELECT "PHYSICAL_SIZE(MB)" - "FREE_SPACE(MB)" "LOGICAL_SIZE(MB)"
   FROM (SELECT (SELECT round(SUM (BYTES / (1014 * 1024 )))
       FROM dba_data_files) "PHYSICAL_SIZE(MB)", (SELECT round(SUM (BYTES / (1024 * 1024 )))
                   FROM dba_free_space) "FREE_SPACE(MB)"
     FROM DUAL);

 spool off;
 exit;
EOF

CURR_PHY_SIZE_V=`cat $CURR_PHY_SIZE`
CURR_LOGI_SIZE_V=`cat $CURR_LOGI_SIZE`
PREV_PHY_SIZE_V=`cat $PREV_PHY_SIZE`
PREV_LOGI_SIZE_V=`cat $PREV_LOGI_SIZE`

#echo $CURR_PHY_SIZE_V
#echo $CURR_LOGI_SIZE_V
#echo $PREV_PHY_SIZE_V
#echo $PREV_LOGI_SIZE_V

PHY_GROWTH=`expr $CURR_PHY_SIZE_V - $PREV_PHY_SIZE_V`
#echo $PHY_GROWTH

LOGI_GROWTH=`expr $CURR_LOGI_SIZE_V - $PREV_LOGI_SIZE_V` 
#echo $LOGI_GROWTH


sqlplus -s '/as sysdba' <<EOF
 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180

 set heading on;
 SET MARKUP HTML ON SPOOL ON -
 HEAD '<title></title> -
 <style type="text/css"> -
 table { background: #eee; } -
 th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
 td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
 </style>' TABLE "border='1' align='left'" ENTMAP OFF

 spool $OUT_FILE


 PROMPT Hi Team,

 PROMPT
 PROMPT Current Database Size - `date`:
 PROMPT

    SELECT Name "DB_NAME","PHYSICAL_SIZE(GB)", "PHYSICAL_SIZE(GB)" - "FREE_SPACE(GB)" "LOGICAL_SIZE(GB)", "FREE_SPACE(GB)"
  FROM (SELECT (SELECT round(SUM (BYTES / (1014 * 1024 * 1024)))
                  FROM dba_data_files) "PHYSICAL_SIZE(GB)", (SELECT round(SUM (BYTES / (1024 * 1024 * 1024)))
                                                               FROM dba_free_space) "FREE_SPACE(GB)"
          FROM DUAL),v\$database;

 PROMPT <br>
 PROMPT <br>
 PROMPT <br>
 PROMPT Database Growth (in MB) Since $LAST_CAPTURED_DATE_TIME  

 select Name "DB_NAME",$PHY_GROWTH "PHYSICAL_GROWTH(MB)", $LOGI_GROWTH "LOGICAL_GROWTH(MB)"  from dual,v\$database;

 SPOOL OFF
 SET MARKUP HTML OFF

 exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Report : $APPS_ID - Current Database Size and its Growth on $HOST_NAME"
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
#cat $LOG_FILE
#Taking Backup of Output File To keep History On Server
cp $OUT_FILE $OUT_FILE.`date +"%m-%b-%Y:%T"`

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID>

$ cd $HOME/DBA_MON/bin
$ sh MonDBSizeGrowth.sh DEV11G

This script will send the notification with current database size and its growth since last run.

Sample Output:

Query To Monitor Archive Log Shipping and Gap Status on a Standby Database

Please run below query on a Standby Oracle Database

Download(archshipgapmon.sql)

REM +======================================================================+
REM                    
REM File Name: archshipgapmon.sql
REM 
REM Description:
REM   Query To Monitor Archive Log Shipping and Gap Status
REM   on a standby Database
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @archlogapplymon.sql 
REM
REM   
REM +======================================================================+

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') time,
        a.thread#,
        (select max(sequence#) from v$archived_log where archived='YES' and thread#=a.thread#) archived,
        max(a.sequence#) applied,
        (select max(sequence#) from v$archived_log where archived='YES' and thread#=a.thread#)-max(a.sequence#) gap
    from v$archived_log a where a.applied='YES'  group by a.thread#;

Sample Output:

Query To Get Archive Log Apply Rate Speed of a Standby Database

Please run below query on Standby Oracle Database

Download(archlogapplymon.sql)

REM +======================================================================+
REM                    
REM File Name: archlogapplymon.sql
REM 
REM Description:
REM   Query To Get Archive Log Apply Rate Speed of a Standby Database
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @archlogapplymon.sql 
REM
REM   
REM +======================================================================+

set linesize 180
col Values for a70
col Recovery_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recovery_start",
to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values" 
from v$recovery_progress 
where start_time=(select max(start_time) from v$recovery_progress);

Sample Output:

Undo/redo logs filled up due to the following query and produces high number of archive logs

Issue:

Undo/redo logs filled up due to the following query and produces high number of archive logs:

UPDATE QUERY SET OBJVERSION = NVL(OBJVERSION,0) + 1 WHERE ID = :1

Query To find which transactions/sessions consuming the UNDO tablespace

Cause:

Found that this is a bug in Agile on Solaris SPARC (64-bit):

Bug 14661425 : AGILE - ROGUE QUERY PRODUCES HIGH NUMBER OF ARCHIVE LOGS FILLING DATABASE

Solution:

Upgrade to Agile PLM 9.3.3 to resolve the problem.

GA Patch is available for 9.3.1.2:
Patch:14763729: 9.3.1.2.93: AGILE - ROGUE QUERY PRODUCES HIGH NUMBER OF ARCHIVE LOGS FILLING DATABASE

Query To find which transactions/sessions consuming the UNDO tablespace

Query To find which transactions/sessions consuming the UNDO tablespace

Use below query to determine which users/sessions are using and how much UNDO is being used


Download(undotsusage.sql)

REM +======================================================================+
REM                    
REM File Name: undotsusage.sql
REM 
REM Description:
REM   Query To check transaction/query exhausting the UNDO tablespace
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @undotsusage.sql 
REM   
REM +======================================================================+

Clear columns
SET pages 100
SET Lines 280
select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;

With:
  USED_UBLK = Number of undo blocks used
  USED_UREC = Number of undo records used

Script to Start/Stop Oracle EBS Workflow Mailer Components From Backend Database

If somehow, you are not able to access Oracle EBS front-end then you can use below scripts to Start/Stop Oracle EBS Workflow Mailer Components from Back-end Database:

Get the details of Oracle EBS Workflow Mailer Components:

col component_name for a20;
col COMPONENT_STATUS for a20;
select COMPONENT_NAME, COMPONENT_STATUS,Component_Id from fnd_svc_components;

Connect to APPS schema then execute below to Start Workflow Mailer Component:

declare
l_Component_Id number :=10006; --Enter workflow Component_Id which needs to be started
l_errcode number;
l_errstr varchar2(4000);
begin
FND_SVC_COMPONENT.Start_Component(l_Component_Id, l_errcode, l_errstr);
commit;
end;
/

Connect to APPS schema then execute below to Stop Workflow Mailer Component:

declare
l_Component_Id number :=10006; --Enter workflow Component_Id which needs to be stopped
l_errcode number;
l_errstr varchar2(4000);
begin
FND_SVC_COMPONENT.Stop_Component(l_Component_Id, l_errcode, l_errstr);
commit;
end;
/

Check Workflow Notification Preference for FND User

Query To Check Workflow Notification Preference for FND User

select * from fnd_user_preferences
where user_name = 'SYSADMIN'  --Enter FND User Name here
AND PREFERENCE_NAME = 'MAILTYPE';

SELECT name,email_address, 

       nvl(WF_PREF.get_pref(name, 'MAILTYPE'),notification_preference) 

       as "Notification_Preference"

FROM wf_roles
WHERE name = upper('&recipient_role') --Enter FND User Name here

/

Query To Check Notification Status

select * from WF_NOTIFICATIONS
where ORIGINAL_RECIPIENT='SYSADMIN' --Enter FND User Name here

/

Test DBA_DIRECTORY and UTL_FILE_DIR read/write permissions

To test the DBA_DIRECTORY permissions and see if we are able to create the test file:

DECLARE
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'DBA_DIR_NAME', 'filepath_new_file_name.txt', 'W' );
  utl_file.put_line( l_file, 'Here is some text' );
  utl_file.fclose( l_file );
END;

To read the file from DBA_DIRECTORY path:

DECLARE
  l_exists     boolean;
  l_size       integer;
  l_block_size integer;
BEGIN
  utl_file.fgetattr( 'DBA_DIR_NAME', 
                     'filepath_new_file_name.txt', 
                     l_exists, 
                     l_size, 
                     l_block_size );
   if( l_exists )
   then
     dbms_output.put_line( 'The file exists and has a size of ' || l_size );
   else
     dbms_output.put_line( 'The file does not exist or is not visible to Oracle' );
   end if;
END;

Note : To check the utl_file_dir path then replace the DBA_DIRECTORY name with the path in above script

Unregister a Database from RMAN catalog DB

Connect to RMAN catalog Database:

SQL> conn rman/rman@catlogdb
Connected.


Get the details of a database which need to de-register

SQL> SELECT db_key, dbid, name,RESETLOGS_TIME FROM rc_database where NAME like '%DBNAME%';

    DB_KEY       DBID NAME     RESETLOGS_TIME
---------- ---------- -------- ------------------
   3288657 3228736951 DBNAME 08-MAR-16

 
Execute below to Unregister a database from RMAN catalog DB
   
SQL> EXECUTE dbms_rcvcat.unregisterdatabase(db_key,dbid);

Example:
SQL> EXECUTE dbms_rcvcat.unregisterdatabase(3288657,3228736951);

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.