As single-tenant is the new default for all Oracle databases 12c R2 (12.2.0.1) I was happy that I already had a multi-tenant database in my Oracle 12.1.0.2 home on my laptop (running Windows 10). To test some stuff I need to prepare for my presentation at DOAG Conference 2017 (e.g. new cross-endian functionality of RMAN), I installed the fresh 12.2.0.1 database software to my oracle product folder.
Within the installer I liked to create a container database, but without a PDB – my goal was to unplug one of my existing (Spatial) PDBs from 12.1 and plug it into the 12.2 database on the same machine - but the installer does not allow to create a container database without a PDB, so I created also a PDB called jso2pdb.
(For your understanding: When I refer to CDB121 it’s the 12.1.0.2 container database I am working with, when I refer to CDB122 it’s the 12.2.0.1.)
After the software installation and the new CDB122 was created, I connected as sys to CDB122 and dropped the new pluggable database (which I never wanted to exist):
SQL> drop pluggable database jso2pdb including datafiles;
SQL> select pdb_id, pdb_name from dba_pdbs;
PDB_ID PDB_NAME
---------- --------------------------------------------------------------------------------
2 PDB$SEED
Next to that, I recreated the listener service from the new home using the old *.ora files and added my new stuff to tnsnames.ora. Everything is prepared now, so I started with the work on the CDB121 as a SYS User connected to the CDB:
SQL> select con_id, name, open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 JSOPDB1 MOUNTED
4 SPATEST READ WRITE
The PDB SPATEST is the one I want to unplug, plug and upgrade, but it is still in READ WRITE mode, so first we need to put it in the mount state by closing it. Don’t forget to close always PDBs immediate – otherwise they are shut down with the normal mode.
SQL> alter pluggable database SPATEST close immediate;
Done
SQL> select con_id, name, open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 JSOPDB1 MOUNTED
4 SPATEST MOUNTED
Now I unplug the PDB SPATEST by creating the XML File which stores the metadata of the PDB:
SQL> ALTER PLUGGABLE DATABASE SPATEST
2 UNPLUG INTO 'C:\app\oracle\admin\orcljso2\temp\SPATEST.xml';
Done
Selecting from v$pdbs does still show my PDB as mounted, but at dba_pdbs we can see it’s unplugged.
SQL> select con_id, name, open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 JSOPDB1 MOUNTED
4 SPATEST MOUNTED
SQL> select PDB_ID, PDB_NAME, STATUS from dba_pdbs;
PDB_ID PDB_NAME STATUS
---------- -------------------------------------------------------------------------------- ---------
2 PDB$SEED NORMAL
3 JSOPDB1 NORMAL
4 SPATEST UNPLUGGED
Do not drop the PDB from the old CDB121 NOW.
When I open the XML file with an editor, I can see that my file paths still refer to the old location:
<path>C:\DATA\ORACLEDB\ORADATA\DATAFILES\ORCLJSO\SPATEST\SYSTEM01.DBF</path>
Don’t change the entries in the XML file by your own, we can do this later…
We now test the compatibility of the SPATEST PDB with the new CDB122 by running the following PL/SQL block on CDB122:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 compatible CONSTANT VARCHAR2(3) :=
3 CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
4 pdb_descr_file => 'C:\app\oracle\admin\orcljso2\temp\SPATEST.xml',
5 pdb_name => 'SPATEST')
6 WHEN TRUE THEN 'YES'
7 ELSE 'NO'
8 END;
9 BEGIN
10 DBMS_OUTPUT.PUT_LINE(compatible);
11 END;
12 /
NO
PL/SQL procedure successfully completed
Unfortunately, it is not compatible, so we need to check, why:
SQL> select cause, type, line, message from pdb_plug_in_violations where name='SPATEST';
CAUSE TYPE LINE MESSAGE
---------------------------------------------------------------- --------- ---------- --------------------------------------------------------------------------------
VSN not match ERROR 1 PDB's version does not match CDB's version: PDB's version 12.1.0.2.0. CDB's vers
OPTION ERROR 1 PDB's version is lower than CDB and PDB has more components than the CDB, plug i
Parameter WARNING 1 CDB parameter processes mismatch: Previous 300 Current 320
APEX ERROR 2 APEX mismatch: PDB has installed common APEX. CDB has not installed APEX.
Parameter WARNING 2 CDB parameter pga_aggregate_limit mismatch: Previous 10G Current 3980M
Parameter WARNING 3 CDB parameter memory_target mismatch: Previous 4080M Current 0
CDB undo mode WARNING 1 Undo mode mismatch: PDB using SHARED undo. CDB using LOCAL undo.
Parameter WARNING 4 CDB parameter memory_max_target mismatch: Previous 6096M Current 0
Parameter WARNING 5 CDB parameter compatible mismatch: Previous '12.1.0.2.0' Current '12.2.0'
Parameter WARNING 6 CDB parameter job_queue_processes mismatch: Previous 200 Current 4000
10 rows selected
OK, let’s sort it out:
- VSN not match: Yes, we want to upgrade the pdb after plugging it into CDB122.
- Option and APEX: Ok – some options are missing in my CDB122, we should fix that first
- Parameter: Ok, nothing that makes any trouble while plugging the database in
- CDB undo mode: Yes, 12.2 has local undo, we need to change that after the PDB is plugged into CDB122.
- There is no warning/error about a mismatch in endian format or character set.
To fix the only real error I have remembered that the database CDB122 was created by the software installation process while the CDB121 I had created some months ago using DBCA. And it’s true, the DBCA shows that Oracle Application Express is not installed in CDB122 (JVM, Text, Multimedia, OLAP, Spatial, Label Security, Database Vault and Database Extensions for .NET are all installed by default), so I added it to the installed components list and finished the DBCA. After that, I still got an error about APEX: The Apex version in the CDB121 is 4.x, the Apex installed in CDB122 is 5.x, but I still can proceed with the plugin of the PDB.
So I plugin the SPATEST into CDB122 (which is running some seconds):
SQL> create pluggable database SPATEST
2 using 'C:\app\oracle\admin\orcljso2\temp\SPATEST.xml'
3 move file_name_convert=
4 ('C:\data\OracleDB\oradata\Datafiles\ORCLJSO\SPATEST','C:\data\OracleDB\oradata\Datafiles\ORCLJSO2\SPATEST');
Done
SQL> select con_id, name, open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- -------------------------------------------------------------------------------- ----------
2 PDB$SEED READ ONLY
3 SPATEST MOUNTED
My database is now plugged into the CDB122 and stays in mount state. With Windows Explorer I can see all datafiles do exist in the new location.
In Oracle DB 12.2 we can upgrade PDBs using priority lists, but as we do need to upgrade only one, we can specify the upgrade direct with the –c switch.
First I prepare the SPATEST PDB:
SQL> alter pluggable database SPATEST open upgrade;
Done
Next I run the new parallel upgrade utility on SPATEST by using the dbupgrade.cmd in Windows. The parameters are the same as for catctl.pl (-n= 4 parallel sessions, -c= List of PDBs to upgrade)
C:\>%ORACLE_HOME%\bin\dbupgrade -c 'SPATEST' –n 4
On my laptop, this was running now for roundabout 45 minutes. After this has finished, the PDB can now be opened on CDB122:
SQL> alter session set container=CDB$ROOT;
Session altered
SQL> select con_id, name, open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- -------------------------------------------------------------------------------- ----------
2 PDB$SEED READ ONLY
3 SPATEST MOUNTED
SQL> alter pluggable database SPATEST open;
Done
SQL> col message for a35 word_wrapped
SQL> select cause, type, line, message from pdb_plug_in_violations where name='SPATEST' and status <> 'RESOLVED';
CAUSE TYPE LINE MESSAGE
---------------------------------------------------------------- --------- ---------- -----------------------------------
OPTION WARNING 15 Database option RAC mismatch: PDB i
nstalled version 12.2.0.1.0. CDB in
stalled version NULL.
Still one warning – There is a RAC option enabled in the PDB (CDB121 wasn’t a RAC?), so we need to switch this option off. Checking the CDB122 first:
SQL> select COMP_ID, COMP_NAME, STATUS from dba_registry;
COMP_ID COMP_NAME STATUS
------------------------------ -------------------------------------------------------------------------------- --------------------------------------------
CATALOG Oracle Database Catalog Views VALID
CATPROC Oracle Database Packages and Types VALID
JAVAVM JServer JAVA Virtual Machine VALID
XML Oracle XDK VALID
CATJAVA Oracle Database Java Packages VALID
APS OLAP Analytic Workspace VALID
RAC Oracle Real Application Clusters OPTION OFF
XDB Oracle XML Database VALID
OWM Oracle Workspace Manager VALID
CONTEXT Oracle Text VALID
ORDIM Oracle Multimedia VALID
SDO Spatial VALID
XOQ Oracle OLAP API VALID
OLS Oracle Label Security VALID
APEX Oracle Application Express VALID
DV Oracle Database Vault VALID
16 rows selected
Checking the DBA_REGISTRY of the PDB shows that RAC was upgraded:
SQL> alter session set container=SPATEST;
Session altered
SQL> select COMP_ID, COMP_NAME, STATUS from dba_registry;
COMP_ID COMP_NAME STATUS
------------------------------ -------------------------------------------------------------------------------- --------------------------------------------
CATALOG Oracle Database Catalog Views UPGRADED
CATPROC Oracle Database Packages and Types UPGRADED
JAVAVM JServer JAVA Virtual Machine UPGRADED
XML Oracle XDK UPGRADED
CATJAVA Oracle Database Java Packages UPGRADED
APS OLAP Analytic Workspace UPGRADED
RAC Oracle Real Application Clusters UPGRADED
XDB Oracle XML Database UPGRADED
OWM Oracle Workspace Manager UPGRADED
CONTEXT Oracle Text UPGRADED
ORDIM Oracle Multimedia UPGRADED
SDO Spatial UPGRADED
XOQ Oracle OLAP API UPGRADED
OLS Oracle Label Security UPGRADED
APEX Oracle Application Express UPGRADED
DV Oracle Database Vault UPGRADED
16 rows selected
This seems to be a bug, because in the CDB121 RAC also has the status “OPTION OFF”. Now it’s easy to set the RAC option of on the PDB level.
SQL> exec dbms_registry.OPTION_OFF('RAC');
PL/SQL procedure successfully completed
SQL> select COMP_ID, COMP_NAME, STATUS from dba_registry;
COMP_ID COMP_NAME STATUS
------------------------------ -------------------------------------------------------------------------------- --------------------------------------------
CATALOG Oracle Database Catalog Views UPGRADED
CATPROC Oracle Database Packages and Types UPGRADED
JAVAVM JServer JAVA Virtual Machine UPGRADED
XML Oracle XDK UPGRADED
CATJAVA Oracle Database Java Packages UPGRADED
APS OLAP Analytic Workspace UPGRADED
RAC Oracle Real Application Clusters OPTION OFF
XDB Oracle XML Database UPGRADED
OWM Oracle Workspace Manager UPGRADED
CONTEXT Oracle Text UPGRADED
ORDIM Oracle Multimedia UPGRADED
SDO Spatial UPGRADED
XOQ Oracle OLAP API UPGRADED
OLS Oracle Label Security UPGRADED
APEX Oracle Application Express UPGRADED
DV Oracle Database Vault UPGRADED
16 rows selected
Oracle checks the violations when we open a PDB, so I do a last restart of my pdb and check the status again:
SQL> alter pluggable database close immediate;
Done
SQL> alter pluggable database open;
Done
SQL> select cause, type, line, message from pdb_plug_in_violations where name='SPATEST' and status <> 'RESOLVED';
CAUSE TYPE LINE MESSAGE
---------------------------------------------------------------- --------- ---------- -----------------------------------
SQL>
All violations are gone. Now it’s time to make a backup of the new database environment. After this has finished the pluggable database can safely be dropped from CDB121 (but be aware of losing backup metadata https://www.pythian.com/blog/rman-12c-say-goodbye-to-your-backup-when-dropping-your-pdb/). I needed to remove the SYSAUX and SYSTEM datafiles manually from the disk stored in the OLD PDB folder.
At this step I was wondering about my undo. Because in 12.1 there is one shared undo for the CDB and all PDBs and in 12.2 every PDB has it’s local Undo. And I never have changed something in here, but the upgrade procedure did it by itself and created a local undo tablespace.
SQL> select t.name,d.name, d.status, to_char(d.creation_time,'DD.MM.YYYY HH24:MI:SS') from v$tablespace t, v$datafile d where d.ts#=t.ts# and t.name like '%UNDO%' ;
NAME NAME STATUS TO_CHAR(D.CREATION_TIME,'DD.MM
------------------------------ -------------------------------------------------------------------------------- ------- ------------------------------
UNDO_1 C:\DATA\ORACLEDB\ORADATA\DATAFILES\ORCLJSO2\SPATEST\SYSTEM01_I1_UNDO.DBF ONLINE 26.07.2017 13:25:26
I can live with that, so I didn’t change the default undo to another name and another file.
Lessions learned:
- Upgrading a PDB from 12.1 to 12.2 by unplug / plugin on Windows is running roundabout 1h
- Seems there is a bug while upgrading the options, so RAC Option is enabled/upgraded by default, even it is off
- Never touch the XML File created, everything like moving datafiles etc. can be done at the plugin phase
- I thought I have to deal with the creation of a local undo, but it’s migrated by default
- Even if the test says, the PDB is NOT compatible with the CDB I want to plug it in – check the errors and proceed
- Upgrading a PDB from 12.1 to 12.2 by unplug / plugin on Windows is running roundabout 1h
- Seems there is a bug while upgrading the options, so RAC Option is enabled/upgraded by default, even it is off
- Never touch the XML File created, everything like moving datafiles etc. can be done at the plugin phase
- I thought I have to deal with the creation of a local undo, but it’s migrated by default
- Even if the test says, the PDB is NOT compatible with the CDB I want to plug it in – check the errors and proceed
