When upgrading to 12cR1 (pluggable and non-CDB) database we can choose between the following methods:
1) Upgrade the pre-12cR1 database to 12cR1 using DBUA or command-line/upgrade script (catctl.pl); for some releases a Direct Upgrade to 12cR1 is possible (10.2.0.5, 11.1.0.7 and >=11.2.0.2). Notice that in 12cR1 the upgrade can now run in parallel to speed-up the process. If the target database is a pluggable database, then simply plug-in the upgraded non-CDB database into an existing CDB (if no restrictions/limitations exist).
2) Use export/import, either Data Pump, possibly in combination with Transportable Tablespaces (TTS), or use the classical “exp/imp” utilities. If the database to be upgraded is at least 11.2.0.3 (and Enterprise Edition), then one option could be to perform Data Pump Full Transportable Export/Import. For example on the 12cR1 target-database simply perform the import using the parameters NETWORK_LINK=, FULL=Y, TRANSPORTABLE=ALWAYS, TRANSPORT_DATAFILES= and for pre-12cR1 source database specify VERSION=12.
3) Use GoldenGate replication.
Setup Golden Gate on the pre-12c database and the 12cR1 target-database (either non-CDB or CDB) and start the data replication.
When the 12cR1 database has catched-up with the pre-12c database, you can perform fail-over.
The options 1 and 2 will result in some downtime, for option 2 this can be of a longer period of time depending of the size of the database to export/import.
For option 3 the downtime can be eliminated for the database tier.
In the following we’ll take a look at option 2, where we’ll upgrade an 11.2.0.3 database (“orcl”) to a 12cR1 pluggable database (“LBV11203” in the CDB “CDB121A”). We’ll demonstrate the Data Pump Full Transportable Export/Import method and we’ll simply initiate the “impdp” using NETWORK_LINK on the 12cR1 database “LBV11203”.
First on the 11.2.0.3 “orcl” source-database perform the following steps from SQL*Plus (we’ll be using the SYSTEM user for the export/import):
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 2 17:54:55 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning option
SQL> grant DATAPUMP_EXP_FULL_DATABASE to system;
Grant succeeded.
SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
/u02/oradata/orcl/users_01.dbf
/u02/oradata/orcl/undotbs_01.dbf
/u02/oradata/orcl/sysaux_01.dbf
/u02/oradata/orcl/system_01.dbf
/u02/oradata/orcl/users2_01.dbf
We’re going to transfer the tablespaces USERS and USERS2.
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
Now place the tablespaces USERS and USERS2 in read-only mode:
SQL> conn / as sysdba
SQL> alter tablespace users read only;
Tablespace altered.
SQL> alter tablespace users2 read only;
Tablespace altered.
NOTICE: If DB instance is started READ ONLY, then the following error occurrs during “impdp”:
ORA-02047: cannot join the distributed transaction in progress
Now prepare the pluggable target-database “LBV11203” in the “CDB121A” database.
Modify Oracle Net (tnsnames.ora) to contain source database “orcl” and verify you can pin/connect to it from the 12cR1 environment.
Make new PDB called “LBV11203” to hold the 11.2.0.3 database, we’ll clone it from en existing PDB, namely “LBV01”, so we connect to SQL*Plus as SYSDBA:
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Dec 2 18:02:07 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select name, open_mode, con_id from v$pdbs;
NAME OPEN_MODE CON_ID
—————————— ———- ———-
PDB$SEED READ ONLY 2
PDB MOUNTED 3
LBV01 READ WRITE 4
LBV02 MOUNTED 5
SQL> alter pluggable database LBV01 close;
Pluggable database altered.
SQL> alter pluggable database LBV01 open read only;
Pluggable database altered.
SQL> alter system set db_create_file_dest=’/u02/app/oracle/oradata’ scope=both;
System altered.
SQL> create pluggable database LBV11203 from LBV01;
Pluggable database created.
SQL> alter pluggable database LBV01 close;
Pluggable database altered.
SQL> select name, open_mode, con_id from v$pdbs;
NAME OPEN_MODE CON_ID
—————————— ———- ———-
PDB$SEED READ ONLY 2
PDB MOUNTED 3
LBV01 MOUNTED 4
LBV02 MOUNTED 5
LBV11203 MOUNTED 6
Now let’s connect to the freshly created PDB “LBV11203” and perform startup using SQL*Plus (after the clone the new database is not started/OPEN):
sqlplus sys@LBV11203 as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Dec 2 18:18:30 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter database open;
Database altered.
SQL> select name, open_mode, con_id from v$pdbs;
NAME OPEN_MODE CON_ID
—————————— ———- ———-
LBV11203 READ WRITE 6
SQL> grant DATAPUMP_IMP_FULL_DATABASE to SYSTEM;
Grant succeeded.
SQL> CREATE OR REPLACE DIRECTORY FULL_TRANSPORT_DIR as ‘/u02/datapump’;
Directory created.
SQL> grant read,write on directory FULL_TRANSPORT_DIR to DATAPUMP_IMP_FULL_DATABASE;
Grant succeeded.
SQL> create public database link ORCL connect to system identified by manager using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=orcl)))’;
SQL> select username from dba_users order by 1;
USERNAME
——————————————————————————–
ANONYMOUS
APEX_040200
APEX_PUBLIC_USER
APPQOSSYS
AUDSYS
CTXSYS
DBSNMP
DIP
DVF
DVSYS
FLOWS_FILES
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
LBACSYS
LBV01DBA
MDDATA
MDSYS
OJVMSYS
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
SYS
SYSBACKUP
SYSDG
SYSKM
SYSTEM
WMSYS
XDB
XS$NULL
36 rows selected.
As we see there’s no SCOTT user present in “LBV11203”.
Let’s connect to the PDB and test the database link we’ve created:
sqlplus system/manager@LBV11203
SQL*Plus: Release 12.1.0.1.0 Production on Thu Nov 28 15:12:39 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_name
CON_NAME
——————————
LBV11203
SQL> show user
USER is “SYSTEM”
SQL> select * from v$instance@orcl;
INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
linux-4
11.2.0.3.0 28-NOV-13 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
It works fine – from the 12cR1 pluggable database “LBV11203” we can access the 11.2.0.3 database “orcl”.
Now we copy the tablespace datafiles for USERS and USERS2 tablespaces to the 12cR1 database location (/u02/app/oracle/oradata/LBV11203):
cp /u02/oradata/orcl/users_01.dbf /u02/app/oracle/oradata/LBV11203
cp /u02/oradata/orcl/users2_01.dbf /u02/app/oracle/oradata/LBV11203
We’re now ready to start the “impdp” to begin transporting the data accross the NETWORK_LINK.
Notice that the impdp parameter TRANSPORT_DATAFILES contains the datafile-names fully qualified for the new location in the 12cR1 database “LBV11203”.
In the 12cR1 environment we start “impdp” as follows:
impdp system/manager@LBV11203 FULL=Y VERSION=12 TRANSPORTABLE=ALWAYS DIRECTORY=FULL_TRANSPORT_DIR TRANSPORT_DATAFILES=/u02/app/oracle/oradata/LBV11203/users_01.dbf,/u02/app/oracle/oradata/LBV11203/users2_01.dbf NETWORK_LINK=orcl
Import: Release 12.1.0.1.0 – Production on Fri Nov 29 15:47:46 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/********@LBV11203 FULL=Y VERSION=12 TRANSPORTABLE=ALWAYS DIRECTORY=FULL_TRANSPORT_DIR TRANSPORT_DATAFILES=/u02/app/oracle/oradata/LBV11203/users_01.dbf,/u02/app/oracle/oradata/LBV11203/users2_01.dbf NETWORK_LINK=orcl
Source timezone version is +00:00 and target timezone version is -07:00.
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
…
ORA-39082: Object type PACKAGE BODY:”SYSMAN”.”MGMT_TIME_SYNC” created with compilation warnings
ORA-39082: Object type PACKAGE BODY:”SYSMAN”.”MGMT_USER” created with compilation warnings
ORA-39082: Object type PACKAGE BODY:”SYSMAN”.”MGMT_VIEW_PRIV” created with compilation warnings
ORA-39082: Object type TRIGGER:”APEX_030200″.”WWV_BIU_FLOW_SESSIONS” created with compilation warnings
ORA-39082: Object type TRIGGER:”SYSMAN”.”MGMT_CREDS_INS_UPD” created with compilation warnings
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ completed with 383 error(s) at Fri Nov 29 16:17:14 2013 elapsed 0 00:29:27
Errors due to object existence as well as “ORA-65040: operation not allowed from within a pluggable database” can be ignored.
Let’s check what we’ve imported into “LBV11203”, checking the tablespace-datafiles and the users:
sqlplus sys@LBV11203 as sysdba
SQL> select file_name from dba_data_files;
FILE_NAME
—————————————————————————————————-
/u02/app/oracle/oradata/CDB121A/EC5267F2B34A6543E045000000000001/datafile/o1_mf_system_99k84hd8_.dbf
/u02/app/oracle/oradata/CDB121A/EC5267F2B34A6543E045000000000001/datafile/o1_mf_sysaux_99k84hpy_.dbf
/u02/app/oracle/oradata/LBV11203/users_01.dbf
/u02/app/oracle/oradata/LBV11203/users2_01.dbf
SQL> select username from dba_users order by 1;
USERNAME
——————————————————————————–
ANONYMOUS
APEX_030200
APEX_040200
APEX_PUBLIC_USER
APPQOSSYS
AUDSYS
CTXSYS
DBSNMP
DIP
DVF
DVSYS
FLOWS_FILES
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
LBACSYS
LBV01DBA
MDDATA
MDSYS
MGMT_VIEW
OJVMSYS
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
OWBSYS
OWBSYS_AUDIT
SCOTT
SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
SYS
SYSBACKUP
SYSDG
SYSKM
SYSMAN
SYSTEM
WMSYS
XDB
XS$NULL
42 rows selected.
Now we see that the SCOTT user is present and from the below query we see that component status in DBA_REGISTRY is “VALID”:
SQL> select comp_name,version,status from dba_registry;
COMP_NAME VERSION STATUS
—————————————- —————————— ——————————————–
Oracle Database Vault 12.1.0.1.0 VALID
Oracle Application Express 4.2.0.00.27 VALID
Oracle Label Security 12.1.0.1.0 VALID
Spatial 12.1.0.1.0 VALID
Oracle Multimedia 12.1.0.1.0 VALID
Oracle Text 12.1.0.1.0 VALID
Oracle Workspace Manager 12.1.0.1.0 VALID
Oracle XML Database 12.1.0.1.0 VALID
Oracle Database Catalog Views 12.1.0.1.0 VALID
Oracle Database Packages and Types 12.1.0.1.0 VALID
JServer JAVA Virtual Machine 12.1.0.1.0 VALID
Oracle XDK 12.1.0.1.0 VALID
Oracle Database Java Packages 12.1.0.1.0 VALID
OLAP Analytic Workspace 12.1.0.1.0 VALID
Oracle OLAP API 12.1.0.1.0 VALID
Oracle Real Application Clusters 12.1.0.1.0 OPTION OFF
16 rows selected.
Now we can drop the PUBLIC database link “orcl” used by “impdp” and try to connect as SCOTT to access the transferred tables/data:
SQL> drop public database link ORCL;
Database link dropped.
SQL> conn scott/tiger@LBV11203
Connected.
SQL> col tname format a20
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
——————– ——- ———-
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
SQL> select count(*) from emp;
COUNT(*)
———-
14
Now post-steps/sanity checks can be performed to verify that the application data has been transferred correctly.
And you can then switch the application/users to the 12cR1 database.
Conclusion:
We’ve demonstrated an upgrade from 11.2.0.3 to 12.1.0.1 pluggable database using the the Datapump Full Transportable Export/Import method.
If you can afford the downtime this is a feasable method, for example for cross-platform/endianess migrations.