Data Pump

Database Migration from non-CDB to PDB – Migration with Data Pump

You may have realized that there are a few techniques missing describing how to do a Database Migration from non-CDB to PDB – Migration with Data Pump is one of them. I will explain the most simple approach of going to Single- or Multitenant. It isn’t the coolest – and it isn’t very fast as soon as your database has a significant size. But it is not complex. And it allows you to move even from very old versions directly into an Oracle 19c PDB – regardless of patch levels or source and destination platform.

Database Migration from non-CDB to PDB – Migration with Data Pump

High Level Overview

Endianness change

Behavior Change in Oracle 18c/19c: No symbolic links for Data Pump directories

Posted on July 15, 2019 by Mike.Dietrich Behavior Change 8

Most of you may have recognized the desupport of UTL_FILE_DIR with Oracle Database 18c. Reason is mostly that UTL_FILE_DIR opens a lot of possibilities to do insecure things. But this has another effect which may not be obvious to you. There’s a behavior change in Oracle 18c/19c: No symbolic links for Data Pump directories. Read further to learn more about it.

UTL_FILE_DIR Desupport

In Oracle Database 18c we announced the desupport of UTL_FILE_DIR initialization parameter. But as you can read as well in the Database Upgrade Guide, there’s this remark:

UTL_FILE Package Symbolic Link in Directory Paths Not Supported

Data Pump: The Time Zone Pitfalls

Posted on May 14, 2019 by Mike.Dietrich Flaws and Pitfalls 15

Photo by Luis Cortes on Unsplash

Last week a very experienced colleague called me. He had issues with an export dump taken from an Oracle 20c database importing into 19c. It failed. So this blog post is about Data Pump: The Time Zone Pitfalls.

The Case

Usually you will see this issue only when you try to export from a higher version, and then attempt to import into a lower one. But the same thing can happen when you patched your databases partially with a newer time zone patch regardless of the version.

My colleague saw this error:

impdp system/welcome1@//localhost:1521/MYDB

OOW 2018: How to Migrate to Oracle Autonomous Database Cloud

Posted on October 17, 2018 by Mike.Dietrich OOW 0

OOW 2018: How to Migrate to Oracle Autonomous Database Cloud

And last but not least our forth talk at OOW 2018: How to Migrate to Oracle Autonomous Database Cloud on Tuesday, Oct 23 at a fairly late slot at 5:45 p.m. (or 17:45h) as well in Moscone West 3004.

What is this talk all about?

It’s about the Oracle Autonomous Database cloud. But not so much about the Cloud offering itself. There are other talks covering this topic. We are the upgrade and migration guys. Hence, in this talk my boss, Roy Swonger and my PM mate Bill Beauregard will demonstrate you in a live demo how to migrate …

Data Pump 12.1.0.2 – Wrong Dump File Version – ORA-39142

Posted on July 16, 2018 by Mike.Dietrich Data Pump Flaws and Pitfalls Patch Recommendation 2

Data Pump 12.1.0.2 - Wrong Dump File Version - ORA-39142 Again I’ll have to thank my colleague Roland Gräff from the German ACS Support team in Stuttgart for bringing this into our radar. Roland alerted me a week ago about an issue with exports in Oracle 12.1.0.2 only when you are on a certain patch level. I summarize the issue here under Data Pump 12.1.0.2 – Wrong Dump File Version – ORA-39142.

In the below blog post you will learn about the actual issue, where it happens and when, and of course how to workaround it.

When does it happen?

The issue I will describe below happens only with …

Export with Data Pump and Long Identifiers

Posted on July 9, 2018 by Mike.Dietrich Data Pump 0

I blogged a few days ago about Long Identifiers in Oracle Database 12.2 and accessing the objects via database links from lower database version. As this raised a few questions, I realized there may be a bit more clarification necessary. One question was about what happens during export with Data Pump and Long Identifiers. That’s a pretty good question.

Export with Data Pump and Long Identifiers

I’d like to demonstrate the effect with a short example. I’m doing all my tests in a fresh PDB inside an Oracle 18.1.0 CDB from out Hands-On Lab. But you can repeat …

Upgrade and Migration Office Hours on March 21, 2018

Posted on March 16, 2018 by Mike.Dietrich Office Hours 0

The first Office Hours Session was just a month ago. It was a very good experience and based on the feedback we received from customers it was very helpful. You don’t believe it? Then you may watch the recording. But now it’s time for our second Upgrade and Migration Office Hours on March 21, 2018.

What are “Office Hours”?

This is a new interactive format we’d like to try out. Several groups in Oracle schedule Office Hours sessions. Roy and I held our first Office Hours session on February 21, 2018 for a full hour trying to answer …

Data Pump’s amazingly useful METRICS=Y and LOGTIME=ALL parameters

Posted on September 27, 2016 by Mike.Dietrich Data Pump 7

Now that I am back from OpenWorld, I will hijack the Upgrade blog while Mike is traveling. 🙂

Thank you to everybody who came to our presentations or stopped at the demo booth to chat last week. We had a great many conversations, and we always learn from talking to customers! One of the common questions about Data Pump came in the form, “I have a data pump job that used to run in X minutes, but now takes <multiple of X> minutes. Can you tell me what might be happening?“

Of course with that much information we …

Data Pump – Exclude Stats differently for TTS and FTEX

Posted on April 20, 2016 by Mike.Dietrich Data Pump Transportable 15

Nice little best practice for statistics and Data Pump when doing either Transportable Tablespaces or Full Transportable Export-Import (credits to Roy and Dean Gagne).

Transport Statistics via a Staging Table

First of all we always recommend to exclude statistics when doing a Data Pump export as the import of such stats takes way longer than transporting them via a stats table. If you are unfamiliar with transporting stats between databases please see the Oracle Performance Tuning Guide with a nice tutorial:

Importing and Exporting Optimizer Statistics

The basic steps to transport statistics from one database to another fast and

Some Data Pump issues: DBMS_DATAPUMP Import via NETWORK_LINK fails + STATUS parameter giving bad performance

Posted on June 30, 2015 by Mike.Dietrich Data Pump 8

One of my dear Oracle ACS colleagues (Danke Thomas!) highlighted this issue to me as one of his lead customers hit this pitfall a week ago. .

DBMS_DATAPUMP Import Over NETWORK_LINK fails with ORA-39126 / ORA-31600

Symptoms are:

KUPW$WORKER.CONFIGURE_METADATA_UNLOAD [ESTIMATE_PHASE]
ORA-31600: invalid input value IN ('VIEWS_AS_TABLES/TABLE_DATA') for parameter VALUE in function SET_FILTER

This can be cured with the patch for bug 19501000 – but this patch can conflict with:Bug 18793246 EXPDP slow showing base object lookup during datapump export causes full table scan per object and therefore may require a merge patch – patch 21253883 is the one to …

Parallel Index Creation with Data Pump Import

Posted on April 10, 2015 by Mike.Dietrich Data Pump Patch Recommendation 34

Here is a new capability that might be interesting to anybody who is performing a migration using Data Pump. Previously, Data Pump would create indexes one at a time, specifying the PARALLEL keyword for the CREATE INDEX statement to invoke parallel query for index creation. We used to recommend a workaround to create indexes in parallel, which involved a three-step process of importing without indexes, then creating a SQLFILE of the CREATE INDEX statements, and breaking that file into multiple windows.

Through extensive performance testing we found that it is faster to create multiple indexes in parallel (using a parallel …

Full Transportable Export/Import White Paper

Posted on July 18, 2013 by Mike.Dietrich Oracle Database 12c Transportable 2

One of the really cool and helpful features in Oracle Database 12c is called Full Transportable Export/Import. It combines the basics of transportable tablespaces – of course cross-platform, cross-endianess, cross-version – with Data Pump taking care on all the stuff not stored in tables and indexes, such as views, synonyms, trigger, packages etc. And you can even reduce downtime by combining the feature with incrementally converted RMAN backups.

And the best thing: this will work with an Oracle Database 11.2.0.3/4 to Oracle Database 12c – so you don’t have to be on Oracle Database 12c in order to …

Data Pump: Consistent Export?

Posted on July 3, 2012 by Mike.Dietrich Data Pump 2

Ouch … I have to admit as I did say in several workshops in the past weeks that a data pump export with expdp is per se consistent.

Well … I thought it is … but it’s not. Thanks to a customer who is doing a large unicode migration at the moment. We were discussing parameters in the expdp’s par file. And I did ask my colleagues after doing some research on MOS. And here are the results of my “research”:

MOS Note 377218.1 has a nice example showing a data pump export of a partitioned table with DELETEs on

Exclude DBMS_SCHEDULER Jobs from expdp?

Posted on December 4, 2011 by Mike.Dietrich Data Pump 1

You have never thought about excluding DBMS_SCHEDULER jobs from a Data Pump export? Me neither but I’ve recently got a copy of an email for such a customer case from Roy who owns Data Pump as well. And this is the code example from Dean Gagne:

exclude.par:

exclude=procobj:"IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN
(47,48,66,67,68,69,71,72,74))"

This will work only on export
It’s an all or nothing approach

Quite interesting, isn’t it?…

How to get the Master Table from a Data Pump expdp?

Posted on July 19, 2011 by Mike.Dietrich Data Pump 0

Pump Interesting question a customer had last week during the Upgrade Workshop in Munich. He’s getting export dump files from several customers and often not much information describing the contents. So how can ge find out what’s in there, which was the source characterset etc.

This seems to be a simple question but it did cost me a few searches and tests to come back with some (hopefully) useful information.

First attempt: $strings expdp.dmp > outexpdp.txt

I bet there are better ways to do this but in my case this will give me:

"APP"."SYS_EXPORT_SCHEMA_01"
x86_64/Linux 2.4.xx
WE8ISO8859P15
LBB EMB GHC JWD

Data Pump

Database Migration from non-CDB to PDB – Migration with Data Pump

High Level Overview

Behavior Change in Oracle 18c/19c: No symbolic links for Data Pump directories

UTL_FILE_DIR Desupport

Data Pump: The Time Zone Pitfalls

The Case

OOW 2018: How to Migrate to Oracle Autonomous Database Cloud

What is this talk all about?

Data Pump 12.1.0.2 – Wrong Dump File Version – ORA-39142

When does it happen?

Export with Data Pump and Long Identifiers

Export with Data Pump and Long Identifiers

Upgrade and Migration Office Hours on March 21, 2018

What are “Office Hours”?

Data Pump’s amazingly useful METRICS=Y and LOGTIME=ALL parameters

Data Pump – Exclude Stats differently for TTS and FTEX

Transport Statistics via a Staging Table

Some Data Pump issues: DBMS_DATAPUMP Import via NETWORK_LINK fails + STATUS parameter giving bad performance

DBMS_DATAPUMP Import Over NETWORK_LINK fails with ORA-39126 / ORA-31600

Parallel Index Creation with Data Pump Import

Full Transportable Export/Import White Paper

Data Pump: Consistent Export?

Exclude DBMS_SCHEDULER Jobs from expdp?

How to get the Master Table from a Data Pump expdp?

Archives

Categories

Upgrade @YouTube

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Recent Posts