Reorg DBTABLOG online

[Detelin_Nedev]

Hello Guys,

We would like to reorganize DBTABLOG online but not using BR*Tools, because we would like to avoid LONG2LOB conversion.

The idea is the following.

1. Shutdown SAP system

1. Rename DBTABLOG to DBTABLOG_OLD

2. Create a new table DBTABLOG based on DBTABLOG_OLD

3. Apply all constraints to new DBTABLOG

4. Start SAP System

5. Start a PL/SQL procedure that will insert the records from DBTABLOG_OLD into DBTABLOG.

In parallel there will be inserts from the SAP system into DBTABLOG as well.

The records that should be inserted into the new DBTABLOG are around 650 000.

What do you think about slowing the whole performance of the system?

Best regards,

Detelin

[fidel_vales]

Detelin Nedev wrote:
Hello Guys,
We would like to reorganize DBTABLOG online but not using BR*Tools, because we would like to avoid LONG2LOB conversion.
The idea is the following.
1. Shutdown SAP system
1. Rename DBTABLOG to DBTABLOG_OLD
2. Create a new table DBTABLOG based on DBTABLOG_OLD
3. Apply all constraints to new DBTABLOG
4. Start SAP System
5. Start a PL/SQL procedure that will insert the records from DBTABLOG_OLD into DBTABLOG.
In parallel there will be inserts from the SAP system into DBTABLOG as well.
The records that should be inserted into the new DBTABLOG are around 650 000.
What do you think about slowing the whole performance of the system?
Best regards,
Detelin

Hi,

you mention that you want to reorganize DBTABLOG "ONLINE" and the first thing you do is to shutdown SAP.

AFAIK that is not online.

From that point, anything you do will not be an online "reorganization"

also you mention that you want to avioid LONG2LOB conversion. WHY?

[willi_eimler]

Hi Detelin,

your approach is very tricky and you will fix the problem with the high water mark of dbtablog. In my opinion this procedure have only advantages. The primary key of the dbtablog is: LOGDATE,    LOGTIME and  LOGID . LOGID contains milliseconds in order to be able to address changes within milliseconds. I don't see a show stopper. Every new entry to dbtablog should not use a key of the entries you want to move from dbtablog_old to dbtablog. If you use this procedure please tell us about your experiences.

P.S. Don't forget the indexes of dbtablog.

Best regards

Willi Eimler

[Reagan]

Also read this SAP note

Note 976435 - Support for Oracle Data Pump in BRSPACE

It speaks about replace option during import which will replace the existing content with the data from the dump.

Regards

RB

[Detelin_Nedev]

Hello all,

Thank you for your answers and suggestions.

Actually this is my opinion as well but I would like to collect more pieces of advice.

The idea came from the fact that our data center service provider estimated around 10 hours downtime for this table reorg. The current size of the table is around 500 GB with indexes and have more that 1200000 records. Half of these records will be deleted with archiving tools, so I think RSTBPDEL will not be needed.

So, this was the idea to minimize the downtime however I am more considering the whole system performance in this scenario.

BR,

Detelin

[former_member206552]

Hi Detelin,

I agree with Deepak, as i would also use export import but with oracle data pump then you can use parallel processes which will take much quicker (as this is a big table)

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

System preformance will be affect but this also depends largely on the type of system you have and what type of disks you are using

Best Regards

Marius

[Reagan]

1. Shutdown SAP system

2. Update BRtools

3. Delete unwanted data from the table - probably using RSTBPDEL

4. Export table DBTABLOG using BRTools

5. truncate table DBTABLOG

6. Import the dump to the table DBTABLOG using BRTools

7. Start SAP system

Note: make sure you use BRTools as this will let you use Oracle export and import tools to do this activity.

Regards

RB

[former_member188883]

Hi Detelin,

If you plan to take SAP application down, my suggestion would be to perform table export and import.

It will be much faster than the steps you chose above.

You may use oracle uilitilies like exp to export and imp to import.

More details can be found in oracle documentation about optimization of export/import.

http://docs.oracle.com/cd/B19306_01/server.102/b14215/exp_imp.htm

Hope this helps.

Regards,

Deepak Kori