1 2 3 4 5 Previous Next 60 Replies Latest reply: Aug 17, 2010 8:10 AM by 618702 Go to original post RSS
      • 30. Re: Primary database log_archive_dest_2 parameter change automatically
        727876
        Dear Chinar,

        Yes I can (Now I sound like a Obama supporter :) )
        $ sqlplus sys/<my_password>@STANDBY as sysdba
        
        SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 13 15:49:07 2010
        
        Copyright (c) 1982, 2009, Oracle.  All rights reserved.
        
        
        Connected to:
        Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
        With the Partitioning, OLAP, Data Mining and Real Application Testing options
        I can also successfully connect to the standby using dgmgrl from primary.

        Regards
        • 31. Re: Primary database log_archive_dest_2 parameter change automatically
          Chinar
          user11150436 wrote:
          Dear Chinar,

          Yes I can (Now I sound like a Obama supporter :) )
          $ sqlplus sys/<my_password>@STANDBY as sysdba
          
          SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 13 15:49:07 2010
          
          Copyright (c) 1982, 2009, Oracle.  All rights reserved.
          
          
          Connected to:
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options
          I can also successfully connect to the standby using dgmgrl from primary.

          Regards
          Interesting.Then now execute alter system switch logfile at primary and check alrt.logs again
          • 32. Re: Primary database log_archive_dest_2 parameter change automatically
            727876
            Here is the tnsnames from primary(grid and oracle user look the same):
            STANDBY =
             (DESCRIPTION =
              (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost.domain.com)(PORT = 1521))
              )
              (CONNECT_DATA = (SERVICE_NAME = STANDBY.domain.com))
             )
            
            PRIMARY1 =
              (DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = primaryhost1-vip.domain.com)(PORT = 1521))
                )
               (CONNECT_DATA = (SID = PRIMARY1))
              )
            
            PRIMARY =
              (DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = scan-address.domain.com)(PORT = 1521))
                )
               (CONNECT_DATA = (SERVICE_NAME = PRIMARY.domain.com))
              )
            listener.ora (from grid user)
            LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
            LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by Agent
            LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent
            LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
            
            SID_LIST_LISTENER =
                (SID_LIST =
                    (SID_DESC =
                        (GLOBAL_DBNAME = PRIMARY_DGMGRL.domain.com)
                        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/racdb)
                        (SID_NAME = PRIMARY)
                        (SERVICE_NAME = PRIMARY.domain.com)
                    )
                )
            From standby server.
            tnsnames:
            STANDBY =
             (DESCRIPTION =
              (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost.domain.com)(PORT = 1521))
              )
              (CONNECT_DATA = (SERVICE_NAME = STANDBY))
             )
            
            PRIMARY1 =
              (DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = primaryhost1-vip.domain.com)(PORT = 1521))
                )
               (CONNECT_DATA = (SID = PRIMARY1))
              )
            
            PRIMARY2 =
              (DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = primaryhost2-vip.domain.com)(PORT = 1521))
                )
               (CONNECT_DATA = (SID = PRIMARY2))
              )
            
            PRIMARY =
              (DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = scan-address.domain.com)(PORT = 1521))
                )
               (CONNECT_DATA = (SERVICE_NAME = PRIMARY.domain.com))
              )
            listener.ora
            SID_LIST_LISTENER =
             (SID_LIST =
               (SID_DESC =
                 (GLOBAL_DBNAME = STANDBY.domain.com)
                 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
                 (SID_NAME = STANDBY)
                 (SERVICE_NAME = STANDBY.domain.com)
               )
               (SID_DESC =
                 (GLOBAL_DBNAME = STANDBY_DGMGRL.domain.com)
                 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
                 (SID_NAME = STANDBY)
                 (SERVICE_NAME = STANDBY.domain.com)
               )
            
              )
            
            LISTENER =
              (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost.domain.com)(PORT = 1521))
              )
            Thanks

            Edited by: user11150436 on Aug 13, 2010 4:01 PM
            • 33. Re: Primary database log_archive_dest_2 parameter change automatically
              727876
              Here is the alerts after the switch logfile:
              SQL> alter system switch logfile;
              
              System altered.
              primary alert 20 lines:
              Error 12154 received logging on to the standby
              Errors in file /u01/app/oracle/diag/rdbms/primary/PRIMARY1/trace/PRIMARY1_arc2_6231.trc:
              ORA-12154: TNS:could not resolve the connect identifier specified
              PING[ARC2]: Heartbeat failed to connect to standby 'standby'. Error is 12154.
              Fri Aug 13 15:57:59 2010
              Error 12154 received logging on to the standby
              Errors in file /u01/app/oracle/diag/rdbms/primary/PRIMARY1/trace/PRIMARY1_arc2_6231.trc:
              ORA-12154: TNS:could not resolve the connect identifier specified
              PING[ARC2]: Heartbeat failed to connect to standby 'standby'. Error is 12154.
              Fri Aug 13 16:02:56 2010
              Thread 1 advanced to log sequence 192 (LGWR switch)
                Current log# 12 seq# 192 mem# 0: +DATA/primary/onlinelog/group_12.263.726318819
                Current log# 12 seq# 192 mem# 1: +FRA/primary/onlinelog/group_12.256.726318821
              Fri Aug 13 16:02:56 2010
              Archived Log entry 372 added for thread 1 sequence 191 ID 0xf3c3900f dest 1:
              Fri Aug 13 16:04:00 2010
              Error 12154 received logging on to the standby
              Errors in file /u01/app/oracle/diag/rdbms/primary/PRIMARY1/trace/PRIMARY1_arc2_6231.trc:
              ORA-12154: TNS:could not resolve the connect identifier specified
              PING[ARC2]: Heartbeat failed to connect to standby 'standby'. Error is 12154.
              last 20 lines from standby alert:
              ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='STANDBY';
              ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='STANDBY';
              ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
              ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
              ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
              ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
              ALTER SYSTEM SET db_file_name_convert='+DATA/PRIMARY/datafile/','/u01/app/oracle/oradata/STANDBY/','+DATA/PRIMARY/tempfile/','/u01/app/oracle/oradata/STANDBY/' SCOPE=SPFILE;
              ALTER SYSTEM SET log_file_name_convert='+FRA/PRIMARY/onlinelog/','/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/','+DATA/PRIMARY/onlinelog/','/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/' SCOPE=SPFILE;
              ALTER SYSTEM SET fal_server='PRIMARY' SCOPE=BOTH;
              ALTER SYSTEM SET log_archive_config='dg_config=(STANDBY,PRIMARY)' SCOPE=BOTH;
              Fri Aug 13 15:15:51 2010
              RFS[5]: Assigned to RFS process 26970
              RFS[5]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 22629
              Primary database is in MAXIMUM PERFORMANCE mode
              RFS[5]: Selected log 31 for thread 1 sequence 189 dbid -264958047 branch 726318599
              Fri Aug 13 15:15:51 2010
              Archived Log entry 13 added for thread 1 sequence 189 ID 0xf3c3900f dest 1:
              RFS[5]: Selected log 31 for thread 1 sequence 190 dbid -264958047 branch 726318599
              Fri Aug 13 15:16:00 2010
              Archived Log entry 14 added for thread 1 sequence 190 ID 0xf3c3900f dest 1:
              thanks
              • 34. Re: Primary database log_archive_dest_2 parameter change automatically
                618702
                Dear user11150436,

                I believe that there is still something wrong with your naming conventions or tnsnames.ora file. Primary database's data guard configuration can not connect to the standby database and can not send archivelogs.

                I will read the thread all over again to understand it.

                Ogan
                • 35. Re: Primary database log_archive_dest_2 parameter change automatically
                  727876
                  Thanks Ogan,

                  it is much appreciated!

                  Regards
                  • 36. Re: Primary database log_archive_dest_2 parameter change automatically
                    727876
                    I've removed the configuration again from dgmgrl and doubled checked my setup.
                    Recreated the configuration and still same error.

                    I've been almost 8 hours straight busy with this error and am sure I'm staring blindly into the problem and working in circles.

                    Thanks everybody for the help thus far.

                    I'm almost set to leave work will be back Monday morning to struggle further with this issue and hopefully with a fresh brain I will be able to spot something new.
                    Will post all new findings and recap on any other follow-ups on Monday as well.

                    Enjoy the weekend.

                    Cheers
                    • 37. Re: Primary database log_archive_dest_2 parameter change automatically
                      Richard Harrison .
                      Hi Again,
                      I know you said you'd already checked this but are you sure?

                      tnsping standby obviously works from some of your posts

                      but does tnsping standby.domain.com actually work

                      It's not in your tnsnames file so it should fail to resolve and throw up the 12154 error

                      Failing that how many tnsnames files have you got - is the database somehow picking up a different one to the one you are using when doing the tests?

                      TNS_ADMIN - something in /etc for example?

                      Cheers,
                      Harry
                      • 38. Re: Primary database log_archive_dest_2 parameter change automatically
                        727876
                        Dear Harry,

                        thanks for your response.

                        tnsping standby.domain.com does not work I get the following error:
                        TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 16-AUG-2010 09:07:19
                        
                        Copyright (c) 1997, 2009, Oracle.  All rights reserved.
                        
                        Used parameter files:
                        /u01/app/oracle/product/11.2.0/racdb/network/admin/sqlnet.ora
                        
                        TNS-03505: Failed to resolve name
                        But my log_archive_dest_2 is set to use the service without the domain as follow:
                        log_archive_dest_2                   string      service="standby", LGWR ASYNC
                                                                         NOAFFIRM delay=0 optional comp
                                                                         ression=disable max_failure=0
                                                                         max_connections=1 reopen=300 d
                                                                         b_unique_name="STANDBY" net_ti
                                                                         meout=30, valid_for=(all_logfi
                                                                         les,primary_role)
                        What service does DGMGRL use? Doesn't it use the STANDBY_DGMGRL.domain.com?
                        I've added the following to the tnsnames.ora files(and am sure it should not be necessary) and am able to tnsping both:
                        STANDBY.domain.com =
                         (DESCRIPTION =
                          (ADDRESS_LIST =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost.domain.com)(PORT = 1521))
                          )
                          (CONNECT_DATA = (SERVICE_NAME = STANDBY.domain.com))
                         )
                        
                        STANDBY_DGMGRL.domain.com =
                         (DESCRIPTION =
                          (ADDRESS_LIST =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost.domain.com)(PORT = 1521))
                          )
                          (CONNECT_DATA = (SERVICE_NAME = STANDBY.domain.com))
                         )
                        But this still did not resolve the issue. Is there somewhere else where I must set this?

                        I have set only 2 tnsnames.ora files.
                        The one is in the ORACLE_HOME/network/admin and the other in the GRID_HOME/network/admin.
                        They are set exactly the same and I am able to tnsping "STANDBY" and sqlplus into "STANDBY" from both oracle and grid users.
                        After the entries that I've added above in the tnsnames.ora I am able to tnsping and sqlplus both with the domain.

                        Regards

                        Edited by: user11150436 on Aug 16, 2010 11:11 AM
                        • 39. Re: Primary database log_archive_dest_2 parameter change automatically
                          727876
                          I have dropped the standby database and cleared all configurations and started over again.

                          I have not configured dgmgrl yet but only gave the following command on the standby:
                          ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
                          If I archive the current log file I get the same error in the primary alert log:
                          ORA-12154: TNS:could not resolve the connect identifier specified
                          PING[ARC2]: Heartbeat failed to connect to standby 'STANDBY'. Error is 12154.
                          There are no errors in the standby alert log.

                          I am able to tnsping and sqlplus from primary to standby and vice versa.

                          My setup on the Primary is as follow:
                          tnsnames.ora
                          STANDBY =
                            (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost)(PORT = 1521))
                              (CONNECT_DATA =
                                (SERVER = DEDICATED)
                                (SERVICE_NAME = STANDBY.domain.com)
                              )
                            )
                          listener.ora (of grid user)
                          LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by Agent
                          LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent
                          LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
                          LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
                          ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
                          ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
                          ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent
                          ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent
                          log_archive_dest_2                   string      SERVICE=STANDBY ASYNC DB_UNIQU
                                                                           E_NAME=STANDBY VALID_FOR=(ONLI
                                                                           NE_LOGFILE,PRIMARY_ROLE)
                          the Standby look as follow:
                          tnsnames.ora
                          PRIMARY =
                            (DESCRIPTION =
                              (ADDRESS_LIST =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = scan-address)(PORT = 1521))
                              )
                             (CONNECT_DATA = (SERVICE_NAME = PRIMARY.domain.com))
                            )
                          listener.ora
                          LISTENER =
                            (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost.domain.com)(PORT = 1521))
                            )
                          
                          SID_LIST_LISTENER =
                            (SID_LIST =
                              (SID_DESC =
                                (GLOBAL_DBNAME = STANDBY.domain.com)
                                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
                                (SID_NAME = STANDBY)
                              )
                            )
                          The Standby's local_listener and remote_listener are set as follow:
                          local_listener                       string      (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standbyhost.domain.com)(PORT=1521)))
                          
                          remote_listener                      string      scan-address.domain.com:1521
                          Any help or suggestion will be appreciated.

                          Thanks
                          • 40. Re: Primary database log_archive_dest_2 parameter change automatically
                            727876
                            OK I got this resolved :), there was a small typo

                            The log_archive_dest_2 parameter was set as follow on primary:
                            alter system set log_archive_dest_2='SERVICE=STANDBY ASYNC DB_UNIQUE_NAME=STANDBY VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';
                            I changed it to the following and the problem went away:
                            alter system set log_archive_dest_2='SERVICE=STANDBY ASYNC DB_UNIQUE_NAME=STANDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)';
                            Thanks for all the help with this everyone, it is appreciated.

                            Regards.
                            • 41. Re: Primary database log_archive_dest_2 parameter change automatically
                              618702
                              Dear user11150436,

                              That is great to hear that. Now have fun!

                              Regards.

                              Ogan
                              • 42. Re: Primary database log_archive_dest_2 parameter change automatically
                                727876
                                error returned!!!

                                After above mentioned change the log files were carried across:

                                Primary alert:
                                Mon Aug 16 14:10:16 2010
                                ALTER SYSTEM SET log_archive_dest_2='SERVICE=STANDBY ASYNC DB_UNIQUE_NAME=STANDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH;
                                Mon Aug 16 14:10:32 2010
                                ******************************************************************
                                LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
                                ******************************************************************
                                Mon Aug 16 14:10:32 2010
                                Thread 1 advanced to log sequence 291 (LGWR switch)
                                  Current log# 12 seq# 291 mem# 0: +DATA/xxx/onlinelog/group_12.263.726318819
                                  Current log# 12 seq# 291 mem# 1: +FRA/xxx/onlinelog/group_12.256.726318821
                                Mon Aug 16 14:10:32 2010
                                Archived Log entry 605 added for thread 1 sequence 290 ID 0xf3c3900f dest 1:
                                LNS: Standby redo logfile selected for thread 1 sequence 290 for destination LOG_ARCHIVE_DEST_2
                                LNS: Standby redo logfile selected for thread 1 sequence 291 for destination LOG_ARCHIVE_DEST_2
                                Mon Aug 16 14:10:33 2010
                                Standby Alert:
                                Mon Aug 16 14:10:36 2010
                                RFS[5]: Assigned to RFS process 23392
                                RFS[5]: Identified database type as 'physical standby': Client is ARCH pid 6235
                                RFS[5]: Opened log for thread 1 sequence 287 dbid -264958047 branch 726318599
                                Archived Log entry 9 added for thread 1 sequence 287 rlc 726318599 ID 0xf3c3900f dest 2:
                                I've enabled dgmgrl and I get the error again in the alert log of the primary.
                                I am really out of answers now.

                                Edited by: user11150436 on Aug 16, 2010 2:45 PM

                                Edited by: user11150436 on Aug 16, 2010 2:48 PM
                                • 43. Re: Primary database log_archive_dest_2 parameter change automatically
                                  727876
                                  I have just changed the parameter again (although it was the same still) to test:

                                  Primary Alert
                                  ALTER SYSTEM SET log_archive_dest_2='SERVICE=STANDBY ASYNC DB_UNIQUE_NAME=STANDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH;
                                  Mon Aug 16 14:57:39 2010
                                  ALTER SYSTEM ARCHIVE LOG
                                  Mon Aug 16 14:57:42 2010
                                  ******************************************************************
                                  LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
                                  ******************************************************************
                                  Mon Aug 16 14:57:42 2010
                                  Thread 1 advanced to log sequence 295 (LGWR switch)
                                    Current log# 13 seq# 295 mem# 0: +DATA/xxx/onlinelog/group_13.267.726318825
                                    Current log# 13 seq# 295 mem# 1: +FRA/xxx/onlinelog/group_13.260.726318825
                                  Mon Aug 16 14:57:42 2010
                                  Archived Log entry 620 added for thread 1 sequence 294 ID 0xf3c3900f dest 1:
                                  LNS: Standby redo logfile selected for thread 1 sequence 294 for destination LOG_ARCHIVE_DEST_2
                                  LNS: Standby redo logfile selected for thread 1 sequence 295 for destination LOG_ARCHIVE_DEST_2
                                  Mon Aug 16 14:57:54 2010
                                  Error 12154 received logging on to the standby
                                  Errors in file /u01/app/oracle/diag/rdbms/gebeta/PRIMARY1/trace/PRIMARY1_arc2_6231.trc:
                                  ORA-12154: TNS:could not resolve the connect identifier specified
                                  PING[ARC2]: Heartbeat failed to connect to standby 'STANDBY'. Error is 12154.
                                  Standby Alert
                                  Mon Aug 16 14:57:42 2010
                                  RFS[6]: Assigned to RFS process 24093
                                  RFS[6]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 25097
                                  Primary database is in MAXIMUM PERFORMANCE mode
                                  RFS[6]: Selected log 31 for thread 1 sequence 294 dbid -264958047 branch 726318599
                                  Mon Aug 16 14:57:43 2010
                                  Archived Log entry 11 added for thread 1 sequence 294 ID 0xf3c3900f dest 1:
                                  RFS[6]: Selected log 31 for thread 1 sequence 295 dbid -264958047 branch 726318599
                                  Before I changed the parameter there was never any activity in the Standby alert log when I issued a "archive log current" command.
                                  The alert log file from the Standby does show activity now but I still see the error on the primary.

                                  What does this mean exactly?
                                  • 44. Re: Primary database log_archive_dest_2 parameter change automatically
                                    Richard Harrison .
                                    Hi Again,
                                    add

                                    STANDBY.domain.com =
                                    (DESCRIPTION =
                                    (ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost)(PORT = 1521))
                                    (CONNECT_DATA =
                                    (SERVER = DEDICATED)
                                    (SERVICE_NAME = STANDBY.domain.com)
                                    )
                                    )

                                    To your tnsnames on the primary - does the error go away?

                                    Cheers,
                                    Harry