Begin backup takes a long time
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Begin backup takes a long time

  1. #1
    Join Date
    Feb 2001
    Posts
    128

    Begin backup takes a long time

    All,

    Database- 9206
    OS - Sun solaris 64bit, 5.8

    We have a 4tb database with 64 tablespaces, 896 datafiles, putting this database in backup mode using alter tablespace begin backup commands would take 20 to 25 mins. Lately, it is taking over 2 hrs.

    The only change before this unusually big difference was we moved to locally managed temporary tablespaces. I don't think this contributes in any way, but that was the only change on the database.

    We tried different backup times, but still the same result.

    I don't know what/where to start looking for this problem.

    Greatly appreciate all your suggestions.

    Regards
    Vj

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    use rman instead

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    We have a 4tb database with 64 tablespaces, 896 datafiles, putting this database in backup mode using alter tablespace begin backup commands would take 20 to 25 mins. Lately, it is taking over 2 hrs.
    unheard of this problem.

    is it for one tablespace or for all tablespaces?

    Have you ever dropped tablespace and recreated?
    Meaning, is there any "gap" in tablespace numbers?
    You can easily check in v$tablespace.

    Tamil

  4. #4
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    How busy is your db when you start begin backup? What do waits look like while it's going on?

    I wonder if you can multi-thread the begin backup script...
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

  5. #5
    Join Date
    Feb 2001
    Posts
    128
    Thanks for the replies.

    There are gaps in the ts#'s, we have created and dropped tablespaces.

    TS# NAME INCLUDED_IN_DATABASE_BACKUP
    0 SYSTEM YES
    1 PSAPTEMP YES
    3 PSAPBTABD YES
    4 PSAPDDICD YES
    5 PSAPPOOLI YES
    6 PSAPBTABI YES
    7 PSAPUSER1I YES
    8 PSAPCLUD YES
    9 PSAPSOURCED YES
    10 PSAPLOADI YES
    11 PSAPCLUI YES
    12 PSAPDOCUI YES
    13 PSAPSTABD YES
    14 PSAPPROTI YES
    15 PSAPPROTD YES
    16 PSAPLOADD YES
    17 PSAPSOURCEI YES
    18 PSAPUSER1D YES
    19 PSAPSTABI YES
    20 PSAPDOCUD YES
    21 PSAPPOOLD YES
    22 PSAPDDICI YES
    29 PSAPVERTEXD YES
    39 PSAPATABD YES
    40 PSAPATABI YES
    43 PSAPWORKD YES
    44 PSAPWORKI YES
    52 PSAPMAST1D YES
    53 PSAPMAST1I YES
    54 PSAPMAST2D YES
    55 PSAPMAST2I YES
    56 PSAP007D YES
    57 PSAP007I YES
    58 PSAP014D YES
    59 PSAP014I YES
    70 PSAPMAST3D YES
    71 PSAPMAST3I YES
    72 PSAPMAST4D YES
    73 PSAPMAST4I YES
    74 PSAPMAST5D YES
    75 PSAPMAST5I YES
    76 PSAPMAST6D YES
    77 PSAPMAST6I YES
    92 PSAPEL46CI YES
    93 PSAPES46CI YES
    94 PSAPES46CD YES
    95 PSAPEL46CD YES
    96 STATSPACK YES
    97 QUEST YES
    98 QUEST_IDX YES
    101 PSAPUSER1128MD YES
    102 PSAPUSER1128MI YES
    106 PSAPUNDO YES
    107 PSAPQUESTD YES
    108 PSAPBTA1D YES
    109 PSAPBTA1I YES
    110 PSAPBTA2D YES
    111 PSAPBTA2I YES
    112 PSAPBTA3D YES
    113 PSAPBTA3I YES
    114 PSAPBTA4D YES
    115 PSAPBTA4I YES

    Will the gap in ts#'s cause delay...

    The load on the database has not been anything different, the usual jobs. I did check for any wait events.. there are a few on the file i/o but it is not too different ..regular waits .... i got the info from STATS$FILESTATXS

    Regards
    Vj

  6. #6
    Join Date
    Feb 2001
    Posts
    128
    Tamilselvan,

    It takes a total of 2 hrs to put all the tablespaces in backup mode.

    Regards
    Vijay

  7. #7
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    I was thinking more like waits that your script session is waiting on - v$session_wait
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

  8. #8
    Join Date
    Feb 2001
    Posts
    128
    I checked for any unusual waits, but did not find any.

    Btw, there was DB parm change since then we are having this problem.
    SGA_MAX_SIZE was to 35GB. Before that it was not set, it was using the default(that is the sga size, 29GB). I'm reverting that change back this weekend.

    Any other ideas?

    Regards
    Vj

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by Nugpot
    Tamilselvan,

    It takes a total of 2 hrs to put all the tablespaces in backup mode.

    Regards
    Vijay
    Why do you want to put all tablespaces in "BACKUP" mode? You will see more redo will be generated if concurrent transactions do DML activities.

    I suspect the "gap" in tablespace numbers causes a problem.

    Tamil

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What mechanism did you use to check for waits? If not a 10046 trace, then I wonder if that would be productive?

    (TS: VJ is putting the db into suspend mode for a sync/split backup)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width