Infrastructure at your Service

Alain Fuhrer

After startup database with srvctl, sqlplus as sysdba results in ORA-12547: TNS:lost contact

A few days ago a customer called me, that he have a strange issue with a new created database in a oracle restart configuration with 19c.

Problem
The customer has patches his 19c Databases with the newest RU and afterwards he get some strange issues in the oracle Restart integration. The Tasks that were done are

  1. Install new Oracle Home (u01/app/oracle/product /19.0.0/dbhome_1)
  2. Patch new Oracle Home with newest RU for 19c
  3. stop database
  4. modify oracle home in oracle restart to new patched oracle home
  5. start database with the new oracle home
  6. run datapatch againt the database

So everything was good, the patch was applied successfully. A day later, the DBA wanted to connect to the database as sysdba. However, this was no longer possible because the connection was immediately rejected with ORA-12547: TNS:lost contact.

[[email protected]:/home/oracle]$[cdb19] sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 02 17:45:21 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:
ERROR:
ORA-12547: TNS:lost contact
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Problem solving
My first assumption was that something had gone wrong with the database patching, so I wanted to check the status of the new patch in the database first. First of all I needed to be able to connect to the database again, so I tried to stop the database with srvctl and start it manually with sqlplus.

This worked and I was able to log back into the database.

[email protected]:/home/oracle/ [cdb19] srvctl stop database -db cdb19
[email protected]:/home/oracle/ [cdb19] sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 6 11:22:45 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1543500144 bytes
Fixed Size 8896880 bytes
Variable Size 369098752 bytes
Database Buffers 1157627904 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>

dba_registry_sqlpatch view showed no abnormalities, and the patch was applied cleanly to the database.
So let’s do a restart of the database to check if we still have the same issue with the login

[email protected]:/u01/app/oracle/ [cdb19] srvctl stop database -db cdb19
[email protected]:/u01/app/oracle/ [cdb19] srvctl start database -db cdb19
[email protected]:/u01/app/oracle/ [cdb19] sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 6 11:34:31 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact

Nothing has changed, a login to the database after starting it with srvctl is still not possible.
Let’s check the cluster settings of the database:

[email protected]:/u01/app/oracle/ [cdb19] srvctl config database -db cdb19
Database unique name: cdb19
Database name: cdb19
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1/
Oracle user: oracle
Spfile: /u01/app/oracle/admin/cdb19/pfile/spfilecdb19.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,RECO
Services:
OSDBA group: oinstall
OSOPER group: oinstall
Database instance: cdb19

Solution
As a very attentive DBA, you may have already discovered the problem. There is a small error in the configuration, but I did not recognize it at first glance. So i did a research on Oracle Support about that and after a few minutes i found the following note:
DB started by sqlplus cannot be stopped by srvctl( ORA-12547 ), and vice versa (Doc ID 2612663.1)

The problem is a copy / paste error. If you modify your oracle home with srvctl to attach your database to a new oracle home and you copy the path in your shell, then be careful, that you don’t have a “/” in the end!

[email protected]:/u01/app/oracle/ [cdb19] srvctl config database -db cdb19 | grep home
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1/

This / at the end does not trigger an error when updating the home with srvctl, but it ensures that a sqlplus / as sysdba is no longer possible from this point on.
After adjusting the Oracle home parameter and restarting the database, the login works as expected.

[email protected]:/u01/app/oracle/ [cdb19] srvctl modify database -db cdb19 -o /u01/app/oracle/product/19.0.0/dbhome_1
[email protected]:/u01/app/oracle/ [cdb19] srvctl stop database -db cdb19
[email protected]:/u01/app/oracle/ [cdb19] srvctl start database -db cdb19
[email protected]:/u01/app/oracle/ [cdb19] sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 6 11:57:03 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>

Conclusion
This example shows once again that a small cause can have a big effect. Copy / Paste is basically a good thing, because it prevents typos, but it can unfortunately also lead to a significant additional effort.

 

 

One Comment

  • Victor says:

    I have to learn that same lesson during an ODA Patching exercise.. for some reason, one of the DBs has a the same OH configuration error… It was an easy fix but took us a bit of time to figure it out… 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Alain Fuhrer
Alain Fuhrer

Delivery Manager & Consultant