Showing posts with label Unix/Linux. Show all posts
Showing posts with label Unix/Linux. Show all posts

Sunday, May 10, 2015

How to Pass Arguments to OS Shell Script from Oracle Database

Imagine you have several Oracle databases on the same host under same OS user.

In scripts directory you have shell script that kills OS processes.
Idea is to call OS script from database procedure and kill problematic process using shell script.

Script will run simple query to get process id and kill that process.

But how to assure that this script will execute in correct environment for correct database?

One way is to create one script per database and set environment inside the script, or create just one script which will dynamically set correct environment for instance that is calling script.

For demo case I’ve created simple script that spools query output to the file.

#!/bin/bash

# Avoid oraenv asking
ORAENV_ASK="NO"; export ORAENV_ASK

ORACLE_SID=$1; export ORACLE_SID

. oraenv ${ORACLE_SID}

$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF > /tmp/my_environment.txt
set heading off feedback off verify off
col instance_name for a10
col host_name for a10
col status for a10
select instance_name, host_name, status
 from v\$instance;
exit
EOF

$ chmod u+x simple_script.sh


What happens when we execute script.

$ ./simple_script.sh testdb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 is /u01/app/oracle
$
$ cat /tmp/my_environment.txt

testdb     asterix    OPEN

$ ./simple_script.sh ora11gr2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 is /u01/app/oracle
$
$ cat /tmp/my_environment.txt

ora11gr2   asterix    OPEN

Notice how I specified ORACLE_SID using command line argument. Script sets environment from ORATAB file according to specified SID and spools output to my_enviroment.txt file.

I will demonstrate how to pass argument from database layer.


To execute external job I have to create credentials on both databases.

-- Session 1

system@ORA11GR2> begin
  2      dbms_scheduler.create_credential(
  3      credential_name => 'ORACLE_CRED',
  4      username => 'oracle',
  5      password => 'password');
  6      end;
  7  /

PL/SQL procedure successfully completed.


-- Session 2

system@TESTDB> begin
  2      dbms_scheduler.create_credential(
  3      credential_name => 'ORACLE_CRED',
  4      username => 'oracle',
  5      password => 'password');
  6      end;
  7  /

PL/SQL procedure successfully completed.


Use SYS_CONTEXT function to get instance name and execute script for specified instance.

-- Session 1

system@ORA11GR2> DECLARE
  2    l_oracle_sid varchar2(20);
  3  BEGIN
  4    select sys_context('userenv','instance_name') into l_oracle_sid
  5    from dual;
  6        DBMS_SCHEDULER.CREATE_JOB (
  7              job_name => 'J_SIMPLE_SCRIPT',
  8              job_type => 'EXECUTABLE',
  9              job_action => '/home/oracle/skripte/simple_script.sh',
 10              number_of_arguments => 1,
 11              start_date => NULL,
 12              repeat_interval => NULL,
 13              end_date => NULL,
 14              enabled => FALSE,
 15              auto_drop => TRUE,
 16              comments => 'Set environment and execute query on v$instance view');
 17           dbms_scheduler.set_attribute('J_SIMPLE_SCRIPT','credential_name','ORACLE_CRED');
 18           DBMS_SCHEDULER.set_job_argument_value('J_SIMPLE_SCRIPT',1,l_oracle_sid);
 19          DBMS_SCHEDULER.enable('J_SIMPLE_SCRIPT');
 20          DBMS_SCHEDULER.run_job (job_name=> 'J_SIMPLE_SCRIPT', use_current_session => FALSE);
 21  END;
 22  /

PL/SQL procedure successfully completed.


system@ORA11GR2> host cat /tmp/my_environment.txt

ora11gr2   asterix    OPEN


I’ve called script from "ora11gr2" database and OS script was executed for specified database. DBMS_SCHEDULER job was used for passing argument to external OS script and for script execution.

From another session.

-- Session 2

system@TESTDB> DECLARE
  2    l_oracle_sid varchar2(20);
  3  BEGIN
  4    select sys_context('userenv','instance_name') into l_oracle_sid
  5    from dual;
  6        DBMS_SCHEDULER.CREATE_JOB (
  7              job_name => 'J_SIMPLE_SCRIPT',
  8              job_type => 'EXECUTABLE',
  9              job_action => '/home/oracle/skripte/simple_script.sh',
 10              number_of_arguments => 1,
 11              start_date => NULL,
 12              repeat_interval => NULL,
 13              end_date => NULL,
 14              enabled => FALSE,
 15              auto_drop => TRUE,
 16              comments => 'Set environment and execute query on v$instance view');
 17           dbms_scheduler.set_attribute('J_SIMPLE_SCRIPT','credential_name','ORACLE_CRED');
 18           DBMS_SCHEDULER.set_job_argument_value('J_SIMPLE_SCRIPT',1,l_oracle_sid);
 19          DBMS_SCHEDULER.enable('J_SIMPLE_SCRIPT');
 20          DBMS_SCHEDULER.run_job (job_name=> 'J_SIMPLE_SCRIPT', use_current_session => FALSE);
 21  END;
 22  /

PL/SQL procedure successfully completed.


SQL> host cat /tmp/my_environment.txt

testdb     asterix    OPEN

Notice how "/tmp/my_environment.txt" file changed according to specified database.


Using this method you can easily reuse OS scripts for more databases.


Monday, December 22, 2014

ORA-19599 block corruption when filesystemio_options=SETALL on ext4 file system using Linux

Few days ago I experienced strange issue in my development environment running on OEL 5.8 with EXT4 filesystem. Note - EXT4 filesystem is supported from OEL 5.6 version.

This was virtual machine running oldish 10.2.0.5.0 Oracle database.

I noticed that backup for my database is failing because of archive log corruption. As this is development database I simply deleted corrupted archive logs and initiated full backup again. But backup failed because new archive logs were corrupted.

Weird issue...

I forced switch of log file few times and validated new archive logs - everything was OK. Redo logs were multiplexed and everything was fine with them. I have validated database for physical and logical corruption - everything was OK.

Then I initiated backup again and it failed.
This is excerpt from RMAN log (I've changed log slightly):


RMAN> connect target *
2> run
3> {
7>
8> ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
9> BACKUP INCREMENTAL LEVEL 0 FORMAT '/u01/backup_db/QAS/fullbkp_dir/FULL_%d_%u' DATABASE TAG "weekly_full";
10> RELEASE CHANNEL d1;
11> sql 'alter system archive log current';
12> ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
13> BACKUP (ARCHIVELOG ALL FORMAT '/u01/backup_db/QAS/fullbkp_dir/ARCH_%d_%T_%u_s%s_p%p' DELETE INPUT TAG "archivelogs");
14> RELEASE CHANNEL d1;
15>
16> DELETE OBSOLETE;
17>
18> BACKUP CURRENT CONTROLFILE FORMAT '/u01/backup_db/QAS/fullbkp_dir/controlf_%d_%u_%s_%T';
19> }
20>
connected to target database: QAS (DBID=2203246509)
using target database control file instead of recovery catalog

allocated channel: d1
channel d1: sid=43 devtype=DISK

Starting backup at 17.12.2014 08:17:02
channel d1: starting compressed incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00035 name=/u01/oradata/qas700.data1
input datafile fno=00036 name=/u01/oradata/qas700.data2
input datafile fno=00037 name=/u01/oradata/qas700.data3
input datafile fno=00002 name=/u01/oradata/undo.data1
...
...
...
channel d1: starting piece 1 at 17.12.2014 08:17:03
channel d1: finished piece 1 at 17.12.2014 09:45:48
piece handle=/u01/backup_db/QAS/fullbkp_dir/FULL_QAS_26pqchvu tag=WEEKLY_FULL comment=NONE
channel d1: backup set complete, elapsed time: 01:28:46
Finished backup at 17.12.2014 09:45:48

Starting Control File and SPFILE Autobackup at 17.12.2014 09:45:48
piece handle=/u01/app/oracle10/product/10.2.0/db_1/dbs/c-2203246509-20141217-13 comment=NONE
Finished Control File and SPFILE Autobackup at 17.12.2014 09:45:53

released channel: d1

sql statement: alter system archive log current

allocated channel: d1
channel d1: sid=43 devtype=DISK

Starting backup at 17.12.2014 09:45:54
current log archived
channel d1: starting compressed archive log backupset
channel d1: specifying archive log(s) in backup set
input archive log thread=1 sequence=11350 recid=39 stamp=866540753
input archive log thread=1 sequence=11351 recid=40 stamp=866540754
channel d1: starting piece 1 at 17.12.2014 09:45:55
released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on d1 channel at 12/17/2014 09:45:56
ORA-19599: block number 6144 is corrupt in archived log /u01/oradata/QAS/QASarch/1_11350_826737654.dbf

Recovery Manager complete.


Notice that full backup finished successfully and when RMAN tried to backup new archive logs it failed due to corruption.

I've mentioned this issue on Twitter and got responses from Ronald Rood (@Ik_zelf) and Philippe Fierens (@pfierens) who helped me to find problem resolution.
Thanks guys!


Check this note:
ORA-1578 ORA-353 ORA-19599 Corrupt blocks with zeros when filesystemio_options=SETALL on ext4 file system using Linux (Doc ID 1487957.1)

I had filesystemio_options configured as SETALL and resetting this parameter to default value solved my corruption problem.


As this was development machine I wasn't thinking much about filesystem, but next time it will be ASM or XFS - EXT4 probably not :-)


Friday, October 24, 2014

Increase disk space for VM running Linux

When I create virtual machines on my notebook I always create too small disk for root partition or partition where I put Oracle binaries. After a while when I want to perform upgrade, or install another Oracle software, there is not enough space. This time I want to note steps about how to increase disk free space.

I can easily extend or shrink my logical volumes because I am using LVM in my virtual machines. Consider using LVM in production also because it gives you more flexibility then using normal hard drive partitions.

In this demo I'm using Oracle Linux 6.4.


Check disk free space after OS installation.

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      4.9G  2.8G  2.0G  59% /
tmpfs                 770M  100K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot


Add "/u01" mount and assign some disk space for Oracle installation files.


Shutdown VM and add disk.


Partition new disk "/dev/sdb" using fdisk command.

# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xa07249dd.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

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

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-391, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-391, default 391):
Using default value 391

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): L

 0  Empty           24  NEC DOS         81  Minix / old Lin bf  Solaris
 1  FAT12           39  Plan 9          82  Linux swap / So c1  DRDOS/sec (FAT-
 2  XENIX root      3c  PartitionMagic  83  Linux           c4  DRDOS/sec (FAT-
 3  XENIX usr       40  Venix 80286     84  OS/2 hidden C:  c6  DRDOS/sec (FAT-
 4  FAT16 <32M      41  PPC PReP Boot   85  Linux extended  c7  Syrinx
 5  Extended        42  SFS             86  NTFS volume set da  Non-FS data
 6  FAT16           4d  QNX4.x          87  NTFS volume set db  CP/M / CTOS / .
 7  HPFS/NTFS       4e  QNX4.x 2nd part 88  Linux plaintext de  Dell Utility
 8  AIX             4f  QNX4.x 3rd part 8e  Linux LVM       df  BootIt
 9  AIX bootable    50  OnTrack DM      93  Amoeba          e1  DOS access
 a  OS/2 Boot Manag 51  OnTrack DM6 Aux 94  Amoeba BBT      e3  DOS R/O
 b  W95 FAT32       52  CP/M            9f  BSD/OS          e4  SpeedStor
 c  W95 FAT32 (LBA) 53  OnTrack DM6 Aux a0  IBM Thinkpad hi eb  BeOS fs
 e  W95 FAT16 (LBA) 54  OnTrackDM6      a5  FreeBSD         ee  GPT
 f  W95 Ext'd (LBA) 55  EZ-Drive        a6  OpenBSD         ef  EFI (FAT-12/16/
10  OPUS            56  Golden Bow      a7  NeXTSTEP        f0  Linux/PA-RISC b
11  Hidden FAT12    5c  Priam Edisk     a8  Darwin UFS      f1  SpeedStor
12  Compaq diagnost 61  SpeedStor       a9  NetBSD          f4  SpeedStor
14  Hidden FAT16 <3 63  GNU HURD or Sys ab  Darwin boot     f2  DOS secondary
16  Hidden FAT16    64  Novell Netware  af  HFS / HFS+      fb  VMware VMFS
17  Hidden HPFS/NTF 65  Novell Netware  b7  BSDI fs         fc  VMware VMKCORE
18  AST SmartSleep  70  DiskSecure Mult b8  BSDI swap       fd  Linux raid auto
1b  Hidden W95 FAT3 75  PC/IX           bb  Boot Wizard hid fe  LANstep
1c  Hidden W95 FAT3 80  Old Minix       be  Solaris boot    ff  BBT
1e  Hidden W95 FAT1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.


Notice that I have identified partition as "Linux LVM" choosing "8e" hex code.


Using pvcreate command create a physical volume for later use by the LVM.

# pvcreate /dev/sdb1
  Physical volume "/dev/sdb1" successfully created

Create new volume group "vg_orabin". Later I can add or remove disks from this volume group.

# vgcreate vg_orabin /dev/sdb1
  Volume group "vg_orabin" successfully created


Information about volume group.

# vgdisplay vg_orabin
  --- Volume group ---
  VG Name               vg_orabin
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  1
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                0
  Open LV               0
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               2.99 GiB
  PE Size               4.00 MiB
  Total PE              766
  Alloc PE / Size       0 / 0
  Free  PE / Size       766 / 2.99 GiB
  VG UUID               h3N1o5-AlYF-9nkL-PXiB-P8HK-tGAa-GlXPa5


Create logical volume using disk space from volume group.

# lvcreate --extents 766 -n lv_orabin vg_orabin
  Logical volume "lv_orabin" created


Create and mount filesystem.

# mkfs.ext4 /dev/mapper/vg_orabin-lv_orabin
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
196224< inodes, 784384 blocks
39219 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=805306368
24 block groups
32768 blocks per group, 32768 fragments per group
8176 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912

Writing inode tables: done
Creating journal (16384 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 25 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.




# mkdir /u01
# mount /dev/mapper/vg_orabin-lv_orabin /u01

Check disk space available.


# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      4.9G  2.8G  2.0G  59% /
tmpfs                 770M   88K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot
/dev/mapper/vg_orabin-lv_orabin
                      3.0G   69M  2.8G   3% /u01


Hm, 2.8G is not enough free space for me. Let’s extend this mount adding another disk.



Shutdown VM and add disk.


Partition new disk and create physical volume for LVM.

# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x16953397.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

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

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.




# pvcreate /dev/sdc1
  Physical volume "/dev/sdc1" successfully created

Check current status of volume group “vg_orabin”.

# vgdisplay vg_orabin
  --- Volume group ---
  VG Name               vg_orabin
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  2
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                1
  Open LV               0
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               2.99 GiB
  PE Size               4.00 MiB
  Total PE              766
  Alloc PE / Size       766 / 2.99 GiB
  Free  PE / Size       0 / 0
  VG UUID               h3N1o5-AlYF-9nkL-PXiB-P8HK-tGAa-GlXPa5

Extend volume group by adding physical volume "/dev/sdc1" using vgextend command.

# vgextend vg_orabin /dev/sdc1
  Volume group "vg_orabin" successfully extended


Check volume group size - it is extended from 2.99G to 7.98G.

# vgdisplay vg_orabin
  --- Volume group ---
  VG Name               vg_orabin
  System ID
  Format                lvm2
  Metadata Areas        2
  Metadata Sequence No  3
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                1
  Open LV               0
  Max PV                0
  Cur PV                2
  Act PV                2
  VG Size               7.98 GiB
  PE Size               4.00 MiB
  Total PE              2044
  Alloc PE / Size       766 / 2.99 GiB
  Free  PE / Size       1278 / 4.99 GiB
  VG UUID               h3N1o5-AlYF-9nkL-PXiB-P8HK-tGAa-GlXPa5


Using pvscan command scan all disks and notice physical volumes with free space.

# pvscan
  PV /dev/sdb1   VG vg_orabin      lvm2 [2.99 GiB / 0    free]
  PV /dev/sdc1   VG vg_orabin      lvm2 [4.99 GiB / 4.99 GiB free]
  PV /dev/sda2   VG vg_linuxtest   lvm2 [6.51 GiB / 0    free]
  Total: 3 [14.49 GiB] / in use: 3 [14.49 GiB] / in no VG: 0 [0   ]


With lvdisplay command display logical volume properties
Notice LV size = 2.99G.

# lvdisplay /dev/vg_orabin/lv_orabin
  --- Logical volume ---
  LV Path                /dev/vg_orabin/lv_orabin
  LV Name                lv_orabin
  VG Name                vg_orabin
  LV UUID                ypw9X1-vIsM-4rVF-NtVB-ACrf-f5nh-25p2sn
  LV Write Access        read/write
  LV Creation host, time linuxtest.localdomain, 2014-10-23 13:19:56 +0200
  LV Status              available
  # open                 0
  LV Size                2.99 GiB
  Current LE             766
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           252:2


I will add only 2G (of 5G) using lvextend command.

# lvextend -L +2G /dev/mapper/vg_orabin-lv_orabin /dev/sdc1
  Extending logical volume lv_orabin to 4.99 GiB
  Logical volume lv_orabin successfully resized


Mount volume and check for free space.

# mount /dev/mapper/vg_orabin-lv_orabin /u01

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      4.9G  2.8G  2.0G  59% /
tmpfs                 770M   88K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot
/dev/mapper/vg_orabin-lv_orabin
                      3.0G   69M  2.8G   3% /u01


Resize filesystem using resize2fs command:
# resize2fs /dev/mapper/vg_orabin-lv_orabin
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/mapper/vg_orabin-lv_orabin is mounted on /u01; on-line resizing required
old desc_blocks = 1, new_desc_blocks = 1
Performing an on-line resize of /dev/mapper/vg_orabin-lv_orabin to 1308672 (4k) blocks.
The filesystem on /dev/mapper/vg_orabin-lv_orabin is now 1308672 blocks long.


Now I have 4.6G free space for "/u01" mount.

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      4.9G  2.8G  2.0G  59% /
tmpfs                 770M   88K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot
/dev/mapper/vg_orabin-lv_orabin
                      5.0G   70M  4.6G   2% /u01



===========================================

Now I will try to extend root partition.

Newer Oracle Linux releases are using LVM by default during install.
Let’s see can I increase my root partition using commands above.


Display information about logical volumes using lvs command.

# lvs
  LV        VG           Attr      LSize Pool Origin Data%  Move Log Cpy%Sync Convert
  lv_root   vg_linuxtest -wi-ao--- 4.97g
  lv_swap   vg_linuxtest -wi-ao--- 1.54g
  lv_orabin vg_orabin    -wi-a---- 4.99g

Check free space.
# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      4.9G  2.8G  2.0G  59% /
tmpfs                 770M   88K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot


Shutdown VM and add disk for extending root partition.


Partition new disk and create physical volume for LVM.

# fdisk /dev/sdd
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xf0608435.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

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

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.



# pvcreate /dev/sdd1
  Physical volume "/dev/sdd1" successfully created


Check information about volume group.

# vgdisplay vg_linuxtest

  --- Volume group ---
  VG Name               vg_linuxtest
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  3
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                2
  Open LV               2
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               6.51 GiB
  PE Size               4.00 MiB
  Total PE              1666
  Alloc PE / Size       1666 / 6.51 GiB
  Free  PE / Size       0 / 0
  VG UUID               TXkKYl-PIxu-s2xk-LsEB-sgTZ-TdcO-8wapCV

Extend volume group using new physical volume.

# vgextend vg_linuxtest /dev/sdd1
  Volume group "vg_linuxtest" successfully extended

Logical volume status.
# lvdisplay  /dev/vg_linuxtest/lv_root
  --- Logical volume ---
  LV Path                /dev/vg_linuxtest/lv_root
  LV Name                lv_root
  VG Name                vg_linuxtest
  LV UUID                VNgeT7-4yhd-XqRi-2da1-XTqT-qTvm-oVK2pz
  LV Write Access        read/write
  LV Creation host, time linuxtest.localdomain, 2014-10-23 10:30:21 +0200
  LV Status              available
  # open                 1
  LV Size                4.97 GiB
  Current LE             1272
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           252:0


Extend logical volume.
# lvextend /dev/mapper/vg_linuxtest-lv_root /dev/sdd1
  Extending logical volume lv_root to 9.96 GiB
  Logical volume lv_root successfully resized

Resize filesystem.

# resize2fs /dev/mapper/vg_linuxtest-lv_root
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/mapper/vg_linuxtest-lv_root is mounted on /; on-line resizing required
old desc_blocks = 1, new_desc_blocks = 1
Performing an on-line resize of /dev/mapper/vg_linuxtest-lv_root to 2611200 (4k) blocks.
The filesystem on /dev/mapper/vg_linuxtest-lv_root is now 2611200 blocks long.


Check disk free space. Notice that I have 6.6G of free space for my root partition.

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxtest-lv_root
                      9.9G  2.8G  6.6G  30% /
tmpfs                 770M   88K  770M   1% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot



WARNING! Be very careful when using commands from blog post on your production system. These are dangerous commands which can cause loss of data or many other problems. I’ve used this commands in my test environment for educational purpose and it is possible that I have made mistakes in this demo. After all I am only simple Oracle DBA not Linux SA :-)


REFERENCES
http://www.linuxuser.co.uk/features/resize-your-disks-on-the-fly-with-lvm
http://www.rootusers.com/how-to-increase-the-size-of-a-linux-lvm-by-adding-a-new-disk/
https://wiki.archlinux.org/index.php/LVM

Saturday, July 6, 2013

How to detach storage device from running virtual machine in VirtualBox

In this post I want to show how to test what will happen with your cluster if you simply detach shared storage device from it. VirtualBox is great tool for that purpose because you can test such scenario without involving many people or causing any damage.

I am aware that virtual environment on my notebook is completely different environment than production. Please let me know if this test is not even similar with situations that could happen in the production. Goal is to test OCFS2 node fencing after device is detached.


My environment is HP notebook with Windows 7, VirtualBox 4.2.16 and two Oracle Linux 64bit virtual machines.

Let the test begin...

Shutdown virtual machines and create shareable virtual disk.
C:\>cd "Program Files\Oracle\VirtualBox"

> VBoxManage.exe createhd --filename D:\VirtualneMasine\ClusterSharedDisks\disk3.vdi ^
--size 1024 --format VDI --variant Fixed

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: 862b1d65-eb04-42b2-8a1e-eafafb5bbcd3

Connect disk to virtual machines Cluster1 and Cluster2.
> VBoxManage.exe storageattach Cluster1 --storagectl "SATA" --port 5 --device 0 ^
--type hdd --medium D:\VirtualneMasine\ClusterSharedDisks\disk3.vdi --mtype shareable

> VBoxManage.exe storageattach Cluster2 --storagectl "SATA" --port 5 --device 0 ^
--type hdd --medium D:\VirtualneMasine\ClusterSharedDisks\disk3.vdi --mtype shareable

Start virtual machines and partition newly added disk on Cluster1 node.
[root@cluster1 ~]# fdisk /dev/sdf
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.

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

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1): 
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-130, default 130): 
Using default value 130

Command (m for help): p

Disk /dev/sdf: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdf1               1         130     1044193+  83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

Inform the OS of partition table changes using partprobe.
[root@cluster1 ~]# /sbin/partprobe /dev/sdf

[root@cluster1 ~]# ssh -l root cluster2
root@cluster2's password: 
Last login: Sat Jul  6 10:12:58 2013 from 192.168.56.101

[root@cluster2 ~]# /sbin/partprobe /dev/sdf

As I am using OCFS2 for shared-disk cluster file system I must create OCFS2 file system on a device. Execute this command on just one node.

[root@cluster1 ~]# mkfs.ocfs2 -b 4K -C 128K -N 4 -L disk3 /dev/sdf1
mkfs.ocfs2 1.6.3
Cluster stack: classic o2cb
Label: disk3
Features: sparse backup-super unwritten inline-data strict-journal-super
Block size: 4096 (12 bits)
Cluster size: 131072 (17 bits)
Volume size: 1069154304 (8157 clusters) (261024 blocks)
Cluster groups: 1 (tail covers 8157 clusters, rest cover 8157 clusters)
Extent allocator size: 4194304 (1 groups)
Journal size: 16777216
Node slots: 4
Creating bitmaps: done
Initializing superblock: done
Writing system files: done
Writing superblock: done
Writing backup superblock: 0 block(s)
Formatting Journals: done
Growing extent allocator: done
Formatting slot map: done
Formatting quota files: done
Writing lost+found: done
mkfs.ocfs2 successful

Create directories and mount virtual storage device on both nodes.
[root@cluster1 ~]# mkdir /disk3
[root@cluster1 ~]# mount -t ocfs2 -o datavolume,nointr,noatime -L "disk3" /disk3

[root@cluster1 ~]# ssh -l root cluster2
root@cluster2's password: 
Last login: Sat Jul  6 10:13:14 2013 from 192.168.56.101

[root@cluster2 ~]# mkdir /disk3
[root@cluster2 ~]# mount -t ocfs2 -o datavolume,nointr,noatime -L "disk3" /disk3

Now I want to test what will happen if I simply detach specified storage device.

Using "del" command to delete file that represents shared storage for virtual machines won’t work.
C:\>del d:\VirtualneMasine\ClusterSharedDisks\disk3.vdi

d:\VirtualneMasine\ClusterSharedDisks\disk3.vdi
The process cannot access the file because it is being used by another process.


So, how to detach device while virtual machines are running.

Detach device using VBoxManage:
> VBoxManage.exe storageattach Cluster1 --storagectl "SATA" --port 5 --device 0 --medium none
> VBoxManage.exe storageattach Cluster2 --storagectl "SATA" --port 5 --device 0 --medium none


Check /var/log/messages on Cluster2.

Jul  6 10:33:37 cluster2 kernel: ata6: exception Emask 0x10 SAct 0x0 SErr 0x10000 action 0xe frozen
Jul  6 10:33:37 cluster2 kernel: ata6: irq_stat 0x80400000, PHY RDY changed
Jul  6 10:33:37 cluster2 kernel: ata6: SError: { PHYRdyChg }
Jul  6 10:33:37 cluster2 kernel: ata6: hard resetting link
Jul  6 10:33:38 cluster2 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:33:38 cluster2 kernel: ata6: failed to recover some devices, retrying in 5 secs
Jul  6 10:33:43 cluster2 kernel: ata6: hard resetting link
Jul  6 10:33:43 cluster2 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:33:43 cluster2 kernel: ata6: failed to recover some devices, retrying in 5 secs
Jul  6 10:33:48 cluster2 kernel: ata6: hard resetting link
Jul  6 10:33:49 cluster2 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:33:49 cluster2 kernel: ata6.00: disabled
Jul  6 10:33:49 cluster2 kernel: sd 5:0:0:0: rejecting I/O to offline device
Jul  6 10:33:49 cluster2 kernel: sd 5:0:0:0: SCSI error: return code = 0x00010000
Jul  6 10:33:49 cluster2 kernel: end_request: I/O error, dev sdf, sector 2879
Jul  6 10:33:49 cluster2 kernel: (kjournald,415,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:49 cluster2 kernel: sd 5:0:0:0: rejecting I/O to offline device
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:49 cluster2 kernel: sd 5:0:0:0: rejecting I/O to offline device
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:49 cluster2 kernel: sd 5:0:0:0: rejecting I/O to offline device
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:49 cluster2 kernel: ata6: EH complete
Jul  6 10:33:49 cluster2 kernel: ata6.00: detaching (SCSI 5:0:0:0)
Jul  6 10:33:51 cluster2 kernel: scsi 5:0:0:0: rejecting I/O to dead device
Jul  6 10:33:51 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:51 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:51 cluster2 kernel: scsi 5:0:0:0: rejecting I/O to dead device
Jul  6 10:33:51 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:51 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:53 cluster2 kernel: scsi 5:0:0:0: rejecting I/O to dead device
Jul  6 10:33:53 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:53 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:53 cluster2 kernel: scsi 5:0:0:0: rejecting I/O to dead device
Jul  6 10:33:53 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:53 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5

Notice heartbeat errors due to missing device. Every 2 secs we will get error until timeout is reached then it's time for self-fencing.


In another test I will unmount device prior detaching.
# Cluster1
[root@cluster1 ~]# umount -t ocfs2 /disk3

# Cluster2
[root@cluster2 ~]# umount -t ocfs2 /disk3

Detach using VBOxManage:
> VBoxManage.exe storageattach Cluster1 --storagectl "SATA" --port 5 --device 0 --medium none
> VBoxManage.exe storageattach Cluster2 --storagectl "SATA" --port 5 --device 0 --medium none

Check /var/log/messages on Cluster1:
Jul  6 10:30:52 cluster1 kernel: ocfs2: Unmounting device (8,81) on (node 0)
Jul  6 10:31:38 cluster1 kernel: ata6: exception Emask 0x10 SAct 0x0 SErr 0x10000 action 0xe frozen
Jul  6 10:31:38 cluster1 kernel: ata6: irq_stat 0x80400000, PHY RDY changed
Jul  6 10:31:38 cluster1 kernel: ata6: SError: { PHYRdyChg }
Jul  6 10:31:38 cluster1 kernel: ata6: hard resetting link
Jul  6 10:31:39 cluster1 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:31:39 cluster1 kernel: ata6: failed to recover some devices, retrying in 5 secs
Jul  6 10:31:44 cluster1 kernel: ata6: hard resetting link
Jul  6 10:31:44 cluster1 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:31:44 cluster1 kernel: ata6: failed to recover some devices, retrying in 5 secs
Jul  6 10:31:49 cluster1 kernel: ata6: hard resetting link
Jul  6 10:31:50 cluster1 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:31:50 cluster1 kernel: ata6.00: disabled
Jul  6 10:31:50 cluster1 kernel: ata6: EH complete
Jul  6 10:31:50 cluster1 kernel: ata6.00: detaching (SCSI 5:0:0:0)

Both nodes stayed up and running without heartbeat errors.
I can conclude from this test - if you unmount OCFS2 device from both nodes prior detaching device everything should continue to work without sudden reboots.


If you want to attach virtual storage again just shutdown virtual machines and connect device using commands from the beginning of the post.
>VBoxManage.exe storageattach Cluster1 --storagectl "SATA" --port 5 --device 0 ^
--type hdd --medium D:\VirtualneMasine\ClusterSharedDisks\disk3.vdi --mtype shareable

>VBoxManage.exe storageattach Cluster2 --storagectl "SATA" --port 5 --device 0 ^
--type hdd --medium D:\VirtualneMasine\ClusterSharedDisks\disk3.vdi --mtype shareable


If you have the opportunity to perform tests on a real hardware this should be always your first choice. But in case you are unable to do that it is better to perform tests in virtual environment then nothing.


Thursday, February 7, 2013

Kill stuck Oracle process from OS using DBMS_SCHEDULER

Last month we had annoying problems caused by stuck Oracle process holding locks and not releasing them. Every few days one process hangs with status “ACTIVE” and “SQL*Net message from client” wait event.

In that state it holds locks and never releases them. As this is active OLTP database, new sessions arrive wanting those locked resources but they could not get them. So after some time you get lots of waiters and if you don’t react quickly maximum number of processes could be reached. Then your database refuses new connections and you're system becomes unresponsive.

To complicate a problem little more, when you kill session from Oracle using “disconnect session immediate” or “kill session immediate” - session is only marked as “KILLED”. It is not cleaned and locks are not released even if you wait for several hours. Only opinion is to kill process from OS side and then PMON manages to perform cleaning and release locks.

So we created temporary solution until “stuck process” problem is solved. As this is active OLTP system we are expecting most queries to complete under a minute. Every session active for over 30 minutes is probably stuck process and needs to be killed from OS side.

I will publish scripts we used to temporary solve this problem. You should change them according to your needs.

kill_stuck_process.sh
Main script used to perform OS kill. This script needs to be executable file.
#!/bin/bash

# SET ORACLE ENVIRONMENT #
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
PATH=$ORACLE_HOME/bin:$PATH; export PATH
export ORACLE_SID=orcl

# File which will be used to keep spid
if [ -f /tmp/spid.txt ]; then
/bin/rm -f /tmp/spid.txt
fi

# Find SPID of the hanging process you want to kill
$ORACLE_HOME/bin/sqlplus -s system/password @/oracle/scripts/get_spid.sql > /tmp/spid.txt

spid=`cat /tmp/spid.txt`

# If spid exists kill specified process
if [ -s /tmp/spid.txt ]; then
# Send mail from Oracle about killed process
$ORACLE_HOME/bin/sqlplus -s system/password @/oracle/scripts/send_mail.sql
# Kill process
/bin/kill -9 $spid
fi

get_spid.sql
Query used to retrieve SPID of the stuck process. Modify this query based on your needs.
set serverout on head off ver off feed off
SELECT spid from v$process
where addr = ( SELECT paddr
                        FROM
                        (
                            SELECT paddr, rownum rn
                                from (
                                    SELECT paddr, last_call_et 
                                            FROM v$session
                                            where username='OLTP_USER'
                                            and last_call_et >= 1800
                                            and status='ACTIVE'
                                            order by last_call_et desc
                                       )
                        )
                        where rn=1);
exit

send_mail.sql
Procedure sends detailed information about killed process for better diagnosis later. This is just additional info and it is not essential for the script.
begin
  "ADMINUSR"."P_SEND_PROCESS_DETAILS";
end;
/
exit

Now to create DBMS_SCHEDULER job which will be executed every 30 minutes and kill stuck process.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'KILL_STUCK_PROCESS',
   job_type             => 'EXECUTABLE',
   job_action           => '/oracle/script/kill_stuck_process.sh',
   start_date           => SYSDATE,
   repeat_interval      => 'FREQ=MINUTELY;INTERVAL=30',
   enabled              =>  FALSE,
   comments             =>'Kill long running stuck OLTP_USER session!');
END;
/

Now to test the job:
BEGIN
  DBMS_SCHEDULER.run_job (job_name=> 'KILL_STUCK_PROCESS', use_current_session => TRUE);
END;
/

You will probably receive error:
BEGIN
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied
ORA-06512: at "SYS.DBMS_ISCHED", line 154
ORA-06512: at "SYS.DBMS_SCHEDULER", line 450
ORA-06512: at line 2

When dbms_scheduler is executing external (operating system) jobs it is using lowly privileged user and group for your platform. In my environment (OEL 5.8) this is “nobody” and “nobody”. As you must kill OS process you must specify more privileged user. There is “externaljob.ora” configuration file in “$ORACLE_HOME/rdbms/admin”. Edit this file as root and specify privileged user and group. In my case:
run_user = orcl11
run_group = oinstall

Everything is working fine now:
BEGIN
  DBMS_SCHEDULER.run_job (job_name=> 'KILL_STUCK_PROCESS', use_current_session => TRUE);
END;
/

PL/SQL procedure successfully completed.


Thoroughly test this scripts in your environment before going to production with them.


Tuesday, May 31, 2011

Datapump fails with ORA-27086 on NFS

I've received following error while trying to perform full database export using datapump to NFS:

Export: Release 10.2.0.4.0 - Production on Tuesday, 31 May, 2011 11:27:50

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/backup-nfs/testdb/export/exportfull.dmp"
ORA-27086: unable to lock file - already in use
Linux Error: 37: No locks available
Additional information: 10

In this case problem was lying in NFS locking deamons - on most Unix-like systems also known as rpc.lockd and the rpc.statd.



Checking if 'nfslock' service is running:
# service nfslock status
rpc.statd is stopped

Start 'nfslock' service:
# /etc/init.d/nfslock start
Starting NFS statd:                                        [  OK  ]

Status:
# service nfslock status
rpc.statd (pid  4943) is running...


Starting 'nfslock' service resolved my problem with performing export to NFS.


Monday, October 13, 2008

Monitoring Load Average

Knowledge about databases is often not sufficient to effectively monitor usage that application produces. You also must have some basic knowledge about the host machine which runs your database.

When users experience performance problems with application one of the first things you should check on host machine is load average.

You can do that executing uptime command.


12:05:13 up 227 days, 11:15, 2 users, load average: 1.93, 2.41, 2.30


This information shows how long the system has been running, how many users are currently logged on, and the system load averages for the past 1, 5, and 15 minutes.



You can also get info about load average executing w or top commands.


In my example I have three numbers for load average: 1.93, 2.41, 2.30.

This information shows average load for last 1, 5 and 15 mins.

- 1.93 means that processes were using about 96.5% of my overall CPU capability. I can conclude from this that processes were served well last minute.
- 2.41 tells me that in past 5 minutes processes requested about 20.5% more CPU power than machine could serve. This means that some processes were forced to wait for CPU.
- 2.30 tells me that in past 15 minutes processes requested about 15% more CPU power than machine could serve.

It is important to notice that my machine has 2 CPUs so ideal load average for my machine is below 2.0. If I had machine with just 1 CPU then load average below 1.0 would be OK.

Also it is important to understand that load isn't CPU usage. I could have one or hundred processes (most of them probably idle) with CPU usage 99% but still my load would be acceptable.


To find out number of CPUs on machine you could execute following commands:
LINUX

[oracle@dibidus ~]$ cat /proc/cpuinfo | grep processor | wc -l
2

This Linux machine has 2 CPUs.

SOLARIS

# psrinfo -p -v
The UltraSPARC-IV physical processor has 2 virtual processors (0, 16)
The UltraSPARC-IV physical processor has 2 virtual processors (2, 18)

This Solaris machine has 2 physical CPUs with 2 virtual processors.