Migrating data from 11G Linux to 19C on Windows

oracleoracle-19c

I'm using datapump to move data from 11.2.0.4 on Linux to 19C on Windows. It seems to be working except for some objects in the system schemas. Is there any reason to bring these schemas across or can I simply exclude them?

Best Answer

Always exclude them. They contain version and instance-specific metadata that should not be migrated. When performing this kind of migration (porting applications to new DB versions), only ever export/import your application schemas and user accounts. Never do a "full" export/import.

If you have already overwritten some data dictionary objects and experienced errors, you may need to validate the state of your dictionary (see here: https://oracle-base.com/articles/misc/hcheck-sql-check-known-data-dictionary-problems) and consider a restore from backup to a point prior to your import.

Related Solutions

Mysql – Replicating data from Oracle to MySQL

From your question, it doesn't sound like you need true transactional semantics, and that asynchronous replication would be fine.

If that's the case, it's fairly trivial to build a replication system using triggers within Oracle to track data changes, and then replaying those changes into MySQL, using something like perl (or other scripting languages) as the glue code to talk between both systems using SQL.

This might sound like it would be a lot of overhead, but we've built numerous systems like this for our clients, and the overhead from Oracle's standpoint is pretty low; we've run it on some OLTP systems powering top 1000 websites that had hefty transactional workloads, and Oracle handled it like a champ.

Exporting from Oracle enterprise edition to standard edition reaches schemas limit

Apparently a full=y is allowed.
Converting An Enterprise Edition Database To Standard Edition (Doc ID 139642.1)

The only way to properly convert from an Enterprise Edition back to a
Standard Edition is through an Export/Import operation. This way you will
get rid of all Enterprise Edition specific Data Dictionary objects, as the
SYS schema objects are not exported. The Standard Edition EXP utility is
preferred to be used to export the data.

So apparently all the EE DD objects are not transfered. Followed by dropping or excluding while exporting the EE schemas.

Best Answer

Related Solutions

Mysql – Replicating data from Oracle to MySQL

Exporting from Oracle enterprise edition to standard edition reaches schemas limit

Related Question