Tuesday, May 29, 2018

ORA-04068 Errors from Oracle Packages

The following queries can useful when investigating ORA-4068 errors that will not clear.


To check the timestamp associated with a particular package / procedure / function run the below query:
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';


Column OBJECT_NAME format a15
Column OBJECT_TYPE format a15
Column OWNER format a15
Column STATUS format a8
SELECT object_name, object_type, owner, status, last_ddl_time FROM
 dba_objects WHERE object_name = '&OBJECT';

 To check the timestamp for dependant packages:
SELECT object_name, object_type, owner, status, last_ddl_time FROM
dba_objects WHERE ( object_name, object_type ) IN ( SELECT
referenced_name, referenced_type FROM dba_dependencies WHERE name =
'&OBJECT' );

For situations where you suspect a dependency check could be faulty:


alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
SELECT du.name duname,do.name dname, pu.name puname, po.name pname, p_timestamp, po.stime p_stime
 FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po,sys.user$ du,sys.user$ pu
 WHERE p_obj#=po.obj#(+)
 AND d_obj#=do.obj#
 AND do.owner# = du.user#
 AND po.owner# = pu.user#
 AND do.status=1 /*dependent is valid*/
 AND po.status=1 /*parent is valid*/
 AND po.stime!=p_timestamp /*parent timestamp does not match*/
 AND do.type# not in (13,28,29,30) /*dependent type is not a type or java*/
 AND po.type# not in (13,28,29,30) /*parent type is not a type or java*/
 ORDER BY 4,2;

The above query  should not return any rows. If it does then both items referred to should be recompiled.


 - using  ALTER PACKAGE <package_name> COMPILE REUSE SETTINGS;
 - using  ALTER PACKAGE <package_name> COMPILE;

To check for details of why a package will not recompile, check to see if there were any errors reported in the last compilation:

SELECT name, type, text FROM dba_errors WHERE name = '&OBJECT';

References:
ORA-04068 Errors from User-Written and Oracle Packages (Doc ID 1161225.1)
ORA-4068 Errors for Valid Objects (Doc ID 835792.1)

Monday, April 30, 2018

adop phase=apply hotpatch=yes

An EBS 12.2.X patch is normally applied using an ADOP online patching cycle, which is the only case tested internally before 12.2 patches are released (unless documented otherwise).

We can  use Hotpatch mode only for certain patches, such as NLS and Online Help, or when it is specifically documented in the patch readme. The safe use of hotpatch mode is restricted to cases where we know in advance that the patch will not modify any existing schema or code object used by the running applications instance. If you attempt to apply an unsafe patch using hotpatch mode, the patch may fail or users may experience application failure.

In EBS release 12.2, certain kinds of patches are even less stable for use with “hotpatch” than they were in EBS 12.1. when an unsafe patch is applied via hotpatch the system will go through intermittent states of:
•invalid objects, missing code dependencies
•mismatches between code level of file system and database
•missing column data, or other data integrity problems
•out-of-date indexes and materialized view definitions
•invalid data in runtime caches

Important FAQ about HOTPATCH mode in adop
1)When you apply the patch using hotpatch in adop,  adop will still shows phase APPLY status ACTIVE after having applied successfully a patch using
 #adop phase=apply hotpatch=yes patches=123456

There were no errors in the adop patch logs. But adop status still shows:
Node Name   Node Type   Phase   Status   Started   Finished
ed-olapplin1 master PREPARE NOT APPLICABLE
APPLY ACTIVE 01-APR-18 09:18:18 +04:00 <<<< APPLY phase is still ACTIVE
FINALIZE NOT STARTED
CUTOVER NOT APPLICABLE
CLEANUP NOT STARTED

One cannot run adop phase=cutover or adop phase=cleanup because there are no Online Patching cycle that was started. The patch was applied in hotpatch=yes mode on the RUN filesystem

Solution:
a) When the patch is applied with adop hotpatch=yes mode, there are no Online Patching cycle started yet — all is on the RUN filesystem — so there are no adop commands that can be used like adop phase=cutover or adop phase=cleanup.
b) When one applies in hotpatch mode the APPLY phase will remain active until such time as run a PREPARE during the next patching cycle.
c) The APPLY phase is active because you can potentially run another hotpatch. Once one apples a hotpatch, there are no further actions required.

2)  How hotpatch on run filesystem are propagated to patch file system on next prepare phase?
Solution:
Run the prepare command when we want to start the new online patch cycle
#adop phase=prepare

At that time, adop config change detector will find that the RUN filesystem has had patches applied in hotpatch mode and will sync the PATCH filesystem as part of the prepare phase.

3) Do we have abort phase in Hotpatch mode?
Solution
The “adop phase=abort” command is only applicable to an online patching cycle, which you would have started by running adop phase=prepare. A hotpatch CANNOT be aborted with the adop phase=abort command.

If a hotpatch session fails for an approved patch (NLS, Online Help, or documented in the patch readme) you should:
a. Investigate the error and try to complete the hotpatch session by re-applying the hotpatch with restart=yes
OR
b. Apply a replacement patch with abandon=yes.
It should be possible to just abandon the hotpatch session, and then start a new ADOP online patching cycle (Make sure you are on AD/TXK Delta 6 or higher).

For any queries regarding ADOP utility email me on samiappsdba@gmail.com

Monday, March 12, 2018

Setup Oracle Business Intelligence OBIEE 12c Step by Step

Following are the steps to do the Oracle Business Intelligence 12c Setup.
1. Install JDK 1.8
2. Setup Weblogic 12c
3. Oracle Business Intelligence setup
4. RCU to create BI related schemas in database
5. Configure Oracle BI Domain
6. Oracle BI Developer Client tool to create BI repository files (.rpd)
7. Upload the RPD BI Repository file to BI Server
8. Run Analysis through analytics web console


All required software to do this setup are available in below link,
[OF COURSE YOU MUST LICENSE ALL REQUIRED ORACLE SOFTWARE BEFORE USING IT]
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/business-intelligence-3046226.html


Step 1: Install JDK 1.8
[obieeuser@ed-olbiapplin1 ~]$ which java
/u01/obiee/jdk1.8.0_151/bin/java
[obieeuser@ed-olbiapplin1 ~]$ java -version
java version "1.8.0_151"
Java(TM) SE Runtime Environment (build 1.8.0_151-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.151-b12, mixed mode)

2. Setup Weblogic 12c












3. Oracle Business Intelligence Setup
4. RCU to create BI related schemas in database
Selecting ‘Oracle Business Intelligence’ will select all the other options check boxes automatically.


5. Configure Oracle BI Domain

6. Oracle BI Developer Client tool 12.2.1.3.0 to create BI repository files (.rpd)


Start Oracle BI Administrator Tool by running below file,
D:\BIToolHome\bi\bitools\bin\admintool.cmd
We can now open any repository .rpd file and it will display all the three layers of the administrator tool as follows,
Physical Layer, Model and Mapping Layer (MML) and Presentation layer
7. Upload the BI Repository file (.rpd) to BI Server
8. Run Analysis through analytics web console
You will now be able to access the below analytics tool to create Analysis and Dashboards based on the repository .rpd file that we uploaded in step 7.
http://ed-olapplin1.samiora.blogspot.com:9502/analytics


9. Creating a Repository Using the Oracle Business Intelligence Administration Tool:
Below link on Oracle site is a step by step tutorial covering using the Oracle Business Intelligence (BI) Administration Tool to build, modify, enhance, and manage an Oracle BI repository.
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi1113/biadmin11g_01/biadmin11g.htm



The figure shows how a logical SQL query traverses the layers of an Oracle BI Repository.
For any further queries on topics related to this article please email me on samiappsdba@gmail.com

Monday, February 12, 2018

Deregister Oracle EBS from Oracle Access Manager

Oracle Access Manager maintains a single registration for your Oracle E-Business Suite instance and does not distinguish between run and patch file systems. Hence removing the registration from Oracle Access Manager will affect the running system.


Below are the steps to deregister your Oracle E-Business Suite instance from Oracle Access Manager:


Source the Oracle E-Business Suite environment file of your run file system.
$ cd <EBS_BASE_HOME>
 $ . EBSapps.env
 $ echo $FILE_EDITION
EBS_BASE_HOME is the top directory where fs1, fs2, and others are installed.
Type "R" to select the run file system environment when prompted. Echo $FILE_EDITION returns "run" to indicate that the run file system is sourced. Ensure there is no active Online Patching cycle.


Stop the OHS server on the Oracle E-Business Suite Environment:


$ adapcctl.sh stop
Execute the following command to deregister Oracle E-Business Suite from Oracle Access Manager.


Reference:
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)



[applmgr@ed-olapplin2 ~]$ txkrun.pl -script=SetOAMReg -deregisteroam=yes
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /u02/ebsdev/fs2/inst/apps/ebsdev_ed-olapplin1/logs/appl/rgf/TXK/txkSetOAMReg_Sun_Jan_28_14_07_54_2018.log
Enter OAM Console URL (for ex: http://myoam.us.oracle.com:7001): http://oamsrv1.wl.oracle.nz:7012
Enter OAM console user name (for ex: weblogic): weblogic
Enter OAM console password:
Enter APPS password:
######################################################################
oamHost                 = http://oamsrv1.wl.oracle.nz:7012
oamApplicationDomain    = ebsdev_f5url.wl.oracle.nz_443
oamHostIdentifier       = ebsdev_f5url.wl.oracle.nz_443
contextFile             = /u02/ebsdev/fs2/inst/apps/ebsdev_ed-olapplin1/appl/admin/ebsdev_ed-olapplin2.xml
webGateInternal         = Yes
ebsProfileLevel         = Site
webGateUrl              = https://f5url.wl.oracle.nz:443
contextRoot             = accessgate
######################################################################
Do you wish to continue (y|n)?
y


admanagedsrvctl.sh stop oaea_server1
perl $AD_TOP/patch/115/bin/adProvisionEBS.pl ebs-delete-managedserver -contextfile=$CONTEXT_FILE -managedsrvname=oaea_server1
perl $FND_TOP/patch/115/bin/txkSetAppsConf.pl -contextfile=$CONTEXT_FILE -configoption=removeMS -accessgate=ed-olapplin1.wl.oracle.nz:6801


[applmgr@ed-olapplin1 ~]$ txkrun.pl -script=SetOAMReg -deregisteroam=yes
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /u02/ebsdev/fs2/inst/apps/ebsdev_ed-olapplin1/logs/appl/rgf/TXK/txkSetOAMReg_Sun_Jan_28_14_07_54_2018.log
Enter OAM Console URL (for ex: http://myoam.us.oracle.com:7001): http://oamsrv1.wl.oracle.nz:7012
Enter OAM console user name (for ex: weblogic): weblogic
Enter OAM console password:
Enter APPS password:
######################################################################
oamHost                 = http://oamsrv1.wl.oracle.nz:7012
oamApplicationDomain    = ebsdev_f5url.wl.oracle.nz_443
oamHostIdentifier       = ebsdev_f5url.wl.oracle.nz_443
contextFile             = /u02/ebsdev/fs2/inst/apps/ebsdev_ed-olapplin1/appl/admin/ebsdev_ed-olapplin1.xml
webGateInternal         = Yes
ebsProfileLevel         = Site
webGateUrl              = https://f5url.wl.oracle.nz:443
contextRoot             = accessgate
######################################################################
Do you wish to continue (y|n)?
y

admanagedsrvctl.sh stop oaea_server1
perl $AD_TOP/patch/115/bin/adProvisionEBS.pl ebs-delete-managedserver -contextfile=$CONTEXT_FILE -managedsrvname=oaea_server1
perl $FND_TOP/patch/115/bin/txkSetAppsConf.pl -contextfile=$CONTEXT_FILE -configoption=removeMS -accessgate=ed-olapplin1.wl.oracle.nz:6802


Now run autoconfig on all nodes.


Reference:
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)


$ txkrun.pl -script=SetOAMReg -deregisteroam=yes -ebsProfileLevel=[Site|Server]


This will switch back the Oracle E-Business Suite profile options Application Authenticate Agent (APPS_AUTH_AGENT) and Applications SSO Type (APPS_SSO) to local login.


 Specify -ebsProfileLevel=Server if you registered the instance at server level. This will not affect the site level profiles, and only remove the profiles at server level for the server that you deregister.


The script will prompt for the following information.

•Enter OAM console URL (for example: http://myoam.us.oracle.com:7001)
•Enter OAM console user name (for example: weblogic)
•Enter OAM console password
•Enter APPS password
Enter the required information when prompted.


The script will provide a summary of input values. Confirm that these are correct and start the deregistration.


Do you wish to continue (y|n)? y


The script will now perform the following main tasks automatically:
•Deregister Oracle E-Business Suite AccessGate with Oracle Access Manager.
•Disable WebGate in your Oracle E-Business Suite webtier.
•Clear Oracle E-Business Suite profile options Application Authenticate Agent
(APPS_AUTH_AGENT) and Applications SSO Type (APPS_SSO) to switch back to local login. If you registered the instance with -ebsProfileLevel=Site (default), deregistration will clear the profiles at SITE level. If you registered the instance with -ebsProfileLevel=Server, deregistration will clear the profiles at SERVER level.


Alternatively, you can execute the script with parameters. For example:


$ txkrun.pl -script=SetOAMReg -deregisteroam=yes \
 -oamHost=http://myoam.us.oracle.com:7001 \
 -oamUserName=weblogic \
 -skipConfirm=yes


The script must complete successfully. Review the log files for any error messages.


The script will not automatically delete the following entries, as you may have also used these for other partner applications:

•Authentication Scheme (by default named EBSAuthScheme)
•Authentication Module (by default named LDAP_EBS)
•Identity Store (by default named OIDIdentityStore)


If you exclusively used these entries for the Oracle E-Business Suite instance that you deregistered, you may delete the Authentication Scheme, Authentication Module, and Identity Store in the order listed, using your OAM Administration Console.


After de-registering your Oracle E-Business Suite instance from Oracle Access Manager, you no longer need the Oracle E-Business Suite AccessGate deployment. Delete your Oracle E-Business Suite AccessGate using your WebLogic Administration Console, for example:
http://ebshost.example.com:7001/console


In the WebLogic Administration Console, navigate to EBS_domain_sid > Deployments, stop then delete the Oracle E-Business Suite AccessGate application named "accessgate". Ensure that you click 'Activate Changes' in the 'Change Center' pane, for the changes to take effect.


If you do not use the data source "OAEADatasource" for any other application, you may also delete the datasource. Navigate to EBS_domain_sid > Services > Data Sources, and delete data source "OAEADatasource". Ensure that you click 'Activate Changes' in the 'Change Center' pane, for the changes to take effect.


Delete the managed server on which accessgate was deployed:

1.If the managed server oaea_server1 is currently running, shut it down as follows:

$ sh $ADMIN_SCRIPTS_HOME/admanagedsrvctl.sh stop oaea_server1
The script will prompt for the following passwords:
•Enter the WebLogic Admin password.
Enter the required information when prompted.


2.Run the command below on the application tier node where the oaea_server1 managed server resides. This will delete the managed server, and also update the respective context variables that contain references to the deleted managed server:

$ perl $AD_TOP/patch/115/bin/adProvisionEBS.pl \
 ebs-delete-managedserver \
 -contextfile=$CONTEXT_FILE -managedsrvname=oaea_server1
The script will prompt for the following passwords:
•Enter the APPS Schema password.
•Enter the WebLogic AdminServer password.
Enter the required information when prompted.
The following confirmation message will be displayed: ManagedServer oaea_server1 deleted successfully.


3.Remove the managed server and port from the mod_wl_ohs.conf configuration:

$ perl $FND_TOP/patch/115/bin/txkSetAppsConf.pl \
 -contextfile=$CONTEXT_FILE \
 -configoption=removeMS \
 -accessgate=<host>.<domain>:<port>
To determine the value of the Port that was used for the oaea_server1 managed server, search for 's_wls_oaeaport' in $CONTEXT_FILE.


Stop and restart the Oracle E-Business Suite Application Tier services.


Reference: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)

Monday, January 15, 2018

Performance Tune Oracle SOA 12c EM Console

This article applies to Enterprise Manager for Fusion Middleware Version 11.1.1.2.0 and later, i.e., it applies to 11g and 12c. Logging into Enterprise Manager Fusion Middleware Control (FMWC) takes a long time.  Sometimes as long as 45-60 seconds.  This is often viewed as slow response time, performance or hanging issues. This time is expected because EM discovery 'mbeans' need to be invoked for every target.
Solution is to cache the discovery results in the servlet context and use it for subsequent logins. This discovery result will be shared by all the FMWC users. This will still require the entire discovery to be done at least once. 

Enable the “Data Display Option”: Login into EM console and make the data display option to 30 minutes default, so when user will login into EM console, it will load data of last 30 minutes process and it will load EM console faster as with default configuration.
  • Enable the checkbox for below options-
  • Disabled fetching of instance and fault matrices count.
Restrict display of instance and fault to the last 30 minute


Decrease the frequency of DMS application: EM has one inbuilt application called Dynamic Monitoring System (DMS) which does the status collection for all the targets wherever DMS got deployed, if the frequency of DMS stats collection will be too fast then it makes EM console to slow down. To increase the frequency here are the steps-
Increased below parameters in dms_config.xml file given at this location –
$MiddlewareHome/fmw/soa11.1.1.6/oracle_common/modules/oracle.dms_11.1.1/server_config
prefetch intervalSeconds from 15 second to 120,
Discover intervalSeconds from 180 second to 300 seconds
<collectorConfiguration>
   <prefetch intervalSeconds="120" removeCycle="2" isDefault="true"/>
   <prefetch intervalSeconds="300" removeCycle="3"/>
   <discover intervalSeconds="300"/>
</collectorConfiguration>

Disabled “BPEL recovery console” option from Dashboard: Once you logged in into EM console, during dashboard page loading, EM will try to fetch the data from “DLV_MESSAGE” tables for “invoke” and “callback” activities which take longer time since DLV_ MESSAGE table normally is quite huge.
In order to disable the same please perform this –
EM console>>Farm>>soa-infra>>Administration>>System Mbeam Browser>> Filter>> Type bean name "oracle.as.soainfra.config:name=soa-infra,*", >> In result click on "AduitConfig" attribute and change the "bpelRecoveryStatus=Off", default value for bpelRecoveryStatus is "all"

Added JVM parameter at domain level “-Dweblogic.management.disableManagedServerNotifications=true”
Above JVM parameter has been suggested by Oracle, this parameter will help to reduce JMX notification which occur between Admin and Managed servers whenever any new component get added, any state get changes etc.
This configuration got added only for Admin Server Instance, not at Manage server level, and we have notice huge benefit in EM after this change, now EM was not at all hanging during login time, internal link were slow but at least multiple users were able to login into EM.

Increased cache timeout for discovery: During login process in EM console, EM does three things:
  • Authentication
  • Discovery of targets
  • Loading the page.
 Discovery of target step was taking too long around 10 minute since Domain was big and having a list of targets.

From Oracle note 1423893.1 we can implement to cache the discovery result, so the sub sequent login attempt will be fast - Following given Mbeans attributes has been added into to improve performance.

All timing for mbeans in milliseconds. After implementing above change, very first user login will take time after restart of Admin and the discovery result will be stored in cache and sub sequent login attempt will be fast.
  • If the caching is enabled, fmwc will use the cached discovery results.
  • The default setting is "not use the cached results"










  • This step applies to FMW 11g & 12c.

    -If FMW version is lower than 11.1.1.6,  upgrade to FMw 11.1.1.6 or apply available patch 13251077 for your version.
    -If applying patch 13251077, pay attention to patch README for setting of ORACLE_HOME.

    Navigate to fmwc System mBean browser. (Screenshots are shown for fmw 11g, it is similar for 12c)

    Access following AdminServer mBean for setting the cache property.
    • emoms.props:Location=AdminServer,name=emoms.properties,type=Properties,Application=em
    Setting following three properties.  Unless using non-default values, the last two properties are optional.

    # Enable caching of FMw Discovery data and use it for other subsequent users.
    # Values=true/false   Default=false
    oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_USE_CACHED_RESULTS=true
    # If caching of discovery data is true, this parameter indicates how long the discovery data
    # from cache should be used before requiring a fresh discovery.
    # Time value is in milliseconds.  Default is 7200000 milliseconds.
    oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_MAX_CACHE_AGE=7200000
    # If caching of discovery data is true, a user logs in and a discovery session is in progress,
    # this parameter indicates how long the user can wait for current discovery to complete.
    # After this wait time is elapsed and discovery is still not finished:  If there is already data
    # in cache it will be used, else the user will launch a new discovery session.
    # Time value is in milliseconds.  Default is 10000 milliseconds.
    oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_MAX_WAIT_TIME=10000

    If new targets are added after enabling discovery cache and new targets are not displayed in fmwc, perform a manual refresh of the Farm to update the discovery cache.

    Please subscribe to the blog and For any further queries you can email me on samiappsdba@gmail.com

    I wish you all a Very Happy and Prosperous New Year 2018.