Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2001-07-02
8
Medium Priority
?
2,652 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

963 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