Configuring multiple DHCP networks for 11gR2 Grid Infrastructure

 

In 11gR2 cluster's network, both VIPs and Private IPs can be assigned using DHCP. Generally we have to maintain separate physical networks using 2 switches for public and private networks of RAC. If we connect all public and private interfaces of nodes in cluster to same switch (this will be happen in learning environments only, for example a home setup), we cannot guarantee if VIPs and private IPS will be assigned to respective network interfaces. But we need to have VIP and Private IPs should be assigned exactly as per their networks.

Translating this problem into system administrator terms, in a physical network made using one switch; the following has to be done.

 1. A DHCP server that can assign IPs in 2 subnets: 172.16.1.0/24, 192.168.15.0/24

 2. A DHCP client having 2 interfaces eth0, eth1. In this eth0 will have an alias interface eth0:1. All these interfaces should be assigned IP addresses like this.

   a. eth0   - Static IP address in the subnet 172.16.1.0/24

   b. eth0:1 - DHCP assigned IP in the subnet 172.16.1.0/24

   c. eth1   - DHCP assigned IP in the subnet 192.168.15.0/24

And the constraint we have here is both interfaces of DHCP client connected to same switch.

After experimenting, I found this is the configuration that satisfies given requirement.

DHCP server configuration (dhcpd.conf):

class "pub-net" {

  match if option dhcp-client-identifier = "pubnet";

}

class "priv-net" {

  match if option dhcp-client-identifier = "privnet";

}

subnet 172.16.1.0 netmask 255.255.255.0 {

  pool {

    allow members of "pub-net";

    range 172.16.1.11 172.16.1.30;

    option domain-name-servers 172.16.1.1;

    option routers 172.16.1.1;

  }

}

subnet 192.168.15.0 netmask 255.255.255.0 {

  pool {

    allow members of "priv-net";

    range 192.168.15.11 192.168.15.30;

    option domain-name-servers 172.16.1.1;

    option routers 192.168.15.1;

  }

}

DHCP client configuration (/etc/dhclient.conf):

Note: New file /etc/dhclient.conf has to be created if it does not exist already.

interface "eth0:1" {

    send  dhcp-client-identifier = "pubnet";

}

interface "eth1" {

    send  dhcp-client-identifier = "privnet";

}

Testing configuration at client:

# killall -9 dhclient

# dhclient eth0:1

Now verify IP addresses for eth0:1, eth1 if we got them in correct subnets. After testing is finished please make sure you have done the following

# service network restart

This is to ensure that eth0:1 will not have any IP address assigned to it before Oracle grid infrastructure installation.

Assigning a primary key value to all rows from a sequence

Sometimes we need to add a primary key for the tables which does not have any. And primary key value comes from a sequence. As soon as we add primary key column and create sequence, here is a sample PL/SQL block we have to execute to assign a primary key value to all rows before enabling the primary key constraint. In this,

Table name : tab1

Primary key column that we added: col1

Sequence name from which primary key values are generated: seq1

 

DECLARE

  CURSOR c1 IS SELECT * FROM tab1 FOR UPDATE;

BEGIN

  FOR i IN c1

  LOOP

    UPDATE tab1 SET col1 = seq1.nextval WHERE CURRENT OF c1;

  END LOOP;

  -- COMMIT;

END;

/

From next time onwards, for every row insert if primary key value has to be auto incremented, here is the insert trigger.

CREATE OR REPLACE TRIGGER tabl1_ins_pk_trig

  BEFORE INSERT ON tab1 FOR EACH ROW

BEGIN

  SELECT seq1.NEXTVAL INTO :new.col1 FROM dual;

END;

/

Simple script which generates disable/enable foreign keys of child tables

When we need to truncate tables that have child tables with foreign keys enabled, first we should disable all foreign keys. Here is the script that generates SQL commands to disable and enable foreign keys. Enable constraint script is useful to enable them back after truncating.

  select 'alter table ' ||

         child_tab_cons.owner || '.' || child_tab_cons.table_name ||

         ' disable constraint ' ||

         -- ' enable constraint ' ||

         child_tab_cons.constraint_name || ';'

    from all_constraints   parent_tab_cons,

         all_constraints   child_tab_cons

   where parent_tab_cons.owner            = 'MURTY'

     and parent_tab_cons.table_name       in ('TAB1', 'TAB2', 'TAB3')

     and parent_tab_cons.constraint_type  in ('P', 'U')

     and child_tab_cons.r_constraint_name = parent_tab_cons.constraint_name

order by child_tab_cons.owner,

         child_tab_cons.table_name,

         child_tab_cons.constraint_name

;