BW Tablespace ONLINE reorganisation

[Former Member]

Hi community,

to regain about 60GB wasted Disk resources I am planning to reorganize my ODS Tablespace on my BW System running Oracle 10.2.0.4.0

In the past we did this task offline, using export/import procedure. Now I think about the possibility of an ONLINE reorganization. In Note 646661 and 771191 I understood that there are some restriction on BW systems while moving a Tablespace. It seems that in BW there are dependencies between Tablespacename and BW-Tables.

When I use brspace to reorg online from old-Tabelspace to new-Tablespace and finaly rename the new-Tablespace to it's original name - what tasks I need to take care - sorry, note 771191 confuses me and I am not sure about the required steps

I appreciate your expierence

Alex

[Former Member]

This time I did the reorg again offline with expdp and impdp ... I will try the online reorg on my sandbox first

[former_member204746]

how big is PSAPODSD and PSAPODSI ?

You can reorg online especially when there are no loads being done in your BW systems. That's the best time frame.

you could reorg online to PSAPODS2D and PSAPODS2I, when finished, you will need a 5 minutes downtime to:

0. stop SAP BW

1. reorg again to get some objects that might have been create in original tablepsace

2. drop PSAPODSD and PSAPODSI if they are empty

3. rename PSAPODS2D to PSAPODSD

4. rename PSAPODS2I to PSAPODSI

5. restart BW

[Former Member]

Hi Eric,

Thank you for your feedback

>

> how big is PSAPODSD and PSAPODSI ?
> 

I use the new TS-Layout with only one PSAP<SID>ODS and no extra Index-TS -> It's about 150GB

Did I get you right ... the only fact I have to take care, that during the online Reorg no dataloads should happen?

What happens to the consitency if an user, loads data during the reorg, regardless I prohibited?

Kind regards

Alex

Edited by: Alexander Oswald on Oct 30, 2009 1:08 PM

[former_member204746]

You got me right.

users won't see a difference. you might have a small performance hit while reorg runs.

BW does not really care where the info is stored when reading it. the only problem is when new tables/indexes are created. this is when BW decides win which tablespace the objects should be created. If this happens, you might get some objects in PSAPSR3ODS and not PSAPSR3ODS2.

test this out on a test box first.