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 ) 

Use sar to find point in time cpu usage

# using sar to find point in time cpu usage

$ echo `sar 1 1|tail -1f` | cut -d' ' -f5 

Redirect stdout to logfile

#!/bin/bash

# redirect stdout to logfile

LOGFILE=/tmp/log.$DATE

exec 2>&1 >>${LOGFILE}

Kill all non-local Oracle processes

* be very careful with this one

$ ps -ef | grep '(LOCAL=NO)' | grep -v grep | awk '{print $2}' | xargs kill -9

Shipping files with ftp, scp, and tar

# ship a file with tar

$ tar cf - $finename | /usr/bin/ssh osuser@10.10.10.123 'cd /home/ftp; tar xf -' 

# scp push, pull, and throttle

* pull

$ scp osuser@10.10.10.123:/tmp/a.foo .

* push

$ scp a.foo 10.10.10.123:/tmp/

* pull w/25 mbit throttle

$ scp -l 25000 osuser@10.10.10.123:/tmp/a.foo .

* push w/50 mbit throttle

$ scp -l 50000 a.foo osuser@10.10.10.123:/tmp/

Locate/Remove files older than (n) days

* using ctime  date-30

# test 

$ find /tmp/*.foo -depth -type d -ctime +30

# removal

$ find /tmp/*.foo -depth -type d -ctime +30 —exec rm {} \;

* using mtime date-15

# test

$ find /tmp/*.foo -depth -type f -mtime +15

# removal 

$ find /tmp/*.foo -depth -type f -mtime +15 -exec rm {} \;

Recover a deleted file from an inode

if a process still holds the file in memory you can recover the deleted file.

# create a file with some data, tail and follow it, leaving the process running.
[root@localhost ~]# touch daniels.a
[root@localhost ~]# echo "hello" > daniels.a
[root@localhost ~]# tail -f daniels.a
hello


# now remove the file from another session.
[root@localhost ~]# ls
anaconda-ks.cfg  bin  daniels.a  install.log  install.log.syslog  scripts  sysctl-adds

[root@localhost ~]# rm -rf daniels.a


# locate the open process still holding the inode.
[root@localhost ~]# ps ax| grep tail
12254 pts/1    S+     0:00 tail -f daniels.a

# lsof shows the inode(3) and process id(12254), and the pointer to the removed file.
[root@localhost ~]# lsof | grep daniels.a
tail      12254    root    3r      REG              253,0           6               491553 /root/daniels.a (deleted)

[root@localhost ~]# ls daniels.a
ls: daniels.a: No such file or directory


# locate the inode using process and inode to build a recovery file.
[root@localhost ~]# ls -l /proc/12254/fd/3
lr-x------ 1 root root 64 Oct  3 19:40 /proc/12254/fd/3 -> /root/daniels.a (deleted)


[root@localhost ~]# cat /proc/12254/fd/3
hello

[root@localhost ~]# ls
anaconda-ks.cfg  bin  install.log  install.log.syslog  scripts  sysctl-adds
[root@localhost ~]# cp /proc/12254/fd/3  daniels.b
[root@localhost ~]# cat daniels.b
hello


enjoy...

Keep a scripted job from overlapping on itself

seems simple but have you ever had a scheduled job run long for any number of reasons and wished you could easily prevent it?

to prevent this you can easily touch/timestamp a file when the job starts, then have your script look for that file before it proceeds with the job.

 -i use this snippet in some backup scripts and it works quite well.

KEEP IT SIMPLE STUPID

#!/bin/bash

if [ -f $JOB_FLAG_DIR/.kiss.job.in_progress ]
    then
  /bin/mail -s "JOB OVERLAP $job_name -$host_name $script_name" $critical_mail < $JOB_FLAG_DIR/.kiss.job.in_progress
    exit 1555
    else
echo $job started `date +%Y%m%d.%H:%M:%S` > $JOB_FLAG_DIR/.kiss.job.in_progress
ps ax | grep $script_name | head -n 1 >> $JOB_FLAG_DIR/.kiss.job.in_progress
fi

Oracle 12c Interactive Reference Tool

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/poster/OUTPUT_poster/poster.html#tab_1

EXAMPLES:
12c Database Architecture


12c Multitenant Architecture

Setup Vagrant with VirtualBox

this is the most minimalist install/config i could come up with.

download/install vagrant from https://atlas.hashicorp.com

download/install virtual box hypervisor from https://www.virtualbox.org/wiki/Downloads

search for desired box from https://atlas.hashicorp.com/boxes/search
* for this example i have chosen an oracle enterprise version of linux 6.5 x86/64. 
stoilis/oel65-64 Oracle Linux 6.5 x86_64

$ cd ~
$ mkdir vagrants  -this will be your vagrant root home, all files placed here are shared among your boxes.
$ cd vagrants

$ vagrant init stoilis/oel65-64;   -this will download box and create your Vagrantfile for managing boxes.

add box definition to Vagrantfile and save.

* do not remove this file, edit to add and removed machines.

  

* add this config to Vagrantfile

  config.vm.define "oraclelinux1" do |oralinux|

    oralinux.vm.box = "stoilis/oel65-64"

  end

$ vagrant status

Current machine states:

oraclelinux1              not created (virtualbox)

$ vagrant up

Bringing machine 'oraclelinux1' up with 'virtualbox' provider...

==> oraclelinux1: Importing base box 'stoilis/oel65-64'...

==> oraclelinux1: Matching MAC address for NAT networking...

==> oraclelinux1: Checking if box 'stoilis/oel65-64' is up to date...

==> oraclelinux1: Setting the name of the VM: vagrants_oraclelinux1_1446680168757_20990

==> oraclelinux1: Clearing any previously set forwarded ports...

==> oraclelinux1: Clearing any previously set network interfaces...

==> oraclelinux1: Preparing network interfaces based on configuration...

    oraclelinux1: Adapter 1: nat

    oraclelinux1: Adapter 2: hostonly

==> oraclelinux1: Forwarding ports...

    oraclelinux1: 22 => 2222 (adapter 1)

==> oraclelinux1: Booting VM...

==> oraclelinux1: Waiting for machine to boot. This may take a few minutes...

    oraclelinux1: SSH address: 127.0.0.1:2222

    oraclelinux1: SSH username: vagrant

    oraclelinux1: SSH auth method: private key

    oraclelinux1: Warning: Connection timeout. Retrying...

    oraclelinux1: Warning: Connection timeout. Retrying...

    oraclelinux1: 

    oraclelinux1: Vagrant insecure key detected. Vagrant will automatically replace

    oraclelinux1: this with a newly generated keypair for better security.

    oraclelinux1: 

    oraclelinux1: Inserting generated public key within guest...

    oraclelinux1: Removing insecure key from the guest if it's present...

    oraclelinux1: Key inserted! Disconnecting and reconnecting using new SSH key...

==> oraclelinux1: Machine booted and ready!

==> oraclelinux1: Checking for guest additions in VM...

    oraclelinux1: The guest additions on this VM do not match the installed version of

    oraclelinux1: VirtualBox! In most cases this is fine, but in rare cases it can

    oraclelinux1: prevent things such as shared folders from working properly. If you see

    oraclelinux1: shared folder errors, please make sure the guest additions within the

    oraclelinux1: virtual machine match the version of VirtualBox you have installed on

    oraclelinux1: your host and reload your VM.

    oraclelinux1: 

    oraclelinux1: Guest Additions Version: 4.3.12

    oraclelinux1: VirtualBox Version: 5.0

==> oraclelinux1: Configuring and enabling network interfaces...

==> oraclelinux1: Mounting shared folders...

$ vagrant status

Current machine states:

oraclelinux1              running (virtualbox)

connect via ssh

$ vagrant ssh oraclelinux1

Welcome to Veewee built Vagrant Base Box.

[vagrant@oracle ~]$ su -

Password: 

[root@oracle ~]# uname -a

Linux oracle.vagrantup.com 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013 x86_64 x86_64 x86_64 GNU/Linux

[root@oracle ~]# cat /etc/redhat-release 

Red Hat Enterprise Linux Server release 6.5 (Santiago)

[root@oracle ~]# ifconfig -a

eth0      Link encap:Ethernet  HWaddr 08:00:27:71:CD:31  

          inet addr:10.0.2.15  Bcast:10.0.2.255  Mask:255.255.255.0

          inet6 addr: fe80::a00:27ff:fe71:cd31/64 Scope:Link

          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

          RX packets:899 errors:0 dropped:0 overruns:0 frame:0

          TX packets:549 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 txqueuelen:1000 

          RX bytes:91751 (89.6 KiB)  TX bytes:74069 (72.3 KiB)

eth1      Link encap:Ethernet  HWaddr 08:00:27:F7:BC:43  

          inet addr:172.28.128.3  Bcast:172.28.128.255  Mask:255.255.255.0

          inet6 addr: fe80::a00:27ff:fef7:bc43/64 Scope:Link

          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

          RX packets:2 errors:0 dropped:0 overruns:0 frame:0

          TX packets:10 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 txqueuelen:1000 

          RX bytes:1180 (1.1 KiB)  TX bytes:1272 (1.2 KiB)

lo        Link encap:Local Loopback  

          inet addr:127.0.0.1  Mask:255.0.0.0

          inet6 addr: ::1/128 Scope:Host

          UP LOOPBACK RUNNING  MTU:65536  Metric:1

          RX packets:0 errors:0 dropped:0 overruns:0 frame:0

          TX packets:0 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 txqueuelen:0 

          RX bytes:0 (0.0 b)  TX bytes:0 (0.0 b)

[root@oracle ~]# df -h

Filesystem                    Size  Used Avail Use% Mounted on

/dev/mapper/VolGroup-lv_root  8.3G  1.4G  6.5G  18% /

tmpfs                         230M     0  230M   0% /dev/shm

/dev/sda1                     477M   55M  398M  12% /boot

vagrant                       465G  181G  285G  39% /vagrant      <---shared vagrant root home

[root@oracle ~]# exit

logout

[vagrant@oracle ~]$ exit

logout

Connection to 127.0.0.1 closed.

test your box's ip address

$ ping 172.28.128.3 

PING 172.28.128.3 (172.28.128.3): 56 data bytes

64 bytes from 172.28.128.3: icmp_seq=0 ttl=64 time=0.755 ms

64 bytes from 172.28.128.3: icmp_seq=1 ttl=64 time=0.323 ms

64 bytes from 172.28.128.3: icmp_seq=2 ttl=64 time=0.308 ms

$ vagrant halt oraclelinux1   -shutdown your box

$ vagrant destroy oraclelinux1  -delete your box if desired

 --enjoy