Skip to Main Content
  • Questions

  • Migrating LOB data across Oracle databases with different versions

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rohan.

Asked: July 24, 2017 - 3:01 am UTC

Last updated: February 01, 2019 - 2:56 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hello,

Hope you are doing well.

We are working on a database migration exercise where our source Oracle database is on 11g (11.2.0) and the target Oracle database is 12c (12.1.0).

We are using expdp/impdp for exporting and importing data between the 2 databases. There are some tables which have LOB data in it and are in sizes over 450 GB.
An expdp on one of the table was running for more than 12 hours and finally failed with a snapshot too old error.

We wish to know is there an efficient way to migrate this LOB data from source to target database?
From what I know our application cannot have more than 5 hours of downtime hence we want to optimize the data migration activity as much as we can.

Below are some additional details I was able to gather through some test runs on lower environments where table sizes are smaller and on performance environment where table sizes mimic the production environment.

1. Source Database version: - 11g (11.2.0.2).
2. Target Database version: - 12c (12.1.0.2).
3. The character set on both databases is different.
a. Source database charset - WE8ISO8859P1
b. Target database charset - AL32UTF8

4. Since the databases have differing charactersets we cannot use transportable tablespaces, as to use them both databases should be on same characterset. (
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm#ADMIN11396
)
5. On a lower environment where the table sizes are smaller, a table with lob data of size 18 GB with COMPRESSION=ALL set in par file took 31 minutes for expdp command.
6. The same table when exported without the compression parameter in same database took 3 minutes for expdp command.
7. The tables having LOB data are not partitioned. They do have historic data in them which can pre-migrated before the actual go live.
8. The box on which the old database resides has 125 GB of RAM however the MAX SGA is set to 15 GB.

Queries: -

1. Does compression parameter actually makes the expdp process slow for LOB data?
2. We are thinking of exporting and importing (expdp/impdp) data in phases before hand since it is all historic and does not change using the query parameter in par file. Is this a good option? Since the table has close to 75 million rows we dont want the query parameter itself to add on to the expdp slowness. Any thoughts on any other way of picking up data in chunks beforehand?
3. The 2 databases are not in the same data centres and are geographically located at different places. In such a case would an expdp using network link really benefit? (We havent tried that approach yet.)
4. Apart from transportable tablespaces, is there any other feature we can use to migrate this data efficiently and in less time?
5. Does parallel work in case of expdp with lob data? I have read that even if we mention parallel degrees more than 1, Oracle employs only 1 process to perform the export of table having lob data? Please correct me if I am wrong here. if parallel does not work, what causes oracle to not run it in parallel.
6. Is there any other way to parallelize the expdp process lob data?
7. Would increasing the SGA size help expdp process run faster?

and Connor said...


But to answer your questions

1) Potentially. Compression costs CPU

2) Definitely. This statement

"They do have historic data in them which can pre-migrated before the actual go live."

is the key weapon you have at your disposal. With any form of migration, anything that can be done outside the outage window is a bonus.

3) Unlikely, but no matter what you do, I assume you are going to have to transfer that data somehow. You didnt mention "how far" but never underestimate the network bandwidth of an external hard drive and a vehicle :-)

5/6) There is nothing to stop you from running multiple datapumps using the query parameter to isolate different row ranges of the same table.

7) Possibly, but not orders of magnitude, just a few percentage points.

Rating

  (1 rating)

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

Comments

Why does expdp serialise source table export

Paul Dubar, January 31, 2019 - 2:57 am UTC

Hi Connor,

Enjoyed your preso at the AUOUG this year,

I am working on a similar migration with similar constraints - 11.2.0.3 source on Linux to 12.2.0.1 on AIX.
Test runs using the following impdp is completing in 4hrs 48mins for an estimated 106.6GB.

impdp userid=DEVDBAADM/xxxx@t_gmm1 schemas=GMM TRANSFORM=OID:N NETWORK_LINK=SGMM1.WORLD PARALLEL=16 STATUS=60 TRANSFORM=LOB_STORAGE:SECUREFILE EXCLUDE=TABLE_STATISTICS,INDEX_STATISTICS CLUSTER=TRUE TABLE_EXISTS_ACTION=SKIP LOGFILE=TMP_DGMM_DATA_PUMP_DIR:impdp_gmm_netlink_from_sgmm1_para16.log

Parallelism 16 is effective initially, but then the impdp settles into a state where it is reading large tables from the source in a seralised process. All the selects are issuing a NOPARALLEL hint.
So parallelism is now 4, on the last largest tables, that are being read serially via direct path read from source. This is the bottleneck.

This is what I see at the source
SELECT /*+ OPAQUE_TRANSFORM NESTED_TABLE_GET_REFS NESTED_TABLE_GET_REFS NOPARALLEL ("KU$") */ "ID","DATE_TIME_HASH","NODEDESCRIPTOR","PAYLOAD","ASSOCIATED_ID","JMS_PROPERTIES","ARRIVED","HOST_NAME","COMPONENT","STATE","PROCESSING_TIME"
FROM "GMM"."NAB_MESSAGE_STORE_INBOUND" "KU$"

I'd estimate that 90% of the import time is for these last 4 large tables. If the export is serial, then I guess so is the import.

Instance settings (2 node RAC)

parallel_force_local = TRUE
parallel_max_servers = 640 is too high with a VP 2 - reduce to 64
parallel_min_servers=64 is too high – set to zero – Oracle can allocate pq slaves as needed and deallocate when not
parallel_servers_target = 256 is too high - reduce to 64
parallel_threads_per_cpu = 2 (leave as 2 DEFAULT)
parallel_degree_policy = MANUAL change to ADAPTIVE
sga_target=32G


Is there any way to tell impdp to parallelise the export of these large source tables?

My expectation is that a network link across a gigabit ethernet would be the best approach.
Rudimentary calculations 106.6GB in 288mins ~ 379MB/min ~ 6MB/s (export/transfer/import)

I'm yet to test a expdp to file -> scp -> impdp and compare. (I don't have OS access)
Theoretical throughput of GBe is ~ 118MB/min. A 106.6GB file might xfer in 15-30mins (optimistic , yet to test)
Connor McDonald
February 01, 2019 - 2:56 pm UTC

Can we get the DDL for the 4 tables of interest. I'd like to see the data types etc.
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

Utilities

All of the database utilities are explained in the Utilities guide.