To give you the best possible starting point for the multitenant migration, our team has prepared two webinars about the multitenant architecture. Actually, we planned on just one. Still, we have so many things to share that we decided to make two webinars. When product managers get a chance to talk, they talk a lot!
Move to Oracle Database 23c – Everything you need to know about Oracle Multitenant
You can sign up here. My team (Mike, Rodrigo, Roy and Alex) will be there and answer all your questions. I promise we won’t end the webinar until there are no more questions.
If you miss the webinar, you can watch a recording later on our YouTube channel. Be sure to subscribe so you don’t miss out.
Even if you already use Oracle Database on multitenant architecture, I guarantee there are still new things to learn.
More often than you think, I get mails about migration options from ancientOracle Database releases. Typically, it is from Oracle 8i or 9i. My high score is an inquiry about Oracle 7.2.
My good colleague, Rodrigo, calls it migrations from Jurassic Park data centers.
At one point, our team got so many questions about it that we decided to create a presentation. It premiered at Oracle DatabaseWorld at CloudWorld 2023. We named it:
In Oracle Database 10g, Oracle introduced Data Pump – replacing the original export and import utilities. If possible, use Data Pump.
In older versions, you must use original export, exp.
In recent versions of Oracle Database, Oracle desupported original export, but they still support original import utility, imp, for migrations from really old Oracle Database releases.
Oracle released original export back in Oracle v5. You can export data in Oracle v5 (or newer) and import it directly into an Oracle Database 23c PDB. This is 40 years of backward compatibility. This is rather impressive!
From Oracle 8i Database, you can use same-platform transportable tablespaces. From Oracle Database 10g, you can even do cross-platform transportable tablespaces.
To Which Version Should You Migrate?
If you have such old databases in your environment, I strongly recommend migrating to Oracle Autonomous Database. In Autonomous Database, Oracle will take care of the database for you. Oracle will patch it, maintain it, and upgrade it. You will never end up with a legacy database again.
Note that the original import utility does not meet the security standards of Autonomous Database, so it is impossible to import directly into Autonomous Database. In such a situation, you first import into a staging database that supports Data Pump, and then into Autonomous Database.
Otherwise, I recommend going to the latest long-term support release. Also, you should migrate to the multitenant architecture and import directly into a PDB.
Client/Server Interoperability
Before you migrate the database, you must come up with a plan for your clients and applications. Older database clients can’t connect to modern databases.
With each Oracle Database release, the authentication mechanism changes to become more secure and support newer features. This means that clients must be of a certain version to communicate with the database.
Here’s a video showing you such a migration step-by-step.
The biggest pitfall in such migration is the loss of characters due to incorrect character set conversion. You must set the NLS_LANG environment variable to the character set of the database. Otherwise, you’ll use the default language setting of your operating system and that might lead to character loss.
Only in specific situations do you set the NLS_LANG to something else.
Appendix
Fun Fact
Here’s a list of Oracle Database releases, including their year of release, marque features, and a mobile phone of the same year.
Oracle Data Pump supports moving Advanced Queues (AQ) using export/import and full transportable export/import. But there are some things to be aware of.
Data Pump Does Not Start Queues
Data Pump creates the queues during import but does not start the queues. At the end of the import, you must manually start the queues:
exec dbms_aqadm.start_queue(queue_name => ... );
Be sure to start all queue:
select owner, name, queue_table, enqueue_enabled, dequeue_enabled
from dba_queues;
This behavior is intentional. During a migration, you typically don’t want to use the queues. Depending on your use case, it can have unintended side effects. After the migration, when you confirm a successful migration and are ready to go live, you can manually start the queues.
If you forget to start the queues, your application will start to receive the following error:
ORA-25207: enqueue failed, queue string.string is disabled from enqueueing
ORA-25226: dequeue failed, queue string.string is not enabled for dequeue
The Database Does Not Create All Queue Objects
Data Pump creates the queues using the Advanced Queuing administration API DBMS_AQADM:
exec dbms_aqadm.create_queue_table( ...
The database then creates the essential parts of the underlying queue infrastructure: tables, views, IOTs, etc. This should include:
<queue_table_name>
AQ$_<queue_table_name>_E
AQ$_<queue_table_name>_I
AQ$_<queue_table_name>_T
AQ$_<queue_table_name>_F
In the source database, depending on your configuration and use of Advanced Queueing, you might also see the following objects:
AQ$_<queue_table_name>_C
AQ$_<queue_table_name>_D
AQ$_<queue_table_name>_G
AQ$_<queue_table_name>_H
AQ$_<queue_table_name>_L
AQ$_<queue_table_name>_P
AQ$_<queue_table_name>_S
AQ$_<queue_table_name>_V
This is intentional. The database creates those objects when needed. You should never create them or otherwise touch the underlying queue objects.
Typically, as part of a migration, you compare the count of objects in the source and target database to ensure nothing is lost. This comparison must consider the above.
For queue tables, it is sufficient to compare using the following query:
SQL> select count(*)
from dba_objects
where owner='<schema>'
and object_type = 'QUEUE';
Definition Of Queue Tables Might Change
As stated, you should not worry about the underlying queue objects. However, if you compare the underlying queue objects, you might see that the definition of the object changes. I describe this situation in a different blog post.
Migrate Old Queues
If the queues are REALLY old, the queue compatibility setting might also be old. If so, I strongly recommend migrating the old queues to the newest version. You can learn more about that in this blog post.
When migrating Oracle Databases, you often transport the statistics using dbms_stats. Our team got involved in an interesting case the other day, and I would like to share the solution with you.
Problem
A customer migrated to Oracle Database 19c and decided to move the statistics using dbms_stats.import_schema_stats. They noticed that the procedure started to burn a lot of CPU, and after 38 hours, they gave up.
A SQL Monitor report showed one statement which spent almost all the time. An insert statement into sys.dbms_stats_id_map_tab with a subselect referencing the staging table. The staging table is the one you specify in the argument stattab in the call to dbms_stats.import_schema_stats. The staging holds the optimizer statistics in a portable format. From the staging table, the procedure can insert it into the data dictionary in the proper format.
Obviously, they could see already that the procedure would use far too much time.
Diagnosing
The first shot was to look at the code behind dbms_stats. But this specific code hadn’t changed since its introduction many releases ago.
Recursive statements that touch the data dictionary immediately brought our attention to dictionary statistics. But the customer told us that they were not stale.
The customer ran SQL Tuning Advisor on the offending statement, and one of the findings was about stale statistics. SQL Tuning Advisor recommended gathering statistics on the staging table and corresponding indexes.
One of our performance experts looked at the execution plan and found a pattern he had seen before. He tried to disable Join Predicate Push Down in the session. It helped, but this was just a workaround. We wanted to find the root cause.
Solution
The SQL Tuning advisor came up with the real problem. Stale statistics on the staging table and corresponding indexes. Once the customer gathered statistics on the staging table and indexes, the import of statistics finished in 2 hours and 27 minutes which was acceptable.
We also discovered that the dictionary statistics were not as accurate as the customer had initially concluded. In fact, by mistake, they had misinformed us. A fresh run of dbms_stats.gather_dictionary_stats gave a slight performance improvement as well.
Recommendations
These recommendations are now part of our best practices for migrations.
Before importing statistics using dbms_stats.import_schema_stats, gather stats on the staging table.
Immediately after importing a lot of data, and before you import statistics with dbms_stats or anything else, you should gather dictionary statistics. This applies to regular Data Pump imports and transportable tablespace metadata imports.
In the above example, the staging table is called SYSTEM.MY_STG_TAB_1.
The recommendation applies as well if you are using the procedures dbms_stats.import_database_stats or dbms_stats.import_table_stats.
Dictionary Statistics
Why is it important to gather dictionary statistics immediately after the import?
When you import data or use transportable tablespaces, you often have a brand new, empty database. Then you import a lot of objects. Those objects are represented as rows in the data dictionary.
For instance, the tables you import now appear as rows in SYS.TAB$, the partitions in SYS.TABPART$, the indexes in SYS.IND$, and so forth.
Those internal tables were almost empty before – there were not a lot of tables. Now they have a lot of rows. This means that the statistics are stale. When you start to use functionality in the database, like importing statistics, recursive queries using the internal tables will be executed. With stale statistics on the dictionary, you can have suboptimal execution plans and bad performance. Gathering dictionary statistics can fix this for you.
Conclusion
Statistics are always vital, whether optimizer statistics on user data or internally in the data dictionary. Be sure to verify the accuracy of your statistics when you have problems.
Also, SQL Tuning Advisor is a great tool. It can quickly come up with suggestions for fixing problems. Use the recommendations as input to your troubleshooting. SQL Tuning Advisor also works on internal tables.
Additional Information
We have a few videos on our YouTube channel which have more information about transporting statistics with dbms_stats.
When migrating Oracle Database to a different endian format using transportable tablespaces and incremental backups (XTTS), a list of requirements must be met. The following list of requirements exist when using:
The most important requirements – for a complete list check MOS note:
Windows is not supported.
RMAN on the source system must not have DEVICE TYPE DISK configured to COMPRESSED.
RMAN on the source system must not have default channel configured to type SBT.
For Linux: Minimum version for source and destination is 11.2.0.3.
Other platforms: Minimum version for source and destination is 12.1.0.2.
Disk space for a complete backup of the database on both source and target host. If your data files take up 100 TB, you need an additional 100 TB of free disk space. For 12c databases, and if your data files have a lot of free space, the backup might be smaller due to RMAN unused block compression.
Also worth mentioning is that the Perl script during the roll forward phase (applying level 1 incremental) will need to restart the target database. Applying the incremental backups on the target data files happens in NOMOUNT mode. Be sure nothing else uses the target database while you roll forward.
Block Change Tracking (BCT) is strongly recommended on the source database. Note, that this is an Enterprise Edition feature (in OCI: DBCS EE-EP or ExaCS). If you don’t enable BCT the incremental backups will be much slower because RMAN has to scan every single data block for changes. With BCT the database keeps track of changes in a special file. When RMAN backs up the database, it will just get a list of data blocks to include from the change tracking file.
What If – V3 Perl Script
If disk space is a problem or if you can’t meet any of the other requirements, check out the below two MOS notes:
They describe a previous version of the Perl script, version 3. The scripts use DBMS_FILE_TRANSFER to perform the conversion of the data files in-flight. That way no extra disk space is needed. However, DBMS_FILE_TRANSFER has a limitation that data files can’t be bigger than 2 TB.
Also, the V3 scripts might be useful for very old databases.
Transportable Tablespaces In General
To get a complete list of limitations on transporting data, you should look in the documentation. The most notable are:
Source and target database must have compatible character sets. If the character sets in both databases are not the same, check documentation for details.
No columns can be encrypted with TDE Column Encryption. The only option is to remove the encryption before migration and re-encrypt afterward.
TDE Tablespace Encryption is supported for same-endian migration if the source database is 12.1.0.2 or newer. If you need to go across endianness, you must decrypt the tablespaces and re-encrypt after migration. Remember, Oracle Database 12.2 can encrypt tablespaces online.
If you are migrating across endianness, you must convert the data files. You must have disk space to hold a copy of all the data files. In addition, you should perform the conversion on the platform that has the best I/O system and most CPUs. Typically, this is the cloud platform, which also offers scaling possibilities.
Requires Enterprise Edition.
The database timezone file version in the target database must be equal to or higher than the source database.
The database time zone must match if you have tables with TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ). If you have such tables, and the database time zone does not match, those tables are skipped during import. You can then move the affected tables using a normal Data Pump table mode export and import.
To check the database time zone:
SQL> select dbtimezone from dual;
You can alter the time zone for a database with an ALTER DATABASE statement.
Full Transportable Export/Import
FTEX automates the process of importing the metadata. It is simpler to use and automatically includes all the metadata in your database. Compared to a traditional transportable tablespace import, FTEX is a lot easier and removes a lot of manual work from the end user. But there are a few requirements that must be met:
Source database must be 11.2.0.3 or higher.
Target database must be 12.1.0.1 or higher.
Requires Enterprise Edition.
COMPATIBLE must be set to 12.0.0 or higher in both source and target database. If your source database is an Oracle Database 11g, this is not possible. In that case, set version to 12 or higher during Data Pump export instead.
If you can’t meet the requirements, check out traditional transportable tablespace. It have different requirements, and it allows more customization.
When you use ASM, there is tight control over the file names. ASM strictly enforces the naming standard dictated by Oracle Managed Files (OMF), and only the database can create file names that comply with OMF.
Sometimes it is handy to create files in other locations in ASM that still refer to a database file. Here you can use aliases. Aliases work like a symbolic link in the file system.
Alias Oracle ASM file names are distinguished from fully qualified file names or numeric file names because they do not end in a dotted pair of numbers. It is an error to attempt to create an alias that ends in a dotted pair of numbers, such as in the format USERS.259.685366091.
When you use ls -l you can also tell whether a file is an alias. The column SYS (System-generated) is N, meaning this is not a proper OMF file. Also, you can see in the Name column that it is an alias. The => indicate it:
ASMCMD> ls -l +DATA
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE MAR 16 08:00:00 N account_25.dbf => +DATA/CDB1_FRA2KR/86D5DC2587337002E0532AB2A8C0A57C/DATAFILE/ACCOUNT.282.1099469855
DATAFILE UNPROT COARSE MAR 16 08:00:00 N accountidx_26.dbf => +DATA/CDB1_FRA2KR/86D5DC2587337002E0532AB2A8C0A57C/DATAFILE/ACCOUNTIDX.280.1099469855
You can read about Fully Qualified File Name Form in the ASM documentation, if you are interested.
Why Are the Aliases Created?
When the Perl script is restoring and recovering the data files on the target database, they do not belong to any database yet. The tablespaces have not been plugged into any database yet. Hence, it is impossible to figure out the right OMF name of the data files.
As an alternative, ASM names the data files according to the syntax of the source database. For instance, it will use the source database GUID (select guid from v$containers) as part of the name.
In addition, the Perl script creates ASM aliases using the following format: <dest_datafile_location>/<tablespace_name>_<file#>.dbf
When you perform the Data Pump import, you can refer to the aliases in your Data Pump parameter file (transport_datafile). Using the aliases is especially useful if you plan on having a standby database.
How Do I Get Rid of the Aliases?
After performing the Data Pump import, the tablespaces are plugged into a database, and now the data files belong to a database. But the target database is referring to the data files either via:
An ASM alias
Or directly via the absolute file name. As described earlier, the absolute file path uses the OMF syntax of the source database
Let me illustrate that. Imagine:
In xtt.propertiesdest_datafile_location=+DATA.
My data file is named users01.dbf, belongs to tablespace USERS and has file ID 65.
Target DB_UNIQUE_NAME is SALES2.
Source database GUID is 86D5DC2587337002E0532AB2A8C0A57C.
How will the file be registered in the database?
If I used the aliases, it is known as +DATA/users_65.dbf.
If I used the absolute file name, it is known as +DATA/SALES2/86D5DC2587337002E0532AB2A8C0A57C/DATAFILE/users.280.1099469855. ASM generates the last two sets of numbers.
Neither of the two formats is proper OMF names. What is the real OMF name? Imagine:
Target database GUID is DA495482D68D0220E0530F01000A98DF
The real OMF file name is (notice the change in GUID):
+DATA/SALES2/DA495482D68D0220E0530F01000A98DF/DATAFILE/users.280.1099469855
You can get the GUID of a database by using select guid from v$containers.
In ASM, only the database can store a file in OMF syntax. You must fix this from the target database. The easiest way is to use online data file move. If you don’t specify the target location, the database will generate an OMF name:
SQL> --using file number
SQL> alter database move datafile 65;
SQL> --using full name
SQL> alter database move datafile '+DATA/users_65.dbf';
How does the move work?
It is a entirely online operation.
It is a block-by-block copy.
The database copies the data file. While the copy operation takes place, the two files are kept in sync until the database can switch to the new file. After that, the database removes the original file.
If the data file belongs to a PDB, you must switch your session to that container.
You can learn more about online data file move in our YouTube video:
Why Bother?
If my database works fine, why should I worry? I can think of at least two reasons:
Comply to naming standard
Avoid problems in other migrations
Comply to naming standard
I highly recommend that you use and comply with any naming standard, including OMF. Data files that are not appropriately stored according to OMF, should be moved to the correct location.
When I worked outside in the real world as a DBA, I remember multiple occasions of loss of data files. In many situations, a DBA had found a file apparently not belonging to a database – at least according to the naming standard. But the file was used by a database; it was just not stored in the correct location. When the file was removed = big problem in the database.
With many databases and many hosts, it is very important that you make standards and keep with the standards. Otherwise, you will end up in a big mess.
Avoid problems in other migrations
This is especially relevant if you need to perform multiple migrations to the same database host.
The Perl script used for the migration will create the aliases in the location specified by dest_datafile_location. The names of the aliases are very simple, and there is a risk that another migration will try to make the same alias.
Imagine you already did one migration. The database uses the alias +DATA/users_4.dbf. Now you want to make a second migration, and this database also wants to use +DATA/users_4.dbf. The same alias can’t be used for two different files. Big problem!
A user left a comment on my blog telling me this actually lead to corruption in the first database. That risk is a very good reason for getting rid of the aliases and using only proper OMF file names.
Conclusion
ASM aliases are created automatically as part of the migration. The aliases are very useful during the migration, but I highly recommend getting rid of the aliases right after the migration.
When doing a cross-platform migration with transportable tablespaces and incremental backup, is it possible to perform backups on a standby database? If so, you could offload the work from a primary database.
The short answer is yes. You can use a physical standby database but not a snapshot standby database.
Using a standby database for the backups is an advantage in some situations:
The primary database does not have the capacity to perform the extra backups (CPU, disk space, or I/O).
You want the primary database to be completely unaffected by the migration.
Perform the Data Pump export on the primary database:
[oracle@src-pri]$ expdp system ... full=y transportable=always ...
Active Data Guard
If you have a license for Active Data Guard, you can simply keep the physical standby database in OPEN WITH REDO APPLY mode. You don’t need to switch from OPEN mode to MOUNT mode.
Conclusion
Being able to perform the backups on a standby database is a huge advantage. But it makes the process slightly more complicated, so I would recommend it only if really needed. In addition, if you don’t follow the procedure strictly, you might accidentally use a feature that requires Active Data Guard.
When you migrate an Oracle Database using cross-platform transportable tablespaces (XTTS) and incremental backups, you will need to transfer a large amount of data to the target host. In some situations, especially if the target host is in a remote data center or the cloud, you are restricted by the network throughput, and it can be a limiting factor.
One approach to speed up the data transfer is to compress it before it leaves the source host. Compressing and decompressing takes time and CPU. But if your network connection is slow enough, it may pay off in the end.
First, you start the initial level 0 backup of the source database:
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
The backups will be stored in the directory defined by src_scratch_location. In my case, the directory is /u01/app/oracle/xtts_src_scratch. Now you can simple compress the entire directory:
gzip -r /u01/app/oracle/xtts_src_scratch
This should significantly reduce the size of the files. Transfer the compressed files to your target host, and put them into the directory defined by dest_scratch_location. Then, uncompress the files:
gunzip -r /u01/app/oracle/xtts_dest_scratch
Continue the procedure described in the above MOS note and start the restore:
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore
Some Figures
I made a little test to give you an idea of how much there is to save.
Size
Data files total size
197 GB
Backup set size
197 GB
After gzip compression
12 GB
Because I don’t have any free space in my data files, the backup sets have almost the same size as the actual data files – 197 GB. By gzipping the files I could reduce the file size to 12 GB.
So there is a significant amount to save – from 197 GB to 12 GB. It makes a huge difference whether you have to transfer 197 or 12 GB. But you must consider the time it takes to compress and decompress.
Elapsed time
Compression
13 min 52 sec
Decompression
8 min 7 sec
I made the same test but with image file backups and the numbers were almost the same. Generally, you should always try to use backup sets over image file backups. Backup sets use unused block compression which will skip all the unused blocks in your data files. That can make quite a difference for database with a lot of free space.
What About RMAN Compression
RMAN does have the option of compressing the backup set, but that is currently not supported by the Perl scripts. If the Perl scripts would be able to do that, you could get a good compression ratio with RMAN compression as well (of course provided you have a license for Advanced Compression Option) and avoid the gzip trick. Using the example above, by using RMAN compressed backup sets (medium compression) the backup files would be 0,3 GB which is very good as well.
But for now, you are "stuck" with the gzip trick.
Final Words
A few things to consider:
You should only consider this approach if you have a slow network.
You will need extra disk space during the compression/decompression operation.
You should only compress the initial, level 0 backup. The subsequent level 1 incremental backups will be much smaller in size, and it is unlikely to pay off.
You should test on your own database and hardware to know the actual compression ratio. Add the network throughput to the equation, and you can do the math to see if it is a good idea for your specific project.
To get all the details, visit the Upgrade and Utilities part of the new features documentation. There are some good examples of how the features can be used.
I like ROOH, but it takes some time to get used to. For instance, network/admin files (tnsnames, sqlnet) and dbs files (pfile, spfile) are now in a new location.
Remember, 21c is an innovation release, which means a shorter support window than Long Term Releases such as Oracle Database 19c. If you adopt Innovation Releases, you should be prepared to upgrade to the next database release within one year after the next database release ships.
I would not recommend that you upgrade your production systems to Oracle Database 21c due to the limited support period. Not unless you are prepared to upgrade the database soon again – when support runs out. Oracle Database 19c is the current Long Term Support release. I recommend that for production databases.
To learn more about innovation release and our release model, have a look at our slide deck. We discuss it in the first chapter.
New Features
I want to mention a few new features. They haven’t attracted as much attention as the marque features, but they are still cool.
Expression based init.ora parameters make it possible to base database parameters (init.ora) on calculations made on the system’s configuration. For example, setting the database parameter CPU_COUNT to half the number of CPUs (Windows):
alter system set cpu_count='$NUMBER_OF_PROCESSORS/2';
Placeholders in SQL DDL Statements can improve application security because sensitive information, like passwords, doesn’t need to be hardcoded in SQL DDL. Example: You can make this statement:
CREATE USER :!username IDENTIFIED BY :!password ...
And Oracle Call Interface programs can substitute the placeholders into:
CREATE USER "DANIEL" IDENTIFIED BY "MyS3cr3tP!d" ...
This is similar to data binding but occurs in Oracle Client.
If you decided to do a logical migration of your Oracle Database with Zero Downtime Migration (ZDM), here is something important about optimizer statistics. You must manually take care of the optimizer statistics on the target database after the Data Pump import. Either by recreating the statistics or transporting the statistics from the source database.
Background
Whenever I talk about migration of Oracle Database with Data Pump, I always mention that it is best practice to exclude optimizer statistics from the Data Pump export. Here is how you do it:
$ expdp ... exclude=statistics
Why is that recommended? Data Pump is not very good at extracting the optimizer statistics. There is nothing wrong with the statistics when they are imported. But it can take a very long time to do the export of statistics.
ZDM and Data Pump
The development team behind ZDM wanted the tool to be easy to use. Also, they wanted ZDM to use all the best practices that come with the various other tools that ZDM uses. When they talked to us about Data Pump, we told them to exclude statistics.
BUT – this also leaves you in a situation where you have a database completely without optimizer statistics. It goes without saying that it is a disaster waiting to happen. You must ensure that optimizer statistics are present on the target database before allowing the users to connect to it.
Regather
One option is to regather optimizer statistics on the target database after the Data Pump import. When the Data Pump import completes, and before you proceed with the switchover, it is time to regather the statistics. Typically, you would start ZDM something like this:
It will start by performing the initial load of the database with Data Pump. Then it will configure GoldenGate before it pauses – waiting for your signal to complete the migration. At this time, use DBMS_STATS to gather statistics:
SQL> exec dbms_stats.gather_database_stats;
This has some drawbacks:
It requires time and resources – which might not be the biggest problem. The source database is still open for business. We haven’t performed the switchover yet.
Column usage information (table COL_USAGE$) is not populated, so in some cases, no histograms will be created. This will happen if the database is supposed to automatically determine whether histograms are needed (method_opt includes size auto). In that case, you can merge the column usage information from another database via a database link, which can be a good idea if your application is depending on histograms.
The table, schema, or database statistics preferences are not present. It could be degree, method_opt, stale_pct or any other preferences that you can set with DBMS_STATS.SET_TABLE_PREFS (or schema or database-wide preferences). These preferences can be transferred to the new system, which is what I will talk about next.
The optimizer statistics in the source database are extracted from the data dictionary and stored in a transportable format in a regular heap table (referred to as a staging table).
Using Data Pump, you move that table to the target database.
Then you put the statistics into the data dictionary of the target database so that optimizer can use them.
Pro tip: You should perform step #1 before you start ZDM and store the staging table in one of the schemas that you are migrating with ZDM. That way, you don’t have to move the table manually. It is moved by ZDM together with the real data.
One thing to be aware of is that the table, schema or database statistics preferences are not transferred when you use e.g. DBMS_STATS.EXPORT_TABLE_STATS. There are dedicated procedures for transferring the statistics preferences:
You must figure out how to move the optimizer statistics into your target database when you use ZDM to perform logical migrations. If not, your target database will be without optimizer statistics which is a disaster waiting to happen.
Appendix
You might now ask. If Data Pump is bad at exporting statistics, but there are already better ways available in the database, why don’t we change the Data Pump code? And you are right – but so far, other things have been prioritized. I would love to see this one day fully embedded in Data Pump.
Databases Are Fun 