ORA-12154: TNS:could not resolve the connect identifier specified

Error " ORA-12154: TNS:could not resolve the connect identifier specified " or " ORA-12505: TNS:listener does not currently know of SID given in connect descriptor " is a very generic message. It does not tell the true problem.

For example, I had a wrong port number 1521 in tnsnames.ora file. tnsping reads the string from tnsnames.ora without a complaint:

$  tnsping ebsdev
......
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=db_server1d.domain.com)(PORT=1521)) (CONNECT_DATA= (SID=EBSDEV)))
OK (20 msec)

But SQL*Plus does not connect to the database:

$ sqlplus apps/appsPWD@ebsdev
......
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

The real problem is that port 1521 is the wrong port entered in tnsnames.ora file.

In Oracle EBS environment, SQL*Plus uses env variable $TWO_TASK as default to make database connection, and database connection string is not necessary.

$  echo $TWO_TASK
EBSDEV

Below two lines work fine:
$ sqlplus apps/appsPWD
SQL>

$ sqlplus apps/appsPWD@ebsdev
SQL>

If that variable is set to something else, SQL*Plus will give ORA-12154 error:

$ export TWO_TASK=dbdb
$ sqlplus apps/appsPWD
......
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Query to find long-run concurrent jobs

Use below SQL statement to find who is running concurrent request and how long it has been running.

SQL> SELECT fcr.request_id req_id,

to_char(fcr.actual_start_date,'mm/dd hh24:mi') start_time,

TRUNC(((sysdate - fcr.actual_start_date)/(1/24))*60) elap_in_Mins,

substr(fcq.concurrent_queue_name, 1, 20) queue,

substr(fcp.user_concurrent_program_name, 1, 45)  name,

substr(fu.user_name, 1, 9 ) u_name,

-- round((sysdate - actual_start_date) *24, 2) elap,

-- TRUNC(((sysdate - fcr.actual_start_date)/(1/24))*60) elap,

substr(decode( fcr.status_code, 'A', 'WAITING', 'B', 'RESUMING',

'C', 'NORMAL', 'D', 'CANCELLED', 'E', 'ERROR', 'F', 'SCHEDULED',

'G', 'WARNING', 'H', 'ON HOLD', 'I', 'NORMAL', 'M', 'NO MANAGER',

'Q', 'STANDBY', 'R', 'NORMAL', 'S', 'SUSPENDED', 'T', 'TERMINATED',

'U', 'DISABLED', 'W', 'PAUSED', 'X', 'TERMINATED', 'Z', 'WAITING',

'UNKNOWN'), 1, 10) status

FROM  applsys.fnd_concurrent_queues fcq,

applsys.fnd_concurrent_processes fcp, applsys.fnd_user fu,

apps.fnd_concurrent_programs_vl fcp,

applsys.fnd_concurrent_requests fcr

WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id

and fcp.queue_application_id = fcq.application_id

and fcr.controlling_manager = fcp.concurrent_process_id

and fcr.requested_by = fu.user_id

and fcr.concurrent_program_id = fcp.concurrent_program_id

and fcr.program_application_id = fcp.application_id

-- and round((sysdate -fcr.actual_start_date) *24, 2) >= 1

-- TRUNC(((sysdate - fcr.actual_start_date)/(1/24))*60) > 5

and fcr.phase_code = 'R'

ORDER BY round((sysdate -actual_start_date) *24, 2) DESC, fu.user_name,

fcr.request_id, to_char(fcr.actual_start_date,'dd-mon-yy hh24:mi:ss'),

fcp.user_concurrent_program_name

; 

References: 

Doc ID 152209.1 (STATUS_CODE and PHASE_CODE Columns of FND_CONCURRENT_REQUESTS Table)

Useful queries:

-  Find the OS process IDs of a concurrent job

SQL> SELECT b.sid, b.serial#, client_identifier, oracle_process_id DB_OS_PID, os_process_id EBS_PID

FROM fnd_concurrent_requests a , v$session b

WHERE a.ORACLE_SESSION_ID = b.AUDSID and

a.request_id=5510725;

- Check the performance trend on one concurrent program/job

SQL> SELECT

fcr.request_id request_id,

round(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) time_in_Minutes,

fcr.actual_start_date start_date,

status_code,

fu.user_name user_name,

fcpt.user_concurrent_program_name user_conc_prog,

fcr.argument_text parameters

FROM

fnd_concurrent_programs fcp,

fnd_concurrent_programs_tl fcpt,

fnd_concurrent_requests fcr,

fnd_user fu

WHERE fcr.concurrent_program_id = fcp.concurrent_program_id

and fcr.program_application_id = fcp.application_id

and fcr.concurrent_program_id = fcpt.concurrent_program_id

and fcr.program_application_id = fcpt.application_id

and fcpt.language = USERENV('Lang')

and fcr.requested_by = fu.user_id and fcr.actual_start_date > sysdate - 30 -- within 30 days

and fcp.concurrent_program_name = 'FNDGSCST'        -- Program name: on statistics

-- and fcr.argument_text like 'ALL%'

-- and status_code != 'E'

ORDER BY start_date desc;

- Check if parameter has been changed in defining a concurrent job

SELECT

fcr.request_id request_id,fcr.argument_text,

TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) time_in_mins,

fcr.actual_start_date start_date,

fu.user_name user_name,

fcpt.user_concurrent_program_name user_conc_prog

FROM

fnd_concurrent_programs fcp,

fnd_concurrent_programs_tl fcpt,

fnd_concurrent_requests fcr,

fnd_user fu

WHERE fcr.concurrent_program_id = fcp.concurrent_program_id

and fcr.program_application_id = fcp.application_id

and fcr.concurrent_program_id = fcpt.concurrent_program_id

and fcr.program_application_id = fcpt.application_id

and fcpt.language = USERENV('Lang')

and fcr.requested_by = fu.user_id and fcr.actual_start_date > sysdate - 21  -- within 21 days

and fcp.concurrent_program_name = 'CSTRBICR5G'            -- Program name: on BOM

ORDER BY start_date desc;

adoacorectl.sh exiting with status 204

After server rebooted unexpectedly, adoacorectl.sh failed to start OACORE OC4J due to opmn issue and made R12.1.3 initial page unable to re-direct to login page. The error is

adoacorectl.sh: exiting with status 204

File $INST_TOP/logs/appl/admin/log/adoacorectl.txt shows more detail:
10/09/18-11:37:25 :: adoacorectl.sh version 120.13
10/09/18-11:37:25 :: adoacorectl.sh: starting OPMN if it is not running
opmnctl: opmn is already running.
10/09/18-11:37:25 :: adoacorectl.sh: Starting OPMN managed OACORE OC4J instance
opmnctl: starting opmn managed processes...
============================================
opmn id=hostname.domian.com:6240
    0 of 1 processes started.
ias-instance id=EBSDEV_hostname.hostname.domian.com
++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
    default_group/oacore/default_group/
Error
--> Process (index=1,uid=1230729787,pid=17205)
    failed to start a managed process after the maximum retry limit
    Log:
   $INST_TOP/logs/ora/10.1.3/opmn/default_group~oacore~default_group~1.log
10/09/18-11:37:37 :: adoacorectl.sh: exiting with status 204

File $INST_TOP/logs/ora/10.1.3/opmn/default_group~oacore~default_group~1.log has below warning which was not new and existed before:
--------
18/10/09 11:37:25 Start process
--------
18/10/09 11:37:26 WARNING: ApplicationLogManager is not installed, may result in loader leaks. Set -Djava.util.logging.manager=oracle.classloader.util.ApplicationLogManager

I did not make any changes in EBS in past months. Seems the problem was with opmn, but I do not know what it is. So I just ran autocofig. After that, "adoacorectl.sh start" worked surprisingly and login page showed up. The warning message with ApplicationLogManager still stays in the opmn log file.

The problem could be some session lock files got inconsistent by the server crash.

BTW, to turn trace (increase logging) on OC4J oacore by editing two files:

Edit j2ee-logging.xml adjust the following in file:
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config/j2ee-logging.xml
<logger name=’oracle’ level='TRACE:32′ useParentHandlers=’false’>
Edit orion-web.xml adjust the following in file:
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml
<param-name>debug_mode</param-name>
<param-value>true</param-value>
But it is difficult to read and understand the log log.xml under $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1

Find the NLS settings on a connection

When making a connection to the database, below query will tell its process IDs on both client and db server:

SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid
from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv('sessionid');

       SID    SERIAL#  PROCESSID   CLIENTPID
------------ ------------  ----------------    -----------------
       180      42146      48824530         26093
26093 is the process ID for this sqlplus session on client/Apps side.
48824530 is the process ID on database server for this session.

In general,
V$SESSION.SID and V$SESSION.SERIAL#  – process ID in database
V$PROCESS.SPID – Shadow process ID on the database server
V$SESSION.PROCESS – Client process ID

If start a new session on the same client where sqlplus is still running, you can verify it is running:
$ ps -ef | grep 26093
ebsdev  26093 25836  0 15:09 pts/1    00:00:00 sqlplus

To find what NLS settings are used to support the connection, run below lines:
$ ps ewww 26093 | tr ' ' '\n' | grep NLS
-DNLS_ASIA
FORMS_OVERRIDE_ENV=NLS_LANG,NLS_NUMERIC_CHARACTERS,NLS_SORT,NLS_DATE_LANGUAGE,NLS_DATE_FORMAT,FORMS_USER_DATE_FORMAT,FORMS_USER_DATETIME_FORMAT,FORMS_OUTPUT_DATE_FORMAT,FORMS_OUTPUT_DATETIME_FORMAT,FORMS_ERROR_DATE_FORMAT,FORMS_ERROR_DATETIME_FORMAT,FORMS_TZFILE,FORMS_DATETIME_SERVER_TZ,FORMS_DATETIME_LOCAL_TZ,FORMS_USER_CALENDAR
NLS_DATE_FORMAT=DD-MON-RR
NLS_DATE_LANGUAGE=
NLS_LANG=American_America.WE8ISO8859P1
NLS_NUMERIC_CHARACTERS=.,
NLS_SORT=BINARY
ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Do the same for other sessions. For example, if you are interested on the listener session, find its process ID first and then see its NLS settings by "ps ewww".
$ ps -ef|grep tnslsnr
ebsdev  23845     1  0 Aug01 ?        00:00:00 $ORACLE_HOME/bin/tnslsnr APPS_EBSDEV -inherit

Similarly, you can do the same with 48824530 on database server to find its NLS settings.

$ ps ewww 48824530 | tr ' ' '\n' | grep NLS
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Below query can help to find parameters for database characterset. Ideally all of three sides shall be consistent.

SQL> SELECT db.parameter as parameter, db.value as database_value,
s.value as session_value,  i.value as instance_value
FROM
nls_database_parameters db
LEFT JOIN
nls_session_parameters s
ON s.parameter = db.parameter
LEFT JOIN
nls_instance_parameters i
ON i.parameter = db.parameter
ORDER BY parameter;

PARAMETER DATABASE_VALUE SESSION_VALUE INSTANCE_VALUE
------------------ ------------------------- ------------------------- -------------------
NLS_CALENDAR GREGORIAN GREGORIAN
NLS_CHARACTERSET WE8ISO8859P1
NLS_COMP BINARY BINARY binary
NLS_CURRENCY $ $
NLS_DATE_FORMAT DD-MON-RR DD-MON-RR DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN AMERICAN
NLS_DUAL_CURRENCY $ $
NLS_ISO_CURRENCY AMERICA AMERICA
NLS_LANGUAGE AMERICAN AMERICAN AMERICAN
NLS_LENGTH_SEMANTICS BYTE BYTE BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE FALSE FALSE
NLS_NUMERIC_CHARACTERS ., ., .,
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_SORT BINARY BINARY binary
NLS_TERRITORY AMERICA AMERICA america
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM DD-MON-RR HH.MI.SSXFF AM TZR TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR HH.MI.SSXFF AM TZR

When NLS_LANG is unset, it defaults to US7ASCII.

Without proper setting on ORA_NLS10, package UTL_FILE may not work correctly. See https://erpondb.blogspot.com/2016/10/troubleshhot-utlfile-error.html

"deleted" files still take disk space

We received alerts on disk space used by EBS is 97% full. When I use "du" to check the space, it shows only 60% of the space is used. But "df -h" shows it is 97% full. The problem is that some files deleted by EBS process will not really release the space back yet until the process is stopped. See https://access.redhat.com/solutions/2316

Below line will find if there are "deleted" files that still hold space, assuming the partition is /u02/app:

$ lsof | grep /u02/ | grep app | grep delete | more
rwrun     18759   applMgr   13u      REG              253,3 3140558848    3082366 $INST_TOP/temp/dat000418759 (deleted)
rwrun     18759   applMgr   15u      REG              253,3  745730048    3082368 $INST_TOP/temp/idx000518759 (deleted)
rwrun     18759   applMgr   17u      REG              253,3          0    3082375 $INST_TOP/temp/000718759 (deleted)

3140558848 is the size by the file. You may see the size is increasing if process 18759 keeps running. But you will not see the file by "ls" command.

$ ps -ef | grep 18759
$ORACLE_HOME/bin/rwrun mode=character ARRAYSIZE=5 P_CONC_REQUEST_ID=21334937 P_MAIL_INDICATOR='ALL' report=$APPL_TOP/aear/reports/US/XXXXRREG.rdf userid=APPS batch=yes destype=file desname=$APPLCSF/out/o21334937.out desformat=$FND_TOP/reports/PD pagesize=2050x66

We found concurrent report "XXXX Receipt Register" in request ID 21334937 ran for 15 hours. After it is cancelled, the "hidden" space is released back.

The same problem happens to /tmp file partition, when parameter forms_tmpdir points to /tmp.

$ grep forms_tmpdir $CONTEXT_FILE

         <forms_tmpdir oa_var="s_forms_tmpdir" osd="UNIX">/tmp</forms_tmpdir>

$ lsof | grep /tmp | grep delete   <== it takes longer

$ lsof /tmp | grep delete             <== it runs quick

frmweb      398              applMgr    86u      REG              253,5    5303826        365 /tmp/filer6nHW3.TMP (deleted)

frmweb      570              s044150   86u      REG              253,5   41307666        144 /tmp/file4FWO2C.TMP (deleted)

$ ps -ef | grep 570

applMgr     570 31251  0 Mar15 ?        00:07:39 frmweb server webfile=HTTP-0,0,1,default

It is better to change forms_tmpdir to $APPLTMP, $INST_TOP/temp or $NE_BASE/EBSapps/log. /tmp is for server logs and if it is full, the server may crash and go down.

NOTES: EBS Profile option "Attachment File Directory" may also point to/tmp (and make /tmp space filled up).