Since the launch in early August 2018, the EDB Migration Portal has been used to assess and migrate hundreds of Oracle database schemas, and convert them into EDB Postgres Advanced Server compatible databases. We have been improving the Migration Portal regularly since our launch with more functionalities, features, performance improvements, the overall look and feel, and many other aspects, and are now ready to release our Beta 3.
Today’s Beta 3 release (11th Oct 2018) is a major release and some of the key functionalities important from an use case perspective are now available. These include Multi Schema Capability, Support for EDB Postgres Advanced Server version 11 Beta, and new Repair Handlers.
Multi Schema Capability
Until now, our EDB DDL extractor took one schema at a time from the source Oracle database, allowing you to assess only one schema at a time on the Portal. However, we know that even a mid-size application will certainly have several schemas. Therefore, it makes sense to extract and assess them all together instead of one at a time. Also, the dependencies between different objects across different schemas had to be manually resolved.
We do not need to do all this anymore. We have added multi schema capability to the Migration Portal, which allows one to extract multiple schemas from an Oracle database and assess them all at a single click on Migration Portal. All objects within schemas are assessed against a choice of target database (EPAS 10 or EPAS 11 Beta) and you would be able to see individual schema results the way you once saw them. Also, you can now Export the DDL for a complete project all at once, which will include EPAS compatible DDLs for all objects under all schema. Or, if you wish, you can Export an individual schema’s DDL as well.
Support for EDB Postgres Advanced Server version 11 Beta
EDB released its Postgres Advanced Server 11 Beta on August 9. It has many important database features that improve its Oracle compatibility along with a few other distinctive features. The Migration Portal now supports Advanced Server 11 Beta as a target database, allowing you to assess/reassess Oracle schema compatibility against EPAS 11 Beta as well. Important EPAS 11 features include:
- Default partitions
- Foreign key on partitioned tables
- Partition by hash key
- Faster partition pruning
- UPDATE row movement
- Automatic child partition index creation
- Partition-wise aggregate
- Autonomous transactions
- Data redaction
- Performance diagnostics
- Default partition on range-partitioned tables
- Procedure overloading
This means if any of your source Oracle schemas use any of the above features, it would be supported in EPAS 11 as well, and the Migration Portal will take care of it appropriately while assessing.
New Repair Handlers
Your Oracle DDLs are made EPAS compatible with the help of Repair Handlers. We are continuously adding more and more Repair Handlers so that once assessed, you directly get the EPAS compatible DDL. In this release, we added the following Repair Handlers to the Migration Portal, resulting in an increase in the success ratio for schema assessments.
- ERH-2030 : Translates REFERENCING clause in triggers to EDB compatible syntax. (REFERENCING OLD TABLE AS old NEW TABLE AS new)
- ERH-2031 : Transforms BITMAP index to EDB compatible syntax. (CREATE INDEX ENAME_IDX ON BONUS)
- ERH-2032 : Removes unsupported NLS_CALENDAR=GREGORIAN option from the source DDL.
- ERH-2033 : Transforms Oracle’s syntax for adding local indexes on partitioned tables to EDB compatible syntax.
- ERH-2034 : NULL for an attribute while defining type is not allowed in EDB. (CREATE OR REPLACE TYPE addr_type AS OBJECT(city VARCHAR2(20) NULL,state CHAR(2) NULL))
- ERH-2035 : BINARY_FLOAT is not a supported datatype in EDB. It’s replaced with its EPAS equivalent datatype REAL
- ERH-2036 : BINARY_DOUBLE is not a supported datatype in EDB. It’s replaced with its EPAS equivalent datatype DOUBLE PRECISION
- ERH-2037 : EDB cannot support TSWTZ unless we compromise the zones supported in data. We need to transform TIMESTAMP WITH LOCAL TIME ZONE to TIMESTAMP WITH TIME ZONE. LOCAL is understood.
- ERH-2038 : INTERVAL in EDB does not support DAY precision. (INTERVAL DAY TO SECOND (6)).
- ERH-2039 : BFILE is not a supported datatype in EDB. It’s replaced with its EPAS equivalent datatype TEXT.
- ERH-2040 : EDB does not need prefixing TIMESTAMP when one uses the TIMESTAMP values to partition a table.
- ERH-2041 : EDB does not support INCLUDING clause to control which nonkey columns are stored with the key columns of IOT.
- ERH-2042 : INTERVAL in EDB does not support YEAR precision.
Needless to say, we have worked on some other minor issues (not limited to the list below), but definitely important ones like
- Overall app performance
- Progress bar during assessment runs
- Updated guides and “What’s New” section under “Getting started” Menu
If you are currently using our Migration Portal Beta, look for these improvements in our latest release. If you’d like to join our Beta program, click here to get started
Prashant Agarwal is a Sr. Development Manager at EnterpriseDB and manages different tools around Postgres & Postgres Advanced Server. He has overall 13+ yrs of industry experience and leads teams for MTK, Migration Portal & Cloud Database Service (CDS - EDB's upcoming offering).