Offline Re-organization of database

[Former Member]

Hi

We are using SAP CRM 4.0 ( ECC 5.0) on HPUX 11.23 and Oracle 9.2.0.6 as the database.

I would like to carry out an offline reorganization of the database. What are the steps involved, Do I use brtools to do this activity.

Some of my tables eg BALDAT are in RAW format.

Can someone who has done this type of activity guide me on this.

Regards

Alfred

[fidel_vales]

First, you should ask yourself

> Why?

Why do you want to perform a complete database reorganization?

Why do you want to do it offline? (raws must be done offline)

Will the benefits complensate all the time spent preparing and performing the reorganization?

I found out that a lot of people does not have a proper answer to any of the previous questions. There are very few cases where such thing is necessary

[Former Member]

HI Fidel.

Yes good questions.....

We have the following issues.....

1. We have a problem with the huge size of the tables...out of which there are 2 tables which are in RAW format. and are also huge in size. SAP itself have recommended a Reorg of these tables

2. Overall our Performance of our system at the database level is quite high...During our sales closing cycles. we see a utilization of the CPU at around 95 -99 percent throughout the period

3. I also need to look at oracle DB peformance tuning parameters. If you have any inputs on these please send it to me.....any notes related to this are welcome....anything that you have experienced in your work environment can you share it with us.

4. I've an issue on the mount points on which my Oracle DB is currently residing....Some mount points have reached a critical stage, I also need to move certains tables into a different tablespace

Our environment is - HPUX 11.23....and DB is on Oracle 9.2.0.6, My CRM application uses the transaction CIC0 ( Customer Interaction Centre) which is highly resource intensive.

Regards,

Alfred

[fidel_vales]

Hi,

I'll try to offer some advice:

> 1. We have a problem with the huge size of the tables...out of which there are 2 tables which are in RAW format. and are also huge in size. SAP itself have recommended a Reorg of these tables.

As you are on 9i then the reorganization of those tables has to be done online, but it is only two tables not the complete database.

> Can SAPDBA and Brspace be installed together.

Yes, you can use one or the other. I preffer BRSPACE as it is "newer" but the handling of OFFLINE reorgs is easier with SAPDBA.

For instructions you can take a look at the [SAPDBA|http://help.sap.com/saphelp_webas630/helpdata/en/d1/a9fe114b8a11d189510000e829fbbd/frameset.htm] documentation

To do it with BRSPACE, you can follow the relevant parts on the note

> 4. I've an issue on the mount points on which my Oracle DB is currently residing....Some mount points have reached a critical stage, I also need to move certains tables into a different tablespace

This can be done online, unless the tables have raw devices, in that case follow the previous point.

> 3. I also need to look at oracle DB peformance tuning parameters. If you have any inputs on these please send it to me.....any notes related to this are welcome....anything that you have experienced in your work environment can you share it with us.

I think this is too generic, you should analyze what is consuming the CPU, is it Oracle? or ABAP?

> 2. Overall our Performance of our system at the database level is quite high...During our sales closing cycles. we see a utilization of the CPU at around 95 -99 percent throughout the period

You should take a look at the parameter notes "124361 - Oracle parameterization (R/3 >= 4.x, Oracle 8.x/9.x)" and "358917 - Oracle DB parameterization for CRM and BBP"

>What other housekeeping activities can I undertake for bettering the performance at the Oracle level ?

This is too generic, usually I would advise to

1) check that your parameters are OK

2) 9i is "out of customer care support", the first year of extended maintenance is "free" and ends in August (I think) but you should be at least in 9.2.0.8 and latest optimizer merge fix.

3) Check for expensive statemens, focus on the statements generated during your closing cycles. Later on you can "check" other spensive statements. Perhaps you can install STATSPACK to help identify them

4) you do not mention if the high CPU utilization is on the DB server or AS, the issue could be that you have a CPU bottleneck for your activity.

[Former Member]

Hi Fidel

Is it possible to move a particular data file from once mount point /oracle/SID/sapdata1 to another mount point say /oracle/SID/sapdata2.....

Can this be done online

Regards

Alfred

[fidel_vales]

hi,

the instructions can be found on the note 868750.

It cannot be done online, it is not necessary to stop the DB but the tablespace must be offline

[former_member204746]

datafiles can only be moved offline. use SAPDBA or brspace to do this as it looks you are a newbie in Oracle maintenance.

[former_member204746]

.

duplicate deleted

[former_member204746]

read SAP note 646681, follow it like a cookbook. that`s what I did.

I made 2 reorgs:

1. with Oracle 9i, I used SAPDBA which I found easier to use. I also use brspace for the online reorg stuff

2. with Oracle 10g, I only used brspace. it worked great. with 10g, you can rename a tablespace which makes the process faster becasue you do not need to reorg twice.

so, my recommendation its ti upgrade to 10g first, then do the reorg. test all the steps with a sandbox to prepare yourself. run online reorgs when system activity is low or when a smamm number of user are logged.

if you need more help, just reply and I will share my experience.

[Former Member]

Hi Eric

Thanks for the reply......

I need to reorg an RAW format table which needs to be done offline.....

Can you guide me on what steps need to be followed while doing this.

Can SAPDBA and Brspace be installed together.

What other housekeeping activities can I undertake for bettering the performance at the Oracle level ?

Can you share your experiences on this, it would definetely help

Regards

Alfred

Edited by: Alfred DSouza on Feb 16, 2008 11:12 AM