Ошибка ora 12547

ORA-12547 is the same as TNS-12547, they all indicate that the ability of communication between the client and the listener is interrupted and lost by something, mostly, it’s because of poor network condition.

SQL*Plus

SQL> conn hr/hr@orcl

Solutions

There’re several possible causes when you connect to the database.

Binaries Permission

It might be caused by wrong permission when executing Oracle binaries.

[oracle@test ~]$ cd $ORACLE_HOME/bin
[oracle@test bin]$ ll oracle
-rwxr-x--x 1 oracle oinstall 443586008 Dec 16  2020 oracle

As you can see, the permission set is 0751 which is not proper to use. To make it back to work, we should add SUID and SGID bit to it.

[oracle@test bin]$ chmod 6751 oracle

Or this:

[oracle@test bin]$ chmod ug+s oracle

Let’s see the result.

[oracle@test bin]$ ll oracle
-rwsr-s--x 1 oracle oinstall 443586008 Dec 16  2020 oracle

Relink OS

Oracle binaries may not have linked correctly in this platform, we should relink them back in order to stick with OS.

[oracle@test ~]$ relink all

This could happen after a patching or upgrading.

TNSPING

Let’s see how we reproduce TNS-12547.

C:Usersed>tnsping compdb 100

We consecutively tnsping a remote listener 100 times to see its status change.

Solution

The listener could be hang or not responsive so that caused TNS-12547. You should restart (stop + start) current listener or try to fix it. Otherwise you should go for anyone of backup listeners to keep the continuity of database service.

For more connection troubleshooting, you may refer to Oracle 19c Net Services Administrator’s Guide: 15 Testing Connections.

In general, when logged in as an Oracle installation user, by setting two environment variables ORACLE_SID and ORACLE_HOME, local bequeath connection «sqlplus / as sysdba» works absolutely fine.

But in this specific case it was throwing ERROR: ORA-12547: TNS:lost contact.

There are many useful links available but none were helpful in my particular scenario.

Also, once, ORACLE_BASE was set, it worked like a charm.

It took me reading through 2000 lines of strace and a whole day of debugging to figure out the issue. 

So, here I am sharing my findings, maybe it will save someone else some time

ISSUE MANIFESTATION:

When attempted to connect by setting basic environment variables, connect request failed with ORA-12547

[dev01] : /home/abhimanyusuri> export ORACLE_SID=DEV01
[dev01] : /home/abhimanyusuri> export ORACLE_HOME=/db/oracle/product/12.2/DBHOME
[dev01] : /home/abhimanyusuri> export PATH=${PATH}:${ORACLE_HOME}/bin:.
[dev01] : /home/abhimanyusuri> which sqlplus
/db/oracle/product/12.2/DBHOME/bin/sqlplus
[dev01] : /home/abhimanyusuri> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 11 16:15:11 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-12547: TNS:lost contact


Enter user-name: ^C
[dev01] : /home/abhimanyusuri>

But, it was known that, when a custom «.env» is used to set environment, everything works fine.

So, rather than, setting up the environment by sourcing an «env» file, I decided to go with setting variables defined in it, one by one.

WORKAROUND :

Upon setting the very first one i.e. export ORACLE_BASE=/db/oracle, along with the variables mentioned in the above snippet, the connection was successfully established. But why not, without it?

[dev01] : /home/abhimanyusuri> export ORACLE_BASE=/db/oracle
[dev01] : /home/abhimanyusuri> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 12 14:02:42 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

It is also established from observed behavior that, binaries are in place, intact with desired permissions, otherwise, it would have never worked.

DEBUGGING ROUND1: 

I looked through the following areas and confirmed everything is as required/suggested in metanotes

• Checked SQLNET.EXPIRE_TIME.
• Checked permissions on file ${ORACLE_HOME}/bin/sqlplus —>  6751
• Checked permissions on file ${ORACLE_HOME}/bin/oracle  —>  6751
• Performed relinking as suggested in metalink : ${ORACLE_HOME}/bin/relink all
• Checked /proc/<<PID_PMON>>/environ to rule out if instance was started with wrong env settings.
• Restarted DB instance
• Collected «STRACE» and stared at the o/p for few hours
• Compared straces for successful and unsuccessful connection

NO HELP

At this point, being completely clueless about what is happening, decided to start from scratch. 

DEBUGGING ROUND2:

1. Login to the server via Oracle installation user (same user as before).

2. Run oraenv

Upon running oranev, I discovered something strange «The Oracle base has been set to /db/oracle/product/12.2/DBHOME».

Rather than setting $ORACLE_BASE to /db/oracle, it was being set to $ORACLE_HOME.

But connection did work fine post this.

Change approach: Need to shift focus to $ORACLE_BASE.

To further investigate, executed oraenv in debug mode i.e. «sh -x /usr/local/bin/oraenv», which lead to below mentioned code piece

+ ORABASE_EXEC=/db/oracle/product/12.2/DBHOME/bin/orabase
+ '[' x '!=' x ']'
+ OLD_ORACLE_BASE=
+ '[' -w /db/oracle/product/12.2/DBHOME/inventory/ContentsXML/oraclehomeproperties.xml ']'  
+ '[' -f /db/oracle/product/12.2/DBHOME/bin/orabase ']'
+ '[' -x /db/oracle/product/12.2/DBHOME/bin/orabase ']'
++ /db/oracle/product/12.2/DBHOME/bin/orabase
+ ORACLE_BASE=
+ '[' x '!=' x ']'
+ '[' '' '!=' true ']'
+ echo 'The Oracle base has been set to '
The Oracle base has been set to 
+ export ORACLE_BASE
+ '[' x = x ']'
+ '[' '' '!=' true ']'
+ echo 'Resetting ORACLE_BASE to its previous value or ORACLE_HOME'
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
+ '[' '' '!=' '' ']'
+ ORACLE_BASE=/db/oracle/product/12.2/DBHOME
+ '[' '' '!=' true ']'
+ echo 'The Oracle base has been set to /db/oracle/product/12.2/DBHOME'

Checked content and permissions of file «oraclehomeproperties.xml», everything seems in place, here is an excerpt 

[dev01] : /home/abhimanyusuri> ls -ltr /db/oracle/product/12.2/DBHOME/inventory/ContentsXML/oraclehomeproperties.xml
-rw-r-----. 1 abhimanyusuri dba 545 Feb 19  2018 /db/oracle/product/12.2/DBHOME/inventory/ContentsXML/oraclehomeproperties.xml

cat /db/oracle/product/12.2/DBHOME/inventory/ContentsXML/oraclehomeproperties.xml
..
<PROPERTY NAME="ORACLE_BASE" VAL="/db/oracle"/>
..

Still, execution of «orabase» was not returning anything

++ /db/oracle/product/12.2/DBHOME/bin/orabase
+ ORACLE_BASE=
+ '[' x '!=' x ']'
+ '[' '' '!=' true ']'
+ echo 'The Oracle base has been set to '
The Oracle base has been set to

Thought, to peek into STRACE o/p one more time. One big difference between strace o/p from successful and unsuccessful connection was logging in «sqlnet.log» and «log.xml»

Here is an excerpt from both log files at the time of failure

SQLNET.LOG was filled with below error

Fatal NI connect error 12547, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/db/oracle/product/12.2/DBHOME/bin/oracle)(ARGV0=oracleDEV01)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(DETACH=NO))(CONNECT_DATA=(CID=(PROGRAM=sqlplus)(HOST=DEV01)(USER=abhimanyusuri))))

VERSION INFORMATION:
TNS for Linux: Version 12.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
Time: 11-SEP-2018 23:08:25
Tracing not turned on.
Tns error struct:
ns main err code: 12547

TNS-12547: TNS:lost contact
ns secondary err code: 12560
nt main err code: 517

TNS-00517: Lost contact
nt secondary err code: 32
nt OS err code: 0

diag/rdbms/alert/log.xml had below message

</msg>
<msg time='2018-09-11T23:08:25.738-07:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='dev01'
host_addr='xxxxxxxxxxx' pid='220600'>
<txt>Oracle Clusterware infrastructure fatal error in oracle (OS PID 220600_139933757714208): Internal error (ID (:CLSB00107:)) - Error -1 (ORA-08275) determining Oracle base
</txt>
</msg>

And again, error related to oracle base.

Please note, this is a single instance database with no ASM/grid, so error is little misleading.

At this point, I had an intuition, that whatever it is, had to be related with $ORACLE_BASE.

So, decided to hammer «oraenv», using ${ORACLE_TRACE} 

[dev01] : /home/abhimanyusuri> export ORACLE_TRACE=T
[dev01] : /home/abhimanyusuri> . oraenv
++ N=
++ C=
++ grep c
++ echo 'c'
++ N=-n
++ '[' 0 = 0 ']'
..
..
ORACLE_SID = [abhimanyusuri] ? ++ read NEWSID
DEV01
++ case "$NEWSID" in
++ ORACLE_SID=DEV01
..
++ ORACLE_BASE=/db/oracle/product/12.2/DBHOME
++ '[' '' '!=' true ']'
++ echo 'The Oracle base has been set to /db/oracle/product/12.2/DBHOME'
The Oracle base has been set to /db/oracle/product/12.2/DBHOME
++ export ORACLE_BASE

Since, all environment variables had been set, decided to go for another iteration, without passing any argument value at prompt and let oracle decipher.

BULLS-EYE, «orabasetab»

[dev01] : /home/abhimanyusuri> . oraenv
+ . oraenv
++ SILENT=
++ '[' 0 -gt 0 ']'
++ case ${ORACLE_TRACE:-""} in
++ set -x
++ N=
..
..
++ ORASID=DEV01
++ echo -n 'ORACLE_SID = [DEV01] ? '
ORACLE_SID = [DEV01] ? ++ read NEWSID

++ case "$NEWSID" in
++ ORACLE_SID=DEV01
++ export ORACLE_SID
+++ dbhome DEV01
..
..
++ case "$OLDHOME" in
++ case "$PATH" in
+++ echo /usr/local/symlinks:/usr/local/scripts:---
#orabasetab file is used to track Oracle Home associated with Oracle Base
..
..
..
..

«ORABASETAB», I had never heard of it before. 

Circled back to o/p of strace and found the reference in there as well. 

Below is an excerpt from strace log

lstat("/etc/orabasetab", 0x------) = -1 ENOENT (No such file or directory)
open("/etc/orabasetab", O_RDONLY) = -1 ENOENT (No such file or directory)
lstat("/db/oracle/product/12.2/DBHOME/install/orabasetab", {st_mode=S_IFREG|0660, st_size=129, ...}) = 0
open("/db/oracle/product/12.2/DBHOME/install/orabasetab", O_RDONLY) = 9
fstat(9, {st_mode=S_IFREG|0660, st_size=129, ...}) = 0
fstat(9, {st_mode=S_IFREG|0660, st_size=129, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x-----
read(9, "#orabasetab file is used to trac"..., 4096) = 129
read(9, "", 4096)                = 0
close(9)                         = 0

ROOT CAUSE IDENTIFICATION AND FIX:

Upon, checking the content of «orabasetab» file, it was discovered that file had a wrong mapping of ORACLE_HOME and ORACLE_BASE.

[dev01] : /home/abhimanyusuri> cat /db/oracle/product/12.2/DBHOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base
/db/oracle/product/12.2/DB_HOME_DOESNT_EXIST:/db/oracle:OraDB12Home1:N:

Commented old entry and entered a new line with the right mapping.

Back to business. 

[dev01] : /home/abhimanyusuri> export ORACLE_HOME=/db/oracle/product/12.2/DBHOME
[dev01] : /home/abhimanyusuri> export ORACLE_SID=DEV01
[dev01] : /home/abhimanyusuri> env|egrep -i "ora|tns"
ORACLE_SID=DEV01
ORACLE_HOME=/db/oracle/product/12.2/DBHOME
[dev01] : /home/abhimanyusuri> PATH=${ORACLE_HOME}/bin:${PATH}:
[dev01] : /home/abhimanyusuri> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 12 22:50:51 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

Reference to Oracle metanote for ORABASETAB:

https://docs.oracle.com/en/database/oracle/oracle-database/18/ntdbi/win-orabasetab.html

Thanks,

Suri

Getting the following error when trying to connect using Sql*Plus

ERROR:
$sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Mar 30 11:59:06 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact

CAUSE:

1) to kernel parameters settings
2) Incorrect permissions on the ORACLE executable
3) Insufficient ulimit setting for stack
4) $ORACLE_HOME/rdbms/lib/config.o is 0 bytes
5) Oracle binaries have not been linked correctly

SOLUTION:

1) Please check the notes below that provide the required settings for kernel parameters
Note 169706.1 Oracle Database on AIX,HP-UX,Linux,MacOSX,Solaris,Tru64

2) Please check permissions by running:

$ cd $ORACLE_HOME/bin
$ ls -l oracle
The output should show the correct permission which is:

-rwsr-s–x 1 oracle dba

If not, then please execute the following to correct the permissions:
$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle
$ ls -l oracle

3) Check the current ulimit setting for stack:

ulimit -a

Check the install guide for your specific platform and version of Oracle and set the stack appropriately.

4) Check to ensure the following two files are not 0 bytes:

$ORACLE_HOME/bin/oracle
$ORACLE_HOME/rdbms/lib/config.o

If yes, rename the following file:

$ cd $ORACLE_HOME/rdbms/lib
$ mv config.o config.o.bad

Then, relink the oracle binary:
$ relink oracle

5) Check the alert log for any errors (ORA-00020: maximum number of processes) and solve.

select * from v$resource_limit and check maximum utilization and limit values.

RESOURCE_NAME      MAX_UTILIZATION LIMIT_VALUE
————-           ————————– ————————–
processes      350                350
sessions      380                380

6) If the above does not resolve I suggest that you shutdown the database and listener and then “relink all”

Please refer the ORACLE SUPPORT DOCUMENT ID 422173.1

Thank You !!

Post a comment or leave a trackback: Trackback URL.

So I have cloned this repo and built the oracle/database:12.2.0.1-ee image locally. Everything worked fine.

Now I am trying to run this built image with the following docker-compose file:

oracle:
    image: oracle/database:12.2.0.1-ee
    container_name: oracle
    volumes:
      - oracle:/opt/oracle/oradata # persistent oracle database data.
    ports:
      - 1521:1521
      - 8080:8080
      - 5500:5500

volumes:
  oracle:
    name: oracle

In the docker-compose file I am doing nothing special, just the standard stuff I saw in the sample docker-compose file in this repo. The only difference is that I am using a named value to get over the permissions issues that I’ve seen on the issues board. However, that being said, when I run the docker-compose file with docker-compose up, I receive the below.

ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: qLAVE9zSuQw=1

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-AUG-2020 22:59:35

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Starting /opt/oracle/product/12.2.0.1/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/e7bde90b6c02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                14-AUG-2020 22:59:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/e7bde90b6c02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully
[WARNING] [DBT-11209] Current available physical memory is less than the required physical memory (2,048MB) for creating the database.
[WARNING] [DBT-10102] The listener configuration is not selected for the database. EM DB Express URL will not be accessible.
   CAUSE: The database should be registered with a listener in order to access the EM DB Express URL.
   ACTION: Select a listener to be registered or created with the database.
Copying database files
1% complete
2% complete
DBCA Operation failed.
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.
[ 2020-08-14 22:59:40.071 UTC ] Copying database files
DBCA_PROGRESS : 1%
[ 2020-08-14 22:59:40.263 UTC ] ORA-12547: TNS:lost contact

DBCA_PROGRESS : 2%
[ 2020-08-14 22:59:40.421 UTC ] Error while cataloging RMAN Backups
[ 2020-08-14 22:59:40.526 UTC ] DBCA_PROGRESS : DBCA Operation failed.

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 14 22:59:40 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-12547: TNS:lost contact


Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
mv: cannot stat '/opt/oracle/product/12.2.0.1/dbhome_1/dbs/spfileORCLCDB.ora': No such file or directory
mv: cannot stat '/opt/oracle/product/12.2.0.1/dbhome_1/dbs/orapwORCLCDB': No such file or directory
ORACLE_HOME = [/home/oracle] ? ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID .
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base remains unchanged with value /opt/oracle
/opt/oracle/checkDBStatus.sh: line 26: sqlplus: command not found
#####################################
########### E R R O R ###############
DATABASE SETUP WAS NOT SUCCESSFUL!
Please check output for further info!
########### E R R O R ###############
#####################################
The following output is now a tail of the alert.log:
tail: cannot open '/opt/oracle/diag/rdbms/*/*/trace/alert*.log' for reading: No such file or directory
tail: no files remaining

FYI I have a storage driver of overlay2 (I’m running a mac using Catalina) which I’ve seen mentioned on a few issues, so I don’t think it’s that.

More information, the reason I am using this custom built image instead is because Partitioning is disabled on the pre-built image on DockerHub Store, and I just wanted to see if it was included in the image I built from this repository. However in efforts to just see if that was the case, I’ve spend the last few hours tackling with just getting this image to run.

I’ve seen across the issues that '/opt/oracle/product/12.2.0.1/dbhome_1/dbs/orapwORCLCDB': No such file or directory means that its a permissions issues however using the named volume I thought would resolve this. Open to suggestions.