Re: Size of files. Can you estimate?

On Dec 8, 9:57 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Mark D Powell wrote:
> > On Dec 7, 5:48 pm, "hpuxrac" <johnbhur..._at_sbcglobal.net> wrote:
> > > ExecMan wrote:
> > > > Hi,
>
> > > > I've just taken over a database. The database has 28 rollback
> > > > segments. And, to my astonishment, these guys are not running their
> > > > production database in archive log mode.
>
> > > > Short term fix is to put it in archive log mode. but, I'm wondering if
> > > > there is any way to estimate how large the archive log files will be,
> > > > or, how often they will be created based on the number of wraps the
> > > > rollback segments have........
>
> > > > Here is my data to work with. I know it's a lot, so I appreciate your
> > > > help.
>
> > > > NAME USN RSSIZE OPTSIZE HWMSIZE EXTENDS WRAPS
> > > > SHRINKS AVESHRINK AVEACTIVE
> > > > ------ --- ---------- ---------- ---------- ---------- ----------
> > > > ---------- ---------- ----------
> > > > RBS0 1 4190208 4194304 17297408 120 1225
> > > > 23 2735415 2429731
> > > > RBS1 2 4190208 4194304 17821696 219 1325
> > > > 32 3588096 2870508
> > > > RBS10 11 4190208 4194304 17821696 151 1239
> > > > 21 3769880 1724976
> > > > RBS11 12 4190208 4194304 17297408 176 1250
> > > > 25 3690987 1310009
> > > > RBS12 13 4190208 4194304 17821696 120 1216
> > > > 22 2859752 1878547
> > > > RBS13 14 4190208 4194304 17297408 239 1379
> > > > 38 3297495 2398540
> > > > RBS14 15 4190208 4194304 17821696 241 1385
> > > > 38 3325089 873264
> > > > RBS15 16 4190208 4194304 17297408 181 1285
> > > > 29 3272280 1564103
> > > > RBS16 17 4190208 4194304 16248832 155 1246
> > > > 26 3125563 1497080
> > > > RBS17 18 4190208 4194304 17297408 167 1279
> > > > 28 3127003 662942
> > > > RBS18 19 4190208 4194304 17297408 191 1325
> > > > 31 3230290 2764290
> > > > RBS19 20 4190208 4194304 17821696 149 1240
> > > > 23 3396474 1908383
> > > > RBS2 3 4190208 4194304 17297408 173 1271
> > > > 27 3359326 1255459
> > > > RBS20 21 4190208 4194304 16248832 197 1343
> > > > 33 3129840 1922859
> > > > RBS21 22 4190208 4194304 16248832 125 1241
> > > > 23 2849391 2520293
> > > > RBS22 23 4190208 4194304 17297408 273 1457
> > > > 43 3328619 1596865
> > > > RBS23 24 4190208 4194304 15724544 169 1274
> > > > 25 3544186 2106861
> > > > RBS24 25 4190208 4194304 17821696 188 1268
> > > > 27 3650597 1403512
> > > > RBS25 26 4190208 4194304 17297408 219 1350
> > > > 35 3280544 669183
> > > > RBS26 27 4190208 4194304 17297408 212 1335
> > > > 32 3473408 769582
> > > > RBS27 28 4190208 4194304 16248832 218 1405
> > > > 39 2930635 1670012
> > > > RBS28 29 4190208 4194304 17821696 185 1337
> > > > 31 3128815 717392
> > > > RBS3 4 4190208 4194304 13627392 73 1151
> > > > 15 2551534 2173562
> > > > RBS4 5 4190208 4194304 17821696 247 1387
> > > > 39 3320490 1928782
> > > > RBS5 6 4190208 4194304 17297408 217 1315
> > > > 31 3670016 1460498
> > > > RBS6 7 4190208 4194304 17297408 203 1348
> > > > 34 3130307 562245
> > > > RBS7 8 4190208 4194304 17297408 171 1269
> > > > 26 3448201 527361
> > > > RBS8 9 4190208 4194304 13627392 147 1245
> > > > 24 3211264 931141
> > > > RBS9 10 4190208 4194304 17821696 130 1210
> > > > 19 3587233 2876121
>
> > > > JohnUmm John ( nice name by the way ) ... rollback segments don't have
> > > anything to do ( directly ) with how much archive is going to be
> > > generated.
>
> > > However, the activity of your online redo logs is important and can be
> > > used to get an idea of how much archive log data is going to be
> > > generated.- Hide quoted text -- Show quoted text -
>
> > John, look in the alert log and count how many log switches you have
> > per day. Based on your online redo log size make an estimate.
>
> > You can also try looking at v$log_history. I do not have a non-archive
> > log mode database in front of me to check but I am pretty sure that
> > Oracle still logs the swithes in the control file so you can see the
> > history of switches here.
>
> > HTH -- Mark D Powell --Hi Mark,
>
> Don't get upset, I know it is a lot of data, but how does this show me
> the log switches? This is a query from v$log_history......
>
> RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME
> NEXT_CHANGE#
> ---------- ---------- ---------- ---------- -------------
> ------------------- ------------
> 584240 608604129 1 584240 2815333393 12/08/2006
> 00:41:53 2815333640
> 584241 608604141 1 584241 2815333640 12/08/2006
> 00:42:09 2815334042
> 584242 608604152 1 584242 2815334042 12/08/2006
> 00:42:20 2815335349
> 584243 608604173 1 584243 2815335349 12/08/2006
> 00:42:32 2815336687
> 584244 608604185 1 584244 2815336687 12/08/2006
> 00:42:53 2815338011
> 584245 608604197 1 584245 2815338011 12/08/2006
> 00:43:05 2815339303
> 584246 608604212 1 584246 2815339303 12/08/2006
> 00:43:17 2815340614
> 584247 608604225 1 584247 2815340614 12/08/2006
> 00:43:32 2815341938
> 584248 608604237 1 584248 2815341938 12/08/2006
> 00:43:45 2815343240
> 584249 608604261 1 584249 2815343240 12/08/2006
> 00:43:57 2815344520
> 584250 608604273 1 584250 2815344520 12/08/2006
> 00:44:21 2815345808
> 584251 608604284 1 584251 2815345808 12/08/2006
> 00:44:33 2815347127
> 584252 608604309 1 584252 2815347127 12/08/2006
> 00:44:44 2815348375
> 584253 608604323 1 584253 2815348375 12/08/2006
> 00:45:09 2815349686
> 584254 608606581 1 584254 2815349686 12/08/2006
> 00:45:23 2815352695
> 584255 608607008 1 584255 2815352695 12/08/2006
> 01:23:01 2815353252
> 584256 608607019 1 584256 2815353252 12/08/2006
> 01:30:08 2815353314
> 584257 608607030 1 584257 2815353314 12/08/2006
> 01:30:19 2815353359
> 584258 608607039 1 584258 2815353359 12/08/2006
> 01:30:30 2815353424
> 584259 608607049 1 584259 2815353424 12/08/2006
> 01:30:39 2815353474
> 584260 608607060 1 584260 2815353474 12/08/2006
> 01:30:49 2815353543
> 584261 608607070 1 584261 2815353543 12/08/2006
> 01:31:00 2815353615
> 584262 608607085 1 584262 2815353615 12/08/2006
> 01:31:10 2815353678
> 584263 608607093 1 584263 2815353678 12/08/2006
> 01:31:25 2815353738
> 584264 608607102 1 584264 2815353738 12/08/2006
> 01:31:32 2815353802
> 584265 608607114 1 584265 2815353802 12/08/2006
> 01:31:42 2815353921
> 584266 608607143 1 584266 2815353921 12/08/2006
> 01:31:54 2815354453
> 584267 608607156 1 584267 2815354453 12/08/2006
> 01:32:23 2815354501
> 584268 608607170 1 584268 2815354501 12/08/2006
> 01:32:36 2815354542
> 584269 608607179 1 584269 2815354542 12/08/2006
> 01:32:50 2815354591
> 584270 608607184 1 584270 2815354591 12/08/2006
> 01:32:59 2815354655
> 584271 608607200 1 584271 2815354655 12/08/2006
> 01:33:04 2815354712
> 584272 608607207 1 584272 2815354712 12/08/2006
> 01:33:20 2815354775
> 584273 608607216 1 584273 2815354775 12/08/2006
> 01:33:27 2815354830
> 584274 608607227 1 584274 2815354830 12/08/2006
> 01:33:36 2815354890
> 584275 608607236 1 584275 2815354890 12/08/2006
> 01:33:46 2815354947
> 584276 608607248 1 584276 2815354947 12/08/2006
> 01:33:56 2815355010
> 584277 608608838 1 584277 2815355010 12/08/2006
> 01:34:08 2815358195
> 584278 608611011 1 584278 2815358195 12/08/2006
> 02:00:38 2815361900
> 584279 608614811 1 584279 2815361900 12/08/2006
> 02:36:51 2815367037
> 584280 608614842 1 584280 2815367037 12/08/2006
> 03:40:11 2815370987
> 584281 608614871 1 584281 2815370987 12/08/2006
> 03:40:42 2815374953
> 584282 608614900 1 584282 2815374953 12/08/2006
> 03:41:11 2815378075
> 584283 608614925 1 584283 2815378075 12/08/2006
> 03:41:40 2815381848
> 584284 608614949 1 584284 2815381848 12/08/2006
> 03:42:05 2815385731
> 584285 608617592 1 584285 2815385731 12/08/2006
> 03:42:29 2815427651
> 584286 608618894 1 584286 2815427651 12/08/2006
> 04:26:32 2815431451
> 584287 608618899 1 584287 2815431451 12/08/2006
> 04:48:14 2815434137
> 584288 608618907 1 584288 2815434137 12/08/2006
> 04:48:19 2815436923
> 584289 608618913 1 584289 2815436923 12/08/2006
> 04:48:27 2815439748
> 584290 608618919 1 584290 2815439748 12/08/2006
> 04:48:33 2815442602
> 584291 608618928 1 584291 2815442602 12/08/2006
> 04:48:39 2815445378
> 584292 608618935 1 584292 2815445378 12/08/2006
> 04:48:48 2815448304
> 584293 608618941 1 584293 2815448304 12/08/2006
> 04:48:55 2815451141
> 584294 608618950 1 584294
> ...
>
> read more »- Hide quoted text -- Show quoted text -

Oracle comes with a complete set of manuals. The dynamic performance views, v$, views are documented in the REFERENCE manuals. The entry for v$log_history says:
>>

This view contains log history information from the control file. <<

Looking at the data you should recognize that there is one entry for every log switch.

And has been pointed out by Charles you are switching way too often because your online redo logs are way too small. You can find instruction for recreating the online redo logs in the DBA Administration manual.

If you do not have the documentation CD you can find all the Oracle manuals online via the main Oracle site which will point you into technet or at http://tahiti.oracle.com.

HTH -- Mark D Powell -- Received on Fri Dec 08 2006 - 11:37:08 CST