1 2 Previous Next 17 Replies Latest reply: May 13, 2013 8:40 PM by Hemant K Chitale RSS

    why does backing up archivelogs take so long....

    977635
      I'm running Oracle EE 11.2 on Sun Solaris.

      I'm not understanding a peculiar phenomenon and was hoping someone might be able to help.

      I have a database that is approx. 563G, which, for reference, contains approx. 400G of CLOBs.
      Our redo logs are 1G each and we accumulate approx. 18 archive logs each day.
      I run a nightly Lvl-0 (full backup) and archive logs beginning at 1:00am.

      From the log file, I see that it completes backing up the database by approx. 2:30.
      But then it backs up the archive logs which take from 2:30 until approx. 5:00am.

      I don't understand why it would take only 1-1/2 hours to backup 563 of actual data in datafiles, and it takes 2-1/2 hours to back up approx. 18G of archivelogs.
      Any ideas?

      For reference:
      Total database footprint...
      =============================
      
      All DATA files (GB)
      -------------------
                    619.9
      
      Total Space used (GB)
      ---------------------
                        563
      
      TEMP Space (GB)
      ---------------
                   42
      
      Total UNDO Space
      ----------------
                    22
      
      Count of DATA FILES
      -------------------
                       28
      
      Count of REDO LOGS
      ------------------
                      13
      Database and archive logs reside on a SAN and backups are being written to an NFS mount that is also on the SAN.
        • 1. Re: why does backing up archivelogs take so long....
          Niket Kumar
          check backup timings with this query and paste the output.


          col STATUS format a9
          col hrs format 999.99
          select * from (select
          SESSION_KEY, INPUT_TYPE, STATUS,
          to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
          to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
          elapsed_seconds/3600 hrs
          from V$RMAN_BACKUP_JOB_DETAILS
          order by SESSION_KEY DESC) where rownum<=10;
          • 2. Re: why does backing up archivelogs take so long....
            977635
            That's really strange.... But this shows the entire backup, including archivelogs.
            But why does it show one backup is still running. I confirmed from v$session that it is not there.
            BTW: We had a bad week which is why it shows so many backups failed but that is unrelated.
            Also, last night's backup included extra archive logs which is why it didn't finish until 6:23am.
            But the backup of the database finished at 2:42am.
                SESSION_KEY INPUT_TYPE    STATUS    START_TIME     END_TIME           HRS
            --------------- ------------- --------- -------------- -------------- -------
                       5543 DB INCR       COMPLETED 05/08/13 01:00 05/08/13 06:23    5.39
                       5540 DB INCR       RUNNING   05/06/13 01:26 05/06/13 03:10    1.74
                       5535 DB INCR       FAILED    05/06/13 01:00 05/06/13 01:00     .00
                       5526 DB INCR       COMPLETED 05/05/13 05:41 05/05/13 08:31    2.83
                       5521 DB INCR       FAILED    05/05/13 05:29 05/05/13 05:29     .00
                       5516 DB INCR       FAILED    05/05/13 01:00 05/05/13 01:00     .00
                       5507 DB INCR       COMPLETED 05/04/13 02:28 05/04/13 04:57    2.48
                       5499 DB INCR       FAILED    05/03/13 20:00 05/03/13 23:58    3.96
                       5490 DB INCR       COMPLETED 05/03/13 01:00 05/03/13 05:30    4.50
                       5481 DB INCR       COMPLETED 05/02/13 01:00 05/02/13 05:43    4.72
                       5472 DB INCR       COMPLETED 05/01/13 01:00 05/01/13 05:46    4.77
                       5463 DB INCR       COMPLETED 04/30/13 01:00 04/30/13 04:57    3.96
                       5454 DB INCR       COMPLETED 04/29/13 01:00 04/29/13 04:56    3.94
                       5445 DB INCR       COMPLETED 04/28/13 01:00 04/28/13 05:13    4.23
                       5436 DB INCR       COMPLETED 04/27/13 04:00 04/27/13 07:00    3.00
                       5427 DB INCR       COMPLETED 04/26/13 01:00 04/26/13 04:56    3.94
                       5418 DB INCR       COMPLETED 04/25/13 01:00 04/25/13 04:35    3.59
                       5409 ARCHIVELOG    COMPLETED 04/24/13 03:24 04/24/13 05:09    1.74
            
            18 rows selected.
              
            From the actual rman log file:
            Database datafiles:
            ==============
            Starting backup at 08-MAY-2013 01:00:06
            channel d1: starting compressed incremental level 0 datafile backup set
            channel d1: specifying datafile(s) in backup set
            . . . .
            Database datafiles:  Finished backup at 08-MAY-2013 02:42:57
            
            Archivelogs:
            ===========
            Starting backup at 08-MAY-2013 02:42:59
            current log archived
            channel d1: starting archived log backup set
            . . . .
            channel d1: starting piece 1 at 08-MAY-2013 02:43:31
            channel d2: starting archived log backup set
            channel d2: specifying archived log(s) in backup set
            . . . .
            channel d2: starting piece 1 at 08-MAY-2013 02:43:32
            channel d3: starting archived log backup set
            channel d3: specifying archived log(s) in backup set
            . . . .
            channel d2: backup set complete, elapsed time: 03:34:29
            channel d3: backup set complete, elapsed time: 03:38:45
            channel d1: backup set complete, elapsed time: 03:39:36
            Finished backup at 08-MAY-2013 06:23:07
            Also, for whatever it is worth mentioning, I am using 3 channels.

            Edited by: 974632 on May 8, 2013 6:52 AM
            • 3. Re: why does backing up archivelogs take so long....
              Niket Kumar
              so its ok now...your archive backup take 1 and half hour and db backup take more time...you can check for other days whats the trend going on.
              check also if you can take incremental backup with level 1 its give you good advantage as your database size is very big.
              • 4. Re: why does backing up archivelogs take so long....
                977635
                No, it is not okay now. It is still taking much longer to backup the archivelogs than it takes to backup the database and the database is several times larger than the archivelogs.

                I may consider doing incrementals again. I was running level 1 before using block change tracking, but the background process for change block tracking was impacting our performance.
                I could try it again without the BCT.
                • 5. Re: why does backing up archivelogs take so long....
                  EdStevens
                  974632 wrote:
                  No, it is not okay now. It is still taking much longer to backup the archivelogs than it takes to backup the database and the database is several times larger than the archivelogs.

                  I may consider doing incrementals again. I was running level 1 before using block change tracking, but the background process for change block tracking was impacting our performance.
                  I could try it again without the BCT.
                  Even without BCT, the level 1 backup should be faster. With BCT, rman knows before it starts which blocks need to be backed up. Without it, it has to actually visit the block, but if it finds it doesn't have to back it up, you still save that i/o.
                  • 6. Re: why does backing up archivelogs take so long....
                    977635
                    Yes, i understand that. I guess I'm just not a fan of using incrementals and having to keep the original full backup on disk all week, and I certainly wouldn't want to delete it after it is backed up to tape because restoring it from tape might add more hours to a disaster recovery. But, its something to think about.
                    • 7. Re: why does backing up archivelogs take so long....
                      977635
                      So, my question remains: why is there such a disproportionate time to back up the archive logs compared to the database files?
                      Is there any logical explanation for this?

                      I have confirmed no other activity going on at that time.

                      Is this normal for other people to have the archive logs take that much longer time than the actual database backups?

                      Let me explain one thing: if you do not set in your backup script the NLS date function, you can not tell the timestamp of the completion of the various tasks of backing up things.

                      You must set in your rman backup script: export NLS_DATE_FORMAT="dd-MON-yyyy hh24:mi:ss"
                      Otherwise, you will not get a time stamp, only the date stamp of when the different parts of your backups complete.
                      • 8. Re: why does backing up archivelogs take so long....
                        EdStevens
                        974632 wrote:
                        Yes, i understand that. I guess I'm just not a fan of using incrementals and having to keep the original full backup on disk all week, and I certainly wouldn't want to delete it after it is backed up to tape because restoring it from tape might add more hours to a disaster recovery. But, its something to think about.
                        I know this is aside from your primary question, but what is the issue with keeping a full backup on disk for a week? What is your recovery window? Regardless of what you do with incrementals and archivelogs, all recovery starts with that full backup.
                        • 9. Re: why does backing up archivelogs take so long....
                          977635
                          I understand your point Ed. I guess its just a matter of personal preference. I personally don't like to have to rely on two sets of backups, plus latest archive logs, to restore and recover my database. I just feel safer with a full backup, and the latest archivelogs.

                          But aside from that, I'm still trying to understand why the archivelogs take so long to backup compared to the database when the archivelogs are only a fraction of the size of the database.
                          • 10. Re: why does backing up archivelogs take so long....
                            Hemant K Chitale
                            Are you running the backups to tape or to disk ?
                            How many channels are you using for the database backup and for the archivelog backup ?
                            (It seems that you are using multiple channels for the archivelog backups and there could be i/o contention / wait time within these multiple channels ??)


                            Hemant K Chitale
                            • 11. Re: why does backing up archivelogs take so long....
                              977635
                              Hi Hermant.

                              I am using only 3 channels for both database and archivelog backups.

                              I look at performance charts in OEM during the times of the backups and I see a fairly even amount of cpu and system I/O during the entire backup (including archive logs), and then when the backup is done, I see a fair amount of CPU and system I/O by DBWx and PSPx background processes for about another hour as it tapers off to nothing. Strange that there would be so much DBWriter activity after the backups already completed, but I suppose it is similar to how we used to do hot backups by taking the tablespace offline and then when the backup completed for that tablespace, we put it back online and all the changes stored in the redo would get applied to it (or something like that).

                              Edited by: 974632 on May 10, 2013 11:06 AM
                              • 12. Re: why does backing up archivelogs take so long....
                                Bobby Durrett
                                You said:

                                Database and archive logs reside on a SAN and backups are being written to an NFS mount that is also on the SAN.

                                Suggestion:

                                Do you think you can monitor the network traffic (blocks/second, bytes/second) over your NFS mount while the backup is running? It would be interesting to see if there is a long pause with no traffic during your archive log backups. I.e. is it a steady but slow stream of data or are you seeing some sort of hangup? I would watch this in glance on HP-UX but you must have some way to monitor this on Solaris. If you see a big pause then maybe there is some sort of network hangup with NFS.

                                - Bobby
                                • 13. Re: why does backing up archivelogs take so long....
                                  Hemant K Chitale
                                  Strange that there would be so much DBWriter activity after the backups already completed, but I suppose it is similar to how we used to do hot backups by taking the tablespace offline and then when the backup completed for that tablespace, we put it back online and all the changes stored in the redo would get applied to it (or something like that)
                                  No, there is no difference for DBWR. When using BEGIN BACKUP mode, it is LGWR that, potentially, has to write more to the redo stream -- because the first time a block is modified when it's datafile is in backup mode, the entire block (not just the changes to it) is written to the redo stream. However, DBWR behaves the same way during a BEGIN BACKUP .. END BACKUP cycle and after it as it does with RMAN backups.


                                  Hemant K Chitale
                                  • 14. Re: why does backing up archivelogs take so long....
                                    Hemant K Chitale
                                    You could try
                                    a. Using only 1 channel for the archivelog backups
                                    b. Moving the archivelog backups to a different location


                                    Hemant K Chitale
                                    1 2 Previous Next