Auswahl  

Automatischer Start einer Oracle Datenbank unter LINUX/UNIX 

Oracle
DBA
RDBMS 11.x
18.03.19 (MP)
18.03.19 (MP)
Autostart, Datenbank

Body

Da Oracle das Thema eines automatischen Starts in den Produktiv-Datenbanken immer 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 gestart 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.1.0/dbhome_1:Y

Dann legen wir 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: o12c:/u01/app/oracle/product/12.1.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.
 

Besuchen Sie uns doch bei einer unsere über 40 Oracle Schulungen in München - Unterhaching.