Why the Oracle 12c database seems not to be started

connectivityoraclestartup

I have installed an Oracle 12c database on my development environment, which I start only once a while for integration testing. But today, it does not seem to start, or did it start but does not allow connections? Here are some clues:

  • OS is Debian 8
  • I use a shell script to start and stop the database
  • $ORACLE_SID contains

INFO

  • The hosts file contains

127.0.0.1 localhost and 127.0.1.1 Strada.nohoo.biz Strada

  • The /u01/app/oracle/diag/rdbms/info/INFO/alert_INFO.log file ends with

Completed: ALTER DATABASE OPEN Sun Mar 11 20:27:57 2018 Shared IO Pool
defaulting to 112MB. Trying to get it from Buffer Cache for process
4294.
=========================================================== Dumping current patch information
=========================================================== No patches have been applied
=========================================================== Sun Mar 11 20:27:57 2018 db_recovery_file_dest_size of 4560 MB is 0.00% used.
This is a user-specified limit on the amount of space that will be
used by this database for recovery-related files, and does not reflect
the amount of space available in the underlying filesystem or ASM
diskgroup.

  • tnsping INFO returns

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on
11-MAR-2018 21:42:22

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT =
1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
INFO.nohoo.biz)))

OK (0 msec)

  • tnsnames.ora contains
INFO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = INFO.nohoo.biz)
    )
  )
  • listner.ora contains
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
  • lsnrctl services returns

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 11-MAR-2018
21:54:24

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

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

The listener supports no services

The command completed successfully

  • The ps -A | grep ora command in a shell returns a list of

6003 ? 00:00:00 ora_pmon_info
6005 ? 00:00:00 ora_psp0_info
6007 ? 00:00:00 ora_vktm_info
6011 ? 00:00:00 ora_gen0_info
6013 ? 00:00:00 ora_mman_info
6017 ? 00:00:00 ora_diag_info
6019 ? 00:00:00 ora_dbrm_info
6021 ? 00:00:00 ora_vkrm_info
6023 ? 00:00:00 ora_dia0_info
6025 ? 00:00:00 ora_dbw0_info
6027 ? 00:00:00 ora_lgwr_info
6029 ? 00:00:00 ora_ckpt_info

But all this does not look like a successful situation!

And finally, trying to connect with SQLplus ends up with

ORA-12547: TNS:lost contact

Thanks for you help!

Best Answer

Your database seems to be up and running, but it was not registered dynamically into the listener.

Try the below:

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=info

sqlplus / as sysdba
SQL> show parameter local_listener

Check the value of local_listener. You have 3 options here:

  1. parameter value is empty - in this case, the database registers itself into the listener running on port 1521

  2. parameter value is something like LISTENER_INFO - this is a TNS entry that resolves to the address of the listener. If tnsping LISTENER_INFO resolves to a valid address, the database should be able to register itself to the listener specified by that

  3. parameter value is something like (ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)). No name resolution is required, database registers itself in the listener.

You most likely have 2. with an unresolvable name, or 3. with and invalid network address.

You can try fixing it with:

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))';

Which sets the specified address.

Or:

alter system set local_listener='' scope=memory;
alter system reset local_listener;

Which clears the value of local_listener in memory and spfile, so the database will try registering into a listener running on the default 1521 port.

If this still does not help, you can register your database in the listener statically, by adding the below in listener.ora:

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = info.nohoo.biz)
   (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
   (SID_NAME = info)
  )
 )

After that reload the listener:

lsnrctl reload

Or restart the listener:

lsnrctl stop lsnrctl start

And try logging in again.

Related Question