OSDB Migration - Sorted export tables failed with shortage of PSAPTEMP

[former_member339862]

Dear SAP community,

                                      We are doing  POC for migrating ECC 6.0 system from one hardware to other hardware. The requirement is migrating same Oracle DB from HP-UX on IA64 to RHEL on X86_64 (i.e from Big Endian - 4102  to Little Endian - 4103) . According to Note 954268,since this involves code page change cluster tables have to be unloaded as sorted always . But to achieve this , we need to have PSAPTEMP configured twice the amount of largest table and one of our largest cluster table is around 500 Gig.Even if we perform parallel export of split cluster tables, we need to have around 2 TB of PSAPTEMP tablespace and is difficult to get this much due to technical constraints. Issue occured when we tried unloading all tables using sorted option ( as SWPM doesn't allows selectively ) without success and due to this most of the table exports terminated with errors due to PSAPTEMP space issue.

Is there any alternate way to achieve this ? Like just exporting cluster tables with sorted and other transparent tables as unsorted or any other workaround?

Appreciate your help in this matter.

Thanks,

Hari

[Former Member · Accepted solution]

Hello Hari,

has already provided a nice overview of tasks.

All I can add is this.

1. I agree with Siddhesh in that I'm not aware of the 2 * largest table statement. What I do is that I take the sum (plus some contingency) of all tables that I will have unloading in parallel (sorted) as the size of PSAPTEMP.

Also bear in mind that if your PGA is sized aggressively then your probability of hitting PSAPTEMP is reduced.

2. If, as it seems, your DB is very big then use MIGMON where you can control the template you use. That way you can unload unsorted (clusters are hard coded for sorted unload).

eg: using ddlfile DDLORA_LRG.TPL.

Additionally, it is also possible to use different *.TPL templates where you can specify packages or tables that you wish to unload sorted/unsorted.

I hope you have a better overview of the possibilities and approach now.

KR,

Amerjit

[former_member185954 · Accepted solution]

Hello Former Member

You are right, I forgot about custom ddlfile, there is so much tinkering you can do, if only we had all the resources and time in the world, the world would be such a nice place

Good Luck Hari.

Regards,

Siddhesh

[former_member240105 · Accepted solution]

Thanks Siddhesh and Amerjit for your feedback. I will take back the statement on PSAPTEMP as I exactly don't recall where I have read.

My DB size is 5 TB and since this involved  OS code page conversion , I have opted for sorted export and this has put the huge burden on PSAPTEMP.I have used oracle PL/SQL splitter which comes along with SWPM package itself to split the large tables.

In the latest SWPM there is an option either to select sorted or unsorted export but my question is if I select unsorted export does R3load has intelligence to know what are cluster/nametab/report tables and export them in sorted order ?

The below two notes mention about interested unsorted unload topic.

1066404 - R3load, ORDER BY, Unicode conversion of cluster tables  - "Newer R3load versions (see Note 954268) ensure that cluster tables are sorted when unloaded "

954268 - Optimization of export Unsorted unloading - The latest R3load from the SAP Service Marketplace, which ensures that specific tables (type: report, screen, nametab) and cluster tables are always unloaded sorted during a codepage conversion - irrespective of the configuration in the DDL<db>.TPL file.

R3load 6.40, as of patch level 55 or as of the compile date February 10, 2006 2006

R3load 7.00, as of patch level 10 or as of the compile date February 10, 2006 2006

I am really interested to explore this option as I don't want to do start migmon manually and export cluster packages separately and transparent tables separately.

I have not seen this options in SDN forums except in Notes and hence wanted to check if anyone has already tried this or has any idea of how it works.

Thanks in advance for your time and feedback.

Thanks,

Hari

[Former Member · Accepted solution]

Hello Hari,

1. Short answer = YES. R3load knows (hardcoded) what needs to be exported sorted.

NB: Always use the latest patch level of the R3l* tools and libdbsl where possible.

2. 5TB isn't that big. Is that 5TB allocated or 5TB used ?

3. Depending on your project timelines, performing a bit of spring cleaning in your system prior to the export is a good idea. OSS Note #706478.

4. As you are doing a Heterogeneous migration, the consultant who performs this needs to be OS/DB certified (TADM70).

Hopefully we have answered your questions so all that remains for your now is to test, test and test.

NB: Mark the thread as answered if you are happy with the replies.

Good luck.

KR,

Amerjit

[former_member185954 · Accepted solution]

Hello Hari,

Manually starting migmon is pretty easy, the system copy tools archive available on service marketplace has an embedded document called migration.pdf or migmon.pdf. This pdf has all options of migmon specified.

However, I am sure you can experiment on a sandbox first to make sure you are comfortable. I am assuming you are OS/DB certified or you have someone who is in your team, as that would be a requirement to implement this kind of work.

Regards,

Siddhesh

[JPReyes]

Just to add to all that, there is another option, you can do an "unsorted" export, this does not require such a large PSAPTEMP

           
954268  - Optimization of export: Unsorted unloading           

Regards, Juan

[former_member240105]

Thanks Juan.Since this is code page conversion that is migrating to different OS, our intention was to  export the data in sorted manner. After starting the export, we realized that we ran into problems with PSAPTEMP and huge runtime while unloading big tables even after splitting.Any how, Note 954268 clearly says, R3load itself will take care of automatically unloading  cluster,nametab...etc in sorted manner even though we select unsorted load. So we will try next dry run with unsorted export.

Thanks Siddhesh,Amerjit for your feedback.

[Former Member]

Hello Hari,

Can you mark the question as answered and close it out.

Good luck.

KR,

Amerjit

[former_member185954]

Hello Hari,

Never heard earlier that PSAPTEMP should be twice your largest table, ideally it should be > 20% of you DB size.

936441 - Oracle settings for R3load based system copy

Can you please check the above note.

if you have already checked and have the constraint that you can't increase the tablespace, you should consider skipping the table from your export using migmon and use oracle data pump to export it individually as flat file and import it target DB.

Regards,

Siddhesh

[former_member240105]

Thanks Siddhesh for your suggestion. Yes, I have followed the Note 936441 but since we are doing sorted export PSAPTEM storage space is constraint as we cannot use more parallel processing.

Coming to your point , we cannot use oracle data pump for few tables as it will not be supported by SAP and it export has to be done through standared R3 tools.

Thanks,

Hari

[former_member185954]

Hello Hari,

The statement about support isn't true, in many cases (OS/DB Migration) SAP recommends using DB tools for migrating Databases as native DB tools are always faster than export/import, this part is documented in TADM70.

What won't be supported is any problem arising out of the datapump functionality or incorrect use of it.

Considering the constraints you have, you maybe left with no options, If you have a test system, you can try the data pump mechanism and ask your functional team to perform tests later.

In anycase, all this is my assumption, each Migration project is unique.

You haven't provided total size of your DB, only data about size of your biggest table, you could split Here is what you could do.

1. Plan for Parallel export import procedure using Migmon
2. Split the biggest tables into manageable parts for e.g split 500GB into say 50 parts, with each part being roughly 10GB.
3. Use 1043380 - Efficient Table Splitting for Oracle Databases to work out splits more efficiently using PL/SQL splitter.
4. Create a spreadsheet of BIG TABLES that may cause huge strain on PSAPTEMP
5. Continuously monitor the export and reduce the number of parallel processes when BIG TABLE  splits are being exported
6. Use time analyser to work out how long each of the big split takes, optimize as necessary.
   
   
   

PS: I understand that PSAPTEMP is used for sorting, however can you please point me to a note or documentation where it says PSAPTEMP has to be twice the size of the biggest table. I somehow don't think remembering this.

Regards,

Siddhesh