Cannot tnsping or connect with SQLNavigator

Posted on 2006-05-04
Last Modified: 2008-01-09
I have been unable to do an export from my PC and I have tried copying all the files in my colleges ORACLE_HOME into my oracle home, but it did not work. However, before doing the copy, I backedup all my files in my ORACLE_HOME. I am now unable to tnsping, I have deleted all files in my ORACLE_HOME and restored all the old files. However, I am still getting the same error during the tnsping. This error is listed below. No changes have been made to the tnsnames.ora, or sqlnet.ora so I am a little confused as to why this is happening. I have checked the environment variables and entries in the registry and it all seems to be fine.

C:\>tnsping the_database

TNS Ping Utility for 32-bit Windows: Version - Production on 04-MAY-20
06 11:11:02

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

TNS-03505: Message 3505 not found; No message file for product=NETWORK, facility=TNS

Any advice would be so welcome.

Question by:bencouve2
    LVL 22

    Expert Comment

    by:Ivo Stoykov
    Hello bencouve2,

    TNS-03505 means Failed to resolve name.

    The service name you provided could not be found in TNSNAMES.ORA, an Oracle Names server, or a native naming service.

    you should verify that you entered the service name correctly. Also ensure that the name was entered correctly into the network configuration.



    Author Comment

    Hello THT,

    I have tried again with the fully qualified entry.


    TNS Ping Utility for 32-bit Windows: Version - Production on 04-MAY-20
    06 11:51:17

    (c) Copyright 1997 Oracle Corporation.  All rights reserved.

    Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=
    TNS-12538: Message 12538 not found; No message file for product=NETWORK, facilit

    It seems to be picking up the entry in the tnsnames.ora. I have noticed that the Version was previously a Version 9.2.....
    LVL 22

    Expert Comment

    by:Ivo Stoykov
    Hi bencouve2,

    TNS-12538 means: no such protocol adapter

    The protocol adapter requested (by way of the "(PROTOCOL=..)"
    keyword-value pair in a TNS address) is unknown.

    If the supplied address is typographically correct then the protocol adaptor is not installed.

    I not installed - install the protocol adapter or correct the typographical error, as appropriate.

    Additionally if the supplied address was derived from resolving the service name,
    check the address in the appropriate file (TNSNAMES.ORA, LISTENER.ORA or TNSNET.ORA).


    LVL 22

    Assisted Solution

    by:Ivo Stoykov
    Hi bencouve2,

    Try to use Net Configuration Assistant to configure properly the *.ora settings...


    LVL 47

    Accepted Solution

    You have not copy the files from Oracle_Home.
    You have to copy mainly the files from Oracle_Base and some additional files.
    So the workaround is to install 8i from scratch and to do the  clonning correctly.
    As you can see clonning is not easy process - very often the DBA forget to transfer
    -control files
    -parameter file
    -password file

    What you have to clone:


    1.      Preparation :
    Obtain a list of the relevant files by querying V$DATAFILE, V$LOGFILE, V$CONTROLFILE and investigate all available redo log files. The connection should be made with sysdba privilege.

    Connected to:
    Oracle9i Enterprise Edition Release - Production
    With the Partitioning option
    JServer Release - Production

    SQL> select name from v$datafile;  


    14 rows selected.

    SQL> select member from v$logfile;  


    SQL> show parameter control_files;  

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    control_files                        string      C:\oracle\oradata\main\control
                                                                01.ctl, C:\oracle\oradata\main
                                                                 \control02.ctl, C:\oracle\orad


     SQL> SELECT value FROM v$parameter
    WHERE name = 'control_files';



    This will create a file with a copy of the physical structure of the database in case it is needed to recreate it at any time.

    TRACE provides a script to create the control file.
     In addition, the individual control files should also be backed-up by using the
    command ALTER DATABASE BACKUP CONTROLFILE to <filename>.
    This provides a binary copy of the control file at that time.

    Locate the relevant parameter files :
    init<sid>.ora file;        
    config<sid>.ora file;
    password file if used.
    Also locate and back up the initialization parameter file and other Oracle product initialization files. To find them, do a search for *.ora starting in your Oracle home directory and recursively search all of its subdirectories.

    Investigate where are all available redo log files:
    SQL> archive log list
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /u03/oradata/oralin1/arch
    Oldest online log sequence     30
    Next log sequence to archive   32
    Current log sequence           32

    Note: If you are forced to perform a restore operation, you must restore the control files to all locations specified in the parameter file. Consequently, it is better to make copies of each multiplexed control file—even if the control files are identical—to avoid problems at restore time.  

    Locate the initialization parameter file and other Oracle product initialization files. To find them, do a search for *.ora starting in your Oracle home directory and recursively search all of its subdirectories:
    root@oralin1:/ > locate *.ora


    oracle@oralin1:~ > locate orapw

    To do this prepare a shell script like:

    # Data files
    cp /u01/app/oracle/oradata/oralin1/*.dbf   /u03/oradata/oralin1/cold_bku/

    # Online log files
    cp /u01/app/oracle/oradata/oralin1/*.log   /u03/oradata/oralin1/cold_bku/

    #Control files
    cp /u01/app/oracle/oradata/oralin1/*.ctl   /u03/oradata/oralin1/cold_bku/

    #Archived log files
    cp /u03/oradata/oralin1/arch/*.arc         /u03/oradata/oralin1/cold_bku/

    # init<sid>.ora file
    cp /u01/app/oracle/admin/oralin1/pfile/initorcf.ora   /u03/oradata/oralin1/cold_bku/

    # NET ora files
    cp /u01/app/oracle/product/8.1.7/network/admin/*.ora  /u03/oradata/oralin1/cold_bku/

    # password file
    cp /u01/app/oracle/product/8.1.7/dbs/orapworcf  /u03/oradata/oralin1/cold_bku/

    Execution :  

    Use operating system commands or a backup utility to make backups of all
    datafiles and all control files specified by the CONTROL_FILES parameter of
    the initialization parameter file. Also back up the initialization parameter file
    and other Oracle product initialization files. To find them, do a search for
    *.ora starting in your Oracle home directory and recursively search all of its

    The database must be shut down cleanly (ie NORMAL, or IMMEDIATE).
    Do not make a whole database backup when the instance is aborted or stopped because of a failure. Reopen the database and shut it down cleanly first.  
    Take OS copies of the database files, redo log files, controlfiles and parameter files as specified above.  
    Consider taking a copy of any relevant archive logs.  
    After this is complete you may open the database again.  

    A backup strategy is useless if the database being backed up is corrupt to begin with. Oracle contains online utilities to verify the correctness of a database; these tools should be used prior to starting the database backup operation. No database backup operation should be allowed to proceed if the database has not been recently verified as consistent in all respects.
    It is recognized that it may not always be feasible to verify the integrity of the database before every database backup. However, an attempt should be made to verify the database integrity as often as operationally possible. For some details read section 2.6  of “Database Backup and Recovery Strategy”.

    Avoiding the Backup of Online Redo Logs
    Although it may seem that you should back up online redo logs along with the
    datafiles and control file, this technique is dangerous. You should not back up
    online redo logs for the following reasons:
    ·      The best method for protecting the online logs against media failure is by multiplexing them, that is, having multiple log members per group, on different disks and disk controllers.
    ·      If your database is in ARCHIVELOG mode, then ARCn is already archiving the redo logs.
    ·      If your database is in NOARCHIVELOG mode, then the only type of backups that you should perform are closed, consistent, whole database backups. The files in this type of backup are all consistent and do not need recovery, so the online logs are not needed.


    Author Comment

    Schwertner, that was overkill for the requiremenet, but you were right it required a re-install. Lots of useful info there for when I want to clone though, thanks for that. ivostovkov, thanks for the tips, all useful.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now