1 2 3 Previous Next 36 Replies Latest reply: Apr 3, 2012 3:56 AM by John-MK RSS

    FRA on Physical standby DB

    John-MK
      Hi,

      Data guard setup: 10.2.0.5 on RHL
      db2- Primary
      db1- Physical Standby

      Question: db1 is not using REDO01 as Flash Recovery Area? Automatic deletion of archivelogs (Feature Oracle Managed Files) is not working?
      SYS@tj2 AS SYSDBA> select space_limit/1024/1024/1024 "Space Limit GB",SPACE_USED/1024/1024 "Used-MB",SPACE_RECLAIMABLE/1024/1024 "Reclaimable-MB" from v$recovery_file_dest;
      # db2
      Space Limit GB       Used-MB Reclaimable-MB
      -------------- ---------- --------------
              110          11244            3
      # db1
      Space Limit GB       Used-MB Reclaimable-MB
      -------------- ---------- --------------
              110            33            0
      # db1 - show parameter log
      log_archive_dest_1               string      LOCATION=use_db_recovery_file_dest, valid_for=(ALL_ROLES,ONLINE_LOGFILE)
      log_archive_dest_2               string      LOCATION=use_db_recovery_file_dest, valid_for=(STANDBY_ROLE,STANDBY_LOGFILE)
      
      # Following error comes in db2 - PRIMARY DB but path shows db1
      RMAN-06207: Warning 20 objects were not delete ...
      RMAN-06214: Archivelog    
      +REDO01/tj_db1/archivelog/2012_03_28/thread_1_seq_184788.779.779125915
      Every day I'm getting this above error in my log file on PRIMARY DB, and the PATH above direct to the PATH of STANDBY DB. From the last 5 days I am observing this, meanwhile everyday I perform DELELTE noprompt ARCHIVELOG ALL BACKUP 1 TIMES TO DISK; -- (The same line is written in the .sh script on both db2 and standby db2- automatic backup everynight) RMAN CATALOG is being used. CROSSCHECK BACKUP OF ARCHIVELOG ALL, CROSSCHECK backup,DELTE FORCE OBSOLETE, etc I perform every day that may be tomorrow it do not come and CATALOG is Synchoronized, but it doesn't.

      LINE IN the backup script -->> backup archivelog all not backed up 1 times; retention policy 28 days.
      # Meanwhile a cronjob is created to delete archive logs on db1
      
      $ORACLE_HOME/bin/rman target / nocatalog 
      delete noprompt archivelog UNTIL TIME 'SYSDATE-1/24';
      Thanks a lot for the help.

      Regards.

      Edited by: 910385 on Mar 29, 2012 4:21 AM
        • 1. Re: FRA on Physical standby DB
          mseberg
          Hello;

          OK
           db1 is not using REDO01 as Flash Recovery Area?
          All my Oracle 10's are long gone but all my Standby use FRA because of this in the spfile on the standby :
          db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
          
          log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
          You can also use RMAN to remove old archive from the standby :
          echo "Starting RMAN..."
          $ORACLE_HOME/bin/rman target / catalog <user>/<password>@<catalog> << EOF
          
          
          delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-8';
          
          
          exit
          EOF
          
          
          echo `date`
          #
          echo
          echo 'End of archive cleanup on STANDBY'
          I'm thinking the mismatched archivelogs were created by standby. ( rman delete fails due to mismatched status of backup pieces )

          Run delete obsolete from primary as well as standby to remove the mismatched status archivelog files.

          You might have to run crosscheck on both the Primary and the Standby.

          You might be able to avoid this by using "DELETE FORCE OBSOLETE;"

          You can try to crosscheck a single archivelog as a test too.

          Check this note :

          RMAN-06207 and RMAN-06208 deleting RMAN backups [ID 290559.1]



          Also these jump out at me on db1

          log_archive_dest_1
          log_archive_dest_2

          I would expect something like this ( a service instead of a location )
          log_archive_dest_2='SERVICE
          
          log_archive_dest_2='SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
          
          {code]
          
          
          
          
          Best Regards
          
          mseberg
          
          Edited by: mseberg on Mar 29, 2012 6:29 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          • 2. Re: FRA on Physical standby DB
            John-MK
            Hello,

            Thank you very much for the suggestion.

            After reading your post, till now I was checking everything again and thinking. Actually, as I wrote in my first post I use every day this crosscheck and delete force delete,etc.

            - - I've observed closely the last 3,4 log files which has errors on Primary db of db2. And I've noticed that whenever Primary db2 has to remove archivelogs from Standby its fails. It always remove the archivelogs when the path is like '+REDO01/tj2_db2/archivelog but it always fails to delete when the path shows Standby db archivelogs. YES, I might not have performed Crosscheck, delete force, etc on BOTH,,,I think I only perform these command on Primary only.

            Any idea/suggestion about my first question please? Physical standby DB is not using REDO01 as Flash Recovery Area.

            Thanks

            Regards,
            • 3. Re: FRA on Physical standby DB
              mseberg
              Yes on the first question.

              Was on my way out the door and I have added content to my first answer.

              Can you post both init files Primary and Standby?

              Best Regards

              mseberg
              • 4. Re: FRA on Physical standby DB
                John-MK
                I think these parameter looks the same as yours on Standby db, isn't it....here its +REDO01 instead
                SYS@tj1 AS SYSDBA> show parameter db_recovery_file_dest
                
                NAME                         TYPE      VALUE
                ------------------------------------ ----------- ------------------------------
                db_recovery_file_dest               string      +REDO01
                db_recovery_file_dest_size          big integer 110G
                
                SYS@tj1 AS SYSDBA> sho parameter log_archive_dest_1
                
                NAME                         TYPE      VALUE
                ------------------------------------ ----------- ------------------------------
                log_archive_dest_1               string      LOCATION=use_db_recovery_file_dest, valid_for=(ALL_ROLES,ONLINE_LOGFILE)
                
                SYS@tj1 AS SYSDBA> select space_limit/1024/1024/1024 "Space Limit GB",SPACE_USED/1024/1024 "Used-MB",SPACE_RECLAIMABLE/1024/1024 "Reclaimable-MB" from v$recovery_file_dest;
                
                Space Limit GB       Used-MB Reclaimable-MB
                -------------- ---------- --------------
                        110            33            0
                This Used-MB of 33 I am checking from the last few days...on Physical standby db
                Edited by: 910385 on Mar 29, 2012 5:45 AM


                AS SYSDBA> show parameter spfile
                NAME                         TYPE      VALUE
                ------------------------------------ ----------- ------------------------------
                spfile                         string      +DATA01/ab_db2/spfileab.ora_241
                spfile                         string      +DATA01/ab_db1/spfileab.ora_224
                Can you please tell which parameters you want to see/compare from both Primary and Standby, so that I paste that.

                Edited by: 910385 on Mar 29, 2012 5:50 AM

                Edited by: 910385 on Mar 29, 2012 5:53 AM
                • 5. Re: FRA on Physical standby DB
                  mseberg
                  OK

                  I was looking for this :
                  log_archive_dest_1
                  Can you post log_archive_dest_1 and log_archive_dest_2 from both Primary and Standby?

                  Also, are you sure your databases are in sync?

                  Best Regards

                  mseberg

                  Edited by: mseberg on Mar 29, 2012 8:00 AM
                  • 6. Re: FRA on Physical standby DB
                    John-MK
                    # PRIMARY DB
                    
                    SYS@tj2 AS SYSDBA> show parameter log_archive_dest_1
                    
                    NAME                         TYPE      VALUE
                    ------------------------------------ ----------- ------------------------------
                    log_archive_dest_1               string      LOCATION=use_db_recovery_file_dest, valid_for=(ALL_ROLES,ONLINE_LOGFILE)
                    
                    SYS@tj2 AS SYSDBA> show parameter log_archive_dest_2
                    
                    NAME                         TYPE      VALUE
                    ------------------------------------ ----------- ------------------------------
                    log_archive_dest_2               string      LOCATION=use_db_recovery_file_dest, valid_for=(STANDBY_ROLE,STANDBY_LOGFILE)
                    # Standby DB
                    
                    SYS@tj1 AS SYSDBA> show parameter log_archive_dest_1
                    
                    NAME                         TYPE      VALUE
                    ------------------------------------ ----------- ------------------------------
                    log_archive_dest_1               string      LOCATION=use_db_recovery_file_dest, valid_for=(ALL_ROLES,ONLINE_LOGFILE)
                    
                    SYS@tj1 AS SYSDBA> show parameter log_archive_dest_2
                    
                    NAME                         TYPE      VALUE
                    ------------------------------------ ----------- ------------------------------
                    log_archive_dest_2               string      location="+REDO01",  valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)
                    • 7. Re: FRA on Physical standby DB
                      mseberg
                      OK

                      Probably a stupid question(s) but....

                      1. How long had this been running?

                      2. Are you using more than log_archive_dest_n 1 and 2? By that Are you using additional log_archive_dest_n?

                      3. How are you check SYNC from the primary?

                      Best Regards

                      mseberg

                      Edited by: mseberg on Mar 29, 2012 8:08 AM
                      • 8. Re: FRA on Physical standby DB
                        John-MK
                        # This is the only difference I can see on PRIMARY DB..dest3 is nothing on Standby.
                        
                        log_archive_dest_3       string  service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX)(PORT=1521)))
                                                                    (CONNECT_DATA=(SERVICE_NAME=XX)(INSTANCE_NAME=ab)(SERVER=dedicated)))",   
                                                                    LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1   reopen=300 db_unique_name="ab_db1" 
                                                                    register net_timeout=180  valid_for=(online_logfile,primary_role)
                        v$database on both DB's says since 2007. So probably since then, but I'm not sure...I am new here in this setup :) Both DB's have same DBID...sharing one instance...of course

                        Edited by: 910385 on Mar 29, 2012 6:16 AM

                        Edited by: 910385 on Mar 29, 2012 6:18 AM
                        3. How are you check SYNC from the primary?
                        Sorry, I dont understand this or dont know this thing?

                        Edited by: 910385 on Mar 29, 2012 6:19 AM
                        • 9. Re: FRA on Physical standby DB
                          mseberg
                          OK

                          This answers how Data Guard is working. It also confirms a setup issue with Data Guard.

                          Your Standby needs a SERVICE entry. Otherwise when it assumes the Primary role it cannot ship REDO to the former primary, the new Standby.

                          Please create new PFILES from your SPFILE on both the Primary and Standby and post. I know of no other way to get your other issue out.

                          Best Regards

                          mseberg
                          • 10. Re: FRA on Physical standby DB
                            John-MK
                            ...
                            @mseberg
                            I am sorry, right now I have to remove pfile contents ...
                            Edited by: 910385 on Mar 29, 2012 8:45 AM

                            Edited by: 910385 on Mar 29, 2012 8:45 AM
                            • 11. Re: FRA on Physical standby DB
                              CKPT
                              Late Response ;-)

                              Every day I'm getting this above error in my log file on PRIMARY DB
                              is your standby SYNC with primary?
                              May be there would be little delay on standby, thats why latest archives not being deleted, Is this RMAN job in primary scheduled or executing manually?


                              Can you please reset your log archive destination on standby as below?

                              alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST';

                              & now post


                              select     name
                              ,     floor(space_limit / 1024 / 1024) "Size MB"
                              ,     ceil(space_used / 1024 / 1024) "Used MB"
                              from     v$recovery_file_dest
                              order by name
                              /
                              • 12. Re: FRA on Physical standby DB
                                mseberg
                                Thanks reviewing standby INIT

                                Very low on time, about to go into a long meeting.

                                Quickly :

                                audit_trail - Watch out using this on Read_only standby on Oracle 10 ( Fixed in Oracle 11 ) Same issue on Primary

                                control_files = ( Noticed only one copy, consider changing ) Same issue on Primary

                                FAL_CLIENT - would expect Oracle Net service name Same issue on Primary

                                fal_server - would expect Oracle Net service name Same issue on Primary

                                log_archive_dest_1 - Would expect something like :

                                log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'

                                log_archive_dest_2 - Two entries might cause conflict and issues

                                cha.log_archive_dest_2='location="+REDO01"',' valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'

                                ASM part OK -



                                log_archive_dest_state_3 - Not set as stated before

                                Would expect something like

                                log_archive_dest_3='SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'



                                log_archive_dest_state_3 - Thinking it should be DEFER

                                log_file_name_convert='null','null' ( This cannot be good ) Same issue on Primary


                                Would consider removing Data Broker until all issue are ironed out.


                                How to Safely Remove a Data Guard Broker Configuration [ID 261336.1]


                                Sorry, out of time for now. ( My friend CKPT is of great help!! )


                                Best Regards

                                mseberg

                                Edited by: mseberg on Mar 29, 2012 9:08 AM
                                • 13. Re: FRA on Physical standby DB
                                  John-MK
                                  Hello,

                                  Better late than never :)

                                  Yes, you are right I think. I also have heard that Standby DB is little behind than PRIMARY.
                                  is your standby SYNC with primary?
                                  How can I find out that please, no idea about that?

                                  I performed ALTER SYSTEM SET log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST';
                                  System Altered.
                                  NAME                         TYPE      VALUE
                                  ------------------------------------ ----------- ------------------------------
                                  log_archive_dest_1               string      location=USE_DB_RECOVERY_FILE_DEST
                                  
                                  # Before ALTER SYSTEM, it was like that.. 
                                  NAME                         TYPE      VALUE
                                  ------------------------------------ ----------- ------------------------------
                                  log_archive_dest_1               string      LOCATION=use_db_recovery_file_dest, valid_for=(ALL_ROLES,ONLINE_LOGFILE)
                                  and the result of your query is same as I posted in the very 1st post:
                                                                          Size MB    Used MB
                                  +REDO01                     112640          33
                                  @Shivananda Rao....Thanks...THe result is same
                                  ## PRIMARY DB
                                  select thread#,max(sequence#) from v$archived_log group by thread#;
                                  
                                     THREAD# MAX(SEQUENCE#)
                                  ---------- --------------
                                        1        185022
                                  # Standby
                                  select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
                                  
                                     THREAD# MAX(SEQUENCE#)
                                  ---------- --------------
                                        1        185022
                                  @mseberg
                                  Thanks alot for your time. Who knows, might I have to continue it from here tomorrow, if it doesnt get solve today :)

                                  Still at the MAIN QUESTION: Standby DB is not using +REDO01 as Flash Recovery Area.



                                  Edited by: 910385 on Mar 29, 2012 7:29 AM

                                  Edited by: 910385 on Mar 29, 2012 7:31 AM

                                  Edited by: 910385 on Mar 29, 2012 7:37 AM

                                  Edited by: 910385 on Mar 29, 2012 7:38 AM
                                  • 14. Re: FRA on Physical standby DB
                                    Shivananda Rao
                                    In your primary database,
                                    *.log_archive_dest_1='LOCATION=use_db_recovery_file_dest','valid_for=(ALL_ROLES,ONLINE_LOGFILE)'
                                    *.log_archive_dest_2='LOCATION=use_db_recovery_file_dest','valid_for=(STANDBY_ROLE,STANDBY_LOGFILE)'
                                    *.log_archive_dest_3='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxx)(INSTANCE_NAME=cha)(SERVER=dedicated)))"','   LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1   reopen=300 db_unique_name="ab_db1" register net_timeout=180  valid_for=(online_logfile,primary_role)'
                                    Any specific reason for having set log_archive_dest_2 again to the FRA ? Already you are having the archives stored to the FRA using dest 1, again why the need to have it getting stored at the same location using dest 2 ?

                                    Both primary and standby show the same dbid as your standby is nothing but a mirrored copy of the primary. So, please check if the standby is in sync with the primary.

                                    Coming to your standby database pflie:
                                    log_archive_dest_1='LOCATION=use_db_recovery_file_dest','valid_for=(ALL_ROLES,ONLINE_LOGFILE)'
                                    ab.log_archive_dest_2='location="+REDO01"',' valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
                                    set dest 1 as below
                                    log_archive_dest_1='location=use_db_recovery_file_dest
                                    valid_for=(all_logfiles,all_roles)
                                    db_unique_name=ab_db'
                                    set dest 2 as below
                                    log_archive_dest_2='service=<net oracle service name of primary database>
                                    valid_for=(online_logfiles,primary_role)
                                    db_unique_name=ab_db2'
                                    Parameters are wrongly configured on the standby database.
                                    is your standby SYNC with primary?
                                    How can I find out that please, no idea about that?
                                    On primary run,
                                    sql>select thread#,max(sequence#) from v$archived_log group by thread#;
                                    On standby run,
                                    sql>select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
                                    check if both the queries give you same result or may be a difference of 1 or 2 archives.

                                    Edited by: Shivananda Rao on Mar 29, 2012 8:02 PM
                                    1 2 3 Previous Next