This article was originally published on December 11, 2020
WHAT’S NEW
This is to update you about the release of Migration Portal 3.0.0, the latest version of the web-based self-service tool for assessing and migrating Oracle database schemas to EDB Postgres Advanced Server. You can quickly and conveniently evaluate the feasibility of migrating one or multiple database schemas from Oracle to Postgres, minimizing the time and risk typically involved with database migrations.
Highlights of this release:
- The Migration Portal now supports:
- Assessment for EDB Postgres Advanced Server version 13, which has additional Oracle compatibility improvements and new Postgres features.
- Added new cloud platforms:
In addition to IBM Cloud, you can now migrate your Oracle schemas to EDB Postgres Advanced Server installed on the following cloud platforms:
- Amazon AWS
- Google cloud
- Microsoft Azure
- Offline Assessment of your schemas
- We added a contact form so you can reach out to us about migration services if you need an offline assessment of your schemas.
- DDL extractor privileges
- With this release, the DDL extractor script requires fewer privileges. It now only requires CONNECT and SELECT_CATALOG_ROLE role and CREATE TABLE privilege.
Repair Handlers
- Repair Handlers
The following repair handlers are added to improve the compatibility ratio. Repair handlers convert Oracle syntax to Postgres-compatible syntax so that you don’t have to do it manually.
- ERH 1013 - PACKAGE_BODY_PROC/FUNC_DECLARATION
- Removes PROCEDURE/FUNCTION declaration from the PACKAGE BODY definition.
This repair handler works for target EDB Postgres Advanced Server v10, v11, and v12. The function/procedure declaration inside Package Body is supported for EDB Postgres Advanced Server v13 or later.
For example,
CREATE OR REPLACE PACKAGE BODY package_name
IS
PROCEDURE sample_proc;
var1 number:=0;
FUNCTION Func_name RETURN number;
PROCEDURE proc_name
IS
BEGIN
NULL;
END;
PROCEDURE sample_proc
IS
BEGIN
NULL;
END;
FUNCTION Func_name RETURN number
IS
BEGIN
return 1;
END;
END;
would become;
CREATE OR REPLACE PACKAGE BODY package_name
IS
var1 number:=0;
PROCEDURE proc_name
IS
BEGIN
NULL;
END;
PROCEDURE sample_proc
IS
BEGIN
NULL;
END;
FUNCTION Func_name RETURN number
IS
BEGIN
return 1;
END;
END
- ERH 2091- CREATE_TABLE_CREATE_INDEX
Removes schema-name from the name of the INDEX in CREATE INDEX definition.
For example,
CREATE TABLE "HR"."DEPARTMENTS"
( "DEPARTMENT_ID" NUMBER(4,0),
"DEPARTMENT_NAME" VARCHAR2(30) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,
"MANAGER_ID" NUMBER(6,0),
"LOCATION_ID" NUMBER(4,0)
) ;
CREATE UNIQUE INDEX "HR"."DEPT_ID_PK" ON "HR"."DEPARTMENTS" ("DEPARTMENT_ID");
would become;
CREATE TABLE "HR"."DEPARTMENTS"
( "DEPARTMENT_ID" NUMBER(4,0),
"DEPARTMENT_NAME" VARCHAR2(30) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,
"MANAGER_ID" NUMBER(6,0),
"LOCATION_ID" NUMBER(4,0)
) ;
CREATE UNIQUE INDEX "DEPT_ID_PK" ON "HR"."DEPARTMENTS" ("DEPARTMENT_ID");
- ERH - 2092 ALTER_TABLE_ADD_PRIMARY_KEY
Transforms Oracle's ALTER TABLE ADD CONSTRAINT for PRIMARY KEY syntax to Advanced Server compatible syntax.
For example,
CREATE TABLE "HR"."DEPARTMENTS"
( "DEPARTMENT_ID" NUMBER(4,0),
"DEPARTMENT_NAME" VARCHAR2(30) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,
"MANAGER_ID" NUMBER(6,0),
"LOCATION_ID" NUMBER(4,0)
) ;
CREATE UNIQUE INDEX "HR"."DEPT_ID_PK" ON "HR"."DEPARTMENTS" ("DEPARTMENT_ID");
ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID")
USING INDEX "HR"."DEPT_ID_PK" ENABLE;
would become;
CREATE TABLE "HR"."DEPARTMENTS"
( "DEPARTMENT_ID" NUMBER(4,0),
"DEPARTMENT_NAME" VARCHAR2(30) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,
"MANAGER_ID" NUMBER(6,0),
"LOCATION_ID" NUMBER(4,0)
) ;
CREATE UNIQUE INDEX "DEPT_ID_PK" ON "HR"."DEPARTMENTS" ("DEPARTMENT_ID");
ALTER TABLE "HR"."DEPARTMENTS" ADD PRIMARY KEY
USING INDEX "DEPT_ID_PK";
Fixed Customer Defects/Enhancements
- Package Body migration issue - A new repair handler ERH-2003 has been added, which removes the function/procedure declaration from the Package Body, as PostgreSQL does not support it. This repair handler works for EDB Postgres Advanced Server v10, v11, and v12.
The function/procedure declaration inside Package Body is supported for EDB Postgres Advanced Server v13 or later.
[MIG - 2003, Support Ticket 1002073]
- Schema names starting with “pg_” are not extracted from Oracle. [MIG - 2243, Support Ticket 1129019]
TELL ME MORE
For more details, please review the Migration Portal documentation:
TROUBLESHOOTING
If you experience problems using the new software, please contact Technical Support at:
Email: support@enterprisedb.com
Phone: US: +1-732-331-1320 or 1-800-235-5891
UK: +44-2033719820
Brazil: +55-2139581371
India: +91-20-66449612