Question:
I am planning a reorg of PSAPFACTD to switch this tablespace from Dictionnary Managed (DMTS) to Locally Managed (LMTS) with ASSM. It is around 250Gb and PSAPFACTI is around 280Gb. I am doing tests and I believe that this reorg using SAPDBA will take roughly 48 hours... that's 48 hours of downtime...
I am working to parallel things up and play around with initSID.ora parameters to make this faster. I have a test server that was refreshed from PRD server and tests are slow becasue each pass takes roughly 48 hours.
dxaver proposed using R3LOAD instead in THIS POST. I will surely investigate this.
Has anyone done a similar reorg and can provide me with clues or helpful hints to speed this up.
config:
SAP BW 3.1
Oracle 9.2.0.7
Windows 2003
4 CPU
Thanks to all
Answer:
Have you thought about using 3rd party tools, like Quest LiveReorg or BMC SpaceManager?
Answer:
Are you reorging with parallel processes? The more defined dumpdevices, the more parallel processes you should be able to use (you also need to specify the number of processes in SAPDBA when setting up the reorg).
Answer:
Have you thought about using 3rd party tools, like Quest LiveReorg or BMC SpaceManager?
Yes I am considering LiveReorg. I am unsure about LiveReorg capability to reorg PSAPFACTD contents and changing to LMTS at the same time. I know that LiveReorg can move tables from a tablespace to another that was created as LMTS, but in BW, I don't believe that this can be easily achieved becasue SAP might have hardcoded the BW cubes to PSAPFACTD.
Are you reorging with parallel processes? The more defined dumpdevices, the more parallel processes you should be able to use (you also need to specify the number of processes in SAPDBA when setting up the reorg).
Yes, I made some test and this works well with smaller tablespaces. For an unknown reason, SAPDBA only parallels PSAPFACTD in 2 dump files even if I specify more... one dump file is 8gb and the other one 142gb... bad job from SAPDBA! The problem may lie in the fact that PSAPFACTD does not hold many tables but each one is big and organized mostly in partition tables...
More inputs will help me!
Answer:
markdijs, if you have documentation or can point me to an OSS note... it looks like the OSS notes system is broken right now as it will not display any notes right now...
_________________
SapFans Moderator
NetWeaver ‘04–SAP Web AS for ORACLE certified
Search: /forums/search.php
SAP Notes: http://service.sap.com/notes
SAP Help: http://help.sap.com
Basic Rules: /forums/viewtopic.php?t=222759
Answer:
I don't believe that this can be easily achieved becasue SAP might have hardcoded the BW cubes to PSAPFACTD.
I don't think so. In NW04 and NW04S, they have other names, like PSAP<SID>FACT, ODS and DIM. See SAP Note 502989
Answer:
thanks snmsee,
There must be a way to change that. I will need to find all references to PSAPFACT other than DD* tables.
Answer:
Hi ,
The relation between TABART (Table data class) and tablespace names is kept in tables DDART, TAORA and IAORA. See SAP Note 778784. Here you can influence in what tablespaces tables are created.
For other reorg options, have a look at SAP Note 541538, which refer to the BRSPACE option (SAP Note 646681). You also might consider upgrading to Oracle 10g and utilize better Online reorg options in Oracle.
Answer:
thanks again for that info.
so, from what I understand, something in table's definition is referring to TABART (as seen in SE11). So, if I completely export PSAPFACTD and export it to PSAPFACT1D and adapt tables TAORA, IAORA and DDART, this would work. So when reorg will be finished, all new objects will be created in the new tablespace. Fair enough.
We are planning to go on Oracle 10g in january 2007. I might wait to reorg big tablespaces after this upgrade. For now, I hate the new BRSPACE interface and find it less user-friendly. I like SAPDBA's reorg interface so want to do as much as I can with this soon-to-die interface while I am still at Oracle 9i.
I am still considering testing R3LOAD as it may be faster as per markdijs.
By the way, OSS notes are usable again.
Answer:
so, from what I understand, something in table's definition is referring to TABART (as seen in SE11). So, if I completely export PSAPFACTD and export it to PSAPFACT1D and adapt tables TAORA, IAORA and DDART, this would work. So when reorg will be finished, all new objects will be created in the new tablespace. Fair enough
Correct. Some explanation can be found at section 4.2.3 of http://www.lulu.com/content/376357
I am still considering testing R3LOAD as it may be faster as per markdijs.
With R3load, you're able to run jobs in parallel.
Answer:
Additional info in this SAP Note 771191
Answer:
Have you thought about using 3rd party tools, like Quest LiveReorg or BMC SpaceManager?
These tools can do the job, however due to the number of objects (eg each partition is one object) in the tablespaces (PSAPFACT*/PSAPODS*) setting up these jobs and running them will take forever. We used SpaceExpert for our R/3 system only. We used brtools for reorging our BW, APO, and CRM systems.
Answer:
You also might consider upgrading to Oracle 10g and utilize better Online reorg options in Oracle.
I read about this and it looks like Online Reorgs is already available in 9i. In SAP systems, it looks like I can use BRSPACE to accomplish this (but not SAPDBA).
So my 3 possibilities:
1. testing R3LOAD
2. create PSAPFACT2D as LMTS with ASSM and slowly migrate tables from PSAPFACTD. Then manipulate TAORA,IAORA and DDART to put PSAPFACT2D as the default tablespace. When PSAPFACTD gets much smaller to a 'reorgable' size , I might consider reorging it with SAPDBA and slowly move back objects from PSAPFACT2D
3. create PSAPFACT2D as LMTS with ASSM and slowly migrate all tables from PSAPFACTD. Then reorg empty PSAPFACTD and leave it there. I read somewhere that standard tablespaces should not be deleted because support packs might want to try to write in the default tablespace as this might be hardcoded... Now that I know about TAORA,IAORA and DDART, I'm not sure about what his guy wrote (see doc below) but I beleive that snmsee has the right answer.
A very interesting doc on a reorg strategy is available: Boost SAP R/3 Performance by Reorganizing Your Oracle Database: A Proven Reorganization Strategy.
snmsee, thanks a lot for you link, this document is well done and it is free. Are you the author? If so, good job!
swimpory and nlsme3, thanks for the info. your help is really appreciated. I love sapfans!
If anyone has more info, please feel free to provide info.
_________________
SapFans Moderator
NetWeaver ‘04–SAP Web AS for ORACLE certified
Search: /forums/search.php
SAP Notes: http://service.sap.com/notes
SAP Help: http://help.sap.com
Basic Rules: /forums/viewtopic.php?t=222759
Answer:
Hi ,
That's why this is a discussion forum; getting to a common conclusion.
2. create PSAPFACT2D as LMTS with ASSM and slowly migrate tables from PSAPFACTD. Then manipulate TAORA,IAORA and DDART to put PSAPFACT2D as the default tablespace. When PSAPFACTD gets much smaller to a 'reorgable' size , I might consider reorging it with SAPDBA and slowly move back objects from PSAPFACT2D
3. create PSAPFACT2D as LMTS with ASSM and slowly migrate all tables from PSAPFACTD. Then reorg empty PSAPFACTD and leave it there. I read somewhere that standard tablespaces should not be deleted because support packs might want to try to write in the default tablespace as this might be hardcoded... Now that I know about TAORA,IAORA and DDART, I'm not sure about what his guy wrote (see doc below) but I beleive that snmsee has the right answer
before you consider this for BW, you have to take a look at SAP Note 771191, because there are more technical tables you have to adjust (e.g. RSDCUBE).
snmsee, thanks a lot for you link, this document is well done and it is free. Are you the author? If so, good job!
Yes. Thanks.
Answer:
I finally chose this strategy:
1. Create PSAPFACT2D as LMTS with ASSM
2. Slowly migrate tables from PSAPFACTD using online reogs:
brspace -f tbreorg -t "/BIC/ECUBE1","/BIC/FCUBE1" -s PSAPFACTD -n PSAPFACT2D -i PSAPFACT2I -p 4 -e 4
3. manipulate TAORA,IAORA to put PSAPFACT2D as the default tablespace. On some systems, people mught need to consider tables DD09L, RSDCUBE, RSDODSO (ODS) and RSTS (PSA)
update sapr3.taora set TABSPACE='PSAPFACT2D' where TABART='DFACT';
update sapr3.iaora set TABSPACE='PSAPFACT2I' where TABART='DFACT';
4. alter table sapr3."/BIC/FCUBE" modify default attributes tablespace PSAPFACT2D;
5. When PSAPFACTD/I are finally empty, drop the tablespace
6. if on Oracle 10g, rename PSAPFACT2D/I to PSAPFACTD/I
7. if On Oracle 9i, create PSAPFACTD as LMTS/ASSM and move objects back with online reorg.
So, I will reorg all that I can offline with SAPDBA with Oracle 9, upgrade to Oracle 10 and choose the previous strategy to rebuild big tablespaces.
People should also consider that this strategy does not take care of LONG and LONG RAW fields you may find in other tablespaces. PSAPFACTD does not contain such fields.