Find Logging level for EBS R12.2 Workflow services

col component_name format A50

col parameter_name format a45 head "Parameter Name"

col parameter_value format a30 head "Value"

SELECT b.component_name,

  c.parameter_name,

  a.parameter_value

FROM fnd_svc_comp_param_vals a,

  fnd_svc_components b,

  fnd_svc_comp_params_vl c

WHERE b.component_id = a.component_id

AND b.component_type = c.component_type

AND c.parameter_id = a.parameter_id

AND c.parameter_name like '%LOG_LEVEL%';

Find SQL Server version

Look at the first few lines of the Errorlog.n file for that instance. By default, the error log is located at 

Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG

EBS R12.2 Clone Log files Location

 

The below-mentioned locations are more important to analyze the EBS cloning issues.

/u01/PROD/fs1/inst/apps/PROD_oracleap/admin/log/clone/

/u01/PROD/fs1/EBSapps/comn/clone/bin/

Archive Log Deletion Policy in Oracle Database RMAN Configuration

RMAN> Show all;   --> use this command to view the RMAN configuration settings

1. Eligible to delete archivelog files applied on all standby databases (required for DR setup) and those files should be backed up 1 time on the database (primary/production).

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

2. No Deletion policy. Eligible to delete all available archivelog files from the database.

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

3. Eligible to delete all archivelog files applied on all standby databases (DR environment).

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

4. Eligible to delete archivelog files which are already backed up 1 time by the database.

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

How to find the archive log sequence from RMAN Backups

RAC Environment:

RMAN> list backup of archivelog sequence between 127 and 155 thread 1;

RMAN> list backup of archivelog sequence between 112 and 160 thread 2;

Java File Deployment Steps in EBS R12.2

 After Java File deployment into an EBS Application. Please follow the below steps to take changes effect on the EBS application.

cd $AD_TOP/bin

adcgnjar

cd $ADMIN_SCRIPTS_HOME

admanagedsrvctl.sh stop oacore_server1

adapcctl.sh stop

adapcctl.sh start

admanagedsrvctl.sh start oacore_server1

ETCC Failed in 19c Database - LsPatchesSession failed

First time running ETCC on newly cloned environment. While running ETCC script in database tier, it shows below error message in 19c Database

Error:

Identified database DST version: 32

Inventory load failed... LsPatchesSession::loadAndPrintInstalledPatch()

LsPatchesSession failed: RawInventory gets null OracleHomeInfo

Inventory load failed... LsPatchesSession::loadAndPrintInstalledPatch()

LsPatchesSession failed: RawInventory gets null OracleHomeInfo

Inventory load failed... LsPatchesSession::loadAndPrintInstalledPatch()

LsPatchesSession failed: RawInventory gets null OracleHomeInfo

Cause:

Database home entry is not registered in an central inventory. Checked the inventory.xml file from oraInventory location and it shows only application home entries.

Solution:

Use below command to register database home,

cd $ORACLE_HOME/oui/bin

./runInstaller -attachhome ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1 ORACLE_HOME_NAME=OraDB19Home

Unable to invoke method HTTPClient.HTTPResponse.listHeaders caused by: java.net.SocketTimeoutException: Read timed out

R12.2 EBS Workflow mails are not sending to any users email address and all the workflow services are running fine.

Cause:

UNEXPECTED:[SVC-GSM-WFMLRSVC-1832203-10006 : oracle.apps.fnd.wf.mailer.NotificationFormatter.handleResEndTag]:Problem obtaining the HTML content oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPResponse.listHeaders 

caused by: java.net.SocketTimeoutException: Read timed out

Workaround:

- tried to restart the workflow mailer service from Workflow administrators as sysadmin user.

- Tested the inbound and SMTP test connection through EBS application and there is no issue.

- Disabled Inbound settings and tried to save the settings only with SMTP but no luck.

Solution:

The Mailer parameter "Framework URL timeout" [FRAMEWORK_URL_TIMEOUT] should be

increased from default of 30 sec. to 120 sec. using $FND_TOP/sql/afsvcpup.sql.

After the change, Restart the Notification Mailer Service:

OAM-> Dashboard->Workflow Manager->Notification Mailers -> Select the Mailer to restart.

Reference: Oracle Doc: ID 602971.1

adopmnctl.sh: exiting with status 152

While starting the Apache/OHS service using adstrtal.sh in R12.2, it gives below error message in Solaris Environment.

$ ./adstrtal.sh

Executing service control script:

/d02/EBSTEST/fs1/inst/apps/TEST_test/admin/scripts/adapcctl.sh start

Timeout specified in context file: 100 second(s)

script returned:

****************************************************

ERROR : Timed out( 100000 ): Interrupted Exception

You are running adapcctl.sh version 120.0.12020000.6

Starting OPMN managed Oracle HTTP Server (OHS) instance ...

**************************************************** 

$ ./adopmnctl.sh status

You are running adopmnctl.sh version 120.0.12020000.2

Checking status of OPMN managed processes...

Optic: request(/dump?type=dms&dmsarg=[nountype=opmn_ias_instance&format=pdml]): Error connecting to OPMN at localhost:6115 -- Connection refused (Connection refused)  request(/dump?type=dms&dmsarg=[nountype=opmn_ias_instance&format=pdml]) at InetSocketAddress: localhost/10.x.x.x:6115

  dialOpmn: no SSL localhost:6115 timeout=60000ms

  dialOpmn: connect failed (Connection refused (Connection refused)): localhost:6115

$ adopmnctl.sh startall

You are running adopmnctl.sh version 120.0.12020000.2

Starting Apache...

EXIT CODE is 152. Please check the log file for more details.

adopmnctl.sh: exiting with status 152

Workaround:

- Restarted the entire EBS R12.2 application services.

- Restarted the database as well.

- Restarted the Entire server.

- export TIMEDPROCESS_TIMEOUT=-1 and restarted the application services.

Cause:

root@test:~# telnet localhost 6115

Trying 10.x.x.x...

telnet: Unable to connect to remote host: Connection refused

Solution:

Checked the below values from the file /etc/nsswitch.conf and it should be hosts: files dns

hosts:  file dns

We found localhost entry was recently added in DNS server and its reflected into server level and its affected apache services which is using localhost internally to start up services.

so we have to update the nsswitch.conf file using below mentioned steps

connect as root user

# svccfg -s name-service/switch

svc:/system/name-service/switch> listprop config

config                     application

config/default             astring     files

config/host                astring     "file dns"

config/value_authorization astring     solaris.smf.value.name-service.switch

svc:/system/name-service/switch> setprop config/host = "files dns"

svc:/system/name-service/switch> listprop config

config                     application

config/default             astring     files

config/host                astring     "files dns"

config/value_authorization astring     solaris.smf.value.name-service.switch

svc:/system/name-service/switch>

# svcadm refresh name-service/switch

# svcadm restart name-service/switch

Then telnet connection worked properly and Apache services started successfully.

Query to find Big Table or High consumption Table in oracle database

 SELECT * FROM

(select

SEGMENT_NAME,

SEGMENT_TYPE,

BYTES/1024/1024/1024 GB,

TABLESPACE_NAME

from

dba_segments

order by 3 desc ) WHERE

ROWNUM <= 10;

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

Issue: DR site is down permanently and disabled the log_archive_dest_state_2 on both nodes, but still archive logs are not deleting from the primary.

cause: 

SELECT * from v$archive_dest where (valid_now = 'UNKNOWN' AND status = 'DEFERRED') ;

If the above query returns a row (rows), then you have some destinations that are marked DEFERRED

If  you have one of V$ARCHIVE_DEST with valid_now=’UNKNOWN’ and status = ‘DEFERRED’.

Then because of Bug 16082541, we are considering DEFERRED destination applied archived logs too and hence RMAN not purging those. 

Solution:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = '' SCOPE = BOTH sid='*';

After changing above parameter archive log files deleted from primary.

How to Kill RMAN Process/session in oracle Database

 1. Os Level:

ps -ef|grep rman

kill -9 <process_id>

--Once killed the os session, rman sessions still running and taking backup in database level.

--Use the below query to find the % completion

2. Database Level:

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

--Kill all the rman sessions in database.

alter system kill session '2515,27025' immediate;

Note: After killing database sessions using alter query, it will take sometime to clear the existing sessions. 

But % wont increase after killing database sessions.