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

Posted by Gavin Soorma
On April 19, 2020
1 Comments

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

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

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

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:

/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:

/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

> 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:

/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

> 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:

/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

News

1 Comments

Leave Reply

Oracle GoldenGate on Oracle Cloud Marketplace (Part 1)

Data migration to Autonomous Database using MV2ADB