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.<\/p>\n
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.
\nLast 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 \ud83d\ude42 ) nor dbi services \ud83d\ude09 would be responsible for any issue or consequence following commands described in this blog. This would be your own responsability. \ud83d\ude09<\/p>\n
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.<\/p>\n
<\/p>\n
Let’s first get information on which dbhome our mydb database is running.<\/p>\n
List dbhomes : List database information : Our database is running on OraDB11204_home1 Oracle home.<\/p>\n Let’s move mydb database on OraDB11204_home2.<\/p>\n The process to link the database to a new home is quite simple and would just be easily done by :<\/p>\n First we need to stop the database : We can list the current grid configuration : 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 : Note that if you are using Oracle 12c, the srvctl command option might differ. Use : 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.<\/p>\n We can check the new grid database configuration : And we can start our database again : Our database will now successfully be running on OraDB11204_home2.<\/p>\n We can check and see that dcs agent has successfully updated the oratab file : Our ORACLE_HOME env variable will now be : Are we done? No, let’s check how the ODA will display the new updated information.<\/p>\n List dbhomes : List database information : As we can see, ODA metadata coming from the derby database will still show mydb database linked to OraDB11204_home1.<\/p>\n Let’s update derby database to reflect the changes.<\/p>\n You can get your current appliance version by running the command : 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 : This command might not be successfull if your database was initially created as instance only : <\/p>\n 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.<\/strong> [root@oda ~]# ps -ef | grep dcs-agent | grep -v grep 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.<\/p>\n Go in the derby db repository folder : List current repository folder : Backup the repository (we will apply the changes on the backup repository to keep the original so far unchanged) : List current repository folder : [root@oda repo]# ps -ef | grep dcs-agent | grep -v grep We now need to connect to the Derby backup database and make home id changes for the specific database.<\/p>\n Let’s connect to the derby database : Let’s check current metadata information : Let’s update metadata according the home changes : Let’s check the updated information : Let’s commit the changes : And finally exit : [root@oda repo]# ps -ef | grep dcs-agent | grep -v grep In this step, we will rename the original repository to keep a backup and put our changes in production.<\/p>\n List current repository folder : Backup the original database : Put our changes in production : Check the repository folder : [root@oda repo]# ps -ef | grep dcs-agent | grep -v grep Now we can check and see that derby database is showing correct metadata information.<\/p>\n Lsit dbhomes : Check new medata database information : [root@oda repo]# odacli describe-database -i f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":48,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[280,23,79,96],"type_dbi":[],"class_list":["post-12802","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-database","tag-dba","tag-oda","tag-oracle"],"acf":[],"yoast_head":"\n
\n[root@oda tmp]# odacli list-dbhomes
\nID Name DB Version Home Location Status
\n---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
\ned0a6667-0d70-4113-8a5e-3afaf1976fc2 OraDB12102_home1 12.1.0.2.171017 (26914423, 26717470) \/u01\/app\/oracle\/product\/12.1.0.2\/dbhome_1 Configured
\n89f6687e-f575-45fc-91ef-5521374c54c0 OraDB11204_home1 11.2.0.4.171017 (26609929, 26392168) \/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_1 Configured
\n8c6bc663-b064-445b-8a14-b7c46df9d1da OraDB12102_home3 12.1.0.2.171017 (26914423, 26717470) \/u01\/app\/oracle\/product\/12.1.0.2\/dbhome_3 Configured
\n9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8 OraDB11204_home2 11.2.0.4.171017 (26609929, 26392168) \/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_2 Configured<\/code><\/p>\n
\n[root@oda tmp]# odacli list-databases
\nID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
\n---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
\nf38f3a6c-987c-4e11-8cfa-af5cb66ff4e3 mydb Si 11.2.0.4 false OLTP Odb1 ACFS Configured 89f6687e-f575-45fc-91ef-5521374c54c0<\/code><\/p>\nMoving database to new home<\/h3>\n
\n
\noracle@oda:\/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_2\/dbs\/ [mydb] srvctl stop database -d mydb_site1<\/code><\/p>\n
\noracle@oda:\/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_2\/dbs\/ [mydb] srvctl config database -d mydb_site1
\nDatabase unique name: mydb_site1
\nDatabase name: mydb
\nOracle home: \/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_1
\nOracle user: oracle
\nSpfile: \/u02\/app\/oracle\/oradata\/mydb_site1\/dbs\/spfilemydb.ora
\nDomain:
\nStart options: open
\nStop options: immediate
\nDatabase role: PRIMARY
\nManagement policy: AUTOMATIC
\nServer pools: mydb_site1
\nDatabase instance: mydb
\nDisk Groups:
\nMount point paths: \/u02\/app\/oracle\/oradata\/mydb_site1,\/u03\/app\/oracle\/
\nServices:
\nType: SINGLE
\nDatabase is administrator managed<\/code><\/p>\n
\noracle@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<\/code><\/p>\n
\n-db for database name
\n-oraclehome for database oracle home
\n-pwfile for password file<\/p>\n
\noracle@oda:\/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_2\/dbs\/ [mydb] srvctl config database -d mydb_site1
\nDatabase unique name: mydb_site1
\nDatabase name: mydb
\nOracle home: \/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_2
\nOracle user: oracle
\nSpfile: \/u02\/app\/oracle\/oradata\/mydb_site1\/dbs\/spfilemydb.ora
\nDomain:
\nStart options: open
\nStop options: immediate
\nDatabase role: PRIMARY
\nManagement policy: AUTOMATIC
\nServer pools: mydb_site1
\nDatabase instance: mydb
\nDisk Groups:
\nMount point paths: \/u02\/app\/oracle\/oradata\/mydb_site1,\/u03\/app\/oracle\/
\nServices:
\nType: SINGLE
\nDatabase is administrator managed<\/code><\/p>\n
\noracle@oda:\/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_1\/dbs\/ [mydb] srvctl start database -d mydb_site1<\/code><\/p>\n
\noracle@oda:\/tmp\/ [mydb] grep mydb \/etc\/oratab
\nmydb:\/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_2:N # line added by Agent<\/code><\/p>\n
\noracle@oda01-p:\/tmp\/ [mydb] echo $ORACLE_HOME
\n\/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_2<\/code><\/p>\nChecking ODA metadata information<\/h3>\n
\n[root@oda tmp]# odacli list-dbhomes
\nID Name DB Version Home Location Status
\n---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
\ned0a6667-0d70-4113-8a5e-3afaf1976fc2 OraDB12102_home1 12.1.0.2.171017 (26914423, 26717470) \/u01\/app\/oracle\/product\/12.1.0.2\/dbhome_1 Configured
\n89f6687e-f575-45fc-91ef-5521374c54c0 OraDB11204_home1 11.2.0.4.171017 (26609929, 26392168) \/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_1 Configured
\n8c6bc663-b064-445b-8a14-b7c46df9d1da OraDB12102_home3 12.1.0.2.171017 (26914423, 26717470) \/u01\/app\/oracle\/product\/12.1.0.2\/dbhome_3 Configured
\n9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8 OraDB11204_home2 11.2.0.4.171017 (26609929, 26392168) \/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_2 Configured<\/code><\/p>\n
\n[root@oda tmp]# odacli list-databases
\nID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
\n---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
\nf38f3a6c-987c-4e11-8cfa-af5cb66ff4e3 mydb Si 11.2.0.4 false OLTP Odb1 ACFS Configured 89f6687e-f575-45fc-91ef-5521374c54c0<\/code><\/p>\nUpdating ODA metadata<\/h3>\n
\nodacli describe-component<\/code><\/p>\nODA version 18.3 or higher<\/h4>\n
\nodacli modify-database -i -dh <\/code><\/p>\n[root@oda tmp]# odacli modify-database -i f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3 -dh 9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8
\nDCS-10045:Validation error encountered: Changing the database home is not allowed for an instance only database.<\/code><\/p>\nODA version lower than 18.3<\/h4>\n
\nThe next steps will describe how to update the derby DB manually.<\/p>\n1) Stop the DCS Agent<\/h5>\n
[root@oda ~]# initctl stop initdcsagent
\ninitdcsagent stop\/waiting<\/p>\n
\n[root@oda ~]#<\/code><\/p>\n2) Copy the derby Database<\/h5>\n
\n[root@oda tmp]# cd \/opt\/oracle\/dcs\/repo<\/code><\/p>\n
\n[root@oda repo]# ls -l
\ntotal 24
\n-rw-r--r-- 1 root root 1149 Aug 27 11:57 derby.log
\ndrwxr-xr-x 4 root root 4096 Aug 27 15:32 node_0
\ndrwxr-xr-x 4 root root 4096 Aug 12 16:18 node_0_orig_12082019_1619
\ndrwxr-xr-x 4 root root 4096 Aug 26 11:31 node_0_orig_26082019_1132
\ndrwxr-xr-x 4 root root 4096 Aug 26 15:27 node_0_orig_26082019_1528
\ndrwxr-xr-x 4 root root 4096 Aug 27 11:57 node_0_orig_27082019_1158<\/code><\/p>\n
\n[root@oda repo]# cp -rp node_0 node_0_backup_27082019_1533<\/code><\/p>\n
\n[root@oda repo]# ls -l
\ntotal 28
\n-rw-r--r-- 1 root root 1149 Aug 27 11:57 derby.log
\ndrwxr-xr-x 4 root root 4096 Aug 27 15:32 node_0
\ndrwxr-xr-x 4 root root 4096 Aug 27 15:32 node_0_backup_27082019_1533
\ndrwxr-xr-x 4 root root 4096 Aug 12 16:18 node_0_orig_12082019_1619
\ndrwxr-xr-x 4 root root 4096 Aug 26 11:31 node_0_orig_26082019_1132
\ndrwxr-xr-x 4 root root 4096 Aug 26 15:27 node_0_orig_26082019_1528
\ndrwxr-xr-x 4 root root 4096 Aug 27 11:57 node_0_orig_27082019_1158<\/code><\/p>\n3) Start DCS Agent<\/h5>\n
[root@oda repo]# initctl start initdcsagent
\ninitdcsagent start\/running, process 45530<\/p>\n
\nroot 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
\n[root@oda repo]#<\/code><\/p>\n4) Update metadata information<\/h5>\n
\n[root@oda repo]# \/usr\/java\/jdk1.8.0_161\/db\/bin\/ij
\nij version 10.11
\nij> connect 'jdbc:derby:node_0_backup_27082019_1533';<\/code><\/p>\n
\nij> select DBHOMEID from DB where ID='f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3';
\nDBHOMEID
\n--------------------------------------------------------------------------------------------------------------------------------
\n89f6687e-f575-45fc-91ef-5521374c54c0
\n1 row selected<\/code><\/p>\n
\nij> update DB set DBHOMEID='9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8' where ID='f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3';
\n1 row inserted\/updated\/deleted<\/code><\/p>\n
\nij> select DBHOMEID from DB where ID='f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3';
\nDBHOMEID
\n--------------------------------------------------------------------------------------------------------------------------------
\n9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8
\n1 row selected<\/code><\/p>\n
\nij> commit;<\/code><\/p>\n
\nij> exit;<\/code><\/p>\n5) Stop the DCS Agent<\/h5>\n
[root@oda repo]# initctl stop initdcsagent
\ninitdcsagent stop\/waiting<\/p>\n
\n[root@oda repo]#<\/code><\/p>\n6) Apply the changes in production<\/h5>\n
\n[root@oda repo]# ls -ltrh
\ntotal 28K
\ndrwxr-xr-x 4 root root 4.0K Aug 12 16:18 node_0_orig_12082019_1619
\ndrwxr-xr-x 4 root root 4.0K Aug 26 11:31 node_0_orig_26082019_1132
\ndrwxr-xr-x 4 root root 4.0K Aug 26 15:27 node_0_orig_26082019_1528
\ndrwxr-xr-x 4 root root 4.0K Aug 27 11:57 node_0_orig_27082019_1158
\ndrwxr-xr-x 4 root root 4.0K Aug 27 15:35 node_0_backup_27082019_1533
\n-rw-r--r-- 1 root root 1.2K Aug 27 15:35 derby.log
\ndrwxr-xr-x 4 root root 4.0K Aug 27 15:36 node_0<\/code><\/p>\n
\n[root@oda repo]# mv node_0 node_0_orig_27082019_1536<\/code><\/p>\n
\n[root@oda repo]# mv node_0_backup_27082019_1533 node_0<\/code><\/p>\n
\n[root@oda repo]# ls -ltrh
\ntotal 28K
\ndrwxr-xr-x 4 root root 4.0K Aug 12 16:18 node_0_orig_12082019_1619
\ndrwxr-xr-x 4 root root 4.0K Aug 26 11:31 node_0_orig_26082019_1132
\ndrwxr-xr-x 4 root root 4.0K Aug 26 15:27 node_0_orig_26082019_1528
\ndrwxr-xr-x 4 root root 4.0K Aug 27 11:57 node_0_orig_27082019_1158
\ndrwxr-xr-x 4 root root 4.0K Aug 27 15:35 node_0
\n-rw-r--r-- 1 root root 1.2K Aug 27 15:35 derby.log
\ndrwxr-xr-x 4 root root 4.0K Aug 27 15:36 node_0_orig_27082019_1536<\/code><\/p>\n7) Start the DCS Agent<\/h5>\n
[root@oda repo]# initctl start initdcsagent
\ninitdcsagent start\/running, process 59703<\/p>\n
\nroot 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
\n[root@oda repo]#<\/code><\/p>\n8) Check ODA metadata<\/h5>\n
\n[root@oda repo]# odacli list-dbhomes
\nID Name DB Version Home Location Status
\n---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
\ned0a6667-0d70-4113-8a5e-3afaf1976fc2 OraDB12102_home1 12.1.0.2.171017 (26914423, 26717470) \/u01\/app\/oracle\/product\/12.1.0.2\/dbhome_1 Configured
\n89f6687e-f575-45fc-91ef-5521374c54c0 OraDB11204_home1 11.2.0.4.171017 (26609929, 26392168) \/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_1 Configured
\n8c6bc663-b064-445b-8a14-b7c46df9d1da OraDB12102_home3 12.1.0.2.171017 (26914423, 26717470) \/u01\/app\/oracle\/product\/12.1.0.2\/dbhome_3 Configured
\n9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8 OraDB11204_home2 11.2.0.4.171017 (26609929, 26392168) \/u01\/app\/oracle\/product\/11.2.0.4\/dbhome_2 Configured<\/code><\/p>\n
\n[root@oda repo]# odacli list-databases
\nID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
\n---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
\nf38f3a6c-987c-4e11-8cfa-af5cb66ff4e3 mydb Si 11.2.0.4 false OLTP Odb1 ACFS Configured 9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8<\/p>\n
\nDatabase details
\n----------------------------------------------------------------
\n ID: f38f3a6c-987c-4e11-8cfa-af5cb66ff4e3
\n Description: mydb
\n DB Name: mydb
\n DB Version: 11.2.0.4
\n DB Type: Si
\n DB Edition: EE
\n DBID:
\n Instance Only Database: true
\n CDB: false
\n PDB Name:
\n PDB Admin User Name:
\n Class: OLTP
\n Shape: Odb1
\n Storage: ACFS
\n CharacterSet: AL32UTF8
\n National CharacterSet: AL16UTF16
\n Language: AMERICAN
\n Territory: AMERICA
\n Home ID: 9783fd89-f035-4d1a-aaaf-f1cdb09c6ea8
\n Console Enabled: false
\n Level 0 Backup Day: Sunday
\n AutoBackup Disabled: false
\n Created: June 11, 2019 2:46:35 PM CEST
\n DB Domain Name: in-kon.ch<\/code><\/p>\nConclusion<\/h3>\n