Avoid Disk Full Issues Because OPatch Backups are stacking up.

it is safe to remove all sub directories in .patch_storage that are no longer present in opatch inventory.  directory names will be prefaced with patchid.

tested in 12.1.0.2 EE but should also work fine in 12.2, 18.x, and 19.x

run cleanup script to get list of directories safe for removal.

oracle> cd $ORACLE_HOME/.patch_storage
(/opt/oracle/product/12.1.0.2/grid/.patch_storage)
oracle>
> for i in `ls -d [0-9]*_[A-S][a
>
>
>   iPATCH_STATUS=`$ORACLE_HOME/OPatch/opatch lsinventory | grep "$iPATCH.*applied" |
>   if [[ -n $iPA
>     then echo $iPATCH_STATUS, so keep `du -sh $i | tr '\t' ' '`
>     else echo `du -sh $i | tr '\t' ' '` can be removed, because $iPATCH is n
>
> done
As of Wed Oct 31 13:33:23 PDT 2018 in rh51477:/srv/oracle/product/12.1.0.2/grid/.patch_storage:
71M 19769480_Dec_15_2014_06_54_52 can be removed, because 19769480 is not in the lsinventory output.
65M 20299023_Mar_16_2015_22_21_54 can be removed, because 20299023 is not in the lsinventory output.
7.3M 20831110_Jul_11_2015_00_45_40 can be removed, because 20831110 is not in the lsinventory output.
33M 21359755_Oct_21_2015_02_52_58 can be removed, because 21359755 is not in the lsinventory output.
1.8M 21436941_Aug_13_2015_04_00_40 can be removed, because 21436941 is not in the lsinventory output.
62M 21948354_Dec_20_2015_23_39_33 can be removed, because 21948354 is not in the lsinventory output.
54M 22291127_Apr_6_2016_03_46_21 can be removed, because 22291127 is not in the lsinventory output.
81M 23054246_Jul_5_2016_07_07_59 can be removed, because 23054246 is not in the lsinventory output.
36M 24006101_Oct_1_2016_12_33_50 can be removed, because 24006101 is not in the lsinventory output.
37M 24732082_Dec_21_2016_07_15_01 can be removed, because 24732082 is not in the lsinventory output.
59M 25171037_Mar_7_2017_12_37_23 can be removed, because 25171037 is not in the lsinventory output.
56M 25755742_Jun_29_2017_09_56_57 can be removed, because 25755742 is not in the lsinventory output.
1.8G 26392164_Aug_31_2017_23_39_16 can be removed, because 26392164 is not in the lsinventory output.
1.2G 26392192_Oct_2_2017_09_28_19 can be removed, because 26392192 is not in the lsinventory output.
1.7M 26609783_Aug_10_2017_05_36_42 can be removed, because 26609783 is not in the lsinventory output.
97M 26713565_Sep_29_2017_06_57_50 can be removed, because 26713565 is not in the lsinventory output.
2.7G 26910974_Nov_8_2017_12_45_39 can be removed, because 26910974 is not in the lsinventory output.
1.5G 26925218_Dec_1_2017_19_38_05 can be removed, because 26925218 is not in the lsinventory output.
40M 26925311_Dec_6_2017_01_18_05 can be removed, because 26925311 is not in the lsinventory output.
Patch 26983807 was applied on Thu Jan 18 2018, so keep 1.8M 26983807_Nov_8_2017_07_59_12
3.0G 27338013_Feb_14_2018_10_26_39 can be removed, because 27338013 is not in the lsinventory output.
1.5G 27338020_Mar_27_2018_11_48_03 can be removed, because 27338020 is not in the lsinventory output.
29M 27338041_Mar_16_2018_02_05_00 can be removed, because 27338041 is not in the lsinventory output.
Patch 27547329 was applied on Mon Aug 27 2018, so keep 169M 27547329_Jun_22_2018_10_43_01
Patch 27762253 was applied on Mon Aug 27 2018, so keep 1.5G 27762253_Jul_16_2018_08_24_16
Patch 27762277 was applied on Mon Aug 27 2018, so keep 3.0G 27762277_Jul_16_2018_08_23_19

Identify is Oracle Process is context switching

identify context switching

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
1535 oracle    20   0 22.5g 240m  30m R 100  0.5   0:10.24 oracle_1535_sbc

pid=1535
grep ctxt /proc/$pid/status

voluntary_ctxt_switches: 10395
nonvoluntary_ctxt_switches: 33415

Monitor RMAN restore/recovery channels in progress.

[ SYS ] sql+> select banner from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

[ SYS ] sql+> set linesize 300

[ SYS ] sql+> select

l.sid,

l.start_time,

l.totalwork

sofar,

(l.sofar/l.totalwork) * 100 pct_done,

s.client_info

from v$session_longops l,

v$process p, v$session s

where l.totalwork > l.sofar

and l.opname not like '%aggregate%'

and l.opname like 'RMAN%'

and p.addr=s.paddr

and l.sid=s.sid;  

       SID START_TIM   SOFAR   PCT_DONE CLIENT_INFO

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

20 18-NOV-15 384000 45.3330729 rman channel=ORA_DISK_1

21 18-NOV-15 1792000 46.1713728 rman channel=ORA_DISK_2

22 18-NOV-15 1792000 51.7713728 rman channel=ORA_DISK_3

23 18-NOV-15 384000 86.3997396 rman channel=ORA_DISK_4

4 rows selected.

[ SYS ] sql+> /

       SID START_TIM   SOFAR   PCT_DONE CLIENT_INFO

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

20 18-NOV-15 384000 48.5330729 rman channel=ORA_DISK_1

21 18-NOV-15 1792000 46.8570871 rman channel=ORA_DISK_2

22 18-NOV-15 1792000 52.3428013 rman channel=ORA_DISK_3

23 18-NOV-15 384000 90.1330729 rman channel=ORA_DISK_4

4 rows selected.

[ SYS ] sql+> /

       SID START_TIM   SOFAR   PCT_DONE CLIENT_INFO

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

20 18-NOV-15 384000 51.7330729 rman channel=ORA_DISK_1

21 18-NOV-15 1792000 47.5428013 rman channel=ORA_DISK_2

22 18-NOV-15 1792000 53.0356585 rman channel=ORA_DISK_3

23 18-NOV-15 384000 93.3330729 rman channel=ORA_DISK_4

handy in a pinch when time is tight and your vp is watching over your shoulder...

Parse Oracle tnsnames.ora into alias list using Python

#!/bin/python

# tns_parser.py

import os

import re

os.chdir('/opt/oracle/etc') # move to tns directoy

tns = open('tnsnames.ora', 'r')              # open file

vlist = tns.read()                           # read file into string

vlist = re.sub("#.*\n","",vlist)             # strip all comments

vlist = re.sub("\(.*\n","",vlist)            # remove "(" to end of line

vlist = re.sub("\)","",vlist)                # clean up ")"

vlist = re.sub(" ","",vlist)                 # remove spaces

vlist = re.sub("\n","",vlist)                # remove carriage returns

vlist = re.sub("=$","",vlist)                # remove last "="

vlist = vlist.split("=")                     # create list

tns.close()

#print '\n'.join(vlist)

#print(vlist)

#print vlist[4]

for i in vlist:

         # do anything you want with this list now.  YAY!

                 print i

# run it.

oracle@localhost $ python tns_parser.py 

dev.world.net 

uat.world.net

prod.world.net

target tnsnames.ora sample:

dev.world.net =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = host_name_or_ip )(PORT = 1571))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = dev.world)

    )

  )

uat.world.net =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = host_name_or_ip )(PORT = 1571))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = uat.world)

    )

  )

prod.world.net =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = host_name_or_ip )(PORT = 1571))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prod.world)

    )

  )

this can probably be accomplished with a single line but i'm new to this language, give me a break.

enjoy...

How to resize/convert a Vagrant VMDK volume to VDI

Using Vagrant and Virtual box is very easy, but most boxes have very little space provisioned for the root volume, these steps will show how to covert your storage from a VMDK (Virtual Machine Disk) to a VDI (Virtual Disk Image), then expand that disk to a more appropriate size.


-make certain you have available space on your host machine prior to starting this exercise.


$ cd VirtualBox\ VMs/
$ ls
vagrants_oraclelinux1_testdb

$ cd vagrants_oraclelinux1_testdb/

$ ls 
Logs                                   box-disk1.vmdk                          vagrants_oraclelinux1_testdb.vbox

$ VBoxManage clonehd box-disk1.vmdk box-disk1.vdi -format VDI

$ ls 
Logs                                   box-disk1.vmdk             box-disk1.vdi           vagrants_oraclelinux1_testdb.vbox

--25gb for small db on root volume
$ VBoxManage modifyhd box-disk1.vdi --resize 25000

$ vagrants_oraclelinux1_testdb daniels$ VBoxManage showvminfo vagrants_oraclelinux1_testdb | grep "Storage"
Storage Controller Name (0):            IDE Controller
Storage Controller Type (0):            PIIX4
Storage Controller Instance Number (0): 0
Storage Controller Max Port Count (0):  2
Storage Controller Port Count (0):      2
Storage Controller Bootable (0):        on
Storage Controller Name (1):            SATA Controller
Storage Controller Type (1):            IntelAhci
Storage Controller Instance Number (1): 0
Storage Controller Max Port Count (1):  30
Storage Controller Port Count (1):      1
Storage Controller Bootable (1):        on

$ vagrants_oraclelinux1_testdb daniels$ VBoxManage showhdinfo "box-disk1.vdi"
UUID:           cd2bdcc2-87ba-4e07-88dd-a5f77c61aba1
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       /Users/drstanle/VirtualBox VMs/vagrants_oraclelinux1_testdb/box-disk1.vdi
Storage format: VDI
Format variant: dynamic default
Capacity:       20000 MBytes
Size on disk:   2166 MBytes
Encryption:     disabled


$ VBoxManage storageattach vagrants_oraclelinux1_testdb --storagectl "IDE Controller" --port 0 --device 0 --type hdd --medium box-disk1.vdi

--note
at this point you can remove/release the vmdk volume from your Virtual Machine. either delete the file on the OS or remove it from your VM console.

$ vagrant up

$ vagrant ssh

# df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root  8.3G  1.4G  6.5G  18% /   <------- not nearly enough
tmpfs                         230M     0  230M   0% /dev/shm
/dev/sda1                     477M   55M  398M  12% /boot

# fdisk -l

# fdisk /dev/sda

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): p

Disk /dev/sda: 26.2 GB, 26214400000 bytes
255 heads, 63 sectors/track, 3187 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0007bd61

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64        1293     9870336   8e  Linux LVM

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

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

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

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.


# shutdown -h now

$ vagrant ssh

# vgdisplay   -determine lv mapping belonging to VolGroupOS
  --- Volume group ---
  VG Name               VolGroup
  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               9.41 GiB
  PE Size               4.00 MiB
  Total PE              2409
  Alloc PE / Size       2409 / 9.41 GiB
  Free  PE / Size       0 / 0
  VG UUID               QLTbVE-sgqO-2CC0-fTmL-unRg-nnC7-vT56n6

# vgextend VolGroup /dev/sda3
  No physical volume label read from /dev/sda3
  Physical volume /dev/sda3 not found
  Physical volume "/dev/sda3" successfully created Volume group "VolGroup" successfully extended

# ls /dev/mapper/
  control  VolGroup-lv_root  VolGroup-lv_swap

# lvextend -l +100%FREE /dev/mapper/VolGroup-lv_root
  Extending logical volume lv_root to 23.01 GiB
  Logical volume lv_root successfully resized

# resize2fs /dev/mapper/VolGroup-lv_root
  resize2fs 1.43-WIP (20-Jun-2013)
  Filesystem at /dev/mapper/VolGroup-lv_root is mounted on /; on-line resizing required old_desc_blocks = 1, new_desc_blocks = 2
  The filesystem on /dev/mapper/VolGroup-lv_root is now 6032384 blocks long.

# exit

$ vagrant reload --provision

$ vagrant ssh

$ df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   23G  1.4G   21G   7% /       <---------woohoo
tmpfs                         230M     0  230M   0% /dev/shm
/dev/sda1                     477M   55M  398M  12% /boot
vagrant                       465G  184G  282G  40% /vagrant

enjoy

Locate current SQL by user, Oracle non-RAC

# example

  1  select s.sid, s.username, sql.sql_text from v$session s, v$sqlarea sql

  2  where s.status='ACTIVE'

  3  and s.type='USER'

  4  and s.sql_address=sql.address

  5* and s.username ='SYS'

[ SYS@localhost ] sql+> /

       SID USERNAME

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

SQL_TEXT

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

       982 SYS

select s.sid, s.username, sql.sql_text from v$session s, v$sqlarea sql where s.s

tatus='ACTIVE' and s.type='USER' and s.sql_address=sql.address and s.username ='

SYS'

1 row selected.

Who is blocking who, Oracle non-RAC

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 ;

BLOCKING_STATUS 

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

MSS@stfatl03 ( SID=1064 )  is blocking MSS@macatl02 ( SID=91 ) 

MSS@stfatl03 ( SID=1064 )  is blocking MSS@macatl02 ( SID=92 ) 

MSS@stfatl03 ( SID=1064 )  is blocking MSS@scmsnq01 ( SID=96 ) 

MSS@stfatl03 ( SID=1064 )  is blocking MSS@macatl02 ( SID=97 ) 

MSS@stfatl03 ( SID=1064 )  is blocking MSS@macsnq01 ( SID=98 ) 

MSS@stfatl03 ( SID=1064 )  is blocking MSS@stfsnq04 ( SID=99 ) 

MSS@stfatl03 ( SID=1064 )  is blocking MSS@scmsnq01 ( SID=100 ) 

MSS@stfatl03 ( SID=1064 )  is blocking MSS@macatl02 ( SID=102 ) 

MSS@stfatl03 ( SID=1064 )  is blocking MSS@macatl02 ( SID=103 ) 

MSS@stfatl03 ( SID=1064 )  is blocking MSS@scmatl03 ( SID=105 ) 

MSS@stfatl03 ( SID=1064 )  is blocking MSS@stfsnq04 ( SID=106 )