Loading....

In example we will use a combination of Oracle Cloud Marketplace GoldenGate and the MV2ADB tool to migrate data in real-time from an Oracle 12c database hosted on Gen 1 OCI Classic infrastructure to an OCI Oracle 18c Autonomous database.

Oracle GoldenGate Extract and Replicat processes run on Cloud Marketplace which has network connectiviy to both the source and target databases.

The MV2ADB tool is used to take a consistent Oracle Data Pump Export of the source 12c database , copy the export dump file to the Oracle Cloud Object Store as well as perform the Oracle Data Pump Import into the target ATP database – all automated and executed via a single mv2adb auto command.

In addition Oracle Instant Client is installed on the OCI Classic compute node.

A few things to note with regards to the ATP database – the user account we use to connect to the ATP database is ADMIN and we have to download the client credential wallet from the ATP database in order to establish connectivity. We can only create a Classic Replicat and not Integrated for the target ATP database. The ggadmin user already exists in the ATP database and we just have to unlock the user account.

Lets take a look at the steps involved.

Configure the source database for Oracle GoldenGate

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system set enable_goldengate_replication=true sid=’*’;

System altered.

SQL> alter system set streams_pool_size=1024m;

System altered.

SQL> create user C##oggadmin identified by oracle container=all;

User created.

SQL> grant dba to C##oggadmin container=all;

Grant succeeded.

SQL> exec dbms_goldengate_auth.grant_admin_privilege(‘C##oggadmin’,container=>’ALL’);

PL/SQL procedure successfully completed.

Download and copy ATP credential wallet

[opc@SITOIC01db wallet]$ cp payment_wallet.zip /tmp

[opc@SITOIC01db wallet]$ sudo su – oracle

[oracle@SITOIC01db ~]$ mkdir wallet

[oracle@SITOIC01db ~]$ cd wallet/

[oracle@SITOIC01db wallet]$ cp /tmp/payment_wallet.zip .

[oracle@SITOIC01db wallet]$ ls -lrt

total 24

-rwxr-xr-x 1 oracle oinstall 20492 Mar 19 15:10 payment_wallet.zip

[oracle@SITOIC01db wallet]$ unzip payment_wallet.zip

Archive:  payment_wallet.zip

  inflating: cwallet.sso

  inflating: readme.md

  inflating: tnsnames.ora

  inflating: truststore.jks

  inflating: ojdbc.properties

  inflating: sqlnet.ora

  inflating: ewallet.p12

  inflating: keystore.jks

Configure connectivity to source and target databases from Cloud Marketplace GoldenGate compute node

bash-4.2$ cd /u01

-bash-4.2$ ls

app

-bash-4.2$ cd app

-bash-4.2$ ls

client  ogg

-bash-4.2$ cd client

-bash-4.2$ ls -lrt

total 16

drwxr-xr-x. 54 opc opc 4096 Jan  2 22:54 oracle11

drwxr-xr-x. 55 opc opc 4096 Jan  2 22:56 oracle12

drwxr-xr-x. 54 opc opc 4096 Jan  2 22:57 oracle18

drwxr-xr-x. 55 opc opc 4096 Jan  2 22:58 oracle19

-bash-4.2$ cd oracle18

-bash-4.2$ cd network/admin

-bash-4.2$ cat tnsnames.ora

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 139.154.98.48)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL.602302376.oraclecloud.internal)

    )

  )

PDB1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 139.154.98.48)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pdb1.602302376.oraclecloud.internal)

    )

  )

payments_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=ogkn8slw3p13upi_payments_high.atp.oraclecloud.com))(security=(ssl_server_cert_dn=”CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US”)))

payments_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=ogkn8slw3p13upi_payments_low.atp.oraclecloud.com))(security=(ssl_server_cert_dn=”CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US”)))

payments_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=ogkn8slw3p13upi_payments_medium.atp.oraclecloud.com))(security=(ssl_server_cert_dn=”CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US”)))

payments_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=ogkn8slw3p13upi_payments_tp.atp.oraclecloud.com))(security=(ssl_server_cert_dn=”CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US”)))

payments_tpurgent = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=ogkn8slw3p13upi_payments_tpurgent.atp.oraclecloud.com))(security=(ssl_server_cert_dn=”CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US”)))

Edit the GoldenGate Manager parameter file

GGSCI (ogg19cora) 1> edit params mgr

Port 10019

ACCESSRULE, PROG COLLECTOR, IPADDR <IP_Address_GGCS_host>, ALLOW

PURGEOLDEXTRACTS /u02/trails, USECHECKPOINTS

AUTORESTART ER *

AUTOSTART ER *

GGSCI (ogg19cora) 2> stop mgr

Manager process is required by other GGS processes.

Are you sure you want to stop it (y/n)?y

Sending STOP request to MANAGER …

Request processed.

Manager stopped.

GGSCI (ogg19cora) 3> start mgr

Manager started.

GGSCI (ogg19cora) 4> info mgr

Manager is running (IP port TCP:ogg19cora.10019, Process ID 21699).

Create Credential Store and add source database user

GGSCI (ogg19cora) 1> add credentialstore

Credential store created.

GGSCI (ogg19cora) 8> alter credentialstore add USER c##oggadmin@orcl alias oggadmin_ocic

Password:

Credential store altered.

GGSCI (ogg19cora) 9> info credentialstore

Reading from credential store:

Default domain: OracleGoldenGate

  Alias: oggadmin_ocic

  Userid: c##oggadmin@orcl

Create Integrated Extract

GGSCI (ogg19cora) 1> dblogin useridalias oggadmin_ocic

Successfully logged into database CDB$ROOT.

GGSCI (ogg19cora as c##oggadmin@ ORCL/CDB$ROOT) 3> add extract ext1 integrated tranlog begin now

EXTRACT (Integrated) added.

GGSCI (ogg19cora as c##oggadmin@ ORCL/CDB$ROOT) 4> add exttrail /u02/deployments/oracle19/dirdat/lt extract ext1

EXTTRAIL added.

GGSCI (ogg19cora as c##oggadmin@ ORCL/CDB$ROOT) 5> edit params ext1

EXTRACT ext1

USERIDALIAS oggadmin_ocic

EXTTRAIL /u02/deployments/oracle19/dirdat/lt

SOURCECATALOG pdb1

TABLE  TEST_MIG.*;

GGSCI (ogg19cora as c##oggadmin@ORCL/CDB$ROOT) 9> register extract ext1 database container(pdb1)

2020-03-31 10:44:49  INFO    OGG-02003  Extract EXT1 successfully registered with database at SCN 1371169086.

Add GoldenGate Supplemental Logging

GGSCI (ogg19cora as c##oggadmin@ORCL/CDB$ROOT) 7> ADD SCHEMATRANDATA PDB1.TEST_MIG PREPARECSN

2020-03-18 15:12:51  INFO    OGG-01788  SCHEMATRANDATA has been added on schema “TEST_MIG”.

2020-03-18 15:12:52  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema ” TEST_MIG “.

2020-03-18 15:12:52  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema ” TEST_MIG “.

2020-03-18 15:12:52  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema ” TEST_MIG “.

Start Integrated Extract

GGSCI (ogg19cora as c##oggadmin@ORCL/CDB$ROOT) 19> start extract ext1

Sending START request to MANAGER …

EXTRACT EXT1 starting

GGSCI (ogg19cora as c##oggadmin@ORCL/CDB$ROOT) 20> info ext1

EXTRACT    EXT1      Initialized   2020-03-18 14:49   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:26:52 ago)

Process ID           5567

Log Read Checkpoint  Oracle Integrated Redo Logs

                     2020-03-18 14:49:42

                     SCN 0.0 (0)

GGSCI (ogg19cora as c##oggadmin@ORCL/CDB$ROOT) 22> info ext1

EXTRACT    EXT1      Last Started 2020-03-18 15:16   Status RUNNING

Checkpoint Lag       unknown (updated 00:00:07 ago)

Process ID           5567

Log Read Checkpoint  Oracle Integrated Redo Logs

                     2020-03-19 02:18:28

                     SCN 0.266562404 (266562404)

Configure ggadmin user on the target ATP database

-bash-4.2$ export ORACLE_HOME=/u01/app/client/oracle19

-bash-4.2$ export PATH=$ORACLE_HOME/bin:$PATH

-bash-4.2$ export TNS_ADMIN=/u01/app/client/oracle19/network/admin

-bash-4.2$ sqlplus admin/<admin pwd>@payments_high

SQL> alter user ggadmin identified by Dreamliner787## ;

User altered.

SQL> alter user ggadmin account unlock;

User altered.

SQL> alter user ggadmin quota unlimited on data;

User altered.

Configure GoldenGate environment for target ATP database

Add ATP target database user to Credential Store

GGSCI (ogg19cora) 2> alter credentialstore add user ggadmin@payments_high alias oggadmin_atp

Password:

Credential store altered.

GGSCI (ogg19cora) 1> dblogin useridalias  oggadmin_atp

Successfully logged into database OGKN8SLW3P13UPI_PAYMENTS.

Create Checkpoint Table

GGSCI (ogg19cora) 4> dblogin useridalias oggadmin_atp

Successfully logged into database OGKN8SLW3P13UPI_PAYMENTS.

GGSCI (ogg19cora as ggadmin@eim1pod8/OGKN8SLW3P13UPI_PAYMENTS) 5> add checkpointtable ggadmin.chkptab

Logon catalog name OGKN8SLW3P13UPI_PAYMENTS will be used for table specification OGKN8SLW3P13UPI_PAYMENTS.ggadmin.chkptab.

Successfully created checkpoint table OGKN8SLW3P13UPI_PAYMENTS.ggadmin.chkptab.

Create Replicat (note integrated or parallel integrated replicat not available on ATP)

GGSCI (ogg19cora as ggadmin@eim1pod8/OGKN8SLW3P13UPI_PAYMENTS) 7> add replicat rep1 exttrail /u02/deployments/oracle19/dirdat/lt checkpointtable ggadmin.chkptab

R

EPLICAT added.

GGSCI (ogg19cora as ggadmin@eim1pod8/OGKN8SLW3P13UPI_PAYMENTS) 8> edit params rep1

REPLICAT rep1

USERIDALIAS oggadmin_atp

MAP PDB1.TEST_MIG.*, TARGET OGKN8SLW3P13UPI_PAYMENTS.TEST_MIG.*;

Install and Configure mv2adb on OCI Classic Compute Node

-bash-4.2$ pwd

/home/opc

-bash-4.2$ mkdir mv2adb

-bash-4.2$ mv mv2adb-2.0.1.80.tar.gz ./mv2adb

-bash-4.2$ cd mv2adb/

-bash-4.2$ ls

mv2adb-2.0.1.80.tar.gz

-bash-4.2$ gunzip mv2adb-2.0.1.80.tar.gz

-bash-4.2$ ls -lrt

total 292

-rw-r–r–. 1 opc opc 296960 Mar 20 03:54 mv2adb-2.0.1.80.tar

-bash-4.2$ tar -xvf mv2adb-2.0.1.80.tar

mv2adb-2.0.1.80/

mv2adb-2.0.1.80/mv2adb

mv2adb-2.0.1.80/conf/

mv2adb-2.0.1.80/conf/DBNAME.mv2adb.cfg

mv2adb-2.0.1.80/utils/

mv2adb-2.0.1.80/utils/install_adb_advisor.sql

mv2adb-2.0.1.80/lib/

mv2adb-2.0.1.80/lib/MV2ADB_Queries.pm

mv2adb-2.0.1.80/lib/MV2ADB_LoggingAndTracing.pm

mv2adb-2.0.1.80/lib/MV2ADB_passwd.jar

mv2adb-2.0.1.80/lib/MV2ADB_Utils.pm

mv2adb-2.0.1.80/lib/MV2ADB_PFile.pm

-bash-4.2$ ls -l

total 156

drwxrwxr-x. 2 opc opc     31 Feb  5 13:02 conf

drwxrwxr-x. 2 opc opc   4096 Feb  5 13:02 lib

-rwxrwxr-x. 1 opc opc 154609 Feb  5 13:02 mv2adb

drwxrwxr-x. 2 opc opc     37 Feb  5 13:02 utils

Install Oracle Instant Client on OCI Classic Compute Node

Download and copy files to OCI Classic compute node

[opc@UATPortal mv2adb]$ sudo su – oracle

[oracle@UATPortal app]$ mkdir ociclient

[oracle@UATPortal app]$ exit

logout

[opc@UATPortal ~]$ sudo su –

[root@UATPortal ~]# cd /home/opc

[root@UATPortal opc]# cp instantclient-basic-linux.x64-18.5.0.0.0dbru.zip /u01/app/ociclient/

[root@UATPortal opc]# cp instantclient-sqlplus-linux.x64-18.5.0.0.0dbru.zip /u01/app/ociclient/

[root@UATPortal opc]# cp  instantclient-tools-linux.x64-18.5.0.0.0dbru.zip /u01/app/ociclient/

[root@UATPortal opc]# cd /u01/app/ociclient/

[root@UATPortal ociclient]# chown oracle:oinstall *.zip

[root@SITOIC01db ociclient]# su – oracle

[oracle@SITOIC01db ~]$ cd /u01/app/ociclient/

[oracle@SITOIC01db ociclient]$ ls -l

total 148584

-rw——- 1 oracle oinstall 72902980 Mar 22 12:06 instantclient-basic-linux.x64-18.5.0.0.0dbru.zip

-rw——- 1 oracle oinstall   918469 Mar 22 12:05 instantclient-sqlplus-linux.x64-18.5.0.0.0dbru.zip

–rw——- 1 oracle oinstall  1143747 Mar 22 12:06 instantclient-tools-linux.x64-18.5.0.0.0dbru.zip

[oracle@SITOIC01db ociclient]$ unzip instantclient-tools-linux.x64-18.5.0.0.0dbru.zip

Archive:  instantclient-tools-linux.x64-18.5.0.0.0dbru.zip

  inflating: instantclient_18_5/exp

  inflating: instantclient_18_5/expdp

  inflating: instantclient_18_5/imp

  inflating: instantclient_18_5/impdp

  inflating: instantclient_18_5/libnfsodm18.so

  inflating: instantclient_18_5/sqlldr

  inflating: instantclient_18_5/TOOLS_LICENSE

  inflating: instantclient_18_5/TOOLS_README

  inflating: instantclient_18_5/wrc

[oracle@SITOIC01db ociclient]$ unzip instantclient-sqlplus-linux.x64-18.5.0.0.0dbru.zip

Archive:  instantclient-sqlplus-linux.x64-18.5.0.0.0dbru.zip

  inflating: instantclient_18_5/glogin.sql

  inflating: instantclient_18_5/libsqlplusic.so

  inflating: instantclient_18_5/libsqlplus.so

  inflating: instantclient_18_5/sqlplus

  inflating: instantclient_18_5/SQLPLUS_LICENSE

  inflating: instantclient_18_5/SQLPLUS_README

[oracle@SITOIC01db ociclient]$ unzip instantclient-basic-linux.x64-18.5.0.0.0dbru.zip

Archive:  instantclient-basic-linux.x64-18.5.0.0.0dbru.zip

  inflating: instantclient_18_5/adrci

  inflating: instantclient_18_5/BASIC_LICENSE

  inflating: instantclient_18_5/BASIC_README

  inflating: instantclient_18_5/genezi

  inflating: instantclient_18_5/libclntshcore.so.18.1

    linking: instantclient_18_5/libclntsh.so  -> libclntsh.so.18.1

  inflating: instantclient_18_5/libclntsh.so.18.1

  inflating: instantclient_18_5/libipc1.so

  inflating: instantclient_18_5/libmql1.so

  inflating: instantclient_18_5/libnnz18.so

    linking: instantclient_18_5/libocci.so  -> libocci.so.18.1

  inflating: instantclient_18_5/libocci.so.18.1

  inflating: instantclient_18_5/libociei.so

  inflating: instantclient_18_5/libocijdbc18.so

  inflating: instantclient_18_5/libons.so

  inflating: instantclient_18_5/liboramysql18.so

   creating: instantclient_18_5/network/

  inflating: instantclient_18_5/ojdbc8.jar

  inflating: instantclient_18_5/ucp.jar

  inflating: instantclient_18_5/uidrvci

  inflating: instantclient_18_5/xstreams.jar

   creating: instantclient_18_5/network/admin/

  inflating: instantclient_18_5/network/admin/README

finishing deferred symbolic links:

  instantclient_18_5/libclntsh.so -> libclntsh.so.18.1

  instantclient_18_5/libocci.so -> libocci.so.18.1

Create the mv2adb Configuration File

[root@SITOIC01db conf]# cat mv2adb.conf

#—————————————————#

# Source DB Parameters                            #

#—————————————————#

DB_CONSTRING=//10.0.3.8/pdb1.602302376.oraclecloud.internal

SYSTEM_DB_PASSWORD=70B9236DC3812800F108B3F8B7FB2886

SCHEMAS=TEST_MIG

DUMP_NAME=exp_test_mig.dmp

DUMP_PATH=/tmp

DUMP_FILES=/tmp/exp_test_mig.dmp

OHOME=/u01/app/oracle/product/12.1.0/dbhome_1

ICHOME=/u01/app/ociclient/instantclient_18_5

# ADB Properties #

#——————————————#

ADB_NAME=payments

ADB_PASSWORD=92FB814DE0A2749AAD7A801FD4106C2327832EAF5ADFA3CEF485BF91982A9455

ADB_CFILE=/tmp/payment_wallet.zip

ADB_CORES=1

#——————————————#

# Object Store Properties #

#——————————————#

OCI_HOST=https://swiftobjectstorage.ap-sydney-1.oraclecloud.com

OCI_NAMESPACE=coexservices01

OCI_BUCKET=migration_data

OCI_ID=automator

OCI_PASSWORD=A60749EE4206FA82CB68D35EA7F1D501AB0031BB2AC68131A718885B87320BFE

#

#—————————————————#

# End Of File #

#—————————————————#

Execute mv2adb auto command

[opc@SITOIC01db ~]$ sudo su –

[root@SITOIC01db ~]# export MV2ADB_HOME=/opt/mv2adb-2.0.1.80

[root@SITOIC01db ~]# cd $MV2ADB_HOME

[root@SITOIC01db mv2adb-2.0.1.80]# ./mv2adb auto -conf conf/mv2adb.conf

INFO: 2020-03-24 18:29:35: Please check the logfile ‘/opt/mv2adb-2.0.1.80/out/log/mv2adb_15326.log’ for more details

——————————————————–

mv2adb – Move data to Oracle Autonomous Database

Version: 2.0.1-80

Copyright (c) 1982-2019 Oracle and/or its affiliates.

——————————————————–

Author: Ruggero Citton <ruggero.citton@oracle.com>

RAC Pack, Cloud Innovation and Solution Engineering Team

——————————————————–

INFO: 2020-03-24 18:29:35: Reading the configuration file ‘conf/mv2adb.conf’

INFO: 2020-03-24 18:29:38: Checking schemas on source DB

INFO: 2020-03-24 18:29:38: Performing schema expdp for ‘TEST_MIG’ from source DB…

INFO: 2020-03-24 18:29:38: Step 1 – …ADB parallelism set by parameter

INFO: 2020-03-24 18:29:38: Step 2 – …getting source DB version

INFO: 2020-03-24 18:29:39: Step 3 – …creating expdp directory ‘MV2ADB_EXPDP_DIR’ for path ‘/tmp’

INFO: 2020-03-24 18:29:39: Step 4 – …getting latest SCN

INFO: 2020-03-24 18:29:40: Step 5 – …checking Cloud Service Type

INFO: 2020-03-24 18:29:40: Step 6 – …executing export datapump

SUCCESS: 2020-03-24 18:29:55: Expdp executed successfully

INFO: 2020-03-24 18:29:55: Performing ‘1’ dump upload to Oracle Object Store

INFO: 2020-03-24 18:29:55: …loading ‘/tmp/exp_test_mig_01.dmp’ into bucket ‘migration_data’

SUCCESS: 2020-03-24 18:29:56: …file ‘/tmp/exp_test_mig_01.dmp’ uploaded on ‘migration_data’ successfully

SUCCESS: 2020-03-24 18:29:56: Upload of ‘1’ dumps over Oracle Object Store complete successfully

INFO: 2020-03-24 18:29:56: Performing impdp into ADB

INFO: 2020-03-24 18:29:56: Step 1 – …drop Object Store Credential

INFO: 2020-03-24 18:29:56: Step 2 – …creating Object Store Credential

INFO: 2020-03-24 18:29:57: Step 3 – …executing import datapump to ADB

INFO: 2020-03-24 18:31:36: Moving impdp log ‘mv2adb_impdp_20200324-182957.log’ to Object Store

SUCCESS: 2020-03-24 18:31:37: Impdp to ADB ‘payments’ executed successfully

INFO: 2020-03-24 18:31:37: Recompiling schemas on ADB ‘payments_high’…

INFO: 2020-03-24 18:31:37: …recompiling schema ‘TEST_MIG’, it may get some time…

INFO: 2020-03-24 18:31:39: Recompiling schemas on ADB ‘payments_high’…

INFO: 2020-03-24 18:31:39: …recompiling schema ‘TEST_MIG’, it may get some time…

Review the steps performed via the mv2adb log file

Export …

2020-03-24 18:29:38: INFO:  Performing schema expdp for ‘TEST_MIG’ from source DB…

2020-03-24 18:29:38: Unzipping ADB credential file

2020-03-24 18:29:38: Executing cmd: unzip -o /tmp/payment_wallet.zip -d /opt/mv2adb-2.0.1.80/out/wlt

2020-03-24 18:29:38: Command output:

>  Archive:  /tmp/payment_wallet.zip

>    inflating: /opt/mv2adb-2.0.1.80/out/wlt/cwallet.sso

>    inflating: /opt/mv2adb-2.0.1.80/out/wlt/readme.md

>    inflating: /opt/mv2adb-2.0.1.80/out/wlt/tnsnames.ora

>    inflating: /opt/mv2adb-2.0.1.80/out/wlt/truststore.jks

>    inflating: /opt/mv2adb-2.0.1.80/out/wlt/ojdbc.properties

>    inflating: /opt/mv2adb-2.0.1.80/out/wlt/sqlnet.ora

>    inflating: /opt/mv2adb-2.0.1.80/out/wlt/ewallet.p12

>    inflating: /opt/mv2adb-2.0.1.80/out/wlt/keystore.jks   ,

>End Command output

2020-03-24 18:29:38: Setting up sqlnet.ora

2020-03-24 18:29:38: Executing command:

export ORACLE_HOME=/u01/app/ociclient/instantclient_18_5; export LD_LIBRARY_PATH=/u01/app/ociclient/instantclient_18_5/lib:/u01/app/ociclient/instantclient_18_5; export TNS_ADMIN=/opt/mv2adb-2.0.1.80/out/wlt; /u01/app/ociclient/instantclient_18_5/sqlplus -v |grep Release

2020-03-24 18:29:38: Executing cmd: export ORACLE_HOME=/u01/app/ociclient/instantclient_18_5; export LD_LIBRARY_PATH=/u01/app/ociclient/instantclient_18_5/lib:/u01/app/ociclient/instantclient_18_5; export TNS_ADMIN=/opt/mv2adb-2.0.1.80/out/wlt; /u01/app/ociclient/instantclient_18_5/sqlplus -v |grep Release

2020-03-24 18:29:38: Command output:

>  SQL*Plus: Release 18.0.0.0.0 – Production ,

>End Command output

2020-03-24 18:29:39: INFO:  Step 3 – …creating expdp directory ‘MV2ADB_EXPDP_DIR’ for path ‘/tmp’

2020-03-24 18:29:39: Creating expdpdirectory

2020-03-24 18:29:39: Will be running following remote sql statements as user: oracle:

        export ORACLE_HOME=/u01/app/ociclient/instantclient_18_5; export LD_LIBRARY_PATH=/u01/app/ociclient/instantclient_18_5/lib:/u01/app/ociclient/instantclient_18_5; export TNS_ADMIN=/opt/mv2adb-2.0.1.80/out/wlt;

        /u01/app/ociclient/instantclient_18_5/sqlplus -L system/password@//10.0.3.2/pdb1.602302376.oraclecloud.internal

        set heading off

set echo off

set feedback off

2020-03-24 18:29:39: INFO:  Step 4 – …getting latest SCN

2020-03-24 18:29:39: Getting latest SCN

2020-03-24 18:29:39: Will be running following remote sql statements as user: oracle:

        export ORACLE_HOME=/u01/app/ociclient/instantclient_18_5; export LD_LIBRARY_PATH=/u01/app/ociclient/instantclient_18_5/lib:/u01/app/ociclient/instantclient_18_5; export TNS_ADMIN=/opt/mv2adb-2.0.1.80/out/wlt;

        /u01/app/ociclient/instantclient_18_5/sqlplus -L system/password@//10.0.3.2/pdb1.602302376.oraclecloud.internal

        set heading off

set echo off

set feedback off

SELECT ‘:SQL:’||CURRENT_SCN||’:SQL:’ FROM V$DATABASE

2020-03-24 18:29:40: Executing: /u01/app/oracle/product/12.1.0/dbhome_1/bin/expdp system/***********@//10.0.3.2/pdb1.602302376.oraclecloud.internal SCHEMAS=TEST_MIG VERSION=12.1.0.2.0 DIRECTORY=MV2ADB_EXPDP_DIR DUMPFILE=exp_test_mig_%u.dmp PARALLEL=1 REUSE_DUMPFILES=y EXCLUDE=cluster,db_link LOGFILE=mv2adb_expdp.log FLASHBACK_SCN=270379005

2020-03-24 18:29:40: INFO:  Step 6 – …executing export datapump

2020-03-24 18:29:55: Command output:

>  Export: Release 12.1.0.2.0 – Production on Tue Mar 24 18:29:40 2020

>  Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

>  Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 – 64bit Production

>  FLASHBACK automatically enabled to preserve database integrity.

>  Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″:  system/********@//10.0.3.2/pdb1.602302376.oraclecloud.internal SCHEMAS=TEST_MIG VERSION=12.1.0.2.0 DIRECTORY=MV2ADB_EXPDP_DIR DUMPFILE=exp_test_mig_%u.dmp PARALLEL=1 REUSE_DUMPFILES=y EXCLUDE=cluster,db_link LOGFILE=mv2adb_expdp.log FLASHBACK_SCN=270379005

>  Estimate in progress using BLOCKS method…

>  Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

>  Total estimation using BLOCKS method: 2 MB

>  Processing object type SCHEMA_EXPORT/USER

>  Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

>  Processing object type SCHEMA_EXPORT/ROLE_GRANT

>  Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

>  Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

>  >>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>  >>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>  >>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>  >>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>  >>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>  >>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

>  Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE

>  Processing object type SCHEMA_EXPORT/TABLE/TABLE

>  Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

>  Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

>  Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

>  Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

>  >>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>  >>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>  >>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>  >>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>  >>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>  >>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

>  . . exported “TEST_MIG”.”MYTABLES”                       1.174 MB    4361 rows

>  Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

>  ******************************************************************************

>  Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

>    /tmp/exp_test_mig_01.dmp

>  Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Mar 24 18:29:54 2020 elapsed 0 00:00:13

Copying file to Object Store …

2020-03-24 18:29:55: INFO:  Performing ‘1’ dump upload to Oracle Object Store…

2020-03-24 18:29:55: INFO:  …loading ‘/tmp/exp_test_mig_01.dmp’ into bucket ‘migration_data’

2020-03-24 18:29:55: put_object

2020-03-24 18:29:55: Executing command:

curl -v -X PUT -u ‘automator:***********************’ –upload-file /tmp/exp_test_mig_01.dmp https://swiftobjectstorage.ap-sydney-1.oraclecloud.com/v1/coexservices01/migration_data/exp_test_mig_01.dmp

2020-03-24 18:29:56: Output is :

2020-03-24 18:29:56: INFO:  Step 2 – …creating Object Store Credential

2020-03-24 18:29:56: Setting-up Object Store credential

2020-03-24 18:29:56: Will be running following remote sql statements as user: oracle:

        export ORACLE_HOME=/u01/app/ociclient/instantclient_18_5; export LD_LIBRARY_PATH=/u01/app/ociclient/instantclient_18_5/lib:/u01/app/ociclient/instantclient_18_5; export TNS_ADMIN=/opt/mv2adb-2.0.1.80/out/wlt;

        /u01/app/ociclient/instantclient_18_5/sqlplus -L admin/password@payments_high

        set heading off

set echo off

set feedback off

exec DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => ‘MV2ADB_CRED_NAME’,username => ‘automator’,password => ‘********’)

Import …

2020-03-24 18:29:57: Executing: /u01/app/ociclient/instantclient_18_5/impdp admin/***********@payments_high CREDENTIAL=MV2ADB_CRED_NAME DUMPFILE=’https://swiftobjectstorage.ap-sydney-1.oraclecloud.com/v1/coexservices01/migration_data/exp_test_mig_01.dmp’ PARALLEL=1 TRANSFORM=segment_attributes:n TRANSFORM=dwcs_cvt_iots:y TRANSFORM=constraint_use_default_index:y EXCLUDE=cluster,db_link REMAP_TABLESPACE=%:data DIRECTORY=data_pump_dir LOGFILE=mv2adb_impdp_20200324-182957.log

2020-03-24 18:29:57: INFO:  Step 3 – …executing import datapump to ADB

2020-03-24 18:31:36: Command output:

>  Import: Release 18.0.0.0.0 – Production on Tue Mar 24 18:29:57 2020

>  Version 18.5.0.0.0

>  Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

>  Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production

>  Master table “ADMIN”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded

>  Starting “ADMIN”.”SYS_IMPORT_FULL_01″:  “admin/********@payments_high” CREDENTIAL=MV2ADB_CRED_NAME DUMPFILE=https://swiftobjectstorage.ap-sydney-1.oraclecloud.com/v1/coexservices01/migration_data/exp_test_mig_01.dmp PARALLEL=1 TRANSFORM=segment_attributes:n TRANSFORM=dwcs_cvt_iots:y TRANSFORM=constraint_use_default_index:y EXCLUDE=cluster,db_link REMAP_TABLESPACE=%:data DIRECTORY=data_pump_dir LOGFILE=mv2adb_impdp_20200324-182957.log

>  Processing object type SCHEMA_EXPORT/USER

>  Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

>  Processing object type SCHEMA_EXPORT/ROLE_GRANT

>  Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

>  Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

>  Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE

>  Processing object type SCHEMA_EXPORT/TABLE/TABLE

>  Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

>  . . imported “TEST_MIG”.”MYTABLES”                       1.174 MB    4361 rows

>  Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

>  Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

>  Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

>  Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

>  Job “ADMIN”.”SYS_IMPORT_FULL_01″ successfully completed at Tue Mar 24 07:31:33 2020 elapsed 0 00:01:35

>   ,

2020-03-24 18:31:37: Output is :

2020-03-24 18:31:37: SUCCESS:  Impdp to ADB ‘payments’ executed successfully

2020-03-24 18:31:37: INFO:  Recompiling schemas on ADB ‘payments_high’…

2020-03-24 18:31:37: INFO:  …recompiling schema ‘TEST_MIG’, it may get some time…

2020-03-24 18:31:37: Executing DBMS_UTILITY.COMPILE_SCHEMA for schema ‘TEST_MIG’

2020-03-24 18:31:37: Will be running following remote sql statements as user: oracle:

        export ORACLE_HOME=/u01/app/ociclient/instantclient_18_5; export LD_LIBRARY_PATH=/u01/app/ociclient/instantclient_18_5/lib:/u01/app/ociclient/instantclient_18_5; export TNS_ADMIN=/opt/mv2adb-2.0.1.80/out/wlt;

        /u01/app/ociclient/instantclient_18_5/sqlplus -L admin/password@payments_high

        set heading off

set echo off

set feedback off

EXEC DBMS_UTILITY.COMPILE_SCHEMA(SCHEMA => ‘TEST_MIG’, COMPILE_ALL => FALSE)

Start the Replicat based on the Flashback SCN noted from the mv2adb log

GGSCI (ogg19cora) 1> start replicat rep1 aftercsn 270379005

Sending START request to MANAGER …

REPLICAT REP1 starting

GGSCI (ogg19cora) 7> send rep1 status

Sending STATUS request to REPLICAT REP1 …

  Current status: At EOF

  Sequence #: 0

  RBA: 259,584,289

  3,570 records skipped in current transaction.

GGSCI (ogg19cora) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EXT1        unknown       00:00:04

REPLICAT    RUNNING     REP1        00:00:00      00:00:09

Please follow and like us:

Last Update: June 7, 2020  

April 19, 2020 147 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , , , ,

One thought on “Migrate data in real-time from OCI Classic to OCI using MV2ADB and Oracle Cloud Marketplace GoldenGate

  1. Siva

    Hi gavin,
    Just want to ask that, no specification for credential file path of source db on mv2adb.conf file, is it not needed? Only system_db_password parameter is specifed

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?