Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :-

Tuesday, December 23, 2014

How To Change DB Server IP of RAC Databases- Step-By-Step Guide


 I was once contracted for a karnataka govt project linked to aadhaar for a short period and was asked to help with a IP change for a RAC database they had, I really did not have any clue on how to go ahead with that requirement, But guess what? I nodded my head, indicating I could do that for them, Almost 12 hours (Morning 11AM to 11PM), I spent googling and end of the day did the IP change for them. But if you ask me how I did that, Sorry no idea. So that led me to write a step-by-step sequence of activities for changing the IP’s of a RAC database.

On what scenario do you need a IP change for your DB servers?

One of the PCI audit requirements is that any database that holds any payment card details should be placed in an isolated network seperated behind special firewalls. In such scenario the we may need to to change the IP address of the database server matching to the new datacenter public network. In this article, I would like explain the step by step approach to change your database node IP in a steamlined manner.

Ideally each of our database servers are having 3 IP addresses.
     Public IP – resolving to the name e.g. ajithpathiyil1. This is recorded in DNS and the /etc/hosts file.
     Virtual IP (VIP)– resolving to the name -vip e.g. ajiithpathiyil1-vip. This is recorded in DNS and the /etc/hosts file.
     Private Interconnect IP – resolving to the name -priv. e.g. ajithpathiyil1-priv. This only exists in the /etc/hosts file on each server in the cluster, as these as they are on a private interconnect network local only to the servers.

Only the public and VIP IP addresses are changing, as the private network isn’t on the LAN, and so no other systems or servers can access it.

The following table shows the old and new IP addresses, I want to change:
(Note for this demo, I will be using the IP address within my home network, But the procedure remains same for IP’s that will belong to different networks as well)
Old IP address
New IP address

Old IP address
New IP address

Capture details of the current configuration, might be useful for troubleshooting issues later. To achieve that, run the following sequence of commands and note their output somewhere.

The nodeapps config

[oracle@ajithpathiyil1 ~]# srvctl config nodeapps -n $HOSTNAME -a

Current interfaces stored in the OCR

[root@ajithpathiyil1 ~]# $ORA_CRS_HOME/bin/oifcfg getif

OS network configuration

[root@ajithpathiyil1 ~]# /sbin/ifconfig

The following steps are required to change the IP addresses of an Oracle database cluster:
1.    Shutdown the database, and disable it from starting automatically.

Go through the standard process of completely stopping oracle processes on a database node
·         Shut down all database instances.
·         Shut down the ASM instance.
·         Shut down nodeapps.

The next step is to verify that the VIP is no longer running by executing 'ifconfig -a' and confirming the IP address is no longer listed in the output.

If the interface still shows as online, this may be an indication that a resource which is dependent on the VIP is still running. The crs_stat command can help to show resources that are still online.
2. Shutdown ASM, and disable it from starting automatically.

That’s advisable to disable ASM and DB instances to make potential troubleshooting easier. They can be disabled as follows:

[oracle@ajithpathiyil1 ~]#srvctl stop database –d
[oracle@ajithpathiyil1 ~]#srvctl disable instance -d -i
[oracle@ajithpathiyil1 ~]#srvctl disable asm -n $HOSTNAME

3.    Shutdown CRS on the DB servers.

Stop CRS services on the node where the IP address is changed or the cluster may run into an instable state for a while (due to mutual node evictions).

[root@ajithpathiyil1 ~]#/etc/init.d/ stop

4.    Unmount all NFS mounts.

Query the /etc/fstab file, and unmount all NFS shares before changing IP on public interface on all servers, as this will cause local mounts to become unstable and can cause oracle ssh access to hang.
5.    Update the /etc/hosts file on the database servers. Log onto each server as root, and update the /etc/hosts file with the new public and VIP IP addresses.
​[root@ajithpathiyil1 ~]# vi /etc/hosts   ajithpathiyil1   ajithpathiyil1-vip   ajithpathiyil1-priv   ajithpathiyil2   ajithpathiyil2-vip   ajithpathiyil2-priv   ajithpathiyil2   ajithpathiyil2-vip   ajithpathiyil2-priv  ajithpathiyil-scan  ajithpathiyil-gns
::1             localhost6.localdomain6 localhost6       localhost.localdomain localhost

7.    Update ifcfg-eth0 file under /etc/sysconfig/network-scripts/ with the new public IP. (Or you can do it with your linux GUI open – I do not prefer GUI personally)

[root@ajithpathiyil1 ~]# ls -l /etc/sysconfig/network-scripts/
total 392
-rw-r--r-- 3 root root   116 Oct 10 12:40 ifcfg-eth0
-rw-r--r-- 3 root root   187 Oct 10 12:40 ifcfg-eth1
-rw-r--r-- 3 root root   127 Oct 21 16:46 ifcfg-eth2
-rw-r--r-- 1 root root   254 Mar  3  2008 ifcfg-lo

[root@ajithpathiyil1 ~]# cat /etc/sysconfig/network-scripts/ifcfg-eth0

8.    Restart network services. 

[root@ajithpathiyil1 ~]# service network stop
[root@ajithpathiyil1 ~]# service network start

9.    Perform ping test.

[root@ajithpathiyil1 ~]# ping
[root@ajithpathiyil1 ~]# ping
[root@ajithpathiyil1 ~]# ping

10. Change the $ORACLE_HOME/network/admin/listener.ora entries if they refer to physical IP's..

11.  Start CRS as root on all servers

[root@ajithpathiyil1 ~]#/etc/init.d/ start

[root@ajithpathiyil2 ~]#/etc/init.d/ start

[root@ajithpathiyil3 ~]#/etc/init.d/ start

12. Stop nodeapps on all servers.

[oracle@ajithpathiyil1 ~]# srvctl stop nodeapps -n $HOSTNAME

[oracle@ajithpathiyil2 ~]# srvctl stop nodeapps -n $HOSTNAME

[oracle@ajithpathiyil3 ~]# srvctl stop nodeapps -n $HOSTNAME

13. Check that nodeapps are stopped on all servers before continuing, as during the DR change, the VIP’s kept re-locating to other nodes as CRS was started.

[oracle@ajithpathiyil1 ~]# srvctl status nodeapps -n $HOSTNAME

14. Change the public IP address in CRS as root (where is the subnet):

[root@ajithpathiyil1 ~]# $ORA_CRS_HOME/bin/oifcfg delif -global eth0
[root@ajithpathiyil1 ~]# $ORA_CRS_HOME/bin/oifcfg setif -global eth0/

[root@ajithpathiyil2 ~]# $ORA_CRS_HOME/bin/oifcfg delif -global eth0
[root@ajithpathiyil2 ~]# $ORA_CRS_HOME/bin/oifcfg setif -global eth0/

[root@ajithpathiyil3 ~]# $ORA_CRS_HOME/bin/oifcfg delif -global eth0
[root@ajithpathiyil3 ~]# $ORA_CRS_HOME/bin/oifcfg setif -global eth0/

15. Review the /etc/hosts file. The setif command above might have appended an entry for the server to the hosts file, pointing to the private interconnect IP. If it has, remove it. In addition, check that the entry for the public IP has the servername with the FQDN, as well as just a servername entry (i.e. in my case).

16. Change the VIP IP address in CRS as root (correcting the servername, IP address and subnet mask as appropriate):

[root@ajithpathiyil1 ~]# srvctl modify nodeapps -n ajithpathiyil1 -A

[root@ajithpathiyil2 ~]# srvctl modify nodeapps -n ajithpathiyil1 -A

[root@ajithpathiyil3 ~]# srvctl modify nodeapps -n ajithpathiyil1 -A

17. Restart nodeapps on each node:

[root@ajithpathiyil3 ~]# $ORA_CRS_HOME/bin/srvctl start nodeapps -n $HOSTNAME

18.  Remount the NFS shares listed in /etc/fstab.

That may be optional: run the’ network configuration verification utlitity. 
Validate that nodeapps starts up properly including the listener.

20. Finally, enable ASM & start ASM instances followed by your database using "SRVCTL"

You done.......


Sunday, December 21, 2014

Kickstart Your Oracle Apps R12.2 Upgrade Project With Upgrade Readiness Report Analysis

Hi All,

If you are planning for a R12.2 upgrade, To get initial idea on how much of work is required for standard & custom remediation and making it online patching compliant, we can start with upgrade readiness patch and API's

Readiness Assessment from Oracle (Patch 14125620)

Download and apply the patch 14125620. It bears few SQL scripts which scans the dictionary for the system compliance with the Online Patching feature.

cd 14125620
adpatch patchtop=`pwd` logfile=adpatch.u14125620.log driver=u14125620.drv options=hotpatch

Sequentially run scripts delivered by the patch

sqlplus system @$AD_TOP/sql/ADZDPSUM.sql APPS

sqlplus system @$AD_TOP/sql/ADZDPAUT.sql APPS

sqlplus system @$AD_TOP/sql/ADZDPMAN.sql APPS

Review generated report

adzdpsum.txt - each section of this report should be reviewed carefully and take appropriate actions.

adzdpman.txt - for detail and follow given instructions

adzdpaut.txt – optional foe review as these violations would be fixed automatically when your instance is enabled for Online Patching.

Let me know, If you like to see a sample upgrade readiness report. Leave me a mail @