Hi Andrew,
I believe we don't have to put any entry in tnsnames.ora for asm , we need
to update tnsnames.ora for db home only for all the nodes using the same
tnsnames.ora, kindly correct me if I am not wrong . .
I now added a static registration for rac service in standby listener as
below :-
(SID_DESC =
(GLOBAL_DBNAME = rac)
(ORACLE_HOME = /u01/app/11.2.0/grid)
(SID_NAME = rac)
)
)
Now listener status is as below :-
oracle@racattack1 trace]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-FEB-2017 08:42:02
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 20-FEB-2017 08:39:13
Uptime 0 days 0 hr. 2 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File
/u01/app/oracle/diag/tnslsnr/racattack1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.53)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.63)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "rac" has 1 instance(s).
Instance "rac", status UNKNOWN, has 1 handler(s) for this service...
Service "rac.racattack" has 1 instance(s).
Instance "rac1", status READY, has 1 handler(s) for this service...
Service "rac1_DGMGRL" has 1 instance(s).
Instance "rac", status UNKNOWN, has 1 handler(s) for this service...
Service "racXDB.racattack" has 1 instance(s).
Instance "rac1", status READY, has 1 handler(s) for this service...
Service "rac_DGB.racattack" has 1 instance(s).
Instance "rac1", status READY, has 1 handler(s) for this service...
The command completed successfully
Now I can connect to rac services as below by tnsping from primary as below
but not able to do remote connectivity :-
[oracle@racattackstandby admin]$ tnsping rac
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 20-FEB-2017
08:39:50
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = racattack-cluster-scan)(PORT = 1521))) (CONNECT_DATA =
(SERVICE_NAME = rac)))
OK (10 msec)
[oracle@racattackstandby admin]$ sqlplus sys/oracle@rac
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 20 08:42:33 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in
connect
descriptor
Can you kindly assist.I have already shared tnsnames.ora files in my
earlier mail for DB .
Best regards,
Sandy
On Mon, Feb 20, 2017 at 8:29 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:
The only rac service I see is rac.racattack. Is this simply a domain
issue? Can you tnsping and connect to the rac service? Also, make sure
the database and asm tnsnames files are the same.
Sent from my iPad
On Feb 19, 2017, at 8:51 PM, Sandip Mukhopadhyay <
sandip.mukhopadhyay24@xxxxxxxxx> wrote:
Hi All,
DGMGRL> show database rac statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL> show database standby statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
standby ERROR ORA-16737: the redo transport service for
standby database 'rac' has an error
-------------------Broker log at primary---------
Redo transport problem detected: redo transport for database rac has the
following error:
ORA-12514: TNS:listener does not currently know of service requested in
connect descriptor
Data Guard Broker Status Summary:
Type Name Severity
Status
Configuration haconfig Warning
ORA-16607
Primary Database standby Error
ORA-16778
Physical Standby Database rac Success
ORA-00000
02/20/2017 07:37:46
Redo transport problem detected: redo transport for database rac has the
following error:
ORA-12514: TNS:listener does not currently know of service requested in
connect descriptor
Data Guard Broker Status Summary:
Type Name Severity
Status
Configuration haconfig Warning
ORA-16607
Primary Database standby Error
ORA-16778
Physical Standby Database rac Success
ORA-00000
02/20/2017 07:38:45
Redo transport problem detected: redo transport for database rac has the
following error:
ORA-12514: TNS:listener does not currently know of service requested in
connect descriptor
Data Guard Broker Status Summary:
Type Name Severity
Status
Configuration haconfig Warning
ORA-16607
Primary Database standby Error
ORA-16778
Physical Standby Database rac Success
ORA-00000
------------------------------Broker Log at Standby
--------------------------------
Broker Configuration File Locations:Starting Data Guard Broker bootstrap <<
dg_broker_config_file1 = "+DATA/rac/dr1rac1.dat"
dg_broker_config_file2 = "+DATA/rac/dr1rac2.dat"
02/19/2017 20:16:33
DMON Registering service rac_DGB with listener(s)
Broker Configuration: "haconfig"
Protection Mode: Maximum Performance
Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0
Primary Database: standby (0x02010000)
Standby Database: rac, Enabled Physical Standby
(0x01010000)
02/19/2017 20:16:37
Site standby returned ORA-16525.
database rac unable to contact primary database for version check; status
ORA-16525
completing bootstrap of this database
Creating process RSM0
02/19/2017 21:11:41
Failed to connect to remote database standby. Error is ORA-01034
Failed to send message to site standby. Error code is ORA-01034.
02/19/2017 21:26:23
-------------------- Dest Status in primary ---------------------------
DEST_NAME
DEST_ID ERROR
STATUS
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
----------------------------------------------------------------------------
---------- -----------------------------------------------------------------
---------
LOG_ARCHIVE_DEST_1 1
VALID
LOG_ARCHIVE_DEST_2 2 ORA-12514: TNS:listener does not
currently know of service ERROR
requested in
connect descriptor
------------------------------------------------------------
------------------------------------------------------------
-----------------------------------------
Dest values in primary
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
log_archive_dest_1 string LOCATION=+FRA
valid_for=(ALL_L
OGFILES,ALL_ROLES)
db_unique_n
ame=standby
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
log_archive_dest_2 string service="rac", LGWR ASYNC
NOAF
FIRM delay=0 optional
compress
ion=disable max_failure=0
max_
connections=1 reopen=300
db_un
ique_name="rac"
net_timeout=30
,
valid_for=(all_logfiles,prim
ary_role)
After switchover ,Unique Name of the primary database is standby,Unique
name of the standby database is raac,now standby is 2 node rac with
instances name rac1 and rac2
DGMGRL> show database standby
Database - standby
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
standby
Error: ORA-16737: the redo transport service for standby database
"rac" has an error
Database Status:
ERROR
DGMGRL> show database rac
Database - rac
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: ON
Instance(s):
rac1 (apply instance)
rac2
Database Status:
SUCCESS
[oracle@racattackstandby admin]$ sqlplus sys/oracle@rac ( While trying
to connect from primary to standby)
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 20 08:15:58 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in
connect
descriptor
----------------------Listener Status at Primary-----------------------
-------------
[oracle@racattackstandby admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-FEB-2017 08:18:23
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 -
Production
Start Date 20-FEB-2017 07:22:47
Uptime 0 days 0 hr. 55 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/
racattackstandby/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.58)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
Service "standby.racattack" has 1 instance(s).
Instance "standby", status READY, has 1 handler(s) for this service...
Service "standby_DGB.racattack" has 1 instance(s).
Instance "standby", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@racattackstandby admin]$
---------------------------Listener status at
Standby-------------------------------
[oracle@racattack1 trace]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-FEB-2017 08:19:20
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 -
Production
Start Date 20-FEB-2017 07:26:24
Uptime 0 days 0 hr. 53 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/
racattack1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.53)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.63)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "rac.racattack" has 1 instance(s).
Instance "rac1", status READY, has 1 handler(s) for this service...
Service "rac1_DGMGRL" has 1 instance(s).
Instance "rac", status UNKNOWN, has 1 handler(s) for this service...
Service "racXDB.racattack" has 1 instance(s).
Instance "rac1", status READY, has 1 handler(s) for this service...
Service "rac_DGB.racattack" has 1 instance(s).
Instance "rac1", status READY, has 1 handler(s) for this service...
The command completed successfully
--------------------------------------------------------------------
I am very confused,can anyone kindly assists..
Best Regards,
Sandy
On Mon, Feb 20, 2017 at 7:51 AM, Sandip Mukhopadhyay <
sandip.mukhopadhyay24@xxxxxxxxx> wrote:
Hi,
This troubleshooting steps has already been performed .
Best Regards,
Sandip
On Mon, Feb 20, 2017 at 7:44 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:
Choose one of the primary password files, copy it to the other node, and
make sure you have orapwPRIMARY1, orapwPRIMARY2, and orapwSTANDBY in all
the $ORACLE_HOME/dbs of all the instances. use just one password file and
copy it to all the other destinations.
On Sun, Feb 19, 2017 at 8:11 PM, Sandip Mukhopadhyay <
sandip.mukhopadhyay24@xxxxxxxxx> wrote:
Hi,
Please find details below :-
SQL> show parameter log_archive_dest_2 ;( For primary )
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
log_archive_dest_2 string service="rac", LGWR
ASYNC NOAF
FIRM delay=0 optional
compress
ion=disable
max_failure=0 max_
connections=1
reopen=300 db_un
ique_name="rac"
net_timeout=30
,
valid_for=(all_logfiles,prim
ary_role)
DGMGRL> show database verbose 'standby'
Database - standby
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
standby
Error: ORA-16737: the redo transport service for standby database
"rac" has an error
Properties:
DGConnectIdentifier = 'standby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA/rac/, +DATA/standby/'
LogFileNameConvert = '+DATA/rac/, +DATA/standby/'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'standby'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOC
OL=TCP)(HOST=)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standb
y_DGMGRL.racattack)(INSTANCE_NAME=standby)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
ERROR
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac
attack-cluster-scan)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME
=rac)(CID=(PROGRAM=oracle)(HOST=racattackstandby.racattack)(
USER=oracle))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 -
Production
Time: 20-FEB-2017 07:37:30
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
(Alert log at primary (Db unique_name - standby)
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac
attack-cluster-scan)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME
=rac)(CID=(PROGRAM=oracle)(HOST=racattackstandby.racattack)(
USER=oracle))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 -
Production
Time: 20-FEB-2017 07:37:30
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 12514 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'rac'. Error is
12514.
------------------------------------------------------------
------------------------------(Broker log at
primary)--------------------------------------------------------
02/20/2017 07:35:30
Redo transport problem detected: redo transport for database rac has
the following error:
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor
Data Guard Broker Status Summary:
Type Name Severity
Status
Configuration haconfig Warning
ORA-16607
Primary Database standby Error
ORA-16778
Physical Standby Database rac Success
ORA-00000
02/20/2017 07:35:58
Redo transport problem detected: redo transport for database rac has
the following error:
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor
Data Guard Broker Status Summary:
Type Name Severity
Status
Configuration haconfig Warning
ORA-16607
Primary Database standby Error
ORA-16778
Physical Standby Database rac Success
ORA-00000
02/20/2017 07:36:25
Redo transport problem detected: redo transport for database rac has
the following error:
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor
02/20/2017 07:36:45
Redo transport problem detected: redo transport for database rac has
the following error:
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor
Data Guard Broker Status Summary:
Type Name Severity
Status
Configuration haconfig Warning
ORA-16607
Primary Database standby Error
ORA-16778
Physical Standby Database rac Success
ORA-00000
02/20/2017 07:37:46
Redo transport problem detected: redo transport for database rac has
the following error:
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor
Data Guard Broker Status Summary:
Type Name Severity
Status
Configuration haconfig Warning
ORA-16607
Primary Database standby Error
ORA-16778
Physical Standby Database rac Success
ORA-00000
02/20/2017 07:38:45
Redo transport problem detected: redo transport for database rac has
the following error:
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor
Data Guard Broker Status Summary:
Type Name Severity
Status
Configuration haconfig Warning
ORA-16607
Primary Database standby Error
ORA-16778
Physical Standby Database rac Success
ORA-00000
02/20/2017 07:39:45
Redo transport problem detected: redo transport for database rac has
the following error:
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor
Data Guard Broker Status Summary:
Type Name Severity
Status
Configuration haconfig Warning
ORA-16607
Primary Database standby Error
ORA-16778
Physical Standby Database rac Success
ORA-00000
Fatal NI connect error 12514, connecting to:
------------------------------------------------------------
------------------------------
Can anyone kindly assist ?
Appreciate your assistance & cooperation .
Best Regards,
Sandip
On Mon, Feb 20, 2017 at 6:25 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx
wrote:
In the trace directory there will be the alert log and one other log
file, call drc<something>.log. This is the dataguard broker log file, and
should list the specific errors. Also, in dataguard broker you should be
able to run the command show database verbose 'standby'; That should give
the specific error message. Once you get the specific error messages, you
should be able to fix it or ask the list again.
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
