Solved

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

Posted on 2001-07-02
8
2,618 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
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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
The purpose of an index when the key is unique 10 89
Stay Alert! 13 63
Space Delimited Sql File 4 70
Dataware house query tuning 9 33
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

760 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

22 Experts available now in Live!

Get 1:1 Help Now