14 Replies Latest reply on Nov 27, 2018 6:06 AM by 3401456

    In recovery catalog no found backups obsolete

    3401456

      RMAN> connect target /

       

       

      connected to target database: XXXXXXXX (DBID=2761987891)

       

       

      RMAN> connect catalog USER/PASSWORD@CATALOG

       

       

      connected to recovery catalog database

       

       

      RMAN> report obsolete;

       

       

      RMAN retention policy will be applied to the command

      RMAN retention policy is set to recovery window of 17 days

      no obsolete backups found

       

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

       

      WITHOUT CONNECTION TO CATALOG

       

       

      RMAN> connect target /

       

       

      connected to target database: xxxxxx (DBID=2761987891)

       

       

      RMAN> report obsolete;

       

       

      using target database control file instead of recovery catalog

      RMAN retention policy will be applied to the command

      RMAN retention policy is set to recovery window of 17 days

      Report of obsolete backups and copies

      Type                 Key    Completion Time    Filename/Handle

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

      Backup Set           11434  30-SEP-18

        Backup Piece       12796  30-SEP-18          /zfssa/xxxxxx/backup1/lutecco6_1_1

      Backup Set           11436  30-SEP-18

        Backup Piece       12798  30-SEP-18          /zfssa/xxxxxx/backup1/lvtecco9_1_1

      Backup Set           11437  30-SEP-18

        Backup Piece       12799  30-SEP-18          /zfssa/xxxxxx/backup1/m0teccob_1_1

      Backup Set           11439  30-SEP-18

        Backup Piece       12801  30-SEP-18          /zfssa/xxxxxx/backup1/m1teccoe_1_1

      Backup Set           11438  30-SEP-18

        Backup Piece       12800  30-SEP-18          /zfssa/xxxxxx/backup1/m2teccoh_1_1

      Backup Set           11440  30-SEP-18

        Backup Piece       12802  30-SEP-18          /zfssa/xxxxxx/backup1/m3teccoj_1_1

      ....

      ...

      ...

      Backup Set           11516  03-OCT-18

        Backup Piece       12878  03-OCT-18          /zfssa/xxxxxx/backup1/ogtemupk_1_1

       

      RMAN> show all;

       

      RMAN configuration parameters for database with db_unique_name XXXXXX are:

      CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 17 DAYS;

      CONFIGURE BACKUP OPTIMIZATION OFF; # default

      CONFIGURE DEFAULT DEVICE TYPE TO DISK;

      CONFIGURE CONTROLFILE AUTOBACKUP ON;

      CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

      CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO COMPRESSED BACKUPSET;

      CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

      CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

      CONFIGURE MAXSETSIZE TO UNLIMITED; # default

      CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

      CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

      CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

      CONFIGURE DB_UNIQUE_NAME 'XXXXXX' CONNECT IDENTIFIER  'XXXXXXX';

      CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

      CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

      CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/zfssa/xxxxxxx/backup1/snapcf_XXXXXXX.f';

       

      ¿¿¿???

        • 1. Re: In recovery catalog no found backups obsolete
          Dude!

          Use the RESYNC CATALOG command to perform a full resynchronization of metadata in a recovery catalog schema with metadata in a target database control file.

          • 2. Re: In recovery catalog no found backups obsolete
            3401456

            In the script of backup it make a resync catalog in each execution but i had executed i and the result is the same.

             

            RMAN> connect target /

             

             

            connected to target database: XXXXXXXX (DBID=2761987891)

             

             

            RMAN> connect catalog USER/PASSWORD@CATALOG

             

             

            connected to recovery catalog database

             

             

            RMAN> resync catalog

            2> ;

             

             

            starting full resync of recovery catalog

            full resync complete

             

             

            RMAN> report obsolete;

             

             

            RMAN retention policy will be applied to the command

            RMAN retention policy is set to recovery window of 17 days

            Report of obsolete backups and copies

            Type                 Key    Completion Time    Filename/Handle

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

            Backup Set           29839408 05-OCT-18

              Backup Piece       29839411 05-OCT-18          +RECP/XXXXXXXX/AUTOBACKUP/2018_10_05/s_988684918.5788.988684921

             

             

            RMAN> connect target /

             

             

            connected to target database: SEC01PRO (DBID=2761987891)

             

            RMAN> report obsolete;

             

             

            RMAN retention policy will be applied to the command

            RMAN retention policy is set to recovery window of 17 days

            Report of obsolete backups and copies

            Type                 Key    Completion Time    Filename/Handle

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

            Backup Set           11434  30-SEP-18

              Backup Piece       12796  30-SEP-18          /zfssa/XXXXXXXX/backup1/lutecco6_1_1

            Backup Set           11436  30-SEP-18

              Backup Piece       12798  30-SEP-18          /zfssa/XXXXXXXX/backup1/lvtecco9_1_1

            Backup Set           11437  30-SEP-18

            .....

            ....

            Backup Set           11516  03-OCT-18

              Backup Piece       12878  03-OCT-18          /zfssa/XXXXXXXX/backup1/ogtemupk_1_1

            Backup Set           11591  05-OCT-18

              Backup Piece       12953  05-OCT-18          +RECP/XXXXXXXX/AUTOBACKUP/2018_10_05/s_988684918.5788.988684921

            • 3. Re: In recovery catalog no found backups obsolete
              Dude!

              RMAN verifies the backup piece header before cataloging. Do the backups at /zfssa still exist? What does a RMAN crosscheck show you?

              • 4. Re: In recovery catalog no found backups obsolete
                3401456

                /zfssa exist and i have access to the FS

                 

                 

                 

                RMAN>

                echo set off

                 

                 

                RMAN>

                connected to target database: XXXXXX(DBID=2761987891)

                 

                 

                RMAN>

                connected to recovery catalog database

                 

                 

                RMAN>

                echo set on

                 

                 

                 

                 

                RMAN>     # We change the retention policy to 30 days. Date: 2017-06-06. Author: David Loinaz - AEG

                2>     # If changed for standby database, it will report RMAN-05021. This message can be ignored

                3>      #CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;

                4>     # Catalog archivelogs

                5>     CATALOG START WITH '+recp/xxxxxx/archivelog' NOPROMPT;

                searching for all files that match the pattern +recp/xxxxxx/archivelog

                no files found to be unknown to the database

                 

                 

                 

                 

                RMAN>     CATALOG START WITH '+recb/xxxxxx/archivelog' NOPROMPT;

                searching for all files that match the pattern +recb/xxxxx/archivelog

                no files found to be unknown to the database

                ### CAMBIO 20180312

                 

                 

                RMAN>     # Catalog maintenance

                2>      ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE disk;

                allocated channel: ORA_MAINT_DISK_1

                channel ORA_MAINT_DISK_1: SID=633 instance=xxxxxx1 device type=DISK

                 

                 

                 

                 

                RMAN>      CROSSCHECK BACKUP;

                crosschecked backup piece: found to be 'AVAILABLE'

                backup piece handle=/zfssa/xxxxxxx/backup1/motc03av_1_1 RECID=11810 STAMP=985664864

                crosschecked backup piece: found to be 'AVAILABLE'

                backup piece handle=/zfssa/xxxxxxxx/backup1/mstc043e_1_1 RECID=11811 STAMP=985665649

                crosschecked backup piece: found to be 'AVAILABLE'

                backup piece handle=/zfssa/xxxxxxx/backup1/mttc043k_1_1 RECID=11812 STAMP=985665652

                crosschecked backup piece: found to be 'AVAILABLE'

                backup piece handle=/zfssa/xxxxxxx/backup1/mntc03av_1_1 RECID=11813 STAMP=985664864

                • 5. Re: In recovery catalog no found backups obsolete
                  Dude!

                  Just a thought but is it possible that these backups use "keep until" in the recovery catalog?  You can view the information querying v$backup_files.

                  • 6. Re: In recovery catalog no found backups obsolete
                    3401456

                    SQL> select BACKUP_TYPE, FILE_TYPE, KEEP, KEEP_UNTIL, STATUS, BYTES/1024/1024 MB, BS_COMPLETION_TIME from v$backup_files where BS_COMPLETION_TIME >= sysdate -30 and FILE_TYPE='PIECE' order by 7 ASC;

                     

                     

                    BACKUP_TYPE                      FILE_TYPE                        KEE KEEP_UNTIL          STATUS             MB BS_COMPLETION_TIME

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

                    BACKUP SET                       PIECE                            NO                      AVAILABLE  13997.8247 2018-09-30 02:16:09

                    BACKUP SET                       PIECE                            NO                      AVAILABLE  128.037598 2018-09-30 02:16:13

                    ...

                    ...

                    BACKUP SET                       PIECE                            YES 2018-12-05 03:43:53 AVAILABLE     2.34375 2018-10-01 03:50:37

                    BACKUP SET                       PIECE                            YES 2018-12-05 03:43:53 AVAILABLE     .078125 2018-10-01 03:50:38

                    BACKUP SET                       PIECE                            YES 2018-12-05 03:43:53 AVAILABLE  80083.3281 2018-10-01 04:00:06

                    BACKUP SET                       PIECE                            YES 2018-12-05 03:43:53 AVAILABLE  85589.7969 2018-10-01 04:03:45

                    BACKUP SET                       PIECE                            YES 2018-12-05 03:43:53 AVAILABLE  107377.172 2018-10-01 04:04:34

                    ...

                    ...

                    BACKUP SET                       PIECE                            NO                      AVAILABLE  385.218262 2018-10-24 02:26:22

                    BACKUP SET                       PIECE                            NO                      AVAILABLE  350.410156 2018-10-24 02:26:22

                    BACKUP SET                       PIECE                            NO                      AVAILABLE  719.040527 2018-10-24 02:26:24

                    BACKUP SET                       PIECE                            NO                      AVAILABLE  789.902344 2018-10-24 02:26:24

                    BACKUP SET                       PIECE                            NO                      AVAILABLE   34.640625 2018-10-24 02:26:31

                    • 7. Re: In recovery catalog no found backups obsolete
                      Dude!

                      Is this the same output with and without a connection to the recovery catalog? As far as I know only keep forever requires a catalog.

                      • 8. Re: In recovery catalog no found backups obsolete
                        3401456

                        In the BBDD

                         

                        SQL> select KEEP, STATUS,FNAME,TAG,RECID,STAMP,COMPLETION_TIME,COMPRESSED,OBSOLETE,BYTES/1024/1024/1024 GB, BS_KEY, BS_COUNT, BS_STAMP, BS_TYPE,BS_INCR_TYPE,BS_STATUS,BP_COPY# from v$backup_files where TAG='FULLBACKUPSET_L1' and BS_COMPLETION_TIME <= sysdate-10;

                         

                         

                        KEE STATUS     FNAME                                    TAG                   RECID      STAMP COMPLETION_TIME     COM OBS         GB     BS_KEY   BS_COUNT    BS_STAMP BS_TYPE       BS_INC BS_STATUS          BP_COPY#

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

                        NO  AVAILABLE  /zfssa/xxxxxxx/backup1/lutecco6_1_1     FULLBACKUPSET_L1  988164873  988164873 2018-09-30 02:16:11 YES YES 13.6697507      11434      12990   988164870 ARCHIVED LOG  FULL   AVAILABLE                 1

                        NO  AVAILABLE  /zfssa/xxxxxxx/backup1/lvtecco9_1_1     FULLBACKUPSET_L1  988164875  988164875 2018-09-30 02:16:14 YES YES 16.5443687      11436      12991   988164873 ARCHIVED LOG  FULL   AVAILABLE                 1

                        NO  AVAILABLE  /zfssa/xxxxxxx/backup1/m0teccob_1_1     FULLBACKUPSET_L1  988164878  988164878 2018-09-30 02:16:20 YES YES 17.1085806      11437      12992   988164875 ARCHIVED LOG  FULL   AVAILABLE                 1

                        NO  AVAILABLE  /zfssa/xxxxxxx/backup1/m1teccoe_1_1     FULLBACKUPSET_L1  988164881  988164881 2018-09-30 02:16:21 YES YES 15.5580235      11439      12993   988164878 ARCHIVED LOG  FULL   AVAILABLE                 1

                        NO  AVAILABLE  /zfssa/xxxxxxx/backup1/m2teccoh_1_1     FULLBACKUPSET_L1  988164883  988164883 2018-09-30 02:16:20 YES YES 14.5165215      11438      12994   988164881 ARCHIVED LOG  FULL   AVAILABLE                 1

                         

                        It is showing these piece of backup OBSOLETE, in the field OBSOLETE it show YES

                         

                        And rman without catalog also show this piece

                         

                        RMAN> connect target /

                         

                         

                        connected to target database: xxxxxx (DBID=2761987891)

                         

                         

                        RMAN> report obsolete;

                         

                         

                        using target database control file instead of recovery catalog

                        RMAN retention policy will be applied to the command

                        RMAN retention policy is set to recovery window of 17 days

                        Report of obsolete backups and copies

                        Type                 Key    Completion Time    Filename/Handle

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

                        Backup Set           11434  30-SEP-18

                          Backup Piece       12796  30-SEP-18          /zfssa/xxxxxx/backup1/lutecco6_1_1

                        Backup Set           11436  30-SEP-18

                          Backup Piece       12798  30-SEP-18          /zfssa/xxxxxx/backup1/lvtecco9_1_1

                        Backup Set           11437  30-SEP-18

                          Backup Piece       12799  30-SEP-18          /zfssa/xxxxxx/backup1/m0teccob_1_1

                        Backup Set           11439  30-SEP-18

                          Backup Piece       12801  30-SEP-18          /zfssa/xxxxxx/backup1/m1teccoe_1_1

                        Backup Set           11438  30-SEP-18

                          Backup Piece       12800  30-SEP-18          /zfssa/xxxxxx/backup1/m2teccoh_1_1

                        Backup Set           11440  30-SEP-18

                         

                        But when i connect to catalog this is wrong

                         

                        RMAN> connect target /

                         

                         

                        connected to target database: xxxxxx (DBID=2761987891)

                         

                         

                        RMAN> connect catalog user/password@catalog

                         

                         

                        connected to recovery catalog database

                         

                         

                        RMAN> report obsolete;

                         

                         

                        RMAN retention policy will be applied to the command

                        RMAN retention policy is set to recovery window of 17 days

                        Report of obsolete backups and copies

                        Type                 Key    Completion Time    Filename/Handle

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

                        Backup Set           30134523 07-OCT-18

                          Backup Piece       30134525 07-OCT-18          +RECP/xxxxxxxx/AUTOBACKUP/2018_10_07/s_988856589.55261.988856591

                         

                         

                        RMAN> exit

                        • 9. Re: In recovery catalog no found backups obsolete
                          Dude!

                          RMAN will not evaluate backups as obsolete that are required to fulfill your retention policy. Giving this another thought, perhaps the issue you are experiencing is simply that you have other backups in your recovery catalog database that rely on the backups in question and which are not obsolete based on your retention policy.

                           

                          Keep in mind that while you can set your retention policy to 17 days, the default control_file_record_keep_time parameter or reusable RMAN records is 7 days. Information about other backups in your control file may have aged out and not be available, hence showing you obsolete records. You should configure your control_file_record_keep_time parameter to meet your retention policy or rely on catalog database.

                          • 10. Re: In recovery catalog no found backups obsolete
                            3401456

                            Hi dude!

                             

                            In the bbdd of catalog

                             

                            NAME TYPE VALUE

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

                            control_file_record_keep_time integer 7

                            • 11. Re: In recovery catalog no found backups obsolete
                              Dude!

                              What is bbdd? The control_file_record_keep_time of the recovery catalog database is irrelevant.

                              • 12. Re: In recovery catalog no found backups obsolete
                                3401456

                                The parameter is the same in the bd. Value = 7.

                                 

                                must it be 17?

                                • 13. Re: In recovery catalog no found backups obsolete
                                  Dude!

                                  What is bd? You mean db?

                                   

                                  If the CONTROL_FILE_RECORD_KEEP_TIME is less than the retention policy then it may overwrite reusable records prior to obsoleting them. It is recommended that the CONTROL_FILE_RECORD_KEEP_TIME should set to a higher value than the retention policy.

                                   

                                  control_file_record_keep_time Tips

                                  • 14. Re: In recovery catalog no found backups obsolete
                                    3401456

                                    Hi dude!

                                     

                                    This question is not resolved yet

                                     

                                    The bd = db_name

                                     

                                    Outside of catalog

                                     

                                    RMAN> connect target /

                                     

                                     

                                    connected to target database: xxxxxxxx (DBID=3632737316)

                                     

                                     

                                    RMAN> report obsolete;

                                     

                                     

                                    using target database control file instead of recovery catalog

                                    RMAN retention policy will be applied to the command

                                    RMAN retention policy is set to recovery window of 17 days

                                    Report of obsolete backups and copies

                                    Type                 Key    Completion Time    Filename/Handle

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

                                    Backup Set           9867   28-OCT-18

                                      Backup Piece       11725  28-OCT-18          /zfssa/xxxxxxx/backup1/fgtgr6nl_1_1

                                    Backup Set           9868   29-OCT-18

                                      Backup Piece       11726  29-OCT-18          /zfssa/xxxxxxx/backup1/fhtgr6nm_1_1

                                    Backup Set           9870   30-OCT-18

                                      Backup Piece       11728  30-OCT-18          /zfssa/xxxxxxx/backup1/fitgr6nn_1_1

                                    Backup Set           9866   31-OCT-18

                                      Backup Piece       11724  31-OCT-18          /zfssa/xxxxxxx/backup1/fktgr6nn_1_1

                                    Backup Set           9869   02-NOV-18

                                      Backup Piece       11727  02-NOV-18          /zfssa/xxxxxxx/backup1/fltgr6nq_1_1

                                    Backup Set           9871   03-NOV-18

                                      Backup Piece       11729  03-NOV-18          /zfssa/xxxxxxx/backup1/fmtgr6o7_1_1

                                    Backup Set           10252  09-NOV-18

                                      Backup Piece       12110  09-NOV-18          +RECB/xxxxxxxx/AUTOBACKUP/2018_11_09/s_991700274.29301.991700313

                                     

                                     

                                     

                                    In the catalog

                                     

                                     

                                    RMAN> connect target /

                                     

                                     

                                    connected to target database: xxxxxxxx(DBID=3632737316)

                                     

                                     

                                    RMAN> connect catalog user/password@CATALOG

                                     

                                     

                                    connected to recovery catalog database

                                     

                                     

                                    RMAN> report obsolete;

                                     

                                     

                                    RMAN retention policy will be applied to the command

                                    RMAN retention policy is set to recovery window of 17 days

                                    Report of obsolete backups and copies

                                    Type                 Key    Completion Time    Filename/Handle

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

                                    Backup Set           32868866 09-NOV-18

                                      Backup Piece       32868870 09-NOV-18          +RECB/xxxxxxxx/AUTOBACKUP/2018_11_09/s_991700274.29301.991700313

                                     

                                     

                                    This is the script of backup

                                     

                                     

                                        connect target /;

                                        connect catalog user/password@CATALOG;

                                        SET ECHO ON;

                                       

                                        CATALOG START WITH '+recp/${bdd}/archivelog' NOPROMPT;

                                        CATALOG START WITH '+recb/${bdd}/archivelog' NOPROMPT;

                                         ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE disk;

                                         CROSSCHECK BACKUP;

                                         CROSSCHECK COPY;

                                         DELETE NOPROMPT EXPIRED BACKUP;

                                         DELETE NOPROMPT EXPIRED COPY;

                                         DELETE NOPROMPT OBSOLETE;

                                     

                                         DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE DISK;  ### ULTIMA MODIFICACION 20180312

                                     

                                     

                                        CONFIGURE COMPRESSION ALGORITHM 'LOW'  AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;

                                        CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO COMPRESSED BACKUPSET;

                                        CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/zfssa/${bdd}/backup1/snapcf_${bdd}.f';

                                        run

                                        {

                                        sql 'alter system set "_backup_disk_bufcnt"=64 scope=memory';

                                        sql 'alter system set "_backup_disk_bufsz"=1048576 scope=memory';

                                        sql 'alter system set "_backup_file_bufcnt"=64 scope=memory';

                                        sql 'alter system set "_backup_file_bufsz"=1048576 scope=memory';

                                     

                                        allocate channel c1 DEVICE TYPE disk format '/zfssa/${bdd}/backup1/%U';

                                        allocate channel c2 DEVICE TYPE disk format '/zfssa/${bdd}/backup1/%U';

                                        allocate channel c3 DEVICE TYPE disk format '/zfssa/${bdd}/backup1/%U';

                                        allocate channel c4 DEVICE TYPE disk format '/zfssa/${bdd}/backup1/%U';

                                        allocate channel c5 DEVICE TYPE disk format '/zfssa/${bdd}/backup1/%U';

                                        allocate channel c6 DEVICE TYPE disk format '/zfssa/${bdd}/backup1/%U';

                                        allocate channel c7 DEVICE TYPE disk format '/zfssa/${bdd}/backup1/%U';

                                        allocate channel c8 DEVICE TYPE disk format '/zfssa/${bdd}/backup1/%U';

                                    backup  CHECK LOGICAL as compressed backupset incremental level $inclevel $ssize database tag FULLBACKUPSET_L$inclevel plus archivelog not backed up tag FULLBACKUPSET_L$inclevel;

                                        }