Check EBS Installed Modules and versions

Check EBS Installed Modules and versions 

How to check if certain Oracle Applications modules/products are installed, and their versions?

 Application tier, 

Method 1

cd $AD_TOP/sql/

 Execute:

sqlplus apps/<password> @adutconf.sql

File adutconf.ls will be created in directory $AD_TOP/sql

Check product name by searching for “Product” in list file

 Reference document : - 443699.1

Method 2

Use Oracle Applications Manager (OAM).

Connect to http://<hostname>.<domain>:<PORT>/servlets/weboam/oam/oamLogin

a. Click 'Site Map'.
b. Under 'System Configuration' choose 'License Manager'.
c. Go to 'Products' link.
d. Click ' Licensed Products' under 'Reports' section.

You can use filter for certain product.

Filter: Product Abbreviation is AR

The value in status column shows product installation status.


Method 3

Use sql query.

select fa.APPLICATION_SHORT_NAME, fpi.PATCH_LEVEL, DECODE(fpi.STATUS, 'I','Installed',
'S','Shared', 'N', 'Inactive', fpi.STATUS) Status , fpi.DB_STATUS
from fnd_product_installations fpi,FND_APPLICATION fa
where fpi.APPLICATION_ID in (
select APPLICATION_ID from FND_APPLICATION where APPLICATION_SHORT_NAME like '<PRODUCT_SHORT_NAME>')
and fa.APPLICATION_ID=fpi.APPLICATION_ID;

where  <PRODUCT_SHORT_NAME> is your product short name

Example 1

How to check if Knowledge Management (CSK), iSupport (IBU), Business Intelligence(BIS) are
implemented?

sqlplus <apps_user>/<apps_password>

sql> select fa.APPLICATION_SHORT_NAME, fpi.PATCH_LEVEL, DECODE(fpi.STATUS, 'I','Installed',
'S','Shared', 'N', 'Inactive', fpi.STATUS) Status , fpi.DB_STATUS
from fnd_product_installations fpi,FND_APPLICATION fa
where fpi.APPLICATION_ID in (
select APPLICATION_ID from FND_APPLICATION where APPLICATION_SHORT_NAME in ('CSK','IBU','BIS'))
and fa.APPLICATION_ID=fpi.APPLICATION_ID;

if any of the products is implemented status should be Installed.

Method 4

Use script patchsets.sh which is documented in NOTE:139684.1 Oracle Applications Current Patchset Comparison Utility - patchsets.sh.

If you have any errors when running script patchsets.sh please refer to the following note to resolve the issue:
NOTE:314442.1 Cannot run Patchsets.Sh Script 

OEM 13c Port Details

OEM 13c Port Details

Enterprise Manager Central Console Http SSL Port=7802
Enterprise Manager Upload Http Port=4889
Enterprise Manager Upload Http SSL Port=4903
OHS Http Port=9788
BI Publisher Http Port=9701
Node Manager Http SSL Port=7403
BI Publisher Http SSL Port=9803
OHS Http SSL Port=9851
Managed Server Http Port=7202
Oracle Management Agent Port=3872
Enterprise Manager Central Console Http Port=7788
Admin Server Http SSL Port=7102
Managed Server Http SSL Port=7301

Diagram showing ports details for OEM 13C

OEM 13C PORTS

ports are divided into 4 Categories
-----------------------------------------

1. Agent and OMS: This is the communication between the targets’ agents to OMS.
2. Browser and OMS: This is browser traffic to OMS.
3. Internal within OMS: This is communication internal to OMS only.
4. OMS and OMR: This is repository traffic from the OMS to the OMR.
5. Workstations and OMR: You still need to access the OMR database from your workstations.

Recommended Port Range	Chosen Port	Purpose	Initiator	Used?	Comments
Agent and OMS
	22	OMS to ssh to target hosts.	OMS	YES	Can be revoked after agent deployment.
3872, 1830-1849	3872	Oracle Management Agent port	OMS	YES
4889-4898	4889	Enterprise Manager Upload Http port	Agent	NO	Locked by default
1159, 4899-4908	4903	Enterprise Manager Upload Http SSL port	Agent	YES
Browser and OMS
	80	http	Browser	YES
	443	https	Browser	YES
7101-7200	7102	Admin Server Http SSL port	Browser	YES
7788-7798	7788	Enterprise Manager Central Console Http port	Browser	NO	Locked by default
7799-7809	7802	Enterprise Manager Central Console Http SSL port	Browser	YES
9788, 9751-9800	9788	OHS Http port	Browser	NO
9899, 9851-9900	9851	OHS Http SSL port	Browser	YES
9701, 9701-9750	9701	BI Publisher Http port	Browser	NO	Locked by default
9801, 9801-9850	9803	BI Publisher Http SSL port	Browser	YES
Internal within OMS
7201-7300	7202	Managed Server Http port	???	NO	Rediections between OHS and Weblogic only.
7301-7400	7301	Managed Server Http SSL port	???	YES	Rediections between OHS and Weblogic only.
7401-7500	7403	Node Manager Http SSL port	???	YES
OMS and OMR
	1521	Oracle database port	OMS	YES	This port needs to be opened for the hostnames, VIPs, and the SCAN name/IPs.
Workstations and OMR
	1521	Oracle database port	Workstations	YES	This port needs to be opened for the hostnames, VIPs, and the SCAN name/IPs.

 Reference - OEM13C_EXPLANATION

External Ports

These ports will be used in every Enterprise Manager 12c installation and will require firewall and/or ACL modifications if your network is restricted.  These are also the components that will be added to your load balancer configuration.

Default Port	Range	Component	Usage	Modify
4889	4889 – 4898	Enterprise Manager OHS Upload HTTP	Agent Communication to OMS (unsecure). Used in load balancer.	To modify after install follow notes 1381030.1 and 1385776.1. Requires changes on all Agents.
1159	1159, 4899 – 4908	Enterprise Manager OHS Upload HTTP SSL	Agent Communication to OMS (secure). Used in load balancer.	To modify after install follow notes 1381030.1 and 1385776.1. Requires changes on all Agents.
7788	7788 – 7798	Enterprise Manager OHS Central Console HTTP (Apache/UI)	Web browser connecting to Cloud Control Console (unsecure). Used in load balancer and for EM CLI.	To modify after install follow notes 1381030.1.
7799	7799 - 7809	Enterprise Manager OHS Central Console HTTP SSL (Apache/UI)	Web browser connecting to Cloud Control Console (secure). Used in load balancer and for EM CLI.	To modify after install follow note 1381030.1.
7101	7101 - 7200	EM Domain WebLogic Admin Server HTTP SSL Port	Cloud Control Admin Server.	To modify after install follow note 1109638.1.
3872	3872, 1830 – 1849	Cloud Control Agent	Only the OMS will connect to this port, to either report changes in the monitoring, submit jobs, or to request real-time statistics.	Port can be provided during Agent install. If the agent port needs to be changed at a later date this can be done with the following command on the agent: emctl setproperty agent -name EMD_URL -value https://hostname.domain:port/emd/main/ This will allow the agent to run on the new port, however the target does not get renamed so continues to show the original port.
1521*	Depends on Listener Configuration	Database Targets -  SQL*Net Listener	For Repository database, only the OMS will connect to store management data from the agents. For all monitored target databases OMS will retrieve information requested by browser clients.	To modify this port for the repository database: Change the listener.ora file for the EM repository. Restart the listener. Then for every OMS machine using that repository run the following: emctl stop oms emctl config oms -store_repos_details -repos_conndesc -repos_user sysman emctl start oms emctl config emrep -agent -conn_desc    To modify this port for monitored targets, change the listener configuration on the target, then update Monitoring Configuration in EM.
7101	7101 - 7200	FMW Targets – Admin Console	Outgoing from OMS, used for managing FMW targets.	To modify after install follow note 1109638.1.
NA	NA	ICMP	Outgoing from OMS to host servers if the Agent is unreachable. Validates if server is up or down.	NA

Internal Ports

These ports are required for internal Enterprise Manager communication and typically do not require additional firewall/ACL configuration.

Default Port	Range	Component	Usage	Modify
7201	7201 – 7300	EM Domain WebLogic Managed Server HTTP Port	Used for Fusion Middleware communication.	Configured during installation
7301	7301 – 7400	EM Domain WebLogic Managed Server HTTP SSL Port	Used for Fusion Middleware communication.	Configured during installation
7401	7401 – 7500	Node Manager HTTP SSL Port	Used for Fusion Middleware communication.	Configured during installation
6702	6100 - 6199	Oracle Notification Server (OPMN) Local	Ports used by OPMN  can be verified from/gc_inst/WebTierIH1 /config /OPMN/opmn/opmn.xml:	Modify the opmn.xml to use free ports as below: 1. Stop OMS 2. Take a backup of the existing opmn.xml and ports.prop in the / gc_inst/WebTierIH1/ config /OPMN/opmn directory. 3. Edit the opmn.xml file, under the element, modify the local / remote port, as necessary to the free port available and save the file. 4. Edit the ports.prop file and modify the remote / local port parameters as necessary and save the file. 5. Start the OMS
6703	6200 - 6201	Oracle Notification Server (OPMN) Remote	Ports used by OPMN  can be verified from/gc_inst/WebTierIH1 /config/OPMN/opmn/opmn.xml:	Modify the opmn.xml to use free ports as below: 1. Stop OMS 2. Take a backup of the existing opmn.xml and ports.prop in the/gc_inst/WebTierIH1/ config/OPMN/opmn directory. 3. Edit the opmn.xml file, under the element, modify the local / remote port, as necessary to the free port available and save the file. 4. Edit the ports.prop file and modify the remote / local port parameters as necessary and save the file. 5. Start the OMS

Optional

These ports required only if certain components are to be used and firewall/ACL changes may be needed.

Default Port	Range	Component	Usage	Modify
443		Secure web connection (https - 443) to updates.oracle.com support.oracle.com ccr.oracle.com login.oracle.com aru-akam.oracle.com	Outgoing from OMS used for online communication with Oracle for OCM, MOS, Patching, Self-Updates, ASR	Proxy settings defined via the UI (Setup -> Proxy Settings) Do not use the OMS parameters!
51099		Application Dependency and Performance RMI Registry Port	ADP	Configured during installation
55003		Application Dependency and Performance Java Provider Port	ADP	Configured during installation
55000		Application Dependency and Performance Remote Service Controller Port	ADP	Configured during installation
4210		Listen	ADP	Configured during installation
4211		SSL Listen Port	ADP	Configured during installation
3800		JVM Managed Server Listen	JVM	Configured during installation
3801		JVM Managed Server SSL Listen	JVM	Configured during installation
9701	9701-49152	BI Publisher HTTP	BI Publisher	During install can modify with configureBIP script.  Post-install can be modified per Note 1524248.1
9702	9701-49152	BI Publisher HTTP SSL Port	BI Publisher	During install can modify with configureBIP script.  Post-install can be modified per Note 1524248.1

Dataguard Monitoring Scripts

Dataguard Monitoring Scripts

LAG AND STATUS CHECK 

SET LINESIZE 1000

select name,value from v$dataguard_stats;

ARCHIVE SEQUENCE NUMBER COMES FROM THE PRIMARY DATABASE LASTLY AND WHICH IS LAST APPLIED IN STANDBY, YOU CAN LEARN THE FOLLOWING SCRIPT.

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last in Sequence", APPL.SEQUENCE# "Last Applied Sequence", (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;

To stop dataguard you can use following query. When dataguard is stopped, MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

To start dataguard starting MRP process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you don’t want any lag you should start dataguard with using logfile option. You should add standby logs to the standby database in this case.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Check which of database is Standby and which of database is Primary,

SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

Display the status of background processes in a standby database

SELECT PROCESS, CLIENT_PROCESS,THREAD#,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

YOU CAN JUST WANT TO SEE MRP PROCESS STATUS

 

SELECT PROCESS, CLIENT_PROCESS,THREAD#,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE '%MRP%';

WHAT IS THE MRP PROCESS WAITING FOR STATUS ?

SELECT A.EVENT, A.WAIT_TIME, A.SECONDS_IN_WAIT FROM GV$SESSION_WAIT A, GV$SESSION B WHERE A.SID=B.SID AND B.SID=(SELECT SID FROM V$SESSION WHERE PADDR=(SELECT PADDR FROM V$BGPROCESS WHERE NAME='MRP0'));

SWITCHOVER AND THE ROLE OF DATABASE WHICH IS IN STANDBY OR PRIMARY STATE

select switchover_status,database_role from v$database;

TO SEE ARCHIVE GAP IN DATAGUARD

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

INFORMATION ABOUT APPROXIMATE COMPLETION TIME OF THE RECOVERY PROCESS

SELECT TO_CHAR(START_TIME,'DD-MON-RR HH24:MI:SS') START_TIME,ITEM,ROUND(SOFAR/1024,2) "MB/SEC" FROM V$RECOVERY_PROGRESS 

WHERE (ITEM='ACTIVE APPLY RATE' OR ITEM='AVERAGE APPLY RATE');

SEE ALL PARAMETERS OF ORACLE DATAGUARD

SET LINESIZE 500 PAGES 0

COL VALUE FOR A80

COL NAME FOR A50

SELECT NAME, VALUE FROM V$PARAMETER

WHERE NAME IN ('DB_NAME','DB_UNIQUE_NAME','LOG_ARCHIVE_CONFIG',    

'LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_2','LOG_ARCHIVE_DEST_3',

'LOG_ARCHIVE_DEST_STATE_1','LOG_ARCHIVE_DEST_STATE_2','LOG_ARCHIVE_DEST_STATE_3', 'REMOTE_LOGIN_PASSWORDFILE',

'LOG_ARCHIVE_FORMAT','LOG_ARCHIVE_MAX_PROCESSES','FAL_SERVER','FAL_CLIENT','DB_FILE_NAME_CONVERT',

'LOG_FILE_NAME_CONVERT', 'STANDBY_FILE_MANAGEMENT') ORDER BY 1;

SEE APPLIED ARCHIVELOGS IN DATAGUARD

SELECT THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED FROM GV$ARCHIVED_LOG WHERE APPLIED='YES';

PROVIDES INFORMATION ABOUT MAX SEQUENCE OF DATAGUARD

SELECT THREAD#,MAX(SEQUENCE#) FROM GV$ARCHIVED_LOG GROUP BY THREAD#;