The 12.2 finally arrived on ODA and is now available on all generations. Modern ODAs are now supporting 11.2.0.4, 12.1.0.2 and 12.2.0.1 database engines, and these 3 versions can work together without any problem.
You probably plan to upgrade some old databases to the latest engine, at least those still running on 11.2. As you may know, 11.2 is no more supported with premier support since January 2015: it’s time to think about an upgrade. Note that premier support for 12.1 will end in July 2018. Actually, running 11.2 and 12.1 databases will need extended support this year. And this extended support is not free, as you can imagine. There is still an exception for 11.2.0.4, Oracle is offering extended support to his customers until the end of 2018.
Database upgrades have always been a lot of work, and often paired with a platform change. You need to recreate the databases, the tablespaces, export and import the data with datapump, correct the problems, and so on. Sometimes you can restore the old database to the new server with RMAN, but it’s only possible if the old engine is supported on your brand new server/OS combination.
As ODA is a longer term platform, you can think about ugrading the database directly on the appliance. Few years ago you should have been using dbua or catupgr, but now latest ODA package is including a tool for one command database upgrade. Let’s try it!
odacli, the ODA Client Line Interface, has a new option: upgrade-database. Parameters are very limited:
[root@oda-dbi01 2018-02-19]# odacli upgrade-database -h Usage: upgrade-database [options] Options: --databaseids, -i Database IDs to be upgraded Default: [] * --destDbHomeId, -to DB HOME ID of the destination --help, -h get help --json, -j json output --sourceDbHomeId, -from DB HOME ID of the source
You need to provide the database identifier (ODA stores a repository of all databases, db homes, jobs in a JavaDB/DerbyDB database) and the destination db home identifier you want to upgrade to. The source db home id is optional as Oracle can determine it quite easily. There is no other option (for the moment): no pre-backup (advised) and no storage migration (switch between acfs and ASM) for example.
Imagine you have an 11.2.0.4 database you want to upgrade to 12.2.0.1. Look for the id of your database ODAWS11:
[root@oda-dbi01 2018-02-19]# odacli list-databases ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID ---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ---------------------------------------- 45ce9de7-3115-45b0-97b0-1384b8401e69 ODAWS Si 12.2.0.1 false OLTP odb2 ASM Configured 1ca87df9-4691-47ed-90a9-2a794128539d a948a32c-1cf2-42c8-88c6-88fd9463b297 DBTEST1 Si 12.2.0.1 false OLTP odb1s ACFS Configured 1ca87df9-4691-47ed-90a9-2a794128539d de281792-1904-4536-b42c-8a55df489b73 ODAWS11 Si 11.2.0.4 false OLTP odb2 ACFS Configured 72023166-a39c-4a93-98b7-d552029b2eeaodacli create-dbhome -v 12.1.0.2.171017
Note that this database is configured with acfs, as 11.2 databases cannot be stored directly in an ASM 12c.
You can upgrade this database to an existing db home only: if you want to upgrade it to a new home, just create this new home, for example:
[root@oda-dbi01 2018-02-19]# odacli create-dbhome -v 12.1.0.2.171017
If you want to use an existing home, just pick the db home id, for example here the one used by ODAWS database.
Let’s do the upgrade:
[root@oda-dbi01 2018-02-19]# odacli upgrade-database -i de281792-1904-4536-b42c-8a55df489b73 -to 1ca87df9-4691-47ed-90a9-2a794128539d { "jobId" : "782e65fd-8b2b-4d16-a542-1f5b2b78d308", "status" : "Created", "message" : null, "reports" : [ ], "createTimestamp" : "February 19, 2018 17:40:58 PM CET", "resourceList" : [ ], "description" : "Database service upgrade with db ids: [de281792-1904-4536-b42c-8a55df489b73]", "updatedTime" : "February 19, 2018 17:40:58 PM CET" }
odacli will schedule a job for that, as for other operations. You can follow the job with describe-job:
[root@oda-dbi01 2018-02-19]# odacli describe-job -i 782e65fd-8b2b-4d16-a542-1f5b2b78d308 Job details ---------------------------------------------------------------- ID: 782e65fd-8b2b-4d16-a542-1f5b2b78d308 Description: Database service upgrade with db ids: [de281792-1904-4536-b42c-8a55df489b73] Status: Running Created: February 19, 2018 5:40:58 PM CET Message: Task Name Start Time End Time Status -------------------------------------------------- ----------------------------------- ----------------------------------- ---------- Setting up ssh equivalance February 19, 2018 5:40:58 PM CET February 19, 2018 5:40:58 PM CET Success Database Upgrade February 19, 2018 5:40:58 PM CET February 19, 2018 5:40:58 PM CET Running
You can also look at the database alert.log file during the operation.
Be patient! Database upgrade is taking time, at least 20 minutes for an empty database. And it seems that other jobs planned during the upgrade are in waiting state (like a create-database for example).
[root@oda-dbi01 2018-02-19]# odacli describe-job -i 782e65fd-8b2b-4d16-a542-1f5b2b78d308 Job details ---------------------------------------------------------------- ID: 782e65fd-8b2b-4d16-a542-1f5b2b78d308 Description: Database service upgrade with db ids: [de281792-1904-4536-b42c-8a55df489b73] Status: Running Created: February 19, 2018 5:40:58 PM CET Message: Task Name Start Time End Time Status -------------------------------------------------- ----------------------------------- ----------------------------------- ---------- Setting up ssh equivalance February 19, 2018 5:40:58 PM CET February 19, 2018 5:40:58 PM CET Success Database Upgrade February 19, 2018 5:40:58 PM CET February 19, 2018 6:01:37 PM CET Success
Now the upgrade seems OK, let’s check that:
su - oracle . oraenv <<< ODAWS11 oracle@oda-dbi01:/home/oracle/ # sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 19 18:01:49 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select instance_name, version from v$instance; INSTANCE_NAME VERSION ---------------- ----------------- ODAWS11 12.2.0.1.0 sho parameter spfile NAME TYPE VALUE -------------------- -------- --------------------------------------------------------------- spfile string /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/spfileODAWS11.ora
Even the spfile has been moved to new home, quite nice.
Let’s check the repository:
[root@oda-dbi01 ~]# odacli list-databases ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID ---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ---------------------------------------- 45ce9de7-3115-45b0-97b0-1384b8401e69 ODAWS Si 12.2.0.1 false OLTP odb2 ASM Configured 1ca87df9-4691-47ed-90a9-2a794128539d a948a32c-1cf2-42c8-88c6-88fd9463b297 DBTEST1 Si 12.2.0.1 false OLTP odb1s ACFS Configured 1ca87df9-4691-47ed-90a9-2a794128539d de281792-1904-4536-b42c-8a55df489b73 ODAWS11 Si 12.2.0.1 false OLTP odb2 ACFS Configured 1ca87df9-4691-47ed-90a9-2a794128539d
Everything looks fine!
Now let’s test the upgrade with a 12.1 database, ODAWS12. This one is using ASM storage:
[root@oda-dbi01 ~]# odacli list-databases ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID ---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ---------------------------------------- 45ce9de7-3115-45b0-97b0-1384b8401e69 ODAWS Si 12.2.0.1 false OLTP odb2 ASM Configured 1ca87df9-4691-47ed-90a9-2a794128539d a948a32c-1cf2-42c8-88c6-88fd9463b297 DBTEST1 Si 12.2.0.1 false OLTP odb1s ACFS Configured 1ca87df9-4691-47ed-90a9-2a794128539d de281792-1904-4536-b42c-8a55df489b73 ODAWS11 Si 12.2.0.1 false OLTP odb2 ACFS Configured 1ca87df9-4691-47ed-90a9-2a794128539d 0276326c-cb6d-4246-9943-8289d29d6a4f DBTEST2 Si 12.2.0.1 false OLTP odb1s ACFS Configured 7d2bbaa0-da3c-4455-abee-6bf4ff2d2630 24821a48-7474-4a8b-8f36-afca399b6def ODAWS12 Si 12.1.0.2 false OLTP odb2 ASM Configured 520167d7-59c8-4732-80a6-cc32ef745cec [root@oda-dbi01 2018-02-19]# odacli upgrade-database -i 24821a48-7474-4a8b-8f36-afca399b6def -to 1ca87df9-4691-47ed-90a9-2a794128539d { "jobId" : "10a2a304-4e8e-4b82-acdc-e4c0aa8b21be", "status" : "Created", "message" : null, "reports" : [ ], "createTimestamp" : "February 19, 2018 18:36:17 PM CET", "resourceList" : [ ], "description" : "Database service upgrade with db ids: [24821a48-7474-4a8b-8f36-afca399b6def]", "updatedTime" : "February 19, 2018 18:36:17 PM CET" } [root@oda-dbi01 ~]# odacli list-databases ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID ---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ---------------------------------------- 45ce9de7-3115-45b0-97b0-1384b8401e69 ODAWS Si 12.2.0.1 false OLTP odb2 ASM Configured 1ca87df9-4691-47ed-90a9-2a794128539d a948a32c-1cf2-42c8-88c6-88fd9463b297 DBTEST1 Si 12.2.0.1 false OLTP odb1s ACFS Configured 1ca87df9-4691-47ed-90a9-2a794128539d de281792-1904-4536-b42c-8a55df489b73 ODAWS11 Si 12.2.0.1 false OLTP odb2 ACFS Configured 1ca87df9-4691-47ed-90a9-2a794128539d 0276326c-cb6d-4246-9943-8289d29d6a4f DBTEST2 Si 12.2.0.1 false OLTP odb1s ACFS Configured 7d2bbaa0-da3c-4455-abee-6bf4ff2d2630 24821a48-7474-4a8b-8f36-afca399b6def ODAWS12 Si 12.1.0.2 false OLTP odb2 ASM Updating 520167d7-59c8-4732-80a6-cc32ef745cec [root@oda-dbi01 2018-02-19]# odacli describe-job -i 10a2a304-4e8e-4b82-acdc-e4c0aa8b21be Job details ---------------------------------------------------------------- ID: 10a2a304-4e8e-4b82-acdc-e4c0aa8b21be Description: Database service upgrade with db ids: [24821a48-7474-4a8b-8f36-afca399b6def] Status: Running Created: February 19, 2018 6:36:17 PM CET Message: Task Name Start Time End Time Status -------------------------------------------------- ----------------------------------- ----------------------------------- ---------- Setting up ssh equivalance February 19, 2018 6:36:17 PM CET February 19, 2018 6:36:17 PM CET Success Database Upgrade February 19, 2018 6:36:17 PM CET February 19, 2018 6:58:05 PM CET Success ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID ---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ---------------------------------------- 45ce9de7-3115-45b0-97b0-1384b8401e69 ODAWS Si 12.2.0.1 false OLTP odb2 ASM Configured 1ca87df9-4691-47ed-90a9-2a794128539d a948a32c-1cf2-42c8-88c6-88fd9463b297 DBTEST1 Si 12.2.0.1 false OLTP odb1s ACFS Configured 1ca87df9-4691-47ed-90a9-2a794128539d de281792-1904-4536-b42c-8a55df489b73 ODAWS11 Si 12.2.0.1 false OLTP odb2 ACFS Configured 1ca87df9-4691-47ed-90a9-2a794128539d 0276326c-cb6d-4246-9943-8289d29d6a4f DBTEST2 Si 12.2.0.1 false OLTP odb1s ACFS Configured 7d2bbaa0-da3c-4455-abee-6bf4ff2d2630 24821a48-7474-4a8b-8f36-afca399b6def ODAWS12 Si 12.2.0.1 false OLTP odb2 ASM Configured 1ca87df9-4691-47ed-90a9-2a794128539d su - oracle . oraenv <<< ODAWS12 oracle@oda-dbi01:/home/oracle/ # sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 19 18:59:08 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select instance_name, version from v$instance; INSTANCE_NAME VERSION ---------------- ----------------- ODAWS12 12.2.0.1.0 SQL> sho parameter spfile NAME TYPE VALUE ------------------ ---------- --------------------------------------------- spfile string +DATA/ODAWS12/PARAMETERFILE/spfileodaws12.ora
It also worked fine with an 12.1 database: and it also took about 20 minutes for an empty database.
You may have noticed that it’s possible to upgrade several databases in the same time by providing multiple database id. Not sure if you would do that in real life 🙂
upgrade-database is also available on ODA that are still using oakcli (nowadays only virtualized ODA I think), but as oakcli has no repository, database id has to be replaced by database name, and db home id by the name registered in classic oraInventory, for example:
oakcli upgrade database -db ODAWS11 -to OraDb12201_home1
This great feature will not revolutionize your DBA life, but it should help to upgrade your database with minimum effort.