Starting an Oracle Database when a first connection comes in

To save resources I thought about the idea to start an Oracle database automatically when a first connection comes in. I.e. if there are many smaller databases on a server, which are not required during specific times, then we may shut them down and automatically start them when a connection comes in. The objective was that even the first connection should be successful. Is that possible? Yes, it is. Here’s what I did:

First of all I needed a failed connection event which triggers the startup of the database. In my case I took the message a listener produces on a connection of a not registered service. E.g.

sqlplus cbleile/@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 9 14:06:55 2020
Version 19.6.0.0.0

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

With the default listener-logging, above connection produces a message like the following in the listener.log-file:

oracle@oracle-19c6-vagrant:/opt/oracle/diag/tnslsnr/oracle-19c6-vagrant/listener/trace/ [orclcdb (CDB$ROOT)] tail -2 listener.log 
09-APR-2020 14:06:55 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLPDB1)(CID=(PROGRAM=sqlplus)(HOST=oracle-19c6-vagrant)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=11348)) * establish * ORCLPDB1 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

or alternatively you may check listener alert-xml-logfile log.xml in the listener/alert directory.
To keep it easy, I used the listener.log file to check if such a failed connection came in.

So now we just need a mechanism to check if we have a message in the listener.log and trigger the database-startup then.

First I do create an application service on my PDB:

alter system set container=pdb1;
exec dbms_service.create_service('APP_PDB1','APP_PDB1');
exec dbms_service.start_service('APP_PDB1');
alter system register;
alter pluggable database save state;

REMARK1: Do not use the default service of a PDB when connecting with the application. ALWAYS create a service for the application.
REMARK2: By using the “save state” I do ensure that the service is started automatically on DB-startup.

Secondly I created a tnsnames-alias, which retries the connection several times in case it fails initially:

ORCLPDB1_S =
  (DESCRIPTION =
  (CONNECT_TIMEOUT=10)(RETRY_COUNT=30)(RETRY_DELAY=2)
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = APP_PDB1)
    )
  )

Important are the parameters

(RETRY_COUNT=30)(RETRY_DELAY=2)

I.e. in case of an error we do wait for 2 seconds and try again. We try 30 times to connect. That means we do have 60 seconds to start the database when the first connection comes in.

The simple BASH-script below polls for the message of a failed connection and starts the database when such a message is in the listener.log:

#!/bin/bash
 
# Set the env for orclcdb
export ORAENV_ASK=NO
export ORACLE_SID=orclcdb
. oraenv
 
# Define where the listener log-file is
LISTENER_LOG=/opt/oracle/diag/tnslsnr/oracle-19c6-vagrant/listener/trace/listener.log
 
# create a fifo-file
fifo=/tmp/tmpfifo.$$
mkfifo "${fifo}" || exit 1
 
# tail the listener.log and write to fifo in a background process
tail -F -n0 $LISTENER_LOG >${fifo} &
tailpid=$! # optional
 
# check if a connection to service APP_PDB1 arrived and the listener returns a TNS-12514
# TNS-12514 TNS:listener does not currently know of service requested in connect descriptor
# i.e. go ahead if we detect a line containing "establish * APP_PDB1 * 12514" in the listener.log
grep -i -m 1 "establish * app_pdb1 * 12514" "${fifo}"
 
# if we get here a request to connect to service APP_PDB1 came in and the service is not 
# registered at the listener. We conclude then that the DB is down.
 
# Do some cleanup by killing the tail-process and removing the fifo-file
kill "${tailpid}" # optional
rm "${fifo}"
 
# Startup the DB
sqlplus -S / as sysdba <<EOF
startup
exit
EOF

REMARK1: You may check the discussion here on how to poll for a string in a file on Linux.
REMARK2: In production above script would probably need a trap in case of e.g. Ctrl-C’ing it to kill the background tail process and remove the tmpfifo file.

Test:

The database is down.
In session 1 I do start my simple bash-script:

oracle@oracle-19c6-vagrant:/home/oracle/tools/test_db_start_whenconnecting/ [orclcdb (CDB$ROOT)] bash ./poll_listener.bash

In session 2 I try to connect:

oracle@oracle-19c6-vagrant:/home/oracle/ [orclcdb (CDB$ROOT)] sqlplus cbleile@orclpdb1_s
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 9 14:33:11 2020
Version 19.6.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Enter password: 

After entering my password my connection-attempt “hangs”.
In session 1 I can see the following messages:

09-APR-2020 14:33:15 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=APP_PDB1)(CID=(PROGRAM=sqlplus)(HOST=oracle-19c6-vagrant)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=11592)) * establish * APP_PDB1 * 12514
ORACLE instance started.
 
Total System Global Area 3724537976 bytes
Fixed Size		    9142392 bytes
Variable Size		 1224736768 bytes
Database Buffers	 2483027968 bytes
Redo Buffers		    7630848 bytes
Database mounted.
Database opened.
./poll_listener.bash: line 38: 19096 Terminated              tail -F -n0 $LISTENER_LOG > ${fifo}
oracle@oracle-19c6-vagrant:/home/oracle/tools/test_db_start_whenconnecting/ [orclcdb (CDB$ROOT)] 

And session 2 automatically connects as the DB is open now:

oracle@oracle-19c6-vagrant:/home/oracle/ [orclcdb (CDB$ROOT)] sqlplus cbleile@orclpdb1_s
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 9 14:33:11 2020
Version 19.6.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Enter password: 
Last Successful login time: Thu Apr 09 2020 14:31:44 +01:00
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
 
cbleile@orclcdb@PDB1> 

All subsequent connects the the DB are fast of course.

With such a mechanism I could even think of starting a virtual machine from a common listener once such a connection arrives. I.e. this would even allow us to e.g. start DB-servers in the Cloud when a first connection from the application comes in. I.e. we could stop DB-VMs on the Cloud to save money and start them up with a terraform-script (or whatever CLI-tool you use to manage your Cloud) when a first DB-connection arrives.

REMARK: With (Transparent) Application Continuity there are even more possibilities, but that feature requires RAC/RAC One Node or Active Data Guard and is out of scope here.