Hello guys
I had many questions from my clients regarding ‘what is the simplest way to migrate Oracle database 11g to Pluggable database 12c’. I should say that this article is focused on the simplest way, not necessarily the best way or the lowest-cost way. so keep reading to show you within a scenario.
Existing setup:
1 2 3 4 5 6 7 8 | Oracle 11g: Hostname: RestoreDB SID: RCIS Schema:mas11g Oracle 12c: Hostname: oel7 SID: DB12C |
migrate Oracle database 11g to pluggable database 12c:
In the source host and database(11g):
Let’s assume the business needs all objects belonging to the schema ‘mas11g’ and we have to move them to our new PDB on Oracle 12c. In the first step we’ll create the schema and some objects within it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | [oracle@RestoreDB ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SYS@RCIS> def; DEFINE _CONNECT_IDENTIFIER = "RCIS" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR) DEFINE _EDITOR = "ed" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000400" (CHAR) SYS@RCIS> create tablespace mas11g; Tablespace created. SYS@RCIS> select ts#, name from v$tablespace where name='MAS11G'; TS# NAME ---------- ------------------------------ 9 MAS11G 1 rows selected. SYS@RCIS> select name from v$datafile where ts#=9; NAME -------------------------------------------------------------------------------- +DATA/rcis/datafile/mas11g.422.965556953 SYS@RCIS> create user mas11g account unlock identified by mas default tablespace mas11g temporary tablespace temp quota unlimited on mas11g; User created. SYS@RCIS> grant create session, create table, connect, create sequence to mas11g; Grant succeeded. SYS@RCIS> disc; @> conn mas11g/mas Connected. MAS11G@RCIS> create table masoud (c1 number primary key, c2 date); Table created. MAS11G@RCIS> insert into masoud values (1, sysdate); 1 row created. MAS11G@RCIS> insert into masoud values (2, sysdate); 1 row created. MAS11G@RCIS> commit; Commit complete. MAS11G@RCIS> create sequence mas_seq start with 3; Sequence created. |
Then we make a logical backup with the expdp command:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [oracle@RestoreDB ~]$ expdp schemas=mas11g dumpfile=mas11g.dmp logfile=mas11g.log Export: Release 11.2.0.4.0 - Production Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA schemas=mas11g dumpfile=mas11g.dmp logfile=mas11g.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB 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/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "MAS11G"."MASOUD" 5.437 KB 3 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/admin/RCIS/dpdump/mas11g.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed elapsed 0 00:00:15 |
Notice to the schemas switch in the expdp command, we use it to bring all objects belonging to that schema in our logical backup. For the final step on our source host, we copy the dump file to the destination host
1 | scp /u01/app/oracle/admin/RCIS/dpdump/mas11g.dmp oracle@oel7:/tmp |
In the destination host and the database(12c):
First, let’s take a look at the current setting of our 12c database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [oracle@oel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> def; DEFINE _CONNECT_IDENTIFIER = "db12c" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR) DEFINE _O_RELEASE = "1202000100" (CHAR) SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 MYPDB READ WRITE NO |
As shown in the top codes, there is one open PDB(mypdb) in our production database, which is used for another business. We intend to create new PDB for our concerned old version database and we’ve decided to NOT using OMF:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | SQL> select name from v$datafile where con_id=2; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/db12c/pdbseed/system01.dbf /u01/app/oracle/oradata/db12c/pdbseed/sysaux01.dbf /u01/app/oracle/oradata/db12c/pdbseed/undotbs01.dbf SQL> create pluggable database mas12c admin user pdb_mas identified by mas 2 file_name_convert=('/u01/app/oracle/oradata/db12c/pdbseed/','/u01/app/oracle/oradata/db12c/mas12c/'); Pluggable database created. SQL> select name from v$datafile where con_id=4; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/db12c/mas12c/system01.dbf /u01/app/oracle/oradata/db12c/mas12c/sysaux01.dbf /u01/app/oracle/oradata/db12c/mas12c/undotbs01.dbf SQL> alter pluggable database mas12c open; Pluggable database altered. SQL> sho pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 MYPDB READ WRITE NO 4 MAS12C READ WRITE NO SQL> select name from v$active_services; NAME ---------------------------------------------------------------- mas12c db12c SYS$BACKGROUND SYS$USERS db12cXDB mypdb 6 rows selected. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@oel7 ~]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel7)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Uptime 3 days 0 hr. 20 min. 40 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oel7/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel7)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oel7)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/db12c/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "62a5b72ff0115788e05380efa8c0a311" has 1 instance(s). Instance "db12c", status READY, has 1 handler(s) for this service... Service "62e227d3146a2b60e05380efa8c0bc3a" has 1 instance(s). Instance "db12c", status READY, has 1 handler(s) for this service... Service "db12c" has 1 instance(s). Instance "db12c", status READY, has 1 handler(s) for this service... Service "db12cXDB" has 1 instance(s). Instance "db12c", status READY, has 1 handler(s) for this service... Service "mas12c" has 1 instance(s). Instance "db12c", status READY, has 1 handler(s) for this service... Service "mypdb" has 1 instance(s). Instance "db12c", status READY, has 1 handler(s) for this service... The command completed successfully |
As you see in the previous codes, we’ve created a PDB named mas12c and it is ready to use. In my opinion, one of the best ways to connect to PDBs is using TNS. we are going to create a TNS for mas12c PDB to connect to the PDB trough it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | vi $ORACLE_HOME/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_DB12C = (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521)) DB12C = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db12c) ) ) MYPDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mypdb) ) ) # new masoud's pdb MAS12C = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mas12c) ) ) [oracle@oel7 ~]$ tnsping mas12c 3 TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mas12c))) OK (0 msec) OK (0 msec) OK (0 msec) [oracle@oel7 ~]$ sqlplus sys@mas12c as sysdba SQL*Plus: Release 12.2.0.1.0 Production Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> SQL> SQL> def; DEFINE _CONNECT_IDENTIFIER = "mas12c" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR) DEFINE _O_RELEASE = "1202000100" (CHAR) |
Well, we are now ready to import our dump to the mas12c PDB. First, we have to create or choose a directory for the IMPDP command to use. It’s worth mentioning there is a Globally Unique immutable ID (GUID) assigned to the PDBs in the creation time which OMF and default data pumps directory use it.
1 2 3 4 5 6 7 8 9 10 11 | SQL> select GUID from v$containers where name='MAS12C'; GUID -------------------------------- 62E227D3146A2B60E05380EFA8C0BC3A SQL> select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR'; DIRECTORY_PATH -------------------------------------------------------------------------------- /u01/app/oracle/admin/db12c/dpdump/62E227D3146A2B60E05380EFA8C0BC3A |
We would like to use default directory “DATA_PUMP_DIR” and it’s enough to make sure directory exist:
1 2 3 4 5 6 7 | SQL> ! mkdir -p /u01/app/oracle/admin/db12c/dpdump/62E227D3146A2B60E05380EFA8C0BC3A SQL> ! ls -ltr /u01/app/oracle/admin/db12c/dpdump/ total 4 -rw-r-----. 1 oracle oinstall 116 Jan 13 12:34 dp.log drwxr-x---. 2 oracle oinstall 6 Jan 14 09:33 62A5B72FF0115788E05380EFA8C0A311 drwxr-x---. 2 oracle oinstall 6 Jan 16 13:18 62E227D3146A2B60E05380EFA8C0BC3A |
Now let’s do import:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | [oracle@oel7 tmp]$ cp mas11g.dmp $ORACLE_BASE/admin/db12c/dpdump/62E227D3146A2B60E05380EFA8C0BC3A/ [oracle@oel7 tmp]$ sqlplus sys@mas12c as sysdba SQL*Plus: Release 12.2.0.1.0 Production Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> create tablespace mas12c datafile '/u01/app/oracle/oradata/db12c/mas12c/mas12c01.dbf' size 100m autoextend on; Tablespace created. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@oel7 tmp]$ impdp schemas=mas11g dumpfile=mas11g.dmp remap_tablespace=MAS11G:mas12c; Import: Release 12.2.0.1.0 - Production Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: sys@mas12c as sysdba Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_SCHEMA_01": sys/********@mas12c AS SYSDBA schemas=mas11g dumpfile=mas11g.dmp remap_tablespace=MAS11G:mas12c 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/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "MAS11G"."MASOUD" 5.437 KB 3 rows Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed elapsed 0 00:00:08 |
So far so good, it’s time to do some verifications:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | Alert log: ######### create pluggable database mas12c admin user pdb_mas identified by * file_name_convert=('/u01/app/oracle/oradata/db12c/pdbseed/','/u01/app/oracle/oradata/db12c/mas12c/') PDB$SEED(2): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated Warning: VKTM detected a forward time drift. Please see the VKTM trace file for more details: /u01/app/oracle/diag/rdbms/db12c/db12c/trace/db12c_vktm_23694.trc MAS12C(4):Endian type of dictionary set to little **************************************************************** Pluggable Database MAS12C with pdb id - 4 is created as UNUSABLE. If any errors are encountered before the pdb is marked as NEW, then the pdb must be dropped local undo-1, localundoscn-0x00000000000000e1 **************************************************************** MAS12C(4):Autotune of undo retention is turned on. MAS12C(4):[76640] Successfully onlined Undo Tablespace 2. MAS12C(4):Undo initialization finished serial:0 start:264185920 end:264186491 diff:571 ms (0.6 seconds) MAS12C(4):Database Characterset for MAS12C is AL32UTF8 MAS12C(4):JIT: pid 76640 requesting stop Completed: create pluggable database mas12c admin user pdb_mas identified by * file_name_convert=('/u01/app/oracle/oradata/db12c/pdbseed/','/u01/app/oracle/oradata/db12c/mas12c/') alter pluggable database mas12c open MAS12C(4):Autotune of undo retention is turned on. MAS12C(4):Endian type of dictionary set to little MAS12C(4):[76640] Successfully onlined Undo Tablespace 2. MAS12C(4):Undo initialization finished serial:0 start:264350366 end:264352556 diff:2190 ms (2.2 seconds) MAS12C(4):Deleting old file#5 from file$ MAS12C(4):Deleting old file#6 from file$ MAS12C(4):Deleting old file#8 from file$ MAS12C(4):Adding new file#13 to file$(old file#5) MAS12C(4):Adding new file#14 to file$(old file#6) MAS12C(4):Adding new file#15 to file$(old file#8) MAS12C(4):Successfully created internal service mas12c at open **************************************************************** Post plug operations are now complete. Pluggable database MAS12C with pdb id - 4 is now marked as NEW. **************************************************************** MAS12C(4):Database Characterset for MAS12C is AL32UTF8 MAS12C(4):Opatch validation is skipped for PDB MAS12C (con_id=0) MAS12C(4):Opening pdb with no Resource Manager plan active Pluggable database MAS12C opened read write Completed: alter pluggable database mas12c open MAS12C(4):Resize operation completed for file# 14, old size 337920K, new size 358400K Schema Objets: ######### [oracle@oel7 ~]$ sqlplus mas11g@mas12c SQL*Plus: Release 12.2.0.1.0 Production Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> def; DEFINE _CONNECT_IDENTIFIER = "mas12c" (CHAR) DEFINE _USER = "MAS11G" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR) DEFINE _O_RELEASE = "1202000100" (CHAR) SQL> select object_name, object_type from user_objects order by object_type; OBJECT_NAME OBJECT_TYPE ---------------------------------------- -------------------------------------------------- SYS_C007400 INDEX MAS_SEQ SEQUENCE MASOUD TABLE SQL> select segment_name, segment_type, tablespace_name from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ---------------------------------------- ---------------------------------------- ------------------------------ MASOUD TABLE MAS12C SYS_C007400 INDEX MAS12C SQL> select * from masoud; C1 C2 ---------- --------- 1 16-JAN-18 2 16-JAN-18 4 16-JAN-18 |
OK, it seems it’s done and our migration has completed successfully.
Thanks for your attention.
3 comments On The simplest way to migrate Oracle database 11g to pluggable database 12c
it is a good article , good luck!
thank you Siroos
Thanks for your efficient topic description.