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

Question

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.

Answer 1

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.

• ansible: A few comments on configuring your Linux environment this way:
• ansible: 1) The $ORACLE_HOME/root.sh script copies the oraenv script from $ORACLE_HOME/bin/oraenv to /usr/local/bin/oraenv; they are not different. diff /usr/local/bin/oraenv /u01/app/oracle/product/12.1.0/db_1/bin/oraenv produces no output. 2) Oracle recommends not calling oraenv inside the .profile script for the Oracle software owner (the oracle user): docs.oracle.com/database/121/UNXAR/admin_ora.htm#UNXAR120. 3) The dbstart script does not require a parameter if you are just starting the databases. Review the link/script at TLDP to see how this works with the listener.

Answer 2

How I Enable Autostarting of Oracle Database for Demonstrations and Development

---

Here are the steps I have been using to enable autostarting of Oracle Database Enterprise Edition 10g, 11g or 12c during boot time of Oracle Linux 5 and 6. I also use these steps with Oracle Linux 7. These steps are useful for the kinds of demonstration and development setups that I typically need.

These steps are not needed for Oracle XE, since its install will prompt whether to autostart the DB and will configure the system automatically.

Create a new service script Create a file /etc/init.d/dbora using dbora with content

#! /bin/sh -x
#
# chkconfig: 2345 80 05
# description: start and stop Oracle Database Enterprise Edition on Oracle Linux 5 and 6
#
# In /etc/oratab, change the autostart field from N to Y for any
# databases that you want autostarted.
#
# Create this file as /etc/init.d/dbora and execute:
# chmod 750 /etc/init.d/dbora
# chkconfig --add dbora
# chkconfig dbora on
# Note: Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for your installation.
# ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
#
# Note: Change the value of ORACLE to the login name of the oracle owner
ORACLE=oracle
PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
case 1ドル in
'start')
 echo -n $"Starting Oracle: "
 su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &
 ;;
'stop')
 echo -n $"Shutting down Oracle: "
 su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" &
 ;;
'restart')
 echo -n $"Shutting down Oracle: "
 su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" &
 sleep 5
 echo -n $"Starting Oracle: "
 su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &
 ;;
*)
 echo "usage: 0ドル {start|stop|restart}"
 exit
 ;;
esac
exit

Set permissions on the script

# chmod 750 /etc/init.d/dbora

Tell Linux to autostart/stop the service On Oracle Linux 5 and 6 run:

# chkconfig --add dbora
# chkconfig dbora on

On Oracle Linux 7 run:

# systemctl enable dbora

Edit /etc/oratab In /etc/oratab, change the autostart field from N to Y for any databases that you want autostarted.

Starting / Stopping the DB The DB will start and stop at machine boot and shutdown.

Or it can be manually controlled with:

-On Oracle Linux 5 and 6 use:

# service dbora start

and

# service dbora stop

-On Oracle Linux 7, use:

# systemctl start dbora

and

# systemctl stop dbora

Starting Oracle Database 12c Multitenant PDBs To also start all pluggable databases when the container database starts, run this in SQL*Plus as SYSDBA:

create or replace trigger sys.after_startup
 after startup on database
begin
 execute immediate 'alter pluggable database all open';
end after_startup;
/

Refer: How I Enable Autostarting of Oracle Database for Demonstrations and Development

Answer 3

First, are you using the $ORACLE_HOME/bin/oraenv command? This is what typically sets the ORACLE_BASE, ORACLE_HOME, and ORACLE_SID environment variables on a Linux system using the Oracle Database. When connecting to an Oracle database from the command line in Linux, running oraenv should usually be your first step, as it will prompt you for the SID of the database you want to connect to, and set the ORACLE_[SID|HOME|BASE] variables based on that SID's entry in the oratab file (default location is usually /etc/oratab). You'll need to source the script by using the . oraenv syntax so that the variables set by the script stay active in the current shell.The "oracle" user should already have $ORACLE_HOME/bin set inside its $PATH variable, so you can simply run . oraenv from the command line, provide the SID for the database, and then issue sqlplus / as sysdba to login successfully. For other users besides the "oracle" user - after running the root.sh script as directed by the OUI during software installation, the oraenv script is copied to /usr/local/bin/, so other users are able to access oraenv to set the Oracle environment variables as well.

Configuring the oratab file correctly and calling . oraenv should solve your problems with being able to login to and startup the database (regardless of the listener).

Second, there is also a script called $ORACLE_HOME/bin/dbstart (and its companion, dbshut) that automatically starts the TNS listener and any databases in the oratab file that have a value of "Y" for that database SID. You can and should use these two scripts to automatically startup and shutdown your Oracle databases; it is especially important to cleanly shut down any open databases before you turn off/shutdown/reboot the VM or server.

To start the TNS listener and Oracle databases automatically at system startup, you can write a simple bash script to call dbstart for you. This usually involves placing the script in the /etc/init.d/ folder; more details on how to write a script for various Linux distros that executes on startup has been answered in detail elsewhere on SE (https://unix.stackexchange.com/questions/7793/bash-script-on-startup-linux). TLDP also has a good example with direct references to the Oracle database (using dbstart and dbshut) - http://www.tldp.org/HOWTO/Oracle-7-HOWTO-6.html. This references an older version of the Oracle Database using RHEL 5.0, but should still be relevant for your system.

• Kitet: I read about oraenv in Oracle Ins. Guide (8. Preferred postinstallation tasks) and since you mentioned it again, I read insides of that script and it explicitly mentions that it's necessary to run it with preceding period, like . oraenv not just oraenv. That's why when I ran it the wrong way I was not getting proper env. Env works properly now, thanks. I also found out about dbstart and dbshut and I created a startup/shutdown script that calls them, but Listener doesn't start, even though when launched from cmdline, it does. I'll read links you posted and will report back my status.
• ansible: Sorry, you're correct, you need to source the oraenv command as you indicated. I'll update my answer to reflect this. Have you tried just manually executing the dbstart script without calling it inside another script? Does that successfully start the listener?
• Kitet: I need to give it $ORACLE_HOME (dbstart $ORACLE_HOME) variable (or full path), else an error is raised, so listener is not started, but database is. Links you provided are only partly helpful, I needed to combine information from various sources. I will answer my own Q because there is not enougt space in a comment.