Workshops, Oracle Events and Conferences about 12C Multitenant features let us think of a direct migration path for the next release. We hope this should be done in very few steps :
1) New binary installation on new ORACLE HOME
2) New root container creation with new binary
3) Target PDB shutdown (running previous release)
4) Unplug target PDB from previous release container
5) Plug target PDB on new root container
6) Open target PDB (new release)
That’s it , quick, simple and straitght forward. Downtime is reduce to database files copy between steps 3) and 6) and even that copy is not necessary if you doesn’t need to ! A dream for DBAs.
As Oracle stuff dictionnary (mainly tight to the release) is on the root container, this could be true and now that patchset 12.1.0.1 is out there, let see how it really works.
Environment Setup:
- Linux 64bits (Virtual Box) using OEL6.1
- Oracle Database Enterprise Edition 12.1.0.1 installed
- Container cdbELE release12.1.2.0.1 up and running (ORACLE_HOME=/u01/app/oracle/product/12.1.0.1)
- PDB database ORACLE_ID= PDBELE plugged on cdbELE (Order Entry schema sample)
- Oracle Database Enterprise Edition installed on new ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
- Container cdb1212 created empty using new binary
Step 1) and 2) l are done so lets go further :
Step 3) Target PDB shutdown
Little addon to the process, Target pre upgrade checkup. This could be useful if something wrong is detected before the upgrade . The script is preupgrd.sql and must be running from directory new $ORACLE_HOME/rdbms/admin
– Administrator account on root container release 12.1.0.1 : cdbELE
[oracle@ele1ole6 ~]$ . oraenv
ORACLE_SID = [oracle] ? cdbELE
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ele1ole6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 14 09:28:30 2014
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 pdbs CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBELE READ WRITE NO
SQL> alter session set container=pdbELE ;
SQL> @/u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql
Output directed to the logfile:
- All componants are VALID
- No errors
- No warning
- One informational message for APEX release wich may be upgraded.
…/…
******************************************************************
[Pre-Upgrade Checks]
**********************************************************************
INFORMATION: --> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade
APEX is currently at version 4.2.0.00.27 and will need to be
upgraded to APEX version 4.2.5 in the new release.
Note 1: To reduce database upgrade time, APEX can be manually
upgraded outside of and prior to database upgrade.
Note 2: See MOS Note 1088970.1 for information on APEX
installation upgrades.
…/…
We feel ready for the jump.
– Shutdown of target PDBELE on original root container :
SQL> alter session set container=CDB$ROOT ; SQL> alter pluggable database pdbELE close ; SQL> show pdbs 2 PDB$SEED READ ONLY NO 3 PDBELE MOUNTED
Step 4) Unplug target PDB from previous release container
Only one SQL command, wich generates quickly one database description on the specified xml file :
SQL> alter pluggable database pdbELE unplug into '/home/oracle/pdbELE.xml' ;
Once the PBD is unplug, the only option in this container is to suppress pdbELE using the « DROP » clause . If you need to reaccess the database, you have to repluged it using specifics options.
Step 5) Plug target PDB on new root container CDB1212
Before that, we can use DBMS_PDB.CHECK_PLUG_COMPATIBILTY() to validate the plugin action :
Connected on root container CDB1212:
[oracle@ele1ole6 ~]$ . oraenv ORACLE_SID = [oracle] ? cdb1212 The Oracle base remains unchanged with value /u01/app/oracle [oracle@ele1ole6 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 14 09:40:30 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> set serveroutput on SQL> DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/home/oracle/pdbELE.xml' , pdb_name => 'PDBELE' ) WHEN TRUE THEN 'YES' ELSE 'NO' END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; /
First bad sign, the ouput result is : NO , more details from view PDB_PLUG_IN_VIOLATIONS :
SQL> select message, status from pdb_plug_in_violations where type like '%ERR%'; MESSAGE STATUS --------------------------------------------------------------------------------------------------------- PDB's version does not match CDB's version: PDB's version 12.1.0.0.0. CDB's version 12.1.0.2.0. PENDING APEX mismatch: PDB installed version 4.2.0.00.27 CDB installed version 4.2.5.00.08 PENDING
The APEX warning is ok, we can delete and reinstall any release using instructions described on note MOS 558340.1. But the PENDING status of PDB’s version warn me about my theory ! Let’s get a try anyway.:
– Create pluggable database :
we set “using <xml file>” and “file_name_convert” for database localisatiion, ny default files are copied over and not moved (that means original files are not impacted, wich may be useful later on) :
SQL> create pluggable database pdbELE using '/home/oracle/pdbELE.xml'
file_name_convert=('/u01/oradata/cdbELE/pdbELE','/u03/oradata/cdb1212/pdbELE') ;
Result : “Pluggable database created.“
There’s still little hope, it’s the moment of thruth.
Step 6) Open PDB in read / write
SQL> alter pluggable database PDBELE open ; Warning: PDB altered with errors.
Oups, that’s not what i expect ! The theory seems wrong !
in fact the PDB is open but in mode « MIGRATE » and « RESTRICTED », no access for applications :
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBELE MIGRATE YES
I’s the same command result :
SQL> alter pluggable database pdbele open upgrade ;
Proofs are on alert log file :
ORA-65000 signalled during: alter pluggable database open ... 2014-08-14 10:30:58.166000 +02:00 alter pluggable database PDBELE open Pluggable database PDBELE dictionary check beginning Pluggable Database PDBELE Dictionary check complete Database Characterset for PDBELE is WE8MSWIN1252 *************************************************************** WARNING: Pluggable Database PDBELE with pdb id - 3 is altered with errors or warnings. Please look into PDB_PLUG_IN_VIOLATIONS view for more details. *************************************************************** Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32 2014-08-14 10:30:59.665000 +02:00 Opening pdb PDBELE (3) with Resource Manager plan: DEFAULT_PLAN ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY; ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY; Resource Manager disabled during database migration: plan '' not set ALTER SYSTEM SET resource_manager_plan= SCOPE=MEMORY; ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY; Pluggable database PDBELE opened read write Completed: alter pluggable database PDBELE open
The error details are the same as when we did the pluggin check on the view PDB_PLUG_IN_VIOLATIONS . Clearly the two dictionnary are not compatible due to version mismatch.
SQL> select message, status from pdb_plug_in_violations where type like '%ERR%'; MESSAGE STATUS --------------------------------------------------------------------------------------------------------- PDB's version does not match CDB's version: PDB's version 12.1.0.1.0. CDB's version 12.1.0.2.0. PENDING
Plug out, plug in is not enough , forget straight migration. The links between own Oracle Objects in the root container those and access from the PDB need to be rebuild. The initial hypthesis is false: there is two dictionnary not one and even if it’s links and not copy, there is job to be done !
To get out of that, no otherway than add one upgrade step :
Step 6) become : PDB dictionnary Upgrade
The tool is the perl named catctl.pl the one from the new release, the same we use for non multitenant database, the one we check during this journey (url link vers la version anglaise)
It’s located under $ORACLE_HOME/rdbms/admin of the new release, and need a destination directory if you want logging to files. I used with the container name passe to « -c » option :
[oracle@ele1ole6 ~]$ cd $ORACLE_HOME/rdbms/admin [oracle@ele1ole6 admin]$ mkdir /home/oracle/upgradePDBELE [oracle@ele1ole6 admin]$ /u01/app/oracle/product/12.1.0.2/perl/bin/perl catctl.pl -c "PDBELE" -l /home/oracle/upgradePDBELE catupgrd.sql
The output is :
Argument list for [catctl.pl] SQL Process Count n = 0 SQL PDB Process Count N = 0 Input Directory d = 0 Phase Logging Table t = 0 Log Dir l = /home/oracle/upgradePDBELE Script s = 0 Serial Run S = 0 Upgrade Mode active M = 0 Start Phase p = 0 End Phase P = 0 Log Id i = 0 Run in c = PDBELE Do not run in C = 0 Echo OFF e = 1 No Post Upgrade x = 0 Reverse Order r = 0 Open Mode Normal o = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 Display Phases y = 0 Child Process I = 0 catctl.pl version: 12.1.0.2.0 Oracle Base = /u01/app/oracle Analyzing file catupgrd.sql Log files in /home/oracle/upgradePDBELE catcon: ALL catcon-related output will be written to /home/oracle/upgradePDBELE/catupgrd_catcon_3693.lst catcon: See /home/oracle/upgradePDBELE/catupgrd*.log files for output generated by scripts catcon: See /home/oracle/upgradePDBELE/catupgrd_*.lst files for spool files, if any Number of Cpus = 1 Parallel PDB Upgrades = 2 SQL PDB Process Count = 2 SQL Process Count = 0 New SQL Process Count = 1 [CONTAINER NAMES] CDB$ROOT PDB$SEED PDBELE PDB Inclusion:[PDBELE] Exclusion:[] Starting [/u02/app/oracle/product/12.1.0.2/perl/bin/perl catctl.pl -c 'PDBELE' -l /home/oracle/upgradePDBELE -I -i pdbele -n 2 catupgrd.sql] Argument list for [catctl.pl] SQL Process Count n = 2 SQL PDB Process Count N = 0 Input Directory d = 0 Phase Logging Table t = 0 Log Dir l = /home/oracle/upgradePDBELE Script s = 0 Serial Run S = 0 Upgrade Mode active M = 0 Start Phase p = 0 End Phase P = 0 Log Id i = pdbele Run in c = PDBELE Do not run in C = 0 Echo OFF e = 1 No Post Upgrade x = 0 Reverse Order r = 0 Open Mode Normal o = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 Display Phases y = 0 Child Process I = 1 catctl.pl version: 12.1.0.2.0 Oracle Base = /u01/app/oracle Analyzing file catupgrd.sql Log files in /home/oracle/upgradePDBELE catcon: ALL catcon-related output will be written to /home/oracle/upgradePDBELE/catupgrdpdbele_catcon_3775.lst catcon: See /home/oracle/upgradePDBELE/catupgrdpdbele*.log files for output generated by scripts catcon: See /home/oracle/upgradePDBELE/catupgrdpdbele_*.lst files for spool files, if any Number of Cpus = 1 SQL PDB Process Count = 2 SQL Process Count = 2 [CONTAINER NAMES] CDB$ROOT PDB$SEED PDBELE PDB Inclusion:[PDBELE] Exclusion:[] ------------------------------------------------------ Phases [0-73] Container Lists Inclusion:[PDBELE] Exclusion:[] Serial Phase #: 0 Files: 1 Time: 10s PDBELE Serial Phase #: 1 Files: 5 Time: 39s PDBELE .../... Serial Phase #:62 Files: 1 Time: 126s PDBELE Restart Phase #:63 Files: 1 Time: 0s PDBELE Serial Phase #:64 Files: 1 Time: 0s PDBELE Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u02/app/oracle/product/12.1.0.2/lib; export LD_LIBRARY_PATH;/u02/app/oracle/product/12.1.0.2/perl/bin/perl -I /u02/app/oracle/product/12.1.0.2/rdbms/admin -I /u02/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch /u02/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only -pdbs PDBELE > /home/oracle/upgradePDBELE/catupgrdpdbele_datapatch_upgrade.log 2> /home/oracle/upgradePDBELE/catupgrdpdbele_datapatch_upgrade.err returned from sqlpatch Time: 10s PDBELE Serial Phase #:66 Files: 1 Time: 3s PDBELE Serial Phase #:68 Files: 1 Time: 3s PDBELE Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u02/app/oracle/product/12.1.0.2/lib; export LD_LIBRARY_PATH;/u02/app/oracle/product/12.1.0.2/perl/bin/perl -I /u02/app/oracle/product/12.1.0.2/rdbms/admin -I /u02/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch /u02/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -pdbs PDBELE > /home/oracle/upgradePDBELE/catupgrdpdbele_datapatch_normal.log 2> /home/oracle/upgradePDBELE/catupgrdpdbele_datapatch_normal.err returned from sqlpatch Time: 11s PDBELE Serial Phase #:70 Files: 1 Time: 79s PDBELE Serial Phase #:71 Files: 1 Time: 11s PDBELE Serial Phase #:72 Files: 1 Time: 5s PDBELE Serial Phase #:73 Files: 1 Time: 0s PDBELE Grand Total Time: 1355s PDBELE LOG FILES: (catupgrdpdbele*.log) Upgrade Summary Report Located in: /u01/app/oracle/product/12.1.0.2/cfgtoollogs/cdb1212/upgrade/upg_summary.log Total Upgrade Time: [0d:0h:22m:35s] Time: 1358s For PDB(s) Grand Total Time: 1358s LOG FILES: (catupgrd*.log) Grand Total Upgrade Time: [0d:0h:22m:38s]
It takes almost as long as for a classical (non mutitenant) database and it’s a pity !
I had a hope that rebuild links could be quicker than updates and inserts.
Once it’s over you can verify there is no error in the log files and go to the last step : opening the PDB database to the world.
Beware that the PDB is closed at the end , check the alert logfile :
2014-08-14 11:40:12.656000 +02:00 SERVER COMPONENT id=POSTUP_END: timestamp=2014-08-14 11:40:12 Container=PDBELE Id=3 2014-08-14 11:40:28.333000 +02:00 ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE 2014-08-14 11:40:31.680000 +02:00 ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local Pluggable database PDBELE closed Completed: ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE
Step 7) PDB opening and last objects recompilation
SQL> alter pluggable database PDBELE open ; Pluggable database altered.
Recompile on last time invalid objects that may stay using utlrp :
SQL> alter session set container=PDBELE
SQL> select count(*) from dba_objects where status not like 'VALID' ;
COUNT(*)
---------
728
SQL> @?/rdbms/admin/utlrp
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2014-08-19 10:24:07
DOC>
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2014-08-19 10:24:11
DOC> The following query reports the number of objects that have compiled
.../...
DOC>
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
...Database user "SYS", database schema "APEX_040200", user# "98" 10:24:34
...Compiled 0 out of 3014 objects considered, 0 failed compilation 10:24:34
...271 packages
...263 package bodies
...452 tables
...11 functions
...16 procedures
...3 sequences
...457 triggers
...1320 indexes
...211 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 10:24:34
...Completed key object existence check 10:24:35
...Setting DBMS Registry 10:24:35
...Setting DBMS Registry Complete 10:24:35
...Exiting validate 10:24:35
PL/SQL procedure successfully completed.
Verify all the components are in the valid state :
SQL>select comp_name ,version , status from dba_registry ; COMP_NAME VERSION STATUS ------------------------------ ------------------------------ ----------- Oracle Database Vault 12.1.0.2.0 VALID Oracle Application Express 4.2.5.00.08 VALID Oracle Label Security 12.1.0.2.0 VALID Spatial 12.1.0.2.0 VALID Oracle Multimedia 12.1.0.2.0 VALID Oracle Text 12.1.0.2.0 VALID Oracle Workspace Manager 12.1.0.2.0 VALID Oracle XML Database 12.1.0.2.0 VALID Oracle Database Catalog Views 12.1.0.2.0 VALID Oracle Database Packages and T 12.1.0.2.0 VALID JServer JAVA Virtual Machine 12.1.0.2.0 VALID Oracle XDK 12.1.0.2.0 VALID Oracle Database Java Packages 12.1.0.2.0 VALID OLAP Analytic Workspace 12.1.0.2.0 VALID Oracle OLAP API 12.1.0.2.0 VALID Oracle Real Application Cluste 12.1.0.2.0 OPTION OFF
End over
Some last things needs to be done. specificaly the new listener setup. Netca is the best tool for that . Warn if you doesn’t want to use default Oracle port 1521 you need to set parameter « local_listener » for the root container CDB1212. Sample for port number 1522 :
a) Add a line in tnsnames.ora ($ORACLE_HOME/network/admin new release) :
LISTENER_CDB1212 = (ADDRESS = (PROTOCOL = TCP)(HOST = ele1ole6)(PORT = 1522))
b) Set parameter « local_listener » for the database:
[oracle@ele1ole6 ~]$ . oraenv ORACLE_SID = [cdb1212] ? cdb1212 The Oracle base remains unchanged with value /u01/app/oracle [oracle@ele1ole6 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 20 09:10:32 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter system set local_listener="LISTENER_CDB1212" ; System altered. SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER_CDB1212
A little bit frustrated and disapointed to not have this direct upgrade option (myth push by Oracle people themself) There are still numerous benefis to use this technology . The main one is that you could have the same PDB database on two containers with different release, which let you some time for test purpose (as soon as you stay in read only mode, at least for one of them) . This configuration (same PDB on two containers) is not supported by Oracle, statement is you couldn’t have two pluggable database with the same name on two roots containers on the same server, but the trick is to have one different listener for each PDB. In fact the problem come frome the same service name for the PDBs. If we use a different port it works !
So no miracle this time for the DBAs but some new possibilities to tes, t like plugin of a PDB thrugh a DBLINK. Another story to share with you.
