11.2 Oracle Restart – Changing Hostname

It has taken me a good deal longer that I would have expected to change the hostname on some cloned VMs that are home to Oracle 11.2 databases running on ASM with Oracle Restart.

I was clearly not alone in thinking that it would just be a case of running localconfig delete/localconfig add, as I have done in the past for hosts running Oracle database 10.2 with ASM for storage. However, things have changed and having done a fair amount of searching using both MetaLink (My Oracle Support) and Google it appears that there isn’t much out there yet in the form of clear instructions. The best I could find was My Oracle Support article ID 887658.1, which got me moving in the right direction.

So, in the hope of saving others the time I have lost working this out, here are the steps I used to rename a Linux host running Oracle Database 11.2 with ASM in an Oracle Restart configuration. The steps have been written for an installation that splits the ownership of the “Grid Infrastructure” and the database between a user named grid and a user named oracle respectively. It is important who you run at least some of these commands as, so please check which user you are before each command. Or more likely, if things don’t look to be working then check which user you ran the command as before going further. It is easy to backtrack if you do get it wrong.

Remove the existing configuration as root (with ORACLE_HOME set to the grid home):

[root@old-host grid]# $ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl -delete

* In some of my earlier attempts I stopped everything gracefully, but it does not appear to be necessary.

Change the value of HOSTNAME in the network file using the editor of your choice.

[root@old-host ~]# vi /etc/sysconfig/network

Update /etc/hosts as appropriate

[root@old-host ~]# vi /etc/hosts

Change the IP address in ifcfg-<interface> as required

[root@old-host ~]# vi /etc/sysconfig/network-scripts/ifcfg-eth0

Change the hostname in the listener configuration file

[root@old-host ~]# vi $ORACLE_HOME/network/admin/listener.ora

* Clearly you don’t need to be root to do the previous command, but as reboot is next it doesn’t seem worth switching user.

Reboot to pickup hostname change

When the host has restarted run roothas.pl as root (with ORACLE_HOME set to the grid home)

[root@new-host grid]# $ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl

The output should look something like:

2009-12-19 15:10:36: Checking for super user privileges
2009-12-19 15:10:36: User has super user privileges
2009-12-19 15:10:36: Parsing the host name
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
CRS-4664: Node new-host successfully pinned.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting

2009/12/19 15:11:58     /u01/app/grid/product/11.2.0/grid/cdata/new-host/backup_20091219_151158.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

Exit from root user.

You now need to add the listener, ASM, database and services (if applicable) into the Oracle Restart configuration.

[grid@new-host ~]$ srvctl add listener
[grid@new-host ~]$ srvctl add asm -d '/dev/sd[a-h]1'
[grid@new-host ~]$ srvctl start listener
[grid@new-host ~]$ srvctl start asm

* Your asm_diskstring, specified with the -d option to “srvctl add asm”, with almost certainly be different!

Note that the diskgroups are not automatically added back to the Oracle Restart configuration (as shown by the output of “srvctl status” below) and must be manually started:

[grid@new-host ~]$ srvctl status diskgroup -g data
PRCR-1001 : Resource ora.DATA.dg does not exist

[grid@new-host ~]$ sqlplus /nolog
SQL> conn / as sysasm
Connected.
SQL> alter diskgroup data mount;

Diskgroup altered.

SQL> exit

[grid@new-host ~]$ srvctl status diskgroup -g data
Disk Group data is running on new-host
[grid@new-host ~]$

Adding the database to Oracle Restart:

[oracle@new-host ~]$ srvctl add database -d ora11gr2 -o $ORACLE_HOME -n ora11gr2 -p +DATA/ora11gr2/spfileora11gr2.ora -a DATA
[oracle@new-host ~]$ srvctl config database -d ora11gr2
Database unique name: ora11gr2
Database name: ora11gr2
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: grid
Spfile: +DATA/ora11gr2/spfileora11gr2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
[oracle@new-host ~]$ srvctl start database -d ora11gr2
[oracle@new-host ~]$ srvctl status database -d ora11gr2
Database is running.
[oracle@new-host ~]$

That should be it. The only other place to update the home name is in the tnsnames.ora file.

Simply Renaming A Schema

What started off as just moving a database from UAT to QA soon turned into something slightly more complex, with an interesting discovery for me.

For reasons that preceed my involvement in this project, not only did the database name indicate the region (UAT, QA, production), but the username for the application schema also indicated the region. This didn’t fit with the standard, but the work required to change this in all regions wasn’t considered viable.

Anyway, this left me in a position of having copied and renamed the UAT database to QA, but the application username/schema indicated UAT. This was clearly a recipe for later confusion… What I needed was to rename the schema, but unfortunately Oracle doesn’t provide this as one-liner.

This is a 9.2.0.8 database, so it was time for imp and exp… “Just a case of export user, create new appropriately named user, import from original user to new user, drop original user if all goes smoothly.” Well, it didn’t.

The export went fine, but when importing I hit ORA-02304 errors in more than one place. This was a new one on me. An example of the errors is shown below:

IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "xxxxxxx" TIMESTAMP 'yyyy-mm-dd:hh24:mi:ss' OID '###############"


Oracle was complaining about the fact that I was trying to create objects with same object identifier (OID) as objects that already exist in the database. I had decided that keeping the original schema in place until I’d the new user was successfully imported was a sensible thing to do just in case things didn’t go to plan, but as it turned out keeping the original user in the database whilst importing under the new user was the root of my problem.

If you found this page because, like me, you haven’t yet got to the point of checking MetaLink as the first port of call then check out MetaLink note 1066139.6 for more detail.