Reorganize tablespaces in Oracle standby server

[former_member273662]

Dear All,

Our current configuration is

SAP ECC 5.0

Oracle 10.2.0.2.0

HP/UX 11iv1

We have Oracle Data Guard installed and working. Since a couple of our SAP tablespaces are very fragmented, we would like to reorganize them using brtools. These tablespaces are large (300+ GB) and we don't have the downtime to do the reorganization in the primary server. I was wondering if we can do the following:

1. discontinue applying archive logs to the standby

2. open the standby database

3. reorganize the tablespaces

4. shutdown and mount the standby database

5. continue applying the archive logs from step 1 on

I am not sure step 5 would work. The SCN would have changed during the tablespace reorganization.

Thank you very much,

Fong

[former_member204746]

here's what I would do. whenever you have downtime, rename PSAPBTABD to PSAPBTAB2D and PSAPBTABI to PSAPBTAB2I.

then create PSAPBTABD and PSAPBTABI,.

start SAP

now, follow SAP note 600641 and reorg everything online.

you might need to adapt your standby database accordingly.

[former_member273662]

Hi Eric,

Thank you.

Looks like SAP note 600641 is not the right note and is in German?

Note 600641 - Pflegebericht - Korrekturen 4.63B

Also, since our primary server is production critical, we are worrying about some locking issues during the online reorganization. That is why we would like to do the reorganization in the standby server.

Best regards,

Fong

[lbreddemann]

> Also, since our primary server is production critical, we are worrying about some locking issues during the online reorganization. That is why we would like to do the reorganization in the standby server.

Stop worrying and learn how online reorgs actually works!

You've payed a huge bunch of money for a DBMS that allows online reorgs and now you don't want to use it because you are in fear.

And you are in fear because you don't seen to know but in doubt and uncertainty...

Bad place to be - especially if your system is so critical.

There is no way you can move your data on your standby size and then be able to re-synch the data with the primary site, since only physical standby is supported for SAP systems (that is block changes are transferred from primary to standby db - not SQL commands).

With online reorg you can actually do what the name promises - perform your reorgs online, while the whole application is fully up and running.

If you don't trust it (which is a good thing), then try it out on a test system before (you should practise it anyhow before you reorganize the production db).

regards,

Lars

[former_member204746]

OOPS

Sap note is 646681. Sorry about that.

[former_member273662]

Hi Eric and Lars,

Thank you. I had read this note and others as well before I posted the question. Now I know the standby won't work because it is not a logical standby.

Lars, it is not that I am in fear of online reorgs, a couple months ago I did a secondary index creation online and that caused some issues.

Best regards,

Fong

[lbreddemann]

> Lars, it is not that I am in fear of online reorgs, a couple months ago I did a secondary index creation online and that caused some issues.

And that relate to online reorgs ... how?

Online reorgs do build up indexes on the target tables.

But they do this before the target table is set active for the SAP workprocesses.

Therefore, you're not even working on the same segments here.

regards,

Lars

[former_member273662]

Hi Lars,

That was an instance that SAP Support (on SAP Message) came back with "there should be no problem" answer on the Online secondary index creation. I am just more cautious after that.

Thank you for the information,

Fong