13 Replies Latest reply: Feb 21, 2013 11:21 PM by Hemant K Chitale RSS

    Backup size is increased after resync catalog

    987770
      Hi everyone,

      I have describe my problem, if anyone have a solution please provide me.

      Rman catalog database version : 10.2.0.1.0, OS : red hat linux
      Target database version : 10.2.0.5.0, OS : solaris 10

      rman catalog database space is full, then i move my example tablespace to another location, but i forgot to change the ownership of new file.
      And I have no backup of my rman catalog database. so i offline the datafile first for opening the rman catalog database after opening the database I have offline the whole example tablespace and this tablespace cannot contain any thing related the catalog.

      after that I used the command : crosscheck archivelog all;
      DELETE EXPIRED ARCHIVELOG ALL

      then i run the command : CROSSCHECK BACKUP;
      DELETE EXPIRED BACKUP;

      then i run the command : report obsolete;
      delete obsolete;

      then i run the command : RESYNC CATALOG;

      then i run the command : change archivelog all validate;

      after doing all the thing when I am taking a backup of archive log by using following command :

      run {allocate channel c1 device type disk format '/appl2/prodback_rman/prod_fullbackup/ARCH_%d_%s.bak';
      backup archivelog all;
      }

      before performing all the above thing my archive backup of whole day is around 5gb but after the above thing my archive backup of one day is around : 126gb

      and my level0 backup is alright after and before the above thing and the size is around : 139gb

      the script for the full backup is :
      run {allocate channel c1 device type disk format '/appl2/prodback_rman/prod_fullbackup/FULL_%d_%s_%c.bak';
      backup incremental level 0 database skip offline;
      }


      after that I have perform the incremental level 1 backup by using the following script :

      run {allocate channel c1 device type disk format '/appl2/prodback_rman/prod_fullbackup/INC1_%d_%s_%c.bak';
      backup incremental level 1 database;
      }

      before the above activity the size of incremental backup is around : 4.5gb but after performing the above activity the incremental backup size is around : 120gb

      it is very critical for me, if any one have the solution please provide me as early as possible.

      Thanks & Regards
      Rakesh Kumar
        • 1. Re: Backup size is increased after resync catalog
          clementeOTN
          Hi

          Just a few questions because

          Your catalog database is shared, or is a dedicated instance for rman?
          The "example tablespace" you moved, was in the target or in the rman database? Do you solved the "example target " problem?

          If all the problem is in your catalog database , I think that increasing the size of the backup may be due to the "example database problem". So, when you solve it, your full backup will be 5Gb again.

          On the other hand a rman catalog could be backup with an export of rman schema (export + rman is better, but, all the data you need is just in rman schema) .


          As a last chance, you can always export the rman schema, delete the catalog database, recreate a new catalog database and import the rman schema.
          You will no lost the catalog infrmation and you will have a new and clean database.
          • 2. Re: Backup size is increased after resync catalog
            Hemant K Chitale
            It seems that the EXAMPLE tablespace that you moved and offlined is in the RMAN Catalog database and not in the Target database that you are backing up.

            The movement of EXAMPLE and the resync have nothing to do with the increase in the sizes of the ArchiveLog and Incremental DB Backups. In fact, the ArchiveLog and Incremental DB Backup sizes are correlated -- both indicated significantly increased volume or sizes of transactions in the target database. Query the target database for archivelog sizes e.g.
            select trunc(first_time) Arch_Dt, sum(blocks*block_size)/1048576 Arch_MB
            from v$archived_log
            group by trunc(first_time)
            order by trunc(first_time)
            /
            and look at the last few days of archivelog volumes. (The information of the first day in the list would be partial assuming that some archivelogs of that day have already been purged)


            Hemant K Chitale
            • 3. Re: Backup size is increased after resync catalog
              987770
              Hi,

              My catalog database is deticated instance for rman.
              Example tablespace resides in the rman catalog database. and this tablespace does not contain anything releated to the catalog database. and I have no backup of rman catalog database that why I am not able to solve the example tablespace problem. So operating my rman catalog I simply offline the example tablespace.

              so please tell me the solution
              • 4. Re: Backup size is increased after resync catalog
                987770
                Hi,

                after assuming the below output, I think the arcivelog generation are almost same.
                now I am taking a full backup that size is : 138gb weekly
                and then i take a incremental level 1 backup that size is : 54gb weekly
                after that i take a archivelog all backup that size is : 12gb daily

                but before the size is

                full backup : 138gb weekly
                incremental backup : 4.5gb weekly
                archivelog all backu : 5gb daily

                so now please provide some action plan to resolve this problem.

                SQL> select trunc(first_time) Arch_Dt, sum(blocks*block_size)/1048576 Arch_MB
                2 from v$archived_log
                3 group by trunc(first_time)
                4 order by trunc(first_time);

                ARCH_DT ARCH_MB
                --------- ----------
                21-DEC-12 151.954102
                22-DEC-12 8181.92969
                23-DEC-12 7640.41504
                24-DEC-12 8503.43066
                25-DEC-12 9204.61816
                26-DEC-12 9175.68652
                27-DEC-12 10291.5996
                28-DEC-12 10429.1016
                29-DEC-12 10281.9482
                30-DEC-12 10275.543
                31-DEC-12 10739.7871

                ARCH_DT ARCH_MB
                --------- ----------
                01-JAN-13 9377.40527
                02-JAN-13 9239.97461
                03-JAN-13 13298.167
                04-JAN-13 9543.97949
                05-JAN-13 9869.17188
                06-JAN-13 7225.61719
                07-JAN-13 8965.75195
                08-JAN-13 8717.8125
                09-JAN-13 9002.80566
                10-JAN-13 9836.4541
                11-JAN-13 9514.85938

                ARCH_DT ARCH_MB
                --------- ----------
                12-JAN-13 7841.49219
                13-JAN-13 7270.00488
                14-JAN-13 11278.1826
                15-JAN-13 10188.8057
                16-JAN-13 4926.9248
                18-JAN-13 1563.56445
                19-JAN-13 11125.3936
                20-JAN-13 9010.58984
                21-JAN-13 11512.6494
                22-JAN-13 11101.873
                23-JAN-13 10380.1846

                ARCH_DT ARCH_MB
                --------- ----------
                24-JAN-13 11087.5469
                25-JAN-13 10249.8008
                26-JAN-13 8400.42676
                27-JAN-13 7899.125
                28-JAN-13 11852.9121
                29-JAN-13 12319.6924
                30-JAN-13 4397.96289

                40 rows selected.

                Thanks & Regards
                Rakesh Kumar
                • 5. Re: Backup size is increased after resync catalog
                  Hemant K Chitale
                  Why do you think 12GB archivelog backups are unusual when your volumes are like these :
                  ARCH_DT     ARCH_MB
                  
                  -------------------
                  15-JAN-13  10188.80 
                  16-JAN-13   4926.92
                  18-JAN-13   1563.56
                  19-JAN-13  11125.39
                  20-JAN-13   9010.58
                  21-JAN-13  11512.64
                  22-JAN-13  11101.87
                  23-JAN-13  10380.18
                  24-JAN-13  11087.54
                  25-JAN-13  10249.80
                  26-JAN-13   8400.42
                  27-JAN-13   7899.12
                  28-JAN-13  11852.91
                  29-JAN-13  12319.69
                  When did you last have 5GB backups ?

                  Your incremental level 1 backups are dependent on the number of blocks changed by transactions during the 24hour period. It has nothing to do with a tablespace in the RMAN catalog database being taken offline.

                  Hemant K Chitale
                  • 6. Re: Backup size is increased after resync catalog
                    987770
                    Hi,

                    Thank you for your reply,

                    till 22nd jan my archive log backup size is 5gb per day, but after all the above problem the size of archive log backup is 12gb on daily basis.

                    so I am confused that its taking a backup of all archivelog from my database that is already backed up by rman or it is the backup of archivelog that is generated on the daily basis.

                    Thanks & Regards
                    Rakesh Kumar
                    • 7. Re: Backup size is increased after resync catalog
                      Hemant K Chitale
                      Your backup command for ArchiveLogs is :
                      backup archivelog all;
                      That would mean that any ArchiveLog that hasn't been deleted would be backed up. If you delete ArchiveLogs older than 2 days, then each Backup run would backup ArchiveLogs of the past two days.

                      You'd need to check the schedule and method of deleting ArchiveLogs


                      Hemant K Chitale
                      • 8. Re: Backup size is increased after resync catalog
                        987770
                        Hi Hemant,

                        You are right, but I want to know that before this happening the same script is running for the backup of archivelog on the daily basis, that I have already posted and the size of the archivelog backup is around 5gb on daily basis, but know the same script is running but the backup size of archivelog is 12gb on daily basis.

                        So I want to how this is happen. Or what will I do to minimize the backup size of archivelog on the daily basis.

                        Thanks & Regards
                        Rakesh Kumar
                        • 9. Re: Backup size is increased after resync catalog
                          Hemant K Chitale
                          till 22nd jan my archive log backup size is 5gb per day,
                          Which doesn't match the archivelog volumes
                          ARCH_DT     ARCH_MB
                           
                          -------------------
                          15-JAN-13  10188.80 
                          16-JAN-13   4926.92
                          18-JAN-13   1563.56
                          19-JAN-13  11125.39
                          20-JAN-13   9010.58
                          21-JAN-13  11512.64
                          22-JAN-13  11101.87
                          So, either
                          a) You were NOT backing up all the ArchiveLogs
                          OR
                          b) You were createing COMPRESSED BACKUPSETs till 22-Jan and are not doing so now


                          Your ArchiveLog generation rate hasn't changed. Your retention of ArchiveLogs on disk (for "backup archivelog all") or your backup script has changed.

                          The figures speak for themselves.

                          Hemant K Chitale
                          • 10. Re: Backup size is increased after resync catalog
                            987770
                            Hi Hemant K Chitale,

                            you said that I am using the COMPRESSED BACKUPSETs till 22-Jan, so I am posting
                            the rman configuration here that is used before all this happening and same
                            configuration is used right now.

                            but before resync catalog the archivelog is generated only one location then I used to archive log
                            dest for archive log generation then after 5 month I will resync my catalog. may be it is the
                            reason.

                            because there are no compressed backupset is defined in the rman configuration.

                            RMAN> show all;
                            RMAN configuration parameters are:
                            CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
                            CONFIGURE BACKUP OPTIMIZATION ON;
                            CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
                            CONFIGURE CONTROLFILE AUTOBACKUP ON;
                            CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/appl2/prodback_rman/prod_fullbackup/ctrl_%F';
                            CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
                            CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
                            CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
                            CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/appl2/prodback_rman/prod_fullbackup/prod_%d_%s_%c.bak', '/appl2/prodback_rman/usb/prod_%d_%s_%c.bak';
                            CONFIGURE MAXSETSIZE TO 20 G;
                            CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
                            CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
                            CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
                            CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/appl2/prodback_rman/prod_fullbackup/snapcf_PROD.f';



                            Archive log generation log on the daily basis.

                            SQL> select trunc(COMPLETION_TIME,'DD') Day, thread#,
                            round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,
                            count(*) Archives_Generated from v$archived_log
                            group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;


                            DAY THREAD# MB ARCHIVES_GENERATED
                            --------- ---------- ---------- ------------------
                            04-FEB-13 1 2776 37
                            05-FEB-13 1 16767 220
                            06-FEB-13 1 16545 216
                            07-FEB-13 1 15772 208
                            08-FEB-13 1 15056 198
                            09-FEB-13 1 18486 244
                            10-FEB-13 1 17591 232
                            11-FEB-13 1 18163 238
                            12-FEB-13 1 15062 198
                            13-FEB-13 1 26383 342
                            14-FEB-13 1 36279 462

                            DAY THREAD# MB ARCHIVES_GENERATED
                            --------- ---------- ---------- ------------------
                            15-FEB-13 1 34010 432
                            16-FEB-13 1 32230 413
                            17-FEB-13 1 31904 408
                            18-FEB-13 1 33448 426
                            19-FEB-13 1 34639 444
                            20-FEB-13 1 36684 468
                            21-FEB-13 1 27547 352


                            right know the archive log backup size by rman is around 19gb per day.

                            so tell me how we reduce the size of my archive log backup according to
                            my rman configuration and how can i reduce the archive log generation on
                            my production server on daily basis.

                            Thanks & Regards
                            Rakesh Kumar
                            • 11. Re: Backup size is increased after resync catalog
                              Hemant K Chitale
                              BACKUP ARCHIVELOG ALL
                              would backup all the archivelogs that are present on disk. Not just those of the current day but also of previous days if they haven't been deleted.

                              If you want to reduce the size of the backups, then you should be deleting archivelogs after they are backed up so that they do not get backed up repeatedly.

                              A BACKUP ARCHIVELOG ALL DELETE INPUT command would delete archivelogs after deletion.


                              Hemant K Chitale
                              • 12. Re: Backup size is increased after resync catalog
                                987770
                                Hi Hemant,

                                Thanks for your valuable replys, I am telling you what I have analyzed during the backup of archivelog.

                                Rman only perform the one day backup of archive log that is not backedup before, because the
                                parameter backup optimization is set.

                                I have manually count the archive log sequence that is backed up for my 21st Feb 2013.

                                So now tell me how I reduce the size of archive log backup as well as How can I reduce the Archive log
                                generation on my Production server


                                Thanks & Regards
                                Rakesh Kumar
                                • 13. Re: Backup size is increased after resync catalog
                                  Hemant K Chitale
                                  You could only reduce the redo and archivelog generation by
                                  a) Reducing transactions in the database
                                  b) Redesigning transactions that generate excessive redo

                                  (e.g. a load job that commits at every row generates more redo than one that commits at the end of the batch -- which could be 'n' thousand rows. -- but you have to redesign the recovery mechanism / procedures used for instances when the job fails midway)


                                  Hemant K Chitale

                                  Edited by: Hemant K Chitale on Feb 22, 2013 1:21 PM