11gR2 RAC Step By Step Configuration On VMWare part3

STAGE 3
Stage 3 subdivided into
  
3.a. Installing and Configuring Oracle Enterprise Linux 5.6 on VMWare (node1)   
3.b. Configuring Shared storage for RAC within VMWare 
3.c. Preparing a clone VM  (node2) from node1

3.a. Installing and Configuring Oracle Enterprise Linux 5.6 on VMWare (node1) 

Before going on  you have to install  VMware Remote Console: VMware Remote Console enables you to interact with the guest operating system on the host or a remote system. 

Power on Virtual Machine :

Click on console window, the new remote console window will appear. If it’s not appear , it will ask you to install REMOTE plugin console. Just you install it. Don't confuse with racnode1 and racnode2 because that  was already configured in VMWare only racha1 is belongs to our practise

 Click skip option

 

Select appropriate languages 

  

Click on YES

Now the window for partitioning the hard disk will come , here we have to think a little.
Here i selected the choice create a custom layout  , as i wish to create the mount point under linux LVM.  Here i made a simple configuration having only two logical volume for "/" and swap mount point. I will create a folder named "/u01" for both grid and rdbms instance configuration . If you have enough storage you can put grid and rdbms instance configuration on separate volume (Say /u01 and /uo2 each having atleaset 10GB)

 my configuration

/       -  26 GB
/boot - 128 MB
swap  - 4096 MB

 if you have enough space available on hard disk chose the following configuration

/         - 10 GB
/boot   - 128 MB 
swap    - 4096 MB
/uo1     - 10 GB ( for grid and asm )
/uo2     - 10 GB ( for rdbms instance configuration )

First you have to create a /boot partition of size 128 MB 

Then from the remaining size create a physical volume for LVM and create a volume group named "rootvg" from that physical volume. After that create two logical volume named rootlv ( for "/" ) and swaplv  (for swap ) from rootvg . choose the option fill to maximum available size while creating physical volume. 

Name the volume group as rootvg  and create a swap volume from rootvg by clicking on 
new button 

Create "/" volume as rootlv having a size of 26 GB

Select the default grub boot loader 

 

 Select eth0 and put a manual IP , disable IPv6

 

Your server name with localdomain name 

 Select eth1 address , and give IP (private IP for interconnect) don't put any gateway , dns for eth1.

  

select the time zone as Asia/Kolkata

Put root password

  

select customize now

  

select the packages required

  

The article that i followed doesn't tell anything about Cluster Storage , Clustering and Virtualization packages , but i selected those package also because i don't sure there is any dependency in between them.

  

Disable the Firewall service

Disable SELinux  

Select date and time. kdump is optional ,here i selected kdump also but we need to configure kdump later for its functioning . If you not selected kdump , it will not affect our oracle practice .Don't create any extra user here we will create it later. 

 The final stage  click finish and reboot the machine

Click on finish and reboot the machine 

Install VMware Client Tools

On the web console, highlight the "racha1" VM and click the "Install VMware Tools" link and click the subsequent "Install" button. 

Now you can see "VMware Tools" gets mounted in our racha1 desktop. By opening it you can see two files in it (one rpm file and a tar.gz file). You can either use rpm or tar.gz file for installing vmware tools. Here i used rpm file. If you are using tar.gz file for installing vmware tool you have to untar it first by tar -zxvf , after that you have to run

./vmware-install.pl  from vmware-tools-distrib directory.

For installing vmware tool through rpm login to racha1 graphic console, right-click on the "VMwareTools*.rpm" file and select the "Open with "Software Installer"" option. Click the "Apply" button and accept the warning by clicking the subsequent "Install Anyway" button.

Next, run the "vmware-config-tools.pl" script as the root user.

Accept all the default settings and pick the screen resolution of your choice. The VMware client tools are now installed. Issue the "vmware-toolbox" command as the root user. On the subsequent dialog, check the "Time synchronization..." option and click the "Close" button. Ignore any warnings or errors during these stage.  

Oracle Installation Prerequisites

Make sure the shared memory file system is big enough for Automatic Memory Manager to work. 

[root@racha1 ~]# umount tmpfs

[root@racha1 ~]# mount -t tmpfs shmfs -o size=1500m /dev/shm

[root@racha1 ~]# vi /etc/fstab

tmpfs /dev/shm tmpfs size=1500m 0 0

Once the basic installation is complete, install the following packages whilst logged in as the root user. Go to

#cd /media

#  cd "Oracle Linux Server dvd 20110119"  ( the name may be different , if you can't see your iso image files in /media directory you have to edit the CD/DVD setting of your vmware node under summary tab of VI web access console )

# cd server 

install the following packages 

rpm -Uvh binutils-2.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh elfutils-libelf-0.*
rpm -Uvh elfutils-libelf-devel-*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh glibc-headers-2.*
rpm -Uvh ksh-2*
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh make-3.*
rpm -Uvh sysstat-7.*
rpm -Uvh unixODBC-2.*
rpm -Uvh unixODBC-devel-2.*

Add ipaddress in /etc/hosts file

[root@racha1 ~]# vi /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

# Public

192.168.2.201 racha1.localdomain racha1

192.168.2.202 racha2.localdomain racha2

# Private

10.10.1.1 racha1-priv.localdomain racha1-priv

10.10.1.2 racha2-priv.localdomain racha2-priv

# Virtual

192.168.2.211 racha1-vip.localdomain racha1-vip

192.168.2.212 racha2-vip.localdomain racha2-vip

# SCAN

192.168.2.301 rac-scan.localdomain rac-scan

Note. The SCAN address should not really be defined in the hosts file. Instead is should be defined on the DNS to round-robin between 3 addresses on the same subnet as the public IPs. For this installation, we will compromise and use the hosts file. This approach will not work with 11.2.0.2 onwards. Instead you should use the DNS. 

Add or amend the following lines to the "/etc/sysctl.conf" file. 

[root@racha1 ~]# vi /etc/sysctl.conf

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 1054504960

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default=262144

net.core.rmem_max=4194304

net.core.wmem_default=262144

net.core.wmem_max=1048586

Run the following command to change the current kernel parameters.

[root@racha1 ~]# /sbin/sysctl -p

Add the following lines to the "/etc/security/limits.conf" file.    

[root@racha1 ~]# vi /etc/security/limits.conf

oracle soft   nproc  2047

oracle hard  nproc  16384

oracle soft   nofile  1024

oracle hard  nofile   65536

Add the following lines to the "/etc/pam.d/login" file, if it does not already exist. 

session required pam_limits.so

 Disable SElinux

[root@racha1 ~]# cat /etc/selinux/config

# This file controls the state of SELinux on the system.

# SELINUX= can take one of these three values:

# enforcing - SELinux security policy is enforced.

# permissive - SELinux prints warnings instead of enforcing.

# disabled - SELinux is fully disabled.

SELINUX=disabled

# SELINUXTYPE= type of policy in use. Possible values are:

# targeted - Only targeted network daemons are protected.

# strict - Full SELinux protection.

SELINUXTYPE=targeted

[root@racha1 ~]# 

Disable NTP as it is not required in 11gr2 

The Oracle Cluster Time Synchronization Service (ctssd) can synchronize the times of the RAC nodes. In this case we will deconfigure NTP.

[root@racha1 ~]# service ntpd stop

Shutting down ntpd: [FAILED]

[root@racha1 ~]# chkconfig ntpd off

[root@racha1 ~]# mv /etc/ntp.conf /etc/ntp.conf.org

[root@racha1 ~]# rm /var/run/ntpd.pid

rm: cannot lstat `/var/run/ntpd.pid': No such file or directory

User and Group Add

[root@racha1 ~]# groupadd -g 500 oinstall

[root@racha1 ~]# groupadd -g 501 dba

[root@racha1 ~]# groupadd -g 502 oper

[root@racha1 ~]# groupadd -g 503 asmadmin

[root@racha1 ~]# useradd -u 1000 -g oinstall -G dba,oper,asmadmin oracle

[root@racha1 ~]# passwd oracle

Changing password for user oracle.

New UNIX password:

BAD PASSWORD: it is based on a dictionary word

Retype new UNIX password:

passwd: all authentication tokens updated successfully.

[root@racha1 ~]#

Make Directory

[root@racha1 ~]# mkdir -p /u01/app/11.2.0/grid

[root@racha1 ~]# mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1

[root@racha1 ~]# chown -R oracle:oinstall /u01

[root@racha1 ~]# chmod -R 775 /u01

Bash Profile

Log on as Oracle User and edit .bash_profile  

# Oracle Settings

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=racha1.localdomain; export ORACLE_HOSTNAME

ORACLE_UNQNAME=RAC; export ORACLE_UNQNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME

ORACLE_SID=RAC1; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

BASE_PATH=/usr/sbin:$PATH; export BASE_PATH

PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

fi

Note:- In bash_profile specify the oracle_sid and oracle_unqname properly.

Shutdown NODE 1 machine.

[root@racha1 ~]#shutdown –h now

we are shut-downing the system after the installation of vmware-tools .  After the reboot, it is possible that  the monitor will not be recognised. If this is the case don't panic. Because after vmware-tools get installed it will re-configure the display so that there may be a chance for getting  a wide display and need to adjust with cursor.

3.b. Configuring Shared storage for RAC within VMWare  

Create three shared DISK within VMware.

Make directory for sharing disk in host OS ( f:\shareasm )

On the VMware Infrastructure web access panel, click Add Hardware and click Add hard disk by highlighting racha1 node.

 Allocate 10GB for newly added disk.

Create other two hard disk as the same above procedure. After added three disks, Then see your racha1.vmx file , you need to modify like this. making sure the following entries are present. Some of the tries will already be present, some will not. While editing take a note on your SCSI disk name because in your system it may look like scsi1.0 .

.encoding = "windows-1252"

config.version = "8"

virtualHW.version = "7"

floppy0.present = "FALSE"

mks.enable3d = "TRUE"

pciBridge0.present = "TRUE"

pciBridge4.present = "TRUE"

pciBridge4.virtualDev = "pcieRootPort"

pciBridge4.functions = "8"

pciBridge5.present = "TRUE"

pciBridge5.virtualDev = "pcieRootPort"

pciBridge5.functions = "8"

pciBridge6.present = "TRUE"

pciBridge6.virtualDev = "pcieRootPort"

pciBridge6.functions = "8"

pciBridge7.present = "TRUE"

pciBridge7.virtualDev = "pcieRootPort"

pciBridge7.functions = "8"

vmci0.present = "TRUE"

nvram = "racha1.nvram"

virtualHW.productCompatibility = "hosted"

ft.secondary0.enabled = "TRUE"

tools.upgrade.policy = "useGlobal"

powerType.powerOff = "soft"

powerType.powerOn = "hard"

powerType.suspend = "hard"

powerType.reset = "soft"

displayName = "racha1"

extendedConfigFile = "racha1.vmxf"

memsize = "2200"

scsi0:0.present = "TRUE"

scsi0:0.fileName = "racha1.vmdk"

scsi0:0.writeThrough = "TRUE"

scsi0:0.mode = "independent-persistent"

ide1:0.present = "TRUE"

ide1:0.fileName = "f:\Enterprise-R5-U6-Server-x86_64-dvd.iso"

ide1:0.deviceType = "cdrom-image"

ide1:0.allowGuestConnectionControl = "FALSE"

ethernet0.present = "TRUE"

ethernet0.allowGuestConnectionControl = "FALSE"

ethernet0.virtualDev = "e1000"

ethernet0.features = "1"

ethernet0.wakeOnPcktRcv = "FALSE"

ethernet0.networkName = "Bridged"

ethernet0.addressType = "generated"

ethernet1.present = "TRUE"

ethernet1.allowGuestConnectionControl = "FALSE"

ethernet1.virtualDev = "e1000"

ethernet1.features = "1"

ethernet1.wakeOnPcktRcv = "FALSE"

ethernet1.networkName = "Bridged"

ethernet1.addressType = "generated"

usb.present = "TRUE"

ehci.present = "TRUE"

guestOS = "rhel5-64"

uuid.location = "56 4d 04 f2 f0 af b7 33-a8 70 c6 25 3b f0 f3 44"

uuid.bios = "56 4d 04 f2 f0 af b7 33-a8 70 c6 25 3b f0 f3 44"

vc.uuid = "52 5a 80 b9 e0 d2 6f 2b-c5 04 36 06 8c c4 80 88"

ide1:0.startConnected = "FALSE"

ethernet0.generatedAddress = "00:0c:29:f0:f3:44"

ethernet1.generatedAddress = "00:0c:29:f0:f3:4e"

tools.syncTime = "TRUE"

ide1:0.clientDevice = "FALSE"

scsi0:0.redo = ""

vmotion.checkpointFBSize = "134217728"

pciBridge0.pciSlotNumber = "17"

pciBridge4.pciSlotNumber = "21"

pciBridge5.pciSlotNumber = "22"

pciBridge6.pciSlotNumber = "23"

pciBridge7.pciSlotNumber = "24"

scsi0.pciSlotNumber = "16"

usb.pciSlotNumber = "32"

ethernet0.pciSlotNumber = "33"

ethernet1.pciSlotNumber = "34"

ehci.pciSlotNumber = "35"

vmci0.pciSlotNumber = "36"

ethernet0.generatedAddressOffset = "0"

ethernet1.generatedAddressOffset = "10"

vmci0.id = "1005646662"

disk.locking = "FALSE"

diskLib.dataCacheMaxSize = "0"

diskLib.dataCacheMaxReadAheadSize = "0"

diskLib.dataCacheMinReadAheadSize = "0"

diskLib.dataCachePageSize = "4096"

diskLib.maxUnsyncedWrites = "0"

scsi0.present = "TRUE"

scsi0.sharedBus = "VIRTUAL"

scsi0.virtualDev = "lsilogic"

scsi0:1.present = "TRUE"

scsi0:1.fileName = "f:\shareasm\asm1.vmdk"

scsi0:1.writeThrough = "TRUE"

scsi0:1.mode = "independent-persistent"

scsi0:1.deviceType = "disk"

scsi0:1.redo = ""

scsi0:2.present = "TRUE"

scsi0:2.fileName = "f:\shareasm\racha2.vmdk"

scsi0:2.writeThrough = "TRUE"

scsi0:2.mode = "independent-persistent"

scsi0:2.deviceType = "disk"

scsi0:2.redo = ""

scsi0:3.present = "TRUE"

scsi0:3.fileName = "f:\shareasm\asm3.vmdk"

scsi0:3.writeThrough = "TRUE"

scsi0:3.mode = "independent-persistent"

scsi0:3.deviceType = "disk"

scsi0.3.redo = ""

scsi0:3.redo = ""

usb.autoConnect.device0 = ""

Now Restart the NODE 1 racha1 machine and after restarted , partition your disks like below 

[root@racha1 ~]# cd /dev/

[root@racha1 dev]# ls sd*

sda sda1 sda2 sdb sdc sdd

[root@racha1 dev]# fdisk sdb

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel. Changes will remain in memory only,

until you decide to write them. After that, of course, the previous

content won't be recoverable.

The number of cylinders for this disk is set to 1305.

There is nothing wrong with that, but this is larger than 1024,

and could in certain setups cause problems with:

1) software that runs at boot time (e.g., old versions of LILO)

2) booting and partitioning software from other OSs

(e.g., DOS FDISK, OS/2 FDISK)

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): Command (m for help): n

Command action

e extended

p primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-1305, default 1): --> just enter her

Using default value 1

Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305): --> just enter her

Using default value 1305

Command (m for help): p

Disk sdb: 10.7 GB, 10737418240 bytes

255 heads, 63 sectors/track, 1305 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

sdb1 1 1305 10482381 83 Linux

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

[root@racha1 dev]#  

Do It as same again procedure for other remaining two Disks (sdc and sdd)

ASMLIB Install

Download ASMLIB rpm package for your OS requirements , i installed following package 

oracleasmlib-2.0.4-1.el5

oracleasm-support-2.1.4-1.el5

oracleasm-2.6.18-238.el5-2.0.5-1.el5

Some of these packages are avaiable in OEL 5.6 CD media  , remaining you can download from OTN website.


Install the packages using the following command.
 #rpm -Uvh oracleasm*.rpm  

Configure ASMLib using the following command.

[root@racha1 ~]# oracleasm configure -i

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver. The following questions will determine whether the driver is

loaded on boot and what permissions it will have. The current values

will be shown in brackets ('[]'). Hitting <ENTER> without typing an

answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle

Default group to own the driver interface []: dba

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

[root@racha1 ~]# /usr/sbin/oracleasm init

Creating /dev/oracleasm mount point: /dev/oracleasm

Loading module "oracleasm": oracleasm

Mounting ASMlib driver filesystem: /dev/oracleasm

[root@racha1 ~]# /usr/sbin/oracleasm createdisk DISK1 /dev/sdb1

Writing disk header: done

Instantiating disk: done

[root@racha1 ~]# /usr/sbin/oracleasm createdisk DISK2 /dev/sdc1

Writing disk header: done

Instantiating disk: done

[root@racha1 ~]# /usr/sbin/oracleasm createdisk DISK3 /dev/sdd1

Writing disk header: done

Instantiating disk: done     

It is unnecessary, but we can run the "scandisks" command to refresh the ASM disk configuration.

# /usr/sbin/oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks... 

We can see the disk are now visible to ASM using the "listdisks" command.

# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3

The shared disks are now configured for the grid infrastructure.   

3.c. Preparing a clone VM  (node2) from node1

Shutdown  racha1 node

[root@racha1 install]# shutdown -h now

Copy racha1 to the same location and rename the folder  as racha2

Edit the contents of F:\racha2\racha1.vmx

displayName = "racha2"

Ignore discrepancies with the file names in the "F:\racha2\" directory. This does not affect the action of the virtual machine. In the VMware Infrastructure Web Access Console, select the "Virtual Machine > Add Virtual Machien to Inventory" menu options and browse for the "F:\racha2\racha1.vmx" file. Once opened, the racha2 virtual machine is visible on the console. 

Start the racha2 virtual machine by clicking the "Play" button on the toolbar. Select the "I _copied it" option click the "OK" button when prompted. 

Ignore any errors during the server startup. We are expecting the networking components to fail at this point.

Now the another cloned copy of node1 virtual machine running. 

Log on to the virtual machine racha2 as root

System > Administration > Network

 

Remove the devices with the "%.bak" nicknames. To do this, highlight a device, deactivate, then delete it. This will leave just the regular "eth0" and "eth1" devices. Highlight the "eth0" interface and click the "Edit" button on the toolbar.

Click on the "Hardware Device" tab and click the "Probe" button. Then accept the changes by clicking the "OK" button.

Click on the "DNS" tab and change the host name to "racha2.localdomain", then click on the "Devices" tab. 

 Configure Eth1 

 Add ip address, subnet mask and Don’t add default gateway ip address here.

  

Once you are finished, save the changes (File > Save) and activate the network interfaces by highlighting them and clicking the "Activate" button. Once activated, the screen should look like the following image. 

If you want restart your network service ,you need to use below commands.

#service network restart

Edit Bash_profile of Node 2 Oracle user path correct to ORACLE_SID and HOSTNAME

[root@racha1 ~]# su - oracle

[oracle@racha1 ~]$ vi .bash_profile

ORACLE_HOSTNAME=racha2.localdomain; export ORACLE_HOSTNAME

ORACLE_UNQNAME=RAC; export ORACLE_UNQNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME

ORACLE_SID=RAC2; export ORACLE_SID 

 Start the racha1 virtual machine and restart the racha2 virtual machine. When both nodes have started, check they can both ping all the public and private IP addresses using the following commands. 

At node1 

[root@racha1 ~]# ping -c 3 racha2

PING racha2.localdomain (192.168.2.202) 56(84) bytes of data.

64 bytes from racha2.localdomain (192.168.2.202): icmp_seq=1 ttl=64 time=62.2 ms

64 bytes from racha2.localdomain (192.168.2.202): icmp_seq=2 ttl=64 time=0.196 ms

[root@racha1 ~]# ping -c 3 racha2-priv

PING racha2-priv.localdomain (10.10.1.2) 56(84) bytes of data.

64 bytes from racha2-priv.localdomain (10.10.1.2): icmp_seq=1 ttl=64 time=0.923 ms

64 bytes from racha2-priv.localdomain (10.10.1.2): icmp_seq=2 ttl=64 time=0.180 ms

At node 2 

[root@racha2 ~]# ping -c 3 racha1

PING racha1.localdomain (192.168.2.201) 56(84) bytes of data.

64 bytes from racha1.localdomain (192.168.2.201): icmp_seq=1 ttl=64 time=0.500 ms

64 bytes from racha1.localdomain (192.168.2.201): icmp_seq=2 ttl=64 time=0.160 ms

[root@racha2 ~]# ping -c 3 racha1-priv

PING racha1-priv.localdomain (10.10.1.1) 56(84) bytes of data.

64 bytes from racha1-priv.localdomain (10.10.1.1): icmp_seq=1 ttl=64 time=0.430 ms

64 bytes from racha1-priv.localdomain (10.10.1.1): icmp_seq=2 ttl=64 time=0.176 ms 

Now our clone racha2 has been completed , next we have to install oracle grid software 

11gR2 RAC Step By Step Configuration On VMWare part2

STAGE-2

2.a RAC Nodes Configuration

once vmware server get installed , open the VMware Intrastructure Web Access Console from desktop shortcut or by pointing your browser to one of the two following URLs depending on whether you need Secure HTTP or not.

http://machine-name:8222
https://machine-name:8333

if you are using Secure HTTP, your browser may fail due to the self-signed certificate. In Firefox you can solve this by clicking the "Or you can add an exception..." link on the failure page.

 On the resulting page, click the "Add Exception..." button..

On the "Add Security Exception" page, click the "Get Certificate" button, then click the "Confirm Security Exception" button.

  

 You are then presented with the web-based login screen.

The first stage that i got stuck is here , as the web console asking for username and password , what i have to provide ? it did'nt ask for configuring a username and password while installation  . I given my os username that i used to login (mahesh) but it is not taking..

Then i googled the error and Finally, I found from searching VMware site that VMware server always expects the user name "Administrator".  By default windows 7 does not enable administrator account with "Administrator" name.  To do this, you need to execute following command at the command line prompt. 

Command window must be opened with "Run as Administrator".  You can do this by going to "All Programs->Accessories".  Here you would see "Command Prompt".  Take your cursor there and press right click on that line.  You would see "Run as Administrator".  Click on that. It opens the command prompt window.

There you should type "net user administrator /active:yes".  This would be enable hidden "Administrator" user. 
Next step is to assign the password to "Administrator" user.  Go to 'Control Panel->User Accounts and Family Safety->Add or remove user accounts".  Here you would see "Administrator" user. Click on it and add a password to it. Now you should be able to login in to the VMware server using "Administrator" and password you have set for the user.     

 Log in with the user specified during the config stage and you are presented with the VMware Intrastructure Web Access Console.

After logged in select the option Create Virtual Machine

Name of the Virtual Machine  racha1

  

In vmware server all files related to virtual machines are stored under datastore , by default it will detect a datastore  named 'standard' and you can use that also . Here i created a seperate datastore name rac in my external hard disk by using the option add datastore . 

 Select the "Linux operating system" option, and set the version to "Red Hat Enterprise Linux 5 (64-bit)", then click the "Next" button. 

 Enter the required amount of memory and number of CPUs for the virtual machine, then click the "Next" button. You should enter a minimum of 2048MB of memory.

  Click on the "Create a New Virtual Disk" link or click the "Next" button.

 Create New Virtual DISK (Hard Disk) , Size 30 GB

 Click the "Add a Network Adapter" link or click the "Next" button.

Select the "Bridged" option and click the "Next" button.Add a Network Adapter first with bridged Network (RAC Environment We need to configure TWO adapter, Add another adapter later ).

  
Nest option is to configure CD-DRIVE here i chose Use an ISO image as i dowloaded the OEL5.6 iso image       from  https://edelivery.oracle.com/linux . If you plan to use ISO image file the software file should be in Virtual Machine Datastore. 

 And here i selected the iso image of OEL5.6 (Enterprise-R5-U6-Server-x86_64-dvd.iso) that i kept in f:\ drive ( ie in my datastore) . I downlaoded a single iso file that comes to 3.53 GB.

On the next screen Click the "Don't Add a Floppy Drive" link. as we dont need floppy drive

Click the "Add a USB Controller" link, or click the "Next" button.

   

Add another one Network Adapter using with bridged network by expanding the more hardware tab on the last ready to complete screen

  

The virtual machine is now configured so we can start the guest operating system installation. 

11gR2 RAC Step By Step Configuration On VMWare part1

I successfully configured 11gr2 RAC on Oracle Enterprise Linux 5.6 (64 bit)  on my laptop , for that i  inspired the article posted by mohamed azar on his blog
http://mohamedazar.wordpress.com
But during the installation i stucked in a lot of stage , that i want to share here.

I configured the whole setup in my windows 7 (i5-64 bit) laptop having  8GB RAM (i suggest 8GB even you can see many blogs and website telling that they configured under 6GB , i fed up with many stages as my LAP goes slow). For the entire set up you will require minimum of  90GB free space in your hard disk.
30 GB for each node and a 30GB for shared storage ( In 11gr2 we can store datafiles , OCR and voting disk in ASM  )

I divide the entire stage into 5 parts

1. Vmware installation
2. Nodes configuration on vmware
3. Installing and configuring Oracle Enterprise Linux 5.6 on VMware + Preparing VMware clone for node2
3. GRID configuration (  In 11gr2 clusterware is renamed as GRID)
5. Rdbms instance configuration

I indebted  Mohamed azar for his effort and here i am using his documents with some alteration.

STAGE 1

1. Vmware configuration

1. First you have to download vmware server , here i used the version "VMware-server-2.0.2". You can download the software free from vmware website , for that you have to register on vmware website after that you will get the link for download. Make sure to save the  the appropriate serial number that you got from vmware website during registration.
So to get VMWare Server free serial number or product key, simply complete this registration. You can get for free up to 100 serial numbers at one go without any  charges or fees.

After getting downloaded (size should be near to 520MB) ,install the software on you laptop.
It is very simple to install  just go forward with each stage as its says..

 Installing VMware Server

Once you download the VMware Server 2.0 file, double click on the file to start the installation

The above screen should come up when you double click on the installation file. wait a few minutes until the installation wizard comes up:

  

click on the Next button.

accept the license agreement on the following screen, and click Next

  
 On the next window choose the destination folder.The default path should be fine.

Click on the next button.

Next, you will need to choose  the storage path for your virtual machines. The default path is  again,

As you can see, you also need to specify the full qualified domain of the machine VMware server will be hosted on. the default settings are fine with me, because I will be using VMware on my local computer. also uncheck “Allow virtual machines to start and stop automatically with the system” that is if you are not going to be using VMware server 2.0 regularly.

click next, after you enter the settings. Next, you will need to choose where you will want to have the shortcuts to launch VMware server. click next, after that.

on the  next screen, you are ready for the installation, click on Install.

After a while through the installation, VMware server installation wizard should ask you for the registration key:

  

Enter your own registration key you got on your e-mail from VMware. click Enter after that.

the installation of VMware Server 2.0 should be completed.

click on Finish.

restart the computer to launch VMware server for  the first time. 

ORA-1652: unable to extend temp segment by x in tablespace  YYY

Today i faced ORA-1652 error on my production environment ,  this type of issue is common with TEMP tablespace but here it comes on non-TEMP tablespace

i am curious about this error , because my datafiles  are in ASM with autoextensible  ON.
there may be no  chance for getting ORA-1652 error .

Later i find out the root cause of the problem , as my datafile belonging to the corresponding
tablespace  reached the maximum limit of allowed size .

As my tablepace is created as SFT (small file tablespace) , there should be a limitation of 32 GB
for each datafile (assuming that block size was 8k)

So i added one more datafile in affected tablespace using following query

alter tablespace yyy add datafile '+DATA' size 5000M;

And the issue got resolved.

Some Useful oracle query for every dba

1. To find the DDL of schema's in Oracle Database Command to find the DDL of Schema's:

select dbms_metadata.get_ddl( 'USER','username' ) from dual

     UNION ALL

     select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','username') from

dual

          UNION ALL

          select dbms_metadata.get_granted_ddl('OBJECT_GRANT','username')

from dual

               UNION ALL

               select dbms_metadata.get_granted_ddl('ROLE_GRANT','username')

from dual;

2.how to trace a querry before executing that

Enable SQL TRACE for a SQL*Plus session (this starts tracing for an individual session):

sql> alter session set SQL_TRACE true;

now run your querry

find the hint of trace file generated using following querry

Select spid, s.sid,s.serial#, s.username, p.program

from v$process p, v$session s

where p.addr = s.paddr

and s.sid = (select sid from v$mystat where rownum=1);

--- the aboe query will returns the sid of the given session

Disable SQL TRACE for the SQL*Plus session:

alter session set SQL_TRACE false;

3.See what SQL users are running on the system

select a.sid, a.serial#, b.sql_text from v$session a, v$sqlarea b

where a.sql_address=b.address

and a.username='SFMS';

4.Here is a query that gives us a list of blocking sessions and the sessions that they are blocking:

select

   blocking_session,

   sid,

   serial#,

   wait_class,

   seconds_in_wait

from

   v$session

where

   blocking_session is not NULL

order by

   blocking_session;

BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS           SECONDS_IN_WAIT

---------------- ---------- ---------- -------------------- --------

             148        135      61521 Idle                              64

In this case, we find that session 148 is blocking session 135 and has been for 64 seconds. We would then want to find out who is running session 148, and go find them and see why they are having a problem.

note:-blocking_session is availabe from 10g onwards

5.Identify the blocking session

   select l1.sid, ' IS BLOCKING ', l2.sid

    from v$lock l1, v$lock l2

   where l1.block =1 and l2.request > 0

   and l1.id1=l2.id1

   and l1.id2=l2.id2;

Detailed inforamation

select s1.username || '@' || s1.machine

    || ' ( SID=' || s1.sid || ' )  is blocking '

   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

    from v$lock l1, v$session s1, v$lock l2, v$session s2

    where s1.sid=l1.sid and s2.sid=l2.sid

   and l1.BLOCK=1 and l2.request > 0

   and l1.id1 = l2.id1

   and l2.id2 = l2.id2 ;

identifieying the content of locked row

select do.object_name,

   row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,

    dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )

    from v$session s, dba_objects do

    where sid=143

    and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

if your output is like this

OBJECT_NAME     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C

--------------- ------------- -------------- --------------- ------------- ------------------

TSTLOCK                 88519             16          171309             0 AAAVnHAAQAAAp0tAAA

Then obtain the locked row by

select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR

--- ---

1   a

6.redo log switch per day

select trunc(completion_time) rundate

,count(*) logswitch

,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"

from v$archived_log

group by trunc(completion_time)

order by 1 desc;

7.High Physical Read Sessions

Select OSUSER os_user,username,PROCESS pid, ses.SID sid,SERIAL#,PHYSICAL_READS,

BLOCK_CHANGES

from v$session ses,

v$sess_io sio

where ses.SID = sio.SID

and username is not null

and status='ACTIVE'

order by PHYSICAL_READS;

8. dbms_stats.gather_table_stats - Stats Gather Dynamic Sql script for tables

SELECT    ' exec sys.dbms_stats.gather_table_stats (ownname=>'''
       || owner
       || ''',tabname=>'''
       || table_name
       || ''', estimate_percent=>SYS.DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>''FOR ALL COLUMNS SIZE 1'',degree=>8,cascade=>true,no_invalidate=>false);'
  FROM dba_tables
WHERE table_name IN
          ('DEPT','BONUS','EMP','SALGRADE')
   AND owner = 'SCOTT';

9. You can check freespace in undo tablesapce with :

select sum(bytes)/1024/1024 "available M in RBS" from dba_undo_extents where status='EXPIRED';

Blocks are expired when undo_retention is past after the commit/rollback of the transaction.

10. how to calculate network bandwidth in data guard configuration

SELECT DT,
SUM(RB*8/3600000000*1.3) Mbps_REQ_A_DAY,
MIN(RB*8/3600000000*1.3) MIN_Mbps_REQ_AN_HOUR,
MAX(RB*8/3600000000*1.3) MAX_Mbps_REQ_AN_HOUR ,
AVG(RB*8/3600000000*1.3) AVG_Mbps_REQ_AN_HOUR
FROM
(
SELECT TRUNC (COMPLETION_TIME) DT,
TO_CHAR (COMPLETION_TIME,'HH24') HH,
SUM(BLOCKS*BLOCK_SIZE) RB
FROM
V$ARCHIVED_LOG
WHERE COMPLETION_TIME > SYSDATE-5
AND DEST_ID=1
GROUP BY TRUNC(COMPLETION_TIME),
TO_CHAR (COMPLETION_TIME, 'HH24')
)
GROUP BY DTorder by DT; 

11. Query to find out features not available in particular database edition

SELECT Parameter,Value FROM V$OPTION Where Value = 'FALSE';

How to Prevent a User to Drop Own Objects

An Oracle user is always granted to drop their own objects. To prevent a user to drop their own objects, we can use DDL triggers.

Here's sample trigger that will prevent MAHI user to drop EMP table:

connect as sys user

sql>CREATE OR REPLACE TRIGGER trigger_prevent_drop BEFORE DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = 'TABLE'
AND ora_dict_obj_owner = 'MAHI'
AND ora_login_user = 'MAHI'
AND ora_dict_obj_name='EMP'
THEN
raise_application_error (-20000, 'YOU CAN NOT DROP EMP TABLE!');
END IF;
END;
/

Trigger created.

then login as mahi user and try to drop emp table

SQL> conn mahi
Enter password:
Connected.
SQL> drop table emp;
drop table emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: YOU CAN NOT DROP EMPLOYEES TABLE!
ORA-06512: at line 7