Re: Log Shipping Issue in my dataguard environment- ORA16737 : the redo transport service has error
Date: Sun, 19 Feb 2017 21:30:31 -0600
Message-Id: <05BA154A-1F89-4D1F-BDAB-A68B78FF6BCD_at_gmail.com>
Yes it needs to be in the ASM tnsnames. Dgmgrl uses it at some point, I have run into that problem before. Did you make sure the tnsnames file is the same on both rac nodes and the standby?
Sent from my iPad
> On Feb 19, 2017, at 9:15 PM, Sandip Mukhopadhyay <sandip.mukhopadhyay24_at_gmail.com> wrote:
>
> 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_at_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_at_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_at_racattackstandby admin]$ sqlplus sys/oracle_at_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_at_gmail.com> 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_at_gmail.com> 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 -------------------------------- >>> >>> >>> >> Starting Data Guard Broker bootstrap << >>> Broker Configuration File Locations: >>> 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_at_racattackstandby admin]$ sqlplus sys/oracle_at_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_at_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_at_racattackstandby admin]$ >>> ---------------------------Listener status at Standby------------------------------- >>> >>> >>> [oracle_at_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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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=(PROTOCOL=TCP)(HOST=)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby_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=racattack-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=racattack-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_at_gmail.com> 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.' >>>> >>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 20 2017 - 04:30:31 CET