Migrate to Exadata Database

Source : Oracle Database platform, such as Oracle Exadata Database, Oracle RAC, or a standalone Oracle Database instance on Linux.

Target : Oracle Exadata Database running on Oracle AI Database@Google Cloud.

• A bucket containing the data file(s) such Oracle Data Pump dump file.

• For Oracle Database, internet connectivity or private link using Oracle Interconnect for Google Cloud is supported.
• For Oracle AI Database@Google Cloud, internal connectivity within the same cloud region is supported by default.

• A Google Cloud Access Key and Secret (HMAC key) with bucket access permissions.

1. Create Google Cloud Storage Access Credentials
   1. In the Google Cloud Console, navigate to Cloud Storage.
   2. Select Settings from the left menu, and then select the Interoperability tab.
   3. Scroll down to the Access keys for your user account section, select the Create a key button to generate an Access key and Secret. Take note of the Access key and Secret as these are required to authenticate Oracle Database access.
2. Determine Object URL
   1. In the Google Cloud Console, navigate to Cloud Storage.
   2. From the left menu, select Buckets, and then select the target bucket that you are using.
   3. From the Bucket details page, locate the object. For example, Oracle Data Pump export file which you want to import.
   4. Construct the public object URL in the form.

      https://<bucket_name>.storage.googleapis.com/<object_name>

      For example:

      • Bucket name: exadbtest
      • Object name: emp.dmp
      • Object URL: https://exadbtest.storage.googleapis.com/emp.dmp
      

1. Create Oracle Credential
   1. Using the DBMS_CLOUD.CREATE_CREDENTIAL PL/SQL procedure, create a credential in the database that stores your GCS access key and secret:

      BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => '<Credential_Name>', username => '<GCS_Access_Key>', password => '<GCS_Secret>' ); END; /

      For example:

      BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 'GCP_CRED', username => 'GOOGXXXXXXXXXXXX', password => 'xs9njBZ+hykXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ); END; /

   2. After running the PL/SQL procedure, the credential is stored and ready for use by DBMS_CLOUD. For more information about the parameters, see CREATE_CREDENTIAL Procedure.
2. Verify Access to Google Cloud Storage
   1. To ensure that the database can reach your Google Cloud Storage bucket, run the following:

      SELECT object_name FROM dbms_cloud.list_objects( credential_name => 'GCP_CRED', uri => 'https://exadbtest.storage.googleapis.com' );

   2. The output should list the object(s), confirming successful access. For example:

      OBJECT_NAME
      --------------------------------------------------------------------------------
      emp.dmp
      employee.csv

After creating the necessary credential for Google Cloud Storage, you can load or access data into Oracle Database using any of the following methods: DBMS_CLOUD.COPY_DATA procedure, Oracle Data Pump Import (impdp), DBMS_CLOUD.CREATE_EXTERNAL_TABLE, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE, the Data Studio Load tool (UI in Exadata Database), or DBMS_CLOUD.CREATE_HYBRID_PART_TABLE for hybrid partitioned tables.

This step demonstrates DBMS_CLOUD.COPY_DATA as well as Oracle Data Pump Import (impdp).

1. Using DBMS_CLOUD.COPY_DATA Procedure
   1. Create a table in your database where you want to load the data.

      CREATE TABLE emp (id NUMBER, name VARCHAR2(64));

   2. Import data from the Google Cloud Storage bucket to your Exadata Database.
   3. Specify the table name and the GCP credential name followed by the Google Cloud Storage object URL.
   4. Use the DBMS_CLOUD.COPY_DATA procedure to load data from your Google Cloud Storage bucket into a table. The file_uri_list parameter specifies the path to your files in Google Cloud Storage.

      BEGIN
      
        DBMS_CLOUD.COPY_DATA(
      
          table_name    => 'YOUR_TARGET_TABLE',
      
          credential_name => 'GOOGLE_CLOUD_CRED',
      
          file_uri_list => 'https://exadbtest.storage.googleapis.com/employee.csv', -- Or a list of files
      
          format        => json_object('type' value 'CSV', 'skipheaders' value '1') -- Specify file format options
      
        );
      
      END;
      
      /

      For more information about the parameters, see COPY_DATA Procedure.

   5. Once you have successfully imported data from Google Cloud Storage to your Exadata Database, you can run this statement and verify the data in your table.

      SELECT * FROM emp;

2. Import Data Using Data Pump

   The import command can be executed either on the VM cluster or on an external virtual machine with authorized SQL*Plus access to the target database.

   1. Create a parameter file imp_gcp.par with the following contents:

      directory=DATA_PUMP_DIR
      credential=GCP_CRED
      schemas=emp
      remap_tablespace=USERS:DATA
      dumpfile=https://exadbtest.storage.googleapis.com/emp.dmp

   2. Run the import from Exadata VM Cluster or Client. Ensure that Oracle client is installed and you can connect to the Exadata Database:

      impdp userid=ADMIN@demo_db parfile=imp_gcp.par

   3. A successful run loads the data into your Oracle Database. Example of a successful completion message:

      Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed.

1. Enter a unique name in the Instance ID field for the Google Cloud Filestore instance. The ID must contain lowercase letters, numbers, and hyphens. It must start with a letter.
2. The Description field is optional.
3. From the Configure service tier section, select an appropriate service tier based on your migration workload requirements. From the Instance type section, choose your instance type. Instance type affects capacity, performance, scalability, durability, and cost.
4. Choose your Storage type based on your requirements.
5. After choosing your storage type, enter the capacity value in the Capacity field.
   For better performance and lower networking cost, place your instance in the same region as the VMs that will connect to it. Choice is permanent.

   1. Select the Capacity range that suits your file system.
   2. Configure your performance based on your workload and scale.
6. Select the Region and Zone where the Google Cloud Filestore instance will be created. This must align with the network location of the database hosts that will access the file share.
7. From the Set up connections section, select the network and address range that clients will use to access your instance.
   • From the VPC network dropdown list, select your VPC.
   • From the Allocated IP range section, choose the Use an automatically allocated IP range (recommended) option. This IP range will be used to create subnets.
8. From the Configure your file share section, enter your file share name in the field. Choice is permanent. You can use lowercase letters, numbers, and underscores. The file share name must start with a letter. Choose your Access control.
9. The Create labels, Tags sections are optional.
10. Review your information, and select the Create button.

1. To install NFS utilities before mounting, run the following command:

   # RHEL / CentOS 
   sudo yum update && sudo yum install nfs-utils

1. Create a directory on the client where the Filestore share will be mounted:

   sudo mkdir -p /mnt/filestore-share

To mount an NFS file share, use the mount command. Replace FQDN or IP Address of Fileshare and Mount Path with your values. In the following example, values are used from the NFS mount point section of the testfs Google Cloud Filestore file share.

If you have configured a Fully Qualified Domain Name (FQDN), you can use it instead of the IP address. See the Migrate to Autonomous AI Database section for guidance on creating an FQDN and setting up DNS resolution.

1. Run the following command after updating with your values:

   sudo mount -o rw <FQDN or IP Address of Fileshare> <Mount Path>

   For example:

   sudo mount -o rw 10.210.123.202:/testfs /mnt/filestore-share

1. To check that the Filestore share is mounted, run the following command:

   df -h --type=nfs

2. After mounting the base share, you can create and mount sub-directories:

   sudo mkdir -p /mnt/filestore-share/subdir
   sudo mount IP_ADDRESS:/share_name/subdir /mnt/filestore-share/subdir

3. To enable automatic mounting, add the mount entry to /etc/fstab. For dynamic mounting, use autofs. For more information on all mounting options, see the Mounting Filestore file share documentation.

1. Use the mounted Filestore as a shared staging location for Oracle Data Pump dump files (.dmp).
2. Place large export files from on-premises or other environments directly on the Filestore share. For example:

   /mnt/filestore-share/datapump/

3. Run impdp from your Exadata Database nodes or client VMs, referencing the local mount path instead of Google Cloud Storage URLs.
4. Example command from a mounted client or Oracle Exadata VM Cluster.

   impdp system/password@exadata_pdb \
     directory=DATA_PUMP_DIR \
     dumpfile=/mnt/filestore-share/datapump/sales_export.dmp \
     logfile=import.log \
     schemas=SALES_APP \
     parallel=8

1. Enable the required features as SYS:

   
   ALTER DATABASE FORCE LOGGING;
   ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(<primary_unique_name>,<standby_unique_name>)' SCOPE=BOTH;
   ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

2. Add standby redo logs (example for 3 online groups, add 4 standby groups).

   ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/oradata/standby_redo04.log') SIZE 100M;
   -- Repeat for additional groups.

3. Create a backup of the primary database using RMAN (include archivelogs for synchronization):

   CONNECT TARGET / BACKUP DATABASE FORMAT '/backup/db_%U' PLUS ARCHIVELOG FORMAT
         '/backup/arc_%U'; BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT
         '/backup/standby_ctl.ctl';

4. Copy the backup files, password file (orapw<sid>), and parameter file (init.ora or spfile) to the target server.

1. Install Oracle software (match or higher version for upgrade).
2. Create necessary directories:

   mkdir -p /u01/oradata/<db_name>mkdir -p /u01/fra/<DB_UNIQUE_NAME_upper>mkdir -p /u01/app/oracle/admin/<db_name>/adump

3. Edit the copied parameter file on standby:
   • Set DB_UNIQUE_NAME to the standby value.
   • Set FAL_SERVER to the primary TNS name.
   • Adjust paths if needed. For example, CONTROL_FILES, DB_RECOVERY_FILE_DEST.
4. Start the standby instance in NOMOUNT:

   CREATE SPFILE FROM PFILE='/path/to/init.ora';STARTUP NOMOUNT;

1. Use RMAN to duplicate the database from the active database :

   CONNECT TARGET sys/<password>@primary_tns CONNECT AUXILIARY sys/<password>@standby_tns
             DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER
             NOFILENAMECHECK;

   Alternative: If you use backup files, duplicate the database for standby from a backup location:

   DUPLICATE DATABASE FOR STANDBY BACKUP LOCATION '/backup'
       NOFILENAMECHECK;

2. On standby, start managed recovery:

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

1. On primary, set remote logging:

   ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=<standby_tns> ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<standby_unique_name>' SCOPE=BOTH;ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;ALTER SYSTEM SET FAL_SERVER='<standby_tns>' SCOPE=BOTH;

2. On standby:

   ALTER SYSTEM SET FAL_SERVER='<primary_tns>' SCOPE=BOTH;

3. (Optional) Enable Data Guard Broker for easier management:
   • Set DG_BROKER_START=TRUE on both.
   • Create the configuration:

     DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS <primary_unique_name> CONNECT IDENTIFIER IS <primary_tns>;

   • Add standby:

     DGMGRL> ADD DATABASE <standby_unique_name> AS CONNECT IDENTIFIER IS <standby_tns>;

   • Enable the configuration:

     DGMGRL> ENABLE CONFIGURATION;

1. On primary, force log switches:

   ALTER SYSTEM SWITCH LOGFILE;

2. On standby, check apply status:

   SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM v$managed_standby;

   Look for MRP0 applying logs.

3. Check for gaps:

   SELECT * FROM v$archive_gap;

   The output should return no rows.

4. Monitor lag:

   SELECT NAME, VALUE FROM v$dataguard_stats WHERE NAME = 'apply lag';

Allow time for full sync before migration.

1. Stop applications connected to primary (minimal downtime starts here).
2. Verify no gaps and stop recovery on standby:

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3. Using Broker (recommended):

   DGMGRL> SWITCHOVER TO <standby_unique_name>;

   • Manual alternative:
     • On primary:

       ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

     • On old primary (now standby):

       STARTUP MOUNT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

     • On new primary (old standby):

       ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY WITH SESSION SHUTDOWN; ALTER DATABASE OPEN;

4. Verify roles:

   SELECT DATABASE_ROLE FROM v$database;

   New primary should be PRIMARY.

5. Redirect applications to new primary.
6. (Optional) Decommission old primary or keep as new standby.