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
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
1 Comments