Migrating On-Premises Database 12cR2 to Oracle Cloud Database 18cR1

Migrating Oracle Database 12cR2 from On-Premises to Oracle Database 18cR1 on Oracle Cloud

As we’ve already done creating Oracle Database 18cR1 on Oracle Cloud in our previous post, now we’ll be migrating our On-Premises Database to our newly created Cloud Database from version 12cR2 to 18cR1.

There’re several methods of migrating On-Premises Database to Cloud Database, This is one of them in which we’ll be using expdp/impdp. So this will go like,

1.  Export On-Premises Database (DEMTEST) using expdp utility.

  • Before exporting, we’ll create Database Directory on some OS location. In my case I’m creating DEM_DIR directory at /u01/app/oracle/oradata/ location.
SQL> CREATE OR REPLACE DIRECTORY DEM_DIR AS '/u01/app/oracle/oradata/';

Directory created.
  • Once we’re done creating Database Directory, we’re good to export our database to the created directory. Let’s export it.
[oracle@dbnode1 oradata]$ expdp system/oradba786@DEMTEST full=Y directory=DEM_DIR dumpfile=DEM.dmp logfile=expdpDEM.log

Export: Release 12.2.0.1.0 - Production on Tue Mar 27 20:43:29 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_03": system/********@DEMTEST full=Y directory=DEM_DIR dumpfile=DEM.dmp logfile=expdpDEM.log
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 5.851 KB 23 rows
. . exported "SYSTEM"."REDO_DB" 25.58 KB 1 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.523 KB 14 rows
. . exported "SYS"."TSDP_SUBPOL$" 6.320 KB 1 rows
. . exported "SYS"."TSDP_PARAMETER$" 5.945 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 5.906 KB 1 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.945 KB 2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SYS"."NACL$_HOST_EXP" 6.906 KB 1 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.507 KB 12 rows
. . exported "DEM_USER"."MIG1" 50.62 MB 800000 rows
. . exported "DEM_USER"."MIG2" 50.62 MB 800000 rows
. . exported "SYSTEM"."SYS_EXPORT_FULL_01" 567.4 KB 2960 rows
. . exported "SYSTEM"."SYS_EXPORT_FULL_02" 570.5 KB 2970 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_03 is:
 /u01/app/oracle/oradata/DEM.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_03" successfully completed at Tue Mar 27 20:46:46 2018 elapsed 0 00:03:15
  • After the successful export, we’ll now login to cloud server.

2. Login to Cloud Server.

We’ll now login to our cloud server to create directory on Cloud with same structure as we had on On-Premises server.

[oracle@dbnode1 keys]$ ssh -i DEMCLOUD.ssh oracle@demunix-cloud
 [oracle@demunix-cloud ~]$ cd /u01/app/oracle/
 [oracle@demunix-cloud oracle]$ pwd
 /u01/app/oracle
 [oracle@demunix-cloud oracle]$ mkdir oradata
 [oracle@demunix-cloud oracle]$ cd oradata/
 [oracle@demunix-cloud oradata]$ pwd
 /u01/app/oracle/oradata

Now, we have directory with same structure on Cloud server as well. Let’s invoke SQLPlus on Cloud and create Database Directory with same name.

SQL> CREATE OR REPLACE DIRECTORY DEM_DIR AS '/u01/app/oracle/oradata/';

Directory created.
  • Now, we have Database directory as well. However we need to do little bit more before copying dump from the On-Premises server. As we’re using Import/Export to bring On-Premises Database to Cloud. We’ll need to create Database users on Cloud Database that we have in our On-Premises Database, so that impdp can import that user’s data to his schema on Cloud Database. So in our demonstration we have only one additional user i.e. DEM_USER in our On-Premises Database so we’ll create same user on Cloud Database and will grant him basic privileges.
SQL> create user DEM_USER identified by demunix;
 create user DEM_USER identified by demunix
 *
 ERROR at line 1:
 ORA-65096: invalid common user or role name

Oops! syntax for user creation is correct, still Oracle fetched error, why? So the answer is, As of now while create database on Oracle Cloud with Database Release 18c we’ll have to create database as a container database and then create a pluggable database within the container database. and oracle does not allow us to use container database as a primary database. as container database will only store metadata of pluggable databases. So to forcefully create user within container database execute below command.

SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.
  • And now try to create user DEM_USER using same command.
SQL> create user DEM_USER identified by demunix;

User created.
  • Cool, user is created now. Let’s grant him basic privileges.
SQL> grant connect, create session,create table to DEM_USER;

Grant succeeded.
  • So now we’re done creating user as well. Let’s now go ahead and copy dump of the database from On-Premises to Oracle Cloud, under same directory which is /u01/app/oracle/oradata/. We’ll copy it using scp.
[oracle@demunix-cloud ~]$ scp -r oracle@dbnode1.demunix.com:/u01/app/oracle/oradata/* /u01/app/oracle/oradata/
 reverse mapping checking getaddrinfo for pacenet.244.188.180.in-addr.arpa [dbnode1.demunix.com] failed - POSSIBLE BREAK-IN ATTEMPT!
 oracle@dbnode1.demunix.com's password:
 DEM.dmp                                                                   100% 105MB 1.1MB/s 01:39
 expdpDEM.log                                                              100% 6228 6.1KB/s 00:00
  • So we’ve now copied database dump from On-Premises to Oracle Cloud.

3. Import dump to Cloud Database (DEMCLOUD) using impdp utility.

  • Let’s now import the dump to the cloud Database.
[oracle@demunix-cloud ~]$ impdp system/oradba786@DEMCLOUD full=Y directory=DEM_DIR dumpfile=DEM.dmp logfile=expdpDEM.log

Import: Release 18.0.0.0.0 - Production on Tue Mar 27 13:39:52 2018
 Version 18.1.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

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
 import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
 export done in UTF8 character set and UTF8 NCHAR character set
 Warning: possible data loss in character set conversions
 Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@DEMCLOUD full=Y directory=DEM_DIR dumpfile=DEM.dmp logfile=expdpDEM.log
 Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/TABLESPACE
 ORA-31684: Object type TABLESPACE:"TEMP" already exists

ORA-31684: Object type TABLESPACE:"USERS" already exists

Processing object type DATABASE_EXPORT/PROFILE
 Processing object type DATABASE_EXPORT/SYS_USER/USER
 ORA-31685: Object type USER:"SYS" failed due to insufficient privileges. Failing sql is:
 ALTER USER "SYS" IDENTIFIED BY VALUES 'S:08DF98E3E117E24A25B22647C9CB859B918306D46C1194F02D1C3E8C0DE8;T:FA173FC50E49CD9AE62A0038A2DBDFBD30F02D8CA1FBB8AF90C94D428CB43909B144091AE2A8C8B0DF3EB942C7FBFE9A2FDBDBE7AC430F24FA632C9D1F7C9062AE6C1D8D6705D9A05CDABA2B0B9CA59D' TEMPORARY TABLESPACE "TEMP"

Processing object type DATABASE_EXPORT/SCHEMA/USER
 ORA-39083: Object type USER:"DEM_USER" failed to create with error:
 ORA-65096: invalid common user or role name

Failing sql is:
 CREATE USER "DEM_USER" IDENTIFIED BY VALUES 'S:DB2557CDE3EAB5A705602E4EA9FA4A0BA41EA43AC52FA1CDF6D20C880F89;T:B9D5C68C84F18F7A93031426AAD432FA297125E9A45F092D6428D77A060BDCA41B8A3C64F7E47A87109B28B38DA0705DC2BAC40F24FE4090115177C3CC64ABFE4ABCDD1FE4F035C2AA8E5DB3E46F5A62' DEFAULT TABLESPACE "DEMTB" TEMPORARY TABLESPACE "TEMP"

Processing object type DATABASE_EXPORT/RADM_FPTM
 Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
 Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
 Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
 Processing object type DATABASE_EXPORT/RESOURCE_COST
 Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
 Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
 ORA-31684: Object type DIRECTORY:"DEM_DIR" already exists

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
 >>> Cannot set an SCN larger than the current SCN. If a Streams Capture configuration was imported then the Apply that processes the captured messages needs to be dropped and recreated. See My Oracle Support article number 1380295.1.
 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
 ORA-39083: Object type PROCOBJ:"SYS"."DSS_CRITICAL_GROUP" failed to create with error:
 ORA-65046: operation not allowed from outside a pluggable database

Failing sql is:
 BEGIN
 declare error_num1 exception;PRAGMA EXCEPTION_INIT(error_num1, -29357);begin dbms_resource_manager.create_consumer_group(consumer_group => 'DSS_CRITICAL_GROUP', comment => 'Consumer group for critical DSS queries');exception when error_num1 then NULL;when others then raise; end;COMMIT; END;

ORA-39083: Object type PROCOBJ:"SYS"."DSS_GROUP" failed to create with error:
 ORA-65046: operation not allowed from outside a pluggable database

Failing sql is:
 BEGIN
 declare error_num1 exception;PRAGMA EXCEPTION_INIT(error_num1, -29357);begin dbms_resource_manager.create_consumer_group(consumer_group => 'DSS_GROUP', comment => 'Consumer group for DSS queries');exception when error_num1 then NULL;when others then raise; end;COMMIT; END;

ORA-39083: Object type PROCOBJ:"SYS"."ETL_GROUP" failed to create with error:
 ORA-65046: operation not allowed from outside a pluggable database

Failing sql is:
 BEGIN
 declare error_num1 exception;PRAGMA EXCEPTION_INIT(error_num1, -29357);begin dbms_resource_manager.create_consumer_group(consumer_group => 'ETL_GROUP', comment => 'Consumer group for ETL');exception when error_num1 then NULL;when others then raise; end;COMMIT; END;

ORA-39083: Object type PROCOBJ:"SYS"."LOW_GROUP" failed to create with error:
 ORA-65046: operation not allowed from outside a pluggable database

Failing sql is:
 BEGIN
 declare error_num1 exception;PRAGMA EXCEPTION_INIT(error_num1, -29357);begin dbms_resource_manager.create_consumer_group(consumer_group => 'LOW_GROUP', comment => 'Consumer group for low-priority sessions');exception when error_num1 then NULL;when others then raise; end;COMMIT; END;

ORA-39083: Object type PROCOBJ:"SYS"."INTERACTIVE_GROUP" failed to create with error:
 ORA-65046: operation not allowed from outside a pluggable database

Failing sql is:
 BEGIN
 declare error_num1 exception;PRAGMA EXCEPTION_INIT(error_num1, -29357);begin dbms_resource_manager.create_consumer_group(consumer_group => 'INTERACTIVE_GROUP', comment => 'Consumer group for interactive, OLTP operations');exception when error_num1 then NULL;when others then raise; end;COMMIT; END;

ORA-39083: Object type PROCOBJ:"SYS"."BATCH_GROUP" failed to create with error:
 ORA-65046: operation not allowed from outside a pluggable database

Failing sql is:
 BEGIN
 declare error_num1 exception;PRAGMA EXCEPTION_INIT(error_num1, -29357);begin dbms_resource_manager.create_consumer_group(consumer_group => 'BATCH_GROUP', comment => 'Consumer group for batch operations');exception when error_num1 then NULL;when others then raise; end;COMMIT; END;

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
 Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
 . . imported "SYS"."KU$_EXPORT_USER_MAP" 5.851 KB 23 rows
 Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
 . . imported "SYSTEM"."REDO_DB_TMP" 25.58 KB 1 rows
 . . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$" 6.523 KB 14 rows
 . . imported "SYS"."DP$TSDP_SUBPOL$" 6.320 KB 1 rows
 . . imported "SYS"."DP$TSDP_PARAMETER$" 5.945 KB 1 rows
 . . imported "SYS"."DP$TSDP_POLICY$" 5.906 KB 1 rows
 . . imported "SYS"."AMGT$DP$AUD$" 0 KB 0 rows
 . . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$" 0 KB 0 rows
 . . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$" 0 KB 0 rows
 . . imported "SYS"."DP$TSDP_ASSOCIATION$" 0 KB 0 rows
 . . imported "SYS"."DP$TSDP_CONDITION$" 0 KB 0 rows
 . . imported "SYS"."DP$TSDP_FEATURE_POLICY$" 0 KB 0 rows
 . . imported "SYS"."DP$TSDP_PROTECTION$" 0 KB 0 rows
 . . imported "SYS"."DP$TSDP_SENSITIVE_DATA$" 0 KB 0 rows
 . . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
 . . imported "SYS"."DP$TSDP_SOURCE$" 0 KB 0 rows
 . . imported "SYSTEM"."REDO_LOG_TMP" 0 KB 0 rows
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
 . . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT" 5.945 KB 2 rows
 . . imported "SYS"."DP$DBA_SENSITIVE_DATA" 0 KB 0 rows
 . . imported "SYS"."DP$DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
 . . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT" 0 KB 0 rows
 . . imported "SYS"."NACL$_ACE_IMP" 0 KB 0 rows
 . . imported "SYS"."NACL$_HOST_IMP" 6.906 KB 1 rows
 . . imported "SYS"."NACL$_WALLET_IMP" 0 KB 0 rows
 . . imported "SYS"."DATAPUMP$SQL$TEXT" 0 KB 0 rows
 . . imported "SYS"."DATAPUMP$SQL$" 0 KB 0 rows
 . . imported "SYS"."DATAPUMP$SQLOBJ$AUXDATA" 0 KB 0 rows
 . . imported "SYS"."DATAPUMP$SQLOBJ$DATA" 0 KB 0 rows
 . . imported "SYS"."DATAPUMP$SQLOBJ$PLAN" 0 KB 0 rows
 . . imported "SYS"."DATAPUMP$SQLOBJ$" 0 KB 0 rows
 . . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP" 0 KB 0 rows
 . . imported "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" 9.507 KB 12 rows
 Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
 . . imported "DEM_USER"."MIG1" 50.62 MB 800000 rows
 . . imported "DEM_USER"."MIG2" 50.62 MB 800000 rows
 . . imported "SYSTEM"."SYS_EXPORT_FULL_01" 567.4 KB 2960 rows
 . . imported "SYSTEM"."SYS_EXPORT_FULL_02" 570.5 KB 2970 rows
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
 Processing object type DATABASE_EXPORT/STATISTICS/MARKER
 Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
 Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
 Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
 Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
 Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 11 error(s) at Tue Mar 27 13:42:32 2018 elapsed 0 00:02:31

You might see some warning and errors while importing it, as those errors occurred because Oracle doesn’t allow some operation from the outside of Pluggable Database. So you may ignore those errors.

  • After the import, let’s invoke SQLPlus on Cloud Database check if we have all the tables and data from the On-Premises Database.
[oracle@demunix-cloud ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Tue Mar 27 13:46:08 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2017, Oracle. All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> select username from dba_users where username like 'DEM%';

USERNAME
--------------------------------------------------------------------------------
DEM_USER
  • Tables in DEM_USER’s schema.
 SQL> select table_name from dba_tables where owner like 'DEM%';

TABLE_NAME
 --------------------------------------------------------------------------------
 MIG1
 MIG2
  • Count of records within the tables.
 SQL> select count(*) from DEM_USER.MIG1;

COUNT(*)
 ----------
 800000

SQL> select count(*) from DEM_USER.MIG2;

COUNT(*)
 ----------
 800000
  • And hostname.
SQL> select INSTANCE_NAME,HOST_NAME from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ------------------
DEMCLOUD         demunix-cloud
  • Let’s now check which tables were there in user DEM_USER’s schema and what was the counts of records within those tables. Let’s invoke SQLPlus on On-Premises Database and cross check the above info.
[oracle@dbnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 27 21:03:10 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select username from dba_users where username like 'DEM%';

USERNAME
--------------------------------------------------------------------------------
DEM_USER
  • Tables in DEM_USER’s schema.
SQL> select table_name from dba_tables where owner like 'DEM%';

TABLE_NAME
--------------------------------------------------------------------------------
MIG2
MIG1
  • Count of records within the tables.
SQL> select count(*) from DEM_USER.MIG1;

COUNT(*)
----------
 800000

SQL> select count(*) from DEM_USER.MIG2;

COUNT(*)
----------
 800000
  • And hostname.
SQL> select INSTANCE_NAME,HOST_NAME from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- -----------------------------------
DEMTEST          dbnode1.demunix.com

Cheers! everything got matched. let’s see tables of the users are in which tablespace.

  • In On-Premises Database 12cR1

Tables and their default tablespace.

SQL> select TABLE_NAME,TABLESPACE_NAME from DBA_TABLES where OWNER like 'DEM%';

TABLE_NAME                TABLESPACE_NAME
------------------------- ------------------------------
MIG2                      DEMTB
MIG1                      DEMTB

And Default tablespace of user DEM_USER.

SQL> select USERNAME,DEFAULT_TABLESPACE from DBA_USERS where USERNAME like 'DEM%';

USERNAME                  DEFAULT_TABLESPACE
------------------------- -------------------------
DEM_USER                  DEMTB
  • In Cloud Database 18cR1.

Tables and their default tablespace.

SQL> select TABLE_NAME,TABLESPACE_NAME from DBA_TABLES where OWNER like 'DEM%';

TABLE_NAME                TABLESPACE_NAME
------------------------- ------------------------------
MIG1                      DEMTB
MIG2                      DEMTB

And Default tablespace of user DEM_USER.

SQL> select USERNAME,DEFAULT_TABLESPACE from DBA_USERS where USERNAME like 'DEM%';

USERNAME        DEFAULT_TABLESPACE
--------------- ------------------------------
DEM_USER        USERS

The default tablespace of the user after import got changed to USERS tablespace. so we just need to alter the user and change the default tablespace of the user DEM_USER.

SQL> alter user dem_user default tablespace DEMTB quota unlimited on DEMTB;

User altered.

Now, let’s check again.

SQL> select USERNAME,DEFAULT_TABLESPACE from DBA_USERS where USERNAME like 'DEM%';

USERNAME                  DEFAULT_TABLESPACE
------------------------- -------------------------
DEM_USER                  DEMTB

Now the default tablespace of DEM_USER is also changed to DEMTB as it was on Source On-Premises Database.

Cheers, we’ve done migration of On-Premises Database from version 12cR1 to Oracle Cloud Database 18cR1.

 

Peace 🙂