Skip to Main Content
  • Questions

  • xTTS Oracle Data migration from big endian to little endian

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prasanna.

Asked: December 18, 2019 - 1:43 pm UTC

Last updated: January 07, 2020 - 3:16 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi Tom,

We have a SAP Oracle database with large no. of objects count. DB Size is less which is around 500GB. But tables count is 75000, indexes are around 95000 and other objects around 10000 each.

When we do the TTS migration from source to target using rman convert (We use xTTS and used Oracle Perl script). This perl script calls RMAN to do backup and convert & apply to target.

We do the export of metadata with full=y & transportable=always. This is completing within 2 hours.

But import of this metadata, runs forever. For this scenario, its taking 12 hours. There are no abnormal waits on the DB. Every second I see 5 to 6 tables getting imported. followed by index, which is 4-5 indexes objects getting imported every second.

This is one expdp and one impdp command. As the limitation, we didnt use parallel since transportable_datafile parameter doesnt support parallel in the import. We really need to use the parallel on the import metadata part. but impdp does both (12c feature) which is transportable_datafiles and metadata together with no parallelism.

Is there any recommendation from you on this ?

Thanks
Prasanna

and Connor said...

It is typically not the datafiles, but the metadata associated with each objects. The most common large piece of metadata for the objects is optimizer statistics. Once you start adding in histograms on each column, that can be a lot of metadata.

To see where the time is being lost, you can check v$active_session_history, or you can do a system wide trace during the import, ie

1) alter system set events='10046 trace name context forever, level 8';
2) run the impdp
3) alter system set events='10046 trace name context off';

There is also a trace=level parameter in datapump itself

 level   Purpose
 ------- -----------------------------------------------
 10300   To trace the Shadow process (API) (expdp/impdp)
 20300   To trace Fixed table
 40300   To trace Process services
 80300   To trace Master Control Process
 100300  To trace File Manager
 200300  To trace Queue services
 400300  To trace Worker process(es)
 800300  To trace Data Package
 1000300 To trace Metadata Package
 1FF0300 To trace all components          (full tracing)


Several trace files will be created (because datapump uses worker slaves) but that should give you some insight into the cause.

If it is indeed the optimizer stats, then you could potentially defer importing them until after the datafiles are transported. And you could then do some (manual) parallelisation of that.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

prasanna, January 06, 2020 - 12:47 pm UTC

Thanks Tom.

We did exclude statistics during the expdp process. So my assumption is that impdp process will not have any statistics to be processed. Also one more update. We found out that the same dump processed in standalone RAC vs 2 node cluster is 2 hours vs 4.5 hours respectively. So we did changed the 2 node cluster to single node (CLUSTER_DATABASE=FALSE) and restarted the import from scratch. This reduced to 2.5 hours. I think this is something related to gv$ view updates for the metadata. What do you think. ?
Connor McDonald
January 07, 2020 - 3:16 am UTC

This sounds like perhaps some data pump slaves were spread across nodes - which would be fine for *data* loading, but for transportable tablespaces etc, which is just hard core dictionary maintenance, it might be introducing just a huge amount of cross-instance chatter and locking.

You should not need to set cluster_database to false. You should be able to just create a service which is bound to a single instance, and then connect to that service with your import job

Or use cluster=n when importing (can be used with expdp as well)

A reader, January 07, 2020 - 6:13 am UTC 


Cheers!

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database