ORA-12547: TNS:lost contact 錯誤處理，ora-12547tns

ORA-12547: TNS:lost contact 錯誤處理，ora-12547tns

閑來無事，配置一下oracle資料庫的隨系統自動重啟，查閱資料得知，主要需要配置/etc/oratab 和 rc.local。 配置oracle隨系統自啟動主要使用了$ORACLE_HOME/bin/dbstart檔案。先把dbstart的內容貼出來，方便查看

#!/bin/sh## $Id: dbstart.sh 22-may-2008.05:05:45 arogers Exp $ # Copyright (c) 1991, 2008, Oracle. All rights reserved.  ##################################### # usage: dbstart $ORACLE_HOME## This script is used to start ORACLE from /etc/rc(.local).# It should ONLY be executed as part of the system boot procedure.## This script will start all databases listed in the oratab file# whose third field is a "Y".  If the third field is set to "Y" and# there is no ORACLE_SID for an entry (the first field is a *),# then this script will ignore that entry.## This script requires that ASM ORACLE_SID's start with a +, and # that non-ASM instance ORACLE_SID's do not start with a +.## If ASM instances are to be started with this script, it cannot# be used inside an rc*.d directory, and should be invoked from# rc.local only. Otherwise, the CSS service may not be available# yet, and this script will block init from completing the boot# cycle.## If you want dbstart to auto-start a single-instance database that uses# an ASM server that is auto-started by CRS (this is the default behavior# for an ASM cluster), you must change the database's ORATAB entry to use# a third field of "W" and the ASM's ORATAB entry to use a third field of "N".# These values specify that dbstart auto-starts the database only after# the ASM instance is up and running.## Note:# Use ORACLE_TRACE=T for tracing this script.## The progress log for each instance bringup plus Error and Warning message[s]# are logged in file $ORACLE_HOME/startup.log. The error messages related to # instance bringup are also logged to syslog (system log module).# The Listener log is located at $ORACLE_HOME_LISTNER/listener.log# # On all UNIX platforms except SOLARIS# ORATAB=/etc/oratab## To configure, update ORATAB with Instances that need to be started up#    Entries are of the form:#    $ORACLE_SID:$ORACLE_HOME:<N|Y|W>:#    An example entry:#    main:/usr/lib/oracle/emagent_10g:Y## Overall algorithm:# 1) Bring up all ASM instances with 'Y' entry in status field in oratab entry# 2) Bring up all Database instances with 'Y' entry in status field in#    oratab entry# 3) If there are Database instances with 'W' entry in status field#    then#      iterate over all ASM instances (irrespective of 'Y' or 'N') AND#      wait for all of them to be started#    fi# 4) Bring up all Database instances with 'W' entry in status field in#    oratab entry######################################LOGMSG="logger -puser.alert -s "trap 'exit' 1 2 3# for script tracingcase $ORACLE_TRACE in  T) set -x ;;esac    # Set path if path not set (if called from /etc/rc)SAVE_PATH=/bin:/usr/bin:/etc:${PATH} ; export PATHSAVE_LLP=$LD_LIBRARY_PATH# First argument is used to bring up Oracle Net ListenerORACLE_HOME_LISTNER=$1if [ ! $ORACLE_HOME_LISTNER ] ; then  echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"  echo "Usage: $0 ORACLE_HOME"else  LOG=$ORACLE_HOME_LISTNER/listener.log  # Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to  # a different ORACLE_HOME for each entry in the oratab.  export ORACLE_HOME=$ORACLE_HOME_LISTNER  # Start Oracle Net Listener  if [ -x $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then    echo "$0: Starting Oracle Net Listener" >> $LOG 2>&1    $ORACLE_HOME_LISTNER/bin/lsnrctl start >> $LOG 2>&1 &    VER10LIST=`$ORACLE_HOME_LISTNER/bin/lsnrctl version | grep "LSNRCTL for " | cut -d' ' -f5 | cut -d'.' -f1`    export VER10LIST  else    echo "Failed to auto-start Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"  fifi# Set this in accordance with the platformORATAB=/etc/oratabif [ ! $ORATAB ] ; then  echo "$ORATAB not found"  exit 1;fi# Checks Version Mismatch between Listener and Database Instance.# A version 10 listener is required for an Oracle Database 10g database.# Previous versions of the listener are not supported for use with an Oracle# Database 10g database. However, it is possible to use a version 10 listener# with previous versions of the Oracle database.checkversionmismatch() {  if [ $VER10LIST ] ; then    VER10INST=`sqlplus -V | grep "Release " | cut -d' ' -f3 | cut -d'.' -f1`    if [ $VER10LIST -lt $VER10INST ] ; then      $LOGMSG "Listener version $VER10LIST NOT supported with Database version $VER10INST"      $LOGMSG "Restart Oracle Net Listener using an alternate ORACLE_HOME_LISTNER:"      $LOGMSG "lsnrctl start"    fi  fi}# Starts a Database Instancestartinst() {  # Called programs use same database ID  export ORACLE_SID  # Put $ORACLE_HOME/bin into PATH and export.  PATH=$ORACLE_HOME/bin:${SAVE_PATH} ; export PATH  # add for bug # 652997  LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${SAVE_LLP} ; export LD_LIBRARY_PATH  PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora  SPFILE=${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora  SPFILE1=${ORACLE_HOME}/dbs/spfile.ora  echo ""  echo "$0: Starting up database \"$ORACLE_SID\""  date  echo ""  checkversionmismatch  # See if it is a V6 or V7 database  VERSION=undef  if [ -f $ORACLE_HOME/bin/sqldba ] ; then    SQLDBA=sqldba    VERSION=`$ORACLE_HOME/bin/sqldba command=exit | awk '      /SQL\*DBA: (Release|Version)/ {split($3, V, ".") ;      print V[1]}'`    case $VERSION in      "6") ;;      *) VERSION="internal" ;;    esac  else    if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then      SQLDBA=svrmgrl      VERSION="internal"    else      SQLDBA="sqlplus /nolog"    fi  fi  STATUS=1  if [ -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.dbf ] ; then    STATUS="-1"  fi  if [ -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.ora ] ; then    STATUS="-1"  fi  pmon=`ps -ef | grep -w "ora_pmon_$ORACLE_SID"  | grep -v grep`  if [ "$pmon" != "" ] ; then    STATUS="-1"    $LOGMSG "Warning: ${INST} \"${ORACLE_SID}\" already started."  fi  if [ $STATUS -eq -1 ] ; then    $LOGMSG "Warning: ${INST} \"${ORACLE_SID}\" possibly left running when system went down (system crash?)."    $LOGMSG "Action: Notify Database Administrator."    case $VERSION in      "6")  sqldba "command=shutdown abort" ;;      "internal")  $SQLDBA $args <<EOFconnect internalshutdown abortEOF        ;;      *)  $SQLDBA $args <<EOFconnect / as sysdbashutdown abortquitEOF        ;;    esac    if [ $? -eq 0 ] ; then      STATUS=1    else      $LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."    fi  fi  if [ $STATUS -eq 1 ] ; then    if [ -e $SPFILE -o -e $SPFILE1 -o -e $PFILE ] ; then      case $VERSION in        "6")  sqldba command=startup ;;        "internal")  $SQLDBA <<EOF connect internalstartupEOF          ;;        *)  $SQLDBA <<EOF connect / as sysdbastartupquitEOF          ;;      esac      if [ $? -eq 0 ] ; then        echo ""         echo "$0: ${INST} \"${ORACLE_SID}\" warm started."       else        $LOGMSG ""         $LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."       fi    else      $LOGMSG ""       $LOGMSG "No init file found for ${INST} \"${ORACLE_SID}\"."       $LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."     fi  fi}# Starts an ASM Instancestartasminst() {  # Called programs use same database ID  export ORACLE_SID  ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`  # Called scripts use same home directory  export ORACLE_HOME  # For ASM instances, we have a dependency on the CSS service.  # Wait here for it to become available before instance startup.  # Is the 10g install intact? Are all necessary binaries present?  if [ ! -x $ORACLE_HOME/bin/crsctl ]; then    $LOGMSG "$ORACLE_HOME/bin/crsctl not found when attempting to start"    $LOGMSG "  ASM instance $ORACLE_SID."  else     COUNT=0    $ORACLE_HOME/bin/crsctl check css    RC=$?    while [ "$RC" != "0" ];      do      COUNT=`expr $COUNT + 1`      if [ $COUNT = 15 ] ; then        # 15 tries with 20 sec interval => 5 minutes timeout        $LOGMSG "Timed out waiting to start ASM instance $ORACLE_SID"                 $LOGMSG "  CSS service is NOT available."        exit $COUNT      fi      $LOGMSG "Waiting for Oracle CSS service to be available before starting "      $LOGMSG " ASM instance $ORACLE_SID. Wait $COUNT."      sleep 20      $ORACLE_HOME/bin/crsctl check css      RC=$?    done  fi  startinst}# Start of dbstartup script## Loop for every entry in oratab file and and try to start# that ORACLE.## ASM instances need to be started before 'Database instances'# ASM instance is identified with '+' prefix in ORACLE_SID# Following loop brings up ASM instance[s]cat $ORATAB | while read LINEdocase $LINE in  \#*)                ;;        #comment-line in oratab  *)  ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`  if [ "$ORACLE_SID" = '*' ] ; then    # same as NULL SID - ignore this entry    ORACLE_SID=""    continue  fi  # Proceed only if last field is 'Y'.  if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then    # If ASM instances    if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then      INST="ASM instance"      ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`      # Called scripts use same home directory      export ORACLE_HOME      # file for logging script's output      LOG=$ORACLE_HOME/startup.log      touch $LOG      chmod a+r $LOG      echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"      startasminst >> $LOG 2>&1    fi  fi  ;;esacdone# exit if there was any trouble bringing up ASM instance[s]if [ "$?" != "0" ] ; then  exit 2fi## Following loop brings up 'Database instances'#cat $ORATAB | while read LINEdocase $LINE in  \#*)                ;;        #comment-line in oratab  *)  ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`  if [ "$ORACLE_SID" = '*' ] ; then    # same as NULL SID - ignore this entry    ORACLE_SID=""    continue  fi  # Proceed only if last field is 'Y'.  if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then    # If non-ASM instances    if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then      INST="Database instance"      ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`      # Called scripts use same home directory      export ORACLE_HOME      # file for logging script's output      LOG=$ORACLE_HOME/startup.log      touch $LOG      chmod a+r $LOG      echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"      startinst >> $LOG 2>&1    fi  fi  ;;esacdone## Following loop brings up 'Database instances' that have wait state 'W'#cat $ORATAB | while read LINEdocase $LINE in  \#*)                ;;        #comment-line in oratab  *)  ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`  if [ "$ORACLE_SID" = '*' ] ; then    # same as NULL SID - ignore this entry    ORACLE_SID=""    continue  fi  # Proceed only if last field is 'W'.  if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then    W_ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`    # DB instances with 'W' (wait state) have a dependency on ASM instances via CRS.    # Wait here for 'all' ASM instances to become available.    cat $ORATAB | while read LINE    do    case $LINE in      \#*)                ;;        #comment-line in oratab      *)      ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`      if [ "$ORACLE_SID" = '*' ] ; then        # same as NULL SID - ignore this entry        ORACLE_SID=""        continue      fi      if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then        INST="ASM instance"        ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`        if [ -x $ORACLE_HOME/bin/srvctl ] ; then          COUNT=0          NODE=`olsnodes -l`          RNODE=`srvctl status asm -n $NODE | grep "$ORACLE_SID is running"`          RC=$?          while [ "$RC" != "0" ]; # wait until this comes up!          do            COUNT=$((COUNT+1))            if [ $COUNT = 5 ] ; then              # 5 tries with 60 sec interval => 5 minutes timeout              $LOGMSG "Error: Timed out waiting on CRS to start ASM instance $ORACLE_SID"                       exit $COUNT            fi            $LOGMSG "Waiting for Oracle CRS service to start ASM instance $ORACLE_SID"            $LOGMSG "Wait $COUNT."            sleep 60            RNODE=`srvctl status asm -n $NODE | grep "$ORACLE_SID is running"`            RC=$?          done        else          $LOGMSG "Error: \"${W_ORACLE_SID}\" has dependency on ASM instance \"${ORACLE_SID}\""          $LOGMSG "Error: Need $ORACLE_HOME/bin/srvctl to check this dependency"        fi      fi     # asm instance      ;;    esac    done # innner while  fi  ;;esacdone # outer while# by now all the ASM instances have come up and we can proceed to bring up# DB instance with 'W' wait statuscat $ORATAB | while read LINEdocase $LINE in  \#*)                ;;        #comment-line in oratab  *)  ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`  if [ "$ORACLE_SID" = '*' ] ; then    # same as NULL SID - ignore this entry    ORACLE_SID=""    continue  fi  # Proceed only if last field is 'W'.  if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then    INST="Database instance"    if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then      $LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started"      $LOGMSG "Error: incorrect usage: 'W' not allowed for ASM instances"      continue    fi    ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`    # Called scripts use same home directory    export ORACLE_HOME    # file for logging script's output    LOG=$ORACLE_HOME/startup.log    touch $LOG    chmod a+r $LOG    echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"    startinst >> $LOG 2>&1  fi  ;;esacdone

從上面可以看出，oracle是通過rc.local來自啟動的，啟動的過程中會檢測/etc/oratab來決定啟動哪個執行個體。廢話少說，動起來先，配置 rc.local檔案如下

[root@yue rc.d]# cat rc.local                                                                                                                 #!/bin/bash                                                                                                                                   su - oraele -c "/home/app/oraele/product/11.2.0/db_1/bin/dbstart $ORACLE_HOME"

配置/etc/oratab檔案如下

[root@yue rc.d]# cat /etc/oratab #                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       # This file is used by ORACLE utilities.  It is created by root.sh                                                                            # and updated by the Database Configuration Assistant when creating                                                                           # a database.                                                                                                                                                                                                                                                                               # A colon, ':', is used as the field terminator.  A new line terminates                                                                       # the entry.  Lines beginning with a pound sign, '#', are comments.## Entries are of the form:#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:## The first and second fields are the system identifier and home# directory of the database respectively.  The third filed indicates# to the dbstart utility that the database should , "Y", or should not,# "N", be brought up at system boot time.## Multiple entries with the same $ORACLE_SID are not allowed.##oraten:/home/app/oraten/product/10.2.0/db_1:Yoraele:/home/app/oraele/product/11.2.0/db_1:Y

oraten和oraele分別屬於使用者oraten 和oraele,使用者屬性如下：

[root@yue rc.d]# id oratenuid=1001(oraten) gid=1002(oinstall) 組=1002(oinstall),1001(dba)[root@yue rc.d]# id oraeleuid=1522(oraele) gid=1002(oinstall) 組=1002(oinstall),1001(dba)

手工測試dbstart指令碼能否正常執行，

[oraele@yue ~]$ dbstart $ORACLE_HOME

問題出現了，只有oraele執行個體啟動，初步估計是使用者權限的問題，在oraele使用者下，設定oraten使用者的環境變數，然後啟動sqlplus，報如下錯誤：

oraele@yue db_1]$ ./bin/sqlplus /nologSQL*Plus: Release 10.2.0.5.0 - Production on 星期四 12月 4 14:13:30 2014Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.SQL> conn / as sysdbaERROR:ORA-12547: TNS:lost contact

網路搜集資料如下：

1:缺少必要安裝包，glibc glibc-devel libaio libaio-devel

2:$ORACLE_HOME/bin/oracle檔案的許可權不對

3:系統核心配置不正確，如檔案數量限制等

4:環境變數設定不正確

5：檢查$ORACLE_HOME/bin/oracle 和 $ORACLE_HOME/rdbms/lib/config.o 的檔案大小是否為0，若為0需要重新編譯

經查看oacle檔案的許可權不對，當前設定為：

[oraele@yue bin]$ ll oracle-rwxrwxr-x 1 oraten oinstall 125155528 8月  19 21:07 oracle

修改如下：

[root@yue bin]# chmod 6751 oracle[root@yue bin]# ll oracle-rwsr-s--x 1 oraten oinstall 125155528 8月  19 21:07 oracle

檔案許可權的詳細說明如下：

u：User，即檔案或目錄的擁有者。g：Group，即檔案或目錄的所屬群組。o：Other，除了檔案或目錄擁有者或所屬群組之外，其他使用者皆屬於這個範圍。a：All，即全部的使用者，包含擁有者，所屬群組以及其他使用者。有關許可權代號的部分，列表於下：r：讀取許可權，數字代號為"4"。w：寫入許可權，數字代號為"2"。x：執行或切換許可權，數字代號為"1"。-：不具任何許可權，數字代號為"0"。s：當檔案被執行時，根據who參數指定的使用者類型設定檔案的setuid或者setgid許可權。

看來oracle檔案的預設許可權是具有s的，而我們手工對其進行了修改，看來在安裝完資料庫後，即使我們執行chmod -R 777 $ORACLE_HOME的操作也可能是有問題的，

修改許可權後，重啟系統，一切正常