kickcom
asked on
Standby Database - Configuring TNSNAMES.ORA & LISTENER.ORA Files
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/te
(SID_DESC = (GLOBAL_DBNAME = test)(ORACLE_HOME = (u01/app/oracle/product/8.
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/te
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.
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/te
(SID_DESC = (GLOBAL_DBNAME = test)(ORACLE_HOME = (u01/app/oracle/product/8.
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/te
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.
Looks like you need a port number specified somewhere in your standby listerner.ora...
I'm not sure why you have three listeners defined?
I'm not sure why you have three listeners defined?
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
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
ASKER
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.
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.
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)(HO
# (ADDRESS=(PROTOCOL=ipc)(KE
# 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.myc
# (SID_NAME=sid1)
# (ORACLE_HOME=/private/app/
# #PRESPAWN CONFIG
# (PRESPAWN_MAX=20)
# (PRESPAWN_LIST=
# (PRESPAWN_DESC=(PROTOCOL=t
# )
# )
# )
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.
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)(HO
# (ADDRESS=(PROTOCOL=ipc)(KE
# 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.myc
# (SID_NAME=sid1)
# (ORACLE_HOME=/private/app/
# #PRESPAWN CONFIG
# (PRESPAWN_MAX=20)
# (PRESPAWN_LIST=
# (PRESPAWN_DESC=(PROTOCOL=t
# )
# )
# )
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.
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.
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.
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
Refund points and save as a 0-pt PAQ.
DanRollins -- EE database cleanup volunteer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.