Adding a Host Target in EM 13C

Before adding any Software Targets such as Databases, EBS, Middleware it is mandatory to add the Host Targets first in EM. In this post, we will see the simple process of adding a Host Target in EM. I am going to add a host “apps.example.com” which actually has an Oracle EBS Vision Instance running on it. Before you add the Host you need ensure below are done already:

No firewall between OMS (EM) Host and Target Host. If you cannot open all ports, at least open the needed ports for e.g. 3872 on Target Host and 4889,4903 ports on EM. Always check ports needed to be opened using “emctl status oms -details”
Ensure both hosts are reachable from one another
Create directory for emagent in Target Host for e.g. /u01/install/emagent

Adding Host Target

Adding a Host Target is very easy and self explanatory. Please find the screenshots below.

Using emctl command

Using the emctl status agent -details, you can find details of the Agent and its status as shown in the screenshot above.

Known Issues and Suggestions

You might be facing issues related to firewall, OS permissions on agent, unavailable of OS packages such as ZIP, UNZIP. These you should be able to fix easily ideally.

How to resolve issue :

Clearing an Enterprise manager agent that fails to upload

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

OPMN fails to start after latest EBS 12.2 ETCC patches

Sometimes applying Tech Stack patches lead to bugs or issues which we know. This is one such instance where the OPMN was failing to start after applying latest ETCC patches. So let us see in detail:

I have applied latest ETCC patches which includes patches for WebTier, 10.1.2.3, WLS etc. Once they are applied, opmn was failing to start and when I checked the OPMN log file, I have found the below error in it:

[opmn] [ERROR:1] [] [ons-secure] Connection server SSL set credentials failed (43084)
[opmn] [ERROR:1] [222] [ons-secure] SSL initialization failed

Oh! But wait.. I don’t have SSL or TLS configured in my environment but why is it complaining about SSL here? So apparently! after applying latest security patches of Oracle Process Manager and Notification Service (OPMN) and Oracle HTTPS Server (OHS) 11.1.1.9 components for Oracle E-Business Suite, this is expected.

Why does it happen?

The FMW no longer accepts the usage of certificates signed by MD5withRSA and requires using SHA256withRSA or better. So even though you don’t use SSL or TLS in EBS, you still have to create self-signed certificates and import them to wallet.

The detailed steps are present below:

Prerequisite Steps to Configure Oracle Fusion Middleware 11.1.1.9 Components for Oracle E-Business Suite Release 12.2 Before Applying the July 2019 and Later FMW OSS Security Patch (Doc ID 2555355.1)

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

AD/TXK 12 are now available for EBS 12.2

Oracle released update to the AD/TXK on 7th Aug, 2020. The previous version for R12.AD.C.Delta.11 was one of the major updates and there has not been a update to AD/TXK since a long time.

Oracle now released for R12.AD.C.Delta.12 and R12.TXK.C.Delta.12. There are no enhancements for any of the AD utilities and mostly its the bug fixes.

Check the documents below for more details:

Oracle E-Business Suite Applications DBA and Technology Stack Release Notes for R12.AD.C.Delta.12 and R12.TXK.C.Delta.12 (Doc ID 2649885.1)

Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1)

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Oracle Statspack Installation / Re-Creation

Why we need statspack? However for some non critical database where we don’t have license of tuning pack. Hence we need to survive on statspack.

1) Take export backup of exiting perftstat user

2) Drop statspack

 @?/rdbms/admin/spdrop.sql;

3) Create stastpack

 @?/rdbms/admin/spcreate.sql;

4) Change stastpack snap level to level 6

BEGIN
statspack.modify_statspack_parameter(i_snap_level=>6, i_modify_parameter=>'true');
END;
/

select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;

5) Take manual snap

 exec PERFSTAT.statspack.snap;

6) Schedule statspack auto jobs for statspack snap ( Please note job id)

 @?/rdbms/admin/spauto.sql

7) Change snap interval to 30 min

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=826;

exec dbms_job.interval(826,'sysdate+(1/48)');

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT , 
to_char(next_date ,'DD-MON-YYYY:HH24:MI:SS') "next date" , 
failures from dba_jobs where JOB=826;

select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS') "Date/Time" 
from stats$snapshot,v$database;

8) After 30 minutes verify snap interval working fine with 30 min and level 6

@?/rdbms/admin/spreport.sql

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Clearing an Enterprise manager agent that fails to upload

This is the standard set of actions that I go through when I have problems with an EM agent that a stop/start/upload does not resolve

The two types of errors I generally see are

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

Starting agent …………………………… started but not ready.

Follow the steps below, which can be cut and pasted

export AGENT_HOME=/app/oracle/agent13c/agent_inst

Stop the agent

$AGENT_HOME/bin/emctl stop agent

Remove the old log files from AGENT_HOME/sysman/log

Delete any pending upload files from the agent home

rm -r $AGENT_HOME/sysman/emd/state/*

rm -r $AGENT_HOME/sysman/emd/upload/*

rm $AGENT_HOME/sysman/emd/lastupld.xml

rm $AGENT_HOME/sysman/emd/agntstmp.txt

rm $AGENT_HOME/sysman/emd/protocol.ini

Start the agent

$AGENT_HOME/bin/emctl start agent

Issue an agent clearstate from the agent home

$AGENT_HOME/bin/emctl clearstate agent

Force an upload to the OMS

$AGENT_HOME/bin/emctl upload agent

Finally I sometimes need to re-secure the agent

$AGENT_HOME/bin/emctl secure agent

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Clearing an Enterprise manager agent that fails to upload

This is the standard set of actions that I go through when I have problems with an EM agent that a stop/start/upload does not resolve

The two types of errors I generally see are

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

Starting agent …………………………… started but not ready.

Follow the steps below, which can be cut and pasted

export AGENT_HOME=/app/oracle/agent13c/agent_inst

Stop the agent

$AGENT_HOME/bin/emctl stop agent

Remove the old log files from AGENT_HOME/sysman/log

Delete any pending upload files from the agent home

rm -r $AGENT_HOME/sysman/emd/state/*

rm -r $AGENT_HOME/sysman/emd/upload/*

rm $AGENT_HOME/sysman/emd/lastupld.xml

rm $AGENT_HOME/sysman/emd/agntstmp.txt

rm $AGENT_HOME/sysman/emd/protocol.ini

Start the agent

$AGENT_HOME/bin/emctl start agent

Issue an agent clearstate from the agent home

$AGENT_HOME/bin/emctl clearstate agent

Force an upload to the OMS

$AGENT_HOME/bin/emctl upload agent

Finally I sometimes need to re-secure the agent

$AGENT_HOME/bin/emctl secure agent

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Monitor Data Guard

Oracle Data Guard concept make sure high availability, disaster recovery and data protection of your enterprise database. In Case of disaster or data correction your database can easily run on data guard.

Data Guard can be of two types:

Physical Stand By Database-

Physical Stand by Database provides physical identical copy of your database. It has similar schema and schema. Physical Stand by synchronized by applying redo logs from primary database.

Logical Stand by Database-

Logical Stand by database provides logical information of the data files. It can be of different structure and physical org. Logical Stand by synchronizes by using sql statement and then execute sql statement in logical database.

Query to monitor the data guard status:

Run in Production to get the database role and thread and sequence of archived_log

a. select name, database_role from v$database;

b. select thread#,max(sequence#) from v$archived_log group by thread#;

Run in DR Database:

a. select thread#,max(sequence#) from v$log_history group by thread#;
b. select name,database_role from v$database;

Command to see MRP & RFS services are running or not

select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;

Take current sync status using below query:

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

If there is any lag between the Production Database versus Standby Database then you have to check the following:

Space in DR using df -h
MRP and RFS status using below query
select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;
If MRP or RFS has issue, you may have to restart it
ALTER database RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database mount standby database;
alter database recover managed standby database disconnect;
You may have to manually copy the Archive from Production to stand by.

In this way you can monitor the DR and incase of issue you can resolve the issue related to DR.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Monitor Data Guard

Data Guard can be of two types:

Physical Stand By Database-

Physical Stand by Database provides physical identical copy of your database. It has similar schema and schema. Physical Stand by synchronized by applying redo logs from primary database.

Logical Stand by Database-

Query to monitor the data guard status:

Run in Production to get the database role and thread and sequence of archived_log

a. select name, database_role from v$database;

b. select thread#,max(sequence#) from v$archived_log group by thread#;

Run in DR Database:

a. select thread#,max(sequence#) from v$log_history group by thread#;
b. select name,database_role from v$database;

Command to see MRP & RFS services are running or not

select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;

Take current sync status using below query:

If there is any lag between the Production Database versus Standby Database then you have to check the following:

Space in DR using df -h
MRP and RFS status using below query
select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;
If MRP or RFS has issue, you may have to restart it
ALTER database RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database mount standby database;
alter database recover managed standby database disconnect;
You may have to manually copy the Archive from Production to stand by.

In this way you can monitor the DR and incase of issue you can resolve the issue related to DR.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Compile Invalid Packages in Oracle EBS Database

EBS Functionality breaks if dependent packages become invalid. While troubleshooting any E-Business Suite Functional issue, always check if any packages are changed, modified, or become invalids.

Query to check invalids:

Few queries you must use to check the status of the invalids.

Total Number of Invalids:

select count(*) from dba_objects where status=’INVALID’;

Number of Invalids with schema details

    col owner for a30

    select owner, object_type, count(*) from dba_objects where status='INVALID'

    group by owner, object_type;

Objects which are invalids in the database along with the OWNER name

    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

How to Compile Invalids

Manual approach – Compile each package or package body manually.

alter package <package_name> compile body;

alter package <package_name> compile;

Similarly, we can compile other object_type like VIEW, FUNCTION, SYNONYM, TRIGGER

and MATERIALIZED VIEW

Use the same above query to compile.

alter procedure <procedure_name> compile;

alter synonym <synonym_name> compile;

alter trigger <trigger_name> compile;

DBMS_DDL Package method:

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');

EBS 12.2 Method

SQL> exec AD_ZD.compile ('XX_DETAIL_PKG');

PL/SQL procedure successfully completed.

SQL> sho error

No errors.

SQL>

utlrp.sql tool

Goto $ORACLE_HOME/rdbms/admin and run utlrp.sql

SQL> @utlrp.sql

EBS 12.2 best methods to compile entire Schema

SQL> EXEC UTL_RECOMP.recomp_serial('APPS');

PL/SQL procedure successfully completed.

SQL>

Compile Invalids with parallel thread

SQL> EXEC UTL_RECOMP.recomp_parallel(10);

Verify the Invalids

    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Compile Invalid Packages in Oracle EBS Database

EBS Functionality breaks if dependent packages become invalid. While troubleshooting any E-Business Suite Functional issue, always check if any packages are changed, modified, or become invalids.

Query to check invalids:

Few queries you must use to check the status of the invalids.

Total Number of Invalids:

select count(*) from dba_objects where status=’INVALID’;

Number of Invalids with schema details

    col owner for a30

    select owner, object_type, count(*) from dba_objects where status='INVALID'

    group by owner, object_type;

Objects which are invalids in the database along with the OWNER name

    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

How to Compile Invalids

Manual approach – Compile each package or package body manually.

alter package <package_name> compile body;

alter package <package_name> compile;

Similarly, we can compile other object_type like VIEW, FUNCTION, SYNONYM, TRIGGER

and MATERIALIZED VIEW

Use the same above query to compile.

alter procedure <procedure_name> compile;

alter synonym <synonym_name> compile;

alter trigger <trigger_name> compile;

DBMS_DDL Package method:

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');

EBS 12.2 Method

SQL> exec AD_ZD.compile ('XX_DETAIL_PKG');

PL/SQL procedure successfully completed.

SQL> sho error

No errors.

SQL>

utlrp.sql tool

Goto $ORACLE_HOME/rdbms/admin and run utlrp.sql

SQL> @utlrp.sql

EBS 12.2 best methods to compile entire Schema

SQL> EXEC UTL_RECOMP.recomp_serial('APPS');

PL/SQL procedure successfully completed.

SQL>

Compile Invalids with parallel thread

SQL> EXEC UTL_RECOMP.recomp_parallel(10);

Verify the Invalids

    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Author: oracleagent

Adding Host Target

Using emctl command

Known Issues and Suggestions

Query to check invalids:

How to Compile Invalids

Query to check invalids:

How to Compile Invalids