This discussion is archived
8 Replies Latest reply: Sep 7, 2013 10:50 AM by 1012457 RSS

Error: ORA-16778: redo transport error for one or more databases.   please help.

user4914135 Newbie
Currently Being Moderated

Hello everyone :

 

 

I can't switchover to primary. following is error and information.

 

 

 

 

RHEL 6.3 x86-64

Oracle database 11.2.0.3.0 Enterprise edition

 

Primary database = orclprmy

Standby database = orclstby1

 

 

##### /etc/hosts on orclstby1

[root@orclstby1 admin]# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

 

192.168.50.211    ttprmy

192.168.50.212    orclstby1

 

 

### DG broker error

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

 

DGMGRL> show configuration;

 

Configuration - TTDGConfig1

 

  Protection Mode: MaxPerformance

  Databases:

    orclstby1 - Primary database

      Error: ORA-16778: redo transport error for one or more databases

 

    orclprmy  - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

ERROR

 

DGMGRL>

 

 

 

 

########### listener.ora on orclstby1

[root@orclstby1 admin]# cat listener.ora

# listener.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = orclstby1)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME=orcl)

      (SID_NAME = orclstby1)

      (ORACLE_HOME = /u2/oracle/product/11.2.0/dbhome_1)

    )

    (SID_DESC =

      (GLOBAL_DBNAME=orclstby1)

      (SID_NAME = orclstby1)

      (ORACLE_HOME = /u2/oracle/product/11.2.0/dbhome_1)

    )

    (SID_DESC =

      (GLOBAL_DBNAME=orclstby1_DGMGRL)

      (SID_NAME = orclstby1)

      (ORACLE_HOME = /u2/oracle/product/11.2.0/dbhome_1)

    )

  )

 

 

ADR_BASE_LISTENER = /u2/oracle

 

 

############## tnsnames.ora on orclstby1

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.212)(PORT = 1521))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl))

  )

orclprmy =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.211)(PORT = 1521))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orclprmy))

  )

orclprmy_DGMGRL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.211)(PORT = 1521))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orclprmy_DGMGRL))

  )

orclstby1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.212)(PORT = 1521))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orclstby1))

  )

orclstby1_DGMGRL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.212)(PORT = 1521))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orclstby1_DGMGRL))

  )

 

 

 

##### alert log on orclstby1.

 

Fatal NI connect error 12504, connecting to:

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=)(CID=(PROGRAM=oracle)(HOST=orclstby1)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.50.211)(PORT=1521)))

 

  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: 06-SEP-2013 13:19:55

  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

 

There is problem  in alert log.

In the /etc/hosts file. The standby server (orclstby1) ip is 192.168.50.212. but alert log is 192.168.50.211.

 

Is any idea?

 

Thanks for help.

 

消息编辑者为:user4914135

  • 1. Re: Error: ORA-16778: redo transport error for one or more databases.   please help.
    saurabh Pro
    Currently Being Moderated

    use the tnsping on both the server for both the database and post the output

  • 2. Re: Error: ORA-16778: redo transport error for one or more databases.   please help.
    user4914135 Newbie
    Currently Being Moderated

    # on standby database

    </u2/oracle/product/11.2.0/dbhome_1/dbs> tnsping orclstby1

     

     

    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-SEP-2013 14:04:41

     

     

    Copyright (c) 1997, 2011, Oracle.  All rights reserved.

     

     

    Used parameter files:

    /u2/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

     

     

    Used EZCONNECT adapter to resolve the alias

    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.50.212)(PORT=1521)))

    OK (0 msec)

     

     

     

     

     

     

     

     

    # on primary database

    </u2/oracle/product/11.2.0/dbhome_1> tnsping orclstby1   

     

     

    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-SEP-2013 14:08:59

     

     

    Copyright (c) 1997, 2011, Oracle.  All rights reserved.

     

     

    Used parameter files:

    /u2/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

     

     

     

     

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orclstby1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orclstby1)))

    OK (10 msec)

    </u2/oracle/product/11.2.0/dbhome_1> ping -c 2 orclstby1

    PING orclstby1 (10.20.50.212) 56(84) bytes of data.

    64 bytes from orclstby1 (10.20.50.212): icmp_seq=1 ttl=64 time=0.171 ms

    64 bytes from orclstby1 (10.20.50.212): icmp_seq=2 ttl=64 time=0.182 ms

     

     

    --- orclstby1 ping statistics ---

    2 packets transmitted, 2 received, 0% packet loss, time 1000ms

    rtt min/avg/max/mdev = 0.171/0.176/0.182/0.014 ms

    </u2/oracle/product/11.2.0/dbhome_1>

  • 3. Re: Error: ORA-16778: redo transport error for one or more databases.   please help.
    saurabh Pro
    Currently Being Moderated

    post the ouput of tnsping of both primary and standby database from both the server

     

    eg server A

     

    tnsping prod

    tnsping standdby

     

    sever B

    tnsping prod

    tnsping standdby

     

     

    And paste their output.

  • 4. Re: Error: ORA-16778: redo transport error for one or more databases.   please help.
    user4914135 Newbie
    Currently Being Moderated

    ##### on primary database

    </u2/oracle/product/11.2.0/dbhome_1> tnsping orclprmy

     

     

    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-SEP-2013 14:11:42

     

     

    Copyright (c) 1997, 2011, Oracle.  All rights reserved.

     

     

    Used parameter files:

    /u2/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

     

     

     

     

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orclprmy)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orclprmy)))

    OK (0 msec)

    </u2/oracle/product/11.2.0/dbhome_1> tnsping orclstby1

     

     

    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-SEP-2013 14:11:48

     

     

    Copyright (c) 1997, 2011, Oracle.  All rights reserved.

     

     

    Used parameter files:

    /u2/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

     

     

     

     

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orclstby1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orclstby1)))

    OK (0 msec)

    </u2/oracle/product/11.2.0/dbhome_1> ping -c 2 orclprmy

    PING orclprmy (10.20.50.211) 56(84) bytes of data.

    64 bytes from tiptop (10.20.50.211): icmp_seq=1 ttl=64 time=0.030 ms

    64 bytes from tiptop (10.20.50.211): icmp_seq=2 ttl=64 time=0.037 ms

     

     

    --- orclprmy ping statistics ---

    2 packets transmitted, 2 received, 0% packet loss, time 999ms

    rtt min/avg/max/mdev = 0.030/0.033/0.037/0.006 ms

    </u2/oracle/product/11.2.0/dbhome_1> ping -c 2 orclstby1

    PING orclstby1 (10.20.50.212) 56(84) bytes of data.

    64 bytes from orclstby1 (10.20.50.212): icmp_seq=1 ttl=64 time=0.169 ms

    64 bytes from orclstby1 (10.20.50.212): icmp_seq=2 ttl=64 time=0.135 ms

     

     

    --- orclstby1 ping statistics ---

    2 packets transmitted, 2 received, 0% packet loss, time 1000ms

    rtt min/avg/max/mdev = 0.135/0.152/0.169/0.017 ms

     

     

     

     

     

     

    ###### on staydby database

    </u2/oracle/product/11.2.0/dbhome_1/network/admin> tnsping orclprmy

     

     

    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-SEP-2013 14:12:36

     

     

    Copyright (c) 1997, 2011, Oracle.  All rights reserved.

     

     

    Used parameter files:

    /u2/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

     

     

    Used EZCONNECT adapter to resolve the alias

    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.50.211)(PORT=1521)))

    OK (0 msec)

    </u2/oracle/product/11.2.0/dbhome_1/network/admin> tnsping orclstby1

     

     

    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-SEP-2013 14:12:41

     

     

    Copyright (c) 1997, 2011, Oracle.  All rights reserved.

     

     

    Used parameter files:

    /u2/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

     

     

    Used EZCONNECT adapter to resolve the alias

    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.50.212)(PORT=1521)))

    OK (0 msec)

  • 5. Re: Error: ORA-16778: redo transport error for one or more databases.   please help.
    saurabh Pro
    Currently Being Moderated

    check the fal setting on both the database. and paste the output of "select DEST_ID,error from v$archive_dest;" on primary database.

     

    Also paste the output of  "show parameter log_archive_dest" from both the server.

  • 6. Re: Error: ORA-16778: redo transport error for one or more databases.   please help.
    user4914135 Newbie
    Currently Being Moderated

    #### on primary database

     

     

    SQL>  select dest_name,status,target,archiver,schedule, valid_type,valid_role,db_unique_name,error from v$archive_dest;

     

     

    DEST_NAME            STATUS    TARGET  ARCHIVER   SCHEDULE VALID_TYPE      VALID_ROLE   DB_UNIQUE_NAME

    -------------------- --------- ------- ---------- -------- --------------- ------------ ------------------------------

    ERROR

    -----------------------------------------------------------------

    LOG_ARCHIVE_DEST_1   VALID     LOCAL   ARCH       ACTIVE   ALL_LOGFILES    ALL_ROLES    orclprmy

     

     

     

     

    LOG_ARCHIVE_DEST_2   VALID     REMOTE  LGWR       PENDING  ALL_LOGFILES    PRIMARY_ROLE orclstby1

     

     

     

     

    LOG_ARCHIVE_DEST_3   INACTIVE  LOCAL   ARCH       INACTIVE ALL_LOGFILES    ALL_ROLES    NONE

     

     

     

     

     

     

     

     

     

     

     

     

     

    #### on standby database

    SQL> select dest_name,status,target,archiver,schedule, valid_type,valid_role,db_unique_name,error from v$archive_dest;

     

     

    DEST_NAME            STATUS    TARGET  ARCHIVER   SCHEDULE VALID_TYPE      VALID_ROLE   DB_UNIQUE_NAME

    -------------------- --------- ------- ---------- -------- --------------- ------------ ------------------------------

    ERROR

    -----------------------------------------------------------------

    LOG_ARCHIVE_DEST_1   VALID     PRIMARY ARCH       ACTIVE   ALL_LOGFILES    ALL_ROLES    orclstby1

     

     

     

     

    LOG_ARCHIVE_DEST_2   ERROR     STANDBY LGWR       PENDING  ONLINE_LOGFILE  PRIMARY_ROLE orclprmy

    ORA-12504: TNS:listener was not given the SERVICE_NAME in

    CONNECT_DATA

     

     

    LOG_ARCHIVE_DEST_3   INACTIVE  PRIMARY ARCH       INACTIVE ALL_LOGFILES    ALL_ROLES    NONE 

     

     

     

     

    ####  log_archive_dest on primary database  

    SQL> show parameter log_archive_dest

     

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    log_archive_dest                     string

    log_archive_dest_1                   string      location=/u3/arch/orcl vali

                                                     d_for=(ALL_LOGFILES,ALL_ROLES)

                                                      db_unique_name=orclprmy

    log_archive_dest_10                  string

    log_archive_dest_11                  string

    log_archive_dest_12                  string

    log_archive_dest_13                  string

    log_archive_dest_14                  string

    log_archive_dest_15                  string

    log_archive_dest_16                  string

    log_archive_dest_17                  string

    log_archive_dest_18                  string

    log_archive_dest_19                  string

    log_archive_dest_2                   string      service="orclstby1", LGWR ASYNC

                                                     NOAFFIRM delay=0 optional comp

                                                     ression=disable max_failure=0

                                                     max_connections=1 reopen=300 d

                                                     b_unique_name="orclstby1" net_ti

                                                     meout=30, valid_for=(all_logfi

                                                     les,primary_role)

    log_archive_dest_20                  string

    log_archive_dest_21                  string

    log_archive_dest_22                  string

     

     

     

     

     

     

    ####  log_archive_dest on standby database

    SQL> show parameter log_archive_dest

     

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    log_archive_dest                     string

    log_archive_dest_1                   string      location=/u3/arch/orclstby1 vali

                                                     d_for=(ALL_LOGFILES,ALL_ROLES)

                                                      db_unique_name=orclstby1

    log_archive_dest_10                  string

    log_archive_dest_11                  string

    log_archive_dest_12                  string

    log_archive_dest_13                  string

    log_archive_dest_14                  string

    log_archive_dest_15                  string

    log_archive_dest_16                  string

    log_archive_dest_17                  string

    log_archive_dest_18                  string

    log_archive_dest_19                  string

    log_archive_dest_2                   string      service=orclprmy ASYNC valid_for

                                                     =(ONLINE_LOGFILE,PRIMARY_ROLE)

                                                      db_unique_name=orclprmy

    log_archive_dest_20                  string

    log_archive_dest_21                  string

    log_archive_dest_22                  string

    log_archive_dest_23                  string

    log_archive_dest_24                  string

    log_archive_dest_25                  string

     

     

     

     

     

     

     

     

    #### spfile on standby database

    </u2/oracle/product/11.2.0/dbhome_1/dbs> strings spfileorclstby1.ora

    orcl.__db_cache_size=1040187392

    orclstby1.__db_cache_size=1090519040

    orcl.__java_pool_size=16777216

    orclstby1.__java_pool_size=16777216

    orcl.__large_pool_size=16777216

    orclstby1.__large_pool_size=16777216

    orcl.__oracle_base='/u2/oracle'#ORACLE_BASE set from environment

    orclstby1.__oracle_base='/u2/oracle'#ORACLE_BASE set from environment

    orcl.__pga_aggregate_target=536870912

    orclstby1.__pga_aggregate_target=536870912

    orcl.__sga_target=1610612736

    orclstby1.__sga_target=161061273

    orcl.__shared_io_pool_size=0

    orclstby1.__shared_io_pool_size=0

    orcl.__shared_pool_size=503316480

    orclstby1.__shared_pool_size=469762048

    orcl.__streams_pool_size=16777216

    orclstby1.__streams_pool_size=0

    *.archive_lag_target=0

    *.audit_file_dest='/u2/oracle/admin/orclstby1/adump'

    *.audit_trail='db'

    *.compatible='11.2.0.0.0'

    *.control_files='/u2/oracle/oradata/orclstby1/control01.ctl','/u2/oracle/fast_recovery_area/orclstby1/control02.ctl'

    *.db_block_size=8192

    *.db_domain=''

    *.db_file_nam

    e_convert='orcl','orclstby1'

    *.db_name='orcl'

    *.db_recovery_file_dest='/u2/oracle/fast_recovery_area'

    *.db_recovery_file_dest_size=5218762752

    *.db_unique_name='orclstby1'

    *.deferred_segment_creation=FALSE

    *.dg_broker_start=TRUE

    *.diagnostic_dest='/u2/oracle'

    *.fal_client='orclstby1'

    *.fal_server='orclprmy'

    *.log_archive_config='dg_config=(orclprmy,orclstby1)'

    *.log_archive_dest_1='location=/u3/arch/orclstby1 valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orclstby1'

    *.log_archive_dest_2='ser

    vice=orclprmy ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orclprmy'

    *.log_archive_dest_state_2='ENABLE'

    orcl.log_archive_format='orcl_%t_%s_%r.arc'

    *.log_archive_format='orclstby1_%t_%s_%r.arc'

    orclstby1.log_archive_format='orclstby1_%t_%s_%r.arc'

    *.log_archive_max_processes=4

    *.log_archive_min_succeed_dest=1

    orcl.log_archive_trace=0

    orclstby1.log_archive_trace=0

    *.log_file_name_convert='orcl','orclstby1'

    *.open_cursors=300

    *.pga_aggregate_target=536870912

    *.processes=

    1500

    *.remote_login_passwordfile='EXCLUSIVE'

    *.sessions=1655

    *.sga_target=1610612736

    *.standby_file_management='AUTO'

    *.undo_tablespace='UNDOTBS1'

    </u2/oracle/product/11.2.0/dbhome_1/dbs>

     

     

     

     

     

     

     

    Thank you for your help.

  • 7. Re: Error: ORA-16778: redo transport error for one or more databases.   please help.
    user4914135 Newbie
    Currently Being Moderated

    dear saurabh :


    I alread fix.

    When I used tnsping orclprmy. It's works.

    But I used sqlplus sys/password@orclprmy as sysdba to connect primary database. It's failed. I modify tnsnames.ora and listener.ora many times. It's still can't be works.

    So I delete the tnsnames.ora listener.ora and copy *.ora *.lst from primary database to standby database and midify throse files.

    Start listener and database and reconnect primary database use "sqlplus sys/password@orclprmy as sysdba" command . 

    It's works!!!!  


    Thank you for your help.

  • 8. Re: Error: ORA-16778: redo transport error for one or more databases.   please help.
    1012457 Newbie
    Currently Being Moderated

    As your problem is solved ,but one thing that should be noticed that in standby database log_archive_dest_state_2 parameter should be in defer mode.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points