Oracle Applications Patching :How to Run hrglobal driver in Oracle EBS 12.2.X Version.

Applying hrglobal driver is needed during an oracle applications upgrade or when the  payroll data is required to be analyzed as per latest code pack.

Below are the steps to apply hrglobal driver in a Oracle EBS environment (12.2.x)

Before applying hrglobal driver download the latest hrglobal patch from My Oracle Support.

Start an Oracle online EBS Patching cycle.

1.adop phase=prepare

2. Apply latest hrglobal patch

adop phase=apply patches=<patchnum>

3. Run Datainstall and hrglobal driver using below commands

ADOP utillity is intelligent enough to switch to Patch Filesystem depending on the ADOP phase being run.
For any Other manual operations, we need to set the environment to point to Patch Filesystem explicitly.

. ./EBSapps.env PATCH

+Run DataInstall

java oracle.apps.per.DataInstall apps appspassword thin test.domain.com:1521:DEV

Select the Required localisations and save the changes.

Example of Data Install Changes Summary is shown below.



          DataInstall - Actions confirmation

Do you really wish to exit and save your changes?

      [Y]      - Yes, save then exit
      [N]      - No, don't save but exit
      [Return] - To return to the DataInstall Main Menu

Enter your choice (for example Y) : Y


          DataInstall - Actions summary
          -----------------------------

The following actions will be performed:


Localisation         Product(s)               Leg. Data? Action
-------------------- ------------------------ ---------- -------------
Global               Human Resources          Installed  Install
United Arab Emirates Human Resources          Installed  Install
United Arab Emirates Payroll                  Installed  Install

Localisation   College Data? Action
-------------- ------------- -------------
United Kingdom
United States

Option                         Data?         Action
------------------------------ ------------- -------------
JIT/Geocode


Legislation                            Action
------------------------------         -------------
ALL  All Legislations


++Apply hrglobal.drv using below syntax.

adop phase=apply patchtop=$PER_TOP/patch/115 patches=driver:hrglobal.drv workers=8

4. adop phase=finalize

5. adop phase=cutover

6. adop phase=cleanup

EBS Plugin Deployment for Oracle Enterprise Manager 13c Cloud Contol (13.2.0.0.0)

Application Management Suite 13.1.1.1 is certified as a plug-in with Oracle Enterprise Manager Cloud Control 13c Release 2.
Steps to Deploy the Application Management Suite 13.1.1.1 
1.Pre-Requisite Patches

Apply the Pre Requisite patches as mentioned in Metalink Doc ID 2045552.1

2.Download the Plugin

Plugin can be downloaded in two ways.
2.1 Download the EBS plugin from the below link
http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/oem-plugins-3237574.html
Size of Plugin - ~27MB
Download the EBS plugin software to a location in OMS Home as oracle user.
Run the below command to update the plugin into Oracle Enterprise Manager
emcli import_update -file="<Location of downloaded Plugin file>" -omslocal

2.2 Download from Oracle Enterprise Manager Console 13c console using Self Update feature.


3.Deployment of EBS Plugin for Oracle Enterprise Manager  OEM 13c
[EBS Plug-in Deployment to be implemented on OMS Agent node and on all EBS Nodes where EM Monitoring is Required]
3.1 Deploy EBS Plugin to OMS Agent

Login to Oracle Enterprise Manager  OEM console to deploy plugin to OMS Agent
 Go to Setup => Extensibility => Plug-ins:
Select Oracle E-business Suite and Click Deploy-On
Provide the Required details and proceed.
[OMS Restart Required for this activity- Implemented through console]
Monitor the deployment status using below command

emctl status oms -details

3.2 Deploy Plug-in to Management Agent
 The process is similar to the deployment procedure to the OMS Agent, but in this case the EBS host targets must be selected.
[Note: Add new management agents for EBS Targets if not already available.]

Oracle Database Recovery Manager Concepts:Script to Restore Oracle Database from RMAN Backup

During Oracle Database Cloning,restore Oracle database from RMAN backup taken on Source environment to the Target environment.


Steps for Database restore using Oracle Recovery Manager (RMAN)

1.Connect to target database 

Startup the Target database in Nomount stage

SQL> Startup nomount

2.Run the below Command to Restore Oracle database using RMAN 

rman auxiliary / 
run
{
duplicate database to 'DEV' backup location '/DB_BACKUP/Daily_Backup/daily_bkp_04102018';
}


Where DEV is the target instance for Restore
Backup Location - Directory where RMAN backup is located

3. Verify that Oracle database is up and running.

Resolve Error ORA-28500 while accessing Heterogenous Database link (ORACLE TO MSSQL)

After creating a heterogenous database link from Oracle database  to MSSQL Server, getting error message "ORA-28500"

SQL>select sysdate from dual@DBLINK_MSSQL;
                         *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host
Name and Port Number. {08001}
ORA-02063: preceding 2 lines from MSSQL_INTG


Reason

$ telnet MSSQLDB 1433
MSSQLDB/1433: Name or service not known

$ telnet 10.x.x.x 1433
Trying 10.x.x.x...
Connected to 10.x.x.x (10.x.x.x).
Escape character is '^]'.
^]
telnet> logout
^CConnection closed by foreign host.


I am able to telnet to MSSQL database Server using IP address , but cannot telnet to MSSQL server using hostname


Solution

Contact your Network admin to allow access to 1433 using Servername (MSSQLDB)

(or)

Change the below configuration

1. Set Oracle Home to point to your Gateway Oracle HOME

2. Navigate to $ORACLE_HOME/dg4msql/admin

Change below values in initdg4msql.ora

changed HS_FDS_CONNECT_INFO from MSSQLDB to IP address

#
HS_FDS_CONNECT_INFO=10.x.x.x:1433//wfcdb
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER


3. Restart the listener in Gateway Oracle HOME

4. Verify the database link is working fine.

SQL>select sysdate from dual@DBLINK_MSSQL;

==>Above Query should return current system date.

Resolve ORA-28545 When trying to access database link between Oracle database and MSSQL

Heterogenous database link between Oracle database and MSSQL was created using the below command.

SQL> create public database link mssql connect to "integration" identified by "*****" using 'dg4msql'; 

Database link created. 

Error Message

SQL> select sysdate from dual@mssql 
2 ; 
select sysdate from dual@mssql 
* 
ERROR at line 1: 
ORA-28545: error diagnosed by Net8 when connecting to an agent 
Unable to retrieve text of NETWORK/NCR message 65535 
ORA-02063: preceding 2 lines from MSSQL 


LISTENER_GTWY = 
(DESCRIPTION_LIST = 
(DESCRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclegtwy.dmn)(PORT = 1526)) 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526)) 
) 
) 
(SID_NAME=dg4msql) 
(ORACLE_HOME=/orabin/gateway_mssql/product/12.1.0/tghome_1) 
(ENV="LD_LIBRARY_PATH=/orabin/gateway_mssql/product/12.1.0/tghome_1/dg4msql/driver/lib:/orabin/gateway_mssql/product/12.1.0/tghome_1/lib") 
(PROGRAM=dg4msql) 


Resolution 
Listener file on the Oracle Database gateway server should look like below 

LISTENER_GTWY = 
(DESCRIPTION_LIST = 
(DESCRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST =oraclegtwy.dmn)(PORT = 1526)) 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526)) 
) 
) 

SID_LIST_LISTENER_GTWY =------------------------>Missing 
 (SID_LIST= 
 (SID_DESC= 
 (SID_NAME=dg4msql) 
 (ORACLE_HOME=/orabin/gateway_mssql/product/12.1.0/tghome_1) 
 (ENV="LD_LIBRARY_PATH=/orabin/gateway_mssql/product/12.1.0/tghome_1/dg4msql/driver/lib:/orabin/gateway_mssql/product/12.1.0/tghome_1/lib") 
 (PROGRAM=dg4msql) 
 ) 
 ) 

How to Resolve ORA-12514 error While accessing Oracle database link?

Our client had requirement to pull data from MSSQL server to Oracle Database. I installed Oracle Gateway Server for MSSQL and configured database link to access the MSSQL Database.
When trying to access a heterogenous database link between Oracle Database 12c and MSSQL database, some users were getting the below error
ORA-12154: TNS:could not resolve the connect identifier specified.

Analysis

Database link was created on the Oracle Database environment to access data from MSSQL Server.
Database link was created using below command

create public database link DBLINK_INTG connect to "MYSQL" identified by "Password" using 'dg4msql';

Error

SQL>select sysdate from dual@DBLINK_INTG;
ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 -  "TNS:could not resolve the connect identifier specified"
*Cause:    A connection to a database or other service was requested using
           a connect identifier, and the connect identifier specified could not
           be resolved into a connect descriptor using one of the naming methods
           configured. For example, if the type of connect identifier used was a
           net service name then the net service name could not be found in a
           naming method repository, or the repository could not be
           located or reached.
*Action:   
           - If you are using local naming (TNSNAMES.ORA file):
           - Make sure that "TNSNAMES" is listed as one of the values of the
           NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA)
           - Verify that a TNSNAMES.ORA file exists and is in the proper
           directory and is accessible.
           - Check that the net service name used as the connect identifier
           exists in the TNSNAMES.ORA file.
           - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
           file.  Look for unmatched parentheses or stray characters. Errors
           in a TNSNAMES.ORA file may make it unusable.
           - If you are using directory naming:
           - Verify that "LDAP" is listed as one of the values of the
           NAMES.DIRETORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA).
           - Verify that the LDAP directory server is up and that it is
           accessible.
           - Verify that the net service name or database name used as the
           connect identifier is configured in the directory.
           - Verify that the default context being used is correct by
           specifying a fully qualified net service name or a full LDAP DN
           as the connect identifier
           - If you are using easy connect naming:
           - Verify that "EZCONNECT" is listed as one of the values of the
           NAMES.DIRETORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA).
          - Make sure the host, port and service name specified
           are correct.



Solution

Recreate Heterogenous Database link using below command


create public database link DBLINK_INTG connect to "MYSQL" identified by "Password" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Test1.domain.com)(PORT=1524))(CONNECT_DATA=(SID=dg4msql))(HS=OK))'
  

Oracle EBS Concurrent Manager Troubleshooting

In Oracle EBS version 12.2.6, Internal concurrent manager had an unexpected Outage.
ICM logfile has the error message  ORA-04068: existing state of packages has been discarded
====================

Shutting down Internal Concurrent Manager : 30-JAN-2018 12:09:54

List of errors encountered:
.............................................................................

_ 1 _
Error in DBMS lock request by handle. (ROUTINE=AFPGMG) (REASON=ORACLE
error 4068 in afpdlrh

Cause: afpdlrh failed due to ORA-04068: existing state of packages has
been discarded
ORA-04061: existing state of package body "APPS.FND_DCP" has been
invalidate


==========================================
Issue Resolution

1. Shut down the Concurrent manager completely using adcmctl.sh

cd $ADMIN_SCRIPTS_HOME

adcmctl.sh stop apps/<appspassword>

2. Compile the invalids in database

SQL>alter package FND_DCP compile body;

OR

Run the utlrp.sql script to recompile all the invalid objects in the database.

3. Startup Concurrent manager using adcmctl.sh

cd $ADMIN_SCRIPTS_HOME

adcmctl.sh start apps/<appspassword>

4. Verify Oracle EBS Concurrent manager is running fine.