Vishal Gupta's Blog

Resizing disks on Linux

Posted by Vishal Gupta on Mar 12, 2011

RHEL 5.3 x64 disks

On a RHEL OS using LVM volume manager, you can extend the underlying disk and its associated filesytem (non-root) can be extended while its mounted.

  • Extend the underlying virtual/SAN disks.
  • Check the size of disk

#   fdisk -l

  • Force the re-scan of disk, so that OS kernel can see the new size

# echo 1 > /sys/block/sdb/device/rescan

where disk extended was /dev/sdb

  • Extend LVM physical volume size.

# pvs

# pvresize /dev/sdb

# pvs

  • Extend LVM logical volume

# lvdisplay

# lvextend /dev/ora_vg/ora /dev/sdb

# lvdisplay

  • Extend filesystem (online)

# df –h

# resize2fs /dev/ora_vg/ora

# df –h

Filesystem is extended.

Posted in Linux | Leave a Comment »

Moving spfile between ASM and filesystem

Posted by Vishal Gupta on Mar 11, 2011

Normally in RAC, database instance spfile file is stored on ASM so that parameters remain consistent across the instance and all instance use the same spfile.

For this sometimes init.ora has following parameter

# cat $ORACLE_HOME/dbs/init<ORACLE_SID>.ora
SPFILE='+DATA/ORACLESID/spfileORACLESID.ora'
 

Now if you want to move spfile between ASM and filesystem, here how you can do it.

 

From ASM -> Filesystem

</span>
<pre>SQL> CREATE pfile='/tmp/initORACLESID.ora' from spfile='+DATA/ORACLESID/spfileORACLESID.ora';
SQL> CREATE SPFILE='/tmp/spfileORACLESID.ora' from pfile='/tmp/initORACLESID.ora';</pre>
<span style="font-family: Times New Roman; font-size: small;"> 

From Filesystem -> ASM

</span>
<pre>SQL> CREATE pfile='/tmp/initORACLESID.ora' from spfile='$ORACLE_HOME/dbs/spfileORACLESID.ora';
SQL> CREATE SPFILE='+DATA/ORACLESID/spfileORACLESID.ora' from pfile='/tmp/initORACLESID.ora';</pre>

Posted in Oracle | Leave a Comment »

Linux auto start/stop scripts

Posted by Vishal Gupta on May 17, 2010

Normally DBAs and SAs have a tendency to create the auto start/stop scripts like following.

# cat /etc/init.d/oracle
case $1 in
start)
 /opt/oracle/product/11.1.0/bin/dbstart;
 ;;
stop)
 /opt/oracle/product/11.1.0/bin/dbshut;
 ;;
esac

# ln -s /etc/init.d/Oracle /etc/rc.d/rc0.d/K01oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc3.d/S99oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc4.d/S99oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc5.d/S99oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc6.d/K01oracle

This works fine on non-Linux operating systems. But on Linux operating system, there is an requirement to for start/stop script to create/remove a /var/lock/subsys/ file, where is name of the file in /etc/init.d. This ensures that, when init is changing runlevel as part of server start and stop routine, a service which is already started does not start again. And a service which is already stopped does not stop again. If /var/lock/subsys/ is not created then inspite of K(ill) symbolic links being present in rc*.d folder for runlevel zero (halt) and 6 (reboot), stop script is not called. This means process are killed instead of being shutdown cleanly by your stop script.

On Linux Operating system, start/stop functions for a service init.d script should look like below. Important part is highlighted in red below

# chkconfig: 2345 99 01

# description: This script start/stop Oracle

case $1 in
start)
   /opt/oracle/product/11.1.0/bin/dbstart;
    touch /var/lock/subsys/Oracle;
    # where  is same as name of file in /etc/init.d
    ;;
stop)
    /opt/oracle/product/11.1.0/bin/dbshut;
    rm –f /var/lock/subsys/Oracle; 
    ;;
esac

# ln -s /etc/init.d/Oracle /etc/rc.d/rc0.d/K01oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc3.d/S99oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc4.d/S99oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc5.d/S99oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc6.d/K01oracle

This applies to not only oracle but all start/stop created for init.d on Linux.

This requirement to create subsystem lock file in /var/lock/subsys can be verified by looking at /etc/rc script, which is called everytime there is runlevel change. See below the portion of /etc/rc script which is checking for presense of /var/lock/subsys/ file before calling Kill/stop scripts.

# First, run the KILL scripts.
for i in /etc/rc$runlevel.d/K* ; do
   check_runlevel "$i" || continue

   # Check if the subsystem is already up.
   subsys=${i#/etc/rc$runlevel.d/K??}
   [ -f /var/lock/subsys/$subsys -o -f /var/lock/subsys/$subsys.init ] \
    || continue

   # Bring the subsystem down.
   if LC_ALL=C egrep -q "^..*init.d/functions" $i ; then
      $i stop
   else
      action $"Stopping $subsys: " $i stop
   fi
done

Posted in Linux | 2 Comments »

V$SQL_HINT

Posted by Vishal Gupta on May 2, 2009

In case you wanted to know which Oracle version a particular SQL hint is applicable in or was introduced in. You can query V$SQL_HINT introduced in 11g for that. It holds even historical information. Column “version” gives oracle version in which a particular hint was introduced and version_outline probably gives upto which version upto which it is applicable.

[Update:2009-05-03 : Jonathan Lewis mentioned in a email to me that version_outline gives version in which a particular hint can be used in an outline. ]

 

This even has a column to give you inverse of an hint.

 

This view is a undocumented view.

 

SQL> desc v$sql_hint
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NAME                                               VARCHAR2(64)
SQL_FEATURE                                        VARCHAR2(64)
CLASS                                              VARCHAR2(64)
INVERSE                                            VARCHAR2(64)
TARGET_LEVEL                                       NUMBER
PROPERTY                                           NUMBER
VERSION                                            VARCHAR2(25)
VERSION_OUTLINE                                    VARCHAR2(25)

Posted in Oracle | Leave a Comment »

Log File Write And Waits

Posted by Vishal Gupta on Dec 2, 2008

Here are some interesting articles regarding log file writing and associated I/O waits.

Jonanthan Lewis – Log File Write

Riyaj Shamsudeen – Log file sync tuning

Christian Bilien – Log file sync wait

Posted in Oracle, Performance | Leave a Comment »

 
%d bloggers like this: