0

I installed Oracle 18c XE on Oracle Linux 7 for local development purposes and everything functions as expected. The script that I wrote to install and configure Oracle XE may be found at https://gist.github.com/cbj4074/d8fee249e5a959d9aced6e6bd5e46959.

The problem, however, is that as soon as I reboot the machine, I'm no longer able to connect to the database.

Attempts to connect yield the following error message in the client:

An error was encountered performing the requested operation:

IO Error: The Network Adapter could not establish the connection

Vendor code 17002

Prior to rebooting for the first time, the listener status output is as follows:

$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 04-OCT-2019 07:06:46

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                04-OCT-2019 05:56:26
Uptime                    0 days 1 hr. 10 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/oracle7/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle7)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle7)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "94166626b1dd63e4e0530100007f8588" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

After a reboot, the listener status is as follows:

$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 04-OCT-2019 07:19:37

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

Clearly, the listener is not running, so I start it:

$ lsnrctl start

Now the default listener is running, but there are no dynamic service bindings (The listener supports no services):

$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 03-OCT-2019 13:21:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                03-OCT-2019 13:21:31
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/oracle7/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle7)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Attempts to connect now yield a different error than before the listener was started:

An error was encountered performing the requested operation:

Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor


Vendor code 12514

Searching for information about ORA-12514 leads down a rabbit-hole of unhelpful and largely irrelevant suggestions given the fact that everything works correctly after initial installation and ceases to function only after rebooting the system.

Surely, there is some mechanism by which the pre-reboot listener and service state can be restored automatically after a reboot or cold startup! The fact that this is not the default behavior in a fresh installation is baffling.

1

On its face, the solution is in fact simple and consists of the following basic steps, performed as the oracle user:

  1. Change N to Y on only entry in /etc/oratab
  2. Start the listener: lsnrctl start
  3. Start the database(s): dbstart

Automating the above steps is a bit more onerous.

Create Startup and Shutdown Scripts

Create the directory in which to store the scripts:

mkdir /home/oracle/scripts

Now, create the three necessary scripts (environment configuration, startup, and shutdown):

/home/oracle/scripts/set_env.sh:

# Oracle Settings

# See: https://oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux#what-i-use

export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=oracle7.localdomain
export ORACLE_UNQNAME=xe
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/18c/dbhomeXE
export ORACLE_SID=xe

export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

/home/oracle/scripts/start_all.sh:

#!/bin/bash

# See: https://oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux

export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

# This had to be moved after the "oraenv" call because that sets $ORACLE_HOME
# incorrectly due to the fact that "dbhome" returns "/home/oracle" instead of the
# actual/correct value, "/opt/oracle/product/18c/dbhomeXE".

. /home/oracle/scripts/set_env.sh

dbstart $ORACLE_HOME

/home/oracle/scripts/stop_all.sh:

#!/bin/bash

# See: https://oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux

export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

# This had to be moved after the "oraenv" call because that sets $ORACLE_HOME
# incorrectly due to the fact that "dbhome" returns "/home/oracle" instead of the
# actual/correct value, "/opt/oracle/product/18c/dbhomeXE".

. /home/oracle/scripts/set_env.sh

dbshut $ORACLE_HOME

Finally, set appropriate ownership and permissions on the scripts:

chown -R oracle:oinstall /home/oracle/scripts
chmod -R 770 /home/oracle/scripts

Create systemd Unit

Beginning with Oracle Linux 7, systemd is included, which makes it easy to handle not only startup, but graceful, automatic shutdown as well.

/home/oracle/scripts/enable-automatic-startup.sh:

#!/bin/sh

# Enable automatic startup for the default instance (otherwise, the "dbstart"
# command will not start it).

sed -i "s/:N/:Y/" /etc/oratab

# See: https://oracle-base.com/articles/linux/linux-services-systemd#creating-linux-services

cat > /lib/systemd/system/dbora.service <<EOF
[Unit]
Description=The Oracle Database Service
After=syslog.target network.target

[Service]
# systemd ignores PAM limits, so set any necessary limits in the service.
# Not really a bug, but a feature.
# https://bugzilla.redhat.com/show_bug.cgi?id=754285
LimitMEMLOCK=infinity
LimitNOFILE=65535

#Type=simple
# idle: similar to simple, the actual execution of the service binary is delayed
#       until all jobs are finished, which avoids mixing the status output with shell output of services.
RemainAfterExit=yes
User=oracle
Group=oinstall
Restart=no
ExecStart=/bin/bash -c '/home/oracle/scripts/start_all.sh'
ExecStop=/bin/bash -c '/home/oracle/scripts/stop_all.sh'

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl start dbora.service
systemctl enable dbora.service

Finally, execute the above script to enable automatic startup and shutdown for the required Oracle database services:

./enable-automatic-startup.sh

Now, when the systems is rebooted, all required services will be started automatically and clients may connect successfully.

Unanswered Questions

  1. Why does the dbhome command return /home/oracle? Is this because the actual directory, /opt/oracle/product/18c/dbhomeXE, was not specified in a Response file during installation?
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.