9 Replies Latest reply: Apr 25, 2014 2:15 PM by mseberg RSS

    Cannot apply archive in standby db

    GTS (DBA)

      Hi

       

      Primary DB : orcl

      Standby DB : clone

      DB VERSION: 10.2.0.5.0

      ** Both are in same  server  **

       

      SQL> select name , DB_UNIQUE_NAME from v$database

       

      NAME      DB_UNIQUE_NAME

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

      ORCL      clone

       

       

      >> standby alert log error

       

      ORA-16009: remote archive log destination must be a STANDBY database

      Thu Apr 24 16:42:47 IST 2014

      PING[ARC0]: Heartbeat failed to connect to standby 'orcl'. Error is 16009.

      Thu Apr 24 16:48:15 IST 2014

      Errors in file /u01/app/oracle/admin/clone/bdump/clone_arc0_4675.trc:

      ORA-16009: remote archive log destination must be a STANDBY database

      Thu Apr 24 16:48:15 IST 2014

      PING[ARC0]: Heartbeat failed to connect to standby 'orcl'. Error is 16009.

       

      $ cat initorcl.ora

      *.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

      *.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'

      *.compatible='10.2.0.5.0'

      *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl',

      '/u01/app/oracle/oradata/orcl/control03.ctl'

      *.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'

      *.db_name='orcl'

      *.remote_login_passwordfile='EXCLUSIVE'

      *.sga_target=465567744

      *.undo_management='AUTO'

      *.undo_tablespace='UNDOTBS1'

      *.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

      fal_server=clone

      fal_client=orcl

      db_unique_name=orcl

      remote_login_passwordfile='exclusive'

      log_archive_dest_1='location=/u02/prim_archive'

      log_archive_dest_2='service=clone'

      standby_archive_dest=/u02/prim_archive

      log_file_name_convert='/u01/app/oracle/oradata/clone/','/u01/app/oracle/oradata/orcl/'

      db_file_name_convert='/u01/app/oracle/oradata/clone/','/u01/app/oracle/oradata/orcl/'

       

      >> Standby pfile

      $ cat initclone.ora

      *.audit_file_dest='/u01/app/oracle/admin/clone/adump'

      *.background_dump_dest='/u01/app/oracle/admin/clone/bdump'

      *.compatible='10.2.0.5.0'

      *.control_files='/u01/app/oracle/oradata/clone/control01.ctl','/u01/app/oracle/oradata/clone/control02.ctl','/u01/app/oracle/oradata/clone/control03.ctl'

      *.core_dump_dest='/u01/app/oracle/admin/clone/cdump'

      *.db_name='orcl'

      *.remote_login_passwordfile='EXCLUSIVE'

      *.sga_target=465567744

      *.undo_management='AUTO'

      *.undo_tablespace='UNDOTBS1'

      *.user_dump_dest='/u01/app/oracle/admin/clone/udump'

      fal_server=orcl

      fal_client=clone

      db_unique_name=clone

      remote_login_passwordfile='exclusive'

      log_archive_dest_1='location=/u02/stby_archive'

      log_archive_dest_2='service=orcl'

      standby_archive_dest=/u02/stby_archive

      log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/clone/'

      db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/clone/'

      [oracle@oel5 dbs]$

        • 1. Re: Cannot apply archive in standby db
          Mahir M. Quluzade

          HI,

           

          Did you  sure created control file for standby database?

           

          Please chekc on standby side:

           

          SQL> select database_role from v$database;

           

          If result is PHYSICAL or LOGICAL Standby then problem in LOG_ARCHIVE_DEST_2 parameter of primary database.

          Check log-archive_dest_2 parameter is correct or not.

           

          Regards

          Mahir M. Quluzade

          • 2. Re: Cannot apply archive in standby db
            GTS (DBA)

            Hi ;

             

            >> From standby DB

             

            SQL> select status from v$instance;

            STATUS

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

            MOUNTED

             

            SQL> select database_role from v$database;

            DATABASE_ROLE

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

            PHYSICAL STANDBY


            SQL> show parameter log_archive_dest;

             

            NAME                                 TYPE        VALUE

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

            log_archive_dest_1                   string      location=/u02/stby_archive

            log_archive_dest_2                   string      service=orcl

             

            >> From my Primary  DB

             

            SQL> show parameter log_archive_dest;

             

             

            NAME                                 TYPE        VALUE

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

            log_archive_dest                     string      /u02/prim_archive

            log_archive_dest_2                   string                                ------------------> Empty

             

             

            FYI :  but i have configured  perfectly. why i am facing error. I dont understand.

             

            >> FROM  initorcl.ora ( primarydb  (orcl)

            log_archive_dest_1='location=/u02/prim_archive'

            log_archive_dest_2='service=clone'

            • 3. Re: Cannot apply archive in standby db
              mseberg

              Hello;

               

              Your parameters look wrong.

               

              Example from my test Primary:

               

              *.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

               

              Would consider setting these too:

               

              LOG_ARCHIVE_MAX_PROCESSES

              STANDBY_FILE_MANAGEMENT

               

              Oracle 10 is getting old, like it or not. 

               

               

               

              Best Regards

               

              mseberg

              • 4. Re: Cannot apply archive in standby db
                Mahir M. Quluzade

                Hi,

                 

                As mseberg said, your LOG_ARCHIVE_DEST_2 parameter is wrong, you must change this parameter.

                 

                Regards

                Mahir M. Quluzade

                • 5. Re: Cannot apply archive in standby db
                  Hemant K Chitale

                  There is a mismatch between :

                  >Heartbeat failed to connect to standby 'orcl'. Error is 16009
                  which shows that the standby is 'orcl'

                   

                  but

                  >log_archive_dest_2='service=clone'

                   

                  Check the ORACLE_SID, DB_NAME, DB_UNIQUE_NAME at each database. Check the tnsnames.ora at each server.  Check the listener SERVICES at the standby.

                   

                  Hemant K Chitale

                  • 6. Re: Cannot apply archive in standby db
                    Warrior25

                    Your parameters look wrong.

                     

                    In Primary Database:

                     

                     

                    alter system set service_names=orcl_prd,orcl_dr

                    sho parameter db_unique_name

                    alter system set log_archive_dest_2=service=orcl_dr ARCH ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=<unique_db_name_primary>;

                     

                     

                     

                    In Standby Database:

                     

                     

                    alter system set service_names=orcl_prd,orcl_dr

                    sho parameter db_unique_name

                    alter system set log_archive_dest_2=service=orcl_prd ARCH ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=<unique_db_name_dr>;

                     

                    Add the below in the  tnsnames.ora file at $ORACLE_HOME/network/admin location:

                     

                     

                    orcl_prd =

                      (DESCRIPTION =

                        (ADDRESS_LIST =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = IP_of_Production)(PORT = XXXX))

                        )

                        (CONNECT_DATA =

                          (SERVICE_NAME =orcl_prd)

                        )

                      )

                     

                     

                    orcl_dr =

                      (DESCRIPTION =

                        (ADDRESS_LIST =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = IP_of_DR)(PORT = XXXX))

                        )

                        (CONNECT_DATA =

                          (SERVICE_NAME =orcl_dr)

                        )

                      )

                     

                    Kindly mark this as correct answer if the issue resolves

                    • 7. Re: Cannot apply archive in standby db
                      GTS (DBA)

                      Hi

                       

                      Thanks for all replies.

                       

                      log_archive_dest_1

                      log_archive_dest_2


                      Above  parameters are  defined  something differently by mseberg &  warrior.. NOT set like that. IT is working fine.

                      I want to know the difference.

                       

                      Is it really necessary to set following below ?

                       

                      MSEBERG WROTE

                       

                      *.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


                      $ cat initorcl.ora

                      *.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

                      *.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'

                      *.compatible='10.2.0.5.0'

                      *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/or                                              adata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'

                      *.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'

                      *.db_name='orcl'

                      *.remote_login_passwordfile='EXCLUSIVE'

                      *.sga_target=465567744

                      *.undo_management='AUTO'

                      *.undo_tablespace='UNDOTBS1'

                      *.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

                      fal_server=clone

                      fal_client=orcl

                      db_unique_name=orcl

                      remote_login_passwordfile='exclusive'

                      log_archive_dest_1='location=/u02/primarch'

                      log_archive_dest_2='service=clone'

                      standby_archive_dest='/u02/primarch'

                      standby_file_management=auto

                      log_file_name_convert='/u01/app/oracle/oradata/clone/','/u01/app/oracle/oradata/orcl/'

                      db_file_name_convert='/u01/app/oracle/oradata/clone/','/u01/app/oracle/oradata/orcl/'

                       

                      $ cat initclone.ora

                      *.audit_file_dest='/u01/app/oracle/admin/clone/adump'

                      *.background_dump_dest='/u01/app/oracle/admin/clone/bdump'

                      *.compatible='10.2.0.5.0'

                      *.control_files='/u01/app/oracle/oradata/clone/control01.ctl','/u01/app/oracle/oradata/clone/control02.ctl','/u01/app/oracle/oradata/clone/control03.ctl'

                      *.core_dump_dest='/u01/app/oracle/admin/clone/cdump'

                      *.db_name='orcl'

                      *.remote_login_passwordfile='EXCLUSIVE'

                      *.sga_target=465567744

                      *.undo_management='AUTO'

                      *.undo_tablespace='UNDOTBS1'

                      *.user_dump_dest='/u01/app/oracle/admin/clone/udump'

                      fal_server=orcl

                      fal_client=clone

                      db_unique_name=clone

                      remote_login_passwordfile='exclusive'

                      log_archive_dest_1='location=/u02/stbyarch'

                      log_archive_dest_2='service=orcl'

                      standby_archive_dest=/u02/stbyarch

                      log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/clone/'

                      db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/clone/'

                      standby_file_management=auto

                       

                      >> what is the need to set  like this

                       

                      .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'

                      • 8. Re: Cannot apply archive in standby db
                        GTS (DBA)

                        Hemant K Chitale wrote:

                         

                        There is a mismatch between :

                        >Heartbeat failed to connect to standby 'orcl'. Error is 16009
                        which shows that the standby is 'orcl'

                         

                        but

                        >log_archive_dest_2='service=clone'

                         

                        Check the ORACLE_SID, DB_NAME, DB_UNIQUE_NAME at each database. Check the tnsnames.ora at each server.  Check the listener SERVICES at the standby.

                         

                        Hemant K Chitale


                        >>  In primary database , log_archive_dest_2 was not set. It was problem.

                             Later it was resolved.


                        SQL> show parameter log_archive_dest;

                         

                        NAME                       TYPE        VALUE

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

                        log_archive_dest           string      /u02/prim_archive

                        log_archive_dest_2         string       ****************           -------->         

                        • 9. Re: Cannot apply archive in standby db
                          mseberg

                          Is it really necessary to set following below ?

                           

                           

                           

                           

                           

                          *.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

                           

                           

                           

                           

                          From E10700-02 - 8.1.1 Preparing for a Role Transition

                           

                          "You must define the LOG_ARCHIVE_DEST_n and LOG_

                          ARCHIVE_DEST_STATE_n parameters on each standby database so

                          that when a switchover or failover occurs, all standby sites continue to

                          receive redo data from the new primary database"

                           

                          Best Regards

                           

                          mseberg