To do reorganization of database after data archiving project

[Former Member]

Dear all,

We finished a data archiving project recently.

So we plan to do a reorganization of database for our production system.

We did two times online reorganization on our test system as follow:

before each reorganization, we did a system copy from procution system to test system to prepare the environment.

1

do a online reorganize on SAPSR3 tablespace using brtools.

this way takes up long time(over 40 hours,maybe because of our server is not very good).

it save free size of SAPSR3(17% to 30%), but the data buffer quality become poor with a value 40% in ST04 view,

and I found big number of fragments with a value 997(it is 7 before reorganization) of SAPSR3 in DB02 view.

2

then we do the second time reorganization, only reorganize the relevant tables with data archiving project.

we reorganize these tables one by one manually using brtools.

the total time is acceptable but the result is still not good.

we get poor data buffer quality and fragments number again.

So we are thinking to do a offline reorganization of database since the online reorganization result is not good.

It seems that the offline reorganization is using export and import this way.

I found the the command menu export tables and import tables in brtools.

It seems we can use brtools to do offline reorganization.

My questiones are as follow:

1

Should we do the offline reorganization on database level or tablespace level ?

I found only tablespace option and table option on export tables menu of brtools.We plan to do it on SAPSR3 tablespace level.

2

What is the standard detailed procedure on offline reorganization?

for example, what we should care before doing offline reorganization,

how to export and import(mainly about procedure) , etc.

or please give the relevant information or data to study about it.

P.S., our database version is ORACLE 10.2.0.2.0, SAP version is 4.6C.

Anyone experienced or expert,

Please kindly give advance.

Regards,

Allen

[former_member204746 · Accepted solution]

Hi,

Do not reorg within the same tablespace

please review SAP note 646681.

you should do this another way,

when you have downtime, rename tablespace PSAPSR3 to PSAPSR3OLd.

now, create a new PSAPSR3.

then, reorg online all contents from PSAPSR3OLD to PSAPSR3.

finally, when completed, drop tablespace PSAPSR3OLD.... just make sure that it is really empty before doing so.

This will need additional disk space but you will really get a real good clean reorg with this process.

[Former Member]

Did you check SAP Note 541538? It contains information about the reorganization process.

You should also note that your database release 10.2.0.2 is no longer supported. You should think about upgrading it to version 10.2.0.4.

Kind regards,