Database Concepts and Internals by Ashish

Sunday, October 21, 2012

RAC 11.2.0.1.0 to 11.2.0.3.0 Upgrade Using Out of Place Upgrade

In my case I am using 2-Node RAC Setup where I have 11.2.0.1.0 (32 bit) Clusterware then I will upgrade my RAC server to 11.2.0.3.0 (32 bit).

Software requirement
---------------------------

1. 11.2.0.3.0 Patchset

2. latest Opatch software required for upgrade (patch : 943827)

STEPS for Upgrade

Step-1: Verify the upgrade on all the servers
------------------------------------------------------

$ ./runcluvfy.sh stage -pre crsinst -upgrade -n host01,host02 -rolling -src_crshome /u01/app/11.2.0/grid/ -dest_crshome /u01/app/11.2.3/ -dest_version 11.2.0.3.0 -fixup -fixupdir /home/grid/fixupscript -verbose

NOTE-1: Here we have a new home for upgrade, /u01/app/11.2.3/grid

OUTPUT of this runcluvfy:
--------------------------------

After running this you will get two errors,

a) Related with "resolve.conf"

b) for latest Opatch

a) Solution for "resolve.conf"
-------------------------------

1. Open the named.conf file in DNS Server and see an entry there like:

# vim /var/named/chroot/etc/named.conf

zone "." IN {
type hint;
file "named.ca";
};

2. Replace this entry by new entry:

zone "." IN {
type hint;
file "/dev/null";
};

3. And then finally restart named service by using this command:

# service named restart

b) Solution for opatch (apply patch 9413827)
------------------------------------------------------

Here you'll have to apply a patch on your hosts one by one, using the following steps

1. Form host01 do this to stop your clusterware for patching (Opatch)

# <CRS_HOME>/crs/install/rootcrs.pl -unlock

2. Set Grid & JAVA home to apply Patch.

$. oraenv = +ASM1

$ export JAVA_HOME=/u01/app/11.2.0/grid/jdk

3. Change your directory to that location where you have your patch(943827), in my case it is in : /stage/943827

$ cd /stage/943827

4. Apply Patch one 1st node where your RAC server is running by grid user.

$ /u01/app/11.2.0/grid/OPatch/opatch napply -jdk $JAVA_HOME -local -oh /u01/app/11.2.0/grid -id 943827

5. After applying patch change the permission on few files from the root user (#).

# chmod +w /u01/app/11.2.0/grid/log/host01/agent/
# chmod +w /u01/app/11..2.0/grid/log/host01/agent/crsd

6. Then again start all the resources in new patched mode from root user.

# <CRS_HOME>/crs/install/rootcrs.pl -patch

7. Verify whether your clusterware home is patched or not from grid user.

$ /u01/app/11.2.0/grid/OPatch/opatch lsinventory

8. Repeat the same on host02 and others (Rolling forward Upgrade)

Step-2: After resolving all the errors, go to the 11.2.0.3.0 patchset directory and invoke runIstaller.

$ /stage/11.2.0.3.0/runIstaller

Step-3: DO the next -> next (Just like your normal installation, but remember your new installation will share old inventory files from old home like, /u01/app/oraInventory)etc

Step-4: In the last run rootupgrade.sh script on the 1st node then other nodes in parallel but not on the last node. after running on all the nodes then run the same rooupgrade.sh script on the last node, and then finish your upgrade.

Step-5: Verify your upgrade:

$ crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.2.0.3.0]

$ crsctl query crs releaseversion

Oracle High Availability Services release version on the local node is [11.2.0.3.0]

$ crsctl query crs softwareversion

Oracle Clusterware version on node [host01] is [11.2.0.3.0]

$ crsctl query crs softwareversion host02

Oracle Clusterware version on node [host02] is [11.2.0.3.0]

BINGO ITS DONE :)

NEW FEATURES INTRODUCED IN EACH VERSION OF 11G

Desupported Options in 11.2

· Block and Raw Devices Not Supported with OUI

New Features for Release 2 (11.2.0.3)

· Oracle ACFS and ADVM Support for Oracle Enterprise Linux (UEK)

· Oracle ACFS and ADVM Support for SUSE Enterprise Linux 11

· Oracle Clusterware Upgrade Configuration Force Feature

New Features for Release 2 (11.2.0.2)

· Enhanced Patch Set Installation

· Oracle ACFS and ADVM Support for SUSE Enterprise Linux

· Cluster Health Monitor Included with Oracle Clusterware

· Grid Installation Owner and ASMOPER

· New Software Updates Option

· Redundant Interconnect Usage

· Oracle Database Quality of Service Management

New Features for Release 2 (11.2)

· Oracle Automatic Storage Management and Oracle Clusterware Installation

· Oracle Automatic Storage Management and Oracle Clusterware Files

· Oracle Automatic Storage Management Cluster File System (Oracle ACFS)

· Oracle ASM Job Role Separation Option with SYSASM

· Cluster Time Synchronization Service

· Oracle Enterprise Manager Database Control Provisioning

· Fixup Scripts and Grid Infrastructure Checks

· Grid Plug and Play

· Improved Input/Output Fencing Processes

· Intelligent Platform Management Interface (IPMI) Integration

· Oracle Clusterware Out-of-place Upgrade

· Oracle Clusterware Administration with Oracle Enterprise Manager

· SCAN for Simplified Client Access

· SRVCTL Command Enhancements for Patching

· Typical Installation Option

· Voting Disk Backup Procedure Change

New Features for Release 1 (11.1)

· Changes in Installation Documentation

· New SYSASM Privilege and OSASM operating system group for Oracle ASM Administration

· Oracle Validated Configuration RPM

Reference:

- Oracle Documentation.

Node Deletetion Steps (Node2)

1. Remove Instance of node2

2. Stop asm for node2 from any nodes.

3. Remove asm for node2 from any nodes.

4. Remove Listener from Node2 usinf NETCA.

5. From Node2:

./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES=node2" -local

6. From Node2, start dbca from Oracle_Home/oui/bin, and remove "DB_HOME"

7. From Node1:

./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES=node1"

8. From Node2, set Oracle_Home to asm_1 and ten fire:

./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES=node2" -local

9. From Node2, start OUI and deinstall ASM Home.

10. From Node1, Set ORACLE_HOME= /u01/app/oracle/product/11.1.0/asm_1

11. From Node1: from /u01/app/oracle/product/11.1.0/asm_1/oui/bin, start OUI

./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES=node1"

12. From Node1, find out ons remote_port no.

cat /u01/app/crs11g/opmn/conf/ons.config

output: 6150 but it should be 6251

13. From Node2: as a root user (#) execute rootdelete.sh from /u01/app/crs11g/install

# /u01/app/crs11g/install/rootdelete.sh

14.From Node-1 first find out the node numbers

# /u01/app/crs11g/bin/olsnodes -n

output : node1 1

node2 2

15. From Node-1 as a root user (#):

# /u01/app/crs11g/install/rootdeletenode.sh node2[Node_Name] 2[node_no]

output:

CRS nodeapps are deleted successfully

clscfg: EXISTING configuration version 4 detected.

clscfg: version 4 is 11 Release 1.

Node deletion operation successful.

'node2' deleted successfully

16. From Node2 set ORACLE_HOME=CRS_HOME and then execute

$$ORACLE_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES=node2" CRS=TRUE -local

17. ./runInstaller and remove CRS_HOME

18. From Node-1:

$ /u01/app/crs11g/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES=node1" CRS=TRUE

19. check node is deleted from

$ ./crs_stat -t

Thanks,

Node Addition in 10g

1. run cluvfy to verify

2. from node1, execute

$/u01/app/crs11g/oui/bin/addNode.sh

3. specify node2 vip address and follow instructions.

4. in the last of installtion it may through an wornig and will ask to click on YES. click on YES

5. from node1,

$ /u01/app/crs11g/bin/racgons add_config node2:6251

6. from Node1,set ORACLE_HOME=ASM_HOME and then execute addNode.sh from $ASM_HOME/oui/bin and Follow instrusctions.

7. From node1, set ORACLE_HOME=DB_HOME and then

$/u01/app/oracle/product/11.1.0/db_1/oui/bin/addNode.sh

and Follow instructions.

8. from node2 start NETCA and configure listener

9. from node1 start dbca from ASM Home to configure ASM instance

10. Again from node1 start dbca from DB Home to add DB instance

11. Then From Node1, start OEM and add node2 database instance with OEM.

12. from Server tab click on addinstance and follow the instruction.

RAC WAIT EVENT PRACTICAL DEMO

In this demo, we'll try to simulate all the wait events

Step-1: Create a table of arround 40 MB

Step-2: Execute the following command to generate wait events

Fire update1 on instance (1) & select query1 on instance (2), update2 on instance(2) & select query2 on instance(1), update3 on instance(3) & select query3 on instance(1).

QUERY-1
--------
declare
cursor c1 is select prod_id,time_id,amount_sold from rac_wait where prod_id between 50 and 100;
v_prodid number;
v_timeid date;
v_amt_sold number;
begin
open c1;
loop
fetch c1 into v_prodid,v_timeid,v_amt_sold;
exit when c1%notfound;
end loop;
close c1;
end;
/

QUERY-2
--------

declare
cursor c1 is select prod_id,time_id,amount_sold from rac_wait where prod_id between 50 and 100;
v_prodid number;
v_timeid date;
v_amt_sold number;
begin
open c1;
loop
fetch c1 into v_prodid,v_timeid,v_amt_sold;
exit when c1%notfound;
end loop;
close c1;
end;
/

QUERY-2
---------

declare
cursor c1 is select prod_id,time_id,amount_sold from rac_wait where prod_id between 101 and 148;
v_prodid number;
v_timeid date;
v_amt_sold number;
begin
open c1;
loop
fetch c1 into v_prodid,v_timeid,v_amt_sold;
exit when c1%notfound;
end loop;
close c1;
end;
/

UPDATE-1
---------

declare
i number:=1;
begin
loop
update rac_wait set amount_sold=i where prod_id=i;
i:=i+1;
commit;
exit when i=50;
end loop;
end;
/

UPDATE-3
---------
declare
i number:=100;
begin
loop
update rac_wait set amount_sold=i where prod_id=i;
i:=i+1;
commit;
exit when i=148;
end loop;
end;
/

UPDATE-2
---------
declare
i number:=51;
begin
loop
update rac_wait set amount_sold=i where prod_id=i;
i:=i+1;
commit;
exit when i=100;
end loop;
end;
/

NFS Configuration for shared archived location in RAC

Step-1: From all the nodes create a new folder to hold archive files.

$ mkdir archive1 (on HOST01)
$ mkdir archive2 (on HOST02)

Step-2: From Node1, set archiving destination for both the nodes.

SQL_ORCL1> alter system set log_archive_dest_1='location=/home/oracle/archive1' scope=both sid='orcl1'

SQL_ORCL1> alter system set log_archive_dest_1='location=/home/oracle/archive2' scope=both sid='orcl2'

Step-3: Verify whether your database is in archived log mode or not

SQL_ORCL1> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archive1
Oldest online log sequence 39
Next log sequence to archive 40
Current log sequence 40

Step-4: Perform logswitch 2-3 times from all the nodes to crate archived files.

SQL_ORCL1> alter system switch logfile;

Step-5: From node1, perform backup of any tablespace to simulate demo

RMAN> backup datafile 4;

Step-6: Perform logswitch 2-3 times from all the nodes to crate archived files.

SQL_ORCL1> alter system switch logfile;

Step-7: Make any tablespace offline and then checkpoint to simulate demo.

SQL_ORCL1> alter tablespace users offline immediate;

SQL_ORCL1> alter system checkpoint;

Step-8: Now try to make that tablespace online and see the error.

SQL_ORCL1> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.793144149'

Step-9: Now from rman, do restore and recovery of that tablespace, while recovery you will recieve an error because remote archives are not visible for node1.

Step-9: Now to solve this problem configure NFS. Using NFS map node2 archive folder with node1.

1. From Node1, create the same folder to map with node2.

$ mkdir archive2

2. From node1, open /etc/exports file and put few entries.

# vim /etc/exports

/home/oracle/archive

# service portmap restart

# service nfs restart

3. From node2, open /etc/exports file and put few entries.

# vim /etc/exports

/home/oracle/archive2 *(rw,sync)

# service portmap restart

# service nfs restart

4. From Node1, mount archive folder of node2 on node1.

# mount host02:/home/oracle/archive2 /home/oracle/archive2

Step-10: Now go and do the recovery.

Steps to configure 2-Node 10 RAC on RHEL-4

Remark-1: H/W requirement for RAC

   a) 4 Machines
       1. Node1
       2. Node2
       3. storage
       4. Grid Control

   b) 2 switchs c) 6 straight cables

Remark-2: S/W requirement for RAC

   a) 10g cluserware b) 10g database

   Both must have the same version like (10.2.0.1.0)

Remark-3: RPMs requirement for RAC

   a) all 10g rpms (Better to use RHEL-4 and choose everything option to install all the rpms)

   b) 4 new rpms are required for installations

1. compat-gcc-7.3-2.96.128.i386.rpm
2. compat-gcc-c++-7.3-2.96.128.i386.rpm
3. compat-libstdc++-7.3-2.96.128.i386.rpm
4. compat-libstdc++-devel-7.3-2.96.128.i386.rpm

Start Machine Preparation

1. Prepare 3 machines

   i. node1.oracle.com
etho    (192.9.201.183)   -    for public network
       eht1    (10.0.0.1) -    for private n/w
       gateway (192.9.201.1)
       subnet (255.255.255.0)

   ii. node2.oracle.com
etho    (192.9.201.187) -    for public network
       eht1    (10.0.0.2)    -    for private n/w
       gateway (192.9.201.1)
       subnet (255.255.255.0)

   iii. openfiler.oracle.com
etho (192.9.201.182) -    for public network
       gateway (192.9.201.1)
       subnet (255.255.255.0)

NOTE:-

-- Here eth0 of all the nodes should be connected by Public N/W using SWITCH-1
-- eth1 of all the nodes should be connected by Private N/W using SWITCH-2

2. network Configuration

#vim /etc/host

192.9.201.183   node1.oracle.com   node1
192.9.201.187   node2.oracle.com   node2
192.9.201.182   openfiler.oracle.com openfiler

10.0.0.1   node1-priv.oracle.com   node1-priv
10.0.0.2   node2-priv.oracle.com   node2-priv

192.9.201.184   node1-vip.oracle.com   node1-vip
192.9.201.188   node2-vip.oracle.com   node2-vip

3. Prepare Both the nodes for installation

a. Set Kernel Parameters (/etc/sysctl.conf)

    kernel.shmall = 2097152
    kernel.shmmax = 2147483648
    kernel.shmmni = 4096
    kernel.sem = 250 32000 100 128
    fs.file-max = 65536
    net.ipv4.ip_local_port_range = 1024 65000
    net.core.rmem_default = 262144
    net.core.rmem_max = 262144
    net.core.wmem_default = 262144
    net.core.wmem_max = 262144

b. Configure /etc/security/limits.conf file
oracle soft nproc 2047
    oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

c. Configure /etc/pam.d/login file
  session required /lib/security/pam_limits.so

d. Create user and groups on both nodes
# groupadd oinstall
   # groupadd dba
   # groupadd oper
   # useradd -g oinstall -G dba oracle
   # passwd oracle

e. Create required directories and set the ownership and permission.
# mkdir –p /u01/crs1020
   # mkdir –p /u01/app/oracle/product/10.2.0/asm
   # mkdir –p /u01/app/oracle/product/10.2.0/db_1
   # chown –R oracle:oinstall /u01/
   # chmod –R 755 /u01/

f. Set the environment variables
$ vi .bash_profile
   ORACLE_BASE=/u01/app/oracle/; export ORACLE_BASE
   ORA_CRS_HOME=/u01/crs1020; export ORA_CRS_HOME
   #LD_ASSUME_KERNEL=2.4.19; export LD_ASSUME_KERNEL
   #LANG=”en_US”; export LANG

4. storage configuration

PART-A Open-filer Set-up
---------------------------

Install openfiler on a machine (Leave 60GB free space on the hdd)

a) Login to root user

b) Start iSCSI target service

    # service iscsi-target start
    # chkconfig –level 345 iscsi-target on

PART –B Configuring Storage on openfiler
-------------------------------------------
a) From any client machine open the browser and access openfiler console (446 ports).

   https://192.9.201.182:446/

b) Open system tab and update the local N/W configuration for both nodes with netmask (255.255.255.255).

c) From the Volume tab click "create a new physical volume group".

d) From "block Device managemrnt" click on "(/dev/sda)" option under 'edit disk' option.

e) Under "Create a partition in /dev/sda" section create physical Volume with full size and then click on 'CREATE'.

f) Then go to the "Volume Section" on the right hand side tab and then click on "Volume groups"

g) Then under the "Create a new Volume Group" specify the name of the volume group (ex- racvgrp) and click on the check box and then click on "Add Volume Group".

h) Then go to the "Volume Section" on the right hand side tab and then click on "Add Volumes" and then specify the Volume name (ex- racvol1) and use all space and specify the "Filesytem/Volume type" as ISCSI and then click on CREATE.

i) Then go to the "Volume Section" on the right hand side tab and then click on "iSCSI Targets" and then click on ADD button to add your Target IQN.

j) then goto the 'LUN Mapping" and click on "MAP".

k) then goto the "Network ACL" and allow both node from there and click on UPDATE.

Note:- To create multiple volumes with openfiler we need to use Multipathing that is quite complex that’s why here we are going for a single volume. Edit the property of each volume and change access to allow.
f) install iscsi-initiator rpm on both nodes to acces iscsi disk

   #rpm -ivh iscsi-initiator-utils-----------
g) Make entry in iscsi.conf file about openfiler on both nodes.

   #vim /etc/iscsi.conf (in RHEL-4)

   and in this file you will get a line "#DiscoveryAddress=192.168.1.2" remove comment and specify your storage ip address here.
       OR

   #vim /etc/iscsi/iscsi.conf (in RHEL-5)
   and in this file you will get a line "#ins.address = 192.168.1.2" remove comment and specify your storage ip address here.

g) #service iscsi restart (on both nodes)
h) From both Nodes fire this command to access volume of openfiler-

   # iscsiadm -m discovery -t sendtargets -p 192.2.201.182

i) #service iscsi restart (on both nodes)

j) #chkconfig –level 345 iscsi on (on both nodes)
k) make the partition 3 primary and 1 extended and within extended make 11 logical partition

   A. Prepare partitions

   1. #fdisk /dev/sdb
       ……
       :e (extended)
       Part No. 1
       First Cylinder:
       Last Cylinder:
       :p
       :n
       :l
       First Cylinder:
       Last Cylinder: +1024M
       …………………
       ……………………
       …………………………..
   2. Note the /dev/sdb* names.
   3. #partprobe
   4. Login as root user on node2 and run partprobe

   B. On node1 login as root user and create following raw devices

       # raw /dev/raw/raw5 /dev/sdb5
       #raw /dev/raw/taw6 /dev/sdb6
       ……………………………….
       ……………………………….
       # raw /dev/raw/raw12 /dev/sdb12

       Run ls –l /dev/sdb* and ls –l /dev/raw/raw* to confirm the above

       -Repeat the same thing on node2

   C. On node1 as root user

       # vi .etc/sysconfig/rawdevices
           /dev/raw/raw5 /dev/sdb5
           /dev/raw/raw6 /dev/sdb6
           /dev/raw/raw7 /dev/sdb7
           /dev/raw/raw8 /dev/sdb8
           /dev/raw/raw9 /dev/sdb9
           /dev/raw/raw10 /dev/sdb10
           /dev/raw/raw11 /dev/sdb11
           /dev/raw/raw12 /dev/sdb12
           /dev/raw/raw13 /dev/sdb13
           /dev/raw/raw14 /dev/sdb14
           /dev/raw/raw15 /dev/sdb15
   D. Restart the raw service (# service rawdevices restart)

       #service rawdevices restart

       Assigning devices:
                  /dev/raw/raw5 -->   /dev/sdb5
       /dev/raw/raw5: bound to major 8, minor 21
                  /dev/raw/raw6 -->   /dev/sdb6
       /dev/raw/raw6: bound to major 8, minor 22
                  /dev/raw/raw7 -->   /dev/sdb7
       /dev/raw/raw7: bound to major 8, minor 23
                  /dev/raw/raw8 -->   /dev/sdb8
       /dev/raw/raw8: bound to major 8, minor 24
                  /dev/raw/raw9 -->   /dev/sdb9
       /dev/raw/raw9: bound to major 8, minor 25
                 /dev/raw/raw10 -->   /dev/sdb10
       /dev/raw/raw10: bound to major 8, minor 26
                  /dev/raw/raw11 -->   /dev/sdb11
       /dev/raw/raw11: bound to major 8, minor 27
                  /dev/raw/raw12 -->   /dev/sdb12
       /dev/raw/raw12: bound to major 8, minor 28
                  /dev/raw/raw13 -->   /dev/sdb13
       /dev/raw/raw13: bound to major 8, minor 29
                  /dev/raw/raw14 -->   /dev/sdb14
       /dev/raw/raw14: bound to major 8, minor 30
                  /dev/raw/raw15 -->   /dev/sdb15
       /dev/raw/raw15: bound to major 8, minor 31
       done

   E. Repeat the same thing on node2 also

   F. To make these partitions accessible to oracle user fire these commands from both Nodes.

       # chown –R oracle:oinstall /dev/raw/raw*
       # chmod –R 755 /dev/raw/raw*
   F. To make these partitions accessible after restart make these entry on both nodes

       # vi /etc/rc.local
           Chown –R oracle:oinstall /dev/raw/raw*
           Chmod –R 755 /dev/raw/raw*

5. SSH configuration (User quivalence)

On node1:- $ssh-keygen –t rsa
$ssh-keygen –t dsa

On node2:- $ssh-keygen –t rsa
$ssh-keygen –t dsa

On node1:- $cd .ssh
$cat *.pub>>node1

On node2:- $cd .ssh
$cat *.pub>>node2
On node1:- $scp node1 node2:/home/oracle/.ssh
On node2:- $scp node2 node2:/home/oracle/.ssh

On node1:- $cat node*>>authowized_keys
On node2:- $cat node*>>authowized_keys

Now test the ssh configuration from both nodes

$ vim a.sh
   ssh node1 hostname
   ssh node2 hostname
   ssh node1-priv hostname
   ssh node2-priv hostname

$ chmod +x a.sh

$./a.sh
first time you'll have to give the password then it never ask for password

6. To run cluster verifier

On node1 :-$cd /…/stage…/cluster…/cluvfy

           $./runcluvfy stage –pre crsinst –n node1,node2
First time it will ask for four New RPMs but remember install these rpms by double clicking because of dependancy. So better to install these rpms in this order (rpm-3, rpm-4, rpm-1, rpm-2)

   1. compat-gcc-7.3-2.96.128.i386.rpm
   2. compat-gcc-c++-7.3-2.96.128.i386.rpm
   3. compat-libstdc++-7.3-2.96.128.i386.rpm
   4. compat-libstdc++-devel-7.3-2.96.128.i386.rpm
And again run cluvfy and check that "It should given a clean cheat" then start clusterware installation.

Thanks,