Solved

Standby Database - Configuring TNSNAMES.ORA & LISTENER.ORA Files

Posted on 2001-07-02
8
2,628 Views
Last Modified: 2007-11-27
I am using 32-bit Oracle 8.1.6.2.0 for both the Primary and Standby databases and each database is on a separate host/server.  Both hosts are running the same version of the operating system.

After following Chapter 5 "Oracle8i Standby Database Concepts and Administration Release 2 (8.1.6)" documentation on setting up the network files for the Primary and Standby databases, I am unable to establish a connection between the two using the "sqlplus test/test@cab32s.zenith" on the Primary database.

Here are the statistics:

Primary database SID = CAB32P
Primary database host = ALPHA

Standby database SID = CAB32P
Standby database host = ZENITH

(The Standby database SID is CAB32P since this is the actual SID of the Standby database, internally and in the Standby database's INITCAB32S.ORA file ("S" for Standby.))  In addition, every Oracle Net8 Listener connection between all 32-bit Oracle 8.1.6.2.0 servers is being performed successfully via port 1521.

To test the connectivity, the following has been performed:

-- Primary database is open and the Standby database is mounted with the "alter database mount standby database;" statement.

-- Primary and Standby database Listeners have been shutdown and started for the changes to the TNSNAMES.ORA and/or LISTENER.ORA files to take effect, respectively.

Once this problem is corrected, I can activate the following INIT.ORA parameter in the Primary database:

log_archive_dest_2 = 'service=cab32s.zenith optional reopen=30'



PRIMARY DATABASE - TNSNAMES.ORA File
====================================

cab32s.zenith = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp) (HOST = zenith)(PORT = 1521)) (CONNECT_DATA = (SID = cab32p)))


STANDBY DATABASE - LISTENER.ORA File
====================================

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/test)(PROGRAM = extproc))
(SID_DESC = (GLOBAL_DBNAME = test)(ORACLE_HOME = (u01/app/oracle/product/8.1.6-64)(SID_NAME = test)))

STBY1_LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(KEY = cab32p)))

SID_LIST_STBY1_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = cab32p) (ORACLE_HOME = /u01/app/oracle/product/test)))


ORACLE ERROR WHEN "sqlplus test/test@cab32s.zenith"
IS EXECUTED FROM PRIMARY DATABASE
===================================================

ERROR:
ORA-12154: TNS:could not resolve service name



Thank you in advance for all feedback on this.
0
Comment
Question by:kickcom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 2

Expert Comment

by:santoshmathew
ID: 6246093
Hi,

When you get a ora-12514, there are a couple of checks to do which might give us more info on that.

1. First check the connectivity using tnsping with the connect string. If this succeeds then you are able to contact the listener on the standby. If it errors out, then you have to check the connect string entry for its correctness.
2. invoke the listener on the standby box and check whether it is configured to route the calls to the standby. You can use the "services <listener-name>" in the lsnrctl utility.

Cheers,
Santosh.
0
 
LVL 2

Expert Comment

by:banicki
ID: 6246117
Looks like you need a port number specified somewhere in your standby listerner.ora...
I'm not sure why you have three listeners defined?
0
 
LVL 1

Expert Comment

by:ashokskumar
ID: 6246462
What is status of listener at Standby site.

Check the listener at standby site with "lsnrctl status"
and tnsnames service with "tnsping <service name>

I don't see any requirment to provide many parameters like GLOBAL_NAME

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:kickcom
ID: 6246499
Connectivity via TNSPING with the connect string works as expected.  I also used LSNRCTL SERVICES to confirm all services on the Primary and Standby databases, and verified that the Primary and Standby hosts/server ports were configured and active.

I also reviewed the documentation on the GLOBAL_NAME parameter for Standby databases as well as Oracle Net8 documentation and it seems it is not a required parameter.

I also carefully reviewed the INIT.ORA files for both the Primary and Standby databases to discover that the Standby database INIT.ORA's SERVICE_NAMES parameter was set to a value of CAB32S instead of CAB32S.ZENITH as it is the service name on the Primary database TNSNAMES.ORA file.  After updating the Standby database INIT.ORA file with the correct SERVICE_NAMES value, the Primary database archived redo log files were being automatically transmitted to the Standby database.

Thanks to all who replied.
0
 

Expert Comment

by:nikhilnk
ID: 6247546
hi,

As Expert Banicki has suggested I am also confused about your listener.ora file, make sure you have posted the correct file.

line from your listener.ora file
----------------------------
STBY1_LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(KEY = cab32p)))
-----------------------------

While using TCP as protocol, generally we defined PORT no ie. 1521 or 1526 ...., but your file says key. KEY parameter is used only with IPC protocol.

Apart from PORT, you need to mention HOST parameter for HOSTNAME ie. zenith in your case.
For your reference see below sample listener.ora

# LISTENER =
#  (ADDRESS_LIST=
#       (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
#       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

# SID_LIST_<lsnr>
#   List of services the listener knows about and can connect
#   clients to.  There is no default.  See the Net8 Administrator's
#   Guide for more information.
#
# SID_LIST_LISTENER=
#   (SID_LIST=
#       (SID_DESC=
#                       #BEQUEATH CONFIG
#          (GLOBAL_DBNAME=salesdb.mycompany)
#          (SID_NAME=sid1)
#          (ORACLE_HOME=/private/app/oracle/product/8.0.3)
#                       #PRESPAWN CONFIG
#         (PRESPAWN_MAX=20)
#         (PRESPAWN_LIST=
#           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
#         )
#        )
#       )      

Since, you are using Oracle 8i version, you will able to tnsping, since your tnsnames.ora is properly configured and also Auto SID registration feature.


0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7038047
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7052582
Lacking timely response from kickcom or contributing experts, but seeing that there is some useful info here, I recommend:
    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer
0
 
LVL 6

Accepted Solution

by:
Mindphaser earned 0 total points
ID: 7052789
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

751 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