1 2 Previous Next 18 Replies Latest reply: Feb 14, 2013 3:31 AM by CKPT Go to original post RSS
      • 15. Re: Some doubts regarding Data Guard
        mseberg
        Hello again;

        This is not normal, you still have an issue :
        Error 1034 received logging
        Let me reread the entire thread and I will post something here as an edit


        OK, I'm thinking LOG_ARCHIVE_DEST_N on your Primary does not match the tnsnames entry. Can you post the LOG_ARCHIVE_DEST_N for ( all of them ) for both the Primary and standby sides?
        and the LOG_ARCHIVE_DEST_STATE_N for both the Primary and standby sides

        I'm thinking there's a mismatch causing the error.

        Later

        Found this in your prior post :
        log_archive_dest_2 ...: service=dg02 async valid_for=(online_logfile,primary_role) db_unique_name=dg02
        Would still like to compare all your settings. Have tried to recreate on my test system, no luck so far.


        Similar issue here :

        ORA-16009: invalid redo transport destination;

        Archive log can not ship to GAP logfiles to standby DB automatically

        archive logs not shipped !!

        Archive Logs NOT APPLIED but transferred

        Best Regards

        mseberg

        Edited by: mseberg on Feb 5, 2013 1:41 PM

        Edited by: mseberg on Feb 5, 2013 2:14 PM
        • 16. Re: Some doubts regarding Data Guard
          Fabricio_Jorge
          Thanks again for your attention and help.

          Below are the requested informations regarding LOG_ARCHIVE_DEST_N parameters from both primary and standby:
          #############
          ### PRIMARY ###
          ############
          
          SQL> show parameter log_archive_dest_
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_1               string      LOCATION=/archives
          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
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_2               string      service=dg02 async valid_for=(online_logfile,primary_role) db_unique_name=dg02
          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
          log_archive_dest_26               string
          log_archive_dest_27               string
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_28               string
          log_archive_dest_29               string
          log_archive_dest_3               string
          log_archive_dest_30               string
          log_archive_dest_31               string
          log_archive_dest_4               string
          log_archive_dest_5               string
          log_archive_dest_6               string
          log_archive_dest_7               string
          log_archive_dest_8               string
          log_archive_dest_9               string
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_state_1          string      enable
          log_archive_dest_state_10          string      enable
          log_archive_dest_state_11          string      enable
          log_archive_dest_state_12          string      enable
          log_archive_dest_state_13          string      enable
          log_archive_dest_state_14          string      enable
          log_archive_dest_state_15          string      enable
          log_archive_dest_state_16          string      enable
          log_archive_dest_state_17          string      enable
          log_archive_dest_state_18          string      enable
          log_archive_dest_state_19          string      enable
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_state_2          string      ENABLE
          log_archive_dest_state_20          string      enable
          log_archive_dest_state_21          string      enable
          log_archive_dest_state_22          string      enable
          log_archive_dest_state_23          string      enable
          log_archive_dest_state_24          string      enable
          log_archive_dest_state_25          string      enable
          log_archive_dest_state_26          string      enable
          log_archive_dest_state_27          string      enable
          log_archive_dest_state_28          string      enable
          log_archive_dest_state_29          string      enable
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_state_3          string      enable
          log_archive_dest_state_30          string      enable
          log_archive_dest_state_31          string      enable
          log_archive_dest_state_4          string      enable
          log_archive_dest_state_5          string      enable
          log_archive_dest_state_6          string      enable
          log_archive_dest_state_7          string      enable
          log_archive_dest_state_8          string      enable
          log_archive_dest_state_9          string      enable
          
          
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_config               string      dg_config=(dg01,dg02)
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          db_file_name_convert               string
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_file_name_convert               string
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          fal_client                    string
          fal_server                    string
          
          # OBS: I don't really have sure if the db_file_name_convert and log_file_name_convert are necessary for the redo transport.
          
          #############
          ### STANDBY ###
          #############
          
          SQL> show parameter log_archive_dest_
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_1               string      LOCATION=/archives
          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
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_2               string      service=dg01 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dg01
          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
          log_archive_dest_26               string
          log_archive_dest_27               string
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_28               string
          log_archive_dest_29               string
          log_archive_dest_3               string
          log_archive_dest_30               string
          log_archive_dest_31               string
          log_archive_dest_4               string
          log_archive_dest_5               string
          log_archive_dest_6               string
          log_archive_dest_7               string
          log_archive_dest_8               string
          log_archive_dest_9               string
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_state_1          string      enable
          log_archive_dest_state_10          string      enable
          log_archive_dest_state_11          string      enable
          log_archive_dest_state_12          string      enable
          log_archive_dest_state_13          string      enable
          log_archive_dest_state_14          string      enable
          log_archive_dest_state_15          string      enable
          log_archive_dest_state_16          string      enable
          log_archive_dest_state_17          string      enable
          log_archive_dest_state_18          string      enable
          log_archive_dest_state_19          string      enable
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_state_2          string      enable
          log_archive_dest_state_20          string      enable
          log_archive_dest_state_21          string      enable
          log_archive_dest_state_22          string      enable
          log_archive_dest_state_23          string      enable
          log_archive_dest_state_24          string      enable
          log_archive_dest_state_25          string      enable
          log_archive_dest_state_26          string      enable
          log_archive_dest_state_27          string      enable
          log_archive_dest_state_28          string      enable
          log_archive_dest_state_29          string      enable
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_dest_state_3          string      enable
          log_archive_dest_state_30          string      enable
          log_archive_dest_state_31          string      enable
          log_archive_dest_state_4          string      enable
          log_archive_dest_state_5          string      enable
          log_archive_dest_state_6          string      enable
          log_archive_dest_state_7          string      enable
          log_archive_dest_state_8          string      enable
          log_archive_dest_state_9          string      enable
          
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          log_archive_config               string      dg_config=(dg01,dg02)
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          db_file_name_convert               string      /oradata/dg01/, /oradata/dg02/
          log_file_name_convert               string      /redologs/dg01/, /redologs/dg02/
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          fal_client                    string      dg02
          fal_server                    string      dg01
          
          
          ########################
          ### TNSNAMES FROM PRIMARY ###
          ########################
          
          dg01 =
             (DESCRIPTION =
                (ADDRESS_LIST =
                   (ADDRESS = (PROTOCOL = TCP)(Host = dg1)(Port = 1521))
                )
                (CONNECT_DATA =
                   (SERVICE_NAME=dg01)(UR=A)
                )
             )
          
          dg02 =
             (DESCRIPTION =
                (ADDRESS_LIST =
                   (ADDRESS = (PROTOCOL = TCP)(Host = dg2)(Port = 1521))
                )
                (CONNECT_DATA =
                   (SERVICE_NAME=dg02)(UR=A)
                )
             )
          EDIT: I read something about the parameter standby_archive_dest... on my primary, this parameter is set as follows:
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          standby_archive_dest               string      ?/dbs/arch
          standby_file_management           string      AUTO
          I don't know if this makes any difference at all.

          Edited by: Fabricio_Jorge on Feb 5, 2013 5:26 PM
          • 17. Re: Some doubts regarding Data Guard
            mseberg
            Hello again;

            This is not your issue but, The FAL_CLIENT database initialization parameter is no longer required in Oracle 11r2.

            http://docs.oracle.com/cd/E11882_01/server.112/e25608/whatsnew.htm

            The rest of the requested parameters look OK.

            Will try to recreate on my Test system right now.


            Test Conditions

            Primary database started
            Listener on Primary started
            Listener on Standby started
            Standby database down


            Test One tnsping
            /home/oracle:PRIMARY >tnsping standby
            
            TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 20:29:15
            
            Copyright (c) 1997, 2011, Oracle.  All rights reserved.
            
            Used parameter files:
            
            
            Used TNSNAMES adapter to resolve the alias
            Attempting to contact (DESCRIPTION = (SDU = 32767) (SEND_BUF_SIZE = 2092500) (RECV_BUF_SIZE = 2092500) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.13)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = STANDBY)))
            OK (10 msec)
            Works. Without any database.

            Test Two v$archive_dest query
            SQL> col dest_name format a18
            SQL> col error format a22
            SQL> set linesize 200
            SQL> set pagesize 80
            
            
            SQL> select dest_name, target, process, status, error from v$archive_dest;
            
            DEST_NAME          TARGET  PROCESS    STATUS    ERROR
            ------------------ ------- ---------- --------- ----------------------
            LOG_ARCHIVE_DEST_1 PRIMARY ARCH       VALID
            LOG_ARCHIVE_DEST_2 STANDBY LGWR       ERROR     ORA-12541: TNS:no
                                                            listener
            
            LOG_ARCHIVE_DEST_3 PRIMARY ARCH       INACTIVE
            LOG_ARCHIVE_DEST_4 PRIMARY ARCH       INACTIVE
            LOG_ARCHIVE_DEST_5 PRIMARY ARCH       INACTIVE
            LOG_ARCHIVE_DEST_6 PRIMARY ARCH       INACTIVE
            I get a different error.

            Test Three Same query with Standby MOUNTED but no recovery
            DEST_NAME          TARGET  PROCESS    STATUS    ERROR
            ------------------ ------- ---------- --------- ----------------------
            LOG_ARCHIVE_DEST_1 PRIMARY ARCH       VALID
            LOG_ARCHIVE_DEST_2 STANDBY LGWR       VALID
            LOG_ARCHIVE_DEST_3 PRIMARY ARCH       INACTIVE
            Test Four Same query with database open
            DEST_NAME          TARGET  PROCESS    STATUS    ERROR
            ------------------ ------- ---------- --------- ----------------------
            LOG_ARCHIVE_DEST_1 PRIMARY ARCH       VALID
            LOG_ARCHIVE_DEST_2 STANDBY LGWR       ERROR     ORA-03135: connection
                                                            lost contact
            Test Five

            With standby completely down I get a "ORA-03135: connection" but not your error.

            If I didn't know better I'd say you were using SQL plus and Data broker and the issue was caused by the parameters between the two not matching.

            Unable to recreate. Look for trace file in Primary alert logs and if it exists post clues from it.

            Listener Standby side
            # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/network/admin/listener.ora
            # Generated by Oracle configuration tools.
            
            LISTENER =
              (DESCRIPTION_LIST =
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
                )
              )
            
            
            SID_LIST_LISTENER = (SID_LIST =
                                   (SID_DESC =
                                  (SID_NAME = STANDBY)
                                  (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
                                  (SDU=32767)
                                  (GLOBAL_DBNAME = STANDBY_DGMGRL.localdomain)
                                  )
                                  (SID_DESC =
                                   (global_dbname = STANDBY)
                                  (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
                                  (sid_name = STANDBY)
                                   )
            )    
            
              
            
            ADR_BASE_LISTENER = /u01/app/oracle
            Tnsnames Standby side
            # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/network/admin/tnsnames.ora
            # Generated by Oracle configuration tools.
            
            PRIMARY =
              (DESCRIPTION =
                (SDU=32767)
                (SEND_BUF_SIZE=2092500)
                (RECV_BUF_SIZE=2092500)
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.8)(PORT = 1521))
                )
                (CONNECT_DATA =
                  (SERVICE_NAME = PRIMARY)
                )
              )
            
            STANDBY =
              (DESCRIPTION =
                 (SDU=32767)
                 (SEND_BUF_SIZE=2092500)
                 (RECV_BUF_SIZE=2092500)
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.13)(PORT = 1521))
                )
                (CONNECT_DATA =
                  (SERVICE_NAME = STANDBY)
                )
              )
              
            RCATALOG =
              (DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
                )
                (CONNECT_DATA =
                  (SERVICE_NAME = RCATALOG)
                )
              )  
            Pfile standby side
            *.audit_file_dest='/u01/app/oracle/admin/PRIMARY/adump'
            *.audit_trail='db'
            *.compatible='11.2.0.0.0'
            *.control_files='/u01/app/oracle/oradata/PRIMARY/control01.ctl','/u01/app/oracle/oradata/PRIMARY/control02.ctl'
            *.db_block_size=8192
            *.db_domain=''
            *.db_flashback_retention_target=2880
            *.db_name='PRIMARY'
            *.db_recovery_file_dest_size=10737418240
            *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
            *.DB_UNIQUE_NAME='STANDBY'
            *.diagnostic_dest='/u01/app/oracle'
            *.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMARYXDB)'
            *.FAL_CLIENT='STANDBY'
            *.FAL_SERVER='PRIMARY'
            *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
            *.log_archive_dest_2='SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
            *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
            *.LOG_ARCHIVE_DEST_STATE_2='DEFER'
            *.log_archive_format='ARC%S_%R.%T'
            *.LOG_ARCHIVE_MAX_PROCESSES=30
            *.LOG_FILE_NAME_CONVERT='PRIMARY','STANDBY'
            *.memory_target=419430400
            *.open_cursors=300
            *.processes=150
            *.remote_login_passwordfile='EXCLUSIVE'
            *.STANDBY_FILE_MANAGEMENT='AUTO'
            *.undo_tablespace='UNDOTBS1'
            Pfile primary side
            *.audit_file_dest='/u01/app/oracle/admin/PRIMARY/adump'
            *.audit_trail='DB'
            *.compatible='11.2.0.0.0'
            *.control_files='/u01/app/oracle/oradata/PRIMARY/control01.ctl','/u01/app/oracle/oradata/PRIMARY/control02.ctl'
            *.db_block_size=8192
            *.db_domain=''
            *.db_flashback_retention_target=2880
            *.db_name='PRIMARY'
            *.db_recovery_file_dest_size=10737418240
            *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
            *.db_unique_name='PRIMARY'
            *.diagnostic_dest='/u01/app/oracle'
            *.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMARYXDB)'
            *.event=''
            *.fal_client='PRIMARY'
            *.fal_server='STANDBY'
            *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
            *.log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
            *.log_archive_dest_state_1='ENABLE'
            *.log_archive_dest_state_2='ENABLE'
            *.log_archive_format='ARC%S_%R.%T'
            *.log_archive_max_processes=30
            *.log_file_name_convert='STANDBY','PRIMARY'
            *.memory_target=650117120
            *.open_cursors=300
            *.processes=150
            *.remote_login_passwordfile='EXCLUSIVE'
            *.standby_file_management='AUTO'
            *.undo_tablespace='UNDOTBS1'
            I thinking the Standby Database was never created correctly. Might check the Standby alert log too for clues. Compare you INIT's to my samples INIT's.


            No More coming..

            Best Regards

            mseberg

            Edited by: mseberg on Feb 5, 2013 8:31 PM
            • 18. Re: Some doubts regarding Data Guard
              CKPT
              Hi,

              MSeberg requested more information from you. but there is no update on this.
              Also next time whenever you update use this script and post output log in coded format from both primary and standby databases. http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/

              I also would like to know, have you configured Broker? What is the status now?
              If issue is resolved then close the thread as answered and also update Root cause. [Information is wealth]
              1 2 Previous Next