2 customers requested a bit more explanation on how to:
Convert and Plugin an Oracle 12.1 non-CDB database into an Oracle 12.2 container database
Cool, I like that 🙂 I will do this right away and use the approach to Upgrade first, then Plugin afterwards.
Upgrading an Oracle 12.1.0.2 database to Oracle 12.2.0.1
First the preupgrade.jar must be executed – please download always the most recent version from MOS Note:884522.1 as the version we publish is usually newer including important improvements (and fixes) than the version you’ll get with the release drop. We are working on including always the newest drop with future PSUs and BPs.
$ java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar TEXT TERMINAL Report generated by Oracle Database Pre-Upgrade Information Tool Version 12.2.0.1.0 Upgrade-To version: 12.2.0.1.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: DB12 Container Name: DB12 Container ID: 0 Version: 12.1.0.2.0 Compatible: 12.1.0.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 18 Database log mode: NOARCHIVELOG Readonly: FALSE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID Oracle Workspace Manager [to be upgraded] VALID Oracle XML Database [to be upgraded] VALID ============== BEFORE UPGRADE ============== Run /preupgrade_fixups.sql to complete all of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ + Adjust TABLESPACE SIZES as needed. Auto 12.2.0.1.0 Tablespace Size Extend Min Size Action ---------- ---------- -------- ---------- ------ SYSAUX 550 MB DISABLED 500 MB None SYSTEM 700 MB ENABLED 765 MB None TEMP 20 MB ENABLED 150 MB None UNDOTBS1 295 MB ENABLED 400 MB None Note that 12.2.0.1.0 minimum sizes are estimates. If you plan to upgrade multiple pluggable databases concurrently, then you must ensure that the UNDO tablespace size is equal to at least the number of pluggable databases that you upgrade concurrently, multiplied by that minimum. Failing to allocate sufficient space can cause the upgrade to fail. RECOMMENDED ACTIONS =================== + (AUTOFIXUP) Gather stale data dictionary statistics prior to database upgrade in off-peak time using: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Dictionary statistics do not exist or are stale (not up-to-date). Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade. For information on managing optimizer statistics, refer to the 12.1.0.2 Oracle Database SQL Tuning Guide. ============= AFTER UPGRADE ============= Run /postupgrade_fixups.sql to complete all of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== + Upgrade the database time zone version using the DBMS_DST package. The database is using timezone datafile version 18 and the target 12.2.0.1.0 database ships with timezone datafile version 26. Oracle recommends using the most recent timezone data. For further information, refer to My Oracle Support Note 1585343.1. + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Oracle recommends gathering dictionary statistics after upgrade. Dictionary statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. After a database upgrade, statistics need to be re-gathered as there can now be tables that have significantly changed during the upgrade or new tables that do not have statistics gathered yet. Preupgrade generated files: /u01/app/oracle/cfgtoollogs/DB12/preupgrade/preupgrade_fixups.sql /u01/app/oracle/cfgtoollogs/DB12/preupgrade/postupgrade_fixups.sql
Next step is to follow the guidelines displayed by the output, i.e. changing parameters, gathering stats, running the preupgrade_fixups.sql etc., then shutting down the database and copying the SPFILE and creating a new password file in the 12.2 Oracle Home’s directory.
[oracle@localhost ~]$ . db121
[DB12] oracle@localhost:~
$ s
SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 7 14:09:25 2017Copyright (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> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL> @/u01/app/oracle/cfgtoollogs/DB12/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 3
Generated on: 2017-03-07 13:46:51
For Source Database: DB12
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
dictionary_stats Passed None
PL/SQL procedure successfully completed.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[DB12] oracle@localhost:~
$ cp /u01/app/oracle/product/12.1.0.2/dbs/spfileDB12.ora $OH12/dbs
[DB12] oracle@localhost:~
$ cp /u01/app/oracle/product/12.1.0.2/dbs/orapwDB12 $OH12/dbs
[DB12] oracle@localhost:~
-- it's Best Practice to create a new password file instead of copying the old one!
Now the database is ready to get upgraded to Oracle Database 12.2.0.1.
Database Upgrade to Oracle Database 12.2.0.1
Database must be in STARTUP UPGRADE mode – and the upgrade gets executed from the command line in my case. Alternative would be the DBUA of course. You could also use the shortcut “dbupgrade” instead of typing all the characters below.
[DB12] oracle@localhost:~ $ . db122 [DB12] oracle@localhost:~ $ s SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 14:16:20 2017Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 1048576000 bytes Fixed Size 8628640 bytes Variable Size 369100384 bytes Database Buffers 662700032 bytes Redo Buffers 8146944 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [DB12] oracle@localhost:~ $ cd /u01/app/oracle/product/12.2.0.1/rdbms/admin/ [DB12] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin $ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l /home/oracle catupgrd.sql Argument list for [catctl.pl] Run in c = 0 Do not run in C = 0 Input Directory d = 0 Echo OFF e = 1 Simulate E = 0 Forced cleanup F = 0 Log Id i = 0 Child Process I = 0 Log Dir l = /home/oracle Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 6 SQL PDB Process Count N = 0 Open Mode Normal o = 0 Start Phase p = 0 End Phase P = 0 Reverse Order r = 0 AutoUpgrade Resume R = 0 Script s = 0 Serial Run S = 0 RO User Tablespaces T = 0 Display Phases y = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 catctl.pl VERSION: [12.2.0.1.0] STATUS: [production] BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170107] /u01/app/oracle/product/12.2.0.1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1] /u01/app/oracle/product/12.2.0.1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1] catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1] Analyzing file /u01/app/oracle/product/12.2.0.1/rdbms/admin/catupgrd.sql Log file directory = [/home/oracle] catcon: ALL catcon-related output will be written to [/home/oracle/catupgrd_catcon_13547.lst] catcon: See [/home/oracle/catupgrd*.log] files for output generated by scripts catcon: See [/home/oracle/catupgrd_*.lst] files for spool files, if any Number of Cpus = 2 Database Name = DB12 DataBase Version = 12.1.0.2.0 Parallel SQL Process Count = 6 Components in [DB12] Installed [CATALOG CATPROC OWM XDB] Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM RAC SDO WK XML XOQ] ------------------------------------------------------ Phases [0-115] Start Time:[2017_03_07 14:17:11] ------------------------------------------------------ *********** Executing Change Scripts *********** Serial Phase #:0 [DB12] Files:1 Time: 29s *************** Catalog Core SQL *************** Serial Phase #:1 [DB12] Files:5 Time: 43s Restart Phase #:2 [DB12] Files:1 Time: 1s *********** Catalog Tables and Views *********** Parallel Phase #:3 [DB12] Files:19 Time: 22s Restart Phase #:4 [DB12] Files:1 Time: 0s ************* Catalog Final Scripts ************ Serial Phase #:5 [DB12] Files:6 Time: 16s ***************** Catproc Start **************** Serial Phase #:6 [DB12] Files:1 Time: 12s ***************** Catproc Types **************** Serial Phase #:7 [DB12] Files:2 Time: 11s Restart Phase #:8 [DB12] Files:1 Time: 0s **************** Catproc Tables **************** Parallel Phase #:9 [DB12] Files:69 Time: 38s Restart Phase #:10 [DB12] Files:1 Time: 0s ************* Catproc Package Specs ************ Serial Phase #:11 [DB12] Files:1 Time: 37s Restart Phase #:12 [DB12] Files:1 Time: 0s ************** Catproc Procedures ************** Parallel Phase #:13 [DB12] Files:97 Time: 12s Restart Phase #:14 [DB12] Files:1 Time: 1s Parallel Phase #:15 [DB12] Files:118 Time: 21s Restart Phase #:16 [DB12] Files:1 Time: 1s Serial Phase #:17 [DB12] Files:13 Time: 2s Restart Phase #:18 [DB12] Files:1 Time: 0s ***************** Catproc Views **************** Parallel Phase #:19 [DB12] Files:33 Time: 31s Restart Phase #:20 [DB12] Files:1 Time: 0s Serial Phase #:21 [DB12] Files:3 Time: 7s Restart Phase #:22 [DB12] Files:1 Time: 0s Parallel Phase #:23 [DB12] Files:24 Time: 117s Restart Phase #:24 [DB12] Files:1 Time: 0s Parallel Phase #:25 [DB12] Files:11 Time: 67s Restart Phase #:26 [DB12] Files:1 Time: 0s Serial Phase #:27 [DB12] Files:1 Time: 0s Serial Phase #:28 [DB12] Files:3 Time: 3s Serial Phase #:29 [DB12] Files:1 Time: 0s Restart Phase #:30 [DB12] Files:1 Time: 0s *************** Catproc CDB Views ************** Serial Phase #:31 [DB12] Files:1 Time: 1s Restart Phase #:32 [DB12] Files:1 Time: 0s Serial Phase #:34 [DB12] Files:1 Time: 0s ***************** Catproc PLBs ***************** Serial Phase #:35 [DB12] Files:283 Time: 18s Serial Phase #:36 [DB12] Files:1 Time: 0s Restart Phase #:37 [DB12] Files:1 Time: 0s Serial Phase #:38 [DB12] Files:1 Time: 5s Restart Phase #:39 [DB12] Files:1 Time: 0s *************** Catproc DataPump *************** Serial Phase #:40 [DB12] Files:3 Time: 48s Restart Phase #:41 [DB12] Files:1 Time: 1s ****************** Catproc SQL ***************** Parallel Phase #:42 [DB12] Files:13 Time: 69s Restart Phase #:43 [DB12] Files:1 Time: 0s Parallel Phase #:44 [DB12] Files:12 Time: 12s Restart Phase #:45 [DB12] Files:1 Time: 1s Parallel Phase #:46 [DB12] Files:2 Time: 0s Restart Phase #:47 [DB12] Files:1 Time: 0s ************* Final Catproc scripts ************ Serial Phase #:48 [DB12] Files:1 Time: 4s Restart Phase #:49 [DB12] Files:1 Time: 0s ************** Final RDBMS scripts ************* Serial Phase #:50 [DB12] Files:1 Time: 20s ************ Upgrade Component Start *********** Serial Phase #:51 [DB12] Files:1 Time: 0s Restart Phase #:52 [DB12] Files:1 Time: 0s **************** Upgrading Java **************** Serial Phase #:53 [DB12] Files:1 Time: 1s Restart Phase #:54 [DB12] Files:1 Time: 0s ***************** Upgrading XDK **************** Serial Phase #:55 [DB12] Files:1 Time: 0s Restart Phase #:56 [DB12] Files:1 Time: 1s ********* Upgrading APS,OLS,DV,CONTEXT ********* Serial Phase #:57 [DB12] Files:1 Time: 0s ***************** Upgrading XDB **************** Restart Phase #:58 [DB12] Files:1 Time: 0s Serial Phase #:60 [DB12] Files:3 Time: 8s Serial Phase #:61 [DB12] Files:3 Time: 5s Parallel Phase #:62 [DB12] Files:9 Time: 3s Parallel Phase #:63 [DB12] Files:24 Time: 4s Serial Phase #:64 [DB12] Files:4 Time: 6s Serial Phase #:65 [DB12] Files:1 Time: 0s Serial Phase #:66 [DB12] Files:30 Time: 3s Serial Phase #:67 [DB12] Files:1 Time: 0s Parallel Phase #:68 [DB12] Files:6 Time: 2s Serial Phase #:69 [DB12] Files:2 Time: 14s Serial Phase #:70 [DB12] Files:3 Time: 31s Restart Phase #:71 [DB12] Files:1 Time: 0s ********* Upgrading CATJAVA,OWM,MGW,RAC ******** Serial Phase #:72 [DB12] Files:1 Time: 32s **************** Upgrading ORDIM *************** Restart Phase #:73 [DB12] Files:1 Time: 0s Serial Phase #:75 [DB12] Files:1 Time: 1s Parallel Phase #:76 [DB12] Files:2 Time: 0s Serial Phase #:77 [DB12] Files:1 Time: 0s Restart Phase #:78 [DB12] Files:1 Time: 0s Parallel Phase #:79 [DB12] Files:2 Time: 1s Serial Phase #:80 [DB12] Files:2 Time: 0s ***************** Upgrading SDO **************** Restart Phase #:81 [DB12] Files:1 Time: 0s Serial Phase #:83 [DB12] Files:1 Time: 1s Serial Phase #:84 [DB12] Files:1 Time: 0s Restart Phase #:85 [DB12] Files:1 Time: 0s Serial Phase #:86 [DB12] Files:1 Time: 0s Restart Phase #:87 [DB12] Files:1 Time: 1s Parallel Phase #:88 [DB12] Files:3 Time: 0s Restart Phase #:89 [DB12] Files:1 Time: 0s Serial Phase #:90 [DB12] Files:1 Time: 1s Restart Phase #:91 [DB12] Files:1 Time: 0s Serial Phase #:92 [DB12] Files:1 Time: 0s Restart Phase #:93 [DB12] Files:1 Time: 1s Parallel Phase #:94 [DB12] Files:4 Time: 0s Restart Phase #:95 [DB12] Files:1 Time: 0s Serial Phase #:96 [DB12] Files:1 Time: 1s Restart Phase #:97 [DB12] Files:1 Time: 0s Serial Phase #:98 [DB12] Files:2 Time: 0s Restart Phase #:99 [DB12] Files:1 Time: 0s Serial Phase #:100 [DB12] Files:1 Time: 1s Restart Phase #:101 [DB12] Files:1 Time: 0s *********** Upgrading Misc. ODM, OLAP ********** Serial Phase #:102 [DB12] Files:1 Time: 0s **************** Upgrading APEX **************** Restart Phase #:103 [DB12] Files:1 Time: 1s Serial Phase #:104 [DB12] Files:1 Time: 0s Restart Phase #:105 [DB12] Files:1 Time: 0s *********** Final Component scripts *********** Serial Phase #:106 [DB12] Files:1 Time: 0s ************* Final Upgrade scripts ************ Serial Phase #:107 [DB12] Files:1 Time: 118s ********** End PDB Application Upgrade ********* Serial Phase #:108 [DB12] Files:1 Time: 0s ******************* Migration ****************** Serial Phase #:109 [DB12] Files:1 Time: 32s Serial Phase #:110 [DB12] Files:1 Time: 0s Serial Phase #:111 [DB12] Files:1 Time: 40s ***************** Post Upgrade ***************** Serial Phase #:112 [DB12] Files:1 Time: 73s **************** Summary report **************** Serial Phase #:113 [DB12] Files:1 Time: 0s Serial Phase #:114 [DB12] Files:1 Time: 0s Serial Phase #:115 [DB12] Files:1 Time: 22s ------------------------------------------------------ Phases [0-115] End Time:[2017_03_07 14:34:45] ------------------------------------------------------ Grand Total Time: 1055s LOG FILES: (/home/oracle/catupgrd*.log) Upgrade Summary Report Located in: /home/oracle/upg_summary.log Grand Total Upgrade Time: [0d:0h:17m:35s] [DB12] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin
Database DB12 is now upgraded to Oracle Database 12.2.0.1 but needs some post-upgrade treatments.
Post-Upgrade Treatments
Those include recompilation, postupgrade_fixups.sql and time zone adjustment.
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 14:36:06 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1048576000 bytes Fixed Size 8628640 bytes Variable Size 369100384 bytes Database Buffers 662700032 bytes Redo Buffers 8146944 bytes Database mounted. Database opened. SQL> @?/rdbms/admin/utlrp.sql TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2017-03-07 14:36:40 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2017-03-07 14:41:52 DOC> The following query reports the number of invalid objects. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of exceptions 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> Note: Typical compilation errors (due to coding errors) are not DOC> logged into this table: they go into DBA_ERRORS instead. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 Function created. PL/SQL procedure successfully completed. Function dropped. PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed. SQL> @/u01/app/oracle/cfgtoollogs/DB12/preupgrade/postupgrade_fixups.sql Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 12.2.0.1.0 Build: 3 Generated on: 2017-03-07 13:46:52 For Source Database: DB12 Source Database Version: 12.1.0.2.0 For Upgrade to Version: 12.2.0.1.0 Fixup Check Name Status Further DBA Action ---------- ------ ------------------ old_time_zones_exist Failed Manual fixup recommended. post_dictionary Passed None PL/SQL procedure successfully completed. Session altered. SQL> @/home/oracle/DST/upg_tzv_check.sql INFO: Starting with RDBMS DST update preparation. INFO: NO actual RDBMS DST update will be done by this script. INFO: If an ERROR occurs the script will EXIT sqlplus. INFO: Doing checks for known issues ... INFO: Database version is 12.2.0.1 . INFO: Database RDBMS DST version is DSTv18 . INFO: No known issues detected. INFO: Now detecting new RDBMS DST version. A prepare window has been successfully started. INFO: Newest RDBMS DST version detected is DSTv26 . INFO: Next step is checking all TSTZ data. INFO: It might take a while before any further output is seen ... A prepare window has been successfully ended. INFO: A newer RDBMS DST version than the one currently used is found. INFO: Note that NO DST update was yet done. INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update. INFO: Note that the upg_tzv_apply.sql script will INFO: restart the database 2 times WITHOUT any confirmation or prompt. SQL> @/home/oracle/DST/upg_tzv_apply.sql INFO: If an ERROR occurs the script will EXIT sqlplus. INFO: The database RDBMS DST version will be upda ted to DSTv26 . WARNING: This script will restart the database 2 times WARNING: WITHOUT asking ANY confirmation. WARNING: Hit control-c NOW if this is not intended. INFO: Restarting the database in UPGRADE mode to start the DST upgrade. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 1048576000 bytes Fixed Size 8628640 bytes Variable Size 369100384 bytes Database Buffers 662700032 bytes Redo Buffers 8146944 bytes Database mounted. Database opened. INFO: Starting the RDBMS DST upgrade. INFO: Upgrading all SYS owned TSTZ data. INFO: It might take time before any further output is seen ... An upgrade window has been successfully started. INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 1048576000 bytes Fixed Size 8628640 bytes Variable Size 369100384 bytes Database Buffers 662700032 bytes Redo Buffers 8146944 bytes Database mounted. Database opened. INFO: Upgrading all non-SYS TSTZ data. INFO: It might take time before any further output is seen ... INFO: Do NOT start any application yet that uses TSTZ data! INFO: Next is a list of all upgraded tables: Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S" Number of failures: 0 INFO: Total failures during update of TSTZ data: 0 . An upgrade window has been successfully ended. INFO: Your new Server RDBMS DST version is DSTv26 . INFO: The RDBMS DST update is successfully finished. INFO: Make sure to exit this sqlplus session. INFO: Do not use it for timezone related selects. SQL>
Database is now fully upgraded and ready to go. You may create fixed objects statistics a while after the database is up and running. But I can proceed now with the plugin operation.
Plugin the upgraded database as a PDB into a CDB
This step is simple and straight forward, though on part, the sanity script noncdb_to_pdb.sql, which will be only run once in the life span of a database and is irreversible (but restartable in Oracle Database 12.2.0.1 – it wasn’t before), may take a bit to complete depending on the number of objects and some other constraints.
First I’m creating the XML manifest file which basically described the physical layout of my database. The database has to be on read only mode – and it has to be at least an Oracle 12.1.0.1 database as otherwise the package DBMS_PDB wouldn’t exist.
$ . db122
[DB12] oracle@localhost:~
$ sSQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 22:58:38 2017Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 8628640 bytes
Variable Size 369100384 bytes
Database Buffers 662700032 bytes
Redo Buffers 8146944 bytes
Database mounted.
Database opened.
SQL> exec DBMS_PDB.DESCRIBE('/tmp/pdb1.xml');
PL/SQL procedure successfully completed.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Once the file has been created and the database is shutdown I’ll connect now into my already existing container database and progress from there. I will plugin now the DB12 database, give it a new name (not required but I’d recommend some obvious name identifying it as a PDB), doing a compatibility check (for errors please see PDB_PLUG_IN_VIOLATIONS) and then kick off the sanity script noncdb_to_pdb.sql.
[DB12] oracle@localhost:~
$ . cdb2
[CDB2] oracle@localhost:~
$ s
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 23:07:19 2017Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set serverout on
SQL> DECLARE
compatible CONSTANT VARCHAR2(3) := CASE
DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/tmp/pdb1.xml',
pdb_name => 'PDB1')
WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('Is UPGR compatible? ' || compatible);
END;
/
Is UPGR compatible? YES
PL/SQL procedure successfully completed.
SQL> create pluggable database PDB1 using '/tmp/pdb1.xml' nocopy tempfile reuse;
Pluggable database created.
SQL> alter session set container=PDB1;
Session altered.
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
SQL> startup
Pluggable database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL>
When I run the CREATE PLUGGABLE DATABASE command I use the NOCOPY option intentionally. But be aware that in this case where the databases stays in place and does not get moved you have to have a valid backup of the database as in case of failure you may be lost in outer space with no way back or forth.
In addition, after this operation is completed, the sanity script has been run successfully and the new PDB is OPEN you must take a fresh backup as all your archives and backups from “before” are useless from now on. Don’t forget this – it’s key!
Yep – all done!
Database DB12 got upgraded to Oracle Database 12.2.0.1 – and then plugged into CDB2 as a new PDB1.
–Mike
Mike,
Thanks for posting this, we will be migrating 12.1 Non-CDB/PDB to 12.2 multitenant sometime in 2017.
I have a couple of questions.
Will Exadata Quarterly Full Stack Download Patches for 12.2 be released this year?
Is the upgrade from 12.1 to 12.2 absolutely necessary prior to converting non-multitenant to multitenant on 12.2 or could these steps be reversed?
Thanks.
Peter,
I’d hope that the Exa Quarterly patches will appear – mid of April may be the time at the usual date.
If you have a 12.1 db you may plugin it as PDB as well and then upgrade it – I’d still prefer the upgrade-first, then plugin path as the first one is irreversible whereas 12.1plugin-then-upgrade allows you less fallback options.
Cheers, Mike
I will right away grab your rss feed as I can not in finding your email subscription link or e-newsletter service. Do you’ve any? Please let me realize so that I may subscribe. Thanks.
Not possible yet – but I will offer this soon. Just have to learn how to do it (read it right now) and which plugin I’ll use. Please give me a few weeks.
Cheers
Mike
Hello, I think your website might be having browser compatibility issues. When I look at your blog site in Ie, it looks fine but when opening in Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up! Other then that, terrific blog!
Thanks – I’m not finished yet with converting and reworking all posts – Mar 2017 – Aug 2015 is not done yet – older posts are.
If you see this again in 2 weeks (by then I should be done with the conversion from the old blog platform) please be so kind and drop me a message again. In between I’ll check if I experience the same.
Thanks for the heads-up
Mike
Hi Mike!
First of all thank you for the very nice and informative post!
I’d like to ask you about reducing the time of downtime. The most time consuming tasks which require full db outage are: catupgrd.sql + utlrp.sql + upg_tzv_apply.sql
We may improve these steps using logical transient standby like it was described in WP “Oracle Database Rolling Upgrades Using a Data Guard Physical Standby Database” and significantly save “time and money” to a couple of minutes of downtime during switchovers.
My main question is – how to reduce downtime for the step of converting noncdn to pdb (noncdb_to_pdb.sql)? If I’m not mistaken we cannot use such a method here?
Thank you!
Sergei,
unfortunately you are absolutely right. There’s no way to reduce this downtime for the noncdb_to_pdb.sql.
The alternative would be to use a different method to move to Multitenant (Data Pump, Transportable Tablespaces) and use Oracle GoldenGate for syncing source and destination.
Thanks,
Mike
Hi Mike!
I looked into noncdb_to_pdb.sql trying to find the steps which take most of the time and it seems possible candidats are:
exec dbms_pdb.noncdb_to_pdb(1);
@@?/rdbms/admin/reenable_indexes.sql
@@utlrp
exec dbms_pdb.noncdb_to_pdb(2);
Or may be I’m wrong and it’s better to just set optimizer_adaptive_features to false ?
Please share your experience how did you speed up the noncdb_to_pdb.sql if you had a chance?
Thank you in advance!
—
Best regards,
Sergei
Hi Sergei,
no, optimizer_adapative_* won’t help you in any way as far as I see and experienced it.
The recompilation is painful. And the index-re-enabling is painful as well.
I have my theory why this hasn’t been optimized so far.
But I fear there’s not much you can do except using a different technique to migrate into a PDB.
Cheers,
Mike
Mike,
Few questions:
Here are the steps I followed at a high level:
Upgrade 11g to 18c non-CDB by running dbupgrade
Create XML metadata from the upgraded 18c non-CDB
Plug it into a CDB.
a) After plugging in, I see that the PDB has Oracle built-in users like SYS, SYSTEM, DBSNMP and so on. What should I do with these user accounts? The password of these accounts was changed to the SYS/SYSTEM password of the CDB.
b) For plug in operation, I was testing the move command:
create pluggable database pdborcl using ‘/tmp/pdborcl.xml’ move tempfile reuse;
I found that after plug-in was complete, the datafiles of PDBORCL still remained in the ASM diskgroup. Oracle created a copy of all the datafiles inside the CDBORCL. This is not expected.
Run asmcmd.
cd dg_data
cd pdborcl
cd datafile
ls -l
all the datafiles are listed.
cd dg_data
cd cdborcl
cd
cd datafile
ls -l
all the datafiles are listed.
c) Also found that plug-in operation does not remove the non-CDB database from clusterware. If I try to use DBCA to delete the database, DBCA fails to delete the non-CDB database completely. Is there any documentation on how to do the cleanup after plug-in?
Thanks,
Arun
Hi Arun,
regarding your questions:
(1) Users
All Oracle provided users (SYS, SYSTEM, OLAPSYS, WMSYS etc) per definition are COMMON users. As COMMON users only exist in the CDB$ROOT you previous SYSTEM user for instance becomes obsolete during the plugin. Don’t misunderstand this: it does not get removed neither do objects or such get removed. But the control over these users is now handled solely from the CDB$ROOT. And hence you can’t have a SYSTEM in the CDB$ROOT with lets say password “oracle”, and at the same time your previous SYSTEM in the PDBORCL with the password “manager”.
(2) Move
Thanks for this hint – I wasn’t even aware that the MOVE command option exists. But it is there actually since Oracle 12.1, the first release of Multitenant. Hm …
I have no ASM but a file system in my test env. And in my case:
CREATE PLUGGABLE DATABASE db12 USING ‘/tmp/db12.xml’ MOVE FILE_NAME_CONVERT=(‘/u02/oradata/DB12′,’/u02/oradata/CDB2/db12’) TEMPFILE REUSE;
moves all datafiles but leaves the controlfiles, redologs (both expected) and the tempfile (not expected at first sight) in the old directory.
I fear you need to open an SR please.
(3) Clusterware
Oh yes, of course not. Always, when you do operation on command line, the clusterware wouldn’t take notice. The same happens when you do a command line upgrade (which I favor by 100% over DBUA). But then you end up with your clusterware commands to have the database resources configured correctly afterwards. This is what you encounter here as well.
Now using the DBCA to clean up the leftovers won’t work. The database is not there in its correct state. When you will use the DBCA to cleanup a database it will connect to it first, then issue the DROP DATABASE command, then cleanup on the OS side. The DROP DATABASE command will fail, and thus the DBCA operation will fail.
I haven’t seen a guide “how to clean up afterwards” even though I believe this would be VERY useful. My interpretation is that such things are not seen as “real challenges” and thus … well … we have the same situations with the need to DROP a pluggable database once you unplug in the previous CDB. And other tiny little nits like (why is my previous directory not cleaned up after I use the MOVE command). I can’t comment further on the blog but you can always drop me an email please, and I’ll forward it to the right (and very nice and helpful) people being responsible on it.
Hope this helps – cheers,
Mike
Mike,
Hello. I upgraded a database through DBUA from 11g to 12cR1 and I selected the “Upgrade Timezone Data” checkbox. Since Im still not clear on whether selecting that option means that I don’t need to perform any other manual steps related to timezone, I went ahead and launched @upg_tzv_check.sql but I got:
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues …
INFO: Database version is 12.1.0.2 .
INFO: Database RDBMS DST version is DSTv18 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
ERROR: No newer RDBMS DST patch has been detected.
ERROR: Check if a newer RDBMS DST patch is actually installed.
DECLARE
*
ERROR at line 1:
ORA-20070: Stopping script – see previous message …..
ORA-06512: at line 36
Does this mean DBUA already took care of timezone stuff?
If not, what does it mean?
Roberto,
yes it did. The default TZ version in 12.1.0.2 is V18. As the script detected V18 it didn’t go forward.
But I agree, the error message is very misleading. The script should instead tell you not “ERROR: No newer RDBMS DST patch has been detected.” but say: “INFO: No newer RDBMS DST patch has been detected. Nothing to do. Exiting.”
Thanks
Mike
Thank you. So just to clarify — When performing an upgrade though DBUA and checking ““Upgrade Timezone Data”, DBUA handles all the steps? Meaning both the time zone file and the data get updated?
Roberto.
Yes!
Cheers,
Mike