Truncate Bi File

[catch.saravana]

9.1E version: Let's say I have a large BI file that is grown to 15 GB and reached the critical limit. In our project they take a backup (will run for 7 hrs) before truncating the BI. Is a backup necessary? Can't I bring down the DB, do a roll forward and truncate the BI? May be a dumb question but would like to know what will be the effects if we do it without a backup - if we can then it will save us a good amount of prod downtime. [We do incremental backups everyday and a full back up once a week]

 

[Rob Fitzpatrick]

If the objective is to truncate the BI then it should just be necessary to shut down the database, truncate the BI, optionally grow the BI by an additional number of clusters to get it to its typical size, and then restart the database. The proutil bigrow may potentially be slow, depending on storage hardware performance and database structure. For this reason I typically use a fixed extent for the portion of BI file I always expect to use. E.g. If I expect the BI to grow to 256 MB then I would have a 256 MB fixed extent and a variable extent. It helps with the database open after the proutil bigrow.

If this is happening on a regular basis then if I were you I'd be focusing my efforts on why the BI is growing; likely due to poorly-scoped code causing long transactions.

I don't see what roll-forward has to do with this. Roll forward is something that happens on a recovery database, e.g. on your DR system, not on a production system that was shut down normally as part of a scheduled maintenance activity.

 

[catch.saravana]

Thanks Rob. Yes the objective is to truncate the BI. We have identified the program that has caused this issue and we have fixed the same.

I don't see what roll-forward has to do with this. Roll forward is something that happens on a recovery database, e.g. on your DR system, not on a production system that was shut down normally as part of a scheduled maintenance activity.

Agreed Rob, I got confused with the other discussion we were having this morning.

 

[catch.saravana]

I hear from senior DBA's that they will need to recreate the hotspare DB in this case. Do we really need to recreate a hotspare DB in this case? - if not I need to check with them if they feel it as a best practice or what is the necessary of it.

 

[Rob Fitzpatrick]

You don't need to recreate the hot spare if you truncate bi on the source. If you are using OE Replication then you cannot truncate bi on the target.

 

[catch.saravana]

We use AI based replication.

 

[cj_brandt]

you can't truncate the stand by database's BI file and then continue to apply AI files to it.

 

[catch.saravana]

@cj_brandt - True, we are not truncating the BI file of Hotspare DB (Stand By DB). We wanted to truncate BI of only live DB.

I understand BI of hotspare db will also get increased but haven't reached the critical stage. As the program has been identified and fixed I hope it will not grow any further. We have to add a new extent by end of next week which will force us to recreate the hotspare db by that time and that will resolve the large BI file issue on hotspare.

 

[cj_brandt]

You can add extents to the live DB or the Hotspare DB without having to recreate the hotspare db.

"We have to add a new extent by end of next week which will force us to recreate the hotspare db"

 

[catch.saravana]

@cj_brandt - I haven't done this before, let me try on our dev machine. But I heard from couple of senior DBA's saying we will have to recreate hotspare db or we will get an error during AI Roll Forward saying 'Area # mismatch'; if in case we are using OE Replication then we don't have to recreate the hotspare DB. As I mentioned earlier on this thread we are using AI based replication. Is this assumption wrong? Please advise.

 

[TomBascom]

"Area #" mismatch would be a result of adding storage areas.

Not from merely adding extents.

A new storage area will have a new area #. A new extent will use an existing storage area#.

Extents are a sub-unit of a storage area. The storage area is the "logical" thing, the extents that make it real are the "physical" portion. You need to have the same storage areas defined in both databases but you can implement a different number of extents of different size and in a different path.

 

[catch.saravana]

My bad - In this application we have all area's being defined with variable extents (no fixed extents at all). I meant area and not extent. In this case we are adding 2 new AREA's each of them with a variable extent (no fixed extent).

Sorry for the confusion @cj_brandt @TomBascom.

 

[TheMadDBA]

You can still add new areas (or extents) without breaking AI based replication. You just need to add the areas and extents to the target (the one you apply the AI files against) first.. and then to the source database.

I assume this is still true for 9.1E (which is ancient and unsupported). It was released in 2004.. when Google went public, MySpace was the hot new thing and Facebook didn't exist.