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 [email protected]:/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! [email protected]'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 🙂