Oracle Business Intelligence Application OOTB ETL Architecture

Oracle Business Intelligence Application OOTB ETL Architecture:
Oracle Business Intelligence Application (BI APPS) Out of the Box (OOTB)  ETL Architecture:

W_INVENTORY_MONTHLY_BAL_F

W_INVENTORY_DAILY_BAL_F(SDE_ORA_InventoryDailyBalanceFact)

W_ORA_INVENTORY_BAL_FS_TMP (SDE_ORA_InventoryBalanceFact_Temporary)

Mtl_onhand_quantities_detail

w_product_d

mtl_system_items_b   

w_ora_product_ds_tmp

Session Name: SDE_ORA_ProductDimension_Derive

w_product_ds

Session Name: SDE_ORA_ProductDimension  

w_product_d

w_inventory_product_d

w_ora_invprod_ds_tmp

Session Name: SDE_ORA_InventoryProductDimension_Derive

w_inventory_product_ds

Session Name: SDE_ORA_InventoryProductDimension

w_inventory_product_d

Session Name: SIL_InventoryProductDimension

W_PRODUCT_XACT_F

Source EBS Table:  MTL_MATERIAL_TRANSACTIONS

W_PRODUCT_LOT_XACT_FS

Session Name: SDE_ORA_ProductLotTransactionFact_Process

Session Name: SDE_ORA_ProductLotTransactionFact_Discrete

2.    W_PRODUCT_LOT_XACT_F

Session Name: SIL_ProductLotTransactionFact

3     W_PRODUCT_XACT_F

       Session Name: SIL_ProductTransactionFact

W_AP_BALANCE_F

1.       W_AP_XACT_F (PLP_APINCREMENTALACTIVITYLOAD)

2.       
          W_AP_BALANCE_F_TMP

3.       W_AP_BALANCE_F

Oracle Business Intelligence OPatch failed with error code 73

Problem:
If you see below error while applying Opatch:

OPatch detects the Middleware Home as "/opt/OBI_Home/Middleware" 

OPatchSession cannot load inventory for the given Oracle Home /opt/OBI_Home/Middleware/Oracle_BI1. Possible causes are: 
No read or write permission to ORACLE_HOME/.patch_storage 
Central Inventory is locked by another OUI instance 
No read permission to Central Inventory 
The lock file exists in ORACLE_HOME/.patch_storage 
The Oracle Home does not exist in Central Inventory 

UtilSession failed: Lock file left by a different patch, OPatch will not try re-using the lock file. 
Log file location: /opt/OBI_Home/Middleware/Oracle_BI1/cfgtoollogs/opatch/opatch2017-01-14_13-15-31PM_1.log 

OPatch failed with error code 73 

Solution:

Backup and remove 

<Middleware Home>/Oracle_BI1/.patch_storage/patch_locked folder.

Oracle OAF Page Error

(oracle.jbo.common.ampool.ApplicationPoolException) JBO-30003: The application pool (.156A6DEE716) failed to checkout an application module due to the following exception:
----- LEVEL 1: DETAIL 0 -----
(oracle.jbo.JboException) JBO-29000: Unexpected exception caught: oracle.jbo.JboException, msg=JBO-29000: Unexpected exception caught: java.lang.ExceptionInInitializerError, msg=null
   ----- LEVEL 2: DETAIL 0 -----
   (oracle.jbo.JboException) JBO-29000: Unexpected exception caught: java.lang.ExceptionInInitializerError, msg=null
      ----- LEVEL 3: DETAIL 0 -----
      (java.lang.ExceptionInInitializerError) null

Console Log

"D:\OAF\jdevbin\jdk\bin\javaw.exe" -XX:MaxPermSize=256M -mx256m -DRUN_FROM_JDEV=true -DCACHENODBINIT=true -DFND_JDBC_STMT_CACHE_SIZE=200 -classpath "D:\OAF\jdevbin\BC4J\jlib\bc4jtester.jar;D:\OAF\jdevbin\jlib\jdev-cm.jar;D:\OAF\jdevbin\lib\xmlparserv2.jar;D:\OAF\jdevbin\jlib\help4.jar;D:\OAF\jdevbin\jlib\share.jar;D:\OAF\jdevbin\jlib\jewt4.jar;D:\OAF\jdevbin\jlib\oracle_ice.jar;D:\OAF\jdevbin\jlib\ojmisc.jar;D:\OAF\jdevbin\jdk\jre\lib\resources.jar;D:\OAF\jdevbin\jdk\jre\lib\rt.jar;D:\OAF\jdevbin\jdk\jre\lib\sunrsasign.jar;D:\OAF\jdevbin\jdk\jre\lib\jsse.jar;D:\OAF\jdevbin\jdk\jre\lib\jce.jar;D:\OAF\jdevbin\jdk\jre\lib\charsets.jar;D:\OAF\jdevbin\jdk\jre\lib\modules\jdk.boot.jar;D:\OAF\jdevbin\jdk\jre\classes;D:\OAF\jdevhome\jdev\myclasses;D:\OAF\jdevbin\jdev\lib\jdev-rt.jar;D:\OAF\jdevbin\jdbc\lib\ojdbc14dms.jar;D:\OAF\jdevbin\jdbc\lib\orai18n.jar;D:\OAF\jdevbin\jdbc\lib\ocrs12.jar;D:\OAF\jdevbin\diagnostics\lib\ojdl.jar;D:\OAF\jdevbin\lib\dms.jar;D:\OAF\jdevbin\BC4J\lib\adfshare.jar;D:\OAF\jdevbin\BC4J\lib\bc4jmt.jar;D:\OAF\jdevbin\BC4J\lib\collections.jar;D:\OAF\jdevbin\BC4J\lib\bc4jct.jar;D:\OAF\jdevbin\jlib\commons-el.jar;D:\OAF\jdevbin\jlib\jsp-el-api.jar;D:\OAF\jdevbin\jlib\oracle-el.jar;D:\OAF\jdevbin\BC4J\lib\bc4jctejb.jar;D:\OAF\jdevbin\j2ee\home\lib\ejb.jar;D:\OAF\jdevbin\BC4J\lib\bc4jmtejb.jar;D:\OAF\jdevbin\BC4J\jlib\bc4jhtml.jar;D:\OAF\jdevbin\BC4J\jlib\datatags.jar;D:\OAF\jdevbin\BC4J\jlib\bc4juixtags.jar;D:\OAF\jdevbin\BC4J\jlib\graphtags.jar;D:\OAF\jdevbin\j2ee\home\lib\ojsp.jar;D:\OAF\jdevbin\j2ee\home\jsp\lib\taglib\ojsputil.jar;D:\OAF\jdevbin\j2ee\home\oc4j.jar;D:\OAF\jdevbin\j2ee\home\lib\oc4j-internal.jar;D:\OAF\jdevbin\j2ee\home\lib\servlet.jar;D:\OAF\jdevbin\jdev\lib\ojc.jar;D:\OAF\jdevbin\jdev\appslibrt\regexp.jar;D:\OAF\jdevbin\jdev\appslibrt\share.jar;D:\OAF\jdevbin\jdev\appslibrt\ebsuix.jar;D:\OAF\jdevbin\oaext\mds\lib\mdsrt.jar;D:\OAF\jdevbin\oaext\lib\mdsdt.jar;D:\OAF\jdevbin\oaext\lib\oamdsdt.jar;D:\OAF\jdevbin\jdev\appslibrt\fwkjbo.zip;D:\OAF\jdevbin\jdev\appslibrt\fwk.zip;D:\OAF\jdevbin\jdev\appslibrt\atg.zip;D:\OAF\jdevbin\jdev\appslibrt\collections.zip;D:\OAF\jdevbin\jdev\appslibrt\concurrent.zip;D:\OAF\jdevbin\jdev\appslibrt\oamMaintMode.zip;D:\OAF\jdevbin\jdev\appslibrt\wsp.zip;D:\OAF\jdevbin\jdev\appslibrt\fwkCabo.zip;D:\OAF\jdevbin\jdev\appslibrt\diagnostics.jar;D:\OAF\jdevbin\jdev\appslibrt\oracle-portal-api.jar;D:\OAF\jdevbin\jdev\appslibrt\pdkjava.jar;D:\OAF\jdevbin\jdev\appslibrt\ptlshare.jar;D:\OAF\jdevbin\jdev\appslibrt\xml.jar;D:\OAF\jdevbin\jdev\appslibrt\wsrp-container-types.jar;D:\OAF\jdevbin\jdev\appslibrt\jaxb-impl.jar;D:\OAF\jdevbin\jdev\appslibrt\jaxb-libs.jar;D:\OAF\jdevbin\jdev\appslibrt\jazn.jar;D:\OAF\jdevbin\jdev\appslibrt\jazncore.jar;D:\OAF\jdevbin\bibeans\lib\biamlocal.jar;D:\OAF\jdevbin\bibeans\lib\bipres.jar;D:\OAF\jdevbin\bibeans\lib\bicmn.jar;D:\OAF\jdevbin\bibeans\lib\bidatasvr.jar;D:\OAF\jdevbin\bibeans\lib\bidataclt.jar;D:\OAF\jdevbin\bibeans\lib\bidatacmn.jar;D:\OAF\jdevbin\bibeans\lib\biext.jar;D:\OAF\jdevbin\bibeans\lib\bicmn-nls.zip;D:\OAF\jdevbin\bibeans\lib\bipres-nls.zip;D:\OAF\jdevbin\bibeans\lib\bidata-nls.zip;D:\OAF\jdevbin\bibeans\lib\olap_api.jar;D:\OAF\jdevbin\BC4J\lib\bc4jdomorcl.jar;D:\OAF\jdevbin\BC4J\jlib\bc4jdatum.jar;D:\OAF\jdevbin\jdev\appslibrt\rosettaRt.zip;D:\OAF\jdevbin\jdev\appslibrt\iasjoc.zip;D:\OAF\jdevbin\jdev\appslibrt\pat.zip;D:\OAF\jdevbin\jdev\appslibrt\patch_jars;D:\OAF\jdevbin\jdev\appslibrt\portalFlexComps.jar;D:\OAF\jdevbin\jdev\appslibrt\svc.zip;D:\OAF\jdevbin\jdev\appslibrt\svctester.jar;D:\OAF\jdevbin\lib\xml.jar;D:\OAF\jdevbin\BC4J\lib\adfm.jar;D:\OAF\jdevbin\BC4J\jlib\adfui.jar;D:\OAF\jdevbin\BC4J\lib\adfbinding.jar;" oracle.jbo.jbotester.MainFrame -X 156A6DEE716 -H "jar:file:/D:/OAF/jdevbin/jdev/doc/studio_doc/ohj/bc4j_f1.jar!/bc4j_f1.hs"

Aug 20, 2016 12:34:01 PM oracle.adf.share.config.ADFConfigFactory findOrCreateADFConfig

INFO: oracle.adf.share.config.ADFConfigFactory No META-INF/adf-config.xml found

java.lang.IllegalArgumentException: Unknown signal: ALRM

at sun.misc.Signal.<init>(Signal.java:126)

at oracle.apps.fnd.framework.AppsDiagnosticsHandler.install(Unknown Source)

at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.initializeSignalHandler(Unknown Source)

at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.<clinit>(Unknown Source)

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:247)

at oracle.jbo.common.java2.JDK2ClassLoader.loadClassForName(JDK2ClassLoader.java:38)

at oracle.jbo.common.JBOClass.forName(JBOClass.java:164)

at oracle.jbo.common.JBOClass.findCustomClass(JBOClass.java:177)

at oracle.jbo.server.ApplicationModuleDefImpl.loadFromXML(ApplicationModuleDefImpl.java:836)

at oracle.jbo.server.ApplicationModuleDefImpl.loadFromXML(ApplicationModuleDefImpl.java:770)

at oracle.jbo.server.MetaObjectManager.loadFromXML(MetaObjectManager.java:534)

at oracle.jbo.mom.DefinitionManager.loadLazyDefinitionObject(DefinitionManager.java:587)

at oracle.jbo.mom.DefinitionManager.findDefinitionObject(DefinitionManager.java:449)

at oracle.jbo.mom.DefinitionManager.findDefinitionObject(DefinitionManager.java:382)

at oracle.jbo.mom.DefinitionManager.findDefinitionObject(DefinitionManager.java:364)

at oracle.jbo.server.MetaObjectManager.findMetaObject(MetaObjectManager.java:700)

at oracle.jbo.server.ApplicationModuleDefImpl.findDefObject(ApplicationModuleDefImpl.java:232)

at oracle.jbo.server.ApplicationModuleImpl.createRootApplicationModule(ApplicationModuleImpl.java:401)

at oracle.jbo.server.ApplicationModuleHomeImpl.create(ApplicationModuleHomeImpl.java:91)

at oracle.jbo.common.ampool.DefaultConnectionStrategy.createApplicationModule(DefaultConnectionStrategy.java:139)

at oracle.jbo.common.ampool.DefaultConnectionStrategy.createApplicationModule(DefaultConnectionStrategy.java:80)

at oracle.jbo.common.ampool.ApplicationPoolImpl.instantiateResource(ApplicationPoolImpl.java:2468)

at oracle.jbo.pool.ResourcePool.createResource(ResourcePool.java:536)

at oracle.jbo.common.ampool.ApplicationPoolImpl.prepareApplicationModule(ApplicationPoolImpl.java:2094)

at oracle.jbo.common.ampool.ApplicationPoolImpl.doCheckout(ApplicationPoolImpl.java:1961)

at oracle.jbo.common.ampool.ApplicationPoolImpl.useApplicationModule(ApplicationPoolImpl.java:2793)

at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:453)

at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:424)

at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:419)

at oracle.jbo.client.Configuration.getApplicationModule(Configuration.java:1546)

at oracle.jbo.client.Configuration.createRootApplicationModuleFromConfig(Configuration.java:1519)

at oracle.jbo.jbotester.ConnectionInfo.useApplicationModule(ConnectionInfo.java:129)

at oracle.jbo.jbotester.MainFrame.init(MainFrame.java:410)

at oracle.jbo.jbotester.MainFrame.main(MainFrame.java:394)

ADOP Errors and Solutions

Error:
Lines #(178-182):
              [EVENT]     Calling: /opt/oracle/TEST/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl
              [STATEMENT] Output directory: /opt/oracle/TEST/fs_ne/EBSapps/log/adop/7/prepare_20160416_143741/PROD_appsprod
              [UNEXPECTED]Error occurred while executing "perl /opt/oracle/TEST/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl -contextfile=/opt/oracle/TEST/fs1/inst/apps/PROD_appsprod/appl/admin/PROD_appsprod.xml -patchcontextfile=/opt/oracle/TEST/fs2/inst/apps/PROD_appsprod/appl/admin/PROD_appsprod.xml -promptmsg=hide -console=off -patchtop=/opt/oracle/TEST/fs_ne/EBSapps/patch -mode=update -sessionid=7 -timestamp=20160416_143741 -workercount=32 -outdir=/opt/oracle/TEST/fs_ne/EBSapps/log/adop/7/prepare_20160416_143741/PROD_appsprod -autoskip=No"
              [UNEXPECTED]Error while runSyncFS sub-routine is called
              [PROCEDURE] [START 2016/04/16 14:39:50] Check and Stop patch Admin Server

Lines #(179-183):
              [STATEMENT] Output directory: /opt/oracle/TEST/fs_ne/EBSapps/log/adop/7/prepare_20160416_143741/PROD_appsprod
              [UNEXPECTED]Error occurred while executing "perl /opt/oracle/TEST/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl -contextfile=/opt/oracle/TEST/fs1/inst/apps/PROD_appsprod/appl/admin/PROD_appsprod.xml -patchcontextfile=/opt/oracle/TEST/fs2/inst/apps/PROD_appsprod/appl/admin/PROD_appsprod.xml -promptmsg=hide -console=off -patchtop=/opt/oracle/TEST/fs_ne/EBSapps/patch -mode=update -sessionid=7 -timestamp=20160416_143741 -workercount=32 -outdir=/opt/oracle/TEST/fs_ne/EBSapps/log/adop/7/prepare_20160416_143741/PROD_appsprod -autoskip=No"
              [UNEXPECTED]Error while runSyncFS sub-routine is called
              [PROCEDURE] [START 2016/04/16 14:39:50] Check and Stop patch Admin Server
                [EVENT]     [START 2016/04/16 14:39:51] Check and Stop patch FS Admin Server

Lines #(188-192):
                [EVENT]     [END   2016/04/16 14:39:59] Check and Stop patch Node Manager
              [PROCEDURE] [END   2016/04/16 14:39:59] Check and Stop patch Node Manager
              [ERROR]     Prepare phase completed with errors or warnings. Please check logfiles.
              [STATEMENT] SQL statement : "  update ad_adop_sessions
  set status='F'

/opt/oracle/TEST/fs_ne/EBSapps/log/adop/7/prepare_20160416_143741/PROD_appsprod/TXK_SYNC_update_Sat_Apr_16_14_39_29_2016/txkADOPPreparePhaseSynchronize_Sat_Apr_16_14_39_29_2016.log: 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Lines #(502-504):
TIME    : Sat Apr 16 14:39:50 2016
FUNCTION: main::execADPATCH [ Level 1 ] 
ERRORMSG: Application of patch did not go through successfully.


in abort.log file if you found these lines:

create or replace package APPS_DDL AUTHID DEFINER as * ERROR at line 1: ORA-00955: name is already used by an existing object

Solution:

Execute below query:

select * from dba_objects where object_name like '%APPS_DDL%' and object_type not in ('PACKAGE','PACKAGE BODY'); 


Drop synonym appeared in above query output.

Oracle Clone keep pointing to Source Server

Error:

When Set enviornment desitnation clone keep pointing to source server, This is the bug and the solution is:

grep: /d01/oracle/TEST/fs1/inst/apps/TEST_pcltest/appl/admin/TEST_pcltest.xml: No such file or directory

grep: /d01/oracle/TEST/fs1/inst/apps/TEST_pcltest/appl/admin/TEST_pcltest.xml: No such file or directory

grep: /d01/oracle/TEST/fs1/inst/apps/TEST_pcltest/appl/admin/TEST_pcltest.xml: No such file or directory

Solution:

The issue is a bug (yet to be logged) caused when using similar hostname from Source and Target, eg test & pcltest been used for your systems, but we can easily work around the issue by modifying the node_info.txt file 

1. On Target nodes backup and then open 

/opt/oracle/TEST/fs1/FMW_Home/Oracle_EBS-app1/applications/oacore/APP-INF/node_info.txt 

/opt/oracle/TEST/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/APP-INF/node_info.txt 

Linux Errors and Solutions

Error:
Package kit error

Solution
kill the pid in the list

----------------------------------------

Error:
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle


GPG key retrieval failed: [Errno 14] Could not open/read file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

Solution
rpm --import http://oss.oracle.com/ol6/RPM-GPG-KEY-oracle

rpm -q gpg-pubkey-ec551f03-4c2d256a

Oracle EBS API Errors and Solutions

Error:

ORA-20001: SQL_PLSQL_ERROR: N, ROUTINE, MO_GLOBAL.INIT, N, ERRNO, -20001, N, REASON, ORA-20001: APP-FND-02902: Multi-Org profile option is required. Please set either MO: Security Profile or MO: Operating Unit profile option.

Solution:

Set Master Operating Unit at Site Level