Best procedure for online reorg + ACO + change to new TS layout on Oracle 11.2.0.2

[Former Member]

Dear all,

I have some questions about the best procedure for an onlinereorg including compression and a change to the new TS layout.

I´ve done it on some dev and sandbox systems and it works fine. First of all I will describe the procedure how I ´ve done the reorg including compression and a change to the new TS layout.

Helpful SAP-Notes:

355771 - Oracle: Erläuterung zum neuen Tablespace-Layout

1289494 - FAQ: Oracle Komprimierung

1436352 - Oracle 11g: Advanced Compression für SAP-Systeme

1847870 - Oracle 11g: Advanced Compression - Bek. Probleme und Lösung

1431296 - LOB-Umsetzung und Tabellenkomprimierung mit BRSPACE 7.20

Current state:

- Oracle 11.2.0.2

- SUSE Linux Enterprise Server 11 (x86_64) VERSION = 11 PATCHLEVEL = 3

- EHP6 FOR SAP ERP 6.0

- Old tablespace layout with locally managed TS and no ASSM

1. First attempt for LONG2LOB migration

- brspace -u / -f tbreorg -a long2lob -t "*"

an error occur because we do´nt have TS with ASSM

BR1185W Table SAPR3./SAPDMC/LSODOC located in a non-ASSM tablespace PSAPPOOLD

This error message helps to identify which tablespaces contain LONG fields.

2. Create new TS PSAPSR3

- brspace -u / -f tscreate -t PSAPR3 -d both

(create tablespace PSAPSR3 extent management local autoallocate segment space management auto default compress for oltp datafile '/oracle/M06/sapdata1/sr3_1/sr3.data1' size 30720M autoextend on next 1000M maxsize 32767M )

3. Second attempt for LON2LOB migration

brspace -u / -f tbreorg -a long2lob -o sapr3 -s PSAPDOCUD -t allsel -n PSAPSR3 -c ctablob -lc medium -SCT -p 2

brspace -u / -f tbreorg -a long2lob -o sapr3 -s PSAPDOCUD -t allsel -n PSAPSR3 -p 2

4. LOB2LOB migration

brspace -u / -f tbreorg -a lob2lob -o sapr3 -s PSAPDOCUD -t allsel -n PSAPSR3 -c ctablob -lc medium -SCT -p 2

brspace -u / -f tbreorg -a lob2lob -o sapr3 -s PSAPDOCUD -t allsel -n PSAPSR3 -p 2

5. Reorg and  compress tables without LONGs and old LOBs

brspace -u / -p reorgEXCL.tab -f tbreorg -a reorg -o sapr3 -s PSAPDOCUD -t allsel -n PSAPSR3 -c ctablob -SCT -p 2

6. Reorg from the rest of the tables

brspace -u / -f tbreorg -a reorg -o sapr3 -s PSAPDOCUD -t allsel -n PSAPSR3 -p 2

7. Delete empty source TS

brspace -u / -f tsdrop -t PSAPDOCUD

8. Create new statistics

brconnect -u / -c -f stats -t PSAPSR3 -f collect -p 2

9. Index rebuild with comression cind_only

brspace -u / -c force -f idrebuild -o sapr3 -s PSAPDOCUI -i allsel -n PSAPSR3 -c cind_only -ic ora_proc -p 2

10. Index rebuild without compression

brspace -u / -f idrebuild -s PSAPDOCUI -i "*" -n PSAPSR3 -p 2

11. Delete empty source TS

brspace -u / -f tsdrop -t PSAPDOCUI

12. Create new statistics

brconnect -u / -c -f stats -t PSAPSR3 -f collect -p 2

After I´ve dropped the empty TS I have to change the entries in the TAORA and IAORA manually to the new TS.

Normally the brspace programm do it automatically, but only if you create the new TS and tell him from which reorg TS he should take the tabart.

In this case it is not possible because we have many TS to reorganize and you can only assign one reorg TS to the new TS.

Now the questions.

When should I change the IAORA and TAORA ? Directly after I ´ve dropped the empty TS ? Could it happen that during the online reorganization errors occuring because the IAORA and TAORA are not on current state ? Should I do the reorganization + compression + change to new TS layout in the afternoon or the weekend ?

Thanks and best regards

Carsten

[former_member209959]

Hi Carsten,

I'm in a similar task that you. I want to migrate our ECC 6.0, Oracle 11g with tablespaces not ASSM to ASSM and migrating at the same time LONG to LOB.

Now I'm analyzing several notes and in 646681 - Reorganization of tables with BRSPACE, point 1. Creating a new target tablespace regards about the table data class with the option -l.

I'm not sure if it that will help you.

Regards.

[ACE-SAP]

Hi,

IAORA & TAORA are not used by SAP system outside of upgrade/reorg/system copy

It should not be a problem to update them afterward (778784 - Inconsistencies between data class and database container)

I do not think setting compression a tablespace level is a good option, I thinks it is better to only compress only the 20 biggest tables / index (index compression has to be performed explicitly it is not inherited from tablespace attributes => 1109743 - Use of Index Key Compression for Oracle Databases).

Check the remarks to that blog

Regards