Create Linux Swap File

Currently I am using oravirt (Mikael Sandström) · GitHub (https://github.com/oravirt) vagrant boxes.

The swap is too small, wanted to increase for 18c upgrade test, tired of doing this manually, and here’s a script for that.

#!/bin/sh -x
swapon --show
free -h
rm -fv /swapfile1
dd if=/dev/zero of=/swapfile1 bs=1G count=16
ls -lh /swapfile?
chmod 0600 /swapfile1
mkswap /swapfile1
swapon /swapfile1
swapon --show
free -h
echo "/root/swapfile1         swap                    swap    defaults        0 0" >> /etc/fstab
cat /etc/fstab
exit

Script in action:

[root@racnode-dc1-2 patch]# ./mkswap.sh
+ swapon --show
NAME      TYPE      SIZE USED PRIO
/dev/dm-1 partition   2G  33M   -1
+ free -h
              total        used        free      shared  buff/cache   available
Mem:           5.6G        4.0G        114M        654M        1.4G        779M
Swap:          2.0G         33M        2.0G
+ rm -fv /swapfile1
+ dd if=/dev/zero of=/swapfile1 bs=1G count=16
16+0 records in
16+0 records out
17179869184 bytes (17 GB) copied, 42.7352 s, 402 MB/s
+ ls -lh /swapfile1
-rw-r--r-- 1 root root 16G Apr 14 15:18 /swapfile1
+ chmod 0600 /swapfile1
+ mkswap /swapfile1
Setting up swapspace version 1, size = 16777212 KiB
no label, UUID=b084bd5d-e32e-4c15-974f-09f505a0cedc
+ swapon /swapfile1
+ swapon --show
NAME       TYPE      SIZE   USED PRIO
/dev/dm-1  partition   2G 173.8M   -1
/swapfile1 file       16G     0B   -2
+ free -h
              total        used        free      shared  buff/cache   available
Mem:           5.6G        3.9G        1.0G        189M        657M        1.3G
Swap:           17G        173M         17G
+ echo '/root/swapfile1         swap                    swap    defaults        0 0'
+ cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Tue Apr 18 08:50:14 2017
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/ol-root     /                       xfs     defaults        0 0
UUID=ed2996e5-e077-4e23-83a5-10418226a725 /boot                   xfs     defaults        0 0
/dev/mapper/ol-swap     swap                    swap    defaults        0 0
/dev/vgora/lvora /u01 ext4 defaults 1 2
/root/swapfile1         swap                    swap    defaults        0 0
+ exit
[root@racnode-dc1-2 patch]#

Advertisements

Source Oracle Environment Easily

I have been patching a lot lately and wanted a fast and easy method to source Oracle environment.

The objective is to copy, paste from action plan vs having to selectively copy, edit, paste.

Example: . /media/patch/gi.env vs . oraenv — +ASM[n]

Started by creating gi.env which will be used to source GI for all RAC hosts.

You are probably thinking, isn’t it a PITA to have to edit and maintain all the gi.env per host, e.g. 6 nodes RAC cluster?

Rightfully so and it’s a PITA unless it’s dynamic.

There is one requirement: host# = instance#

Hence, +ASM1 is running on host05 will not work.

Next step would probably be to script the tasks.

DEMO1:

[oracle@racnode-dc1-1 ~]$ ps -ef|grep [p]mon
oracle 10818 1 0 03:58 ? 00:00:00 asm_pmon_+ASM1
oracle 11456 1 0 03:58 ? 00:00:00 ora_pmon_hawk1
oracle 11763 1 0 03:58 ? 00:00:00 mdb_pmon_-MGMTDB

[oracle@racnode-dc1-1 ~]$ . /media/patch/gi.env
ORACLE_SID = [+ASM1] ? The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.2.0.1/grid
ORACLE_HOME=/u01/app/12.2.0.1/grid
Oracle Instance alive for sid “+ASM1”

[oracle@racnode-dc1-1 ~]$ . /media/patch/hawk.env
ORACLE_SID = [+ASM1] ? The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_UNQNAME=hawk
ORACLE_SID=hawk1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/11.2.0.4/db1
Oracle Instance alive for sid “hawk1”

[oracle@racnode-dc1-1 ~]$ srvctl status database -d $ORACLE_UNQNAME
Instance hawk1 is running on node racnode-dc1-1
Instance hawk2 is running on node racnode-dc1-2

[oracle@racnode-dc1-1 ~]$ df -h /media/patch/
Filesystem Size Used Avail Use% Mounted on
media_patch 3.7T 413G 3.3T 12% /media/patch

[oracle@racnode-dc1-1 ~]$

[oracle@racnode-dc1-2 ~]$ ps -ef|grep [p]mon
oracle 7339 1 0 03:56 ? 00:00:00 asm_pmon_+ASM2
oracle 8904 1 0 03:57 ? 00:00:00 ora_pmon_hawk2

[oracle@racnode-dc1-2 ~]$ . /media/patch/gi.env
ORACLE_SID = [oracle] ? The Oracle base has been set to /u01/app/oracle
ORACLE_SID=+ASM2
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.2.0.1/grid
ORACLE_HOME=/u01/app/12.2.0.1/grid
Oracle Instance alive for sid “+ASM2”

[oracle@racnode-dc1-2 ~]$ . /media/patch/hawk.env
ORACLE_SID = [+ASM2] ? The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_UNQNAME=hawk
ORACLE_SID=hawk2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/11.2.0.4/db1
Oracle Instance alive for sid “hawk2”

[oracle@racnode-dc1-2 ~]$ srvctl status database -d $ORACLE_UNQNAME
Instance hawk1 is running on node racnode-dc1-1
Instance hawk2 is running on node racnode-dc1-2

[oracle@racnode-dc1-2 ~]$ cat /media/patch/gi.env
set +x
unset ORACLE_UNQNAME
h=$(hostname -s)
n=1
. oraenv <<< +ASM${h:${#h} – $n}
export GRID_HOME=$ORACLE_HOME
env|egrep ‘ORACLE|GRID’
sysresv|tail -1

[oracle@racnode-dc1-2 ~]$

[oracle@racnode-dc1-2 ~]$ cat /media/patch/hawk.env
set +x
h=$(hostname -s)
n=1
export ORACLE_UNQNAME=hawk
. oraenv <<< $ORACLE_UNQNAME${h:${#h} – $n}
env|grep ORACLE
sysresv|tail -1
[oracle@racnode-dc1-2 ~]$

DEMO2:

[oracle@racnode-dc1-1 ~]$ export PATCH_TOP_DIR=/u01/stage/patch/Jan2019
[oracle@racnode-dc1-1 ~]$
[oracle@racnode-dc1-1 ~]$ . /media/patch/gi.env
ORACLE_SID = [+ASM1] ? The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.2.0.1/grid
ORACLE_HOME=/u01/app/12.2.0.1/grid
Oracle Instance alive for sid “+ASM1”
[oracle@racnode-dc1-1 ~]$
[oracle@racnode-dc1-1 ~]$ export PREPATCH_LOG=$PATCH_TOP_DIR/`echo $ORACLE_HOME | awk -F/ ‘{print $NF}’`_prepatch_”$(hostname -s)”_lsinv.log
[oracle@racnode-dc1-1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail > $PREPATCH_LOG; echo $?
0
[oracle@racnode-dc1-1 ~]$ ls -l $PREPATCH_LOG
-rw-r–r– 1 oracle oinstall 205889 Feb 13 04:41 /u01/stage/patch/Jan2019/grid_prepatch_racnode-dc1-1_lsinv.log
[oracle@racnode-dc1-1 ~]$
[oracle@racnode-dc1-1 ~]$ . /media/patch/hawk.env
ORACLE_SID = [+ASM1] ? The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_UNQNAME=hawk
ORACLE_SID=hawk1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/11.2.0.4/db1
Oracle Instance alive for sid “hawk1”
[oracle@racnode-dc1-1 ~]$
[oracle@racnode-dc1-1 ~]$ export PREPATCH_LOG=$PATCH_TOP_DIR/`echo $ORACLE_HOME | awk -F/ ‘{print $NF}’`_prepatch_”$(hostname -s)”_lsinv.log
[oracle@racnode-dc1-1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail > $PREPATCH_LOG; echo $?
0
[oracle@racnode-dc1-1 ~]$ ls -l $PREPATCH_LOG
-rw-r–r– 1 oracle oinstall 118180 Feb 13 04:41 /u01/stage/patch/Jan2019/db1_prepatch_racnode-dc1-1_lsinv.log
[oracle@racnode-dc1-1 ~]$

 

 

RMAN 12c NF – SQL interface in RMAN

Admittedly, I did not realize the benefit at first until there came a need.

Example:

# Set ORACLE_SID
ORACLE_SID=$1
export ORACLE_SID
### This retrieve the numeric value from PROD1/PROD2, i.e. 1,2 respectively
n=`echo "${ORACLE_SID:(-1)}"`

run {
backup tag 'ARCHIVED_LOG' archivelog all delete input skip inaccessible;
alter database backup controlfile to trace as '/rman_bkup$n/$ORACLE_SID/cf_@.sql' REUSE RESETLOGS;
create pfile='/rman_bkup$n/$ORACLE_SID/init@.ora' from spfile;
create pfile from spfile;
}

 

Automating DG Broker

I have been applying PSU lately and what’s so hard out it?

Four+ databases running on Primary with DG Broker for standby.

There are no conventions, as some standby databases have dr appended to primary name while others have 2 appended to primary name.

I wanted to view the DG configuration for currently active instances and show_dg_config.sh will show me this.

Next, I want a faster way to shutdown DG by having syntax generated and  gen_dg_cmd.sh does this.

Guess I could have taken it further by creating a shell script to create shell scripts to shutdown DG.

One day when I am really bore, I might OR may be you are so nice to complete my mission.

Tested on AIX 7.1

Note: the ps -ef syntax is for AIX and will not work with Linux.

See below for the Linux alternative.

$ ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ

$ ps -eo args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
thor
hulk

show_dg_config.sh

#!/bin/sh -e
ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
export ORAENV_ASK=NO
for SID in ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'`
do
export ORACLE_SID=$SID
. /usr/local/bin/oraenv
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
echo "+++: " $ORACLE_SID $ORACLE_HOME
sysresv
dgmgrl -echo << END
connect /
show configuration
exit
END
done
exit

gen_dg_cmd.sh

#!/bin/sh -e
for XB in `egrep 'Primary|Physical' /tmp/dg.log |sort |awk -F" " '{print $3 $1}'`
do
#echo $XB
#echo $XB|awk '{print substr($1,1,7)}'
if [ "`echo $XB|awk '{print substr($1,1,7)}'`" == "Primary" ]
then
PRI=`echo $XB|awk '{print substr($1,8)}'`
echo "edit database $PRI set state='LOG-TRANSPORT-OFF';"
echo "show database $PRI"
echo "edit database $PRI set state='ONLINE';"
echo "show database $PRI"
fi
if [ "`echo $XB|awk '{print substr($1,1,8)}'`" == "Physical" ]
then
SBY=`echo $XB|awk '{print substr($1,9)}'`
echo "edit database $SBY set state='APPLY-OFF';"
echo "show database $SBY"
echo "edit database $SBY set state='APPLY-ON';"
echo "show database $SBY"
fi
done
exit

./show_dg_config.sh > /tmp/dg.log

egrep ‘Primary|Physical’ /tmp/dg.log |sort |awk -F” ” ‘{print $3 $1}’

Primarydb02
Physicaldb02dr
Primarydb01
Physicaldb01dr
Primarystageqa
Physicalstageqa2
Primarytest
Physicaltestdr

./gen_dg_cmd.sh

edit database db01 set state='LOG-TRANSPORT-OFF';
show database db01
edit database db01 set state='ONLINE';
show database db01
edit database db01dr set state='APPLY-OFF';
show database db01dr
edit database db01dr set state='APPLY-ON';
show database db01dr
edit database db02 set state='LOG-TRANSPORT-OFF';
show database db02
edit database db02 set state='ONLINE';
show database db02
edit database db02dr set state='APPLY-OFF';
show database db02dr
edit database db02dr set state='APPLY-ON';
show database db02dr
edit database stageqa set state='LOG-TRANSPORT-OFF';
show database stageqa
edit database stageqa set state='ONLINE';
show database stageqa
edit database stageqa2 set state='APPLY-OFF';
show database stageqa2
edit database stageqa2 set state='APPLY-ON';
show database stageqa2
edit database test set state='LOG-TRANSPORT-OFF';
show database test
edit database test set state='ONLINE';
show database test
edit database testdr set state='APPLY-OFF';
show database testdr
edit database testdr set state='APPLY-ON';
show database testdr
oracle:/home/oracle/working/dinh$

Using dynamic variables for shell scripting

So you may ask, why go through all the trouble?

Have you ever had to move scripts from one directory to another to find out the script is now broken?

This is due to the hard coding of the location for SQL script in the shell script.

When possible, I would like to separate shell / SQL scripts so they can be run individually and easier for debugging.

Don’t you hate it when copy/paste SQL from shell and require modifications?

Example:   SELECT count(*) FROM v\$transaction t, v\$session s WHERE t.addr=s.taddr

When the shell & SQL scripts have the same name, it’s more intuitive, as well as the log file.

Example:  open_trans.sh, open_trans.sql, /tmp/open_trans_hawklas.log

You can find example of shell scripts with all comments open_trans.sh

DEMO:

oracle@arrow:hawklas:/media/sf_working/scripts
$ ./open_trans.sh

Not enough variables passed to shell script.

./open_trans.sh: line 7: 1: ---> USAGE: ./open_trans.sh [oracle_sid] [minutes]

oracle@arrow:hawklas:/media/sf_working/scripts
$ ./open_trans.sh hawklas 12

The Oracle base remains unchanged with value /u01/app/oracle
+ rm -fv /tmp/open_trans_hawklas.log
removed `/tmp/open_trans_hawklas.log'
++ sqlplus -SL '/ as sysdba'
+ trans_ct='         1'
+ '[' 0 '!=' 0 ']'
+ '[' '         1' -eq 0 ']'
+ sqlplus -L '/ as sysdba'
+ '[' 0 '!=' 0 ']'
+ mail -s 'hawklas Open transactions longer than 12 minutes' dba@911.com
+ echo '*** Review /tmp/open_trans_hawklas.log for results ***'
*** Review /tmp/open_trans_hawklas.log for results ***
+ ls -l /tmp/open_trans_hawklas.log
-rw-r--r--. 1 oracle oinstall 950 Oct  4 11:48 /tmp/open_trans_hawklas.log
+ set +x

oracle@arrow:hawklas:/media/sf_working/scripts
$ cat /tmp/open_trans_hawklas.log

*** Open transactions longer than 12 minutes ***
old  17: AND t.start_date < sysdate-(&_minute/1440)
new  17: AND t.start_date < sysdate-(12/1440)

START_DT              USERNAME           OSUSER       MACHINE          PROGRAM          STATUS   EVENT                          SID_SERIAL   TSTATUS  SQLID
--------------------- ------------------ ------------ ---------------- ---------------- -------- ------------------------------ ------------ -------- -------------
04-OCT-2015 09:36:55  MDINH              oracle       arrow.localdomai sqlplus@arrow.lo INACTIVE SQL*Net message from client    108,219      ACTIVE


*** SQL syntax to kill sessions with open transactions ***
old   4: AND t.start_date < sysdate-(&_minute/1440)
new   4: AND t.start_date < sysdate-(12/1440)

SQL
-------------------------------------------------------------------------------------------------------------------------
 ALTER SYSTEM KILL SESSION '108,219' IMMEDIATE;

Run SQL script independently:

oracle@arrow:hawklas:/media/sf_working/scripts
$ sysdba @open_trans.sql 23

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 4 11:48:34 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


NAME
----------------
hawklas


*** Open transactions longer than 23 minutes ***
old  17: AND t.start_date < sysdate-(&_minute/1440)
new  17: AND t.start_date < sysdate-(23/1440)

START_DT              USERNAME           OSUSER       MACHINE          PROGRAM          STATUS   EVENT                          SID_SERIAL   TSTATUS  SQLID
--------------------- ------------------ ------------ ---------------- ---------------- -------- ------------------------------ ------------ -------- -------------
04-OCT-2015 09:36:55  MDINH              oracle       arrow.localdomai sqlplus@arrow.lo INACTIVE SQL*Net message from client    108,219      ACTIVE


*** SQL syntax to kill sessions with open transactions ***
old   4: AND t.start_date < sysdate-(&_minute/1440)
new   4: AND t.start_date < sysdate-(23/1440)

SQL
-------------------------------------------------------------------------------------------------------------------------
 ALTER SYSTEM KILL SESSION '108,219' IMMEDIATE;


"-------------------------------------------------"
+++++ Spool Output: /tmp/open_trans_hawklas.log
"-------------------------------------------------"

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Run shell script from another directory specifying full path:

oracle@arrow:hawklas:/media/sf_working/scripts
$ cd /tmp/
oracle@arrow:hawklas:/tmp
$ /media/sf_working/scripts/open_trans.sh hawlas 45

ORACLE_HOME = [/home/oracle] ? ^C

oracle@arrow:hawklas:/tmp
$ /media/sf_working/scripts/open_trans.sh hawklas 45

The Oracle base remains unchanged with value /u01/app/oracle
+ rm -fv /tmp/open_trans_hawklas.log
removed `/tmp/open_trans_hawklas.log'
++ sqlplus -SL '/ as sysdba'
+ trans_ct='         1'
+ '[' 0 '!=' 0 ']'
+ '[' '         1' -eq 0 ']'
+ sqlplus -L '/ as sysdba'
+ '[' 0 '!=' 0 ']'
+ mail -s 'hawklas Open transactions longer than 45 minutes' dba@911.com
+ echo '*** Review /tmp/open_trans_hawklas.log for results ***'
*** Review /tmp/open_trans_hawklas.log for results ***
+ ls -l /tmp/open_trans_hawklas.log
-rw-r--r--. 1 oracle oinstall 950 Oct  4 11:49 /tmp/open_trans_hawklas.log
+ set +x

oracle@arrow:hawklas:/tmp
$ cat /tmp/open_trans_hawklas.log

*** Open transactions longer than 45 minutes ***
old 17: AND t.start_date < sysdate-(&_minute/1440)
new 17: AND t.start_date < sysdate-(45/1440)

START_DT USERNAME OSUSER MACHINE PROGRAM STATUS EVENT SID_SERIAL TSTATUS SQLID
--------------------- ------------------ ------------ ---------------- ---------------- -------- ------------------------------ ------------ -------- -------------
04-OCT-2015 09:36:55 MDINH oracle arrow.localdomai sqlplus@arrow.lo INACTIVE SQL*Net message from client 108,219 ACTIVE

*** SQL syntax to kill sessions with open transactions ***
old 4: AND t.start_date < sysdate-(&_minute/1440)
new 4: AND t.start_date < sysdate-(45/1440)

SQL
-------------------------------------------------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '108,219' IMMEDIATE;
oracle@arrow:hawklas:/tmp
$

oracle@arrow:hawklas:/tmp
$ /media/sf_working/scripts/open_trans.sh hawklas 44444445 > /tmp/cron.log 2>&1

oracle@arrow:hawklas:/tmp
$ cat /tmp/cron.log

The Oracle base remains unchanged with value /u01/app/oracle
+ rm -fv /tmp/open_trans_hawklas.log
removed `/tmp/open_trans_hawklas.log'
++ sqlplus -SL '/ as sysdba'
+ trans_ct='         0'
+ '[' 0 '!=' 0 ']' 
+ '[' '         0' -eq 0 ']'
+ echo 'Number of opened transactions:          0'
Number of opened transactions:          0
+ exit 1
oracle@arrow:hawklas:/tmp
$ ls open*
ls: cannot access open*: No such file or directory
oracle@arrow:hawklas:/tmp
$

Shell script – checking for NULL & RMAN backup

The existing backup strategy for archivelog is Disk-to-Disk-to-Tape (D2D2T), using RMAN for disk and NetBackup for tape.

Delete existing backup after successfully back up to tape.

Don’t know enough about NetBackup or whether it can do the deletion, and that’s a story for another day.

Here is the scenario: the file exist but contains NULL data, will the RMAN backup be deleted?

File exists:

> ll /home/oracle/check_backup/check_bkup.out
-rw-r--r--   1 mdinh    dba            0 Dec 15 13:15 /home/oracle/check_backup/check_bkup.out

NULL data: the file is  ZERO byte.

Script:

> cat t.sh
#!/bin/sh

DN=`dirname $0`
BN=`basename $0`
SID=${1:?"---> USAGE: $DN/$BN <ORACLE_SID>"}

date
FILE=/home/oracle/check_backup/check_bkup.out
LAST=`head /home/oracle/check_backup/check_bkup.out |grep Archive|head -1|awk '{print $1}'`
# Check for ora_smon process
if [ `pgrep -lf ora_smon_$1|awk -F_ '{print $3}'` != $1 ]; then
exit 1
elif [ `pgrep -lf "$BN $1"|wc -l` -gt 2 ]; then
exit 1
elif [ ! -f $FILE ]; then
mailx -s "Missing $FILE" maile@me.com < /dev/null
exit 1
fi

echo ---------- NO NULL check ----------
echo "delete noprompt backup completed before \"to_date('${LAST}','mm/dd/yyyy')\";"

# if $LAST IS NOT NULL
if [ ! -z "$LAST" ]; then
echo "delete noprompt backup completed before \"to_date('${LAST}','mm/dd/yyyy')\";"
fi
exit

Execution:

> ./t.sh db01
Tue Dec 18 05:31:20 PST 2012
---------- NO NULL check ----------
delete noprompt backup completed before "to_date('','mm/dd/yyyy')";

Curiosity: What will happen when delete noprompt backup is executed? Any guess?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

RMAN> list backup;
using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> backup archivelog from sequence 157;

Starting backup at 18-DEC-2012 05:55:20
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=102 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=157 RECID=271 STAMP=802331584
input archived log thread=1 sequence=158 RECID=272 STAMP=802331720
channel ORA_DISK_1: starting piece 1 at 18-DEC-2012 05:55:21
channel ORA_DISK_1: finished piece 1 at 18-DEC-2012 05:55:25
piece handle=/oracle/oradata/flashrecovery/SAN_DBTEST/backupset/2012_12_18/o1_mf_annnn_TAG20121218T055521_8f0xgb07_.bkp tag=TAG20121218T055521 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 18-DEC-2012 05:55:25

RMAN> list backup summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1       B  A  A DISK        18-DEC-2012 05:55:24 1       1       NO         TAG20121218T055521

RMAN> delete noprompt backup completed before "to_date('','mm/dd/yyyy')";
using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1       1       1   1   AVAILABLE   DISK        /oracle/oradata/flashrecovery/SAN_DBTEST/backupset/2012_12_18/o1_mf_annnn_TAG20121218T055521_8f0xgb07_.bkp
deleted backup piece
backup piece handle=/oracle/oradata/flashrecovery/SAN_DBTEST/backupset/2012_12_18/o1_mf_annnn_TAG20121218T055521_8f0xgb07_.bkp RECID=1 STAMP=802331722
Deleted 1 objects

RMAN>

Shell and SQL Script

A quick note on separating SQL from shell script.

If I have a shell script name longops.sh, then it will call longops.sql.

Don’t embed SQL in shell script so that SQL script can be executed independently.

> cat longops.sh
#!/bin/sh
. /home/oracle/.common.conf > /dev/null
DN=`dirname $0`
BN=`basename $0`

sql=`echo $BN|cut -d'.' -f1`.sql
for sid in `ps -eo args|grep ora_smon|grep -v grep|awk -F_ '{print $3}'`
do
  OUTF=/tmp/longops.${sid}.html
  rm -f $OUTF
  ORACLE_SID=$sid
  . oraenv
  $ORACLE_HOME/bin/sqlplus -SL > /dev/null / << END
    spool $OUTF
    prompt From: oracle@mail.com
    prompt To: dba@mail.com
    prompt Subject: WARNING ${BN}: $ORACLE_SID - SQL running over one hour
    prompt Content-type: text/html
    prompt MIME-Version: 1.0
    set markup html on entmap off table 'BORDER="2"'
    @${sql}
    spool off
    exit
END
  if [ "$?" -ne "0" ]; then
    mailx -s "FAILED: $BN $*" $EMAIL < /dev/null
  exit 1
  fi
  if [ `grep -ic MACHINE $OUTF` != 0 ]; then
    /usr/sbin/sendmail -t < $OUTF
  fi
done
exit


> cat longops.sql
ALTER SESSION SET NLS_DATE_FORMAT='Dy, DD-Mon-YYYY HH24:MI:SS';
SELECT s.sid, s.serial#,
       s.username,  s.osuser, s.machine, s.program,
       s.sql_id, s.sql_exec_start, s.logon_time,
       ROUND (s.last_call_et/3600,3) last_call_hr,
       ROUND ((SYSDATE-s.SQL_EXEC_START)*24,3) exec_start_hr,
       CASE WHEN s.state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state,
       CASE WHEN s.state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event
FROM   v$session s
WHERE  s.type = 'USER' AND s.status = 'ACTIVE' AND ROUND (s.last_call_et/3600)>1
ORDER BY s.last_call_et DESC;