Known issues, limitations, and notes v4

Suggest edits

This page lists known issues, limitations, and notes for:

Migration Portal

Wrapped objects

Migration Portal can't assess wrapped objects. If you include them in the extracted DDL, they aren't loaded into Migration Portal and aren't included in the count of objects that are assessed. If you want to assess wrapped objects and migrate them to EDB Postgres Advanced Server, include unwrapped versions of the objects in the DDL file that you upload to Migration Portal. The recommended way of doing this is to replace the wrapped versions in the Oracle database with clear-text versions before performing the schema extraction. After performing the schema extraction, you can replace the objects with the wrapped versions.

File encoding

Migration Portal recommends the .SQL output file be in the UTF-8 encoding format. If you upload a .SQL file with non-UTF-8 encoding, all the characters that aren't compatible with UTF-8 are converted to the replacement character ‘�’ in the output DDL.

Tip

You can manually convert the extracted file to the UTF-8 format by using the iconv utility on Linux or the LibIconv utility on Windows. For example, if your database character set is in Latin-1 (ISO-8859-1), you can convert the extracted file to the UTF-8 format, as follows:

iconv -f iso-8859-1 -t UTF-8 sample.sql > sample_utf8.sql

ALTER statements

Except for ALTER PROFILE DEFAULT, ALTER TABLE and ALTER TRIGGER, Migration Portal doesn't process any other ALTER statements in the DDL.

Users, roles, profiles, and grants

Grants

The Migration Portal now supports assessing and migrating the Oracle users for the schemas that have been extracted and uploaded. All the users, roles, and profiles for the schemas associated with a project are grouped together under a GLOBAL_OBJECTS pseudo schema in the Migration Portal views. Currently, grants of system privileges, object privileges, and roles are included with the DDL of the user object. Similarly, grants of system and object privileges to roles are included with the DDL of the role objects.

Since the grants are currently attached to the user and role objects, if one or more objects that the user is being granted a permission on fails, the user object itself fails assessment. To resolve this issue, any grant statements on failed objects can be commented out either using a SQL single line comment (--) for individual statements or a SQL multi-line comment (/* */) for consecutive grant statements and then a reassessment of the object can be performed. After the failed object corresponding to the failed grant statements have been repaired, the previously failing grant statement can be uncommented and the user object reassessed.

Granting the RESOURCE role is equivalent to granting the CREATE and USAGE privileges on a schema that shares the same name as the grantee. If you want to grant the RESOURCE role, you need to first create a schema with the same name as the user or role. Otherwise, a "Schema does not exist" error occurs.

Privileges

To migrate a schema to a target EDB Postgres Advanced Server database, the target database user being used to perform the migration needs the following privileges:

  • SUPERUSER to migrate profiles

  • SUPERUSER or CREATE ROLE to migrate users and roles

Passwords

When migrating users, the Migration Portal does not migrate the users' passwords. The users are created in the target EDB Postgres Advanced Server database without a password. If it is desired or required that the users be configured with a password, this can be done manually in the target database after the users have been migrated.

Profiles

The Migration Portal migrates non-DEFAULT profiles assigned to the schema users. Oracle profiles include both password and resource limits. EDB Postgres Advanced Server only supports the Oracle password related limits. Resource limits are extracted with the profiles, but the Migration Portal only uploads, assesses, and migrates the password limits. Also, the DEFAULT profile may have been overridden in the Oracle database with new limit values. The EDB DDL Extractor extracts ALTER PROFILE statements for DEFAULT profiles that are overridden in the Oracle database, and the Migration Portal attempts to apply these statements in the target EDB Postgres Advanced Server database. If the schema is being migrated to an EDB Postgres Advanced Server instance with existing users and databases, we recommend that the settings of the ALTER PROFILE DEFAULT statement be verified and updated, if necessary, before migrating the DDL to the target database.

Note

Starting with version 4.3.0, the Migration Portal supports assessment of users, roles, profiles, and grants. However for the projects created using an earlier version (earlier than 4.3.0) of the Migration Portal, this capability is not supported. In order to assess the users, roles, profiles, and grants, use the latest version of the EDB DDL Extractor and create a new project.

Using Oracle default case

The option to use the Oracle default case feature preserves Oracle’s default uppercase naming convention for all database objects when migrating a schema from an Oracle database to an EDB Postgres Advanced Server database.

The EDB DDL Extractor and Oracle Data Pump utilities extract all the DDLs in the default case of Oracle (that is, uppercase for object names or the respective case for case-sensitive object names) except for object names referenced inside the body of PL/SQL objects unless they are explicitly double-quoted by the user when creating the DDL.

So, the Migration Portal preserves all double quotes from the source schema file for this feature, and in addition, the Migration Portal applies double quotes to:

  • The column names inside the SELECT statement of views and materialized views.

  • The column names inside check constraints.

  • The table name and column names inside the trigger header.

When the option to use Oracle casing is not selected, the default behavior of the Migration Portal is that all objects are created in lowercase (the default case of EDB Postgres Advanced Server) or mixed case if an object name is specified using a quoted mixed case in the source DDL.

Find the below source and target DDLs using the useOraCase feature:

-- Source DDL
CREATE TABLE "RHS_SANITY"."DEPT"
   (	"DEPTNO" NUMBER(2,0),
	"DNAME" VARCHAR2(14),
	"LOC" VARCHAR2(14),
	 CONSTRAINT "DEPTDEPTNO_PK" PRIMARY KEY ("DEPTNO")
  USING INDEX  ENABLE,
	 CONSTRAINT "DEPTDNAME_UK" UNIQUE ("DNAME")
  USING INDEX  ENABLE
   ) ;

-- Target DDL
CREATE TABLE "RHS_SANITY"."DEPT"
   (	"DEPTNO" NUMBER(2,0),
	"DNAME" VARCHAR2(14),
	"LOC" VARCHAR2(14),
	 CONSTRAINT "DEPTDEPTNO_PK" PRIMARY KEY ("DEPTNO")
  USING INDEX  ENABLE,
	 CONSTRAINT "DEPTDNAME_UK" UNIQUE ("DNAME")
  USING INDEX  ENABLE
   ) ;

While using the Oracle default case, you may experience a lower compatibility ratio compared to other projects. The following are reasons that might contribute to this lower compatibility ratio:

  • The most common reason is that the object/relation doesn't exist. This scenario is caused by the fact that the table/object is in double-quoted upper case to preserve the Oracle case, whereas in the failing object it is referenced without the double-quotes. For example: 

    CREATE TABLE "HR"."EMP"
   (   "EMPNO" NUMBER(4,0),
       "ENAME" VARCHAR2(10),
       "JOB" CLOB,
       "MGR" NUMBER(4,0),
       "HIREDATE" DATE,
       "SAL" NUMBER(7,2),
       "COMM" NUMBER(7,2),
       "DEPTNO" NUMBER(2,0),
     CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX  ENABLE
   ) ;

CREATE OR REPLACE  PROCEDURE "HR"."PROC"
IS
 CURSOR emp_cur IS SELECT hiredate, CAST(hiredate AS timestamp(6)) AS timestamped FROM emp;
 REC emp_cur%rowtype;
BEGIN
   OPEN emp_cur;
   LOOP
   FETCH emp_cur INTO rec;
     EXIT WHEN emp_cur%NOTFOUND ;
	          DBMS_OUTPUT.PUT_LINE(rec.hiredate ||'  '||rec.timestamped);
         END LOOP;
    CLOSE EMP_CUR;
   END ;

    The error displays as follows:

    relation "emp" does not exist

    In the example above, the table name and its column names are specified with quoted uppercase names in the CREATE TABLE statement; however, in the CREATE OR REPLACE PROCEDURE statement, the table and its columns are referenced using using unquoted lowercase identifiers.

  • The variables defined using %type are failing in code objects as preceding table.column-name are in lower case, like employees.employee_id%type whereas the table name and the column names for the actual table are in double-quoted uppercase. For example: 

    CREATE TABLE "HR"."EMPLOYEES"
   (    "EMPLOYEE_ID" NUMBER(6,0),
        "FIRST_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
        "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
        "PHONE_NUMBER" VARCHAR2(20),
        "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
        "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
        "SALARY" NUMBER(8,2),
        "COMMISSION_PCT" NUMBER(2,2),
        "MANAGER_ID" NUMBER(6,0),
        "DEPARTMENT_ID" NUMBER(4,0),
         CONSTRAINT "EMP_SALARY_MIN" CHECK ("SALARY" > 0) ENABLE,
         CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX  ENABLE,
         CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
  USING INDEX  ENABLE
   ) ;
CREATE OR REPLACE  PROCEDURE "HR"."PKG" AS
-- declare variables for data fetched from cursor
  empid       employees.employee_id%TYPE; -- variable for employee_id
  hiredate    employees.hire_date%TYPE;   -- variable for hire_date
  firstname   employees.first_name%TYPE;  -- variable for first_name
  lastname    employees.last_name%TYPE;   -- variable for last_name
  rowcount    NUMBER;
  bonusamount NUMBER;
  yearsworked NUMBER;
-- declare the cursor with a parameter
  CURSOR cursor1 (thismonth NUMBER)IS
    SELECT employee_id, first_name, last_name, hire_date FROM employees
       WHERE EXTRACT(MONTH FROM hire_date) = thismonth;
BEGIN
  null;
END;

    The error displays as follows:

    syntax error at or near "%" (line 3, char 37)

  • When tables have TYPE reference in column data type they fail due to the difference in case. For example:

    CREATE OR REPLACE TYPE "HR"."USER_TYPE" IS OBJECT(
    "ID" NUMBER,
    "NAME" VARCHAR2(256)
);

CREATE TABLE "HR"."MY_TABLE"
(
    "ID" NUMBER,
    "MY_NAME" user_type
);

    The error displays as follows:

    type "user_type" does not exist
LINE 4:     "MY_NAME" user_type
              ^
SQL state: 42704
Character: 58

EDB DDL Extractor

  • The EDB DDL Extractor script doesn't extract objects restored using Flashback that still have names like BIN$b54+4XlEYwPgUAB/AQBWwA==$0. If you want to extract these objects, you must change the names of the objects and rerun the extraction process.
  • The EDB DDL Extractor extracts nologging tables as normal tables. Once these tables are migrated to EDB Postgres Advanced Server, WAL log files are created.
  • The EDB DDL Extractor extracts objects only with VALID status. For any objects that have INVALID status that you want Migration Portal to assess, first update them to VALID.
  • The EDB DDL Extractor doesn't extract objects that were obfuscated using the Oracle wrap feature. As such, these objects aren't included in the set of DDL assessed by the Migration Portal. If you want to assess these objects and migrate them to EDB Postgres Advanced Server, replace the wrapped versions of the objects with nonwrapped versions. See Wrapped objects for more information.
  • The EDB DDL Extractor creates Global Temporary tables to store the schema names and their dependency information. These tables are dropped at the end of successful extraction.
  • The EDB DDL Extractor script doesn't extract schemas whose name starts with PG_ because PostgreSQL doesn't support it. If you want to extract these schemas, you must change the name of the schema before extraction.
  • The EDB DDL Extractor automatically extracts the information for the profiles, roles, and grants.
  • The EDB DDL Extractor currently doesn't support the extraction of ROLES, SYSTEM GRANTS ON ROLES, OBJECT GRANTS ON ROLES, and ROLE GRANTS from Oracle 11g. This behavior results in error messages being written to the extracted files in the sections corresponding to these object types. These errors don't cause any issue in the assessment of these files by Migration Portal.

Oracle Data Pump utilities

  • Migration Portal might fail to parse your SQL file if you create a database link using the IDENTIFIED BY clause with Oracle's quote operator, for example, IDENTIFIED BY VALUES q'[:1]'. To parse your file successfully, try using an actual password, for example, IDENTIFIED BY my_password.

  • The DDL generated by Oracle Data Pump utilities might contain ALTER STATEMENTS such as ALTER FUNCTION, ALTER PACKAGE, and ALTER TYPE, which are not processed by Migration Portal.

  • Profiles and roles aren't extracted when the Oracle Data Pump is executed in schema mode, that is, when the SCHEMAS parameter is used when executing the expdp command. If the schema user being exported has a profile assigned to it, the assessment of the schema user object fails in Migration Portal since the profile isn't part of the exported impdp file. To correct the issue, remove the profile assignment in the user object target DDL, and reassess the user creation object.

  • You might see some errors while trying to extract ROLES, SYSTEM GRANTS ON ROLES, OBJECT GRANTS ON ROLES, and ROLE GRANTS from Oracle 11g using impdp or expdp in schema mode. The workaround is to use the full=y option, which extracts all the schemas and these object types without error.