Oracle Corp. and/or its affiliates.

Oracle Net Troubleshooting Wizard - Documentation

main - Oracle Net Troubleshooting Wizard 
   
    The Diagnostic Assistant Menu enables troubleshooting and gathering
    of relevant diagnostics for Oracle Net and Sqlnet issues.
    
    Choose option "1" to begin your diagnosis:
      (1) Verify Sqlnet Connectivity via Tnsname Service Name            
      (2) Verify the Database is Running
      (3) Verify Oracle Listner Status
      (4) Perform a Loopback Tests (ping, tnsping, trcroute)
      (5) Validate Localized Configuration Files
      (6) Validate OS Network settings
      (7) Perform Diagnostic Collection using RDA
      (8) Check All of the Above    
  
         (H)elp   (E)xit

[+] Toggle Help

Showing Menu Help: 
    Oracle Diagnostic Assistant Oracle Net Menu   
     
    This menu helps resolve Possible TNS network connectivity-related errors:
    TNS-12545: Connect failed because target host or object does not exist
    ORA-12154: TNS: Could not resolve service name
    ORA-12157: TNS Internal network communication error 
    
    Explanation of available options:
      (1) Verify Sqlnet Connectivity via Tnsname Service Name :
      Invoke SQL*Plus from the OS command line "sqlplus scott/tiger@ORCL11G".  
      If this fails, check to ensure that your listener is the ORCL11G 
      service defined.
      
      (2) Verify the Database is Running:
      
       sqlplus / as sysdba;
       A message appears, confirming that you are connected with the database
       If you receive the following errors, ask your Database Administrator to 
       assist you:

       ORA-1017: invalid U/P
       ORA-1034: Oracle not available
      
      (3) Verify Oracle Listner Status:
      
        lsnrctl status
       
      (4) Perform a Loopback Tests[tnsping and trcroute]
        Check with "tnsping" using TNS service name
        tnsping V1121
        trcroute V1121 5
        
      (5) Validate Localized Configuration Files
      Check if TNS_ADMIN and ORACLE_SID are set
      env|grep TNS_ADMIN
      env|grep ORACLE_SID
      Ensure that the listener.ora, tnsnames.ora, and sqlnet.ora files 
      exist in the correct locations:
      cat $ORACLE_HOME/network/admin/listener.ora
      cat $ORACLE_HOME/network/admin/tnsnames.ora
      cat $ORACLE_HOME/network/admin/sqlnet.ora
      
      Example TNSNAMES.ORA
      
      DEV1.WORLD =
           (DESCRIPTION =
             (ADDRESS_LIST =
                 (ADDRESS =
                   (PROTOCOL = TCP)
                   (Host = 145.45.78.56)
                   (Port = 1521)
                 )
             )
             (CONNECT_DATA = (SID = ORCL)
             )
           )
      
      Example SQLNET.ORA
      
      TRACE_LEVEL_CLIENT = OFF
      SQLNET.AUTHENTICATION_SERVICES = (NONE)
      NAMES.DIRECTORY_PATH = (TNSNAMES)
      AUTOMATIC_IPC = OFF

      (6) Validate OS Network settings
      Ping and traceroute Database Server
      ping dmorldb02 -c 5
      
      The /etc/host file is used to isolate the IP address from the 
      tnsnames.ora file.
      cat /etc/hosts
      
      Active Internet connections (servers and established)
      netstat -a
       
      (7) Perform Diagnostic Collection using RDA
       Calling RDA with Moduules OS, NET, ONET
       S100OS - Collects the Operating System Information
       S122ONET - Collects Oracle Net Information
       S120NET - Collects Network Information
        
      (8) Check All of the Above

Menu Option: 1  -  [+] Show Command Details
Menu Option: 2  -  [+] Show Command Details
Menu Option: 3  -  [+] Show Command Details
Menu Option: 4  -  [+] Show Command Details
Menu Option: 5  -  [+] Show Command Details
Menu Option: 6  -  [+] Show Command Details
Menu Option: 7  -  [+] Show Command Details
Menu Option: 8  -  [+] Show Command Details
Menu Option: M  -  [+] Show Menu Details
Top | Back

COMMAND: os_100
COMMAND TYPE: OS
COMMAND TITLE: Tnsname Service Name Test Result
COMMAND DESCRIPTION: 
Verify Sqlnet Connectivity via Tnsname Service Name :
      Invoke SQL*Plus from the OS command line "sqlplus scott/tiger@ORCL11G".
COMMAND ARGUMENT(S): 
 
#!/bin/sh
SID=${ORACLE_SID}


$ORACLE_HOME/bin/sqlplus -s -l system/manager@$SID <<EOF

exit
EOF

    if [ -w /tmp/sqltest.txt ];then
        rm /tmp/sqltest.txt
    fi
    # echo "${MESSAGE}" >&1 | tee -a  ${File_name}
    errorCode=$?    # checks if the last operation (sqlplus) was completed successfully or not
    if [ ${errorCode} -ne 0 ]; then
      echo -e "\nSUMMARY STEP 1 of 7: " | tee -a /tmp/sqltest.txt
      echo "FAILURE! SQLPlus was unable to connect the DB with the supplied credentials" | tee -a /tmp/sqltest.txt
      echo "Check database is running or contact your DBA with Error ${errorCode}"| tee -a /tmp/sqltest.txt
      exit ${errorCode}
    else
      echo -e "\nSUMMARY STEP 1 of 7: " | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! SQLPlus was connected successfully" | tee -a /tmp/sqltest.txt
      echo  "PROCEED TO STEP 2" | tee -a /tmp/sqltest.txt
    fi  
    
    echo DA_FILE_COLLECTED=/tmp/sqltest.txt
Top|Back

COMMAND: os_101
COMMAND TYPE: OS
COMMAND TITLE: Database Availabilty Test Result
COMMAND DESCRIPTION: 
Verify the Database is Running:
       sqlplus / as sysdba;
COMMAND ARGUMENT(S): 
 
#!/bin/sh

SID=${ORACLE_SID}
$ORACLE_HOME/bin/sqlplus -s -l / as sysdba <<EOF
    
exit
EOF
      
    # echo "${MESSAGE}" >&1 | tee -a  ${File_name}
    errorCode=$?    # checks if the last operation (sqlplus) was completed successfully or not
    if [ ${errorCode} -ne 0 ]; then
      echo -e "\nSUMMARY STEP 2 of 7: " | tee -a /tmp/sqltest.txt
      echo "FAILURE! SQLPlus was unable to connect the DB." | tee -a /tmp/sqltest.txt
      echo "Check database is running or contact your DBA with Error ${errorCode}"| tee -a /tmp/sqltest.txt
      exit ${errorCode}
    else
      echo -e "\nSUMMARY STEP 2 of 7: " | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! SQLPlus was connected successfully" | tee -a /tmp/sqltest.txt
      echo  "PROCEED TO STEP 3" | tee -a /tmp/sqltest.txt
    fi
    
    # echo DA_FILE_COLLECTED=/tmp/sqltest.txt
Top|Back

COMMAND: os_102
COMMAND TYPE: OS
COMMAND TITLE: Listner Status Test Result
COMMAND DESCRIPTION: 
 Verify Oracle Listner Status:
        lsnrctl status
COMMAND ARGUMENT(S): 
 
#!/bin/sh

$ORACLE_HOME/bin/lsnrctl <<EOF
status
exit
EOF
    errorCode=$?    # checks if the last operation (lsnrctl) was completed successfully or not
    if [ ${errorCode} -ne 0 ]; then
      echo -e "\nSUMMARY STEP 3 of 7: " | tee -a /tmp/sqltest.txt
      echo  "FAILURE! Listner is DOWN. ERROR  ${errorCode}" | tee -a /tmp/sqltest.txt
      echo  "Contact your DBA or refer: http://docs.oracle.com/cd/B19306_01/network.102/b14212/troublestng.htm#BABBBGGG"| tee -a /tmp/sqltest.txt
      exit ${errorCode}
    else
      echo -e "\nSUMMARY STEP 3 of 7: " | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! Listner is UP" | tee -a /tmp/sqltest.txt
      echo  "PROCEED TO STEP 4" | tee -a /tmp/sqltest.txt
    fi
    
    # echo DA_FILE_COLLECTED=/tmp/sqltest.txt
Top|Back

COMMAND: os_103
COMMAND TYPE: OS
COMMAND TITLE: TNSPING Test Result
COMMAND DESCRIPTION: 
 Perform a Loopback Tests[tnsping and trcroute]
        Check with "tnsping" using TNS service name
        tnsping V1121
        trcroute V1121 5
COMMAND ARGUMENT(S): 
 
    #!/bin/sh    
    
    ${ORACLE_HOME}/bin/tnsping $SIDNAME | tee -a /tmp/sqltest.txt 2>&1
    errorCode=$?
    if [ ${errorCode} -ne 0 ]; then
      echo -e "\nSUMMARY STEP 4 of 7: " | tee -a /tmp/sqltest.txt
      echo  "FAILURE! Report ERROR: ${errorCode} to DBA" | tee -a /tmp/sqltest.txt
      exit 1
    else
      echo -e "\nSUMMARY STEP 4 of 7: " | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! SERVICE Names were successfully tested" | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! PROCEED TO STEP 5" | tee -a /tmp/sqltest.txt
    fi
    # End of Script
    echo DA_FILE_COLLECTED=/tmp/sqltest.txt
Top|Back

COMMAND: os_104
COMMAND TYPE: OS
COMMAND TITLE: TNS_ADMIN,ORACLE_SID Test Result and SQLNET Configuration Files
COMMAND DESCRIPTION: 
 Validate Localized Configuration Files
      Check if TNS_ADMIN and ORACLE_SID are set
      env|grep TNS_ADMIN
      env|grep ORACLE_SID
      Ensure that the listener.ora, tnsnames.ora, and sqlnet.ora files 
      exist in the correct locations:
      cat $ORACLE_HOME/network/admin/listener.ora
      cat $ORACLE_HOME/network/admin/tnsnames.ora
      cat $ORACLE_HOME/network/admin/sqlnet.ora
COMMAND ARGUMENT(S): 
 
    #!/bin/sh
    echo "BEGIN Enviroment Variable Validation ----------------------------"
    if [ -z "${TNS_ADMIN}" ]; then
      echo "FAILURE! Need to set TNS_ADMIN" | tee -a /tmp/sqltest.txt
      echo "See http://docs.oracle.com/cd/B19306_01/network.102/b14212/config_concepts.htm#i484224" | tee -a /tmp/sqltest.txt
      exit 1
    else
      echo -e "\nSUMMARY STEP 5 of 7: " | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! TNS_ADMIN Variable Set Correctly" | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! PROCEED TO STEP 6" | tee -a /tmp/sqltest.txt
    fi
    echo ""
    if [ -z "$ORACLE_SID" ]; then
      echo FAILURE! "Need to set TNS_ADMIN" | tee -a /tmp/sqltest.txt
      echo "See http://docs.oracle.com/cd/B19306_01/network.102/b14212/config_concepts.htm#i484224" | tee -a /tmp/sqltest.txt
      exit 1
    else
      echo -e "\nSUMMARY STEP 5 of 7: " | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! ORACLE_SID Variable Set Correctly" | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! PROCEED TO STEP 6" | tee -a /tmp/sqltest.txt
    fi
    echo -e "\nSUMMARY STEP 5 of 7: " | tee -a /tmp/sqltest.txt
    echo -e "\nBEGIN Configuration Files Validation ----------------------------"| tee -a /tmp/sqltest.txt
    echo -e "\nChecking that the listener.ora, tnsnames.ora, and sqlnet.ora files" | tee -a /tmp/sqltest.txt
    echo -e "\nexist in the correct locations"| tee -a /tmp/sqltest.txt
    if [ -z "$ORACLE_HOME" ]; then
      echo FAILURE! "Need  to set ORACLE_HOME env Variable"
      echo "See http://docs.oracle.com/cd/B19306_01/network.102/b14212/config_concepts.htm#i484224"
      exit 1
    else
      if [ -f $ORACLE_HOME/network/admin/listener.ora ]; then
        cat $ORACLE_HOME/network/admin/listener.ora | tee -a /tmp/sqltest.txt
         echo "------------------------------------------------------------------"
      else
        echo "FAILURE! listener.ora File does not exists"| tee -a /tmp/sqltest.txt
        echo "Refer to : http://docs.oracle.com/cd/B19306_01/network.102/b14212/config_concepts.htm#i484224"| tee -a /tmp/sqltest.txt
      fi  
      
      if [ -f $ORACLE_HOME/network/admin/tnsnames.ora ]; then
        cat $ORACLE_HOME/network/admin/tnsnames.ora | tee -a /tmp/sqltest.txt
        echo "------------------------------------------------------------------"
      else 
        echo "FAILURE! tnsnames.ora File does not exists"| tee -a /tmp/sqltest.txt
        echo "Refer to : http://docs.oracle.com/cd/B19306_01/network.102/b14212/config_concepts.htm#i484224"| tee -a /tmp/sqltest.txt
      fi 
      
      if [ -f $ORACLE_HOME/network/admin/listener.ora ]; then
        cat $ORACLE_HOME/network/admin/sqlnet.ora | tee -a /tmp/sqltest.txt
        echo "------------------------------------------------------------------"
      else
        echo "FAILURE! sqlnet.ora File does not exists"| tee -a /tmp/sqltest.txt
        echo "Refer to : http://docs.oracle.com/cd/B19306_01/network.102/b14212/config_concepts.htm#i484224"| tee -a /tmp/sqltest.txt
      fi
    fi
    echo  "SUCCESS! PROCEED TO STEP 6" | tee -a /tmp/sqltest.txt
    # echo DA_FILE_COLLECTED=/tmp/sqltest.txt
Top|Back

COMMAND: os_105
COMMAND TYPE: OS
COMMAND TITLE: NETWORK PING and /etc/hosts Collection
COMMAND DESCRIPTION: 
 Validate Localized Configuration Files
      Check if TNS_ADMIN and ORACLE_SID are set
      env|grep TNS_ADMIN
      env|grep ORACLE_SID
      Ensure that the listener.ora, tnsnames.ora, and sqlnet.ora files 
      exist in the correct locations:
      cat $ORACLE_HOME/network/admin/listener.ora
      cat $ORACLE_HOME/network/admin/tnsnames.ora
      cat $ORACLE_HOME/network/admin/sqlnet.ora
command_prompt1:
DBHOST|Please enter the Database Server Host name|dtplnx08
COMMAND ARGUMENT(S): 
 
    #!/bin/sh
    echo -e "\nSUMMARY STEP 6 of 7: " | tee -a /tmp/sqltest.txt
    ping $HOSTNAME -c 3  | tee -a /tmp/sqltest.txt 2>&1
    errorCode=$?
    if [ ${errorCode} -ne 0 ]; then
      echo -e "\nSUMMARY STEP 6 of 7: " | tee -a /tmp/sqltest.txt
      echo  "FAILURE! Report ERROR: ${errorCode} to DBA" | tee -a /tmp/sqltest.txt
      exit 1
    else
      echo -e "\nSUMMARY STEP 6 of 7: " | tee -a /tmp/sqltest.txt
      cat /etc/hosts | tee -a /tmp/sqltest.txt
      echo "BEGIN nestat -a -------------------- %%DBHOST%%"
      netstat -a
      echo  "SUCCESS! Network Pings were successfully tested" | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! PROCEED TO STEP 7" | tee -a /tmp/sqltest.txt
    fi
    
    # echo DA_FILE_COLLECTED=/tmp/sqltest.txt
Top|Back

COMMAND: os_106
COMMAND TYPE: OS
COMMAND TITLE: TRACE ASSISTANT Collection
COMMAND DESCRIPTION: 
 Validate Localized Configuration Files
      Check if TNS_ADMIN and ORACLE_SID are set
      env|grep TNS_ADMIN
      env|grep ORACLE_SID
      Ensure that the listener.ora, tnsnames.ora, and sqlnet.ora files 
      exist in the correct locations:
      cat $ORACLE_HOME/network/admin/listener.ora
      cat $ORACLE_HOME/network/admin/tnsnames.ora
      cat $ORACLE_HOME/network/admin/sqlnet.ora
command_prompt1:
TRACEFILE|Please enter the location of network tracefile|/rdbms/log/
COMMAND ARGUMENT(S): 
#!/bin/sh
     echo "BEGIN Detailed Net Services information -------------- %%TRACEFILE%%"
     trcasst -o %%TRACEFILE%%
     echo ""
     echo "BEGIN Error information ------------------------------ %%TRACEFILE%%"
     trcasst -o %%TRACEFILE%%
     echo ""
     echo "BEGIN Connection informati---------------------------- %%TRACEFILE%%"
     trcasst -i %%TRACEFILE%%
     echo ""
Top|Back

COMMAND: os_107
COMMAND TYPE: OS
COMMAND TITLE: All SQLNET Troubleshooting Collection
COMMAND DESCRIPTION: 
 Validate OS Network settings
      Ping and traceroute Database Server
      ping dmorldb02 -c 5
      
      The /etc/host file is used to isolate the IP address from the 
      tnsnames.ora file.
      cat /etc/hosts
      
      Active Internet connections (servers and established)
      netstat -a
COMMAND ARGUMENT(S): 
 
#!/bin/sh
SID=${ORACLE_SID}
sqlplus -s -l system/manager@$SID <<EOF
exit
EOF

    # echo "${MESSAGE}" >&1 | tee -a  ${File_name}
    errorCode=$?    # checks if the last operation (sqlplus) was completed successfully or not
    if [ ${errorCode} -ne 0 ]; then
      echo -e "\nSUMMARY STEP 1 of 7: " | tee -a /tmp/sqltest.txt
      echo "FAILURE! SQLPlus was unable to connect the DB with the supplied credentials" | tee -a /tmp/sqltest.txt
      echo "Check database is running or contact your DBA with Error ${errorCode}"| tee -a /tmp/sqltest.txt
      exit ${errorCode}
    else
      echo -e "\nSUMMARY STEP 1 of 7: " | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! SQLPlus was connected successfully" | tee -a /tmp/sqltest.txt
      echo  "PROCEED TO STEP 2" | tee -a /tmp/sqltest.txt
    fi
echo -e "\nEND S1 -----------------------------------------------------------------"     
sqlplus -s -l / as sysdba <<EOF
    
exit
EOF
    
    # echo "${MESSAGE}" >&1 | tee -a  ${File_name}
    errorCode=$?    # checks if the last operation (sqlplus) was completed successfully or not
    if [ ${errorCode} -ne 0 ]; then
      echo -e "\nSUMMARY STEP 2 of 7: " | tee -a /tmp/sqltest.txt
      echo "FAILURE! SQLPlus was unable to connect the DB." | tee -a /tmp/sqltest.txt
      echo "Check database is running or contact your DBA with Error ${errorCode}"| tee -a /tmp/sqltest.txt
      exit ${errorCode}
    else
      echo -e "\nSUMMARY STEP 2 of 7: " | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! SQLPlus was connected successfully" | tee -a /tmp/sqltest.txt
      echo  "PROCEED TO STEP 3" | tee -a /tmp/sqltest.txt
    fi
echo -e "\nEND S2 -----------------------------------------------------------------"       
lsnrctl<<EOF
status
exit
EOF
    errorCode=$?    # checks if the last operation (lsnrctl) was completed successfully or not
    if [ ${errorCode} -ne 0 ]; then
      echo -e "\nSUMMARY STEP 3 of 7: " | tee -a /tmp/sqltest.txt
      echo  "FAILURE! Listner is DOWN. ERROR  ${errorCode}" | tee -a /tmp/sqltest.txt
      echo  "Contact your DBA or refer: http://docs.oracle.com/cd/B19306_01/network.102/b14212/troublestng.htm#BABBBGGG"| tee -a /tmp/sqltest.txt
      exit ${errorCode}
    else
      echo -e "\nSUMMARY STEP 3 of 7: " | tee -a /tmp/sqltest.txt
      echo  "SUCCESS! Listner is UP" | tee -a /tmp/sqltest.txt
      echo  "PROCEED TO STEP 4" | tee -a /tmp/sqltest.txt
    fi
echo -e "\nEND S3 -----------------------------------------------------------------"   

  SIDNAME=V1121
  ORA_BIN=${ORACLE_HOME}/bin
  $ORA_BIN/tnsping $SIDNAME | tee -a /tmp/sqltest.txt 2>&1
  errorCode=$?
  if [ ${errorCode} -ne 0 ]; then
    echo -e "\nSUMMARY STEP 4 of 7: " | tee -a /tmp/sqltest.txt
    echo  "FAILURE! Report ERROR: ${errorCode} to DBA" | tee -a /tmp/sqltest.txt
    exit 1
  else
    echo -e "\nSUMMARY STEP 4 of 7: " | tee -a /tmp/sqltest.txt
    echo  "SUCCESS! SERVICE Names were successfully tested" | tee -a /tmp/sqltest.txt
    echo  "SUCCESS! PROCEED TO STEP 5" | tee -a /tmp/sqltest.txt
  fi
  # End of Script
echo -e "\nEND S4 -----------------------------------------------------------------"  


    # echo DA_FILE_COLLECTED=/tmp/sqltest.txt
Top|Back

COMMAND: rda_100
COMMAND TYPE: Rda
COMMAND TITLE: RDA OS, NET, ONET Modules
COMMAND DESCRIPTION: 
 Calling RDA with Moduules OS, NET, ONET
       S100OS - Collects the Operating System Information
       S122ONET - Collects Oracle Net Information
       S120NET - Collects Network Information
COMMAND ARGUMENT(S): 
run_module=OS,NET,ONET
Top|Back