1 2 3 Previous Next 36 Replies Latest reply: Apr 3, 2012 3:56 AM by John-MK Go to original post RSS
      • 15. Re: FRA on Physical standby DB
        CKPT
        Post

        SQL> show parameter db_recovery_file_dest
        SQL> select name from v$archived_log where name is not null;

        Identify where archives are going? is it generating out of the FRA? Post here
        • 16. Re: FRA on Physical standby DB
          John-MK
          SQL> show parameter db_recovery_file_dest
          # Same on Primary and Standby
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          db_recovery_file_dest               string      +REDO01
          db_recovery_file_dest_size          big integer 110G
          # Primary....over 2 thousand rows are selecting...cannot post all
          
           SQL> select name from v$archived_log where name is not null;
          
          +REDO01/ab_db2/archivelog/2012_03_29/thread_1_seq_185024.804.779215769
          (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=db1xx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ab_db1_)(INSTANCE_NAME=ab)(SERVER=dedicated)))
          +REDO01/ab_db2/archivelog/2012_03_29/thread_1_seq_185025.651.779216211
          # Standby ..over 6 thousand rows...and all beginnng with this (DESCRIPTION...as an example below line is there for your consideration please. and 19-20 last rows are starting with +REDO01,,as an example one row is pasted
          
          (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=db2xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ab_db2_)(INSTANCE_NAME=ab)(SERVER=dedicated)))
          +REDO01/ab_db1/archivelog/2012_03_29/thread_1_seq_185016.717.779212253
          • 17. Re: FRA on Physical standby DB
            John-MK
            # I performed this as suggested by mseberg and shavananda rao on STANDBY DB.

            ALTER SYSTEM SET log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';

            and now check the result of the below query. As a first glance it seems to me that now Standby is using REDO01 as FRA, isn't it ? because used space in my previous post is 33MB (infact from the last 2,3 days I'm checking it was at 33MB), but now it seems it is using space..
            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          1914         1646
            • 18. Re: FRA on Physical standby DB
              CKPT
              So here it is.

              Archives are generating in right place, but view FRA view is not updating.
              Have you queried v$flash_recovery_area_usage?

              Looks any bug with views. Not sure
              • 19. Re: FRA on Physical standby DB
                mseberg
                Hopefully I did not miss this but this :
                column FILE_TYPE format a20
                select * from v$flash_recovery_area_usage;
                Still works when the standby is in MOUNT

                ( Also check the primary INIT you posted, you left something you might want to be XXX )

                Starting to believe this question is bogus


                Best Regards

                mseberg

                Looks like CKPT and I are on the same page

                Edited by: mseberg on Mar 29, 2012 10:35 AM

                Edited by: mseberg on Mar 29, 2012 10:38 AM
                • 20. Re: FRA on Physical standby DB
                  John-MK
                  #Standby
                  SYS@ab AS SYSDBA> column FILE_TYPE format a20
                  SYS@ab AS SYSDBA> select * from v$flash_recovery_area_usage;
                  
                  FILE_TYPE          PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                  -------------------- ------------------ ------------------------- ---------------
                  CONTROLFILE                   ,03                0          1
                  ONLINELOG                     0                0          0
                  ARCHIVELOG                   2,3                1,88            11
                  BACKUPPIECE                     0                0          0
                  IMAGECOPY                     0                0          0
                  FLASHBACKLOG                     0                0          0
                  # Primary
                  SYS@ab AS SYSDBA> column FILE_TYPE format a20
                  SYS@ab AS SYSDBA> select * from v$flash_recovery_area_usage;
                  
                  FILE_TYPE          PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                  -------------------- ------------------ ------------------------- ---------------
                  CONTROLFILE                   ,02                0          1
                  ONLINELOG                     0                0          0
                  ARCHIVELOG                 11,01                0            57
                  BACKUPPIECE                     0                0          0
                  IMAGECOPY                     0                0          0
                  FLASHBACKLOG                     0                0          0
                  
                  Sorry, Now I have to leave ..dont want to but I highly appreciate your help and also CKPT..  If you want something more to see..please ask me...but I hope now Standby DB is using REDO01  as FRA..as v$recovery_file_dest view suggests over 1GB space...and is increasing ...it was stuck at 33MB as I said before
                  
                  Thank you.
                  Edited by: 910385 on Mar 29, 2012 8:47 AM
                  • 21. Re: FRA on Physical standby DB
                    CKPT
                    As per output 11 archives available. Can you track COMPLETION_TIME of those archives?
                    I believe may be after recent changes they keep generating?

                    Do one thing, may be transactions are slow, as a test process perform number of log switches on primary, check any increase in archive files from above query?

                    It's interesting.
                    Sorry, Now I have to leave ..dont want to but I highly appreciate your help and also CKPT.. If you want something more to see..please ask me...but I hope now Standby DB is using REDO01 as FRA..as v$recovery_file_dest view suggests over 1GB space...and is increasing ...it was stuck at 33MB as I said before
                    You can leave now, but we won't leave ;-) update with above test ASAP.
                    • 22. Re: FRA on Physical standby DB
                      mseberg
                      Not sure at this point, this is worth a look :

                      Correctly configuring the Flash Recovery Area to allow the release of reclaimable space [ID 316074.1]

                      Will kick it around some more and Post questions if I have them.

                      Best Regards

                      mseberg
                      • 23. Re: FRA on Physical standby DB
                        John-MK
                        Hello,

                        Today's query results for your and CKPT's considerations :) Also little review of the discussion.
                        # Whether Standby is SYNC with Primary - check 
                        SYS@ab AS SYSDBA> select thread#,max(sequence#) from v$archived_log group by thread#;
                        
                        # PRIMARY
                           THREAD# MAX(SEQUENCE#)
                        ---------- --------------
                              1        185022
                        
                        SYS@ab AS SYSDBA> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
                        
                        # Standby - # Standby shows no delay
                        
                           THREAD# MAX(SEQUENCE#)
                        ---------- --------------
                              1        185022
                        
                        # db2 -- select name from v$archived_log where name is not null;
                        - - 
                        +REDO01/abq_db2/archivelog/2012_03_21/thread_1_seq_182808.479.778534459
                        (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=db1rq.tb.noris.de)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=abq_db1_XPT.tb.noris.de)(INSTANCE_NAME=ab)(SERVER=dedicated)))
                        - - 
                        2455 rows selected.
                        
                        # db1 -- select name from v$archived_log where name is not null;
                        
                        - - - 
                        (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=db2rq.tb.noris.de)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=abq_db2_XPT.tb.noris.de)(INSTANCE_NAME=ab)(SERVER=dedicated)))
                        +REDO01/abq_db1/archivelog/2012_03_30/thread_1_seq_185234.1019.779273397
                        - - -
                        6488 rows selected.
                        
                        # db1 - show parameter log
                        log_archive_dest_1  string     LOCATION=use_db_recovery_file_dest, valid_for=(ALL_ROLES,ONLINE_LOGFILE)
                        
                        SYS@ab AS SYSDBA> ALTER SYSTEM SET log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';
                        
                        System altered.
                        
                        # db2
                        Space Limit GB       Used-MB Reclaimable-MB
                        -------------- ---------- --------------
                                110         15014            3
                        
                        # db1
                        Space Limit GB       Used-MB Reclaimable-MB
                        -------------- ---------- --------------
                                110          1921         1653
                        
                        # Standby - "After performing one more time ALTER SYSTEM SET log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';"
                        
                        Space Limit GB       Used-MB Reclaimable-MB
                        -------------- ---------- --------------
                                110          3091         2823
                        
                        ## "Now a question is that on STANDY DB, does it using FRA (+REDO01) really? or this USED and Reclaimable space is increasing 
                        because I two,three times Alter system set log_archive_dest_1 --AS ABOVE Secondly, 
                        Why Reclaimable Space on STANDBY is NOT as on PRIMARY (3MB)?
                         I think this leads to my 2nd original question that Automatic deletion of archivelog is NOT WORKING?"
                        
                        # db2 
                        SYS@ab AS SYSDBA> column FILE_TYPE format a20
                        SYS@ab AS SYSDBA> select * from v$flash_recovery_area_usage;
                        
                        FILE_TYPE          PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                        -------------------- ------------------ ------------------------- ---------------
                        CONTROLFILE                   ,02                0          1
                        ONLINELOG                     0                0          0
                        ARCHIVELOG                  9,97                0            52
                        BACKUPPIECE                     0                0          0
                        IMAGECOPY                     0                0          0
                        FLASHBACKLOG                     0                0          0
                        
                        # db1
                        SYS@ab AS SYSDBA> column file_type format a20
                        SYS@ab AS SYSDBA> select * from v$flash_recovery_area_usage;
                        
                        FILE_TYPE          PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                        -------------------- ------------------ ------------------------- ---------------
                        CONTROLFILE                   ,03                0          1
                        ONLINELOG                     0                0          0
                        ARCHIVELOG                  3,13                2,51            15 # USED space in v$recovery_file_dest is 
                        BACKUPPIECE                     0                0          0 increased by 2GB,and Number_of_files has
                        IMAGECOPY                     0                0          0 only 1 more file,initially it was 14 files.
                        FLASHBACKLOG                     0                0          0
                        RMAN-06207: WARNING: 25 objects could not be deleted for DISK channel(s) due
                        RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
                        RMAN-06210: List of Mismatched objects
                        RMAN-06211: ==========================
                        RMAN-06212: Object Type Filename/Handle
                        RMAN-06213: --------------- ---------------------------------------------------
                        RMAN-06214: Archivelog
                        +REDO01/ab_db1/archivelog/2012_03_29/thread_1_seq_185016.717.779212253
                        - - 
                        # All archivelogs represent the PATH of Standby DB, and this RMAN- 06207 is coming from the last 5 days. Every day I perform:
                        CROSSCHECK BACKUP;
                        CROSSCHECK BACKUP OF ARCHIVELOG ALL,
                        DELETE FORCE OBSOLETE;
                        delete noprompt archivelog all backed up 1 times to device type disk;
                        delete FORCE archivelog all backed up 1 times to device type disk;

                        # Yesterday, as per your suggestion, I performed all the possible commands on both the PRIMARY and DB. but every morning i'm getting this error in the Primary db's log file. Only archivelogs are not being delete automatically on STANDBY DB, and from the last 5 days I'm observing its always from the ONE DAY BEFORE which are not DELETED.
                        Thanks and regards

                        Edited by: 910385 on Mar 30, 2012 3:02 AM

                        Edited by: 910385 on Mar 30, 2012 3:07 AM

                        Edited by: 910385 on Mar 30, 2012 3:13 AM
                        • 24. Re: FRA on Physical standby DB
                          mseberg
                          Hello again;
                          I think this leads to my 2nd original question that Automatic deletion of archivelog is NOT WORKING.
                          Frankly I have never trusted it completely. Maybe it just make me nervous but if the FRA is being used on the Standby and it runs out of space Redo applied stops. I know for sure this area is greatly improved in Oracle 11.
                          As a work around I set the RMAN DELETION POLICY
                          RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY
                          ( This command might be slightly different in 10 ) CONFIGURE ARCHIVELOG DELETION POLICY TO [CLEAR | NONE | APPLIED ON STANDBY];

                          And then I manage the deletion with RMAN
                          $ORACLE_HOME/bin/rman target / catalog <user>/<password>@<catalog> << EOF
                           
                           
                          delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-8';
                          I give it a few days as shown.

                          ( I also would rather drive a Stick than an Automatic too )

                          Not sure if this helps :

                          Maintenance Of Archivelogs On Standby Databases [ID 464668.1]

                          But it has a section on automatic maintenance of the archivelogs on standby.

                          This one is more of a review :

                          Space issue in Flash Recovery Area( FRA ) [ID 829755.1]

                          Best Regards

                          mseberg

                          Edited by: mseberg on Mar 30, 2012 5:25 AM
                          • 25. Re: FRA on Physical standby DB
                            John-MK
                            I've checked it, this deletion policy is already set on the Standby DB.
                            # db2 - Primary
                            
                            RMAN> show all;
                            - - 
                            CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
                            
                            # db1 - standby
                            
                            RMAN> show all;
                            - - 
                            CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
                            # The space is more than terabyte and used is only 2,3 gigs..Running out of space is fine ,,,but that time is far away...let the FRA atleast take the archivelogs :)

                            # I do no understand this below cronjob then. It's been created because automatic archivelog deletion is not being done on standby, and it runs every hour at :42 minutes. What this cronjob is doing? Is it deleting by connecting with NOCATALOG (though all the automatic backup scripts are done with CATALOG). Does catalog confused with this cronjob deletion or what? strange..
                            42 * * * * oracle /oracle/sql/archclean_stbdb.sh >/dev/null 2>&1
                            
                            $ORACLE_HOME/bin/rman target / nocatalog << EOF
                            
                            Regards
                            delete noprompt archivelog UNTIL TIME 'SYSDATE-1/24';
                            EOF
                            Edited by: 910385 on Mar 30, 2012 4:46 AM

                            Edited by: 910385 on Mar 30, 2012 4:55 AM
                            • 26. Re: FRA on Physical standby DB
                              mseberg
                              Hello;

                              Looks like "nocatalog" Hmm I never tried this. This might be a very good idea since the Primary and Standby are really the ( assuming everything it working ) database in different roles. By using NOCATALOg there's probably zero chance of screwing up the backup catalog.

                              So with this script you never have more than a day and 42 minutes of archive.



                              Best Regards

                              mseberg
                              • 27. Re: FRA on Physical standby DB
                                John-MK
                                I have delete all the archivelogs from both DB's. I have observed: It started from 7 or 8 in the column NUMBER_FILES on both..equal equal and increased equally on both DB's. From one perspective, I can say that now BOTH db's are using FRA +REDO01. But here Clearly I noticed after every few seconds that PERCENT_SPACE_RECLAIMABLE on PRIMARY remains ZERO all the time but it's INCREASING on STANDBY as can be seen.

                                # It's not a new thing to post right now, but somehow I have a feeling that I'm close to sort it out with your kind guidelines. I think as I set LOG_ARCHIVE_DEST_1 as per your suggestions and it looked to me that STANDBY DB started using FRA, but I did not changed second destination.
                                # Primary
                                SYS@ab AS SYSDBA> select * from v$flash_recovery_area_usage;
                                
                                FILE_TYPE          PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                                -------------------- ------------------ ------------------------- ---------------
                                CONTROLFILE                   ,02                0          1
                                ONLINELOG                     0                0          0
                                ARCHIVELOG                  2,09                0            10
                                BACKUPPIECE                     0                0          0
                                IMAGECOPY                     0                0          0
                                FLASHBACKLOG                     0                0          0
                                
                                # Standby
                                SYS@ab AS SYSDBA> select * from v$flash_recovery_area_usage;
                                
                                FILE_TYPE          PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                                -------------------- ------------------ ------------------------- ---------------
                                CONTROLFILE                   ,03                0          1
                                ONLINELOG                     0                0          0
                                ARCHIVELOG                  2,09                1,67            10
                                BACKUPPIECE                     0                0          0
                                IMAGECOPY                     0                0          0
                                FLASHBACKLOG                     0                0          0
                                # Standby
                                SYS@ab 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_LOGFILES,ALL_ROLES)
                                
                                SYS@ab AS SYSDBA> show parameter log_archive_dest_2
                                
                                NAME                         TYPE      VALUE
                                ------------------------------------ ----------- ------------------------------
                                log_archive_dest_2               string      location="+REDO01",  valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)
                                Might changing log_archive_dest_2 will do the magic, i.e., it start AUTO ARCHIVELOG DELETE after backup, if yes? could you please write what value I should give to log_archive_dest_2 on Standby db

                                Thank you.

                                Regards.
                                • 28. Re: FRA on Physical standby DB
                                  mseberg
                                  Hello
                                   Might changing log_archive_dest_2 will do the magic
                                  This will probably cause an issue. Early in this question I did not understand you were using Data Broker.

                                  Data Broker almost certainly added log_archive_dest_3. You have to be careful with Data Broker as if you mix it with SQL, Broker settings will conflict with the database settings

                                  Best Regards

                                  mseberg
                                  • 29. Re: FRA on Physical standby DB
                                    CKPT
                                    I'm Back :)

                                    I have to go through with the posts, Mean time a question.
                                    Posted: Mar 29, 2012 7:57 PM
                                    @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
                                    Posted: Mar 30, 2012 3:31 PM
                                    # PRIMARY
                                       THREAD# MAX(SEQUENCE#)
                                    ---------- --------------
                                          1        185022
                                     
                                    SYS@ab AS SYSDBA> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
                                     
                                    # Standby - # Standby shows no delay
                                     
                                       THREAD# MAX(SEQUENCE#)
                                    ---------- --------------# Whether Standby is SYNC with Primary - check 
                                    SYS@ab AS SYSDBA> select thread#,max(sequence#) from v$archived_log group by thread#;
                                     
                                    # PRIMARY
                                       THREAD# MAX(SEQUENCE#)
                                    ---------- --------------
                                          1        185022
                                     
                                    SYS@ab AS SYSDBA> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
                                     
                                    # Standby - # Standby shows no delay
                                     
                                       THREAD# MAX(SEQUENCE#)
                                    ---------- --------------
                                          1        185022
                                    Not even one archive in one day? Have a look and reply me.