Source database workload and activity

Source database configuration

• Apply recommended/required Oracle GoldenGate and RDBMS patches. See Oracle GoldenGate -- Oracle RDBMS Server Recommended Patches (Doc ID 1557031.1) and Latest GoldenGate/Database (OGG/RDBMS) Patch recommendations (Doc ID 2193391.1) for details.

• Enable ARCHIVELOG mode

• Enable FORCE LOGGING

• Enable database minimal supplemental logging

• Set STREAMS_POOL_SIZE to at least 2GB

  See Source Database Prerequisites for Logical Migration for details.

Source tables uniqueness

• Oracle GoldenGate requires a unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

  This is usually considered with primary key or unique key indexes. If there are tables identified that do not have any such keys, GoldenGate must construct a pseudo key that contains all of the allowable columns in the table, excluding virtual columns, UDTs, function-based columns, extended (32K) VARCHAR2/NVARCHAR2 columns, and any columns that are explicitly excluded from the Oracle GoldenGate configuration by an Oracle GoldenGate user.

  However, constructing a key from all of the columns will significantly impede the performance of Oracle GoldenGate.

  If the source database is version Oracle Database 12g (12.2) or later, use the data dictionary view DBA_GOLDENGATE_NOT_UNIQUE to identify all of the tables that do not have a primary key or non-null unique columns.

• See Ensuring Row Uniqueness in Source and Target Tables for details.

MAX_STRING_SIZE

• If source database is configured with MAX_STRING_SIZE=STANDARD, then set MAX_STRING_SIZE=STANDARD for the target database.
• See MAX_STRING_SIZE for details.

Source database server CPU and memory requirements

If there are enough FREE CPU resources on the source system, set ZDM configuration parameter

GOLDENGATESETTINGS_EXTRACT_PERFORMANCEPROFILE = HIGH. Else, set ZDM configuration parameter GOLDENGATESETTINGS_EXTRACT_PERFORMANCEPROFILE = LOW-RES

GOLDENGATESETTINGS_EXTRACT_PERFORMANCEPROFILE = HIGH

GOLDENGATESETTINGS_EXTRACT_PERFORMANCEPROFILE = LOW

Resource usage on source system:

Target database server CPU and memory requirements

Use ZDM parameter GOLDENGATESETTINGS_REPLICAT_PERFORMANCEPROFILE to tune Oracle GoldenGate Replicat. Setting performance profile automatically configures relevant parameters to achieve the desired throughput and latency. ZDM defaults to HIGH.

GOLDENGATESETTINGS_REPLICAT_PERFORMANCEPROFILE = HIGH

Replicat Appliers = 2 * CPU_COUNT of target database

GOLDENGATESETTINGS_REPLICAT_PERFORMANCEPROFILE = LOW

Replicat Appliers = CPU_COUNT of target database / 2

Oracle GoldenGate server CPU requirements

OCPUs for GoldenGate server = ceil(((#replicat_appliers1 / 2) + #vCPU_extract2 + #vCPU_OS3) / 2)

¹#replicat_appliers: number of GoldenGate Replicat Appliers configured using ZDM parameter GOLDENGATESETTINGS_REPLICAT_PERFORMANCEPROFILE

²#vCPU_extract: based on ZDM parameter

GOLDENGATESETTINGS_EXTRACT_PERFORMANCEPROFILE

³#vCPU_OS: used for Operating System resources management (set to 2 vCPUs)

Example:

When:

#vCPU_extract = 6

#replicat_appliers = 10

#vCPU_OS = 2

then,

OCPUs for GG server = ceil(6.5) = 7

See Oracle Zero Downtime Migration – Logical Migration Performance Guidelines for details.

Oracle GoldenGate server trail file storage space requirement

The space required depends upon the selected size of the trails, the amount of data being captured for replication, and how long the consumed trails are kept on the disk. The recommended minimum disk allocated for trails is computed as:

((transaction log size * 0.33) * number of log switches per day) * number of days to retain trails

For example, if the transaction logs are 1GB in size and there is an average of 10 log switches per day, it means that Oracle GoldenGate will capture 3.3GB data per day. To be able to retain trails for 7 days, the minimum amount of disk space needed to hold the trails is 23GB. See Other Disk Space Considerations for details.

GGADMIN users in non-ADB source and target databases

Create a GoldenGate administration user, ggadmin, in source database granting all of the permissions listed in the example. If the source database is multitenant (CDB), create the user in the source PDB, as shown in this example.

If the source database is multitenant (CDB), also create user c##ggadmin in CDB$ROOT as shown in this example:

If the target database is not an Autonomous Database, create a GoldenGate administration user, ggadmin, in the target database granting all of the permissions listed in the example. If the target database is multitenant (CDB), create the user in the target PDB, as shown in this example.

Network connectivity requirements

Application switchover

Handling Application Switchover in a Logical Migration

During the migration job it is recommended that your database avoid Data Definition Language (DDL) operations to provide the most optimal environment for fast database replication. When DDL is replicated, Oracle GoldenGate Replicat serializes data to ensure that there are no locking issues between DML and DDL on the same objects.

Complete the following prerequisites on the source database to prepare for a logical migration.

Offline and Online Migrations Require:

• The character set on the source database must be the same as the target database.

• Configure the streams pool with the initialization parameter STREAMS_POOL_SIZE.

  For offline logical migrations, for optimal Data Pump performance, it is recommended that you set STREAMS_POOL_SIZE to a minimum of 256MB-350MB, to have an initial pool allocated, otherwise you might see a significant delay during start up.

  For online logical migrations, set STREAMS_POOL_SIZE to at least 2GB. See https://support.oracle.com/epmos/faces/DocumentDisplay?id=2078459.1 for the recommendation 1GB STREAMS_POOL_SIZE per integrated extract + additional 25 percent.

• System time of the Zero Downtime Migration service host and source database server should be in sync with your Oracle Cloud Infrastructure target.

  If the time on any of these systems varies beyond 6 minutes from the time on OCI, it should be adjusted. You can use ntp time check to synchronize the time if NTP is configured. If NTP is not configured, then it is recommended that you configure it. If configuring NTP is not an option, then you need to correct the time manually to ensure it is in sync with OCI time.

• If you are using a database link, and your target database is on Autonomous Database Shared Infrastructure, you must configure TCPS on the source. Autonomous Database Shared Infrastructure doesn't allow a database link to a source that is not configured with TCPS.

• If you are migrating from an Amazon Web Services RDS environment, see Migrating from Amazon Web Services RDS to Oracle Autonomous Database for information about source environment preparations.

• In the PDB being exported, if you have created local objects in the C## user's schema and you want to import them, then either make sure a common user of the same name already exists in the target CDB instance (for non-Autonomous Database targets) or use the following Zero Downtime Migration parameter to rename the schema on import.

  DATAPUMPSETTINGS_METADATAREMAPS-1=type:REMAP_SCHEMA,oldValue:c##common_user,newValue:new_ name

• If you are migrating to Oracle Autonomous Database on Exadata Cloud@Customer from any on-premises Oracle Database, including existing Exadata Cloud@Customer systems, see Migrating to Oracle Autonomous Database on Exadata Cloud@Customer for additional prerequisite setup tasks.

Online Migrations Require:

• If the source is Oracle Database 11.2, apply mandatory 11.2.0.4 RDBMS patches on the source database.

  See My Oracle Support note Oracle GoldenGate -- Oracle RDBMS Server Recommended Patches (Doc ID 1557031.1)

  • Database PSU 11.2.0.4.210720 includes a fix for Oracle GoldenGate performance bug 28849751 - IE PERFORMANCE DEGRADES WHEN NETWORK LATENCY BETWEEN EXTRACT AND CAPTURE IS MORE THAN 8MS

  • OGG RDBMS patch 32248879 MERGE REQUEST ON TOP OF DATABASE PSU 11.2.0.4.201020 FOR BUGS 32048478 20448066 - This patch contains mandatory fix for Oracle GoldenGate Microservices bug 20448066 DBMS_XSTREAM_GG APIS SHOULD BE ALLOWED FOR SCA PROCESSES

• If the source is Oracle Database 12.1.0.2 or a later release, apply mandatory RDBMS patches on the source database.
• If the source is Oracle Database Standard Edition 2 available with Oracle Database 18c or Oracle Database 19c and is lower than DBRU 19.11, apply RDBMS patch for bug 29374604 - Integrated Extract not starting against Oracle RDBMS Standard Edition.

  See My Oracle Support note Latest GoldenGate/Database (OGG/RDBMS) Patch recommendations (Doc ID 2193391.1), which lists the additional RDBMS patches needed on top of the latest DBBP/RU for Oracle Database 12c and later.

  For source Oracle Database 12.1.0.2, along with above mandatory patches, apply patch 20448066.

• Enable ARCHIVELOG mode for the database. See Changing the Database Archiving Mode.

• Enable FORCE LOGGING to ensure that all changes are found in the redo by the Oracle GoldenGate Extract process. See Specifying FORCE LOGGING Mode

• Enable database minimal supplemental logging. See Minimal Supplemental Logging.

  SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

• Enable initialization parameter ENABLE_GOLDENGATE_REPLICATION.

• Install the UTL_SPADV or UTL_RPADV package for Integrated Extract performance analysis.

  Source database version 19c and later, see UTL_SPADV

  Operational notes: To use this package, you must connect to an Oracle database as an Oracle Replication administrator (for example, ggadmin) and run the utlrpadv.sql script in the rdbms/admin directory in ORACLE_HOME.

  Earlier database versions, see UTL_SPADV Operational Notes

  Operational notes: To use this package, you must connect to an Oracle database as an Oracle Replication administrator (for example, ggadmin) and run the utlspadv.sql script in the rdbms/admin directory in ORACLE_HOME.

• During the migration period, to provide the most optimal environment for fast database replication, avoid large batch DML operations. Running large batch operations, like a single transaction that affects multi-millions of rows, can slow down replication rates. Create, alter, and drop DDL operations are not replicated.

• Set WALLET_SOURCEADMIN to specify the path to the directory that contains the auto login wallet file cwallet.sso, which provides the source database administrator password. See WALLET_SOURCEADMIN.
• For adding large number of INCLUDEOBJECTS in Datapump component, create a table in the Source database in the export user schema to list all the TABLE objects to be filtered and specify the following parameters. For example, create a table SYSTEM.INCLUDE_TEMP_LIST and list all objects specified in INCLUDEOBJECTS to be filtered for SCHEMA SCOTT:
  INCLUDEOBJECTS-1=owner:SCOTT DATAPUMPSETTINGS_METADATAFILTERS-1=name:NAME_EXPR,value:’IN(select OBJECT_NAME from SYSTEM.INCLUDE_TEMP_LIST’)’, objectType:TABLE

Offline Migrations Require:

• The DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE roles are required. These roles are required for Data Pump to determine whether privileged application roles should be assigned to the processes comprising the migration job.

  DATAPUMP_EXP_FULL_DATABASE is required for the export operation at the source database for the specified database user. The DATAPUMP_IMP_FULL_DATABASE role is required for the import operation at the specified target database for specified target database user.

  See the Oracle Data Pump documentation for more information.

• Oracle Database Standard Edition 2 does not allow Data Pump dump encryption support so you must encrypt the target database post-migration. To proceed with the migration, set DATAPUMPSETTINGS_DATAPUMPPARAMETERS_ENCRYPTION =NONE. See, DATAPUMPSETTINGS_DATAPUMPPARAMETERS_ENCRYPTION for more information. So, specify NONE if database is Oracle Database Standard Edition 2 and Oracle Advanced Security is not enabled.

Configuring the PROFILE parameter:

Specify the full path to the source database profile file. ZDM maintains a key-value template for each of the supported database PROFILE. See PROFILE

Complete the following additional prerequisites to prepare for a logical migration.

Create an OCI API key pair

See Required Keys and OCIDs for details.

Configure AWS S3 security credentials

If you are migrating from an Amazon Web Services RDS environment, see Migrating from Amazon Web Services RDS to Oracle Autonomous Database for information about source environment preparations.

Set Up Data Transfer Media

• To use Object Storage data transfer medium:

  Create an Object Store bucket on Oracle Cloud Infrastructure if you are using Object Storage as a data transfer medium. This is not required for Oracle Exadata Database Service on Cloud@Customer or on-premises Exadata Database Machine targets.

• To use NFS shared storage:

  Ensure that the NFS is shared between the source and target database server and mapped to the Database Directory object.

  For information about how to mount NFS for dump storage, see Mount Options for Oracle files for RAC databases and Clusterware when used with NFS on NAS devices (Doc ID 359515.1).

• To use a database link (DBLINK):

  If you are using an existing database link between the target database to an on-premises source database by global_name of the source database, ensure that the database link is not broken. Zero Downtime Migration can reuse the pre-existing database link for migration if that data transfer medium is configured.

  Zero Downtime Migration supports the use of a database link for all Autonomous Database targets; however, when you set up the database link for Autonomous Database Dedicated Infrastructure, you must use the Easy Connect syntax or provide a complete descriptor in the USING 'connect string' clause. You cannot use a network service name because the tnsnames.ora file is not available for lookup. Database links can only be used for TCP connections because TCPS connections require a wallet.

  See SQL Commands with Restrictions in Autonomous Database and Create Database Links from Autonomous Database to Oracle Databases

• If you are not using a database link for data transfer, ensure that the file system used for the Data Pump export directory has sufficient space to store Data Pump dump files.

• To use Amazon S3 bucket:

  See Setting Up S3 Bucket Data Transfer Medium

If the source uses self-signed database server certificates:

If the source database listener is configured with TLS (TCPS) using self-signed database server certificates, then ensure that the self-signed certificate is added to the Zero Downtime Migration home cert store as follows.

keytool -import -keystore ZDM_HOME/jdk/jre/lib/security/cacerts -trustcacerts
-alias "src ca cert" -file source_db_server-certificate

Online Migration Additional Prerequisites

For online migration, do the following additional prerequisite tasks:

• Set up an Oracle GoldenGate Microservices hub:

  For Oracle Database Cloud Services targets, deploy the "Oracle GoldenGate for Oracle - Database Migrations" image from Oracle Cloud Marketplace.:

  The "Database Migrations" version of the Oracle GoldenGate Marketplace image provides limited free licensing for use with OCI Database Migration Service. See the license agreement for details.

  Any other use of GoldenGate requires purchasing a license for the Oracle GoldenGate product. See the Oracle GoldenGate documentation for more information.

  1. Log in to Oracle Cloud Marketplace.
  2. Search for the "Oracle GoldenGate for Oracle - Database Migrations" Marketplace listing.
  3. From the Marketplace search results, select the "Oracle GoldenGate for Oracle - Database Migrations" listing.
  4. For instructions to deploy the Marketplace listing, see Deploying Oracle GoldenGate Microservices on Oracle Cloud Marketplace.

  For guidance in configuring Zero Downtime Migration GoldenGate settings and choosing the correct GoldenGate Hub shape, see Oracle MAA technical brief, Oracle Zero Downtime Migration – Logical Migration Performance Guidelines.

  If you are migrating to Exadata Cloud@Customer, or any on-premises Oracle Exadata Database Machine, you must use an on-premises Oracle GoldenGate Microservices instance to create a deployment for the source and target. The “Oracle GoldenGate for Oracle – Database Migrations” marketplace offering now contains a downloadable docker image for migrations to ExaCC. You can review the latest marketplace VM at https://cloudmarketplace.oracle.com/marketplace/en_US/listing/96175416, and the documentation for this functionality can be found at Migrating to Exadata Cloud@Customer Using Oracle Zero Downtime Migration .

  By default, Oracle GoldenGate deployment endpoints are configured using self-signed HTTPS certificate. Either of the following options are available:

  • Specify the GOLDENGATEHUB_ALLOWSELFSIGNEDCERTIFICATE parameter to TRUE in response file to indicate that ZDM can trust the self-signed HTTPS certificate.
  • Update the configuration for Oracle GoldenGate deployment to use a certificate issued by a trusted Certificate Authority.

• Create a GoldenGate administration user:

  Note that the examples shown below are for basic migrations. For comprehensive Oracle GoldenGate permissions information, see Granting the Appropriate User Privileges in Using Oracle GoldenGate with Oracle Database.

  • On the Source Database:

    • Create a GoldenGate administration user, ggadmin, granting all of the permissions listed in the example. If the source database is multitenant (CDB), create the user in the source PDB, as shown in this example.

      SQL> create user ggadmin identified by password
       default tablespace users temporary tablespace temp;
      SQL> grant connect, resource to ggadmin;
      SQL> alter user ggadmin quota 100M ON USERS;
      SQL> grant unlimited tablespace to ggadmin;
      SQL> grant select any dictionary to ggadmin;
      SQL> grant create view to ggadmin;
      SQL> grant execute on dbms_lock to ggadmin;
      SQL> exec dbms_goldengate_auth.GRANT_ADMIN_PRIVILEGE('ggadmin');

    • If the source database is multitenant (CDB), also create user c##ggadmin in CDB$ROOT as shown in this example.

      SQL> create user c##ggadmin identified by password default tablespace users
      temporary tablespace temp;
      SQL> alter user c##ggadmin quota 100M ON USERS;
      SQL> grant unlimited tablespace to c##ggadmin;
      SQL> grant connect, resource to c##ggadmin container=all;
      SQL> grant select any dictionary to c##ggadmin container=all;
      SQL> grant create view to c##ggadmin container=all;
      SQL> grant set container to c##ggadmin container=all;
      SQL> grant execute on dbms_lock to c##ggadmin container=all;
      SQL> exec
      dbms_goldengate_auth.GRANT_ADMIN_PRIVILEGE('c##ggadmin',container=>'all');

  • On the Target Database:

    • If the target is Autonomous Database, unlock the pre-created ggadmin user.

    • If the target is not Autonomous Database, create a ggadmin user in the target PDB as shown in the above examples. This user is similar to the ggadmin user on the source database. Grant the following additional privileges:

      
      SQL> grant select any table to ggadmin;
      SQL> grant insert any table to ggadmin;
      SQL> grant update any table to ggadmin;
      SQL> grant delete any table to ggadmin;

      See Establishing Oracle GoldenGate Credentials for information about privileges required for a "Replicat all modes" user.

  • Regardless of migration mode (FULL or SCHEMA or TABLE), ZDM enforces the following privileges for ggadmin user in the target database for DML replication:

    grant select any table to ggadmin;
    grant insert any table to ggadmin;
    grant update any table to ggadmin;
    grant delete any table to ggadmin;

  • Similarly, in case of DDL replication, ZDM enforces the following privileges for ggadmin user in the target database:

    GRANT CREATE ANY TABLE to GGADMIN;
    
    GRANT ALTER ANY TABLE to GGADMIN;
    
    GRANT DROP ANY TABLE to GGADMIN;
    
    GRANT CREATE ANY INDEX to GGADMIN;
    
    GRANT ALTER ANY INDEX to GGADMIN;
    
    GRANT DROP ANY INDEX to GGADMIN;
    
    GRANT CREATE ANY VIEW to GGADMIN;
    
    GRANT ALTER ANY VIEW to GGADMIN;
    
    GRANT DROP ANY VIEW to GGADMIN;
    
    GRANT CREATE ANY PROCEDURE to GGADMIN;
    
    GRANT ALTER ANY PROCEDURE to GGADMIN;
    
    GRANT DROP ANY PROCEDURE to GGADMIN;

  • To replicate tables with Oracle GoldenGate support_mode = ID KEY, the following privileges are required for ggadmin user in the source database. See Setting Flashback Query for more information.

    GRANT FLASHBACK ANY TABLE TO ggadmin;

• If the source database is configured to use SSL/TLS:

  If the source database is configured to use SSL/TLS, then ensure that the wallet containing certificates for TLS authentication is located in directory /u02/deployments/deployment_name/etc on the GoldenGate instance.

• If Oracle Database Vault is enabled in the source or target database:

  If you want to use Oracle GoldenGate in an Oracle Database Vault environment in the source or target database, then you must have the appropriate privileges granted for an Oracle GoldenGate account.

  See Privileges for Using Oracle GoldenGate with Oracle Database Vault for more information.

• If the target database is configured to use SSL/TLS:

  Ensure that the wallet containing certificates for TLS authentication is located in the correct location on the GoldenGate instance, as follows:

  • For an Autonomous Database, the wallet file should be located in directory /u02/deployments/deployment_name/etc/Wallet_<adbname> path or /u02/deployments/deployment_name/etc/adb. The new path is an alternate to use the Oracle GoldenGate for concurrent migration to two different targets.

  • For a co-managed database, the wallet file should be located in directory /u02/deployments/deployment_name/etc/Wallet_<dbname>

  Autonomous databases are always configured to use TLS.

• If GoldenGate schema name is different from the default value ggadmin:

  ZDM supports GoldenGate schema name different from default value other than ggadmin. By default, GoldenGate deployment is configured with GGSCHEMA= ggadmin in the GLOBALS file. You can specify a different GoldenGate schema name for source (PDB, CDB) and target. If you specify a different GoldenGate schema name, then you will need to update the GLOBALS file.

• Dynamic Export Parallelism for online logical migration

  In some cases, GoldenGate replication from source to target is unsuccessful due to INSTANTIATION_SCN MISMATCH between DBA_APPLY_INSTANTIATED_SCHEMAS and DBA_APPLY_INSTANTIATED_OBJECTS. As a solution, ZDM limits initial parallelism for Data Pump jobs to 1, until the SCHEMA/PROCACT_SCHEMA path is processed. Subsequently, ZDM adjusts the parallelism to the expected level for the remaining data export.

• Connecting to OCI through proxy server

  When ZDM server requires to access OCI REST endpoints through HTTP_PROXY, import the HTTP PROXY server certificate to the ZDM server $ZDM_HOME/jre/lib/security/cacerts using the ZDM_HOME/jdk/bin/keytool binary.

• Source and target database requirements for Oracle Database 23ai

  For database versions earlier than Oracle Database 23ai, continue granting privileges as follows:

  SQL> exec dbms_goldengate_auth.GRANT_ADMIN_PRIVILEGE(‘ggadmin’);

  However, for Oracle Database 23ai, grant the following roles for source and target:

  Source ggadmin: grant OGG_CAPTURE to ggadmin;
  Target ggadmin: grant OGG_APPLY to ggadmin;

• Source and target database requirements for non Autonomous databases

  Ensure to have the supported default shell as bash. If this is not the case, then you might get the following error: ZDM_VALIDATE_SRC fails with PRCZ-2103 : Failed to execute command"/usr/bin/id" LANG=en_US.UTF-8.

  This is the issue with default shell being cshell and not bash. On cshell, the environment parameters are not recognized so all the commands with environments will fail.

• If the version of the target database is earlier than Oracle Database 19c (19.18)

  You require Bug 34677088 if the version of the non-ADB target database is earlier than Oracle Database 19c (19.18) to avoid the following error:

  ORA-43853: SECUREFILE LOBs (Large Objects) cannot be used in non-automatic segment space management tablespace

  During import, ZDM applies the transform parameter LOB_STORAGE to SECUREFILE and if the target tablespace is not ASSM, then import can fail with ORA-43853: SECUREFILE LOBs (Large Objects) cannot be used in non-automatic segment space management tablespace.

  If you cannot apply BLR for Bug 34677088 in the target database, then set DATAPUMPSETTINGS_SECUREFILELOB to FALSE. However, the action makes all other objects (present in ASSM tablespaces) to not get transformed to secure LOB.

• Ensure that /tmp is mounted with execute permission as a prerequisite for the source and target databases if you want to perform a logical migration with host SSH access.

Direct Transfer from Source to Target

This option applies only to co-managed cloud target databases.

Zero Downtime Migration enables logical migration using direct transfer of the Data Pump dump from the source to the target securely. The data is copied over from the source database directory object path to the target database server directory object path, or to a target transfer node, using either secure copy or RSYNC. This avoids the data being transferred over a WAN or needing additional shared storage between the source and target environments. This capability greatly simplifies the logical migration within the data center.

About Transfer Nodes

You will configure a node, referred as a transfer node, for both the source data center and the target tenancy.

The response file parameters that are prefixed with DUMPTRANSFERDETAILS_SOURCE_TRANSFERNODE designate the node that handles the export dumps at the source data center. This source transfer node defaults to the source database.

Similarly, the response file parameters that are prefixed with DUMPTRANSFERDETAILS_TARGET_TRANSFERNODE designate the node that handles the import of dumps at the target. This target transfer node defaults to the target database, for co-managed targets.

Transfer Node Requirements

The source transfer node can be any of the following:

• Source database server (default)
• NAS mounted server
• Zero Downtime Migration service node

The target transfer node can be any of the following:

• Target Database server (default)
• NAS mounted server
• Zero Downtime Migration service node

For a server to be designated as transfer node, the following critical considerations are necessary.

• Availability of CPU and memory to process the upload or transfer workload

• Connectivity to the specified upload or transfer target

  • Port 443 connectivity to Object Storage Service if the chosen data transfer medium is OSS

  • Port 22 connectivity to target storage server if the chosen transfer medium is COPY

• Availability of Oracle Cloud Infrastructure CLI. For speedier and resilient upload of dumps this is the recommended transfer utility for the OSS transfer medium.

• OCI CLI must be installed and configured as detailed in https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm.

  Installing and configuring OCI CLI on each source database server may not be feasible. In such cases, one of the nodes in the data center can be designated as a transfer node with OCI CLI configured, and this node can share a network storage path with the database servers for Data Pump dumps to be created. This also avoids the upload workload consuming additional CPU and memory on production database servers.

The designated transfer node can act as the gateway server at the data center for the external data transfer allowing transfer data traffic, thus avoiding the need to allow data transfer from the source database server or to the target database server.

Optionally, the additional transfer node requirement can be avoided by leveraging the Zero Downtime Migration server as the transfer node, provided that the Zero Downtime Migration service is placed in an on-premises data center and can meet the transfer node requirements listed above.

Using the Oracle Cloud Object Storage Transfer Medium

Object Storage data transfer medium is supported for all migration types and targets.

When using Object Storage as the data transfer medium, by setting DATA_TRANSFER_MEDIUM=OSS, it is recommended that dumps be uploaded using OCI CLI for faster and more secure and resilient uploads. You must configure OCI CLI in the upload node, and set parameter DUMPTRANSFERDETAILS_SOURCE_USEOCICLI to TRUE, the parameters for OCI CLI are

DUMPTRANSFERDETAILS_SOURCE_USEOCICLI

DUMPTRANSFERDETAILS_SOURCE_OCIHOME

For Oracle Cloud Object Storage based migration, ensure that the cloud access user is privileged to create a pre- authenticated URL for object storage bucket, specified for Data Pump Dump staging. See Using Pre-Authenticated Requests for details.

Using the Database Link Transfer Medium

Supported for online and offline migrations to all migration targets

When you set DATA_TRANSFER_MEDIUM=DBLINK, a database link is created from the OCI co-managed database or Autonomous Database target to the source database using the global_name of the specified source database.

Zero Downtime Migration creates the database link if it does not already exist, and the link is cleaned once the Data Pump import phase is complete.

Using the NFS Transfer Medium

The NFS mode of transfer is available, by setting DATA_TRANSFER_MEDIUM=NFS, for co-managed target databases that avoid the transfer of dumps. You should ensure that the specified path is accessible between the source and target database server path.

Zero Downtime Migration ensures the security of dumps in the shared storage by preserving the restricted permission on the dumps such that only the source and target database users are allowed to access the dump.

See Mount Options for Oracle files for RAC databases and Clusterware when used with NFS on NAS devices (Doc ID 359515.1)

Using the Copy Transfer Medium

Supported for co-managed and user managed target databases only.

DUMPTRANSFERDETAILS_TRANSFERTARGET_USER

DUMPTRANSFERDETAILS_TRANSFERTARGET_USERKEY

DUMPTRANSFERDETAILS_TRANSFERTARGET_HOST

DUMPTRANSFERDETAILS_TRANSFERTARGET_SUDOPATH

DUMPTRANSFERDETAILS_TRANSFERTARGET_DUMPDIRPATH

You can leverage the RSYNC utility instead of SCP. Set DUMPTRANSFERDETAILS_RSYNCAVAILABLE to TRUE, and verify that RSYNC is available both at the source and target transfer nodes.

Specify rules for objects to include, exclude, or reload in a migration job using the EXLCUDEOBJECTS-n, RELOADOBJECTS-nand INCLUDEOBJECTS-n response file parameters. You can either include or exclude objects in a migration, but you cannot do both.

If no rule is defined, all schemas and objects of the source database will be migrated, with exceptions listed in What Is Migrated During Initial Load.

If you specify Include rules, the migration will only move the specified objects and their dependent objects; all other objects are automatically excluded. USER and TABLESPACE_QUOTA objectType associated with included schema are included by default.

When specifying Exclude rules, the specified objects and their dependent objects will not be migrated using Oracle Data Pump and will not be replicated using Oracle GoldenGate.

If you specify Reload rules, the specified objects will not be replicated using Oracle GoldenGate. Instead, the specified objects will be migrated to target database using Oracle Data Pump during phase ZDM_RELOAD_PARALLEL_EXPORT_IMPORT when there is no active workload on source database.

You can define multiple include, exclude, or reload rules by incrementing the integer appended to the parameter name, as shown in these examples.

Example of a single include rule:

INCLUDEOBJECTS-1=owner:ownerValue1, objectName:objectNameValue1, objectType:objectTypeValue1

Example of two exclude rules:

EXCLUDEOBJECTS-1=owner:ownerValue1, objectName:objectNameValue1, objectType:objectTypeValue1

EXCLUDEOBJECTS-2=owner:ownerValue2, objectName:objectNameValue2, objectType:objectTypeValue2

Example of two reload rules:

RELOADOBJECTS-1=owner:ownerValue1, objectName:objectNameValue1, objectType:objectTypeValue1

RELOADOBJECTS-2=owner:ownerValue2, objectName:objectNameValue2, objectType:objectTypeValue2

Use of wildcard expressions are allowed for RELOADOBJECTS parameter:

RELOADOBJECTS-1=ownerValue1,objectName:.*,objectType:TABLE

RELOADOBJECTS-1=owner:ownerValue1,objectName:EMPL.*,objectType:TABLE

Required Parameter Fields

To specify INCLUDEOBJECTS-n, EXLCUDEOBJECTS-n, and RELOADOBJECTS-n response file parameters, enter values for each of the following fields:

• owner specifies the owner of the selected database objects. When using Include rules, all rules must be for the same owner, and wild characters are not allowed.

• objectName specifies the name of selected database objects

• objectType specifies the type of selected database objects. You can select ALL to select objects of all types (default).

You can filter owner and objectName fields using any valid pattern in Java class Pattern. For example, you can enter .* in the objectName field to select objects of any name.

Restrictions

You must note the following restrictions:

• When excluding an object in a specified schema, and an object of the same name exists in a different schema that is also part of the migration, the objects will not be excluded (that is, the rule is ignored). The exclusion can be accomplished by migrating the schemas in separate migrations.

• When creating Include rules in FULL job mode, only schema-level rules (objectName is .* and objectType is ALL) are allowed.

• If an Include rule has .* in objectName, no other rule for the same objectType is allowed. If the rule has ALL as objectType, no other rule for any type is allowed.

• The objectType ALL is only allowed for schema-level rules (objectName is .*).

• The objectType TABLE is not allowed for schema-level rules where the owner is specified (objectName is .* and owner is any pattern other than .*).

• Object-level rules (objectName is any pattern other than .*) can only be used for the following object types: DIRECTORY, FUNCTION, JOB, MATERIALIZED_VIEW, PACKAGE, PROCEDURE, TRIGGER, SEQUENCE, TABLE, ROLE, PROFILE (Restricted to job mode FULL), PROCOBJ.

  All other objectTypes must be either included or excluded using the .* pattern in objectName, and in addition for exclude, the owner should be .*

• The RELOADOBJECTS parameter is only supported for online logical migration.
• SEQUENCE after migration is not advanced in the following cases:
  • If the sequence name ends with _SEQ or _SHSEQ
  • If the sequence name starts with AQ$_ or ISEQ$$_
  • If the sequence is marked secondary = 'Y' in dba_objects table.

Supported objectTypes for object-level filtering

objectName can be chosen or can be .*

DIRECTORY

FUNCTION

JOB

MATERIALIZED_VIEW

PACKAGE

PROCEDURE

TRIGGER

SEQUENCE

TABLE

ROLE

PROFILE (Restricted to job mode FULL)

PROCOBJ

Supported objectTypes for generic objectType-level exclude or include

CLUSTER

DB_LINK

GRANT

OBJECT_GRANT

SYSTEM_GRANT

ROLE_GRANT

PROCOBJ_GRANT

PROCDEPOBJ_GRANT

INDEX

INDEXTYPE

MATERIALIZED_VIEW_LOG

MATERIALIZED_ZONEMAP

POST_TABLE_ACTION

PROCOBJ

PROFILE

REF_CONSTRAINT

RLS_POLICY

SYNONYM

TABLESPACE

TABLESPACE_QUOTA

USER

XMLSCHEMA

VIEW

AUDIT_TRAILS

ON_USER_GRANT

Filtering other objectTypes

To filter other objectTypes that are NOT listed above like COMMENT, FGA_POLICY, and so on, use the DATAPUMPSETTINGS_DATAPUMPPARAMETERS_EXCLUDETYPELIST parameter.

Examples

Specify Included and Excluded Objects with Special Characters

The following examples show you how to specify objects names that use special characters in the EXCLUDEOBJECTS and INCLUDEOBJECTS parameters.

• To escape a special character, use two slashes (//) before and after all characters in the string before the special character.

  For example, to escape dollar sign ($):

  \\INLUDEOBJECTS-3= owner:GRAF_MULTI\\$_HR

• To match all characters in between prefix and suffix pattern, add a period and an asterisk (.*) where the matching should occur.

  For example, to exclude all schemas starting with GRAF and ending with HR:

  EXCLUDEOBJECTS-3= owner:GRAF.*HR

You can perform a phased migration using this feature.

If you specify a different GoldenGate schema name, then you will need to update the GLOBALS file.

To import METADATA before DATA, set value of the DATAPUMPSETTINGS_METADATAFIRST parameter to TRUE.

To configure batch mode:

In the RSP, set DATAPUMPSETTINGS_JOBMODE parameter to SCHEMA.

The batch mode of operation is not enabled by default; you must enable it by setting one of the following RSP parameters:

• DATAPUMPSETTINGS_SCHEMABATCHCOUNT=integer indicates how many schemas to include in each batch.

  In this case Zero Downtime Migration does not guarantee the set of schemas per batch (the Data Pump job).

• DATAPUMPSETTINGS_SCHEMABATCH-n, where n cam be -1, -2, 03, lets you specify a list schemas to place in a batch for parallel handling.

Note that DATAPUMPSETTINGS_SCHEMABATCHCOUNT and DATAPUMPSETTINGS_SCHEMABATCH are mutually exclusive.

Also, note that database initialization parameter MAX_DATAPUMP_JOBS_PER_PDB determines the maximum number of concurrent Oracle Data Pump jobs for each PDB.

Restrictions

DATAPUMPSETTINGS_JOBMODE value other than SCHEMA is prohibited if a batch of schemas is specified.

Specifying INCLUDEOBJECTS is prohibited if batch of schemas is specified.

No SSH Source and parallel migration is not allowed - the job must be paused for manual upload of dumps that defeats the purpose exporting schemas in parallel.

MAX_DATAPUMP_JOBS_PER_PDB determines the maximum number of concurrent Oracle Data Pump jobs per PDB. Exceeding this limit causes ORA-39391: maximum number of Data Pump jobs (n) exceeded.

Supported Use Cases

The following migration scenarios are supported by Zero Downtime Migration:

• Oracle Exadata Database Service on Cloud@Customer (Gen 1 or Gen 2) source to Oracle Autonomous Database on Exadata Cloud@Customer target (given that the source and target databases have the same standard UID/GID for the Oracle user)

• On-premises Oracle Database source to Oracle Autonomous Database on Exadata Cloud@Customer target (given that the source database has a non-standard UID/GID for the Oracle user)

Migration Parameters

In addition to the required source and target connection parameters, set the following in the logical migration response file:

MIGRATION_METHOD=OFFLINE_LOGICAL or ONLINE_LOGICAL

DATA_TRANSFER_MEDIUM=NFS

Source Prerequisites

In addition to the usual source database prerequisites documented in Source Database Prerequisites for Logical Migration, you must also set up access to the Data Pump dump directory as detailed in the procedures below.

Prerequisite Setup for Exadata Cloud@Customer Environments

1. In all Oracle RAC nodes:

   [root@onprem ~]# cat /etc/fstab | grep nfsshare
   nas-server.us.com:/scratch/nfsshare /u02/app/oracle/mount nfs defaults 0 0
   [root@onprem ~]#

2. On the Autonomous Database target, mount the path

   nas-server.us.com:/scratch/nfsshare

   to the Exadata infrastructure resource, giving you

   specified_mount_path/CDB/PDB_GUID

   For example:

   /scratch/nfsshare/CDB/PDB_GUID

   For information about the option to mount NFS, contact support for details.

3. On the source PDB, run the following:

   SQL> create or replace directory DATA_PUMP_DIR_ADBCC as '/u02/app/oracle/mount/CDB/PDB_GUID';
   
   Directory created.
   
   SQL> select grantee from all_tab_privs where table_name = 'DATA_PUMP_DIR_ADBCC';
   
   no rows selected
   
   SQL> grant read, write on directory DATA_PUMP_DIR_ADBCC to SYSTEM;
   
   Grant succeeded.

4. On the source, mount point permissions expected (drwxr-x---)

   [oracle@onprem opc]$ ls -ldrt /u02/app/oracle/mount/CDB/PDB_GUID
   drwxr-x--- 2 oracle asmadmin 4096 Jul 12 11:34 /u02/app/oracle/mount/CDB/PDB_GUID
   [oracle@onprem opc]$

Prerequisite Setup for On-Premises Environments

1. In all Oracle RAC nodes:

   [root@onprem ~]# cat /etc/fstab | grep nfsshare
   nas-server.us.com:/scratch/nfsshare /u02/app/oracle/mount nfs defaults 0 0
   [root@onprem ~]#

2. Create a group with GID 1001 - miggrp

   root> groupadd -g 1001 miggrp

3. Add the database user to this group.

   root> usermod -aG migrp oracle

4. On the Autonomous Database target, mount the NFS share (Group should get rwx)

   nas-server.us.com:/scratch/nfsshare

   to the Exadata infrastructure resource, giving you

   specified_mount_path/CDB/PDB_GUID

   For example:

   /scratch/nfsshare/CDB/PDB_GUID

   For information about the option to mount NFS, contact support for details.

5. Ensure that the directory is writable.

   Touch specified_mount_path/CDB/PDB_GUID/test.txt

6. In the source PDB, run the following:

   SQL> create or replace directory DATA_PUMP_DIR_ADBCC as '/u02/app/oracle/mount/CDB/PDB_GUID';
   
   Directory created.
   
   SQL> select grantee from all_tab_privs where table_name = 'DATA_PUMP_DIR_ADBCC';
   
   no rows selected
   
   SQL> grant read, write on directory DATA_PUMP_DIR_ADBCC to SYSTEM;
   
   Grant succeeded.

7. On the source, mount point permission expected (drwxrwx—), and the group should match the migration dummy group created.

   [oracle@onprem opc]$ ls -ldrt /u02/app/oracle/mount/CDB/PDB_GUID
   drwxrwx--- 2 1001 asmadmin 4096 Jul 12 11:34 /u02/app/oracle/mount/CDB/PDB_GUID
   [oracle@onprem opc]$

The dumps in NFS mounted path are not readable for target database user unless the Unique Identifiers (UIDs) for the source database user matches with the target database user.

Complete the following prerequisites on the source database to prepare for a logical migration.

• If the IDs match, dumps are readable by default and no action is required.
• If the IDs do not match, ZDM automatically discovers the primary group of the target database user and changes the group of the dump to the primary group of the target database user.

  Note:

  Currently, this option is not supported in IBM AIX.
  • To perform this action, the source database user in the database server must be a part of a group having the same group ID as that of the target database user's primary group.
  • If there is no group available, then create an auxiliary group on the source database server with GID matching to the GID of target database user.
  • Make the source database user to be a part of the auxiliary group.

    Note:

    As an example, refer to Step 1 through Step 3 of Prerequisite Setup for Exadata Cloud@Customer Environments topic in Migrating to Oracle Autonomous Database on Exadata Cloud@Customer.
• However, if GID is not available and the above two conditions cannot be met, as an option you can set value of the DUMPTRANSFERDETAILS_PUBLICREAD to TRUE to make the dump files on NFS readable for others.

Complete the following prerequisites on the source database to prepare for a logical migration.

You can perform a logical migration from an Autonomous Database to an Autonomous Database via DBLINK or OSS.

ZDM configures PDB extract for the following use cases:

To create and register the Extract specific to a PDB, connect to the local ggadmin user in a specific pluggable database.