Table of contents
Preamble
As we have seen in previous post I have migrated my standalone 11gR2 database to a pluggable database (PDB). Pluggable database obviously running in a container database (CDB).
I have chosen to upgrade first in 12.1.0.1 to be able to test the unplug/plug procedure to upgrade to 12.1.0.2. In below my first container cdb1 is in 12.1.0.1.4 with /u01/app/oracle/product/12.1.0/dbhome_1 as Oracle home. My second container is cdb2 in 12.1.0.2.0 with /u01/app/oracle/product/12.1.0/dbhome_2 as Oracle home.
By referring to Oracle marketing slides I was expecting a no-work migration by just unplugging and plugging in new container my PDB and, yes, maybe a synchronize procedure to update the PDB dictionary. In real life I have been disappointed as you still have to execute the upgrade script. Oracle anyway claim that the upgrade is simplified…
My test server is a virtual machine running under Oracle Linux Server release 6.5.
Backup pluggable database
As usual one good advice is to start with a cold backup, for this I configure few basic options:
CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%F'; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/%U'; |
Then after to backup the container and all pluggable databases (including root one):
BACKUP DATABASE |
Or simply to backup the pluggable database I’m planning to upgrade (pdb1):
BACKUP PLUGGABLE DATABASE pdb1; |
Pluggable database migration
Let’s issue pre-upgrade script (preupgrd.sql) on PDB I plan to upgrade:
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SQL> ALTER SESSION SET container=pdb1; SESSION altered. SQL> @/u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/preupgrd.SQL Loading Pre-Upgrade PACKAGE... *************************************************************************** Executing Pre-Upgrade Checks IN PDB1... *************************************************************************** ************************************************************ ====>> ERRORS FOUND FOR PDB1 <<==== The following are *** ERROR LEVEL CONDITIONS *** that must be addressed PRIOR TO attempting your upgrade. Failure TO DO so will result IN a failed upgrade. You MUST resolve the above errors PRIOR TO upgrade ************************************************************ ************************************************************ ====>> PRE-UPGRADE RESULTS FOR PDB1 <<==== ACTIONS REQUIRED: 1. Review results OF the pre-upgrade checks: /u01/app/oracle/cfgtoollogs/cdb1/preupgrade/preupgrade.LOG 2. EXECUTE IN the SOURCE environment BEFORE upgrade: /u01/app/oracle/cfgtoollogs/cdb1/preupgrade/preupgrade_fixups.SQL 3. EXECUTE IN the NEW environment AFTER upgrade: /u01/app/oracle/cfgtoollogs/cdb1/preupgrade/postupgrade_fixups.SQL ************************************************************ *************************************************************************** Pre-Upgrade Checks IN PDB1 Completed. *************************************************************************** *************************************************************************** *************************************************************************** |
I had nothing special to do except below standard command:
SQL> EXECUTE dbms_stats.gather_dictionary_stats; PL/SQL PROCEDURE successfully completed. |
Now let’s create the pluggable database manifest and unplug the pluggable database:
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SQL> ALTER pluggable DATABASE pdb1 CLOSE; Pluggable DATABASE altered. SQL> ALTER pluggable DATABASE pdb1 unplug INTO '/backup/pdb1.xml'; Pluggable DATABASE altered. |
On my 12.1.0.2 container (cdb2):
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO |
As I wanted afterwards to remove completely the +DATA/CDB1 directory I have chosen to copy the pdb1 pluggable database datafiles to new directory. As I’m using Oracle Managed Files (OMF) I have just specified COPY keyword, in a more standard environment using file systems you can also use FILE_NAME_CONVERT. In real life if you have a multi-terabytes pluggable database you may not want to copy them, so consider using NOCOPY:
SQL> CREATE pluggable DATABASE PDB1 USING '/backup/pdb1.xml' copy; Pluggable DATABASE created. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED SQL> ALTER pluggable DATABASE pdb1 OPEN upgrade; Warning: PDB altered WITH errors. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MIGRATE YES |
Here we are, in some Oracle slides you just see the unplug and plug operations (with a nice animation). But this is a bit more time consuming in real world, means that the upgrade script (catupgrd.sql) must be issued. To upgrade your PDB use below new upgrade parallel method (catctl.pl Perl script), pluggable database name(s) must be in capital letters:
[oracle@server1 ~]$ cd $ORACLE_HOME/rdbms/admin [oracle@server1 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c "PDB1" -l /tmp catupgrd.sql |
Once done you can finally open the pluggable database and compile invalid objects if any:
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED SQL> ALTER pluggable DATABASE pdb1 OPEN; Pluggable DATABASE altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SQL> ALTER SESSION SET container=PDB1; SESSION altered. SQL> @?/rdbms/admin/utlrp |
Then executed /u01/app/oracle/cfgtoollogs/cdb1/preupgrade/postupgrade_fixups.sql script. The script is only suggesting to execute:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; |
That’s it, PDB upgraded in 12.1.0.2:
SQL> ALTER SESSION SET container=pdb1; SESSION altered. SQL> SELECT file_name FROM dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/CDB2/02B52D2CC3547ACCE0536538A8C07CAE/DATAFILE/SYSTEM.286.859639681 +DATA/CDB2/02B52D2CC3547ACCE0536538A8C07CAE/DATAFILE/sysaux.287.859639681 +DATA/CDB2/02B52D2CC3547ACCE0536538A8C07CAE/DATAFILE/users.288.859639679 |
Original pluggable database
As I have copied the original datafiles why not trying to reopen the pluggable database on its initial container (cdb1):
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED SQL> ALTER pluggable DATABASE pdb1 OPEN; ALTER pluggable DATABASE pdb1 OPEN * ERROR AT line 1: ORA-65086: cannot OPEN/CLOSE the pluggable DATABASE |
It is not possible to open again the pluggable database… The official documentation claims:
After a PDB is unplugged, it remains in the CDB with an open mode of MOUNTED and a status of UNPLUGGED. The only operation you can perform on an unplugged PDB is DROP PLUGGABLE DATABASE, which will remove it from the CDB. You must drop the PDB before you can plug it into the same CDB or another CDB.
The open mode is displayed with show pdbs command while I had to use DBA_PDBS to have the status:
SQL> SELECT pdb_id,pdb_name,status FROM dba_pdbs; PDB_ID PDB_NAME STATUS ---------- ---------- ------------- 2 PDB$SEED NORMAL 3 PDB1 UNPLUGGED |
So following official documentation recommendations:
SQL> DROP pluggable DATABASE pdb1; Pluggable DATABASE dropped. SQL> CREATE pluggable DATABASE pdb1 USING '/backup/pdb1.xml' NOCOPY; Pluggable DATABASE created. SQL> ALTER pluggable DATABASE pdb1 OPEN; Pluggable DATABASE altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO |
Enterprise Manager Express
Then on my 12.1.0.2 container (cdb2) database I have configured EM Express with:
SQL> EXEC DBMS_XDB_CONFIG.SETHTTPSPORT(5502); PL/SQL PROCEDURE successfully completed. SQL> SELECT dbms_xdb_config.gethttpsport() FROM dual; DBMS_XDB_CONFIG.GETHTTPSPORT() ------------------------------ 5502 |
And the newly created pluggable database can be seen:
References
- EM Express FAQ (Doc ID 1575988.1)
- Upgrading a Pluggable Database (PDB)
