Da das Thema eines automatischen Starts in den Produktiv-Datenbanken immer noch nicht so ausführlich von Oracle (bis gar nicht) behandelt wird, während es in der Express Edition (die auch noch kostenlos herunterladbar ist) wunderbar funktioniert, haben wir uns dem Thema mal angenommen:
Wir brauchen eine angepasste /etc/oratab, denn in der steht, welche Oracle-Datenbanken denn automatisch beim Start des UNIX Servers gestartet werden sollen.
Die Datei oratab hat folgendes Format:
<ORACLE_SID>:<ORACLE_HOME>:Y|N
o12c:/opt/oracle/product/12.2.0/dbhome_1:N
o18c:/opt/oracle/product/18.3.0/dbhome_1:Y
o19c:/opt/oracle/product/19.3.0/dbhome_1:Y
o21c:/opt/oracle/product/21.1.0/dbhome_1:Y
XE:/opt/oracle/product/21c/dbhomeXE:Y
FREE:/opt/oracle/product/23ai/dbhomeFREE:Y
In Oracle 23ai hat Oracle die Arbeit für uns schon gemacht, wir führen nur ein paar kosmetische Aktionen aus: Der Name des Services oracle-free-23ai ist uns zu lang und wird umgetauft in oracle.
cd /etc/init.d/
mv oracle-free-23ai oracle
systemctl daemon-reload
systemctl enable oracle
systemctl start oracle
Für den Fall, dass Sie keine Oracle 23ai besitzen, oder mehr selbst einrichten wollen, hier die ausführliche Variante: Wir legen folgende Datei als /etc/init.d/dbora an
#! /bin/bash
### BEGIN INIT INFO
# Provides: dbora
# Required-Start: $local_fs $network $syslog
# Required-Stop: $local_fs $syslog
# Default-Start: 3 4 5
# Default-Stop: 3 5
# Short-Description: Start or Stop of Oracle DB Instance
### END INIT INFO
### This is just sample code, its free to use.
### It is distributed in the hope that it will be useful to others,
### but WITHOUT ANY WARRANTY; without even the implied warranty of
### MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
## chmod +x /etc/init.d/dbora
## chkconfig --add dbora # For Adding
## chkconfig --level 345 dbora on # Setting Levels (again)
## chkconfig --del dbora # For Deleting
## chkconfig --list | grep dbora # For Check
## MP 4.3.2016: Mehrere Listener finden: awk '!/DESCRIPTION_LIST/ { line = $0 } /DESCRIPTION_LIST/ { print line }' $ORACLE_HOME/network/admin/listener.ora | awk 'BEGIN { FS = "=" } ; { print $1 }' =" } ; { print $1 }'
RETVAL=0
DB_EINTRAG_NUM=0
export ORACLE_OWNER="oracle"
ORATAB=/etc/oratab
# Oratab Format: o19c:/u01/app/oracle/product/19.3.0/dbhome_1:N
LOG=$ORACLE_HOME/shutdown.log
db_start_stop(){
cat $ORATAB | awk -F# '{print $1}' | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
if [ ${#LINE} -gt 5 ]; then # Less then 5 Chars can´t be a good entry
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# NULL SID - ignore
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y' or 'W'
if [ "`echo $LINE | awk -F: '{print $NF}' - | cut -d '' -f 1 `" = "Y" ] ; then
if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then
INST="DB instance"
export ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
export ORACLE_SID
export ORA_NLS10="$ORACLE_HOME/nls/data"
(( DB_EINTRAG_NUM = DB_EINTRAG_NUM + 1 )) # Index erhöhen
LOG=$ORACLE_HOME/shutdown.log
echo "$INST \"$ORACLE_SID\": "
echo "Logfile: $LOG"
if [ "$db_mode" = 'stop' ]; then ###### S T O P ######
# Listener behandeln - ABER NUR bei erstem DB-Eintrag
if [ "${DB_EINTRAG_NUM}" = "1" ]; then
su $ORA_OWNER -mc "$ORACLE_HOME/bin/lsnrctl stop" >> $LOG 2>&1
fi
(su $ORACLE_OWNER -mc "$ORACLE_HOME/bin/sqlplus -s / as sysdba" <<EOF
shutdown immediate
exit;
EOF
) >> $LOG 2>&1
RETVAL=$?
[ $RETVAL -eq 0 ] && rm -rf /var/lock/subsys/dbora
elif [ "$db_mode" = 'stop_plus_info' ]; then ###### S T O P + I N F O ######
su $ORA_OWNER -mc "$ORACLE_HOME/bin/lsnrctl stop" >> $LOG 2>&1
echo "begin shutdown at `date`"
(su $ORACLE_OWNER -mc "$ORACLE_HOME/bin/sqlplus -s / as sysdba" <<EOF
SELECT SUM(used_ublk) as used_undo_blks FROM v\$transaction;
SELECT server as open_sessions,count(*) FROM v\$session WHERE username IS NOT NULL GROUP BY server;
shutdown immediate
exit;
EOF
)
echo "finished shutdown at `date`"
elif [ "$db_mode" = 'status' ]; then ###### S T A T U S ######
echo ## Listener:
su $ORACLE_OWNER -mc "$ORACLE_HOME/bin/lsnrctl status | egrep 'status|LSNRCTL|TNS-'"
echo ## Instance:
su $ORACLE_OWNER -mc "$ORACLE_HOME/bin/sqlplus -s / as sysdba" <<EOF
COL free_fra_gb FORMAT a14
COL inst_name FORMAT a9
COL vers NEW_VALUE vers
COL vers FORMAT a4
COL up_hours FORMAT 99999.99
COL inv_obj FORMAT 99999
WITH v AS (select substr(version,1,instr(version,'.')-1) vers
from dba_registry
where comp_id='CATALOG')
select v.vers,instance_name as inst_name,to_char(startup_time,'DD.MM.YY HH24:MI:SS') as startup_time,
round((sysdate-startup_time)*24,2) as up_hours ,
(select count(*) from dba_objects where status<>'VALID') as inv_obj,
(select round((space_limit-space_used)/1024/1024/1024,2)||' ('||(ROUND(((SPACE_LIMIT-SPACE_USED)*100/SPACE_LIMIT),2))||'%)'
from v\$recovery_file_dest) as free_fra_gb
from v,v\$instance;
EXIT &vers.
EOF
export ORACLE_VERSION=$?
if [ $ORACLE_VERSION -lt 12 ]; then ### Enterprise Manager DB Control
export ORACLE_UNQNAME=$ORACLE_SID
$ORACLE_HOME/bin/emctl status dbconsole
fi
elif [ "$db_mode" = 'start' ]; then ###### S T A R T ######
su $ORACLE_OWNER -mc "$ORACLE_HOME/bin/lsnrctl start" >> $LOG 2>&1
(su $ORACLE_OWNER -mc "$ORACLE_HOME/bin/sqlplus -s / as sysdba" <<EOF
startup
exit;
EOF
) >> $LOG 2>&1
RETVAL=$?
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/dbora
elif [ "$db_mode" = 'start' ]; then ###### S T A R T + I N F O ######
echo "try to start Instance at `date`"
su $ORACLE_OWNER -mc "$ORACLE_HOME/bin/lsnrctl start" >> $LOG 2>&1
(su $ORACLE_OWNER -mc "$ORACLE_HOME/bin/sqlplus -s / as sysdba" <<EOF
startup
exit;
EOF
) >> $LOG 2>&1
RETVAL=$?
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/dbora
[ $RETVAL -eq 0 ] && echo "Instance startet at `date`"
fi
fi
else
echo "Instance `echo $LINE | grep ':' | awk -F: '{print $1}' ` set to N in /etc/oratab"
fi
fi
;;
esac
done
}
case "$1" in
start)
db_mode=start
echo -n $"Starting Oracle + Listener: "
db_start_stop
;;
start2)
db_mode=start
echo -n $"Starting Oracle + Listener: "
db_start_stop
db_mode=status
db_start_stop
;;
stop)
db_mode=stop
echo -n $"Stopping Oracle + Listener: "
db_start_stop
;;
stop2)
db_mode=stop_plus_info
echo -n $"Stopping Oracle + Listener: "
db_start_stop
;;
status)
db_mode=status
db_start_stop
;;
restart)
echo $"Stopping Oracle + Listener: "
db_mode=stop
db_start_stop
echo cat $"Starting Oracle + Listener: "
db_mode=start
db_start_stop
;;
*)
echo $"Usage: $0 {start|start2|stop|stop2|status|restart}"
echo "start = Start Listener and Instance"
echo "start2 = Start Listener and Instance and show status"
echo "stop = Stop Listener and Instance"
echo "stop2 = Stop Listener and Instance + Show info about open sessions and open transactions before shutdown"
echo "status = Show Status of Listener and Database (+ Uptime, Invalid Objects, Free Space in Flash Recovery Area)"
echo "restart = Stop and Start Listener and Instance"
echo
echo "(c) 2014 written by Marco Patzwahl MuniQSoft GmbH Unterhaching/Munich,Germany"
echo "without any warranty. Please only use this script after testing in your environment!!!"
exit 1
esac
exit $?
Nun nur noch ein paar Abschlußarbeiten und es ist fertig:
chmod +x /etc/init.d/dbora
chkconfig --add dbora
chkconfig --level 345 dbora on
Nun sollte Ihre Oracle Datenbank automatisch beim Start des Betriebssystems mitstarten.