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

Wednesday, February 26, 2014

ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table. ORA-06512: at "APPS.AD_ZD_ADOP", line 523

I reported this error to Oracle on an SR:

The adop phase=prepare fails before generating any log files. Whatever we get on the screen is what we have. I'll paste it for you again:

$ adop phase=prepare

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:

Please wait. Validating credentials...


RUN file system context file: $INST_TOP/appl/admin/tsoadba1_alpgetapp049d.xml
PATCH file system context file: $INST_TOP/appl/admin/tsoadba1_alpgetapp049d.xml


************* Start of session *************
version: 12.2.0
started at: Sun Feb 23 2014 20:55:38

APPL_TOP is set to $APPL_TOP
[STATEMENT] Using 48 workers (Default: 48, Recommended maximum limit: 696)
[EVENT] [START 2014/02/23 20:55:48] Verify SSH
Logfile location $INST_TOP/logs/appl/rgf/TXK/verifyssh.log
xml output = $INST_TOP/logs/appl/rgf/TXK/out.xml
[EVENT] [END 2014/02/23 20:55:54] Verify SSH
[ERROR] Failed to execute sql statement :
begin
AD_ZD_ADOP.LOCK_SESSIONS_TABLE('server1',60,0);
end;

[ERROR] SQLPLUS error: buffer=
SQL*Plus: Release 10.1.0.5.0 - Production on Sun Feb 23 20:55:59 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> SQL> Connected.
SQL> begin
*
ERROR at line 1:
ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table.
ORA-06512: at "APPS.AD_ZD_ADOP", line 523
ORA-06512: at line 2
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

[UNEXPECTED]Unable to acquire lock on sessions table

adop exiting with status = 2 (Fail)


Oracle asked me to confirm whether I had run adconfig on both run and patch environments. I had forgotten to run adconfig on patch environment.  After running adconfig on the patch environment, I retried and it worked.


Thursday, February 20, 2014

Product Restrictions during R12.2 online patching

This information is from page 75 of Oracle® E-Business Suite Maintenance Guide Release 12.2 Part No. E22954-13, published in July 2013 available here  http://docs.oracle.com/cd/V39571_01/current/acrobat/122ebsmt.zip

For patches that have manual steps, the patch readme file instructs you to use Oracle Patch Application Assistant (PAA) to create customized instructions for your system. PAA consolidates and displays only the relevant manual steps for all the patches you want to apply, including steps that you have completed. It also automatically merges the contents of individual patch readme files for a merged patch.

Create Checklist of Product Functionality Disabled in Online Patching Cycle:
During an online patching cycle, the following product restrictions will apply. Before you commence patching, you should therefore ensure there will be no requirement for any these actions or features until the cycle is complete.
  • Payroll
    • Users will not be able to define Fast Formulas or use the Fast Formula Assistant.
    • Users will not be able to perform dynamic trigger maintenance.
    • Users will not be able to create, update, or delete US Cities.
    • Data Pump meta-mapper generator will be disabled.
    • The Japanese Balance Dimensions concurrent program will be deferred to after the cutover phase is complete.
    • Pension Calculation Setup cannot be used.
    • US localization earnings and deduction setup cannot be used.
    • Tax Withholding Rules Setup cannot be used.
    • Wage Attachment Earnings Rules Setup cannot be used.
    • Garnishment Rules Setup cannot be used.
    • Quick Paint Reports cannot be used.
    • Quantum Program Update Installer execution is unavailable.
  • Order Management:
    • Creation of a new Defaulting Condition in the Attribute Defaulting Rules form is disabled, unless the same seeded condition already exists for a given attribute.
  • Warehouse Management:
    • WMS Rule creation is restricted.
  • Inventory:
    • Concurrent program "Generate Stock Locator Flexfield Definition for Mobile Transactions" will be disabled.
  • Public Sector Financials International:
    • Users will not be able to run the following concurrent programs:
      • Subledger Security: Apply Security
      • Subledger Security: Import/Export Data Fix
  • Subledger Accounting:
    • Users will not be able to Validate the Application Accounting definitions.
  • Accounts Receivable:
    • Users will not be able to create new Transaction Sources.
  • Incentive Compensation:
    • Transaction collection process for new mappings will not be available and any changed mapping will continue to use previous mapping rules.
    • Users will not be able to run the "Synchronize Classification Rulesets" program.
    • Users will not be able to use the "Formula Generation" feature.
    • Users will not be able to specify new formulas or changes to compensation rules.
  • Oracle Demand Planning:
    • Demand plans will not be available for users.

Saturday, February 8, 2014

R12.2 upgrade - icxr12pd.sql takes a very long time

During an R12.2 upgrade, the worker icxr12pd.sql takes very long time.  This is document in My Oracle Support Article ICXR12PD.Sql Runs Slowly During Upgrade to 12.1.1 (Doc ID 864898.1)
 
Cause: The pre-upgrade steps for iProcurement were not implemented as documented in the upgrade guide
Though this article talks about R12.1.1, it also applies to R12.2.  If you read the 11i to R12.2 upgrade guide ( http://docs.oracle.com/cd/V39571_01/current/acrobat/122upg11i.pdf ). it has specifically mentioned on page 285:

iProcurement
Completing these tasks could substantially reduce the downtime required for your
upgrade.
The Catalog Data Pre-upgrade Process
This pre-upgrade process is strongly recommended if you are upgrading iProcurement
from 11.5.9 or 11.5.10 to Release 12. It pre-processes bulk-loaded content to reduce the
actual time required for the upgrade and to ensure the upgrade process runs smoothly.
You can run it multiple times. If exceptions are found, then make corrections and re-run
the program until no exceptions are noted. Running this program does not require users
to log off the system. Note: Exceptions noted and not fixed before the upgrade will not
be available in the iProcurement Catalog. Specifically, this program shortens the time it
takes to run these upgrade scripts:
• -icxiftug.sql: updates POR_TEMPLATE_INFO
• -icxr12in.sql: general setup upgrade script needed for iProcurement
• -poxujpoh.sql:updates PO_HEADERS_ALL (for example, create_language)
• -poxukpol.sql: updates PO_LINES_ALL (for example, ip_category_id)
• -poxukrt.sql: updates PO_REQEXPRESS_LINES_ALL (for example, ip_catetory_id)
• -icxr12ug.sql: migrates iProcurement data model to PO
• -icxr12rt.sql: populates the requisition templates in iProcurement intermedia index
tables
• -icxr12pd.sql: populates the Purchasing documents in iProcurement intermedia
index tables: blanket purchase agreements (BPAs), global blanket agreements
(GBPAs), and quotes
• -icxr12mi.sql: populates the master items in iProcurement intermedia index tables
• -poxukfi.sql: purchasing script to approve the GBPAs created during iProcurement
migration
• -poxukat.sql: purchasing script to update the attachments
• -icxr12fi.sql: final upgrade script, which upgrades favorite lists, purges the BPAs
and GBPAs that are not approved, and creates the intermedia index


Make sure that you complete the tasks described in the upgrade guide, during the pre-upgrade phase, much before actual cutover downtime starts.

If you haven't done this and proceed to upgrade, it is going to take a long time for all the workers which are going to run icxr12pd.sql.  Based on my analysis of v$sql_longops, this is the insert query most fired during this worker's execution:

INSERT INTO AP_INV_DISTS_TARGET T1 (BATCH_ID, INVOICE_ID, INVOICE_LINE_NUMBER,
INVOICE_DISTRIBUTION_ID, DISTRIBUTION_LINE_NUMBER, LINE_TYPE_LOOKUP_CODE,
DESCRIPTION, DIST_MATCH_TYPE, ORG_ID, DIST_CODE_COMBINATION_ID, ACCOUNTING_DATE,
PERIOD_NAME, ACCRUAL_POSTED_FLAG, CASH_POSTED_FLAG, AMOUNT_TO_POST,
BASE_AMOUNT_TO_POST, POSTED_FLAG, ACCOUNTING_EVENT_ID, SET_OF_BOOKS_ID, AMOUNT,
BASE_AMOUNT, EXCHANGE_DATE, ROUNDING_AMT, QUANTITY_VARIANCE, BASE_QUANTITY_VARIANCE,
MATCH_STATUS_FLAG, ENCUMBERED_FLAG, PACKET_ID, USSGL_TRANSACTION_CODE,
USSGL_TRX_CODE_CONTEXT, REVERSAL_FLAG, PARENT_REVERSAL_ID, CANCELLED_FLAG, INCOME_TAX_REGION,
TYPE_1099, STAT_AMOUNT, CHARGE_APPLICABLE_TO_DIST_ID, PREPAY_AMOUNT_REMAINING,
PREPAY_DISTRIBUTION_ID, PREPAY_TAX_PARENT_ID, PARENT_INVOICE_ID, CORRECTED_QUANTITY,
PO_DISTRIBUTION_ID, RCV_TRANSACTION_ID, UNIT_PRICE, MATCHED_UOM_LOOKUP_CODE, QUANTITY_INVOICED,
FINAL_MATCH_FLAG, RELATED_ID, ASSETS_ADDITION_FLAG, ASSETS_TRACKING_FLAG, PROJECT_ID, TASK_ID, EXPENDITURE_T

In our test environment, we have 25 million + rows in ap_inv_dists_target.  We have already waited for 8 hours for all the workers to complete.  Here's the status of the queries:

set pages 300 lines 300
col "Username" for a12
col "QC/Slave" for A8
col "Slaveset" for A8
col "Slave INST" for A9
col "QC SID" for A6
col "QC INST" for A6
col "operation_name" for A30
col "target" for A30

select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(px.inst_id) "Slave INST",
substr(opname,1,30) operation_name,
substr(target,1,30) target,
sofar,
totalwork,
units,
start_time,
timestamp,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST"
from gv$px_session px,
gv$px_process pp,
gv$session_longops s
where px.sid=s.sid
and px.serial#=s.serial#
and px.inst_id = s.inst_id
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
order by
decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
px.QCSID,
decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID
/

Username QC/Slave SlaveSet Slave INS OPERATION_NAME TARGET SOFAR TOTALWORK UNITS START_TIME TIMESTAMP QC SID QC INS
------------ -------- -------- --------- ------------------------------ ------------------------------ ---------- ---------- -------------------------------- --------------- --------------- ------ ------
APPS QC 1 Table Scan AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329 Blocks 08-FEB-14 2335
APPS QC 1 Table Scan AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329 Blocks 08-FEB-14 2335
APPS QC 1 Table Scan AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329 Blocks 08-FEB-14 2335
APPS QC 1 Table Scan AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329 Blocks 08-FEB-14 2335
APPS QC 1 Gather Table's Index Statistic 0 0 Indexes 08-FEB-14 2335
APPS QC 1 Table Scan AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329 Blocks 08-FEB-14 2335
APPS QC 1 Table Scan AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329 Blocks 08-FEB-14 2335

7 rows selected.