Migrating Oracle schemas with Migration Portal Innovation Release
- Hybrid Manager dual release strategy
- Documentation for the current Long-term support release
Schema handling for Oracle databases
You can use several different methods to prepare your schema, exclude unsupported constraints, and import it to the destination database. The following are guidelines for how to export and import your schemas for Oracle using Migration Portal.
Other valid routes for migrating DDL to import Postgres schemas include:
- Manually creating the schemas in the destination database
- Performing a pg_dump to obtain the schemas and pg_restore to restore them
- Using pgAdmin or psql
Export
Use the collaborative project-scoped workspace of Migration Portal to convert and migrate schemas. Hybrid Manager (HM) provides an integration that automatically creates a Migration Portal project for your schemas preloaded by the EDB Postgres AI agent.
Prerequisites
You registered an Oracle database with Hybrid Manager.
Optional: The HM administrator enabled and configured the AI Copilot with the AI/LLM of your organization. This configuration helps to resolve schema incompatibilities in Migration Portal.
Migration Portal project creation
If you registered an Oracle database with Hybrid Manager via the EDB Postgres AI agent, a Migration Portal project is created automatically for all the database schemas and dependencies. It creates a project per registered database (per resource_id established in the beacon-agent.yaml). Take into account that any schemas added post-registration aren't reflected in the Migration Portal project.
If the Migration Portal didn't automatically create projects for your registered databases, create projects manually. This can be the case when:
- The size of the ingested DDL exceeds the default limit of 300 MB.
- The size of the ingested DDL exceeds the limit customized by yourself.
- You disabled the automatic creation of Migration Portal projects.
Manual project creation
In the Estate page, select the Migrations tab.
To open the database's detailed view, select the name of the database you want to migrate.
In the detailed view of the database, select the Schemas tab.
Select New MP Project or the plus sign next to one of the schemas you want to migrate.
Tip
The Dependencies column shows the other schemas the current schema relies on. We recommend grouping a schema with all its dependencies in a single Migration Portal project to ensure that all referenced objects exist in the destination database.
In the pop-up window, enter the details for the Migration Portal project that will host the assessment, conversion, and migration of the schemas. Select Continue.
Select all the schemas you want to add to this Migration Portal project. You can select only schemas that the EDB Postgres AI agent has ingested successfully and schemas that aren't already part of a different Migration Portal project.
Note
You won't be able to add schemas to the existing project later. Instead, use a different project to migrate other schemas to the same destination database.
Select Create New Project.
A Migration Portal project is created in the background with the source schemas loaded into it for analysis.
After HM finishes loading the schemas and creating a project in Migration Portal, a link is added with the name of the project. Select the link to open Migration Portal.
The selected schemas were uploaded and assessed by the Migration Portal. Use the AI Copilot or QuickHelp to help you resolve incompatibilities.
Import
Standard schema import procedure
If you plan to keep schema, table, and column names unaltered, use the Online migration option to directly connect to the destination database and migrate schema.
After you resolve all compatibility issues, select Migrate to.
Select the Online migration option (recommended).
Select the schemas you want to migrate, but disable Constraints from the list of objects underneath the database. Select Next.
Note
Ensure you exclude foreign key, check, and exclusion constraints from the SQL-formatted DDL before importing the schema to the destination database.
In the Connect to your cluster page, enter the host name of the database cluster you want to use as a destination.
Note
If you're migrating to an HM-managed database cluster, you can find the read/write host and other connection data by selecting Clusters, the cluser name, and then Connect.
Select Next and wait until the schemas are imported successfully.
Connect to the target database cluster and ensure the schemas were migrated.
Note
You must reapply the excluded foreign key, check, and exclusion constraints on the destination database later in the migration process, after the migration is performed successfully. Therefore, keep track of the excluded constraints. The documentation will guide you on when and how to reapply them.
Schema import procedure with object renaming (optional)
If you plan to map schemas, tables, or columns to different names in the destination, use the Offline migration option. This allows you to download a DDL file to edit.
After you resolve all compatibility issues, select Migrate to.
Select the Offline migration option.
Select the schemas you want to migrate, but disable Constraints from the list of objects underneath the database. Select Next.
Note
Ensure you exclude foreign key, check, and exclusion constraints from the SQL-formatted DDL before importing the schema to the destination database.
Select Download SQL file.
After the SQL script is generated, edit the object names directly in the script (DDL) before applying it to the destination. Ensure that the structure and data types of the renamed object in the script exactly match the source. See Mapping schema, tables and columns during a migration for more information.
If you manually update object names in the DDL scripts, ensure that you also update the names in other objects that reference the updated objects. For example, if you rename a table, also update any views or constraints that reference the original table name.
Connect to the destination database, and apply the modified DDL file.
Note
You must reapply the excluded foreign key, check, and exclusion constraints on the destination database later in the migration process, after the migration is performed successfully. Therefore, keep track of the excluded constraints. The documentation will guide you on when and how to reapply them.