8 Replies Latest reply: Sep 7, 2013 12:50 PM by 1012457 RSS

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

    TzuWen, Lin

      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

          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.
            TzuWen, Lin

            # 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

              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.
                TzuWen, Lin

                ##### 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

                  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.
                    TzuWen, Lin

                    #### 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.
                      TzuWen, Lin

                      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

                        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.