ORACLE offline reorg...

[Former Member]

Hi,

I'm running Test system (will be prod.) - after client deletion there would be need of database reorg. Before deletion database size was a bit over 300GB - after deletion disk space shows the same number (also disk BRCPACE). However I need the additional space that would come from reorg (ex. need to delete some database files to return disk space). I have checked the manuals for Online reorg but i would like to do Offline reorg - Could someone please turn me to right direction how to?

(There seems to be many manuals/howto's/explanations about online but not for offline reorg). Also what would I need to expect?

Br,

Kristjan T.

[former_member213250 · Accepted solution]

Hello Kristjan

During/after offline re-org make sure you thoroughly check your re-org job logs as well.

Recently I noticed one scenario,

where indexes are set to unusable state, because of lack of temporary tablespace, one of primary index ( several GB size) was left in this unusable state and customer didn't noticed the error in job log restarted DB had severe performance issue.

if you notice any such index unusable errors, better rebuild them indexes before complete DB restart.

So make sure you have enough temporary tablespace size (eg: twice the size of your largest index )

Hope this might be of some help.

Regards

Venkat

[former_member204746 · Accepted solution]

I would use SAP note 646681,

start by reorg'ing the smallest tablespaces. this wil free up space and hopefully give you enough free space to reorg the big ones. you can use the online strategy and leave SAP down which works marvelously well.

[Former Member · Accepted solution]

Hi,

Thank you for the wery helpful info - just wat I was looking for...

Allthough - I have SAP kernel over 7.0 and Oracle database 10.0.2.0 - what my additional question is:

I did not read from that note - does my tablespaces need also long2lob conversion or not?

(Doing this first time and failure is not an option...)

Or can i just start the reorg option according to that note after section III ???

Edited by: Kristjan T on Apr 2, 2011 4:19 PM

[former_member204746 · Accepted solution]

does my tablespaces need also long2lob conversion or not?

maybe.

you cannot reorg LONG online... unless you convert them

other option is to export LONG .

so, this is your choice.

[Former Member]

In case you want to execute an offline reorg, you should perform a homogeneous system copy using export/import method.

Just follow the system copy guide of SAP, which can be found at: http://service.sap.com/instguides

The only thing you should be aware of, is that the creation script for the target database should not have the same size as the source. In case you use auto-extensible tablespaces, you never allocate too much space. The database will grow automatically during the import. Using auto extend, will generate some overhead during import, so take that in mind.

Kind regards,

Mark

[Former Member]

Hi,

You can either target the large tables via DBO2 (db02old) and do an online reorg using brtools.

This will free up space but will free up space within the tablespace not back to disk.

Or

Export the database(this will reorg the database) trash the datafiles and import the dump again, you will then see space

available on disk assuming you do not oversize the tablespace builds and next extents in SAPinst.

You will find the documentation on SMP 'Homogenous system copy'

Mark