Begin backup takes a long time

[Nugpot]

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

[davey23uk]

use rman instead

[tamilselvan]

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

[tomcat]

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...

[Nugpot]

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

[Nugpot]

Tamilselvan,

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

Regards
Vijay

[tomcat]

I was thinking more like waits that your script session is waiting on - v$session_wait

[Nugpot]

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

[tamilselvan]

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

[slimdave]

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)