Linux – Oracle Database 12c on Oracle Linux 7 – autostart, enabling connection as sysdba

configurationlinuxoracle

I've installed 12c on Oracle Linux 7 for my development work, as per document Oracle Database Installation Guide 12c Release 1 for Linux (E41491-10). My previous experience with administering Oracle Database is version 8 on Novell Netware and 10gR2 on Windows x64 at my prevoius workplace. I have not worked with Linux too much, save for some simple services on Ubuntu.

This time I choose not to create database on installation, so I had to run Oracle Database Configuration Assistant to do so, because I needed some options that default database did not have. I've done everything by the book, but I encountered problem one: ORACLE_HOME or ORACLE_BASE env vars were not set after creation. I had to edit .bash_profile to set these.

Database was created in default location, which is /home/oracle/app/oracle/oradata/<SID>. It was started and configured with listener. Upon completion of database creation, I noticed problem two: I can't connect to oracle instance using sqlplus / AS SYSDBA. I am presented with ORA-12162: TNS:net service name is incorrectly specified. As document Oracle Database Error Messages 12c Release 1 (E49325-06) states, I checked if TNSNAMES.ORA doesn't contain any errors in connect descriptor, but it doesn't. Full contents of this file are (KITET is my instance name):

KITET = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORALINUX7VM)(PORT = 1521))
    (CONNECT_DATA = 
      (SERVER = SHARED)
      (SERVICE_NAME = Kitet)
    )
  )

I connected only by specifying sqlplus <user>/<password>@<service_name>.

After rebooting my virtual machine, I noticed problem three: listener and oracle do not start automatically. I can start listener by using lsnrctl start, but can't start up the database (can't connect using / AS SYSDBA). I figured I could connect by specifying connection string with service_name, but I was greeted with ORA-12505: TNS:listener does not currently know of SID given in connect descriptor.

After looking here and there on the internet, I found out that by exporting ORACLE_SID, I could alleviate "can't startup" problem, so I did just that by editing (again) ~/.bash_profile.

What I still need done

I need listener and database startup automagically on boot, on Oracle Linux 7. Of course I am in posession of a document Oracle Linux Administrator's Guide for Release 7 (E54669) but there's no information on how to create startup scripts for Oracle Database and Listener. There's just info on starting and stopping EXISTING services. Of course I can't login to my VM box and startup services everytime I power it up.

Please assist.

Best Answer

Based on my own research, google results and ansible's answer, I was able to come up with complete solution for Oracle Linux 7. Everytime you read Kitet - this is my Oracle Instance SID.

To start or stop database, dbstart and dbshut scripts are used respectively. They are located in $ORACLE_HOME/bin. Both of them need a parameter, which is $ORACLE_HOME. If there's no $ORACLE_HOME environment variable, first there's a need to set it (and some more) by running 

. /usr/local/bin/oraenv

(note the preceding period, and it's not $ORACLE_HOME/bin/oraenv). Oraenv script will ask for a SID, which is needed to set correct $ORACLE_BASE. With this, using simply

dbstart $ORACLE_HOME

or

dbshut $ORACLE_HOME

will start and stop the listener and database.

To start listener using lsnrctl or start SQL*Plus using sqlplus / as sysdba, running . oraenv beforehand is also required, unless user wants to set manually some environment variables. If our linux box has only one instance, there's a possibility of automating running of . oraenv by editing user's bash profile. Enter

nano ~/.bash_profile

and type following lines (there's probably already umask 022 entry in this file, as per Oracle Database Installation Guide 12c Release 1 for Linux E41491-10):

export ORACLE_SID=Kitet
export ORAENV_ASK=NO
. /usr/local/bin/oraenv
export NLS_LANG=POLISH_POLAND.AL32UTF8
unicode_start

Line one and three are self explanatory. Line two will make oraenv not ask for database SID and use exported variable. Line four is optional and will set your language preference for client connections (e.g. SQL*Plus or lsnrctl will speak in your native language, if available). Line five is needed for console to display accented characters, and is also optional.

Automating startup and shutdown of Listener and Database

1) Once the instance is created, edit the /etc/oratab file setting the restart flag for each instance to 'Y'. My line is as follows:

Kitet:/home/oracle/app/oracle/product/12.1.0/dbhome_1:Y

2) Next, create a file called /etc/init.d/dbora as the root user, containing the following:

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the 
# Oracle database in ORA_HOME.

# path to oracle home (needed only to check if dbstart exists)
ORA_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1
# this is the user who installed oracle
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
    'start')
        # Start the Oracle database and listener:
        # Remove "&" if you don't want startup as a background process.
        export ORACLE_SID=Kitet
        export ORAENV_ASK=NO
        . /usr/local/bin/oraenv
        # at this point we have $ORACLE_HOME env variable set
        su $ORA_OWNER -c "dbstart $ORACLE_HOME" &
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle database and listener:
        export ORACLE_SID=Kitet
        export ORAENV_ASK=NO
        . /usr/local/bin/oraenv
        su $ORA_OWNER -c "dbshut $ORACLE_HOME"
        rm -f /var/lock/subsys/dbora
        ;;
esac

3) Use the chmod command to set the privileges to 750:

chmod 750 /etc/init.d/dbora

4) Associate the dbora service with the appropriate run levels and set it to auto-start using the following command:

chkconfig --add dbora

That's all. dbstart and dbshut should write to their respective logs at $ORACLE_HOME/startup.log and $ORACLE_HOME/shutdown.log. There also is $ORACLE_HOME/listener.log. There we can verify if and how instance and listener started and stopped. We could also use ps -ax | grep ora or ps -ax | grep lsnr to see if listener / oracle are running.

Related Question