Migrating an Oracle Home – dead easy?

Changing a database to use a new oracle home is easy isn’t it? A simple interview question that you would never get wrong. Everyone knows that you shutdown the database , edit the oratab file and restart the database again.  So then the interviewer presses and is obviously looking for more.

You confidently follow-up with ‘of course I would have copied the passwd and the init.ora file to the new ORACLE_HOME/dbs folder during the outage.’ The interviewer says “go on, what else will you do?”.

It is getting a bit more awkward now, but you rise to the occasion with a comment about editing the listener to show the new ORACLE_HOME and then reloading the listener – but he still wants more and you begin to dry up a bit.

After a bit of hinting you recognise that you might need to go into OEM and edit the configuration but you have given your all, you’re are a spent force, wishing you could move onto the next easy question.

The above is from an interview question I asked only last week and unless a candidate has recently been migrating databases around then I think that would be representative of many people’s answers.  Therefore the idea of this post is just to put together a few things to form a checklist of things that may be required when migrating to a new ORACLE_HOME. I am sure all are not relevant and the OWB comment might well be replaced with some other tool you might be using.

So just a bullet point list follows and if I have failed my own interview question then I will be really pleased to accept suggestions and add them to the list

• Edit  the oratab entry to update the database to reflect the new ORACLE_HOME
• Copy the $ORACLE_HOME/dbs/orapwSID.ora and initSID.ora file to the new $ORACLE_HOME/dbs. You may hold these somewhere different  in which case the same principle applies. If  a RAC system ensure you do this on all nodes.
• Edit the listener.ora file and restart or reload the listener
• If you use Oracle Warehouse Builder then follow the note further down
• Update  Oracle Enterprise Manager to reflect the new home
• Review any ONAMES or OID or even LDAP config to see if they are in use or need to be changed
• If your oraenv equivalent or login script does not set the path then check that you are now using the correct path. If it does then all should be OK
• If you use a single listener and tnsnames file  for many homes on a server then you might want to them link them to the new ORACLE_HOME

cd new $ ORACLE_HOME

ln -s /app/oracle/product/11.1.0/asm/network/admin/listener.ora listener.ora

ln -s /app/oracle/product/11.1.0/asm/network/admin/tnsnames.ora tnsnames.ora

• When changing an Oracle Home with either RAC or stand alone 11gr2 Oracle Restart, Oracle Clusterware needs to be modified for the new oracle home

 srvctl modify database –d ORCL –o /app/oracle/product/11.2.0.3/dbhome_AWR1_1

Alternatively the resource can be deleted and re-created with

 srvctl stop service -d ORCL2A -s ORCL2

srvctl remove service -d ORCL2A -s ORCL2

srvctl remove database –d ORCL2A

 srvctl add database -d ORCL2A -o /app/oracle/product/11.2.0.3/dbhome_AWR1_1 -r PRIMARY

or

srvctl add database -d ORCL2B -o /app/oracle/product/11.2.0.1/dbhome_AWR1_1 -r PHYSICAL_STANDBY -s mount

Extra step for RAC only

{

srvctl add instance -d ORCL2A -i ORCL2A1 -n node1

srvctl add instance -d ORCL2A -i ORCL2A2 -n node2

}

 crsctl status resource ora.ORCL2a.db -f | grep DEPENDENCIES

srvctl modify database -d ORCL2A -a “DATA,FRA”

crsctl status resource ora.ORCL2a.db -f | grep DEPENDENCIES

srvctl add service -d ORCL2A -r ORCL2A1,ORCL2A2 -s ORCL2 -l PRIMARY -e SELECT -m BASIC

srvctl modify service -d ORCL2A -s ORCL2 -z 180 -w 1

• Instructions to change OWB Home on the same server - 11GR1

Install the new Oracle Home

Stop the OWB service

Here I am assuming OWB_HOME is the same place as ORACLE_HOME

$ORACLE_HOME/owb/rtp/sql/stop_service

 

SQL> select key, value from OWBSYS.OWBRTPS order by key;
SQL> Update OWBSYS.OWBRTPS set VALUE='<owb_new_home>' where key='key_from_previous_SQL';
SQL> commit;

$ORACLE_HOME/owb/rtp/sql/start_service

 Connect via Control Centre and ensure everything works