Simple and Handy script for RAC: who’s opened the DB and when

When nodes on a clustered database fail to start, or just crash alone, it’s important to find patterns of behaviour. If you have a more than 2-node RAC cluster and need to figure out when did each instance started and opened the database, I have a very simple script that can help you out.

SQL> column startup_time format a30
SQL> select db_name, instance_name, startup_time
2  from  DBA_HIST_DATABASE_INSTANCE order by 3 asc
3  /

DB_NAME   INSTANCE_NAME    STARTUP_TIME
--------- ---------------- ------------------------------
ORCL      ORCL1            09.09.30 19:10:30,000
ORCL      ORCL2            09.09.30 19:14:13,000
ORCL      ORCL2            09.10.08 16:59:07,000
ORCL      ORCL1            09.10.08 17:07:14,000
ORCL      ORCL1            09.10.20 11:30:17,000
ORCL      ORCL2            09.10.20 13:38:40,000

In this example you see that on the 30th September 2009 instance 1 opened the database at 19:10:30 whilst the second instance only did the same four minutes later. On the 8th October, the second instance was the first one opening the database, and later on the 20th October, there’s a gap of 2 hours and 8 minutes between them.

Simple and handy script that let’s you figure out who’s opened the database and when.

LMC.

Putting ACFS to work: how to create a mount point in the new Oracle File System

This post is a translation of another one that I’ve originally wrote in portuguese. I’ve decided to share with the international Oracle community through my weblog.

ACFS stands for ASM Cluster File System. ACFS is a new file system created by Oracle that is created on top of an ASM Disk Group. The architecture is very simple and these mount points can contain both general purpose files (except  db files) or Oracle Home binaries, which is very good for sharing binaries.

There are three ways to setup ACFS:

- Web (with Enterprise Manager)

- With  ASMCA (ASM Configuration Assistant) a graphic tool

- or through command line statements

We will show you examples using ASMCA because it’s nicer

Here’s the step-by-step:

1 – We had two 1GB LUNs/Disks for this example with which two raw devices were created:

[oracle@node1 ~]$ cat /etc/rc.local

#!/bin/sh

#

# This script will be executed *after* all the other init scripts.

# You can put your own initialization stuff in here if you don’t

# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

raw /dev/raw/raw1 /dev/sdb1

raw /dev/raw/raw2 /dev/sdc1

chmod 666 /dev/raw/raw1

chmod 666 /dev/raw/raw2

chown oracle:oinstall /dev/raw/raw1

chown oracle:oinstall /dev/raw/raw2

2 – Now it’s time to call ASMCA:

[oracle@node1 ~]$ . oraenv

ORACLE_SID = [+ASM] ?

ORACLE_HOME = [/home/oracle] ? /u01/11.2.0/grid

The Oracle base for ORACLE_HOME=/u01/11.2.0/grid is /u01/oracle

[oracle@node1 ~]$ asmca &

3 – Create one disk group (DG) with those two raw devices:

4 – We then create a ASM volume on top of this DG, naming it “volumezinho” (we show here several ways of doing so):

There are a few advanced options in ASMCA when you create a volume:

5 – Create a directoy on the ext3 Linux file system so when can mount there the device created by “volumezinho”

[root@node1 ~]# mkdir /u02/oradata/acfs
[root@node1 ~]# chown oracle:oinstall /u02/oradata/acfs

6 – Now let’s create the ACFS mount point. We can choose between one of these two options:

But still you have to mount it manually:

Et voilá!

Now we can go to the directory mounted on the ASM volume formated as ACFS, and create some bogus text file:



[oracle@node1 ~]$ df -m
Filesystem           1M-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00 47551     16389     28708  37% /
/dev/sda1                   99        11        83  12% /boot
tmpfs                     1014       662       352  66% /dev/shm
/dev/sdd1                  512       351       162  69% /u02/oradata/cluster-lmc
/dev/asm/volumezinho-486   512       101       412  20% /u02/oradata/acfs/data_volumezinho

[oracle@node1 ~]$ cd /u02/oradata/acfs/data_volumezinho
[oracle@node1 data_volumezinho]$ ll
total 68
-rw-r--r-- 1 oracle oinstall    89 Sep 24 14:33 ficheiro.txt
drwx------ 2 root   root     65536 Sep 24 14:20 lost+found

[oracle@node1 data_volumezinho]$ cat ficheiro.txt


Primeiro ficheiro de texto criado num volume ASM no qual foi montado um ACFS!!!


Yupii!!


[oracle@node1 data_volumezinho]$



Technology is an amazing thing. So many twists and turns to end up in the same place ...

SSH User Equivalence in 11gR2 Real Application Clusters Clusterware installation

There has been a while since a new Oracle Database release hasn’t hit me with so many key new features.
And for those who had suffer long hours of horror movie-like feelings configuring ssh user equivalence, knowing that Oracle Database 11g Release 2 does this by itself based on the /etc/hosts alone, it’s like a blender that fetches the fruit from the fridge by itself and puts back the juice in the fridge so you can open the door and selfserve from that delicious natural fruit juice. It’s magic, it’s the impossible made possible, it’s … let’s see some images because I’m running out of metaphores:

When I saw the “SSH Connectivity” button … my heart froze

I took a deep breath and clicked. All of the sudden a new part in the installation window showed me a new and wonderfull world of self-configurable SSH user equivalence. I’ve pushed the “TEST” button so I could see the more than expected error because I’ve done nothing so far to configure it, and so I got the following message:

This is more than expected, not because I was installing a one-node clusterware, but because I haven’t done nothing yet in this particular issue of SSH Connectivity. And to tell you the truth I wasn’t in the mood to do it, so I decided to take another deep (deeper) breath and click on the “SETUP” button, whishing the impossible and knowing that another complex form might pop-up for me to fill. But my 14-year Oracle experience didn’t prepared me for what was about to hit me: total and complete self configuring SSH Connectivity. Take a look:

I’ve almost “jizzed in my pants”

Disclaimer from the docs:

“To enable the script to run, you must remove stty commands from the profiles of any Oracle software installation owners, and remove other security measures that are triggered during a login, and that generate messages to the terminal. These messages, mail checks, and other displays prevent Oracle software installation owners from using the SSH configuration script that is built into the Oracle Universal Installer. If they are not disabled, then SSH must be configured manually before an installation can be run.”

The unwritten rule of Clusterware User Equivalence step in a RAC installation

These post titles just keep getting better!

Well the message on this one is pretty simple: after configuring user-equivalence (as a pre-requisite for Oracle Clusterware installation) you should test it not just with the public name, but with all names that are at the hosts file, except for the vip ones.

And you should do it at each and every node of your RAC network.

At a site with the following configuration:

[root@node1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain   localhost
192.168.10.100  node1.oracle.com        node1
192.168.10.101  node1-priv.oracle.com   node1-priv
192.168.10.102  node2.oracle.com        node2
192.168.10.103  node2-priv.oracle.com   node2-priv

You should test user equivalence with all those 8 names! … at each node!

First nodes first:

[oracle@node1 ~]# ssh node1 date
Qui Fev 26 23:44:30 WET 2009
[oracle@node1 ~]# ssh node1.oracle.com date
Qui Fev 26 23:44:31 WET 2009
[oracle@node1 ~]# ssh node2 date
Qui Fev 26 23:44:34 WET 2009
[oracle@node1 ~]# ssh node2.oracle.com date
Qui Fev 26 23:44:40 WET 2009
[oracle@node1 ~]# ssh node1-priv date
Qui Fev 26 23:45:30 WET 2009
[oracle@node1 ~]# ssh node1-priv.oracle.com date
Qui Fev 26 23:45:31 WET 2009
[oracle@node1 ~]# ssh node2-priv date
Qui Fev 26 23:45:34 WET 2009
[oracle@node1 ~]# ssh node2-priv.oracle.com date
Qui Fev 26 23:45:40 WET 2009

And now for something completely different (same stuff but at node2):

[oracle@node2 ~]# ssh node1 date
Qui Fev 26 23:46:30 WET 2009
[oracle@node2 ~]# ssh node1.oracle.com date
Qui Fev 26 23:46:31 WET 2009
[oracle@node2 ~]# ssh node2 date
Qui Fev 26 23:46:34 WET 2009
[oracle@node2 ~]# ssh node2.oracle.com date
Qui Fev 26 23:46:40 WET 2009
[oracle@node2 ~]# ssh node1-priv date
Qui Fev 26 23:47:30 WET 2009
[oracle@node2 ~]# ssh node1-priv.oracle.com date
Qui Fev 26 23:47:31 WET 2009
[oracle@node2 ~]# ssh node2-priv date
Qui Fev 26 23:47:34 WET 2009
[oracle@node2 ~]# ssh node2-priv.oracle.com date
Qui Fev 26 23:47:40 WET 2009

Setting up OCR and Voting Disk with OCFS2

Many people use ASM for database common storage on a RAC but preffer OCFS for OCR and Voting disk instead of raw devices. For those people here’s the steps needed to take on setting up this scenario on a Linux box (all commands ran as root):

1) Install OCFS2 (get it from the OCFS2 Site) on each node and set it up acording to this manual.

2) Create the mount point directory on each node:

mkdir /OCFS2_mountpoint/

3) From one node format the partition “/dev/sdb1″ with this FS format:

mkfs.ocfs2 -b 4K -C 32K -N 4 -L /OCFS2_mountpoint /dev/sdb1

4) On each node mount the partition and edit the “/etc/fstab” file so it can mount on boot:

mount -t ocfs2 -o datavolume,nointr /dev/sdb1 /OCFS2_mountpoint

in /etc/fstab file add the following line:

/dev/sdb1   /OCFS2_mountpoint    ocfs2 _netdev,datavolume,nointr 0 0

5) From one node create the common directory for the CRS files:

mkdir /OCFS2_mountpoint/oracrs

6) From one node set up permissions:

chown oracle:oinstall /OCFS2_mountpoint/oracrs
chmod 775 /OCFS2_mountpoint/oracrs

7) Fill on the files with 100MB for OCR and 20MB for voting disk (do this command as “oracle” user):

dd if=/dev/zero of=/OCFS2_mountpoint/oracrs/votingdisk bs=8192 count=2560
dd if=/dev/zero of=/OCFS2_mountpoint/oracrs/ocrfile bs=8192 count=12800

On Linux 5.x (Tikanga) the key point of doing it for 10g engine is the “nointr” flag used when mounting.

Who’s afraid of Silent Install?

Almost every Oracle Configuration Assistant and installer has a silent or non-graphical version. When you get used to it, you will feel that the classical “NEXT-NEXT-NEXT…” is a waste of time.

Here’s an example of a situation where you will have to use silent install or otherwise you’ll get mad:

Remote installations often use a Windows PC to connect to the database server, through an SSH client like puTTY. When you want to install or configure something through a graphical tool, you must export the DISPLAY environment variable to the PC’s IP address where you have a X Server installed.

Let’s imagine that your remote connection to the Windows PC fails and after a minute or two you’re back. The X Server might blank out you Oracle Installer or Configuration java windows and you are left in the middle of nowhere. Not knowing where you’re at you will have to resume installation and if (God forbids!) you happened to be in the middle of a Clusterware installation you have to unwind your installation, delete a bunch of files and get back to the install process again.

The solution to this scenario: silent install.

The syntax it’s very straingh forward:

./runInstaller -silent -responseFile  my_parameters_for_db_install.rsp

The last part of the command is called “Response File” and you can find a sample one at every installation media inside the “response” folder. There are sample response files for every graphical tool like netca or dbca and installation types like standard, enterprise, personal, custom and even patchset response files or clusterware.

To call a configuration assistant in silent mode here’s an example for the VIPCA (Virtual IP Configuration Assistan):

./vipca -silent -nodelist node1,node2 -vipfile /opt/oracle/crs/bin/vipfile -orahome /opt/oracle/crs

Where VIPFILE is:

node1-vip=192.20.150.68/192.20.0.0/eth0
node2-vip=192.20.150.69/192.20.0.0/eth0

You can also do de-installations with silent install.

Here’s another example of a simple response file for an Oracle 10g clusterware installation (comments begin with “#”):

RESPONSEFILE_VERSION=2.2.1.0.0
#------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall
#------------------------------------------------------------------------------
FROM_LOCATION="../stage/products.xml"
ORACLE_HOME="/opt/oracle/crs"
ORACLE_HOME_NAME="OraClusterware10ghome1"
TOPLEVEL_COMPONENT={"oracle.crs","10.2.0.1.0"}
SHOW_SPLASH_SCREEN=false
SHOW_WELCOME_PAGE=false
SHOW_NODE_SELECTION_PAGE=false
SHOW_SUMMARY_PAGE=false
SHOW_INSTALL_PROGRESS_PAGE=false
SHOW_CONFIG_TOOL_PAGE=false
SHOW_XML_PREREQ_PAGE=false
SHOW_ROOTSH_CONFIRMATION=true
SHOW_END_SESSION_PAGE=false
SHOW_EXIT_CONFIRMATION=false
NEXT_SESSION=false
NEXT_SESSION_ON_FAIL=false
SHOW_DEINSTALL_CONFIRMATION=false
SHOW_DEINSTALL_PROGRESS=false
RESTART_SYSTEM=false
RESTART_REMOTE_SYSTEM=false
SHOW_END_OF_INSTALL_MSGS=false

#------------------------------------------------------------------------------
#Name       : COMPONENT_LANGUAGES
#------------------------------------------------------------------------------
COMPONENT_LANGUAGES={"en"}

#------------------------------------------------------------------------------
#Name       : s_clustername
#------------------------------------------------------------------------------
s_clustername="crs"

#------------------------------------------------------------------------------
#Name       : sl_tableList
#Description: Contains a list of public node names, private node names, and
#             virtual hostnames to be part of the cluster.
#
#             The list is a comma-separated list of nodes.  Each entry in the
#             list should be a colon-separated string that contains 5 fields.
#             The fields should be ordered as follows:
#             1. The first field is for public node name.
#             2. The 2nd field is for private node name,
#             3. The 3rd field is for virtual host name
#             4. The 4th & 5th fields should not be modified, and should remain
#                as "N:Y"
#
#Example    : {"n1:n1-priv:n1-vip:N:Y","n2:n2-priv:n2-vip:N:Y"}
#------------------------------------------------------------------------------
sl_tableList={"node1:node1-priv:node1-vip:N:Y","node2:node2-priv:node2-vip:N:Y"}

#------------------------------------------------------------------------------
#Name       : ret_PrivIntrList
#Description: Contains information about the network interfaces and a
#             designation of how the Oracle Clusterware should use each
#             interface.
#                a. 1 = Public
#                b. 2 = Private
#                c. 3 = Do Not Use
#------------------------------------------------------------------------------
ret_PrivIntrList={"eth0:172.18.0.0:1","eth1:191.138.147.0:2")

#------------------------------------------------------------------------------
#Name       : n_storageTypeOCR
#Description: Represents the redundancy characteristics of the disk used for
#             storing the OCR.
#
#             This entry is a number that can take on the values of 1 or 2.
#             These values have the following meaning:
#             a. 1 = Not Redundant.  In this case, you should specify two disks
#                for the OCR - one for the primary and one for the OCR Mirror.
#             b. 2 = Externally Redundant.  This disk you are placing the OCR
#                on already has some form of external redundancy solution. In
#                this case, you need not specify an OCR mirror.
#Example    : n_storageTypeOCR = 1
#------------------------------------------------------------------------------
n_storageTypeOCR=2

#------------------------------------------------------------------------------
#Description: The location of the OCR.
#------------------------------------------------------------------------------

s_ocrpartitionlocation="/OCFS2_mountpoint/oracrs/ocrfile"

#------------------------------------------------------------------------------
#Description: The location of the OCR Mirror.
#------------------------------------------------------------------------------
s_ocrMirrorLocation=""

#------------------------------------------------------------------------------
#Description: Represents the redundancy characteristics of the disk used for
#             storing the voting disk(s).
#
#             This entry is a number that can take on the values of 1 or 2.
#             These values have the following meaning:
#             a. 1 = Not Redundant.  In this case, you should specify three
#                voting disks to eliminate the possibility of a single point
#                of failure.
#             b. 2 = Externally Redundant.  This disk you are placing the
#                voting disk on already has some form of external redundancy
#                solution. In this case, you need only use one voting disk.
#Example    : n_storageTypeVDSK = 1
#------------------------------------------------------------------------------
n_storageTypeVDSK=2

#------------------------------------------------------------------------------
#Description: The location of your first voting disk.

s_votingdisklocation="/OCFS2_mountpoint/oracrs/votingdisk"