Moving database to new ORACLE_HOME is a common dba task. Performing this task on an ODA will need an additional step knowing ODA Lite is using an internal Derby database for the metadata. ODA HA will not be a problem here, knowing we do not have any Derby database. Through this blog I would like to give you some guidance and work around to move database to a new home (of same major release). In this example we will move a database named mydb, with db_unique_name set to mydb_site1 from OraDB11204_home1 to OraDB11204_home2.

I would like to highlight that the following blog is showing the procedure to move a database between ORACLE_HOME of same major release. The new ORACLE_HOME would for example run additionnal patches. An upgrade between Oracle major release is not possible following this procedure, and you would need to use the appropriate odacli commands (odacli upgrade-database) in that case.
Last but not least, I also would like to strongly advise that updating manually the ODA repository should only be peformed after getting Oracle support guidance and agreement to do so. Neither the author (that’s me 🙂 ) nor dbi services 😉 would be responsible for any issue or consequence following commands described in this blog. This would be your own responsability. 😉

I’m running ODA release 12.2.1.3. The database version used in this exemple is an Oracle 11g, but would work exactly the same for any other version like Oracle 12c databases.

Curent database information

Let’s first get information on which dbhome our mydb database is running.

List dbhomes :
[root@oda tmp]# odacli list-dbhomes
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
ed0a6667-0d70-4113-8a5e-3afaf1976fc2 OraDB12102_home1 12.1.0.2.171017 (26914423, 26717470) /u01/app/oracle/product/12.1.0.2/dbhome_1 Configured
89f6687e-f575-45fc-91ef-5521374c54c0 OraDB11204_home1 11.2.0.4.171017 (26609929, 26392168) /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
8c6bc663-b064-445b-8a14-b7c46df9d1da OraDB12102_home3 12.1.0.2.171017 (26914423, 26717470) /u01/app/oracle/product/12.1.0.2/dbhome_3 Configured
9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8 OraDB11204_home2 11.2.0.4.171017 (26609929, 26392168) /u01/app/oracle/product/11.2.0.4/dbhome_2 Configured

List database information :
[root@oda tmp]# odacli list-databases
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3 mydb Si 11.2.0.4 false OLTP Odb1 ACFS Configured 89f6687e-f575-45fc-91ef-5521374c54c0

Our database is running on OraDB11204_home1 Oracle home.

Moving database to new home

Let’s move mydb database on OraDB11204_home2.

    The process to link the database to a new home is quite simple and would just be easily done by :

  1. Moving the instance parameter file and password file to the new oracle home
  2. Updating the listener configuration by inserting the new oracle home in case static registration is used
  3. Changing grid cluster information using srvctl command

First we need to stop the database :
oracle@oda:/u01/app/oracle/product/11.2.0.4/dbhome_2/dbs/ [mydb] srvctl stop database -d mydb_site1

We can list the current grid configuration :
oracle@oda:/u01/app/oracle/product/11.2.0.4/dbhome_2/dbs/ [mydb] srvctl config database -d mydb_site1
Database unique name: mydb_site1
Database name: mydb
Oracle home: /u01/app/oracle/product/11.2.0.4/dbhome_1
Oracle user: oracle
Spfile: /u02/app/oracle/oradata/mydb_site1/dbs/spfilemydb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: mydb_site1
Database instance: mydb
Disk Groups:
Mount point paths: /u02/app/oracle/oradata/mydb_site1,/u03/app/oracle/
Services:
Type: SINGLE
Database is administrator managed

As we can see the grid cluster database is referring the current dbhome. Let’s update it to have it linked to new oracle home :
oracle@oda:/u01/app/oracle/product/11.2.0.4/dbhome_2/dbs/ [mydb] srvctl modify database -d mydb_site1 -o /u01/app/oracle/product/11.2.0.4/dbhome_2

Note that if you are using Oracle 12c, the srvctl command option might differ. Use :
-db for database name
-oraclehome for database oracle home
-pwfile for password file

With Oracle 12c database you will have to specify the change for the password file as well in case it is stored in $ORACLE_HOME/dbs folder.

We can check the new grid database configuration :
oracle@oda:/u01/app/oracle/product/11.2.0.4/dbhome_2/dbs/ [mydb] srvctl config database -d mydb_site1
Database unique name: mydb_site1
Database name: mydb
Oracle home: /u01/app/oracle/product/11.2.0.4/dbhome_2
Oracle user: oracle
Spfile: /u02/app/oracle/oradata/mydb_site1/dbs/spfilemydb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: mydb_site1
Database instance: mydb
Disk Groups:
Mount point paths: /u02/app/oracle/oradata/mydb_site1,/u03/app/oracle/
Services:
Type: SINGLE
Database is administrator managed

And we can start our database again :
oracle@oda:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [mydb] srvctl start database -d mydb_site1

Our database will now successfully be running on OraDB11204_home2.

We can check and see that dcs agent has successfully updated the oratab file :
oracle@oda:/tmp/ [mydb] grep mydb /etc/oratab
mydb:/u01/app/oracle/product/11.2.0.4/dbhome_2:N # line added by Agent

Our ORACLE_HOME env variable will now be :
oracle@oda01-p:/tmp/ [mydb] echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/dbhome_2

Are we done? No, let’s check how the ODA will display the new updated information.

Checking ODA metadata information

List dbhomes :
[root@oda tmp]# odacli list-dbhomes
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
ed0a6667-0d70-4113-8a5e-3afaf1976fc2 OraDB12102_home1 12.1.0.2.171017 (26914423, 26717470) /u01/app/oracle/product/12.1.0.2/dbhome_1 Configured
89f6687e-f575-45fc-91ef-5521374c54c0 OraDB11204_home1 11.2.0.4.171017 (26609929, 26392168) /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
8c6bc663-b064-445b-8a14-b7c46df9d1da OraDB12102_home3 12.1.0.2.171017 (26914423, 26717470) /u01/app/oracle/product/12.1.0.2/dbhome_3 Configured
9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8 OraDB11204_home2 11.2.0.4.171017 (26609929, 26392168) /u01/app/oracle/product/11.2.0.4/dbhome_2 Configured

List database information :
[root@oda tmp]# odacli list-databases
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3 mydb Si 11.2.0.4 false OLTP Odb1 ACFS Configured 89f6687e-f575-45fc-91ef-5521374c54c0

As we can see, ODA metadata coming from the derby database will still show mydb database linked to OraDB11204_home1.

Updating ODA metadata

Let’s update derby database to reflect the changes.

You can get your current appliance version by running the command :
odacli describe-component

ODA version 18.3 or higher

If you are running ODA version 18.3 or higher you can use following command to move a database from one database home to another database home of same base version :
odacli modify-database -i -dh

This command might not be successfull if your database was initially created as instance only :

[root@oda tmp]# odacli modify-database -i f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3 -dh 9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8
DCS-10045:Validation error encountered: Changing the database home is not allowed for an instance only database.

ODA version lower than 18.3

If you are running a lower version of ODA, you will need to update the derby DB manually. I would strongly recommend to act carefully on the derby database to make sure not to corrupt the ODA. I would also encourage you to get Oracle support guidance in case you need to act on your production ODA.
The next steps will describe how to update the derby DB manually.

1) Stop the DCS Agent

[root@oda ~]# initctl stop initdcsagent
initdcsagent stop/waiting

[root@oda ~]# ps -ef | grep dcs-agent | grep -v grep
[root@oda ~]#

2) Copy the derby Database

It is important to backup the repository and to apply the changes on the backup in order to keep the original version unchanged in case of trouble.

Go in the derby db repository folder :
[root@oda tmp]# cd /opt/oracle/dcs/repo

List current repository folder :
[root@oda repo]# ls -l
total 24
-rw-r--r-- 1 root root 1149 Aug 27 11:57 derby.log
drwxr-xr-x 4 root root 4096 Aug 27 15:32 node_0
drwxr-xr-x 4 root root 4096 Aug 12 16:18 node_0_orig_12082019_1619
drwxr-xr-x 4 root root 4096 Aug 26 11:31 node_0_orig_26082019_1132
drwxr-xr-x 4 root root 4096 Aug 26 15:27 node_0_orig_26082019_1528
drwxr-xr-x 4 root root 4096 Aug 27 11:57 node_0_orig_27082019_1158

Backup the repository (we will apply the changes on the backup repository to keep the original so far unchanged) :
[root@oda repo]# cp -rp node_0 node_0_backup_27082019_1533

List current repository folder :
[root@oda repo]# ls -l
total 28
-rw-r--r-- 1 root root 1149 Aug 27 11:57 derby.log
drwxr-xr-x 4 root root 4096 Aug 27 15:32 node_0
drwxr-xr-x 4 root root 4096 Aug 27 15:32 node_0_backup_27082019_1533
drwxr-xr-x 4 root root 4096 Aug 12 16:18 node_0_orig_12082019_1619
drwxr-xr-x 4 root root 4096 Aug 26 11:31 node_0_orig_26082019_1132
drwxr-xr-x 4 root root 4096 Aug 26 15:27 node_0_orig_26082019_1528
drwxr-xr-x 4 root root 4096 Aug 27 11:57 node_0_orig_27082019_1158

3) Start DCS Agent

[root@oda repo]# initctl start initdcsagent
initdcsagent start/running, process 45530

[root@oda repo]# ps -ef | grep dcs-agent | grep -v grep
root 45530 1 99 15:33 ? 00:00:10 java -Xms128m -Xmx512m -XX:MetaspaceSize=128m -XX:MaxMetaspaceSize=512m -XX:+DisableExplicitGC -XX:ParallelGCThreads=4 -XX:+PrintGCDetails -XX:+PrintHeapAtGC -XX:+PrintTenuringDistribution -XX:+PrintGCTimeStamps -XX:+PrintGCDateStamps -Xloggc:/opt/oracle/dcs/log/gc-dcs-agent-%t-%p.log -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=10M -Doracle.security.jps.config=/opt/oracle/dcs/agent/jps-config.xml -jar /opt/oracle/dcs/bin/dcs-agent-2.4.12-oda-SNAPSHOT.jar server /opt/oracle/dcs/conf/dcs-agent.json
[root@oda repo]#

4) Update metadata information

We now need to connect to the Derby backup database and make home id changes for the specific database.

Let’s connect to the derby database :
[root@oda repo]# /usr/java/jdk1.8.0_161/db/bin/ij
ij version 10.11
ij> connect 'jdbc:derby:node_0_backup_27082019_1533';

Let’s check current metadata information :
ij> select DBHOMEID from DB where ID='f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3';
DBHOMEID
--------------------------------------------------------------------------------------------------------------------------------
89f6687e-f575-45fc-91ef-5521374c54c0
1 row selected

Let’s update metadata according the home changes :
ij> update DB set DBHOMEID='9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8' where ID='f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3';
1 row inserted/updated/deleted

Let’s check the updated information :
ij> select DBHOMEID from DB where ID='f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3';
DBHOMEID
--------------------------------------------------------------------------------------------------------------------------------
9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8
1 row selected

Let’s commit the changes :
ij> commit;

And finally exit :
ij> exit;

5) Stop the DCS Agent

[root@oda repo]# initctl stop initdcsagent
initdcsagent stop/waiting

[root@oda repo]# ps -ef | grep dcs-agent | grep -v grep
[root@oda repo]#

6) Apply the changes in production

In this step, we will rename the original repository to keep a backup and put our changes in production.

List current repository folder :
[root@oda repo]# ls -ltrh
total 28K
drwxr-xr-x 4 root root 4.0K Aug 12 16:18 node_0_orig_12082019_1619
drwxr-xr-x 4 root root 4.0K Aug 26 11:31 node_0_orig_26082019_1132
drwxr-xr-x 4 root root 4.0K Aug 26 15:27 node_0_orig_26082019_1528
drwxr-xr-x 4 root root 4.0K Aug 27 11:57 node_0_orig_27082019_1158
drwxr-xr-x 4 root root 4.0K Aug 27 15:35 node_0_backup_27082019_1533
-rw-r--r-- 1 root root 1.2K Aug 27 15:35 derby.log
drwxr-xr-x 4 root root 4.0K Aug 27 15:36 node_0

Backup the original database :
[root@oda repo]# mv node_0 node_0_orig_27082019_1536

Put our changes in production :
[root@oda repo]# mv node_0_backup_27082019_1533 node_0

Check the repository folder :
[root@oda repo]# ls -ltrh
total 28K
drwxr-xr-x 4 root root 4.0K Aug 12 16:18 node_0_orig_12082019_1619
drwxr-xr-x 4 root root 4.0K Aug 26 11:31 node_0_orig_26082019_1132
drwxr-xr-x 4 root root 4.0K Aug 26 15:27 node_0_orig_26082019_1528
drwxr-xr-x 4 root root 4.0K Aug 27 11:57 node_0_orig_27082019_1158
drwxr-xr-x 4 root root 4.0K Aug 27 15:35 node_0
-rw-r--r-- 1 root root 1.2K Aug 27 15:35 derby.log
drwxr-xr-x 4 root root 4.0K Aug 27 15:36 node_0_orig_27082019_1536

7) Start the DCS Agent

[root@oda repo]# initctl start initdcsagent
initdcsagent start/running, process 59703

[root@oda repo]# ps -ef | grep dcs-agent | grep -v grep
root 59703 1 99 15:37 ? 00:00:11 java -Xms128m -Xmx512m -XX:MetaspaceSize=128m -XX:MaxMetaspaceSize=512m -XX:+DisableExplicitGC -XX:ParallelGCThreads=4 -XX:+PrintGCDetails -XX:+PrintHeapAtGC -XX:+PrintTenuringDistribution -XX:+PrintGCTimeStamps -XX:+PrintGCDateStamps -Xloggc:/opt/oracle/dcs/log/gc-dcs-agent-%t-%p.log -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=10M -Doracle.security.jps.config=/opt/oracle/dcs/agent/jps-config.xml -jar /opt/oracle/dcs/bin/dcs-agent-2.4.12-oda-SNAPSHOT.jar server /opt/oracle/dcs/conf/dcs-agent.json
[root@oda repo]#

8) Check ODA metadata

Now we can check and see that derby database is showing correct metadata information.

Lsit dbhomes :
[root@oda repo]# odacli list-dbhomes
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
ed0a6667-0d70-4113-8a5e-3afaf1976fc2 OraDB12102_home1 12.1.0.2.171017 (26914423, 26717470) /u01/app/oracle/product/12.1.0.2/dbhome_1 Configured
89f6687e-f575-45fc-91ef-5521374c54c0 OraDB11204_home1 11.2.0.4.171017 (26609929, 26392168) /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
8c6bc663-b064-445b-8a14-b7c46df9d1da OraDB12102_home3 12.1.0.2.171017 (26914423, 26717470) /u01/app/oracle/product/12.1.0.2/dbhome_3 Configured
9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8 OraDB11204_home2 11.2.0.4.171017 (26609929, 26392168) /u01/app/oracle/product/11.2.0.4/dbhome_2 Configured

Check new medata database information :
[root@oda repo]# odacli list-databases
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3 mydb Si 11.2.0.4 false OLTP Odb1 ACFS Configured 9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8

[root@oda repo]# odacli describe-database -i f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3
Database details
----------------------------------------------------------------
ID: f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3
Description: mydb
DB Name: mydb
DB Version: 11.2.0.4
DB Type: Si
DB Edition: EE
DBID:
Instance Only Database: true
CDB: false
PDB Name:
PDB Admin User Name:
Class: OLTP
Shape: Odb1
Storage: ACFS
CharacterSet: AL32UTF8
National CharacterSet: AL16UTF16
Language: AMERICAN
Territory: AMERICA
Home ID: 9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8
Console Enabled: false
Level 0 Backup Day: Sunday
AutoBackup Disabled: false
Created: June 11, 2019 2:46:35 PM CEST
DB Domain Name: in-kon.ch

Conclusion

Database is now running on new oracle home and ODA metadata information are up to date. Updating metada might be very important for further database upgrade or further database deletion that will be performed with odacli commands. Otherwise next commands might failed.