As of EBS ICM Application DBA Online Patching ADOP Analyzer version 200.39
ADOP Analyzer was not updated for 19c databases:
Per 19c Database with Oracle E-Business Suite R12 Known Issues & Solutions ( Doc ID 2662860.1 )
Running ADZDDBCC.sql in 19c database is taking time for Section 26.
Before running report set parameter _px_cdb_view_enabled to false . alter session set “_px_cdb_view_enabled”=false ;
This is run both as part of 12.2 upgrade steps, and whenever you log an SR for ATG/ICM.
Work-around – add the “ALTER SESSION” command towards the top of the ADZDDBCC.sql – otherwise, on a 19c database, you’ll discover that when the dependency check portion runs, which looks like: SELECT col.owner, col.table_name, col.column_name, col.data_type_owner, col.data_type FROM dba_tables tab, dba_tab_columns col WHERE tab.owner IN (SELECT oracle_username FROM fnd_oracle_userid WHERE read_only_flag IN (‘A’, ‘B’, ‘E’, ‘U’)) AND col.owner = tab.owner AND col.table_name = tab.table_name AND col.data_type_owner NOT IN (‘APPS_NE’, ‘SYS’, ‘MDSYS’, ‘SYSTEM’) AND NOT EXISTS (SELECT 1 FROM SYSTEM.fnd_oracle_userid fou, fnd_product_installations fpi, ad_obsolete_objects aoo WHERE fpi.application_id = aoo.application_id AND fou.oracle_id = fpi.oracle_id AND fou.oracle_username = tab.owner AND aoo.object_name = tab.table_name AND aoo.object_type = ‘TABLE’) AND EXISTS (SELECT NULL FROM fnd_oracle_userid au, dba_users du WHERE au.oracle_username = du.username AND au.read_only_flag = ‘U’ AND du.editions_enabled = ‘Y’) <snip>
on 19c databases, you start seeing PX queue waits for every row fetch.
Adding the alter session avoids the queue/dequeue process occurring.
James Lui 2022 OATUG President OATUG Board of Directors
Remember that editioning support introduced the concept of stub objects – those that point to the “real” version of the object when multiple editions are present (which is pretty much all the time in 12.2).
This is why the synonym for an object like APPS.FND_USER points to a current editioned object named APPLSYS.FND_USER#, which may exist in different forms in multiple editions.
The trouble is when you perform a simple grant DDL:
SQL> grant SELECT on APPS.FND_USER to XXCUSTOM;
Even if you have the whole middle-tier services down, or follow the instructions in Granting Privileges On An Object May Cause Invalidations (Doc ID 1987947.1) if you then check your usual source for invalid objects (such as using $AD_TOP/sql/adutlrcmp.sql) you’ll be blessed with hundreds and perhaps thousands of invalid stubs, all of which need to be re-compiled lest you start receiving the dreaded ORA-04062: signature of package “APPS.FND_GLOBAL” has been changed (meaning it was automatically re-compiled while in-use by another process), or ORA-07445: exception encountered: core dump (something was valid, but a bunch of dependent stub objects no longer are valid.)
What I’ve discovered in 12.2.6, with an 11.2.0.4 DB, even if you issue the DDL commands in a proper patching cycle, you still get thousands of invalid stubs. So, really there’s only one solution – use the new API (introduced in patch 19674458:R12.AD.C) and present generally after AD.Delta.6
Syntax:
————
ad_zd.grant_privs(
X_PERMISSIONS in varchar2, /* comma-seperated permission list */
X_OBJECT_NAME in varchar2, /* name of APPS object or APPS synonym */
X_GRANTEE in varchar2, /* grantee schema or role */
X_OPTIONS in varchar2 default NULL); /* grant options, example: ‘WITH
GRANT OPTION’ */
Most administrators become familiar with the FNDLOAD utility ($FND_TOP/bin/FNDLOAD) which is used to move various objects between instances that have been defined and set up using the front-end (Menus, FlexField setups, Users, Alerts, Concurrent Programs, et.al.)
Yes, you can manually re-enter all that information using the front-end forms and OAF pages, but you do have a utility to do that for you: FNDLOAD.
Typing FNDLOAD by itself with no parameters gives you a general idea of what kinds of parameters it takes:
You have not provided the required arguments for this program.
Usage: FNDLOAD logon 0 Y mode configfile datafile [ entity [ param ... ] ]
where
logon is username/password[@connect]
mode is either UPLOAD or DOWNLOAD
configfile is the configuration file
datafile is the data file
entity is an entity name, or - to specify all values in an upload
param is a NAME=VALUE string used for parameter substitution
But you can, like most of these utilities, create a script wrapper which makes the process more driven to support repetitive change migrations.
Here’s an example using many of the various parameters in a format that simply downloads the object from the source instance and uploads the resulting *.ldt file into the target instance. Just comment out things you don’t want (or isn’t supported by your particular EBS version) and add new objects as they become supported by FNDLOAD in the future.
# $1 FRSID From Instance sample: apps/apps@DEV1.world
# $2 TYPE: CONC Concurrent Program
# RGRP Request Group
# FORM Form Function
# FDFF Descriptive FlexField
# VSET FND Value Set
# MENU Menu
# ALRT Oracle Alert
# FLDR Folder
# MESG Oracle Message
# LKUP Lookup Values
# USER FND User
# RPROF Responsibility Profile Level
# PROF Profile with no Level specific
# RSET RequestSet
# RSLK RequestSet Links
# RESP Responsibility
# FPER Form Personalization
# XMLT XML Template
# XLAD XLA Definitions
# WADIINTG Web ADI Integrator
# WADILAYO Web ADI Layout
# WADIMAPC Web ADI Mapping Code
# WADISTYL Web ADI Style
# PJSTAT Project Statuses
# ATTCAT Attachment Categories
# $3 OBJECT Object Short Name, Function Name, Responsibility Key, Etc.
# $4 APPL_SN Application ShortName
# $5 TOSID Destination Instance sample: apps/apps@DEV2.world
#
FRSID=$1
ATYP=$2
AOBJ="$3"
AASN=$4
TOSID=$5
fpwd=`echo $FRSID|awk '{FS="/"} {print $2}'|awk '{FS="@"} {print $1}'`
fsid=`echo $FRSID|awk '{FS="@"} {print toupper($2)}'`
tpwd=`echo $TOSID|awk '{FS="/"} {print $2}'|awk '{FS="@"} {print $1}'`
tsid=`echo $TOSID|awk '{FS="@"} {print toupper($2)}'`
fdbline=`tnsping $fsid|awk '/Attempting/ {print $0}'`
ffullhname=`echo $fdbline|awk '{print $5}'|awk '{FS="="} {print $4}'|awk '{FS=")"} {print $1}'`
fhport=`echo $fdbline|awk '{print $5}'|awk '{FS="="} {print $5}'|awk '{FS=")"} {print $1}'`
fhname=`echo $ffullhname|awk '{print $1}'|awk '{FS="."} {print $1}'`
tdbline=`tnsping $tsid|awk '/Attempting/ {print $0}'`
tfullhname=`echo $tdbline|awk '{print $5}'|awk '{FS="="} {print $4}'|awk '{FS=")"} {print $1}'`
thport=`echo $tdbline|awk '{print $5}'|awk '{FS="="} {print $5}'|awk '{FS=")"} {print $1}'`
thname=`echo $tfullhname|awk '{print $1}'|awk '{FS="."} {print $1}'`
tdbline=`tnsping $tsid|awk '/Attempting/ {print $0}'`
tfullhname=`echo $tdbline|awk '{print $5}'|awk '{FS="="} {print $4}'|awk '{FS=")"} {print $1}'`
thport=`echo $tdbline|awk '{print $5}'|awk '{FS="="} {print $5}'|awk '{FS=")"} {print $1}'`
thname=`echo $tfullhname|awk '{print $1}'|awk '{FS="."} {print $1}'`
sqlsmt=""
sqlsmt=$sqlsmt" select 'XMLT:'||DS_APP_SHORT_NAME||':'||DATA_SOURCE_CODE||': '"
sqlsmt=$sqlsmt" from xdo.xdo_templates_b "
sqlsmt=$sqlsmt" where template_code = '"$AOBJ"' "
sqlsmt=$sqlsmt" and application_short_name = '"$AASN"';"
sqlout=`echo $sqlsmt | sqlplus -s $FRSID`
DSASN=`echo $sqlout|awk '{FS=":"} {print $5}'`
DSCODE=`echo $sqlout|awk '{FS=":"} {print $6}'`
rtime=`date +%m%d%H%M%S`
if (test $ATYP = "CONC") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct "$AOBJ".ldt PROGRAM APPLICATION_SHORT_NAME=$AASN CONCURRENT_PROGRAM_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "FORM") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct "$AOBJ".ldt FUNCTION FUNCTION_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "ATTCAT") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afattach.lct "$AOBJ".ldt FND_DOCUMENT_CATEGORIES CATEGORY_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afattach.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "RGRP") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct "$AOBJ".ldt REQUEST_GROUP APPLICATION_SHORT_NAME=$AASN REQUEST_GROUP_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "FDFF") then
FNDLOAD $FRSID O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct "$AOBJ".ldt DESC_FLEX APPLICATION_SHORT_NAME=$AASN DESCRIPTIVE_FLEXFIELD_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "VSET") then
FNDLOAD $FRSID O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct "$AOBJ".ldt VALUE_SET FLEX_VALUE_SET_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "MENU") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct "$AOBJ".ldt MENU MENU_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=REPLACE
elif (test $ATYP = "ALRT") then
FNDLOAD $FRSID 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct "$AOBJ".ldt ALR_ALERTS APPLICATION_SHORT_NAME=$AASN ALERT_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "FLDR") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/fndfold.lct "$AOBJ".ldt FND_FOLDERS NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/fndfold.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "MESG") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct "$AOBJ".ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=$AASN MESSAGE_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "LKUP") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct "$AOBJ".ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=$AASN LOOKUP_TYPE="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "USER") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct "$AOBJ".ldt FND_USER USER_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "PROF") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct "$AOBJ".ldt PROFILE APPLICATION_SHORT_NAME=$AASN PROFILE_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "RPROF") then
FNDLOAD $FRSID 0 Y DOWNLOAD afscprofr.lct "$AOBJ".ldt PROFILE APPLICATION_SHORT_NAME=$AASN PROFILE_NAME="$AOBJ" FND_PROFILE_OPTION_VALUES LEV="10003"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "RSET") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct "$AOBJ".ldt REQ_SET REQUEST_SET_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct "$AOBJ"_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct "$AOBJ"_LINK.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "RELK") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct "$AOBJ".ldt REQ_SET_LINKS REQUEST_SET_NAME="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "RESP") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct "$AOBJ".ldt FND_RESPONSIBILITY RESP_KEY="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "FPER") then
FNDLOAD $FRSID 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct "$AOBJ".ldt FND_FORM_CUSTOM_RULES function_name="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "XMLT") then
FNDLOAD $FRSID 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct "$AOBJ".ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=$AASN DATA_SOURCE_CODE="$DSCODE" TMPL_APP_SHORT_NAME=$DSASN TEMPLATE_CODE="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
sqlsmt=""
sqlsmt=$sqlsmt"select distinct 'XMLTYPE:'||xdo_file_type||':'||file_content_type||':'||"'\n'
sqlsmt=$sqlsmt" decode(file_content_type,'application/rtf','rtf', "'\n'
sqlsmt=$sqlsmt" 'text/xml', 'xml', "'\n'
sqlsmt=$sqlsmt" lower(xdo_file_type))||':'||territory||':'||replace(file_name,' ','_')||':' xtype"'\n'
sqlsmt=$sqlsmt" from xdo.xdo_lobs "'\n'
sqlsmt=$sqlsmt" where application_short_name = '"$AASN"' "'\n'
sqlsmt=$sqlsmt" and lob_type = 'TEMPLATE_SOURCE' "'\n'
sqlsmt=$sqlsmt" and lob_code = '"$AOBJ"'; "'\n'
sqlsmt=$sqlsmt"exit "'\n'
echo $sqlsmt > $rtime.sql
sqlplus -s $FRSID @$rtime.sql > $rtime.out
for i in `awk '/XMLTYPE/ {print $0}' $rtime.out`
do
ftype=`echo $i|awk '{FS=":"} {print $2}'`
ctype=`echo $i|awk '{FS=":"} {print $3}'`
fext=`echo $i|awk '{FS=":"} {print $4}'`
terr=`echo $i|awk '{FS=":"} {print $5}'`
filename=`echo $i|awk '{FS=":"} {print $6}'|awk '{FS="."} {print $1}'`
sname=`echo $i|awk '{FS=":"} {print $6}'`
if (test $terr = "00") then
fname="TEMPLATE_SOURCE_"$AASN"_"$AOBJ"_en".$fext
else
fname="TEMPLATE_SOURCE_"$AASN"_"$AOBJ"_en_"$terr.$fext
fi
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD $fpwd -JDBC_CONNECTION "$fhname:$fhport:$fsid" -APPS_SHORT_NAME $AASN -LOB_TYPE TEMPLATE_SOURCE -LOB_CODE "$AOBJ" -XDO_FILE_TYPE $ftype -FILE_NAME $sname -LANGUAGE en -TERRITORY $terr -LCT_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE "$filename".log
mv "$fname" "$sname"
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD $tpwd -JDBC_CONNECTION "$thname:$thport:$tsid" -APPS_SHORT_NAME $AASN -LOB_TYPE TEMPLATE_SOURCE -LOB_CODE "$AOBJ" -XDO_FILE_TYPE $ftype -FILE_CONTENT_TYPE "$ctype" -FILE_NAME "$sname" -LANGUAGE en -TERRITORY $terr -LCT_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LDT_FILE "$AOBJ".ldt -DRVX_FILE "$AOBJ".drvx -LOG_FILE "$filename".log -CUSTOM_MODE FORCE
done
elif (test $ATYP = "XMLD") then
sqlsmt=""
sqlsmt=$sqlsmt"select distinct 'XMLTYPE:'||xdo_file_type||':'||file_content_type||':'||"'\n'
sqlsmt=$sqlsmt" decode(file_content_type,'application/rtf','rtf', "'\n'
sqlsmt=$sqlsmt" 'text/xml', 'xml', "'\n'
sqlsmt=$sqlsmt" lower(xdo_file_type))||':'||territory||':'||replace(file_name,' ','_')||':' xtype"'\n'
sqlsmt=$sqlsmt" from xdo.xdo_lobs "'\n'
sqlsmt=$sqlsmt" where application_short_name = '"$AASN"' "'\n'
sqlsmt=$sqlsmt" and lob_type = 'DATA_TEMPLATE' "'\n'
sqlsmt=$sqlsmt" and lob_code = '"$AOBJ"'; "'\n'
sqlsmt=$sqlsmt"exit "'\n'
echo $sqlsmt > $rtime.sql
sqlplus -s $FRSID @$rtime.sql > $rtime.out
for i in `awk '/XMLTYPE/ {print $0}' $rtime.out`
do
ftype=`echo $i|awk '{FS=":"} {print $2}'`
ctype=`echo $i|awk '{FS=":"} {print $3}'`
fext=`echo $i|awk '{FS=":"} {print $4}'`
terr=`echo $i|awk '{FS=":"} {print $5}'`
filename=`echo $i|awk '{FS=":"} {print $6}'|awk '{FS="."} {print $1}'`
sname=`echo $i|awk '{FS=":"} {print $6}'`
fname="DATA_TEMPLATE_"$AASN"_$AOBJ"
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD $fpwd -JDBC_CONNECTION "$fhname:$fhport:$fsid" -APPS_SHORT_NAME $AASN -LOB_TYPE DATA_TEMPLATE -LOB_CODE "$AOBJ" -XDO_FILE_TYPE $ftype -FILE_NAME $sname -LANGUAGE en -TERRITORY $terr -LCT_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LDT_FILE "$AOBJ".ldt -DRVX_FILE "$AOBJ".drvx -LOG_FILE "$filename".log
mv "$fname".xml "$sname"
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD $tpwd -JDBC_CONNECTION "$thname:$thport:$tsid" -LOB_TYPE DATA_TEMPLATE -LOB_CODE "$AOBJ" -XDO_FILE_TYPE $ftype -FILE_NAME "$sname" -APPS_SHORT_NAME $AASN -LANGUAGE en -TERRITORY US -LCT_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LDT_FILE "$AOBJ".ldt -DRVX_FILE "$AOBJ".drvx -LOG_FILE "$filename".log -CUSTOM_MODE FORCE
done
# WADIINTG Web ADI Integrator
# WADILAYO Web ADI Layout
# WADIMAPC Web ADI Mapping Code
# WADISTYL Web ADI Style
elif (test $ATYP = "WADIINTG") then
# FNDLOAD $FRSID 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneint.lct "$AOBJ".ldt BNE_INTEGRATORS INTEGRATOR_ASN=$AASN INTEGRATOR_CODE="$AOBJ"
# FNDLOAD $TOSID 0 Y UPLOAD $BNE_TOP/patch/115/import/bneint.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
FNDLOAD $FRSID 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct "$AOBJ".ldt BNE_INTEGRATORS INTEGRATOR_ASN=$AASN INTEGRATOR_CODE="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "WADILAYO") then
FNDLOAD $FRSID 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnelay.lct "$AOBJ".ldt BNE_LAYOUTS LAYOUT_ASN=$AASN LAYOUT_CODE="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $BNE_TOP/patch/115/import/bnelay.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "WADIMAPC") then
FNDLOAD $FRSID 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnemap.lct "$AOBJ".ldt BNE_MAPPINGS MAPPING_ASN=$AASN MAPPING_CODE="$AOBJ"
FNDLOAD $TOSID 0 Y UPLOAD $BNE_TOP/patch/115/import/bnemap.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "WADISTYL") then
FNDLOAD $FRSID 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bness.lct "$AOBJ".ldt BNE_STYLESHEETS CONTENT_ASN=$AASN STYLESHEET_CODE="$AOBJ" STYLESHEET_ASN=$AASN
FNDLOAD $TOSID 0 Y UPLOAD $BNE_TOP/patch/115/import/bness.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "XLAD") then
sqlsmt=""
sqlsmt=$sqlsmt"select distinct 'APPLID:'||application_id"'\n'
sqlsmt=$sqlsmt" from applsys.fnd_application "'\n'
sqlsmt=$sqlsmt" where application_short_name = '"$AASN"'; "'\n'
sqlsmt=$sqlsmt"exit "'\n'
echo $sqlsmt > $rtime.sql
sqlplus -s $FRSID @$rtime.sql > $rtime.out
for i in `awk '/APPLID/ {print $0}' $rtime.out`
do
APPLID=`echo $i|awk '{FS=":"} {print $2}'`
done
FNDLOAD $FRSID 0 Y DOWNLOAD $MWD_TOP/admin/ldt/xlaaadrule.lct "$AOBJ".ldt XLA_AAD APPLICATION_ID=$APPLID AMB_CONTEXT_CODE=DEFAULT FEDERAL=N
FNDLOAD $TOSID 0 Y UPLOAD $MWD_TOP/admin/ldt/xlaaadrule.lct "$AOBJ".ldt XLA_AAD APPLICATION_ID=$APPLID AMB_CONTEXT_CODE=DEFAULT UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
elif (test $ATYP = "PJSTAT") then
FNDLOAD $FRSID 0 Y DOWNLOAD $PA_TOP/patch/115/import/paprjsta.lct "$AOBJ".ldt PA_PROJECT_STATUSES
FNDLOAD $TOSID 0 Y UPLOAD $PA_TOP/patch/115/import/paprjsta.lct "$AOBJ".ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
FNDLOAD $FRSID 0 Y DOWNLOAD $PA_TOP/patch/115/import/paprjsta.lct "$AOBJ"_CONTROLS.ldt PA_PROJECT_STATUS_CONTROLS
FNDLOAD $TOSID 0 Y UPLOAD $PA_TOP/patch/115/import/paprjsta.lct "$AOBJ"_CONTROLS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
FNDLOAD $FRSID 0 Y DOWNLOAD $PA_TOP/patch/115/import/pacinest.lct "$AOBJ"_ALLOW.ldt PA_NEXT_ALLOW_STATUSES
FNDLOAD $TOSID 0 Y UPLOAD $PA_TOP/patch/115/import/pacinest.lct "$AOBJ"_ALLOW.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
fi
rm $rtime.sql
rm $rtime.out
#chmod 777 *.log
#chmod 777 *.rtf
#chmod 777 *.ldt
#chmod 777 *.xml
During an online patching session with ADOP, when the prepare phase checks the data dictionary integrity, you receive the following error:
R12.2 eBusiness Suite EBS – During ADOP phase = prepare data dictionary corruption missing parent:
Verifying data dictionary.
[UNEXPECTED]Data dictionary corrupted:
[UNEXPECTED]Data dictionary corruption – missing parent
1416131 ORA$BASE APPS BPA_GUIDED_PICKING SYNONYM
1416121 ORA$BASE APPS CO_CLIPPERSHIP_INTERFACE SYNONYM
1416132 ORA$BASE APPS BPA_PRINT_GUIDED_PICK_LABELS SYNONYM
1416128 ORA$BASE APPS GEM_ESIG_INQUIRY_CORE SYNONYM
[UNEXPECTED]Data dictionary corruption detected. Provide details to
[UNEXPECTED]Oracle Support and ask for a bug to be opened against the
[UNEXPECTED]Online Patching component of Oracle Application Install.
[STATEMENT] Please run adopscanlog utility, using the command
“adopscanlog -latest=yes”
Issue:
The corruption is detected with the $AD_TOP/sql/ADZDDBCC.sql scriptis executed just prior to setting up the ADOP session. This is also a mandatory check performed during pre-upgrade readiness checking for R12.2 upgrades.
Solution:
There is a rudimentary, but effective script supplied in $AD_TOP/patch/115/sql named adzddmpfix.sql (and a number of other adzd___ named scripts that address other data dictionary issues, such as the common TimeZone timestamp issues (Doc ID 2017686.1))
This script is run as SYS and requires all middle-tier services to be shutdown because it’s rebuilding all of the APPS synonyms for objects and recompiling afterwards.
sqlplus “/ as sysdba” @$AD_TOP/patch/115/sql/adzddmpfix.sql
REM $Header: adzddmpfix.sql 120.0.12020000.3 2015/09/14 10:10:32 sstomar noship$
REM dbdrv: none
REM +======================================================================+
REM | Copyright (c) 2005, 2015 Oracle and/or its affiliates. |
REM | All rights reserved. |
REM | Version 12.0.0 |
REM +======================================================================+
REM | FILENAME
REM | adzddmpfix.sql
REM |
REM | DESCRIPTION
REM | Warning: this script is an experimental workaround to the Oracle
REM | Database data Dictionary “missing parent” corruption described in
REM | Bug 20627866. This bug currently has no other known fix, but this
REM | script will repair the problem sufficiently to allow adop to continue
REM | executing.
REM |
REM | Warning: You must be connected as SYSDBA with application services
REM | shutdown to run this script
REM | Usage:
REM | sqlplus / as sysdba
REM | @adzddmpfix.sql
REM |
REM |
REM +=======================================================================+
Sample output:
SQL> @adzddmpfix.sql
“—- Fixing Data Dictionary Corruptions (missing parent) —-”
4 rows deleted.
Commit complete.
System altered.
“—- Compiling invalids —-”
Don’t forget to re-run the $AD_TOP/sql/ADZDDBCC.sql script to identify whether the data dictionary corruption is still present afterwards.
This file which is normally executed under the adautocfg.sh wrapper script in $ADMIN_SCRIPTS_HOME, can also be executed manually to bypass certain limitations found when configuring the PATCH filesystem, in particular.
The format of the command is:
adconfig.sh run=INSTE8 contextfile=$CONTEXT_FILE
Sample output:
Enter the APPS user password: Script execution phase specified: INSTE8
The log file for this session is located at: /u01/oracle/fs1/inst/apps/$ENV_ID/admin/log/02120957/adconfig.log
AutoConfig is configuring the Applications environment...
AutoConfig will consider the custom templates if present.
Using CONFIG_HOME location : /u01/oracle/fs1/inst/apps/$ENV_ID
Classpath : /u01/oracle/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar:/u01/oracle/fs1/EBSapps/comn/java/classes
Using Context file : /u01/oracle/fs1/inst/apps/$ENV_ID/appl/admin/$ENV_ID.xml
Context Value Management will now update the Context file
Updating Context file...COMPLETED
Execution phase specified at run time: INSTE8
No uploading of Context File and its templates to database.
Configuring templates from all of the product tops...
Configuring AD_TOP........COMPLETED
Configuring FND_TOP.......COMPLETED
Configuring ICX_TOP.......COMPLETED
Configuring MSC_TOP.......COMPLETED
Configuring IEO_TOP.......COMPLETED
Configuring BIS_TOP.......COMPLETED
Configuring CZ_TOP........COMPLETED
Configuring SHT_TOP.......COMPLETED
Configuring AMS_TOP.......COMPLETED
Configuring CCT_TOP.......COMPLETED
Configuring WSH_TOP.......COMPLETED
Configuring CLN_TOP.......COMPLETED
Configuring OKE_TOP.......COMPLETED
Configuring OKL_TOP.......COMPLETED
Configuring OKS_TOP.......COMPLETED
Configuring CSF_TOP.......COMPLETED
Configuring IBY_TOP.......COMPLETED
Configuring JTF_TOP.......COMPLETED
Configuring MWA_TOP.......COMPLETED
Configuring CN_TOP........COMPLETED
Configuring CSI_TOP.......COMPLETED
Configuring WIP_TOP.......COMPLETED
Configuring CSE_TOP.......COMPLETED
Configuring EAM_TOP.......COMPLETED
Configuring GMF_TOP.......COMPLETED
Configuring PON_TOP.......COMPLETED
Configuring FTE_TOP.......COMPLETED
Configuring ONT_TOP.......COMPLETED
Configuring AR_TOP........COMPLETED
Configuring AHL_TOP.......COMPLETED
Configuring IES_TOP.......COMPLETED
Configuring OZF_TOP.......COMPLETED
Configuring CSD_TOP.......COMPLETED
Configuring IGC_TOP.......COMPLETED
AutoConfig completed successfully.
alter session set current_schema=APPS;
exec AD_ZD_PREP.ENABLE_CUSTOM_USER(‘XXCUSTOMSCHEMA’);
/* Create your SELECT grants to the editioned objects as APPS user */
grant select on ap.ap_suppliers# to XXCUSTOMSCHEMA;
/* OR if you’re running newer than 12.2.5…the editioning security will cause hundreds of invalid editioned object stubs that need re-compilation unless you instead use */
/* Create your custom view referencing the APPS synonym – which points to the editioned object */
create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM apps.ap_suppliers ;
This is the same process when creating procedures and functions with the usual caveat that unless it’s a 12c database, you cannot create materialized views on the editioned objects.
For the 11gR2 EBS users, you would stick with the existing non-editioned table object reference:
CREATE MATERIALIZED VIEW APPS.XXAP_SUPPLIER_NEW_MV
(
vendor_id,
vendor_name
)
AS
SELECT aps.vendor_id, aps.vendor_name
FROM ap.ap_suppliers aps;
/* The owning schema for the materialized view must have CREATE TABLE, VIEW AND INDEX privileges on the destination tablespace */
People ask what do I look for when monitoring an EBS instance for availability? Oracle Enterprise Manager (OEM) with the Applications Management Pack (AMS) gives a good starting point, but here is a condensed list of my general health check items for my production instance. It’s not everything possible, but it’s what I generally would check during a shake-down of a disaster-recovery exercise.
Oracle OpenWorld 2016 Cloud Services and Products Summary
4 Days (almost 7 for me) of Product Directors and Managers spinning as many plates in the air as humanly possible, all being tossed into the Cloud with a fervor only matched by the lack of compensation being provided for conventional technology stack sales, left me with some specific value-adds for my time spent (not including the Safe Harbor information, we can’t say anything about….sshhhh.)
Note: (I’m EBS-centric and we have very little budget for hardware, so while I was amazed at the new SPARC S7 processors with on-board encryption and J/SQL optimization, alas they didn’t make it into my summary pages this year.)
AD/TXK Delta.8
Oracle E-Business Suite Applications DBA and Technology Stack Release Notes for R12.AD.C.Delta.8 and R12.TXK.C.Delta.8 (Doc ID 2159750.1)
Configuring Oracle E-Business Suite Release 12.x Using Logical Host Names (Doc ID 1968231.1)
e-Mail Notifications at Parent and Child Levels for Request Sets
Save and Re-use Concurrent Program Parameters (CP Templates)
Regex Support in Profile Option Values and FND_MESSAGES
Multiple OA Framework Windows and Tabs in Multiple Browser Windows
EBS Healthcheck Diagnostics
64-bit JDK’s on CM Tier
adop Improvements
Delta Sync Mode (syncmode=patch/delta) – changed rows only
Rsync support in phase=prepare
Custom module sync auto-supported (no custom drivers req’d)
Collecting Disconnected Mode OCM data for e-Business Suite R12.2
per MOS Document ID 1447334.1 - Oracle E-Business Suite Release 12.2: Release Notes for Oracle Configuration Manager
MOS Oracle Configuration Manager Collector Download screen.
This method is used whenever your EBS instance is behind a firewall and no proxy server is available to reach the OCM collector end-point (ccr.oracle.com)
The reason you want to use OCM is not only because it provides a complete catalog of your Oracle software versions, modules and plug-ins, so that they can easily be associated with a Service Request (SR) instead of the analyst asking for every RDA script being run for every component in your application and database.
It also enables the full functionality of that Systems Tab in My Oracle Support (buried just above your Settings menu) that allows you to do light versions of lifecycle management comparisons (such as what the instance looked like 2 weeks ago versus now) without having to own the OEM Lifecycle Management license. It comes compliments of your support agreement.
This also enables the full functionality of the patch and security recommendations section vital towards identifying high priority one-off patches specific to your particular configuration and platform.
Basically, instead of having a cron job periodically automatically upload the configurations to ccr.oracle.com, you will be creating the same staged configuration file, but uploading it to your Customer Support Identifier (CSI) via a Draft Service Request (SR) or a currently Open SR via the File Attachment... feature of the SRs.
In the background of MOS is a managed file transfer handler that looks for the specifically named "ocmconfig.jar" files and when detected, route them over to the Configuration Manager engine for automatic uploading to your CSI account.
While not as convenient as the automatic cron method, for those of use with no direct internet connectivity to ccr.oracle.com, our OEM and MOS functionalities become quite limited if the configurations aren't present under our CSI's.e.g. ORACLE_BASE=/u01/app/oracle
# Oracle DB Home cd $ORACLE_HOME unset ORACLE_CONFIG_HOME export JAVA_HOME=$ORACLE_HOME/jdk cd $ORACLE_HOME/ccr # Clear prior CCR installation, if required. rm -rf $ORACLE_HOME/ccr/* cd $ORACLE_HOME unzip <patch_stage_dir>/p5567658_<ver>_<platform>.zip cd $ORACLE_HOME/ccr/bin # Run setupCCR in Disconnected mode ./setupCCR -s -d # Create ORACLE_OCM user and related grants . $ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig -s <SID> -r SYS # Additional grants for APPS EBS Collector . $ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh ebs_collectconfig -u <APPSUSERNAME> # Run the collection and generate the ocmconfig.jar cd $ORACLE_HOME/ccr/bin ./emCCR collect # ocmconfig.jar location $ORACLE_HOME/ccr/hosts/$HOSTNAME/state/upload # Oracle 10.1.2 iAS Home . $ORACLE_BASE/EBSapps.env run export ORACLE_HOME=$ORACLE_HOME export ORACLE_CONFIG_HOME=$INST_TOP/ora/10.1.2 export JAVA_HOME=$ORACLE_HOME/jdk # Remove prior OCM config, if needed rm -rf $ORACLE_HOME/ccr/* rm -rf $ORACLE_CONFIG_HOME/ccr/* cd $ORACLE_HOME unzip <patch_stage_dir>/p5567658_<ver>_<platform>.zip # Bugfix specific to HP-UX Itanium - verify file permissions to execute chmod 754 $ORACLE_HOME/ccr/bin/deployPackages chmod 754 $ORACLE_HOME/ccr/bin/emSnapshotEnv cd $ORACLE_HOME/ccr/bin ./setupCCR -s -d # Disconnected mode # Run the collection and generate the ocmconfig.jar cd $ORACLE_HOME/ccr/bin ./emCCR collect # ocmconfig.jar location $INST_TOP/ora/10.1.2/ccr/state/upload #Repeat for patch filesystem; . $ORACLE_BASE/EBSapps.env patch # Oracle FMW WebLogic Home # source the Domain environment . $ORACLE_BASE/EBSapps.env run . $EBS_DOMAIN_HOME/bin/setDomainEnv.sh export ORACLE_HOME=$FMW_HOME/utils export ORACLE_CONFIG_HOME=$INST_TOP/ora/FMW # Remove prior OCM config, if needed rm -rf $ORACLE_HOME/ccr/* rm -rf $ORACLE_CONFIG_HOME/ccr/* cd $ORACLE_HOME unzip <patch_stage_dir>/p5567658_<ver>_<platform>.zip # Bugfix specific to HP-UX Itanium - verify file permissions to execute chmod 754 $ORACLE_HOME/ccr/bin/deployPackages chmod 754 $ORACLE_HOME/ccr/bin/emSnapshotEnv cd $ORACLE_HOME/ccr/bin ./setupCCR -s -d # Disconnected mode # Run the collection and generate the ocmconfig.jar cd $ORACLE_HOME/ccr/bin ./emCCR collect # ocmconfig.jar location $INST_TOP/FMW/ccr/state/upload #Repeat for patch filesystem; . $ORACLE_BASE/EBSapps.env patch # Upload all of your ocmconfig.jar files (can be named uniquely - e.g. mydbocmconfig.jar) per MOS Document ID 763142.1 - How to upload the collection file ocmconfig.jar to My Oracle Support for Oracle Configuration Manager (OCM) running in Disconnected Mode.
# Also can ZIP up all of them at once and upload them to your Draft SR (or current open SR as long as you're happy with the CSI (Customer Support Identifier) that will receive the new configurations.
# Trick is have the string "ocmconfig" and .jar as the extension of each file
Have fun with your new Systems tab in My Oracle Support!
HTTP Server Error “Child Could Not Open Sslmutex Lockfile” (Doc ID 562624.1) addresses a non-critical, but annoying issue (because it tends to fill up the $INST_TOP/logs/ora/10.1.3/Apache directory with big error logs every day – every mouse click on a OA Framework page generates an error entry).
mod_ossl: Child could not open SSLMutex lockfile /apps/oracle/oas/10.1.3.1/Apache/Apache/logs/ssl_mutex.10549 (System error follows)
System: No such file or directory (errno: 2)
And sort of randomly, your HTTP service will show “Stop” on the adapcctl.sh status screen. But it’s probably still actually running (the processes still show up when ps -ef | grep httpd – and the OA Framework still seems to be responding).
applmgr@myhostname$> $SCRIPT_TOP/adapcctl.sh status
You are running adapcctl.sh version 120.7.12010000.2
Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...
Processes in Instance: ENV_ID.myhostname
---------------------------------+--------------------+---------+---------
ias-component | process-type | pid | status
---------------------------------+--------------------+---------+---------
OEM will show the HTTP service as being down, even though the pages are probably still responding:
R12 EBS OEM AMS monitor HTTP Service down screenshot
Implementation for e-Business Suite R12 is relatively easy (just modify 2 configuration files and bounce Apache), but making sure the changes stick after AutoConfig runs means you do need to learn about the configuration templates in $FND_TOP/admin/templates.
These are the skeletal files used during AutoConfig (adautocfg.sh) that regenerate all those files used to control the middleware and database software configuration. These are the files you that contain warning headers written into each and every one of them warning you not to make direct changes to the configuration file itself like:
# ###############################################################
#
# This file is automatically generated by AutoConfig. It will be read and
# overwritten. If you were instructed to edit this file, or if you are not
# able to use the settings created by AutoConfig, refer to Metalink Note
# 387859.1 for assistance.
#
# ###############################################################
)
## Execute on every R12 MT server running HTTP/web services – Apache downtime required during change.
# Stop Apache
$SCRIPT_TOP/adapcctl.sh stop
# Before change
applmgr@myhostname $> cd $INST_TOP/ora/10.1.3/Apache/Apache/conf
applmgr@myhostname $> grep mutex *
As mentioned in the MOS document (Doc ID 562624.1), you may need to use ipcs -a to view hanging semaphore processes after making this change, and use ipcrm -s to kill the hanging processes if Apache refuses to startup after making this change and a different issue causes Apache’s processes to hang.
Every so often, system load on an e-Business Suite instance ramps up and response time to users starts climbing, often resulting in user observed errors such as:
FRM-92100 Your connect to the server has been interrupted
FRM-92102 A network error has occurred
That dreaded FRM-91201 / FRM-91200 error causing you to restart your session.
Or sometimes, the screen just freezes (aka spins, stops, is broken, stuck, motionless, looks like a screen saver,can’t do anything, won’t work, froze-up, etc.) and the person has to close their browser, or even shut-down their workstation and restart.
It’s simply not doing anything – Nothing to see here, just move your cursor around. And wait… and wait.
Old technology often barks with unrelated error messages to the actual cause. If there’s a lot going on with concurrent requests, or interfaces, or analytic extracts running, the front-end response-time slows down, sometimes sufficiently to trigger these kinds of Form errors, even though technically there was no interruption to the network connectivity, either between the hosts, nor the workstation and the middle-tier application server.
However, on the database, the user-experience can be seen, although not necessarily in the place you might expect. OEM had introduced it’s Adaptive Metric Thresholds technology back in OEM 11g (in a slightly different place than in 12c (in Oracle Management Server/OMS 12.1.0.4.0). In OEM 11g, they were a link under the AWR Baseline Reports page.
See the Baseline Metric Thresholds link at the bottom.
Where those adaptive metric thresholds moved in 12c.
On this page and in the list of Baseline Metrics, when you click into them, you can access the trending statistics being gathered for each metric. Many times this will provide direct insight into what a user experiences as the “the system is frozen” translates into “the back-end database response time is incredibly bad.”
See the spikes around 7AM and 11:30AM? Those are being associated with “System Froze” reports.
In the example here, the database experienced a dramatic slow-down in response almost 5 to 10 times slower than usual, which only lasted a few seconds. But that can be enough to show up in many users’ sessions who might have just kicked off a query, or were trying to save something. Based upon the information gathered, we set the Warning and Critical thresholds to 1500ms and 2000ms respectively to start sending e-mail alert notifications upon breach of the levels. If the settings are left at “None”, no incident would be raised, and thus, no notification would be sent.
If you’re experiencing odd transient outages or sluggish behavior that defies the normal AWR and ADDM snapshot analysis, go take a look at what OEM has been gathering in the background over time and see if the statistics correlate to any of your issues. There’s value in that data. Just mine it.
With the forthcoming (but already available) SQLDeveloper 4.1 edition, an improved version of the Oracle Data Miner tools is incorporated into the SQLDeveloper console. However, I found that there were a number of steps needed to actually use this new data modeling product other than just responding ‘Yes’ to the “Do you wish to enable the Data Miner Repository on this database?” prompt.
Here’s what I ended up doing to get things up and running (so that I could play with data modeling and visualization using Excel and the new SQLDeveloper DM extensions.)
#In this case, I’m adding back the demonstration data (i.e. EMP, DEPTNO type tables; the SH, OE, HR, et.al. schemas) into an existing R12 e-Business Suite (12.1.3) instance.
# Installing the Oracle Demo data in an R12 instance.
# Use the runInstaller from the R12 $ORACLE_HOME
cd $ORACLE_HOME/oui/bin
export DISPLAY=<workstation IP>:0.0
./runInstaller
# Choose the source products.xml from the staging area – Download and stage the DB Examples CD from OTN
/mnt/nfs/database/11203/examples/stage/products.xml
# Complete the OUI installation through [Finish]
cd $ORACLE_HOME/demo/schema
mkdir -p $ORACLE_HOME/demo/schema/log
echo $ORACLE_HOME/demo/schema/log/ ## used to respond to the Log Directory prompt during mksample.sql
sqlplus “/ as sysdba”
— will need passwords for: SYS/SYSTEM and APPS (used for all of the demo schemas, some of which pre-exist such as, HR, OE (PM, IX, SH and BI were okay for 12.1.3).
— ## Be sure to comment out any DROP USER <HR, OE, etc.) commands in this script (or you will be restoring your EBS instance from a backup because it just dropped your Module schema tables…) ##
— They look like this:
/*
mksample.sql:– DROP USER hr CASCADE;
mksample.sql:– DROP USER oe CASCADE;
mksample.sql:DROP USER pm CASCADE;
mksample.sql:DROP USER ix CASCADE;
mksample.sql:DROP USER sh CASCADE;
mksample.sql:DROP USER bi CASCADE;
*/
–Similarly – if/when you decide you no longer need the data – do NOT just use the $ORACLE_HOME/demo/schema/drop_sch.sql script
–or you just dropped your HR/OE/BI EBS schemas; don’t do that.
/*
drop_sch.sql:PROMPT Dropping Sample Schemas
drop_sch.sql:– DROP USER hr CASCADE;
drop_sch.sql:– DROP USER oe CASCADE;
drop_sch.sql:DROP USER pm CASCADE;
drop_sch.sql:DROP USER ix CASCADE;
drop_sch.sql:DROP USER sh CASCADE;
drop_sch.sql:DROP USER bi CASCADE;
order_entry/oe_main.sql:– Dropping the user with all its objects
order_entry/oe_main.sql:– DROP USER oe CASCADE;
order_entry/oe_main.sql:– ALTER USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs;
*/
— in this instance the $APPS_PW is synchronized to all application module schemas (i.e. AR, HR, GL, etc.)
— log directory would be the actual path from echo $ORACLE_HOME/demo/schema/log/ (including the trailing slash)
SQL> @mksample.sql
# to additionally create the Data Mining user (DM in this case)
create user &&dmuser identified by &&dmuserpwd
default tablespace &&usertblspc
temporary tablespace &&temptblspc
quota unlimited on &&usertblspc;
GRANT CREATE JOB TO &&dmuser;
GRANT CREATE MINING MODEL TO &&dmuser; — required for creating models
GRANT CREATE PROCEDURE TO &&dmuser;
GRANT CREATE SEQUENCE TO &&dmuser;
GRANT CREATE SESSION TO &&dmuser;
GRANT CREATE SYNONYM TO &&dmuser;
GRANT CREATE TABLE TO &&dmuser;
GRANT CREATE TYPE TO &&dmuser;
GRANT CREATE VIEW TO &&dmuser;
GRANT EXECUTE ON ctxsys.ctx_ddl TO &&dmuser;
GRANT CREATE ANY DIRECTORY TO &&dmuser;
— Grant the SH Demo table and package objects to the DM user
@?/rdbms/demo/dmshgrants.sql &&dmuser
connect &&dmuser/&&dmuserpwd
— Create the Data Mining Views against the SH Demo table and package objects
@?/rdbms/demo/dmsh.sql
@?/rdbms/demo/dmabdemo.sql — Builds the Adaptive Baynes Model demo
@?/rdbms/demo/dmaidemo.sql — Builds the Attribute Importance demo
@?/rdbms/demo/dmardemo.sql — Builds the Association Rules demo
@?/rdbms/demo/dmdtdemo.sql — Builds the Decision Tree demo
@?/rdbms/demo/dmdtxvlddemo.sql — Builds the Cross Validation demo
@?/rdbms/demo/dmglcdem.sql — Builds the Generalized Linear model demo
@?/rdbms/demo/dmglrdem.sql — Builds the General Linear Regression model demo
@?/rdbms/demo/dmhpdemo.sql — not a Data Mining program – Hierarchical Profiler
@?/rdbms/demo/dmkmdemo.sql — Builds the K-Means Clustering model demo
@?/rdbms/demo/dmnbdemo.sql — Builds the Naive Baynes Model data
@?/rdbms/demo/dmnmdemo.sql — Builds the Non-negative Matrix Factorization model
@?/rdbms/demo/dmocdemo.sql — Builds the O-Cluster model Demo
@?/rdbms/demo/dmsvcdem.sql — Builds the Support Vector Machine model demo
@?/rdbms/demo/dmsvodem.sql — Builds the One-Class Support Vector Machine model demo
@?/rdbms/demo/dmsvrdem.sql — Builds the Support Vector Regression model demo
@?/rdbms/demo/dmtxtfe.sql — Builds the Oracle Text Term Feature Extractor demo
@?/rdbms/demo/dmtxtnmf.sql — Builds the Text Mining Non-Negative Matrix Factorization model demo
@?/rdbms/demo/dmtxtsvm.sql — Builds the Text Mining Support Vector Machine model demo
## End of Data Mining Demo user (DM) setup and configuration for use of Oracle Demo Data
If you Care a Little More, Things Happen. Bees can be dangerous. Always wear protective clothing when approaching or dealing with bees. Do not approach or handle bees without proper instruction and training.
During an online patching session with ADOP, when the prepare phase checks the data dictionary integrity, you receive the following error:
Thoughts from James H. Lui 
You must be logged in to post a comment.