9 Replies Latest reply: Mar 20, 2013 2:46 PM by 993195 RSS

    Error when starting dataguard

    993195
      Hello. I set up a new dataguard and am receiving errors when I try to start it on my primary. I looked at support note 172779.1 and it says the DB_UNIQUE_NAME needs to match from the log_archive_dest_2 to the db_unique_name of the standby. I think it looks okay but I'm missing something.

      10.2.0.5 on Linux RH 5


      On primary -
      log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
      DEST valid_for=(ALL_LOGFILES,A
      LL_ROLES) DB_UNIQUE_NAME=bcso

      log_archive_dest_2 string SERVICE=BCSTDBY_40 reopen=60 V
      ALID_FOR=(ONLINE_LOGFILES,PRIM
      ARY_ROLE) DB_UNIQUE_NAME=stdby

      From standby
      SQL> show parameter db_name

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      db_name string bcso

      SQL> show parameter db_uniq

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      db_unique_name string stdby


      error on primary

      ORA-16047: DGID mismatch between destination setting and standby
      Wed Mar 20 16:32:49 GMT 2013
      PING[ARC1]: Heartbeat failed to connect to standby 'BCSTDBY_40'. Error is 16047.
      Wed Mar 20 16:33:20 GMT 2013
      Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST

      tnsping BCSTDBY_40

      TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 20-MAR-2013 16:50:26

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

      Used parameter files:
      /u01/app/oracle/product/10.2.0_64/Db_1/network/admin/sqlnet.ora

      From primary -
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.40)(PORT = 1611)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stdby)))
      OK (0 msec)

      From stdby

      tnsping BCSTDBY_39

      TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 20-MAR-2013 16:52:45

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

      Used parameter files:
      /u01/app/oracle/product/10.2.0_64/Db_1/network/admin/sqlnet.ora


      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.39)(PORT = 1611)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bcso)))
      OK (0 msec)

      Edited by: 990192 on Mar 20, 2013 9:55 AM
        • 1. Re: Error when starting dataguard
          mseberg
          Hello;

          Seems clear.
          log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bcso
          
          log_archive_dest_2 string SERVICE=BCSTDBY_40 reopen=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby
          I read this as stdby does match the setting defined at the destination.

          Post INIT for Standby and Primary

          Best Regards

          mseberg
          • 2. Re: Error when starting dataguard
            993195
            I assume you wanted the spfile contents :) Thanks for helping.
            *.AUDIT_SYS_OPERATIONS=TRUE
            *.audit_trail='NONE'
            *.background_dump_dest='/u01/app/oracle/admin/bcso/bdump'
            *.compatible='10.2.0.5.0'
            *.control_files='/u01/app/oracle/oradata/bcso/control01.ctl','/u02/oradata/bcso/control02.ctl','/u03/oradata/bcso/control03.ctl'#Restore Controlfile
            *.core_dump_dest='/u01/app/oracle/admin/bcso/cdump'
            *.db_16k_cache_size=314572800
            *.db_2k_cache_size=16777216
            *.db_4k_cache_size=16777216
            *.db_block_size=8192
            *.db_cache_size=1258291200
            *.db_domain='world'
            *.db_file_multiblock_read_count=16
            *.db_name='bcso'
            *.db_recovery_file_dest='/u04/flash_recovery_area'
            *.db_recovery_file_dest_size=375809638400
            *.db_unique_name='stdby'
            *.db_writer_processes=3
            *.fal_client='stdby'
            *.fal_server='bcso'
            *.filesystemio_options='setall'
            *.GLOBAL_NAMES=TRUE
            *.job_queue_processes=0
            *.log_archive_config='DG_CONFIG=(bcsst,stdby)'
            *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
            *.log_archive_dest_2='SERVICE=BCSTDBY_39 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bcso'
            *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
            *.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
            *.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
            *.open_cursors=300
            *.os_authent_prefix='BCS3USR$'
            *.parallel_execution_message_size=65535
            *.pga_aggregate_target=1500M
            *.processes=150
            *.remote_login_passwordfile='EXCLUSIVE'
            *.RESOURCE_LIMIT=TRUE
            *.sga_target=2000M
            *.SQL92_SECURITY=TRUE
            *.standby_file_management='auto'
            *.undo_management='AUTO'
            *.undo_tablespace='UNDOTBLS1'
            *.user_dump_dest='/u01/app/oracle/admin/bcso/udump'
            *.AUDIT_SYS_OPERATIONS=TRUE
            *.audit_trail='db'
            *.background_dump_dest='/u01/app/oracle/admin/bcso/bdump'
            *.compatible='10.2.0.5.0'
            *.control_files='/u01/app/oracle/oradata/bcso/control01.ctl','/u02/oradata/bcso/control02.ctl','/u03/oradata/bcso/control03.ctl'
            *.core_dump_dest='/u01/app/oracle/admin/bcso/cdump'
            *.db_16k_cache_size=335544320
            *.db_2k_cache_size=16777216
            *.db_4k_cache_size=16777216
            *.db_block_size=8192
            *.db_cache_size=4G
            *.db_domain='world'
            *.db_file_multiblock_read_count=16
            *.db_name='bcso'
            *.db_recovery_file_dest='/u04/flash_recovery_area'
            *.db_recovery_file_dest_size=536870912000
            *.db_unique_name='BCSO'
            *.db_writer_processes=3
            *.dbwr_io_slaves=1
            *.fal_client='BCSO'
            *.fal_server='STDBY'
            *.filesystemio_options='SETALL'
            *.GLOBAL_NAMES=FALSE
            *.job_queue_processes=20
            *.log_archive_config='DG_CONFIG=(bcso,stdby)'
            *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bcso'
            *.log_archive_dest_2='SERVICE=BCSTDBY_40 reopen=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby'
            *.log_archive_dest_state_2='ENABLE'
            *.log_archive_max_processes=4
            *.open_cursors=300
            *.os_authent_prefix='BCS3USR$'
            *.parallel_max_servers=100
            *.pga_aggregate_target=3336568832
            *.processes=150
            *.remote_login_passwordfile='EXCLUSIVE'
            *.RESOURCE_LIMIT=TRUE
            *.session_max_open_files=20
            *.sga_max_size=9663676416
            *.sga_target=10737418240
            *.SQL92_SECURITY=TRUE
            *.standby_file_management='AUTO'
            *.undo_management='AUTO'
            *.undo_tablespace='UNDOTBS2'
            *.user_dump_dest='/u01/app/oracle/admin/bcso/udump'
            • 3. Re: Error when starting dataguard
              993195
              Okay I corrected the issue on the standby log_archive_config='DG_CONFIG=(bcso,stdby)' and it looks like redo is shipping but now I'm getting an error on the standby.


              ORA-12514: TNS:listener does not currently know of service requested in connect descriptor



              SQL> conn sys@BCSTDBY_39 as sysdba
              Enter password: *******************
              Connected.

              I'll keep digging.
              • 4. Re: Error when starting dataguard
                mseberg
                Hello;

                Sorry for the delay. Yes I see it. Thanks ORA-16047 is hard to track down you probably helped others.
                log_archive_config='DG_CONFIG=(bcsst,stdby)'
                I have a listener example here :

                http://www.visi.com/~mseberg/data_guard/Data_Guard_Listener_Example.html

                If you find something make sure you stop and start your listener(s).

                Best Regards

                mseberg

                Edited by: mseberg on Mar 20, 2013 1:25 PM
                • 5. Re: Error when starting dataguard
                  993195
                  I think this is because I am using a non default port (1611) but it worked before. Do I need to set the local_listener parameter on the standby?

                  ###
                  ### Listener setup for Standby Database
                  ###
                  SID_LIST_DG40_LSTNR =
                  (SID_LIST =
                  (SID_DESC =
                  (GLOBAL_NAME = bcso)
                  (SID_NAME = STDBY)
                  (ORACLE_HOME = /u01/app/oracle/product/10.2.0_64/Db_1)
                  )
                  )

                  DG40_LSTNR =
                  (DESCRIPTION_LIST =
                  (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.40)(PORT = 1611))
                  )
                  )
                  • 6. Re: Error when starting dataguard
                    mseberg
                    Hello again;
                    local_listener parameter on the standby?
                    I'm thinking no.

                    Ed Stevens has some great information on this :

                    http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

                    It compares to mine OK near as I can see
                     ###
                     ### Listener setup for Standby Database
                     ###
                     SID_LIST_DG40_LSTNR =
                       (SID_LIST =
                        (SID_DESC =
                         (GLOBAL_NAME = bcso)
                          (SID_NAME = STDBY)
                        (ORACLE_HOME = /u01/app/oracle/product/10.2.0_64/Db_1)
                       )
                     )
                     
                     DG40_LSTNR =
                       (DESCRIPTION_LIST =
                         (DESCRIPTION =
                        (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.40)(PORT = 1611))
                      )
                    )
                    MINE
                    # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/network/admin/listener.ora
                    # Generated by Oracle configuration tools.
                    
                    SID_LIST_LISTENER =
                      (SID_LIST =
                        (SID_DESC =
                          (SID_NAME = PLSExtProc)
                          (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
                          (PROGRAM = extproc)
                        )
                        (SID_DESC =
                          (global_dbname = PRIMARY_DGMGRL.myhost)
                          (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
                          (sid_name = PRIMARY)
                        )    
                      )
                    
                    LISTENER =
                      (DESCRIPTION_LIST =
                        (DESCRIPTION =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
                          (ADDRESS = (PROTOCOL = IPC)(KEY = REGISTER))
                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
                        )
                      )
                    
                    ADR_BASE_LISTENER = /u01/app/oracle
                    
                    INBOUND_CONNECT_TIMEOUT_LISTENER=120
                    If the TNSNAMES.ORA has a small typo of hidden space you could get this error too.

                    Best Regards

                    mseberg

                    Edited by: mseberg on Mar 20, 2013 2:00 PM
                    • 7. Re: Error when starting dataguard
                      993195
                      I think I am working now. Does this look correct from the stdby log file?
                      Completed: alter database recover managed standby database disconnect from session
                      Wed Mar 20 18:59:43 GMT 2013
                      RFS[1]: Archived Log: '/u04/flash_recovery_area/STDBY/archivelog/2013_03_20/o1_mf_1_91517_8nn1omln_.arc'
                      Wed Mar 20 18:59:44 GMT 2013
                      Media Recovery Log /u04/flash_recovery_area/STDBY/archivelog/2013_03_20/o1_mf_1_91517_8nn1omln_.arc
                      Wed Mar 20 19:00:18 GMT 2013
                      Redo Shipping Client Connected as PUBLIC
                      -- Connected User is Valid
                      RFS[2]: Assigned to RFS process 19730
                      RFS[2]: Identified database type as 'physical standby'
                      Wed Mar 20 19:00:53 GMT 2013
                      Media Recovery Waiting for thread 1 sequence 91518 (in transit)
                      Wed Mar 20 19:02:02 GMT 2013
                      RFS[2]: Archived Log: '/u04/flash_recovery_area/STDBY/archivelog/2013_03_20/o1_mf_1_91518_8nn1t2z3_.arc'
                      Wed Mar 20 19:02:03 GMT 2013
                      Media Recovery Log /u04/flash_recovery_area/STDBY/archivelog/2013_03_20/o1_mf_1_91518_8nn1t2z3_.arc
                      Wed Mar 20 19:02:26 GMT 2013
                      Media Recovery Waiting for thread 1 sequence 91519
                      • 8. Re: Error when starting dataguard
                        mseberg
                        Yes.

                        If I remember correctly this :

                        Redo Shipping Client Connected as PUBLIC


                        Goes away in Oracle 11.


                        Run this on the primary side from time to time :

                        http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html


                        Best Regards

                        mseberg
                        • 9. Re: Error when starting dataguard
                          993195
                          Thanks. I'm not totally sure about your query. I left the dest_id's as is since I only have 1 and 2. I think I am good. Thanks for your help!



                          DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
                          ---------- -------------- ------------ ----------- -------------- -------
                          BCSO HOST 91520 91520 20-MAR/19:40 0



                          STDBY

                          SEQUENCE# ARC APP
                          ---------- --- ---
                          91510 YES YES
                          91509 YES YES
                          91511 YES YES
                          91512 YES YES
                          91513 YES YES
                          91486 YES YES
                          91514 YES YES
                          91515 YES YES
                          91516 YES YES
                          91517 YES YES
                          91518 YES YES

                          SEQUENCE# ARC APP
                          ---------- --- ---
                          91519 YES YES


                          Primary

                          91510 YES YES
                          91509 YES YES
                          91511 YES YES
                          91512 YES YES
                          91513 YES YES
                          91486 YES YES
                          91514 YES NO
                          91514 YES YES
                          91515 YES NO
                          91515 YES YES
                          91516 YES NO
                          91516 YES YES
                          91517 YES NO
                          91517 YES YES
                          91518 YES NO
                          91518 YES YES
                          91519 YES NO
                          91519 YES YES