Solved

Cannot to new instance of Oracle

Posted on 2008-06-19
7
700 Views
Last Modified: 2013-12-19
We installed Oracle 9i on LINUX RedHat rel4, once we got past some package issues the database created without issues.  The problem we have is that we cannot connect to the database from anywhere other then the local server.  We can run sqlplus '/as sysdba' and access it or sqlplus system/abcd123@custdb1 and get to it, again only locally.  We can connect out from the server to other databases, we can ping the server, putty to it and all that.  Does anyone have any suggestions?  We are getting to major crunch time on this and we still cannot access the db.

We have checked everything we can think of.  We dropped the tnsnames, listener, sqlnet and rebuilt from other 9i sources and so on.  Is there anything in the OS maybe host entries that we need to look at?  

Thanks.
0
Comment
Question by:mcioffi209
  • 3
  • 3
7 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
Comment Utility
What happens when you try to access it from a PC over sqlnet?
0
 

Author Comment

by:mcioffi209
Comment Utility
sqlplus produces the below message from remote machines.  Same error message using client shipped with 9i, as you can see below the sqlplus version is from 10g.

X:\>sqlplus system/abcd123@custdb1

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 19 11:01:49 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:


0
 
LVL 16

Expert Comment

by:Milleniumaire
Comment Utility
I'm guessing tnsping gives the same error.

The TNS: protocol adapter error  high level error reporting that an error occurred in the transport layer.  Since the 12560 is the end result of another problem and not necessarily the cause look at the accompanying errors in the error stack.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:mcioffi209
Comment Utility
Ok, how do I do that?  I have not had to troubleshoot anything like this before.  Do you have any reccommendations for me about how to do that?
0
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 100 total points
Comment Utility
Here's a bunch of articles taken from Metalink:

Note:117976.1
ORA-12560: TROUBLESHOOTING
--------------------------

ORA-12560: TNS:protocol adapter error
Cause:  A generic protocol adapter error occurred.
Action: Check addresses used for proper protocol specification. Before
        reporting this error, look at the error stack and check for lower
level transport errors. For further details, turn on tracing and
re-execute the operation. Turn off tracing when the operation
        is complete.

This is a high level error reporting that an error occurred in the transport layer.
Since the 12560 is the end result of another problem and not necessarily the
cause look at the accompanying errors in the error stack. To get an  error
stack, a SQL*Net trace should be obtained.  Note 39774.1  describes how to
set up SQL*Net tracing. Once you have the trace, you should be able to locate
the other errors accompanying the 12560. Note 39662.1 shows the SQL*Net
components that take part during a connection.

Example of stack trace where a ORA-12560 appeared:

 -<ERROR>- nsres: id=0, op=13, ns=12545, ns2=12560; nt[0]=515, nt[1]=0,nt[2]=0
  Call failed...
  Exiting NRICALL with following termination result -1
  -<ERROR>- error from nricall
  -<ERROR>- nr err code: 12206
  -<ERROR>- ns main err code: 12545
  -<ERROR>- ns (2) err code: 12560
  -<ERROR>- nt main err code: 515
  -<ERROR>- nt (2) err code: 0
  -<ERROR>- nt OS err code: 0
  -<ERROR>- Couldn't connect, returning 12545

These lines show all of the errors that have been compiled up to the session
layer within SQL*Net. 'ns' and 'ns2' are the primary and secondary error codes
for the session layer. 'nt[]' is the error code given back by SQL*Nets
transport layer. Keep in mind that it is not the error code given by the
specific transport protocol below SQL*Net. The nt[0], nt[1] and nt[2] are the
primary, secondary, and tertiary error codes for the NT layer. The NT error
codes are not available through the Oracle UNIX 'oerr' tool, but are available
in the Oracle Network Products Troubleshooting Guide error documentation.

Note:39774.1
LOG AND TRACE FACILITIES ON SQL*NET
                      ====================================
 
This article describes the log and trace facilities that can be used to
examine application connections that use SQL*Net. This article is based on
usage of SQL*NET. It explains how to invoke the trace facility and how
to use the log and trace information to diagnose and resolve operating problems.
Following topics are covered below:

               o  What the log facility is

               o  What the trace facility is

               o  How to invoke the trace facility

               o  Logging and tracing parameters

               o  Sample log output

               o  Sample trace output

Note: Information in this section is generic to all operating system
      environments. You may require further information from the Oracle
      operating system-specific documentation for some details of your specific
      operating environment.


________________________________________

1. What is the Log Facility?
============================

All errors encountered in SQL*Net are logged to a log file for evaluation by a
network or database administrator. The log file provides additional information
for an administrator when the error on the screen is inadequate to understand
the failure. The log file, by way of the error stack, shows the state of the
TNS software at various layers. The properties of the log file are:

    o  Error information is appended to the log file when an error occurs.

    o  Generally, a log file can only be replaced or erased by an administrator,
        although client log files can be deleted by the user whose application
       created them. (Note that in general it is bad practice to delete these
       files while the program using them is still actively logging.)


    o  Logging of errors for the client, server, and listener cannot be
       disabled. This is an essential feature that ensures all errors are
       recorded.

    o  The Navigator and Connection Manager components of the MultiProtocol
       Interchange may have logging turned on or off. If on, logging includes
       connection statistics.

    o  The Names server may have logging turned on or off. If on, a Names
       server's operational events are written to a specified logfile. You set
       logging parameters using the Oracle Network Manager.


________________________________________
 

2. What is the Trace Facility?
==============================

The trace facility allows a network or database administrator to obtain more
information on the internal operations of the components of a TNS network
than is provided in a log file. Tracing an operation produces a detailed
sequence of statements that describe the events as they are executed. All
trace output is directed to trace output files which can be evaluated after
the failure to identify the events that lead up to an error. The trace
facility is typically invoked during the occurrence of an abnormal
condition, when the log file does not provide a clear indication of the
cause.

Attention: The trace facility uses a large amount of disk space and may have
           a significant impact upon system performance. Therefore, you are
           cautioned to turn the trace facility ON only as part of a diagnostic
           procedure and to turn it OFF promptly when it is no longer necessary.

Components that can be traced using the trace facility are:

    o  Network listener
    o  SQL*Net components
       -  SQL*Net client
       -  SQL*Net server
    o  MultiProtocol Interchange components
       -  the Connection Manager
       -  the Navigator
    o  Oracle Names
       -   Names server
       -  Names Control Utility

The trace facility can be used to identify the following types of problems:
    -  Difficulties in establishing connections
    -  Abnormal termination of established connections
    -  Fatal errors occurring during the operation of TNS network
       components


________________________________________


3. What is the Difference between Logging and Tracing?
======================================================

While logging provides the state of the TNS components at the time of an
error, tracing provides a description of all software events as they occur,
and therefore provides additional information about events prior to an
error. There are three levels of diagnostics, each providing more
information than the previous level. The three levels are:

  1. The reported error from Oracle or tools; this is the single error that
     is commonly returned to the user.

  2. The log file containing the state of TNS at the time of the error. This
     can often uncover low level errors in interaction with the underlying
     protocols.

  3. The trace file containing English statements describing what the TNS
     software has done from the time the trace session was initiated until the
     failure is recreated.


When an error occurs, a simple error message is displayed and a log file is
generated. Optionally, a trace file can be generated for more information.
(Remember, however, that using the trace facility has an impact on your
system performance.)

In the following example, the user failed to use Oracle Network Manager to
create a configuration file, and misspelled the word "PORT" as "POT" in the
connect descriptor. It is not important that you understand in detail the
contents of each of these results; this example is intended only to provide
a comparison.

Reported Error (On the screen in SQL*Forms):

        ERROR: ORA-12533: Unable to open message file (SQL-02113)

Logged Error (In the log file, SQLNET.LOG):

        ****************************************************************
        Fatal OSN connect error 12533, connecting to:
        (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)
        (USER=ginger)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)
        (KEY=bad_port))(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=1521))))

        VERSION INFORMATION:
        TNS for SunOS: Version 2.0.14.0.0 - Developer's Release
        Oracle Bequeath NT Protocol Adapter for SunOS: Version
        2.0.14.0.0 - Developer's Release
        Unix Domain Socket IPC NT Protocol Adaptor for SunOS: Version
        2.0.14.0.0 - Developer's Release
        TCP/IP NT Protocol Adapter for SunOS: Version 2.0.14.0.0 -
        Developer's Release
        Time: 07-MAY-93 17:38:50
        Tracing to file: /home/ginger/trace_admin.trc
        Tns error struct:
        nr err code: 12206
        TNS-12206: TNS:received a TNS error while doing navigation
        ns main err code: 12533
        TNS-12533: TNS:illegal ADDRESS parameters
        ns secondary err code: 12560
        nt main err code: 503
        TNS-00503: Illegal ADDRESS parameters
        nt secondary err code: 0
        nt OS err code: 0

Example of Trace of Error
-------------------------

The trace file, SQLNET.TRC at the USER level, contains the
following information:

        --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
        New trace stream is "/private1/oracle/trace_user.trc"
        New trace level is 4
        --- TRACE CONFIGURATION INFORMATION ENDS ---

        --- PARAMETER SOURCE INFORMATION FOLLOWS ---
        Attempted load of system pfile source
        /private1/oracle/network/admin/sqlnet.ora
        Parameter source was not loaded
        Error stack follows:
        NL-00405: cannot open parameter file

        Attempted load of local pfile source /home/ginger/.sqlnet.ora
        Parameter source loaded successfully

        -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
        Some parameters may not have been loaded
        See dump for parameters which loaded OK
        -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
        TRACE_DIRECTORY_CLIENT = /private1/oracle
        trace_level_client = USER
        TRACE_FILE_CLIENT = trace_user
        --- PARAMETER SOURCE INFORMATION ENDS ---

        --- LOG CONFIGURATION INFORMATION FOLLOWS ---
        Attempted open of log stream "/tmp_mnt/home/ginger/sqlnet.log"
        Successful stream open
        --- LOG CONFIGURATION INFORMATION ENDS ---

        Unable to get data from navigation file tnsnav.ora
        local names file is /home/ginger/.tnsnames.ora
        system names file is /etc/tnsnames.ora
        -<ERROR>- failure, error stack follows
        -<ERROR>- NL-00427: bad list
        -<ERROR>- NOTE: FILE CONTAINS ERRORS, SOME NAMES MAY BE MISSING

        Calling address:
        (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)(USER=ginger)))
        (ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=bad_port))(ADDRESS=(PROTOCOL=tcp)(HOST
        Getting local community information
        Looking for local addresses setup by nrigla
        No addresses in the preferred address list
        TNSNAV.ORA is not present. No local communities entry.
        Getting local address information
        Address list being processed...
        No community information so all addresses are "local"
        Resolving address to use to call destination or next hop
        Processing address list...
        No community entries so iterate over address list
        This a local community access
        Got routable address information
        Making call with following address information:
        (DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=ipc)(KEY=bad_port)))
        Calling with outgoing connect data
        (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)(USER=ginger)))
        (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=1521))))
        (DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=ipc)(KEY=bad_port)))
        KEY = bad_port
        connecting...
        opening transport...
        -<ERROR>- sd=8, op=1, resnt[0]=511, resnt[1]=2, resnt[2]=0
        -<ERROR>- unable to open transport
        -<ERROR>- nsres: id=0, op=1, ns=12541, ns2=12560; nt[0]=511, nt[1]=2,
        nt[2]=0
        connect attempt failed
        Call failed...
        Call made to destination
        Processing address list so continuing
        Getting local community information
        Looking for local addresses setup by nrigla
        No addresses in the preferred address list
        TNSNAV.ORA is not present. No local communities entry.
        Getting local address information
        Address list being processed...
        No community information so all addresses are "local"
        Resolving address to use to call destination or next hop
        Processing address list...
        No community entries so iterate over address list
        This a local community access
        Got routable address information
        Making call with following address information:
        (DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=1521)))
        Calling with outgoing connect data
        (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)(USER=ginger)))
        (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=521))))
        (DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=1521)))

        -<FATAL?>- failed to recognize: POT

        -<ERROR>- nsres: id=0, op=13, ns=12533, ns2=12560; nt[0]=503, nt[1]=0,
        nt[2]=0
        Call failed...
        Exiting NRICALL with following termination result -1
        -<ERROR>- error from nricall
        -<ERROR>- nr err code: 12206
        -<ERROR>- ns main err code: 12533
        -<ERROR>- ns (2) err code: 12560
        -<ERROR>- nt main err code: 503
        -<ERROR>- nt (2) err code: 0
        -<ERROR>- nt OS err code: 0
        -<ERROR>- Couldn't connect, returning 12533


In the trace file, note that unexpected events are preceded with an
-<ERROR>- stamp. These events may represent serious errors, minor errors, or
merely unexpected results from an internal operation. More serious and
probably fatal errors are stamped with the -<FATAL?>- prefix.

In this example trace file, you can see that the root problem, the
misspelling of "PORT," is indicated by the trace line: -<FATAL?>- failed to
recognize: POT

Most tracing is very similar to this. If you have a basic understanding of
the events the components will perform, you can identify the probable cause
of an error in the text of the trace.
________________________________________
 

4. Log File Names
=================

Log files produced by different components have unique names. The default
file names are:

 SQLNET.LOG           Contains client and/or server
                      information

 LISTENER.LOG         Contains listener information

 INTCHG.LOG           Contains Connection Manager and pump
                      information

 NAVGATR.LOG          Contains Navigator information

 NAMES.LOG            Contains Names server information

You can control the name of the log file. For each component, any valid
string can be used to create a log file name. The parameters are of the
form:

LOG_FILE_component = string

For example:

LOG_FILE_LISTENER = TEST

Some platforms have restrictions on the properties of a file name. See your
Oracle operating system specific manuals for platform specific restrictions.

_____________________________________

5. Using Log Files
==================

Follow these steps to track an error using a log file:

1. Browse the log file for the most recent error that matches the error
number you have received from the application. This is almost always the
last entry in the log file. Notice that an entry or error stack in the log
file is usually many lines in length. In the example earlier in this
chapter, the error number was 12207.

2. Starting at the bottom, look up to the first non-zero entry in the error
report. This is usually the actual cause. In the example earlier in this
chapter, the last non-zero entry is the "ns" error 12560.

3. Look up the first non-zero entry in later chapters of this book for its
recommended cause and action. (For example, you would find the "ns" error
12560 under ORA-12560.) To understand the notation used in the error report,
see the previous chapter, "Interpreting Error Messages."

4. If that error does not provide the desired information, move up the error
stack to the second to last error and so on.

5. If the cause of the error is still not clear, turn on tracing and
re-execute the statement that produced the error message. The use of the
trace facility is described in detail later in this chapter. Be sure to turn
tracing off after you have re-executed the command.

________________________________________
 

6. Using the Trace Facility
===========================

The steps used to invoke tracing are outlined here. Each step is fully
described in subsequent sections.

1. Choose the component to be traced from the list:

       o  Client
       o  Server
       o  Listener
       o  Connection Manager and pump (cmanager)
       o  Navigator (navigator)
       o  Names server
       o  Names Control Utility

2. Save existing trace file if you need to retain information on it. By default
   most trace files will overwrite an existing ones. TRACE_UNIQUE parameter needs
   to be included in appropriate config. files if unique trace files are required.
   This appends Process Id to each file.
   For Example:
       For Names server tracing, NAMES.TRACE_UNIQUE=ON needs to be set in NAMES.
       ORA file. For Names Control Utility, NAMESCTL.TRACING_UNIQUE=TRUE needs
       to be in SQLNET.ORA. TRACE_UNIQUE_CLIENT=ON in SQLNET.ORA for Client
       Tracing.


3. For any component, you can invoke the trace facility by editing the
   component configuration file that corresponds to the component traced. The
   component config. files are SQLNET.ORA, LISTENER.ORA, INTCHG.ORA, and NAMES.
   ORA.

4. Execute or start the component to be traced. If the trace component
   configuration files are modified while the component is running, the
   modified trace parameters will take effect the next time the component is
   invoked or restarted. Specifically for each component:


   CLIENT:   Set the trace parameters in the client-side SQLNET.ORA and invoke
             a client application, such as SQL*Plus, a Pro*C application, or
             any application that uses the Oracle network products.

   SERVER:   Set the trace parameters in the server-side SQLNET.ORA. The next
             process started by the listener will have tracing enabled. The
             trace parameters must be created or edited manually.


   LISTENER: Set the trace parameters in the LISTENER.ORA

   CONNECTION MANAGER:
             Set the trace parameters in INTCHG.ORA and start the Connection
             Manager from the Interchange Control Utility or command line. The
             pumps are started automatically with the Connection Manager, and
             their trace files are controlled by the trace parameters for the
             Connection Manager.

                 
   NAVIGATOR:Again, set the trace parameters in INTCHG.ORA and start the
             Navigator

   NAMES SERVER:  
             Trace parameters needs to be set in NAMES.ORA and start the Names
             server.
 
   NAMES CONTROL UTILITY:
             Set the trace parameters in SQLNET.ORA and start the Names Control
             Utility


5. Be sure to turn tracing off when you do not need it for a specific
   diagnostic purpose.

________________________________________
 

7. Setting Trace Parameters
===========================

The trace parameters are defined in the same configuration files as the log
parameters. Table below shows the configuration files for different network
components and the default names of the trace files they generate.


 --------------------------------------------------------
| Trace Parameters  | Configuration   |                  |
| Corresponding to  | File            | Output Files     |
|-------------------|-----------------|------------------|
|                   |                 |                  |
| Client            | SQLNET.ORA      | SQLNET.TRC       |
| Server            |                 | SQLNET.TRC       |      
| TNSPING Utility   |                 | TNSPING.TRC      |
| Names Control     |                 |                  |
|   Utility         |                 | NAMESCTL.TRC     |
|-------------------|-----------------|------------------|
| Listener          | LISTENER.ORA    | LISTENER.TRC     |
|-------------------|-----------------|------------------|
| Interchange       | INTCHG.ORA      |                  |
|   Connection      |                 |                  |
|     Manager       |                 | CMG.TRC          |
|   Pumps           |                 | PMP.TRC          |
|   Navigator       |                 | NAV.TRC          |
|-------------------|-----------------|------------------|
| Names server      | NAMES.ORA       | NAMES.TRC        |
|___________________|_________________|__________________|

The configuration files for each component are located on the computer
running that component.

The trace characteristics for two or more components of an Interchange are
controlled by different parameters in the same configuration file. For
example, there are separate sets of parameters for the Connection Manager
and the Navigator that determine which components will be traced, and at
what level.

Similarly, if there are multiple listeners on a single computer, each
listener is controlled by parameters that include the unique listener name
in the LISTENER.ORA file.

For each component, the configuration files contain the following
information:

         o  A valid trace level to be used (Default is OFF)
         o  The trace file name (optional)
         o  The trace file directory (optional)


________________________________________

 
7a. Valid SQLNET.ORA Diagnostic Parameters
==========================================

The SQLNET.ORA caters for:
         o Client Logging & Tracing
         o Server Logging & Tracing
         o TNSPING utility
         o NAMESCTL program


 ------------------------------------------------------------------------------
|                        |                |                                    |
| PARAMETERS             | VALUES         | Example (DOS client, UNIX server)  |
|                        |                |                                    |
|------------------------|----------------|------------------------------------|
|Parameters for Client                                                         |
|=====================                                                         |
|------------------------------------------------------------------------------|
|                        |                |                                    |
| TRACE_LEVEL_CLIENT     | OFF/USER/ADMIN | TRACE_LEVEL_CLIENT=USER            |
|                        |                |                                    |
| TRACE_FILE_CLIENT      | string         | TRACE_FILE_CLIENT=CLIENT           |
|                        |                |                                    |
| TRACE_DIRECTORY_CLIENT | valid directory| TRACE_DIRECTORY_CLIENT=c:\NET\ADMIN|
|                        |                |                                    |
| TRACE_UNIQUE_CLIENT    | OFF/ON         | TRACE_UNIQUE_CLIENT=ON             |
|                        |                |                                    |
| LOG_FILE_CLIENT        | string         | LOG_FILE_CLIENT=CLIENT             |
|                        |                |                                    |
| LOG_DIRECTORY_CLIENT   | valid directory| LOG_DIRECTORY_CLIENT=c:\NET\ADMIN  |
|------------------------------------------------------------------------------|
|Parameters for Server                                                         |
|=====================                                                         |
|------------------------------------------------------------------------------|
|                        |                |                                    |
| TRACE_LEVEL_SERVER     | OFF/USER/ADMIN | TRACE_LEVEL_SERVER=ADMIN           |
|                        |                |                                    |
| TRACE_FILE_SERVER      | string         | TRACE_FILE_SERVER=unixsrv_2345.trc |
|                        |                |                                    |
| TRACE_DIRECTORY_SERVER | valid directory| TRACE_DIRECTORY_SERVER=/tmp/trace  |
|                        |                |                                    |
| LOG_FILE_SERVER        | string         | LOG_FILE_SERVER=unixsrv.log        |
|                        |                |                                    |
| LOG_DIRECTORY_SERVER   | valid directory| LOG_DIRECTORY_SERVER=/tmp/trace    |
|------------------------------------------------------------------------------|

 
 ---(SQLNET.ORA Cont.)---------------------------------------------------------
|                        |                |                                    |
| PARAMETERS             | VALUES         | Example (DOS client, UNIX server)  |
|                        |                |                                    |
|------------------------|----------------|------------------------------------|
|
|Parameters for TNSPING                                                        |
|======================                                                        |
|------------------------------------------------------------------------------|
|                        |                |                                    |
| TNSPING.TRACE_LEVEL    | OFF/USER/ADMIN | TNSPING.TRACE_LEVEL=user           |
|                        |                |                                    |
| TNSPING.TRACE_DIRECTORY| directory      |TNSPING.TRACE_DIRECTORY=            |
|                        |                |             /oracle7/network/trace |
|                        |                |                                    |
|------------------------------------------------------------------------------|
|Parameters for Names Control Utility                                          |
|====================================                                          |
|------------------------------------------------------------------------------|
|                        |                |                                    |
| NAMESCTL.TRACE_LEVEL   | OFF/USER/ADMIN |NAMESCTL.TRACE_LEVEL=user           |
|                        |                |                                    |
| NAMESCTL.TRACE_FILE    | file           |NAMESCTL.TRACE_FILE=nc_south.trc    |
|                        |                |                                    |
| NAMESCTL.TRACE_DIRECTORY| directory     |NAMESCTL.TRACE_DIRECTORY=/o7/net/trace|
|                        |                |                                    |
| NAMESCTL.TRACE_UNIQUE  |  TRUE/FALSE    |NAMESCTL.TRACE_UNIQUE=TRUE or ON/OFF|
|                        |                |                                    |
 ------------------------------------------------------------------------------


Note: You control log and trace parameters for the client through Oracle
      Network Manager. You control log and trace parameters for the server by
      manually adding the desired parameters to the SQLNET.ORA file.

      Parameters for Names Control Utility & TNSPING Utility need to be added
      manually to SQLNET.ORA file. You cannot create them using Oracle Network Manager.


________________________________________
 

7b. Valid LISTENER.ORA Diagnostic Parameters
============================================

The following table shows the valid LISTENER.ORA parameters used in logging
and tracing of the listener.

 ------------------------------------------------------------------------------
|                        |                |                                    |
| PARAMETERS             | VALUES         | Example (DOS client, UNIX server)  |
|                        |                |                                    |
|------------------------|----------------|------------------------------------|
|                        |                |                                    |
|TRACE_LEVEL_LISTENER    | USER           | TRACE_LEVEL_LISTENER=OFF           |
|                        |                |                                    |
|TRACE_FILE_LISTENER     | string         | TRACE_FILE_LISTENER=LISTENER       |
|                        |                |                                    |
|TRACE_DIRECTORY_LISTENER| valid directory| TRACE_DIRECTORY_LISTENER=$ORA_SQLNETV2 |
|                        |                |                                    |      
|LOG_FILE_LISTENER       | string         | LOG_FILE_LISTENER=LISTENER         |
|                        |                |                                    |    
|LOG_DIRECTORY_LISTENER  | valid directory| LOG_DIRECTORY_LISTENER=$ORA_ERRORS |
|                        |                |                                    |    
 ------------------------------------------------------------------------------

________________________________________
 

7c. Valid INTCHG.ORA Diagnostic Parameters
==========================================

The following table shows the valid INTCHG.ORA parameters used in logging
and tracing of the Interchange.


 ----------------------------------------------------------------------------------
|                        |                    |                                    |
| PARAMETERS             | VALUES             | Example (DOS client, UNIX server)  |
|                        |           (default)|                                    |
|------------------------|--------------------|------------------------------------|
|                        |                    |                                    |
|TRACE_LEVEL_CMANAGER    | OFF|USER|ADMIN     | TRACE_LEVEL_CMANAGER=USER          |
|                        |                    |                                    |
|TRACE_FILE_CMANAGER     | string (CMG.TRC)   | TRACE_FILE_CMANAGER=CMANAGER       |
|                        |                    |                                    |
|TRACE_DIRECTORY_CMANAGER| valid directory    | TRACE_DIRECTORY_CMANAGER=C:\ADMIN  |
|                        |                    |                                    |
|LOG_FILE_CMANAGER       | string (INTCHG.LOG)| LOG_FILE_CMANAGER=CMANAGER         |
|                        |                    |                                    |
|LOG_DIRECTORY_CMANAGER  | valid directory    | LOG_DIRECTORY_CMANAGER=C:\ADMIN    |
|                        |                    |                                    |
|LOGGING_CMANAGER        | OFF/ON             | LOGGING_CMANAGER=ON                |
|                        |                    |                                    |
|LOG_INTERVAL_CMANAGER   | Any no of minutes  | LOG_INTERVAL_CMANAGER=60           |
|                        |        (60 minutes)|                                    |
|TRACE_LEVEL_NAVIGATOR   | OFF/USER/ADMIN     | TRACE_LEVEL_NAVIGATOR=ADMIN        |
|                        |                    |                                    |
|TRACE_FILE_NAVIGATOR    | string    (NAV.TRC)| TRACE_FILE_NAVIGATOR=NAVIGATOR     |
|                        |                    |                                    |
|TRACE_DIRECTORY_NAVIGATOR| valid directory   | TRACE_DIRECTORY_NAVIGATOR=C:\ADMIN |
|                        |                    |                                    |
|LOG_FILE_NAVIGATOR      |string (NAVGATR.LOG)| LOG_FILE_NAVIGATOR=NAVIGATOR       |
|                        |                    |                                    |
|LOG_DIRECTORY_NAVIGATOR | valid directory    | LOG_DIRECTORY_NAVIGATOR=C:\ADMIN   |
|                        |                    |                                    |
|LOGGING_NAVIGATOR       | OFF/ON             | LOGGING_NAVIGATOR=OFF              |
|                        |                    |                                    |
|LOG_LEVEL_NAVIGATOR     | ERRORS|ALL (ERRORS)| LOG_LEVEL_NAVIGATOR=ERRORS         |
|                        |                    |                                    |
 ----------------------------------------------------------------------------------

  Note: The pump component shares the trace parameters of the Connection
        Manager, but it generates a separate trace file with the unchangeable
        default name PMPpid.TRC.

________________________________________
 
7d. Valid NAMES.ORA Diagnostic Parameters
=========================================

The following table shows the valid NAMES.ORA parameters used in logging and
tracing of the Names server.


 ------------------------------------------------------------------------------
|                        |                |                                    |
| PARAMETERS             | VALUES         | Example (DOS client, UNIX server)  |
|                        |       (default)|                                    |
|------------------------|----------------|------------------------------------|
|                        |                |                                    |
| NAMES.TRACE_LEVEL      | OFF/USER/ADMIN | NAMES.TRACE_LEVEL=ADMIN            |
|                        |                |                                    |
| NAMES.TRACE_FILE       | file(names.trc)| NAMES.TRACE_FILE=nsrv3.trc         |
|                        |                |                                    |
| NAMES.TRACE_DIRECTORY  | directory      | NAMES.TRACE_DIRECTORY=/o7/net/trace|
|                        |                |                                    |
| NAMES.TRACE_UNIQUE     | TRUE/FALSE     | NAMES.TRACE_UNIQUE=TRUE  or ON/OFF |
|                        |                |                                    |  
| NAMES.LOG_FILE         | file(names.log)| NAMES.LOG_FILE=nsrv1.log           |
|                        |                |                                    |  
| NAMES.LOG_DIRECTORY    | directory      | NAMES.LOG_DIRECTORY= /o7/net/log   |
|                        |                |                                    |    
 ------------------------------------------------------------------------------
                     

________________________________________
 
8. Example of a Trace File
===========================
In the following example, the SQLNET.ORA file includes the following line:

                TRACE_LEVEL_CLIENT = ADMIN

The following trace file is the result of a connection attempt that failed
because the hostname is invalid.

The trace output is a combination of debugging aids for Oracle specialists
and English information for network administrators. Several key events can
be seen by analyzing this output from beginning to end:

        (A)  The client describes the outgoing data in the connect
             descriptor used to contact the server.

        (B)  An event is received (connection request).

        (C)  A connection is established over the available transport
             (in this case TCP/IP).

        (D)  The connection is refused by the application, which is the
             listener.

        (E)  The trace file shows the problem, as follows:
               
                -<FATAL?>- ***hostname lookup failure! ***

        (F)  Error 12545 is reported back to the client.

If you look up Error 12545 in Chapter 3 of this Manual, you will find the
following description:

        ORA-12545 TNS:Name lookup failure

        Cause:  A protocol specific ADDRESS parameter cannot be resolved.
        Action: Ensure the ADDRESS parameters have been entered correctly;
                the most likely incorrect value is the node name.


++++++ NOTE: TRACE FILE EXTRACT +++++++

        --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
        New trace stream is "/private1/oracle/trace_admin.trc"
        New trace level is 6
        --- TRACE CONFIGURATION INFORMATION ENDS ---

++++++ NOTE: Loading Parameter files now. +++++++

        --- PARAMETER SOURCE INFORMATION FOLLOWS ---
        Attempted load of system pfile source
        /private1/oracle/network/admin/sqlnet.ora
        Parameter source was not loaded
        Error stack follows:
        NL-00405: cannot open parameter file

        Attempted load of local pfile source /home/ginger/.sqlnet.ora
        Parameter source loaded successfully

        -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
        Some parameters may not have been loaded
        See dump for parameters which loaded OK
        -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
        TRACE_DIRECTORY_CLIENT = /private1/oracle
        trace_level_client = ADMIN
        TRACE_FILE_CLIENT = trace_admin
        --- PARAMETER SOURCE INFORMATION ENDS ---

++++++ NOTE: Reading Parameter files. +++++++

        --- LOG CONFIGURATION INFORMATION FOLLOWS ---
        Attempted open of log stream "/private1/oracle/sqlnet.log"
        Successful stream open
        --- LOG CONFIGURATION INFORMATION ENDS ---

        Unable to get data from navigation file
        tnsnav.ora
        local names file is /home/ginger/.tnsnames.ora
        system names file is /etc/tnsnames.ora
        initial retry timeout for all servers is 500 csecs
        max request retries per server is 2
        default zone is [root]
        Using nncin2a() to build connect descriptor for (possibly remote) database.
        initial load of /home/ginger/.tnsnames.ora
        -<ERROR>- failure, error stack follows
        -<ERROR>- NL-00405: cannot open parameter file
        -<ERROR>- NOTE: FILE CONTAINS ERRORS, SOME NAMES MAY BE MISSING

        initial load of /etc/tnsnames.ora
        -<ERROR>- failure, error stack follows
        -<ERROR>- NL-00427: bad list
        -<ERROR>- NOTE: FILE CONTAINS ERRORS, SOME NAMES MAY BE MISSING

        Inserting IPC address into connect descriptor returned from nncin2a().


++++++ NOTE: Looking for Routing Information. +++++++

        Calling address:
        (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)
        (USER=ginger)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc
        (KEY=bad_host))(ADDRESS=(PROTOCOL=tcp)(HOST=lavender)
        (PORT=1521))))
        Getting local community information
        Looking for local addresses setup by nrigla
        No addresses in the preferred address list
        TNSNAV.ORA is not present. No local communities entry.
        Getting local address information
        Address list being processed...
        No community information so all addresses are "local"
        Resolving address to use to call destination or next hop
        Processing address list...
        No community entries so iterate over address list
        This a local community access
        Got routable address information


++++++ NOTE: Calling first address (IPC). +++++++

        Making call with following address information:
        (DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=ipc)(KEY=bad_host)))
        Calling with outgoing connect data
        (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)
        (USER=ginger)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
        (HOST=lavender)(PORT=1521))))
        (DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=ipc)(KEY=bad_host)))
        KEY = bad_host
        connecting...
        opening transport...
        -<ERROR>- sd=8, op=1, resnt[0]=511, resnt[1]=2, resnt[2]=0
        -<ERROR>- unable to open transport
        -<ERROR>- nsres: id=0, op=1, ns=12541, ns2=12560; nt[0]=511, nt[1]=2,
        nt[2]=0
        connect attempt failed
        Call failed...
        Call made to destination
        Processing address list so continuing


++++++ NOTE: Looking for Routing Information. +++++++

        Getting local community information
        Looking for local addresses setup by nrigla
        No addresses in the preferred address list
        TNSNAV.ORA is not present. No local communities entry.
        Getting local address information
        Address list being processed...
        No community information so all addresses are "local"
        Resolving address to use to call destination or next hop
        Processing address list...
        No community entries so iterate over address list
        This a local community access
        Got routable address information


++++++ NOTE: Calling second address (TCP/IP). +++++++

        Making call with following address information:
        (DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=tcp)
        (HOST=lavender)(PORT=1521)))
        Calling with outgoing connect data
        (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)
        (USER=ginger)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
        (HOST=lavender) (PORT=1521))))
        (DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=tcp)
        (HOST=lavender)(PORT=1521)))
        port resolved to 1521
        looking up IP addr for host: lavender

-<FATAL?>- *** hostname lookup failure! ***

        -<ERROR>- nsres: id=0, op=13, ns=12545, ns2=12560; nt[0]=515, nt[1]=0,
        nt[2]=0
        Call failed...
        Exiting NRICALL with following termination result -1
        -<ERROR>- error from nricall
        -<ERROR>- nr err code: 12206
        -<ERROR>- ns main err code: 12545
        -<ERROR>- ns (2) err code: 12560
        -<ERROR>- nt main err code: 515
        -<ERROR>- nt (2) err code: 0
        -<ERROR>- nt OS err code: 0
        -<ERROR>- Couldn't connect, returning 12545

Most tracing is very similar to this. If you have a basic understanding of
the events the components will perform, you can identify the probable cause
of an error in the text of the trace.

Note:39662.1
Network Products and Error Stack Components
===========================================

Oracle network product error messages are listed in the ORACLE NETWORK
PRODUCTS MESSAGES manual. All error messages are identified by error message
numbers. The error messages are organized in ascending numerical order and are
separated into sections based on the error message prefix. Within each section,
the error messages are organized into subsections based on the component that
reported the error. The prefixes, message numbers, and the product components
where they are generated are shown below:

  ===================================================================
 | PREFIX | ERROR NUMBER |    COMPONENT     |    TYPE OF ERROR       |
 |========|==============|==================|========================|
 |  TNS   | 1 to 500     | NR (routing)     | MultiProtocol          |
 |        |              |                  | Interchange (MPI)      |
 |--------|--------------|------------------|------------------------|
 |  TNS   | 501 to 1000  | NT (transport)   | Protocol Adapter       |
 |--------|--------------|------------------|------------------------|
 |  TNS   | 1001 to 2500 | Listener Control | Listener Control       |
 |        |              |                  |  Program (LSNRCTL)     |
 |--------|--------------|------------------|------------------------|
 |  TNS   | 2501 to 3500 | NA Internal      | NAU (Authentication)   |
 |        |              | messages         | and NAE (Encryption)   |
 |--------|--------------|------------------|------------------------|
 | ORA/TNS|12150 to 12195| SQL*Net          | Oracle SQL*Net         |
 |--------|--------------|------------------|------------------------|
 | ORA/TNS|12196 to 12285| NR (routing)     | TNS                    |
 |--------|--------------|------------------|------------------------|
 | ORA/TNS|12500 to 12530| Listener         | Listener to Client     |
 |--------|--------------|------------------|------------------------|
 | ORA/TNS|12531 to 12629| NS (session)     | TNS                    |
 |--------|--------------|------------------|------------------------|
 | ORA/TNS|12630 to 12699| NA (Native       | Native Services        |
 |        |              |     Services)    |  (Authentication       |
 |        |              |                  |   and Encryption)      |
 |--------|--------------|------------------|------------------------|
 | NMC    |00001 to 11000| Network Manager  | Oracle Network Manager |
 |--------|--------------|------------------|------------------------|
 | NMO    |01001 to 01300| Object Layer     | Oracle Network Manager |
 |        |              |                  |  Object Store          |
 |--------|--------------|------------------|------------------------|
 | NMR    |00001 to 00500| Resource         | Oracle Network Manager |
 |        |              | Manager Layer    |                        |
 |--------|--------------|------------------|------------------------|
 | NNO    |00050 to 00711| NN (Network      | Oracle Names Server    |
 |        |              |     Naming)      |                        |
 |--------|--------------|------------------|------------------------|
 | NNC    |00001 to 00501| NN (Network      | Oracle Names           |
 |        |              |     Naming)      |   client and server    |
 |--------|--------------|------------------|------------------------|
 | NNL    |00001 to 01073| NN (Network      | Oracle Names Control   |
 |        |              |     Naming)      |  utility               |
 |--------|--------------|------------------|------------------------|  
 | NMP    |00001 to 00011| NMP (Network     | Oracle Names           |
 |        |              |      Management  |   client and server    |
 |        |              |      Protocol    |                        |
 |--------|--------------|------------------|------------------------|
 | NPL    |00100 to 00420| NPL (Network     | Network Presentation   |
 |        |              |   Presentation   |   Layer                |
 |        |              |   Layer          |                        |
 |--------|--------------|------------------|------------------------|
 | NMS    |00001 to 00275| NMS (Oracle      | Oracle SNMP Support    |
 |        |              |   SNMP Support)  |                        |
 |--------|--------------|------------------|------------------------|
 | NNF    |00001 to 04999| NNF (Native      | Native Naming Adapters |
 |        |              | Naming Adapters  |                        |
  ===================================================================




0
 
LVL 2

Expert Comment

by:vorb1s
Comment Utility
Hi,
  to try, if you have problems with tnsnames definitions, try login like this:
sqlplus username/password@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.x.xxx)(PORT=1521)))(CONNECT_DATA=(SID=instance_sid_name)))'

of course you can try functioning of your listener with TNSPING command like this:
tnsping custdb1

then I suppose is normal, that you forgot to delete sqlnet.ora from your NETWORK/ADMIN :-)
for testing purposes it is good, for production, you'll find out how it works :-)

Hope it'll work
Vorbis
0
 

Accepted Solution

by:
mcioffi209 earned 0 total points
Comment Utility
Turns out that the RedHat firewall was installed and running.  I ran system-config-securitylevel  and disabled the firewall and everything was fine.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now