4.4. Setting up the ProGate Source and Destination #

This section describes the setup that is needed to run ProGate.

4.4.1. Setting up the Source #

4.4.1.1. Oracle Source #

The following setup of the Oracle source is needed:

  • Grant the user under which the migration will run with the following permissions:

    GRANT CONNECT, RESOURCE TO PROGATE_USER;
    
    GRANT EXECUTE ON DBMS_LOGMNR TO PROGATE_USER;
    GRANT EXECUTE ON SYS.DBMS_CRYPTO TO PROGATE_USER;  -- for procheck to compare LOB objects
    
    GRANT EXECUTE_CATALOG_ROLE TO PROGATE_USER;
    GRANT LOGMINING TO PROGATE_USER;
    
    GRANT SELECT ON V_$LOG TO PROGATE_USER;
    GRANT SELECT ON V_$LOGFILE TO PROGATE_USER;
    GRANT SELECT ON V_$LOGMNR_LOGS TO PROGATE_USER;
    GRANT SELECT ON V_$LOGMNR_CONTENTS TO PROGATE_USER;
    GRANT SELECT ON V_$LOGMNR_PARAMETERS TO PROGATE_USER;
    GRANT SELECT ON V_$ARCHIVED_LOG TO PROGATE_USER;
    GRANT SELECT ON V_$TRANSACTION TO PROGATE_USER;
    GRANT SELECT ON V_$DATABASE TO PROGATE_USER;
    

4.4.2. Setting up the Destination #

Note

No specific setup is needed for the Parquet-format file destination.

4.4.2.1. Postgres Pro Destination #

The following setup of the Postgres Pro destination is needed:

  • Install and enable the following extensions, available starting with Postgres Pro 16.4:

    CREATE EXTENSION IF NOT EXISTS pgpro_bfile;
    CREATE EXTENSION IF NOT EXISTS pgpro_sfile;
    CREATE EXTENSION IF NOT EXISTS dbms_lob;
    
    do
    $$
    BEGIN
        PERFORM sf_initialize();
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'already initialized';
    end;
    $$;
    

  • To migrate BFILE objects, create the same aliases as in the source DB:

    do
    $$
        BEGIN
            PERFORM bfile_directory_create('ALIAS1', '/tmp/alias1');
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'alias1 already exists';
        end;
    $$;
    
    do
    $$
        BEGIN
            PERFORM bfile_directory_create('ALIAS2', '/tmp/alias2');
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'alias2 already exists';
        end;
    $$;
    

  • Transfer the schema from the source DB using ora2pgpro.