• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2470
  • Last Modified:

Cannot tnsping or connect with SQLNavigator

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 8.1.7.0.0 - 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.

0
bencouve2
Asked:
bencouve2
  • 3
  • 2
2 Solutions
 
Ivo StoykovCommented:
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.



HTH

I
0
 
bencouve2Author Commented:
Hello THT,

I have tried again with the fully qualified entry.

C:\>tnsping bo_test.css.eu.int

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

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

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

It seems to be picking up the entry in the tnsnames.ora. I have noticed that the Version 8.1.7.0.0 was previously a Version 9.2.....
0
 
Ivo StoykovCommented:
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).

regards

I
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Ivo StoykovCommented:
Hi bencouve2,

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

regards

I
0
 
schwertnerCommented:
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:

OFFLINE COLD BACKUP

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 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> select name from v$datafile;  

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\MAIN\SYSTEM01.DBF
C:\ORACLE\ORADATA\MAIN\UNDOTBS01.DBF
C:\ORACLE\LSDCONTACT00.DBF
C:\ORACLE\ORADATA\MAIN\TOOLS01.DBF
C:\ORACLE\ORADATA\MAIN\USERS01.DBF
C:\ORACLE\LSICONTACT00.DBF
C:\ORACLE\LSDDOCUMENT00.DBF
C:\ORACLE\LSIDOCUMENT00.DBF
C:\ORACLE\LSDIDMAP00.DBF
C:\ORACLE\LSIIDMAP00.DBF
C:\ORACLE\LSDMEDICAL00.DBF
C:\ORACLE\LSIMEDICAL00.DBF
C:\ORACLE\LSDSYSTEM00.DBF
C:\ORACLE\LSISYSTEM00.DBF

14 rows selected.

SQL> select member from v$logfile;  

MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\MAIN\REDO01.LOG
C:\ORACLE\ORADATA\MAIN\REDO02.LOG
C:\ORACLE\ORADATA\MAIN\REDO03.LOG

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
                                                               ata\main\control03.ctl


or

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


Run
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS <Path and Controlfilename>;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/u03/ctl_file’;

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.

The command ALTER DATABASE BACKUP CONTROLFILE TO
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

/u01/app/oracle/admin/oralin1/pfile/initorcf.ora
/u01/app/oracle/product/8.1.7/dbs/init.ora
/u01/app/oracle/product/8.1.7/dbs/initdw.ora
/u01/app/oracle/product/8.1.7/dbs/initorcf.ora
/u01/app/oracle/product/8.1.7/hs/admin/inithsodbc.ora
/u01/app/oracle/product/8.1.7/hs/admin/listener.ora
/u01/app/oracle/product/8.1.7/hs/admin/tnsnames.ora
/u01/app/oracle/product/8.1.7/network/admin/listener.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/cman.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/listener.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/names.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/sqlnet.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/tnsnames.ora
/u01/app/oracle/product/8.1.7/network/admin/sqlnet.ora
/u01/app/oracle/product/8.1.7/network/admin/tnsnames.ora

oracle@oralin1:~ > locate orapw
/u01/app/oracle/product/8.1.7/bin/orapwd
/u01/app/oracle/product/8.1.7/bin/orapwdO
/u01/app/oracle/product/8.1.7/dbs/orapworcf

To do this prepare a shell script like:
#!/bin/sh

# 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
subdirectories.

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.


0
 
bencouve2Author Commented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now