Tag Archives: Suite

How To Get Those Customizations to Work Correctly with Oracle EBS R12.2 Editioning (EBR)

Oracle e-Business Suite R12.2 Overview of Edition-Based Redefinition (EBR) in 11gR2 databases.
Oracle e-Business Suite R12.2 Overview of Edition-Based Redefinition (EBR) in 11gR2 databases.

Scenario:
Creating a new view based upon the AP.AP_SUPPLIERS table to reside in a new XXCUSTOMSCHEMA.

If you attempt the simple:

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM ap.ap_suppliers ;

It works, but your data and table definition may change whenever patching editions are in-play.

If you attempt to just reference the editioned object instead:

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM ap.ap_suppliers# /* or the preferred apps.ap_suppliers synonym */

You will receive the ominous ORA-38818: illegal reference to editioned object error message.

What’s missing? The XXCUSTOMSCHEMA isn’t recognized as an edition-friendly schema by Oracle’s e-Business Suite (nor the database), yet.

Create your XXCUSTOMSCHEMA in the usual way:

create user XXCUSTOMSCHEMA
identified by (password)
default tablespace USERS
temporary tablepace TEMP
profile DEFAULT
account UNLOCK;

/* as APPS user */

exec FND_ORACLE_USER_PKG.LOAD_ROW(‘XXCUSTOMSCHEMA’, ‘CUSTOM’, ‘INVALID’, NULL, ‘N’, ‘B’);

/* as SYSTEM user */

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;

/* 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 */

R12.2 adop phase fs_clone fails because it can’t copy a file.

Complexity leading to fragility
Complex Systems Integration (Courtesy of: https://www.mulesoft.com/resources/esb/business-process-integration)

Oracle…sometimes blinding in complexity, and a foreshadowing of what happens when a system becomes so complex that it becomes fragile.

This is what the screen session looks like:

adnodemgrctl.sh: check the logfile /u01/oradev/fs1/inst/apps/DEV2_itsrv33m/logs/appl/admin/log/adnodemgrctl.txt for more information …
[UNEXPECTED]fs_clone has failed.
[STATEMENT] Please run adopscanlog utility, using the command
“adopscanlog -latest=yes”
to get the list of the log files along with snippet of the error message corresponding to each log file.
adop exiting with status = 2 (Fail)

Here is what it looks like in the adop.log

[EVENT]     Performing CLONE steps.
[PROCEDURE] [START 2016/11/08 08:20:19] CLONE Patch File System from Run File System
[PROCEDURE] Calling: /u01/oradev/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl
[EVENT]     Log: /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m
[UNEXPECTED]Error occurred running “perl /u01/oradev/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl -contextfile=/u01/oradev/fs2/inst/apps/DEV2_itsrv33m/appl/admin/DEV2_itsrv33m.xml -patchcontextfile=/u01/oradev/fs1/inst/apps/DEV2_itsrv33m/appl/admin/DEV2_itsrv33m.xml -promptmsg=hide -console=off -mode=create -sync_mode=copy -sessionid=25 -timestamp=20161108_081645 -outdir=/u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m”
[UNEXPECTED]occurred during CLONE Patch File System from Run File System, running command: “perl /u01/oradev/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl -contextfile=/u01/oradev/fs2/inst/apps/DEV2_itsrv33m/appl/admin/DEV2_itsrv33m.xml -patchcontextfile=/u01/oradev/fs1/inst/apps/DEV2_itsrv33m/appl/admin/DEV2_itsrv33m.xml -promptmsg=hide -console=off -mode=create -sync_mode=copy -sessionid=25 -timestamp=20161108_081645 -outdir=/u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m”.
[EVENT]     Releasing managed server ports.
[PROCEDURE] [START 2016/11/08 08:30:55] Releasing ports
[STATEMENT] Calling task: “Releasing ports on itsrv33m”; script: “/u01/oradev/fs2/inst/apps/DEV2_itsrv33m/admin/install/txkCloneAcquirePort.pl”; args: ” -option=close -server=itsrv33m.mwd.h2o -ports=5570,7015,7615,7415,7215,7216,7217,7815,6114,6214,10013 -log=/u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/txkCloneAcquirePort.log”
[PROCEDURE] [START 2016/11/08 08:30:56] Releasing ports on itsrv33m
[PROCEDURE] Calling: /u01/oradev/fs2/inst/apps/DEV2_itsrv33m/admin/install/txkCloneAcquirePort.pl
[EVENT]     Log: /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/txkCloneAcquirePort.log
[PROCEDURE] [END   2016/11/08 08:30:57] Releasing ports on itsrv33m
[PROCEDURE] [END   2016/11/08 08:30:57] Releasing ports

How cryptic!  Looks complicated.  This system happens to have AD/TXK.Delta.8, so the logs related to adop are all being centralized under the session # (in this case “25”.)

Diving deep into the log subdirectories:

/u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/TXK_SYNC_create/txkADOPPreparePhaseSynchronize.log

================================
Inside copyComnUtil()…
================================

Directory /u01/oradev/fs1/EBSapps/comn already exists.
Removing the directory: /u01/oradev/fs1/EBSapps/comn/util

Copying the directory
———————
SOURCE : /u01/oradev/fs2/EBSapps/comn/util
TARGET : /u01/oradev/fs1/EBSapps/comn/util

Execute SYSTEM command : cp -rH  /u01/oradev/fs2/EBSapps/comn/util /u01/oradev/fs1/EBSapps/comn/util
EXIT STATUS: 1
Copied /u01/oradev/fs2/EBSapps/comn/util into /u01/oradev/fs1/EBSapps/comn/utilLOG FILE: /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/TXK_SYNC_create/cp_rH.out

<snip>
======================================
Inside copyCloneLogsToFSNE()…
======================================

Creating the directory: /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/TXK_SYNC_create/fsclone_apply

Copying the directory
———————
SOURCE : /u01/oradev/fs2/inst/apps/DEV2_itsrv33m/admin/log/clone
TARGET : /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/TXK_SYNC_create/fsclone_apply

/u01/oradev/fs2/EBSapps/comn/adopclone_itsrv33m/bin/adclone.pl did not go through successfully.
LOG DIRECTORY: /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/TXK_SYNC_create/fsclone_apply.
*******FATAL ERROR*******
PROGRAM : (/u01/oradev/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl)
TIME    : Tue Nov  8 08:30:55 2016
FUNCTION: main::runFSCloneApply [ Level 1 ]
ERRORMSG: /u01/oradev/fs2/EBSapps/comn/adopclone_itsrv33m/bin/adclone.pl did not go through successfully.

/u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/TXK_SYNC_create/fsclone_apply/FSCloneApplyAppsTier_11080821.log

(One innocuous, but annoying file, owned by someone other than applmgr, and happened to be 600 (user read/write only):)

Done copying /u01/oradev/fs2/EBSapps/appl/wms to /u01/oradev/fs1/EBSapps/appl/wms/
Copying /u01/oradev/fs2/EBSapps/appl/gmf to /u01/oradev/fs1/EBSapps/appl/gmf/
WARNING:
.end std out.
cp: cannot open /u01/oradev/fs2/EBSapps/appl/mwd/12.0.0/sql/MWDPOLPO.xsl: Permission denied

.end err out.
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/mwd failed
ERROR: Copying failed, so stopping all the parallel threads
Done copying /u01/oradev/fs2/EBSapps/appl/okc to /u01/oradev/fs1/EBSapps/appl/okc/
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/okc failed
Done copying /u01/oradev/fs2/EBSapps/comn/java/classes/oracle/apps/pqh to /u01/oradev/fs1/EBSapps/comn/java/classes/oracle/apps/pqh/
WARNING: Copying of /u01/oradev/fs2/EBSapps/comn/java/classes/oracle/apps/pqh failed
Done copying /u01/oradev/fs2/EBSapps/appl/gmf to /u01/oradev/fs1/EBSapps/appl/gmf/
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/gmf failed
Done copying /u01/oradev/fs2/EBSapps/comn/java/classes/oracle/apps/ies to /u01/oradev/fs1/EBSapps/comn/java/classes/oracle/apps/ies/
WARNING: Copying of /u01/oradev/fs2/EBSapps/comn/java/classes/oracle/apps/ies failed
Done copying /u01/oradev/fs2/EBSapps/appl/msc to /u01/oradev/fs1/EBSapps/appl/msc/
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/msc failed
Done copying /u01/oradev/fs2/EBSapps/appl/ams to /u01/oradev/fs1/EBSapps/appl/ams/
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/ams failed
Done copying /u01/oradev/fs2/EBSapps/appl/cs to /u01/oradev/fs1/EBSapps/appl/cs/
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/cs failed
START: Inside exitClone….
Updating status INCOMPLETE for ApplyApplTop
START: Updating status INCOMPLETE for action ApplyApplTop
END: Updated status INCOMPLETE for action ApplyApplTop

One file.  A single file.  Could have been a hidden file. Could have been anywhere in the entire AppsTier filesystem.  This one file stopped ADOP cold from performing.  This happened to be phase=fs_clone, but could have been a patching session, or a post-clone config (adcfgclone.pl) session.

Remove the file, and:

The fs_clone phase completed successfully.
adop exiting with status = 0 (Success)

Complexity leads to unintentional fragility.

Monitoring Oracle e-Business Suite (R12.2)

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.

Warning Critical
Oracle E-Business Suite
Oracle Applications Infrastructure Service
Database Instance select SYSDATE from dual; Up/Down
Tablespace Freespace Available >80% >90%
Temporary Tablespace Used >80% >90%
Undo Tablespace Used >80% >90%
Fast Recovery Area Utilization >80% >90%
Redo Log Switch Rate > 10/hr. >20/hr
Blocking Sessions >1 >5
Database Listener tnsping <ORACLE_SID> Up/Down
TNSPing Response Time > 25ms > 100ms
Applications Listener tnsping <FNDFS_ORACLE_SID> Up/Down
Host Monitoring
CPU Utilization > 80% > 95%
RAM Utilization > 90% > 99%
Mountpoint Free Space Available <10% <5%
Concurrent Processing Services
Internal Manager Up/Down
Standard Manager < 10
Output Post Processor < 3
Receiving Transactions Manager < 1
Document Approval Manager < 1
Forms Applications Service
Forms Listener Service Up/Down
Forms Runtime Processes <1
Forms Response Time >5 secs >30 secs
Active Forms Sessions > 100 > 200
Self-Service Applications Service
HTTP Listener Service Up/Down
OPMN Processor Up/Down
OC4J Service Up/Down
WebLogic Deployed Services Up/Down
WebLogic AdminServer Up/Down
OA Framework Page Response Time > 5 secs > 10 secs
Oracle Applications JVM Usage >80% >90%
Workflow Serivce OAM Dashboard Up/Down
Workflow Agent Listener Up/Down
Workflow Background Engine Up/Down
Workflow Notification Mailer Services Up/Down
Oracle E-Business Suite Custom Objects
Invalid Object Count > 482 > 500
Oracle E-Business Suite Patch Information
Patch Inventory
ADOP Session Status >1 Pending >1 Failed

Investigating an OEM 12c E-Business Suite Alert (AMS 12.1.0.x)

OEM 12 Home Page
OEM 12 Home Page

The Applications Management Suite plug-in for Oracle Enterprise Manager simplifies discovery of the myriad of subtargets that make up an Oracle E-Business Suite instance.  Correspondingly, the number of alerts sent out can rise dramatically because of the inter-relationships between the components.

For example, the outage of a single Apache process triggers all of the following associated targets also to flag as a service Down status:

INSTANCE-Oracle E-Business Suite
INSTANCE-Infrastructure INSTANCE_host-APPL_TOP Context
HTTP_Server

The number of downed targets increases if a subcomponent of a primary component (such as a single JVM thread under the OACore process) experiences an outage.

This is a simple walk-through of navigating one of the e-mail alerts to start figuring out what happened.

The e-mail alert looks like this:

From: OEM12 Burbank
Sent: Monday, December 07, 2015 8:30 AM
To: DBAs
Subject: EM Event: Fatal:INSTANCE-Oracle E-Business Suite – Target is down; 1 member is down: INSTANCE_EBS Availability System

Host=hostname
Target type=Oracle E-Business Suite
Target name=INSTANCE-Oracle E-Business Suite
Categories=Availability
Message=Target is down; 1 member is down: INSTANCE_EBS Availability System
Severity=Fatal
Event reported time=Dec 7, 2015 8:29:14 AM PST
Target Lifecycle Status=Production
Operating System=Linux
Platform=x86_64
Associated Incident Id=390885
Associated Incident Status=New
Associated Incident Owner=
Associated Incident Acknowledged By Owner=No
Associated Incident Priority=None
Associated Incident Escalation Level=0
Event Type=Target Availability
Event name=Status
Availability status=Down
Root Cause Analysis Status=Symptom
Rule Name=EBS Notifications,Rule_EBS_Notifications
(to get notified, you set up Rule Sets that tell OEM when and what to notify you about)

Rule Owner=DBA
Update Details:
Target is down; 1 member is down: INSTANCE_EBS Availability System
Incident created by rule (Name = Incident management rule set for all targets, Incident creation rule for a Target Down availability status [System generated rule]).


To investigate an event alert, click on the Associated Incident ID (e.g. the 390885 which on your system will be a URL taking you into OEM) which will take you to the associated Incident Summary page.

Click on Related Events to investigate what raised the event alert (there may be more than one cause):

ss1
OEM 12c AMS 12.1.0.4 – Incident Details

From the screen, it shows the red mark on PRODARMK-Infrastructure PRODARMK_ascopofinm01-APPL_TOP Context (Oracle E-Business Suite Node).

Click on that link in the list of Targets.

Navigate to Monitoring -> Status History:

ss2
OEM 12c AMS 12.1.0.4 – Navigation Target: Monitoring -> Status History

Change the Availability History view to All History (the related underlying event caused is displayed.)

ss3
OEM 12c AMS 12.1.0.4 – Target: Status History Details

If you click on the related Message (e.g. Target is down; 1 member is down: INSTANCE_hostname.auca.corp_oacore_JVM_…); you will then be shown the related Event page for that target:

ss4
OEM 12c AMS 12.1.0.4 – Target: Event Details

Click on the Related Events tab for this target, to confirm the service alert recorded:

ss5
OEM 12c AMS 12.1.0.4 – Target: Event Details -> Related Events Timeline

If this is a recurring issue, by sliding the timeline back and forth (and adjusting the period view to a larger sample) you can see if there are any associated time-related occurrences that can be used to identify root cause.

For the specific issue, login to the associated host, and view the output and error logs for the process itself to determine what triggered the alert (in this case, the JVM automatically restarted the OACore process that had run out of memory.)

R12 e-Business Suite and OEM Monitoring – Oracle Spins Freezes

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

    FRM-92102 Forms Error R12 EBS
    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.
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.

OEM 11g AWR Baselines Page
See the Baseline Metric Thresholds link at the bottom.

In OEM 12c, you’ll find them under

Targets -> Database -> Peformance -> Adaptive Thresholds -> Baseline Metric Thresholds -> Edit Thresholds:

OEM 12c Baseline Metric Thresholds
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.”

OEM 12c Baseline Metric Response Time per Transaction vs. Baseline
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.

Using Demo Data for Oracle Data Mining Tools

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

Java 7 (1.7.0_) and Java 6 on the Same Workstation for R12 e-Business Suite EBS and other Apps

The challenge: We have a number of 3rd party Cloud applications that are still on Java 6, so we have that version (1.6.0_26 specifically) deployed to our workstations. We configured R12 to use 1.6.0_26 on the server side to minimize impact to the users. We are now implementing Informatica Information Lifecycle Management (an archive and purge technology), which has a component (the Enterprise Data Manager – EDM) which is a Java 7-based application used by certain administrative and analyst users. We needed to have those users support both Java 7 and Java 6 on the same workstation for the browsers (Internet Explorer, Firefox, Chrome).

Starting with a Windows 7 (32-bit) workstation with a working Java 1.6.0_26 installed (and 1.6.0_7 disabled).

Used the Java SE 7 SDK from the Oracle Java Archive (staged to the DBA Share /Software/Java because the 7 downloadables will be pulled from the site as of April-2015):

http://www.oracle.com/technetwork/java/javase/archive-139210.html

Chose: Java SE Development Kit 7u25 (includes the JRE runtime edition) – newer versions (post- and including update 40) contain an non-bypassable Security Warning message for using Java 6 applications.

Installed Java 7 using default parameters and completed successfully.

Open the Java Control Panel (available in the Toolbar, as the Java coffee cup icon, if a Java application is already running – e.g. EBS; or via Control Panel -> Java)

Preferred Enabled settings under the [Java] tab -> View…

06a_java_cpanel

06_java_7_and_6_versions_cpanel

De-select the Java Plug-in (next-generation) features (due to co-existence with Java 6) – we also turn off Automatic Java Updates, and default the automatic JNLP MIME/execution association setting to make it easier for users:

07_java_7_adv_tab_disable_next_gen_cpanel

Launch Informatica EDM:

04_java_launch_ilm_edm_ss

Java Update Needed – Check (Do not ask again) -> Select -> Later

01_java_upd_needed_ss

Choose default launcher for EDM’s JNLP (also can be defaulted in the Java Control Panel -> Advanced settings)

05_java_launch_ilm_edm_pick_app_ss

EDM Launches:

02_java_launch_edm_ss

03_java_launch_edm__success_ss

Launching R12 e-Business Suite under Java 6:

Launch Forms Session (Profile -> System used as an example)

Security Warning – due to Java having incomplete Certificate Authority trust to the Aramark Hosts (can be addressed also via Control Panel -> Java -> [Security] -> Manage Certificates -> Import Certificate -> add certificates provided by AUS Server Team (password required).

The Java 7 update 25 warning looks like this (click the Activate Java link; then on the next pop-up click Allow and Remember – to suppress future warning messages):

01b_java_upd_needed_pre40_ss

The post-Java 7 update 40 warning looks like this (select – I accept the risk; click [Run]):

08_java_launch_ebs_sec_warn_ss

Ref: http://java.com/en/download/faq/signed_code.xml

Why don’t I see the option to select Do not show this again for this app in the security dialog for an unsigned application?

Starting with Java 7 Update 40, the option to select Do not show this again for this app is no longer available. Unlike previous versions a user cannot suppress the security dialog for an unsigned application and will have to select the option, I accept the risk and want to run this app, each time to run the unsigned application.

Security Warning – these will come up twice in a row – once when the R12 forms servlet is instantiating and a second time when the actual Forms launch. Once Allow this application to run with the requested version (1.6.0_26) is selected, these warnings should be suppressed in future sessions.

09_java_launch_ebs_sec_warn2_run_6_ss

R12 and EDM simultaneously launched successfully.

11_java_launch_ebs_ilm_success_ss

R12.2.3 / R12.2.x Oracle e-Business Suite – Resetting the WebLogic Password

…because occasionally you either forget it (or in my case, a technology patch reset it to something I wasn’t sure about.)wls_denied_ss

There are 2 basic routes:


The “short” process – re-run the post-clone config (adcfgclone.pl):

# Have to remove the existing FMW home because it will re-unzip a new one from the clone staging archive
applptch@aburpaofinm01 $> cd /u01/fs2
applptch@aburpaofinm01 $> ls
EBSapps  FMW_Home  inst
applptch@aburpaofinm01 $> ls -latr
total 24
drwxrwxr-x  8 applptch oinstall 4096 May  8 21:49 ..
drwxr-xr-x  5 applptch oinstall 4096 May  9 12:33 EBSapps
drwxr-xr-x  3 applptch oinstall 4096 Jul 16 15:30 inst
drwxr-xr-x  6 applptch oinstall 4096 Jul 16 15:41 .
drwxr-x— 13 applptch oinstall 4096 Jul 16 16:16 FMW_Home
applptch@aburpaofinm01 $> rm -rf FMW_HOME_BAK
# Check that the Oracle Homes are not in the OraInventory
# Or you will receive the error:
# Below Oracle Homes are already registered in the global inventory:
#/u01/fs2/FMW_Home/Oracle_EBS-app1
#/u01/fs2/FMW_Home/webtier
#/u01/fs2/FMW_Home/oracle_common
#
# Exiting Cloning…
#
# Ensure that the above Oracle Homes are unregistered from the global inventory “/u01/oraInventory/ContentsXML/inventory.xml” and re-run adcfgclone.pl script
vi /u01/oraInventory/ContentsXML/inventory.xml
# and remove the <HOME> through </HOME> entries for the 3 listed entries
# Begin the clone – needs the APPS password
applptch@aburpaofinm01 $> cd $COMMON_TOP/clone/bin
applptch@aburpaofinm01 $> perl adcfgclone.pl appsTier


The “long” process – by manually manipulating the admin account in the /u01/<fs#>/FMW_Home:

# Verify your Weblogic Environment home
applptch@aburpaofinm01 $> set | grep EBS

EBS_DOMAIN_HOME=/u01/fs2/FMW_Home/user_projects/domains/EBS_domain_PTCHARMK

# Stop Weblogic
$ADMIN_SCRIPTS_HOME/adstpall.sh apps/$APPS_PW
Weblogic Password: [anything because you don’t know it]

# Set your environment
cd $EBS_DOMAIN_HOME/bin
. setDomainEnv.sh

# Remove the existing LDIFT’s, boot.properties and LDAP directory
rm -rf $EBS_DOMAIN_HOME/security/DefaultAuthenticatorInit.ldift ; \
rm -rf $EBS_DOMAIN_HOME/servers/AdminServer/security/boot.properties ; \
rm -rf $EBS_DOMAIN_HOME/servers/oacore_server?/security/boot.properties ; \
rm -rf $EBS_DOMAIN_HOME/servers/forms_server?/security/boot.properties ; \
rm -rf $EBS_DOMAIN_HOME/servers/oafm_server?/security/boot.properties ; \
rm -rf $EBS_DOMAIN_HOME/servers/forms-c4ws_server?/security/boot.properties ; \
rm -rf $EBS_DOMAIN_HOME/servers/oaea_server?/security/boot.properties ; \
rm -rf $EBS_DOMAIN_HOME/servers/AdminServer/data/nodemanager/boot.properties ; \
rm -rf $EBS_DOMAIN_HOME/servers/AdminServer/data/ldap

# Start AdminServer from command line manually
cd $EBS_DOMAIN_HOME ;\
startWebLogic.sh

# Enter new credentials as above
<Sep 16, 2014 4:58:09 PM PDT> <Info> <Security> <BEA-090065> <Getting boot identity from user.>
Enter username to boot WebLogic server:weblogic
Enter password to boot WebLogic server:

Change NodeManager password

Log in to the WebLogic Administration console.
Click the Lock & Edit button.
In the left panel, click on the EBS Domain link.
Select the Security tab.
Click on the ‘Advanced’ link.
Edit the ‘Node Manager password’ field and set it to the new WebLogic Server password. The password should be same as set in Step 3.
Edit the ‘Confirm NodeManager Password’ field and set it to the new WebLogic Server password. The password should be same as set in Step 3.
Save and activate the changes.

Shut down AdminServer from the console. For the first time, AdminServer has to be stopped from the Admin console. Follow these steps:

Log in to the WebLogic Administration console.
Shut down AdminServer.

Shut down NodeManager using the normal AD script.

$ADMIN_SCRIPTS_HOME/adnodemgrctl.sh stop

Copy the boot.properties file for all managed servers.

cp $EBS_DOMAIN_HOME/servers/AdminServer/data/nodemanager/boot.properties \
$EBS_DOMAIN_HOME/servers/oacore_server?/security/boot.properties  \
$EBS_DOMAIN_HOME/servers/forms_server?/security/boot.properties  \
$EBS_DOMAIN_HOME/servers/oafm_server?/security/boot.properties  \
$EBS_DOMAIN_HOME/servers/forms-c4ws_server?/security/boot.properties  \
$EBS_DOMAIN_HOME/servers/oaea_server?/security/boot.properties

The EBS WebLogic Server domain password has now been changed, and all servers can now be started using the normal AD scripts.

To start AdminServer:

$ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start

# Start it all back up again
$ADMIN_SCRIPTS_HOME/adstrtal.sh apps/$APPS_PW
Weblogic Password: [weblogic pwd]

Now your password change is complete (or at least you can get back into your Admin Console).

# Clone the working result from the run filesystem to the patch filesystem
$ adop phase=fs_clone

** Based upon a regular WebLogic post by:

http://shivabizint.wordpress.com/2012/10/15/how-to-reset-the-lost-weblogic-admin-user-password/

**And an R12.2 version by:

http://nagendrachillale.wordpress.com/2014/08/22/if-the-admin-password-of-an-ebs-weblogic-domain-is-lost-or-forgotten/

R12.2 Upgrade – Registering New Application Modules – r122_new_custsch.ksh

Note: This script is based upon an OEL 5.9 environment with a completed first-stage R12.2 e-Business Suite upgrade from 12.1.3. Using a pre-existing registered module (ZFA – Oracle Financial Analyzer in our case), this clones the settings required to fulfill the R12.2 upgrade pre-requisite check for additonal applications, which had been added since the 12.1.3 release.) It is referenced in the primary 12.2.3 Upgrade Post and fully listed herein.

cat /mnt/nfs/config/r122_new_custsch.ksh

#!/bin/ksh
# Create new Custom Schema registration files
# Use r122_new_custsch.ksh <APP> <APPID> <APPL_NAME>
# e.g. r122_new_custsch.ksh ABC 123 My_New_App
if ! [[ ${#} = 3 ]]; then
echo -e “\n Use ${0} <APP> <APPID> <APPL_NAME>\n”
echo -e “e.g. r122_new_custsch.ksh ABC 123 My_New_App\n”
exit
fi
if [[ $RUN_BASE = ” ]]; then
echo -e “\n RUN_BASE not set! Cannot continue. Exiting…\n”
exit
fi
echo -e “\n RUN_BASE: ” $RUN_BASE
cd $RUN_BASE/EBSapps/appl/admin/
OLDAPPL_TOP=/ptcharmk/apps/apps_st/appl
NEWAPPL=${1}
NEWAPPL_LC=`echo ${NEWAPPL} | tr ‘[A-Z]’ ‘[a-z]’`
NEWAPPLID=${2}
NEWAPPL_NAME=${3}
echo -e “\nCreating new ${NEWAPPL_LC}prod.txt and ${NEWAPPL_LC}terr.txt”
echo -e “for Application ${NEWAPPL} which is APPLICATION_ID = ${NEWAPPLID}\n”
cp zfaprod.txt ${NEWAPPL_LC}prod.txt
cp zfaterr.txt ${NEWAPPL_LC}terr.txt
echo -e “Creating driver directories…\n”
mkdir -p $RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/admin/driver
mkdir -p $RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/admin/sql
mkdir -p $RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/sql
echo -e “Copying dummy driver files…\n”
cp $OLDAPPL_TOP/xxad/12.0.0/admin/driver/xxadfile.drv \
$RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/admin/driver/${NEWAPPL_LC}file.drv
cp $OLDAPPL_TOP/xxad/12.0.0/sql/XXADNLINS.sql \
$RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/sql/${NEWAPPL}NLINS.sql
cp $OLDAPPL_TOP/xxad/12.0.0/admin/sql/XXADNLADD.sql \
$RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/admin/sql/${NEWAPPL}NLADD.sql
echo -e “Replacing key values…\n”
perl -p -i.bak -e “s:zfa:${NEWAPPL_LC}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}prod.txt
perl -p -i.bak -e “s:ZFA:${NEWAPPL}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}prod.txt
perl -p -i.bak -e “s:505:${NEWAPPLID}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}prod.txt
perl -p -i.bak -e “s:zfa:${NEWAPPL_LC}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}terr.txt
perl -p -i.bak -e “s:ZFA:${NEWAPPL}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}terr.txt
perl -p -i.bak -e “s:505:${NEWAPPLID}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}terr.txt
perl -p -i.bak -e “s:Oracle_Financial_Analyzer:${NEWAPPL_NAME}:g” \
$RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}terr.txt
echo -e “\nFinished creating new ${NEWAPPL_LC}prod.txt and ${NEWAPPL_LC}terr.txt\n”
# echo -e “Contents ${NEWAPPL_LC}prod.txt:\n”
# cat ${NEWAPPL_LC}prod.txt
# echo -e “Contents ${NEWAPPL_LC}terr.txt:\n”
# cat ${NEWAPPL_LC}terr.txt

The R12.2.3 e-Business Suite Upgrade Process (from 12.1.3.6)

r12_home_pagePlease note: This is a dump of our physical steps taken to complete – not an instructional walk-through. Success on your own environment may vary greatly compared with this experience.  This is provided for educational purposes only.

# R12.2.3 Upgrade Notes (end-to-end timing: 42 hours 36 minutes; 1.7 TB database; includes 1274 CEMLI customizations)

Database and Middle-Tier Configuration (non-Production environment) – 2 CPU (dual core) 3.2GHz; 8GB RAM,; OEL 5.9 64-bit (DB), 32-bit (MT)

My Oracle Support References used (http://support.oracle.com):

Oracle E-Business Suite Upgrade Guide Release 12.0 and 12.1 to 12.2 Part No. E48839-02
http://docs.oracle.com/cd/V39571_01/current/acrobat/122upg12.pdf
Oracle E-Business Suite Release Notes, Release 12.2 (Doc ID 1320300.1)
Oracle E-Business Suite Release 12.2.2 Readme (Doc ID 1506669.1) / 16207672 + 16207673 (online help)
Oracle E-Business Suite Release 12.2.3 Readme        [Article ID 1586214.1] 17020683 + 17020685 (online help)
Deploying Customizations in Oracle E-Business Suite 12.2 Doc ID: 1577661.1
Global Standards Compliance Checker (GSCC) and Readiness Report: 1531121.1
Applying the 12.2.2 AD and TXK Release Update Pack (Doc ID 1560906.1)
Applying R12.AD.C.Delta.3 and R12.TXK.C.Delta.3 Release Update Packs (Doc ID 1586217.1)
Troubleshooting Rapid Install for E-Business Suite Release 12.2 (Doc ID 1378579.1)
Rapid Install of VISION R12.2
http://www.tricoresolutions.com/resources/blog/rapid-install-of-vision-r122-2
12.2 Rapid-Install fails on Installing Web Tier Utilities with Error “Fatal Error: TXK Install Service” (Doc ID 1587452.1)
ADOP Prepare phase fails in upgraded 12.2 instance (Doc ID 1564271.1)
Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)
12.2 – How To Change The Web Port in E-Business Suite R12.2? (Doc ID 1567661.1)
Cloning Single Sign-on Enabled Oracle E-Business Suite Release 12.2 Environments (Doc ID 1614793.1)
Integrating Oracle E-Business Suite Release 12.2 with Oracle Access Manager 11gR2 (11.1.2) using Oracle E-Business Suite AccessGate (Doc ID 1576425.1)
Prepare Phase Fails With Error “runSyncFS sub-routine is called” During 12.2.3 Upgrade (Doc ID 1618309.1)
Patch 17064510 (12.2 Release notes, Note 1320300.1) to fix fs_clone
Consolidated seed table update patch p17204589_12.2.0_R12_GENERIC.zip (Note 1320300.1)
Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)
Bug 17487358 – dbms_object_apps_utils package missing for Ebusiness systems(17487358.8)
ADOP Fails With REP-0736: There exist uncompiled program unit(s). (Doc ID 1640641.1)
12.2.3 Patch 17020683 Fails With “AD_ZD_TABLE_APPLY.sql ORA-20001: Could not apply Crossedition Trigger/error: character string buffer too small” (Doc ID 1662301.1)
Running Autoconfig on DB Tier Errors With: Unsatisfiedlinkerror Exception Loading Native Library: Njni11 (Doc ID 1103163.1)
— Staging area
/mnt/nfs/ebs/StageR122
Patches applied:
13420532: R12.XLA.B Clean up Temp AQ (R12.1.3)
17502060: ADZDPREP XDB Bugfix (RDBMS 11.2.0.3)
## 17540582: 11.2.0.3.9 PSU Jan2014 (RDBMS 11.2.0.3) (on-hold – introduced listener bugs)
10124646: R12.2 American English Upgrade Driver ($AU_TOP/patch/115/driver)
17197281: Consolidated Upgrade Patch CUP4 Patch :12.2.0 (merged with 10124646.drv)
13543062: Online Patching Enablement patch: :R12.AD.C (R12.1.3 -> 12.2)
17766337: R12.AD.C.Delta.4  (R12.2)
17893964: Patch R12.TXK.C.delta.4 (R12.2)
17020683: Patch 12.2.3: ORACLE E-BUSINESS SUITE 12.2.3 RELEASE UPDATE PACK (R12.2)
17020685: Oracle E-Business Suite Online Help for 12.2.3 Release Update Pack (R12.2)
# 3636980:  Support Diagnostics (IZU) patch for AD Splice (R12.2) (Skeleton to show how to use adsplice for a new application)
# 17064510 (12.2 Release notes, Note 1320300.1) to fix fs_clone (applied in CUP4)

RDBMS Patches (11.2.0.3.6):
12949905  p12949905_112030_Linux-x86-64_DST-17.zip
13931044  p13931044_112030_Linux-x86-64_ora_0600_13009.zip
14123213  p14123213_112030_Linux-x86-64_DBMS_LOB_WRT_APPEND.zip
16105745  p16105745_112030_Generic_ALL_TRIGGER_COLS.zip
16303726  p16303726_112030_Linux-x86-64_BUGS_13602312.zip
16342486  p16342486_112030_Linux-x86-64_N_APPLY_BUNDLE_PATCH_II.zip (opatch napply -skip_subset -skip_duplicate)
16438289  p16438289_112030_Linux-x86-64_BUGS_12834800.zip
16587934  p16587934_112030_Linux-x86-64_BUGS_14598522.zip
17875948: MERGE REQUEST ON TOP OF 11.2.0.3.0 FOR BUGS 16587934 17654622
18259911: MERGE REQUEST ON TOP OF 11.2.0.3.0 FOR BUGS 16163946 16833977 4189542 11896575
13544396: TABLES PER_ADDRESSES THROWS ORA-00600 ON ANY DML, DDL (PER_ADDRESSES fix)
17629476: MERGE REQUEST ON TOP OF 11.2.0.4.0 FOR BUGS 17421340 17487358 (dbms_objects_apps_utils fix)
17693770: MERGE REQUEST ON TOP OF 11.2.0.3.0 FOR BUGS 17502060 17487358 (AD_ZD_PREP invalid)
— Notes on StartCD support for JDK 1.6 must use 12.2.0.46
startCD Version    JDK Version    AD/TXK Code Level    Releases Supported    Availability
startCD 12.2.0.47     JDK7     R12.AD.C.delta.4/R12.TXK.C.Delta.4     12.2.2, 12.2.3     Patch 17873425, available on My Oracle Support
startCD 12.2.0.47     JDK7     R12.AD.C.delta.3/R12.TXK.C.Delta.3     12.2.2, 12.2.3     Patch 17873425, available on My Oracle Support
startCD 12.2.0.47     JDK7     R12.AD.C.delta.2/R12.TXK.C.Delta.2     None     N/A
startCD 12.2.0.46     JDK6     R12.AD.C.delta.4/R12.TXK.C.Delta.4     12.2.2, 12.2.3     “Oracle E-Business Suite Release 12.2.0 Rapid Install StartHere – V35215-01”, available in the Oracle E-Business Suite 12.2 Media Pack
To verify your current version, use the RapidWizVersion executable that is located in the rapidwiz directory on the startCD.

Here is an example. Your system path may vary.

UNIX
$ cd /mnt/nfs/ebs/StageR122/startCD/Disk1/rapidwiz
$ ./RapidWizVersion

Oracle E-Business Suite Rapid Install Wizard
Version 12.2.0.46  (Used to maintain Java 1.6.00_27 support)

(c) Copyright 2000-2011 Oracle Corporation.  All rights reserved.

## Prerequisites (12.1.3)
Security -> Audit Trail -> Groups (Disable – Prepare for Archive – 2 Enabled Audit Groups)

(already applied MKS44472) 13027498: R12.IEX.B Collections  – UPGRADE RELATED DIAGNOSTICS SCRIPTS FOR 12.2, 12.1 AND 12.0 (Patch)
13420532: R12.XLA.B Clean up Temp AQ  – 12.2 pre-install: XLA: CLEAN UP ADVANCE QUEUES from prior runs of Create Accounting BEFORE ENABLING EBR in 12.2
[**SNAP R12.1.3 Baseline** 2014-04-16_1222-0700_unlimited_PTCHARMK_ABURPNETC03_ptchapps_mst01_.-]
## Update OraInventory location

vi /etc/oraInst.loc
inventory_loc=/u01/oraInventory

## On existing R12.1.3 MT:
adautopatch.sh 13420532
sqlplus apps/$APPS_PW @$XLA_TOP/patch/115/sql/xlaqclen.sql XLA APPLSYS

— For CharacterSet conversion testing, add Re-generate Payments Hash Data to System Administrator Request Security Group and Submit with Force=YES
— Runtime about 15 mins.

## On existing R12.1.3 DB:

— Update Java version (1.6.0_43 tested from $AGENT_HOME) – Req’d for successful AutoConfig
chmod -R 755 $ORACLE_HOME/jdk/include ; \
chmod -R 755 $ORACLE_HOME/jdk/lib ; \
chmod -R 755 $ORACLE_HOME/jdk/bin ; \
cp -r $AGENT_HOME/../core/12.1.0.3.0/jdk/* $ORACLE_HOME/jdk

sqlplus “/as sysdba”

SQL> alter system set service_names=’PTCHARMK, ebs_patch’ scope=both;

System altered.

SQL> show parameter service_names;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
service_names                        string      PTCHARMK, ebs_patch

— Used to support Online Patching to not connect as SYS
SQL> alter system set recyclebin=off scope=spfile;

SQL> create pfile from spfile;

SQL> shutdown immediate;

# Apply Jan2014 PSU
— Latest OPatch
cd $ORACLE_HOME; unzip /mnt/nfs/opatch/p6880880_112000_Linux-x86-64.zip
export PATH=$ORACLE_HOME/OPatch:$PATH

sqlplus “/as sysdba”
SQL> shutdown immediate
SQL> exit
lsnrctl stop $ORACLE_SID

# Begin DB patching
–Online Patch Enabling Patch Is Failing on ADZDPREP.sql (Doc ID 1606431.1)
cd /mnt/nfs/cpu_psu/2014Jan/17502060 ; opatch apply   #ADZDPREP XDB Bugfix
cd /mnt/nfs/ebs/122rdbms/12949905 ; opatch apply      #DST-17
cd /mnt/nfs/ebs/122rdbms/13931044 ; opatch apply      #ora_0600_13009
cd /mnt/nfs/ebs/122rdbms/14123213 ; opatch apply      #DBMS_LOB_WRT_APPEND
cd /mnt/nfs/ebs/122rdbms/16105745 ; opatch apply      #ALL_TRIGGER_COLS
cd /mnt/nfs/ebs/122rdbms/16303726 ; opatch apply      #BUGS_13602312
cd /mnt/nfs/ebs/122rdbms/16342486 ; opatch napply -skip_subset -skip_duplicate      #N_APPLY_BUNDLE_PATCH_II
cd /mnt/nfs/ebs/122rdbms/16438289 ; opatch apply      #BUGS_12834800
cd /mnt/nfs/ebs/122rdbms/16587934 ; opatch apply      #BUGS_14598522
cd /mnt/nfs/ebs/122rdbms/17875948 ; opatch apply      #BUGS_16587934
cd /mnt/nfs/ebs/122rdbms/18259911 ; opatch apply      #BUGS_16163946

sqlplus “/as sysdba”
SQL> startup
SQL> @?/sqlpatch/17502060/postinstall.sql
SQL> @?/rdbms/admin/bug14296972_apply.sql
SQL> @?/rdbms/admin/catbundle.sql EBS apply
SQL> @/mnt/nfs/ebs/122rdbms/17629476/files/rdbms/admin/dbmsobj.sql    –dbms_objects_apps_util fix
SQL> @/mnt/nfs/ebs/122rdbms/17629476/files/rdbms/admin/dbmsxdbschmig.sql
SQL> @/mnt/nfs/ebs/122rdbms/17629476/files/rdbms/admin/prvtxdbschmig.plb
SQL> grant execute on sys.dbms_objects_apps_utils to apps ;
SQL> grant all on sys.xdb$moveSchemaTab to apps ;
SQL> execute sys.dbms_metadata_util.load_stylesheets;
— recompile dbms_metadata_int to enable the diffing code
SQL> alter package dbms_metadata_int compile plsql_ccflags = ‘ku$xml_enabled:true’;
— recompile dbms_metadata_util to enable the xmlschema load code
SQL> alter package dbms_metadata_util compile plsql_ccflags = ‘ku$xml_enabled:true’;

# Check Product Versioning and Status – same as @?/rdbms/admin/utlu112s.sql
SQL> select substr(comp_id,1,15) comp_id,
substr(comp_name,1,30) comp_name,
substr(version,1,10) version,
status
from dba_registry
order by modified;

— During Onlne Patching Enablement (13543062) – Avoid insufficient free space errors.
— ERROR at line 1:
ORA-20001:  Not enough free space is avaialble in essential tablespaces. The
free-space requirements are: SYSTEM – 25GB free space and APPS_TS_SEED – 5GB
free space.  Please run Free space report from the System status report main
menu to verify if the tablespaces meets the requirements.
ORA-06512: at line 48
— During ADZDPAUT.sql (extend TEMP1 and TEMP2 tablespaces to allow max 50GB growth)
DOC>  SECTION-3
DOC>#
from   dba_tables      atab,
*
ERROR at line 4:
ORA-01652: unable to extend temp segment by 16 in tablespace TEMP1

SQL> ALTER DATABASE
DATAFILE ‘/ptcharmk/db/apps_st/data/a_ref01.dbf’
RESIZE 8G;
SQL> ALTER DATABASE
DATAFILE ‘/ptcharmk/db/apps_st/data/system01.dbf’
RESIZE 32767M;
SQL> ALTER DATABASE TEMPFILE ‘/ptcharmk/db/apps_st/temp/temp01.dbf’
AUTOEXTEND ON
NEXT 100M
MAXSIZE 32767M;
SQL> ALTER DATABASE TEMPFILE ‘/ptcharmk/db/apps_st/temp/temp02.dbf’
AUTOEXTEND ON
NEXT 100M
MAXSIZE 32767M;
SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE ‘/ptcharmk/db/apps_st/data/temp01b.dbf’
SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;
SQL> ALTER TABLESPACE TEMP2 ADD TEMPFILE ‘/ptcharmk/db/apps_st/data/temp02b.dbf’
SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;

## Disable auditing
sqlplus “/ as sysdba”
SQL> NOAUDIT ALL;

SQL> NOAUDIT ALL PRIVILEGES;

lsnrctl start $ORACLE_SID

## See Build section, if Staging area for R12.2 is needed
## R12.2 Primary APPL_TOP install
— Pre-Reqs:
Database SID, Host Name, DNS Domain name, DB OS
Database OS User, OS Group, Database Base Dir, Database Home Dir, Datafile Dir, Storage Type, Instance Prefix
Applications Host Name, DNS Domain Name, Appl OS, Apps OS User (applmgr), OS Group (oinstall)
Apps Base Dir (APPL_TOP), pps Instance Dir (INST_TOP), APPLCSF Dir ($APPLCSF), AppsLog Distribution (Single/Multi)
** WLS Admin User (weblogic), Admin Password, Apps OS User (applmgr), OS User Password
MT:

. ~/r1213_profile            ## Instantiate original 12.1.3 profile to allow upgrade mode
export DISPLAY=172.30.35.30:0.0
cd /mnt/nfs/ebs/StageR122/startCD/Disk1/rapidwiz
applptch@aburpaofinm01 $> ./rapidwiz
— See R:\Oracle DBA\R12_upgrade\12_2_upgrade\r12_2_rapidwiz_ss_notes.docx for screenshots of RapidWiz session

Configuration file written to: /u01/inst/fs1/inst/apps/PTCHARMK_aburpaofinm01/conf_PTCHARMK.txt

Configuration file written to: /u01/inst/fs2/inst/apps/PTCHARMK_aburpaofinm01/conf_PTCHARMK.txt

Second File System logfile – /u01/inst/fs2/inst/apps/PTCHARMK_aburpaofinm01/logs/03251720.log

## Copy additional product info to new homes
. ~/r122_profile
export OLDAPPL_TOP=/ptcharmk/apps/apps_st/appl
. $RUN_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env
cp -r $OLDAPPL_TOP/admin/???prod.txt $APPL_TOP/admin/ ; \
cp -r $OLDAPPL_TOP/admin/xx*prod.txt $APPL_TOP/admin/ ; \
cp -r $OLDAPPL_TOP/admin/???terr.txt $APPL_TOP/admin/ ; \
cp -r $OLDAPPL_TOP/admin/xx*terr.txt $APPL_TOP/admin/
. $PATCH_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env
cp -r $OLDAPPL_TOP/admin/???prod.txt $APPL_TOP/admin/ ; \
cp -r $OLDAPPL_TOP/admin/xx*prod.txt $APPL_TOP/admin/ ; \
cp -r $OLDAPPL_TOP/admin/???terr.txt $APPL_TOP/admin/ ; \
cp -r $OLDAPPL_TOP/admin/xx*terr.txt $APPL_TOP/admin/

## Migrate Custom Tops and code:
— In r12.2 environment MT
export OLDAPPL_TOP=/ptcharmk/apps/apps_st/appl
. $RUN_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env
cp -r $OLDAPPL_TOP/xxad/12.0.0/* $XXAD_TOP/ ; \
cp -r $OLDAPPL_TOP/xxap/12.0.0/* $XXAP_TOP/ ; \
cp -r $OLDAPPL_TOP/xxar/12.0.0/* $XXAR_TOP/ ; \
cp -r $OLDAPPL_TOP/xxcs/12.0.0/* $XXCS_TOP/ ; \
cp -r $OLDAPPL_TOP/xxfa/12.0.0/* $XXFA_TOP/ ; \
cp -r $OLDAPPL_TOP/xxfnd/12.0.0/* $XXFND_TOP/ ; \
cp -r $OLDAPPL_TOP/xxgl/12.0.0/* $XXGL_TOP/ ; \
cp -r $OLDAPPL_TOP/xxhr/12.0.0/* $XXHR_TOP/ ; \
cp -r $OLDAPPL_TOP/xxinv/12.0.0/* $XXINV_TOP/ ; \
cp -r $OLDAPPL_TOP/xxpa/12.0.0/* $XXPA_TOP/ ; \
cp -r $OLDAPPL_TOP/xxpic/12.0.0/* $XXPIC_TOP/ ; \
cp -r $OLDAPPL_TOP/xxpo/12.0.0/* $XXPO_TOP/
. $PATCH_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env
cp -r $OLDAPPL_TOP/xxad/12.0.0/* $XXAD_TOP/ ; \
cp -r $OLDAPPL_TOP/xxap/12.0.0/* $XXAP_TOP/ ; \
cp -r $OLDAPPL_TOP/xxar/12.0.0/* $XXAR_TOP/ ; \
cp -r $OLDAPPL_TOP/xxcs/12.0.0/* $XXCS_TOP/ ; \
cp -r $OLDAPPL_TOP/xxfa/12.0.0/* $XXFA_TOP/ ; \
cp -r $OLDAPPL_TOP/xxfnd/12.0.0/* $XXFND_TOP/ ; \
cp -r $OLDAPPL_TOP/xxgl/12.0.0/* $XXGL_TOP/ ; \
cp -r $OLDAPPL_TOP/xxhr/12.0.0/* $XXHR_TOP/ ; \
cp -r $OLDAPPL_TOP/xxinv/12.0.0/* $XXINV_TOP/ ; \
cp -r $OLDAPPL_TOP/xxpa/12.0.0/* $XXPA_TOP/ ; \
cp -r $OLDAPPL_TOP/xxpic/12.0.0/* $XXPIC_TOP/ ; \
cp -r $OLDAPPL_TOP/xxpo/12.0.0/* $XXPO_TOP/

## Make new missing R12.2 Product Tops (see post regarding r122_new_cust_sch.ksh)
/mnt/nfs/config/r122_new_custsch.ksh BSC 271 Balanced_Scorecard ; \
/mnt/nfs/config/r122_new_custsch.ksh FEM 274 Enterprise_Performance_Foundation ; \
/mnt/nfs/config/r122_new_custsch.ksh FII 450 Financial_Intelligence ; \
/mnt/nfs/config/r122_new_custsch.ksh POA 452 Purchasing_Intelligence ; \
/mnt/nfs/config/r122_new_custsch.ksh ISC 454 Supply_Chain_Intelligence ; \
/mnt/nfs/config/r122_new_custsch.ksh IBP 674 Bill_Presentment_&_Payment ; \
/mnt/nfs/config/r122_new_custsch.ksh BIL 676 Sales_Intelligence ; \
/mnt/nfs/config/r122_new_custsch.ksh BIX 677 Interaction_Center_Intelligence ; \
/mnt/nfs/config/r122_new_custsch.ksh PSB 8401 Public_Sector_Budgeting ; \
/mnt/nfs/config/r122_new_custsch.ksh IGW 8404 Grants_Proposal ; \
/mnt/nfs/config/r122_new_custsch.ksh IGS 8405 Student_System ; \
/mnt/nfs/config/r122_new_custsch.ksh IGF 8406 Financial_Aid ; \
/mnt/nfs/config/r122_new_custsch.ksh CUI 8722 Network_Logistics_-_Inventory ; \
/mnt/nfs/config/r122_new_custsch.ksh CUP 8723 Network_Logistics_-_Purchasing ; \
/mnt/nfs/config/r122_new_custsch.ksh BIV 862 Service_Intelligence ; \
/mnt/nfs/config/r122_new_custsch.ksh OKI 870 Contracts_Intelligence ; \
/mnt/nfs/config/r122_new_custsch.ksh DDD 430 CADView-3D ; \
/mnt/nfs/config/r122_new_custsch.ksh ITA 438 Information_Technology_Audit ; \
/mnt/nfs/config/r122_new_custsch.ksh AMW 242 Internal_Controls_Manager ; \
/mnt/nfs/config/r122_new_custsch.ksh MST 390 Transportation_Planning ; \
/mnt/nfs/config/r122_new_custsch.ksh GCS 266 Financial_Consolidation_Hub ; \
/mnt/nfs/config/r122_new_custsch.ksh ZPB 210 Enterprise_Planning_and_Budgeting ; \
/mnt/nfs/config/r122_new_custsch.ksh PFT 507 Oracle_Profitability_Manager ; \
/mnt/nfs/config/r122_new_custsch.ksh CUF 8724 Capital_Resource_Logistics_-_Financials ; \
/mnt/nfs/config/r122_new_custsch.ksh CUS 8727 Network_Logistics ; \
/mnt/nfs/config/r122_new_custsch.ksh JTS 875 Oracle_CRM_Self_Service_Admin ; \
/mnt/nfs/config/r122_new_custsch.ksh ZSA 506 Oracle_Sales_Analyzer ; \
/mnt/nfs/config/r122_new_custsch.ksh ZFA 505 Oracle_Financial_Analyzer ; \
/mnt/nfs/config/r122_new_custsch.ksh FTP 508 Transfer_Pricing
## Shutdown R12.1.3 MT
. ~/r1213_profile
~/r12ctrl.ksh stop

[**SNAP R12.2 base-install, pre-10117518_CUP4** 2014-04-17_1313-0700_unlimited_PTCHARMK_ABURPNETC03_ptchapps_mst01_.-]
## Applying R12.2 upgrade patches

# Prerequisite Patches (12.2)
. ~/r122_profile
export PATCH_TOP=/mnt/nfs/ebs/122postinstall

cd $PATCH_TOP/AD_source/10117518
— Run the Oracle Patch Application Assistant
admsi.pl
— Review the latest instructions available for this upgrade (R12.2)

17197281: Consolidated Upgrade Patch CUP4 Patch :12.2.0 and this pre-install checkin Patch (if req’d pwd SR req’d) 17821615 -ZXMIGREGIMES.SQL FAILES WHILE UPGRADING TO EBS 12.2 FROM EBS 115102

Merge 10117518: R12.AD.C (patch#10117518) with AD CUP4 patch (patch#17197279:R12.AD.C).

a. Download R12.AD.C (patch#10117518) and 12.2.0 AD CUP4 patch#17197279:R12.AD.C under $PATCH_TOP/AD_source
mkdir -p $PATCH_TOP/AD_source
chmod 777 $PATCH_TOP/AD_source
cd $PATCH_TOP/AD_source
unzip ../p10117518_R12.AD.C.BASE_LINUX.zip
unzip ../p17197279_R12.AD.C_CUP4_R12_LINUX.zip

b. mkdir -p $PATCH_TOP/10117518_CUP4
cd $PATCH_TOP/10117518_CUP4

c. admrgpch  -s $PATCH_TOP/AD_source -d $PATCH_TOP/10117518_CUP4 -merge_name 10117518_cup4  -admode
mkdir backup ; chmod 777 backup

applptch@aburpaofinm01 $> ls
10117518_README.html  admin          f10117518.ldt      metadata_files
10117518_README.txt   admrgpch.log   f17197279.ldt      u_10117518_cup4.drv
17197279_README.html  b10117518.ldt  fnd
17197279_README.txt   b17197279.ldt  j10117518_ad.zip
ad                    backup         j10117518_fnd.zip

## Instantiate APPS Grants required for 10117518_CUP4
# adgrants.sql version 120.67.12020000.18
cp $PATCH_TOP/17766337/admin/adgrants.sql /mnt/nfs/config/admin/.

Change to DB tier:
mkdir -p $ORACLE_HOME/appsutil/admin/.
cp /mnt/nfs/config/admin/adgrants.sql $ORACLE_HOME/appsutil/admin/.

sqlplus /nolog
SQL> @?/appsutil/admin/adgrants.sql APPS
— Runtime about 10 mins.

## Ensure that Maintenance Mode is enabled (required)
. ~/r1213_profile
adadmin [reqs $APPS_PW and $SYSTEM_PW]

## Reset default profile to new R12.2-based profile (sets based upon /u01/fs1/EBSapps/appl/APPS${ENV_ID}.env)
cp ~/r122_profile ~/.profile
— May need to log out and log back in to properly instantiate r122 profile.
. ~/r122_profile
cd $PATCH_TOP/10117518_CUP4
export DISPLAY=172.30.35.30:0.0
admsi.pl           ## Run the Patch Wizard to get the instructions that follow

export LOGDIR=$RUN_BASE/EBSapps/appl/admin/PTCHARMK

adpatch [using /mnt/nfs/ebs/122postinstall//10117518_CUP4/u_10117518_cup4.drv]
— Start time Mon Apr 21 17:00:17 PDT 2014

AutoPatch may have written informational messages to the file
/u01/fs1/EBSapps/appl/admin/PTCHARMK/log/10117518_CUP4.lgi

Errors and warnings are listed in the log file
/u01/fs1/EBSapps/appl/admin/PTCHARMK/log/10117518_CUP4.adp
— Runtime: 30 mins
— Stop time Mon Apr 21 17:27:09 PDT 2014

## Run 2
— Start time Thu Apr 17 2014 13:55:16
— Stop time Thu Apr 17 2014 14:35:16

adadmin -> Compile/Reload Applications Database Entities menu -> Compile APPS Schema
Run Invoker Rights processing in incremental mode [No]: No
— AD Worker started at: Thu Apr 17 14:34:42 PDT 2014
— Runtime: 5 mins
— AD Worker finished at: Thu Apr 17 14:38:42 PDT 2014

## Install 17197281 Consolidated Upgrade Patch CUP4 Patch :12.2.0
#    Oracle E-Business Suite Release Notes, Release 12.2 (Doc ID 1320300.1)
# 4. Notes for Upgrade Customers
# Apply Consolidated Upgrade Patch (CUP4) and Run 12.2.0 Upgrade (required)
# Stages to $APPL_TOP/admin/PTCHARMK/preinstall/u17197281.drv

export LOGDIR=$RUN_BASE/EBSapps/appl/admin/PTCHARMK
export PATCH_TOP=/mnt/nfs/ebs/122postinstall
cd $PATCH_TOP/17197281
adpatch preinstall=y
— Runtime: 10 mins.
cd $AU_TOP/patch/115/driver
admrgpch -d . -preinstall -master u10124646.drv

# Apply $AU_TOP/patch/115/driver/u_merged.drv
adpatch options=nocopyportion,nogenerateportion
Start Time Mon Apr 21 17:50:09 PDT 2014
Failed: Mon Apr 21 2014 20:26:07
Fixed:  Tue Apr 22 2014 09:37:13  -12 hours
ATTENTION: All workers either have failed or are waiting:
FAILED: file ecepoo.odf   on worker  1.
FAILED: file ecepoco.odf  on worker  2.
FAILED: file cssrvw.odf   on worker  3.
The following ORACLE error:

ORA-01012: not logged on

occurred while executing the SQL statement:

alter session set events ‘10520 trace name context off
# Skipped using “8” in adctrl.
Failed: Tue Apr 22 2014 12:45:29
Fixed:  Tue Apr 22 2014 12:50:56  5 mins

FAILED: file perauaddrupg.sql on worker  2.
DECLARE
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [381], [66],
[2443250], [], [], [], [], [], [], [], []
ORA-06512: at line 5
# Skipped using “8” in adctrl.

— Runtime: 14 hours
— Stop time Tue Apr 22 2014 18:54:05

## Ensure that Maintenance Mode is disabled (required) – will lose access to Mode after Online Patching is enabled
# sqlplus -s &un_apps/***** @/ptcharmk/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adsetmmd.sql [ENABLE/DISABLE]
. ~/r1213_profile
adadmin [reqs $APPS_PW and $SYSTEM_PW]

[**SNAP R12.2  17197281/10124646 Consolidated Upgrade Patch CUP4 Patch :12.2.0 2014-04-22_2213-0700_unlimited_PTCHARMK_ABURPNETC03_ptchapps_mst01_.-

## Enable Online Patching
–Enable online patching by following the Oracle E-Business Suite Upgrade Guide Release 12.0 and 12.1 to 12.2 Section 4-3 ‘Enabling Online Patching’.
$ . $RUN_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env

echo $LOG_HOME
# LOG_HOME=/u01/inst/fs1/inst/apps/PTCHARMK_aburpaofinm01/logs
mkdir $LOG_HOME/appl/op ; cd $LOG_HOME/appl/op

— Schema Editioning check – 15 mins.
— The following schemas should NOT be registered as Custom Schemas:
— • SYS
— • SYSTEM
— • CTXSYS
sqlplus system/$SYSTEM_PW @$AD_TOP/sql/ADZDPSUM.sql – 15 mins
mv adzdpsum.txt adzdpsum_pre_dbprep.txt

— object EBR violations – manual fixes required – 10 mins
sqlplus system/$SYSTEM_PW @$AD_TOP/sql/ADZDPMAN.sql
mv adzdpman.txt adzdpman_pre_dbprep.txt

— object EBR violations – auto fixes to be applied – 75 mins (large TEMP requirement 40GB+)
sqlplus system/$SYSTEM_PW @$AD_TOP/sql/ADZDPAUT.sql
mv adzdpaut.txt adzdpaut_pre_dbprep.txt

— Tablespace free space report [reqs $APPS_PW] –
export HOSTNAME=`hostname -a`
perl $AD_TOP/bin/adzdreport.pl apps
— 3 ->  3.  Free space in important tablespaces

— Online Patching Enablement – Status Report
sqlplus apps/$APPS_PW @$AD_TOP/sql/ADZDEXRPT.sql
mv adzdexrpt.txt adzdexrpt_pre_dbprep.txt

— Apply Online Patching Enablement patch: 13543062:R12.AD.C


export PATCH_TOP=/mnt/nfs/ebs/122postinstall
export LOGDIR=$RUN_BASE/EBSapps/appl/admin/PTCHARMK
cd $PATCH_TOP/13543062
— using /mnt/nfs/ebs/122postinstall/13543062/u13543062.dsrv
adpatch options=hotpatch
# Run 3
Start: Wed Apr 23 15:41:22 PDT 2014
FAILED: file ADZDWRKR.sql on worker  1.
declare
*
ERROR at line 1:
ORA-20001: [UPGRADE_TABLE 1:1 ddl_id=18849] Fatal Error, exiting.  ORA-00600:
internal error code, arguments: [ktsircinfo_num1], [381], [66], [2443250], [],
[], [], [], [], [], [], [] SQL: begin ad_zd_table.upgrade(‘HR’,
‘PER_ADDRESSES’); end;
ORA-06512: at “APPS.AD_ZD_PARALLEL_EXEC”, line 510
ORA-06512: at line 34
# Already has OBJECT_VERSION_NUMBER column
# Skipped using “8” in adctrl.

Time when worker failed: Wed Apr 23 2014 17:30:44

# Run 2
Start:  Thu Apr 17 23:28:31 PDT 2014
Stop:   Fri Apr 18 03:23
** 4.0 hours execution time – PTCHARMK

# Run 1 – debugging included
AD Worker started at: Thu Mar 27 2014 17:11:39
Time when worker failed: Thu Mar 27 2014 17:26:35
Time when worker started job: Thu Mar 27 2014 17:26:37
AD Worker started at: Fri Mar 28 2014 19:09:22
Time when worker failed: Fri Mar 28 2014 19:10:14
[** ORA-20001:  Not enough free space – resize APPS_TX_SEED and SYSTEM tablespaces **]
Time when worker failed: Mon Mar 31 2014 02:25:33
[** bounce DB, apply 17502060, Re-run adgrants.sql, Re-compile all **]
AD Worker started at: Mon Mar 31 2014 02:24:34
Time when worker failed: Mon Mar 31 2014 07:51:28
Time when worker restarted job: Mon Mar 31 2014 08:19:55
Time when worker finished: Mon Mar 31 2014 14:51:28
** 13.5 hours execution time – PTCHARMK

— Extra: How to Monitor the Online Patching Enablement patch progress -DDL Status Report
sqlplus apps/$APPS_PW @$AD_TOP/sql/ADZDSHOWDDLS.sql

## Finishing Online Editioning Checks
LOGHOME=/u01/inst/fs1/inst/apps/PTCHARMK_aburpaofinm01/logs
cd $LOGHOME/appl/op

— Online Patching Enablement – Status Report – Confirm Status – 1 mins.
sqlplus apps/$APPS_PW @$AD_TOP/sql/ADZDEXRPT.sql
mv adzdexrpt.txt adzdexrpt_post_dbprep.txt

— Schema Editioning check – Confirm Status – 10 mins.
sqlplus system/$SYSTEM_PW @$AD_TOP/sql/ADZDPSUM.sql
mv adzdpsum.txt adzdpsum_post_dbprep.txt

— object EBR violations – manual fixes required – Confirm Status – 10 mins.
sqlplus system/$SYSTEM_PW @$AD_TOP/sql/ADZDPMAN.sql
mv adzdpman.txt adzdpman_post_dbprep.txt

— object EBR violations – auto fixes to be applied – Confirm Status – 5 mins.
sqlplus system/$SYSTEM_PW @$AD_TOP/sql/ADZDPAUT.sql
mv adzdpaut.txt adzdpaut_post_dbprep.txt

— Online Patching Database Compliance Checker report – 15 mins.
sqlplus apps/$APPS_PW @$AD_TOP/sql/ADZDDBCC.sql
mv adzddbcc.lst adzddbcc_post_dbprep.txt
— See section Manual R12.2 DDL fixes applied – post 13543062 for additional fixes applied

## Applying the 12.2.3 AD and TXK Release Update Packs
— Replicate the new version of the AutoConfig utilities to the DB Tier

1. On MT server:
— Clean Autoconfig Environment [ID 391406.1]
sqlplus apps/$APPS_PW
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
SQL> COMMIT;
DB Tier:
$SCRIPT_TOP/adautocfg.sh -appspass=$APPS_PW <– Run DB AutoConfig
MT Tier:
$SCRIPT_TOP/adautocfg.sh -appspass=$APPS_PW <– Run MT AutoConfig
perl $AD_TOP/bin/admkappsutil.pl        <– creates appsutil.zip in $INST_TOP/admin/out
cp $INST_TOP/admin/out/appsutil.zip /mnt/nfs/config/$ORACLE_SID; chmod 775 /mnt/nfs/config/$ORACLE_SID/appsutil.zip

2. On DB server:
cd $ORACLE_HOME; unzip -o /mnt/nfs/config/$ORACLE_SID/appsutil.zip
$ORACLE_HOME/appsutil/scripts/$ENV_ID/adautocfg.sh  -appspass=$APPS_PW  <– Run AutoConfig on DB tier
cd $ORACLE_HOME/appsutil/bin
perl adbldxml.pl
adautocfg.sh -appspass=$APPS_PW

[**SNAP R12.2 Online Patching Enabled 13543062 Rev1 **  2014-04-25_1519-0700_unlimited_PTCHARMK_ABURPNETC03_ptchapps_mst01_.-]

Note: If you are upgrading from Release 11i, 12.0.x, or 12.1.x to Release 12.2.3 and have already applied the latest AD and TXK Release Update Packs (the same or higher release version than mentioned in this readme), you can skip this section. The minimum required release versions for AD and TXK upgrading to Release 12.2.2 are R12.AD.C.Delta.2 and R12.TXK.C.Delta.2.

1.  Source the run edition applications environment.

UNIX
$ . $RUN_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env
export PATCH_TOP=/mnt/nfs/ebs/122postinstall
— Standard Non-editioned (hotpatch) patchtop=/u01/fs_ne/EBSapps/patch
r12ctrl.ksh start           ## Required for adop – Admin services + CM submissions required
***
2.  Run adgrants from new 17766337 version.
Download and unzip Oracle Applications DBA 12.2.3 Product Release Update Pack (R12.AD.C.Delta.4) , Patch 17766337.
Follow the instructions in the patch readme.
Run adgrants.sql by following the instructions in the ad_run_adgrants.xml PAA step.

# adgrants.sql version 120.67.12020000.18
Change to DB tier:
sqlplus /nolog
SQL> @?/appsutil/admin/adgrants.sql APPS
— Runtime about 10 mins.

## Troubleshoot FS_CLONE issue
# Prepare Phase Fails With Error “runSyncFS sub-routine is called” During 12.2.3 Upgrade (Doc ID 1618309.1)
# 12.2: fs_clone fails with “ERROR: Number of servers are not in sync between Run and Patch Context” (Doc ID 1582473.1)

export PATCH_TOP=/mnt/nfs/ebs/122postinstall

# Stop prior session
adop phase=abort
# Apply 17064510
adop phase=apply hotpatch=yes patches=17064510 patchtop=/mnt/nfs/ebs/122postinstall
Current View – 17064510:US is EXPLICIT
This Patch seems to have been applied already.
Would you like to continue anyway  [N] ? Y *
/u01/fs_ne/EBSapps/log/adop/3/apply_20140425_165404/PTCHARMK_aburpaofinm01/17064510/log/u17064510.log
— Didn’t fix the issue – Try Re-syncing both tops.
. $RUN_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env
$SCRIPT_TOP/adautocfg.sh -appspass=$APPS_PW
. $PATCH_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env
export SCRIPT_TOP=$INST_TOP/admin/scripts
$SCRIPT_TOP/adautocfg.sh -appspass=$APPS_PW

3.  Start new online patching cycle.
Change to MT:
$ adop phase=fs_clone

## Try rsync -avz /u01/fs1/ /u01/fs2

$ adop phase=prepare defaultsfile=${PATCH_BASE}/EBSapps/appl/admin/${ORACLE_SID}_patch/adalldefaults.txt patchtop=/mnt/nfs/ebs/122postinstall
—  started at: Fri Apr 25 2014 15:50:32

4.  Run apply phase for R12.AD.C.DELTA.4 Patch 17766337.
##    $ adop phase=apply patches=17766337 patchtop=/mnt/nfs/ebs/122postinstall
cd /mnt/nfs/ebs/122postinstall/17766337

$ adop phase=apply hotpatch=yes patches=17766337 patchtop=/mnt/nfs/ebs/122postinstall
— Succeeded! Thu May  8 21:27:55 PDT 2014
5.  Run apply phase for R12.TXK.C.DELTA.4 Patch 17893964.
##    $ adop phase=apply patches=17893964

cd /mnt/nfs/ebs/122postinstall/17893964
$ adop phase=apply hotpatch=yes patches=17893964 patchtop=/mnt/nfs/ebs/122postinstall
## Completed! May  8 21:53
Note: R12.AD.C.DELTA.4 and R12.TXK.C.DELTA.4 must be applied in the same online patching cycle, without merging.

6.  Complete the online patching cycle by running the following commands in the order shown.
$ adop phase=finalize
$ adop phase=cutover
$ adop phase=cleanup

adop phase=fs_clone
# Succeeded! Fri May  9 13:07:23 PDT 2014
# Fix broken APPS.AD_ZD_PREP invalid from Delta.4 updates
# Oracle also recommended applying RDBMS 17693770: MERGE REQUEST ON TOP OF 11.2.0.3.0 FOR BUGS 17502060 17487358
sqlplus “/as sysdba”
SQL> @/mnt/nfs/ebs/122rdbms/17629476/files/rdbms/admin/dbmsobj.sql    –dbms_objects_apps_util fix
SQL> @/mnt/nfs/ebs/122rdbms/17629476/files/rdbms/admin/dbmsxdbschmig.sql
SQL> @/mnt/nfs/ebs/122rdbms/17629476/files/rdbms/admin/prvtxdbschmig.plb
SQL> grant execute on sys.dbms_objects_apps_utils to apps ;
SQL> grant all on sys.xdb$moveSchemaTab to apps ;

## Apply the 12.2.3 Suite-Wide Release Update Pack
Apply the Consolidated Upgrade Patch (CUP4) for Release 12.2.0 (Patch 17197279 (17197279:12.2.0) – for latest CUP) in pre-installation mode on the Run File System.

Having applied the Release 12.2.2 AD and TXK Release Update Packs, you can now proceed to apply the 12.2.3 Suite-Wide Release Update Pack.

1.  Start an online patching cycle.
$ adop phase=prepare

2.  Apply Oracle E-Business Suite Release 12.2.3 Patch 17020683 and Oracle E-Business Suite Release 12.2.3 Online Help Patch 17020685.
$ adop phase=apply abandon=no restart=yes flags=autoskip patches=17020683,17020685 patchtop=/mnt/nfs/ebs/122postinstall
p
# Started Sat May 10 00:43:13 PDT 2014
/u01/fs_ne/EBSapps/log/adop/8/adop_20140510_004320.log
# Workers appeared to be dead as of 5/20/2014 11:59A
# /u01/fs_ne/EBSapps/log/adop/8/apply_20140510_004320/PTCHARMK_aburpaofinm01/17020683/log
$ . $PATCH_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env
adctrl
6.    Restart a worker on the current machine
[all]
# Processing resumed  Sun May 11 09:26:53 PDT 2014
# Workers appeared to be dead as of 5/20/2014 09:33A

# In Alert.log
Sun May 11 14:38:30 2014
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x18] [PC:0x37F48E9, pkgsql()+55] [flags: 0x0, count: 1]
Errors in file /ptcharmk/db/tech_st/11.2.0/admin/PTCHARMK_aburpaofind01/diag/rdbms/ptcharmk/PTCHARMK/trace/PTCHARMK_ora_18900.trc  (incident=93414):

#Solution:
SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.
# Appears to recur about 10 times before subsiding – will fail within 10 minutes each time.

## Completed! June 6 17:20:53 (autoskip=yes, 3 compile errors during 17020683

From autoskip.log:
AutoPatch started at: Thu Jun 05 2014 15:39:22
Autoskipped at Thu Jun 05 2014 15:41:48
[Session : 70785, Error while generating Oracle Forms files]
Autoskipped at Thu Jun 05 2014 15:42:07
[Session : 70785, Error while generating Oracle Reports library  files]
Autoskipped at Thu Jun 05 2014 15:46:22
[Session : 70785, Error while generating Oracle Reports files]

3.  Instantiate the Oracle HTTP Server configuration files ssl.conf and mod_wl_ohs.conf.

a.  Source the patch edition environment file:

UNIX
$ . $PATCH_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env

b.  Perform the instantiation with the following commands (the backslash at the end of each line is being used to divide each command across several screen lines for readability). When prompted for the APPS password, press <Return>.

UNIX
$ $ADJVAPRG oracle.apps.ad.autoconfig.InstantiateFile \
-e $CONTEXT_FILE \
-d $FND_TOP/admin/driver/ohstmpl.drv \
-tmpl $FND_TOP/admin/template/ssl_conf_FMW.tmp

$ $ADJVAPRG oracle.apps.ad.autoconfig.InstantiateFile \
-e $CONTEXT_FILE \
-d $FND_TOP/admin/driver/ohstmpl.drv \
-tmpl $FND_TOP/admin/template/mod_wl_ohs_conf_FMW.tmp

$ $ADJVAPRG oracle.apps.ad.autoconfig.InstantiateFile \
-e $CONTEXT_FILE \
-d $FND_TOP/admin/driver/ohstmpl.drv \
-tmpl $FND_TOP/admin/template/admin_conf_FMW.tmp

$ perl $FND_TOP/patch/115/bin/txkSetAppsConf.pl \
-contextfile=$CONTEXT_FILE \
-outfile=$INST_TOP/logs/appl/rgf/TXK/test.log

## Completed! June 6 – 2 mins runtime

4.  Complete the online patching cycle by running the following commands in the order shown.

a.  Source the run edition environment file:

UNIX
$ . $RUN_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env

b.
$ adop phase=finalize
## Completed! June 6 – 10 mins runtime
c.
$ adop phase=cutover
# Started June 6 14:11p
Announces:
PTCHARMK environment has changed.
All users must re-source the environment using below command:
source /u01/EBSapps.env run|patch
## Completed! June 6 – 43 mins runtime
d.
$ adop phase=cleanup
## Completed! June 6 – 10 mins runtime
Synchronize the patch file system
$ . $RUN_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env
$ adop phase=fs_clone
# started at: Fri Jun 06 2014 14:57:59
# Finished Fri Jun 06 2014 2014/06/06 20:42:30

R12.1.3 Patch Warnings during AutoPatch – Fixed

If you’ve been bothered by these warning messages that come up during every AutoPatch (adpatch) session in Oracle e-Business Release 12.1.x:

AutoPatch warning:
Product Data File
/ptcharmk/apps/apps_st/appl/admin/zfaprod.txt
does not exist for product “zfa”.
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.

AutoPatch warning:
Product Data File
/ptcharmk/apps/apps_st/appl/admin/zsaprod.txt
does not exist for product “zsa”.
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.

AutoPatch warning:
Product Data File
/ptcharmk/apps/apps_st/appl/admin/jtsprod.txt
does not exist for product “jts”.
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.

AutoPatch warning:
Territory Data File
/ptcharmk/apps/apps_st/appl/admin/zfaterr.txt
does not exist for product “zfa”.
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.

AutoPatch warning:
Territory Data File
/ptcharmk/apps/apps_st/appl/admin/zsaterr.txt
does not exist for product “zsa”.
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AutoPatch warning:
Territory Data File
/ptcharmk/apps/apps_st/appl/admin/jtsterr.txt
does not exist for product “jts”.
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.

Simply Create the following text files. The contents should be respectively:

$APPL_TOP/admin/jtsprod.txt
%%% Single-product product data file format 12.0.A
jts 875
END_OF_PRODUCT_ABBREVIATIONS -999
875 jts JTS APP
No No No No
Yes Yes
875 JTS JTS
0
1.0.0 1.0.0
none
none
none
none
END_OF_PRODUCTS
Release 12.0.0
12.0.0
R120 R120_ additional-this-mpl
JTS 12.0.0
END_OF_RELEASE 0.0.0

$APPL_TOP/admin/jtsterr.txt (or substitute your own NLS_LANG and NLS_CHARACTERSET settings localized for your instance)
%%% Single-product territory data file format 12.0.A
R120
0 usaeng US AMERICAN EN US American_English
appltape.txt appltape.txt AL32UTF8
Yes Standard Data_Group
none
none
none
none
none
c jts Oracle_CRM_Self_Service_Admin
END_OF_PRODUCT_NAMES
END_OF_LANGUAGE_INFO

$APPL_TOP/admin/zsaprod.txt
%%% Single-product product data file format 12.0.A
zsa 506
END_OF_PRODUCT_ABBREVIATIONS -999
506 zsa ZSA APP
No No No No
Yes Yes
506 ZSA ZSA
0
1.0.0 1.0.0
none
none
none
none
END_OF_PRODUCTS
Release 12.0.0
12.0.0
R120 R120_ additional-this-mpl
ZSA 12.0.0
END_OF_RELEASE 0.0.0

$APPL_TOP/admin/zsaterr.txt
%%% Single-product territory data file format 12.0.A
R120
0 usaeng US AMERICAN EN US American_English
appltape.txt appltape.txt AL32UTF8
Yes Standard Data_Group
none
none
none
none
none
c zsa Oracle_Sales_Analyzer
END_OF_PRODUCT_NAMES
END_OF_LANGUAGE_INFO

$APPL_TOP/admin/zfaprod.txt
%%% Single-product product data file format 12.0.A
zfa 505
END_OF_PRODUCT_ABBREVIATIONS -999
505 zfa ZFA APP
No No No No
Yes Yes
505 ZFA ZFA
0
1.0.0 1.0.0
none
none
none
none
END_OF_PRODUCTS
Release 12.0.0
12.0.0
R120 R120_ additional-this-mpl
ZFA 12.0.0
END_OF_RELEASE 0.0.0

$APPL_TOP/admin/zfaterr.txt
%%% Single-product territory data file format 12.0.A
R120
0 usaeng US AMERICAN EN US American_English
appltape.txt appltape.txt AL32UTF8
Yes Standard Data_Group
none
none
none
none
none
c zfa Oracle_Financial_Analyzer
END_OF_PRODUCT_NAMES
END_OF_LANGUAGE_INFO

Repeat the following dummy file creations for each product you’re registering above:

Create directory $APPL_TOP/zfa/12.0.0/admin

Create directory $APPL_TOP/zfa/12.0.0/admin/driver

Create directory $APPL_TOP/zfa/12.0.0/admin/sql

Create file $APPL_TOP/zfa/12.0.0/admin/driver/zfafile.drv with following contents:
# Dummy xxadfile.drv

Create file $APPL_TOP/zfa/12.0.0/sql/ZFANLINS.sql with following contents:
commit;
exit;

Create file $APPL_TOP/zfa/12.0.0/admin/sql/ZFANLADD.sql with following contents:
commit;
exit;

 

That’s it – no more warnings during AutoPatch. (they are all legacy products anyway)