Infrastructure at your Service

Jérôme Dubar

One command database upgrade on ODA

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.

 

Leave a Reply

Jérôme Dubar
Jérôme Dubar