1805310 - PCM: Oracle related errors

Symptom

TNS-12505 TNS:listener could not resolve SID given in connect descriptor
TNS-12545: Connect failed because target host or object does not exist
TNS-12564: TNS:connection refused
ORA-03114 : Not connected to Oracle
ORA-12157: TNS Internal network communication error
ORA-12154 : TNS : could not resolve the connect identifier specified when trying to connect with the “usrepo” to PCM instance
ORA-12154: TNS: Could not resolve service name
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12547 : TNS: Lost Contact When clicking "Test Connection" to the primary server via PCM Configuration wizard
 Database\Failed\GetSignOnType Database error (class ALGSession - Connect) ORA-12547: TNS:lost contact
 Oracle instance is terminated

Environment

SAP Profitability and Cost Management connected to an Oracle database

Reproducing the Issue

Most Oracle DBA's have seen TNS error messages these at sometime in their careers.  Troubleshooting and resolving such errors is the responsibility of the Oracle DataBase Administrator. This article is for suggestions and guidelines only.
The best source of Oracle resolutions is available through the Oracle website.

Cause

The "ORA-03114: Not Connected to Oracle" error is commonly found when there is a problem with the network connectivity.
Cause: A call to Oracle was attempted when no connection was established. Usually this happens because a user-written program has not logged on. It may also happen if communication trouble causes a disconnection.
Common causes for the ORA-03114 error include:

• Webserver or listener process is not running
• Missing entry to the tnsnames.ora
• Network issues
• Problems with connection pooling (shared servers, MTS)

“ORA-12547 : TNS: Lost Contact”: Please apply the resolution steps described on the attached note “1247442 - PCM: Oracle TNS Listener error "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" when attempting to open a PCM model” and see if the problem will be fixed.
"ORA – 12154 : TNS : could not resolve the connect identifier specified." For further information about this error, follow this link to the Oracle website
“ORA-03114 : not connected to Oracle” : We recommend you to refer to the attached SAP note “1183091 - PCM : Error Code 80880021 "General Database error ORA-3114." to resolve this error.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
Action:

1. - Wait a moment and try to connect a second time. (this is done by the PCM application anyway so no need to do this manually)
2. - Check which services are currently known by the listener by executing: lsnrctl services <listener name>
3. - Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.
4. - If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.
5. - Check for an event in the listener.log file.

TNS-12564: TNS:connection refused
The main reason you get "connection refused" is because you are connecting to a host that doesn't have a service running on a specific port, or there is firewall software preventing a connection being made, usually the first reason though. Make sure you are connection to the correct host and port number.
TNS-12505 TNS:listener could not resolve SID given in connect descriptor
Cause: The SID in CONNECT_DATA was not found in the listener's tables.
Action: Check to make sure that the SID specified is correct. The SIDs that are currently registered with the listener can be obtained by typing "LSNRCTL SERVICES listener_name". These SIDs correspond to SID_NAMEs in TNSNAMES.ORA, or DB_NAMES in the INIT.ORA file.

Resolution

Steps to troubleshoot Oracle connectivity:

1. Check with "ping" using the IP address (e.g. "ping 198.11.34.43")
2. Check with "ping" using DNS name (e.g. "ping <DNS name>")
3. Try telnet to the IP on port 1521 (telnet 1.2.3.4 1521)
4. Check with "tnsping" using TNS service name (e.g. "tnsping PCMDB")
5. Invoke SQL*Plus from the OS command line "sqlplus <x>@<y>". If this fails, check to ensure that your listener has the <y> service defined.
6. Sign-on to SQL*Plus and connect with the TNS name (e.g. "connect user/password@service_name")

 FURTHER INFORMATION
1. TNSPING is a utility in the ORACLE HOME/bin directory used to test if a SQL*Net connect string can connect to a remote listener (check if the socket is reachable).

Note: This utility only tests if the listener is available. It cannot tell if the databases behind the listener is up or not.
You can run this test from your command line: tnsping
2. Check to make sure that the SID is correct. The SIDs that are currently registered with the listener can be obtained by typing: LSNRCTL SERVICES on the Server
These SIDs correspond to SID_NAMEs in TNSNAMES.ORA or DB_NAME in the initialization file. If there are any variations from the expected SIDs, then you can correct those in the relevant file.
DB_NAME Initialization Parameter (taken from http://docs.oracle.com/cd/B28359_01/server.111/b28310/create005.htm#i1013946)

DB_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for DB_NAME is recorded in the datafiles, redo log files, and control file of the database. If during database instance startup the value of the DB_NAME parameter (in the parameter file) and the database name in the control file are not the same, the database does not start.

3. Check your tns_admin entry is correctly set or reset the tns_admin variable and try again.
4. DB installation and un-installation will cause the port number to vary. Therefore it may not be the 1521 always as default, so check the listener.ora @ ora_home to check which is the latest port number it is using. Give the correct details in the PCM Configuration tool.

See Also

Oracle TNS Ping https://community.oracle.com/thread/1066143?tstart=0
“1546209 - PCM : Oracle 11g R2 - #4 Listener Configuration” and the configuration of the connection between PCM and Oracle DB via the Configuration Wizard by referring to the attached SAP note “1546210 - PCM: Oracle 11g R2 - #6 Configuration Wizard”.
1805310 - PCM: Oracle related errors
1753611 - PCM 10 Upgrade Configuration Errors - Oracle database
1626443 - PCM10:Fail to connect to Oracle database when configuring PCM v10 64bit
1853742 - Oracle Support for PCM v10 requires Oracle Database 11g Enterprise Edition Release
1671525 - PCM: What versions of Oracle are supported by PCM 10?
1524945 - PCM: When will PCM support Oracle v11.2?
1546214, "PCM: Oracle 11g R2 - #1 Installation"
1546171, "PCM: Oracle 11g R2 - #2 Create PCM Database"
1546215, "PCM: Oracle 11g R2 - #3 Create PCM TableSpace"
1546209, "PCM: Oracle 11g R2 - #4 Listener Configuration"
1546218, "PCM: Oracle 11g R2 - #5 Create PCM Schema"
1546210, "PCM: Oracle 11g R2 - #6 Configuration Wizard"
See also the PCM 10 Installation Guide available through the SAP Help Portal

Keywords

oracle db, database, jmhiv2016e,  data base, PCM, EPO

Header Data

Released On	08.01.2016 18:18:40
Release Status	Released to Customer
Component	EPM-PCM Profitability and Cost Management, EPO, PP, AA, Metify
Priority	Normal
Category	Problem
Database	ORACLE 11.2.0.2