Thinking Out Loud

July 24, 2019

Rsync DBFS To ACFS For GoldenGate Trail Migration

Filed under: GoldenGate,shell scripting — mdinh @ 2:25 pm

Planning to move GoldenGate trail files from DBFS to ACFS.

This is pre-work before actual migration to stress IO for ACFS.

Learned some cron along the way.

# Run every 2 hours at even hours
0 */2 * * * /home/oracle/working/dinh/acfs_ggdata02_rsync.sh > /tmp/rsync_acfs_ggdata_to_ggdata02.log 2>&1

# Run every 2 hours at odd hours
0 1-23/2 * * * /home/oracle/working/dinh/acfs_ggdata02_rsync.sh > /tmp/rsync_acfs_ggdata_to_ggdata02.log 2>&1

Syntax and ouptput.

+ /bin/rsync -vrpogt --delete-after /DBFS/ggdata/ /ACFS/ggdata
building file list ... done

dirchk/E_SOURCE.cpe
dirchk/P_TARGET.cpe

dirdat/
dirdat/aa000307647
dirdat/aa000307648
.....
dirdat/aa000307726
dirdat/aa000307727

deleting dirdat/aa000306741
deleting dirdat/aa000306740
.....
deleting dirdat/aa000306662
deleting dirdat/aa000306661

sent 16,205,328,959 bytes  received 1,743 bytes  140,305,893.52 bytes/sec
total size is 203,021,110,174  speedup is 12.53

real	1m56.671s
user	1m24.643s
sys	0m45.875s

+ '[' 0 '!=' 0 ']'

+ /bin/diff -rq /DBFS/ggdata /ACFS/ggdata

Files /DBFS/ggdata/dirchk/E_SOURCE.cpe and /ACFS/ggdata/dirchk/E_SOURCE.cpe differ
Files /DBFS/ggdata/dirchk/P_TARGET.cpe and /ACFS/ggdata/dirchk/P_TARGET.cpe differ

Only in /ACFS/ggdata/dirdat: aa000306742
Only in /ACFS/ggdata/dirdat: aa000306743
Only in /ACFS/ggdata/dirdat: aa000306744
Only in /ACFS/ggdata/dirdat: aa000306745

Only in /DBFS/ggdata/dirdat: aa000307728
Only in /DBFS/ggdata/dirdat: aa000307729

real	69m15.207s
user	2m9.242s
sys	17m3.846s

+ ls /DBFS/ggdata/dirdat/
+ wc -l
975

+ ls -alrt /DBFS/ggdata/dirdat/
+ head
total 190631492
drwxrwxrwx 24 root    root             0 Feb  9  2018 ..
-rw-r-----  1 ggsuser oinstall 199999285 Mar  8  2018 .fuse_hidden001a3c47000001c5
-rw-r-----  1 ggsuser oinstall 199999896 May 23 00:23 .fuse_hidden000002b500000001
-rw-r-----  1 ggsuser oinstall 199999934 Jul 23 06:11 aa000306798
-rw-r-----  1 ggsuser oinstall 199999194 Jul 23 06:13 aa000306799
-rw-r-----  1 ggsuser oinstall 199999387 Jul 23 06:14 aa000306800
-rw-r-----  1 ggsuser oinstall 199999122 Jul 23 06:16 aa000306801
-rw-r-----  1 ggsuser oinstall 199999172 Jul 23 06:19 aa000306802
-rw-r-----  1 ggsuser oinstall 199999288 Jul 23 06:19 aa000306803

+ ls -alrt /DBFS/ggdata/dirdat/
+ tail
-rw-r-----  1 ggsuser oinstall 199999671 Jul 24 07:59 aa000307764
-rw-r-----  1 ggsuser oinstall 199999645 Jul 24 08:01 aa000307765
-rw-r-----  1 ggsuser oinstall 199998829 Jul 24 08:02 aa000307766
-rw-r-----  1 ggsuser oinstall 199998895 Jul 24 08:04 aa000307767
-rw-r-----  1 ggsuser oinstall 199999655 Jul 24 08:05 aa000307768
-rw-r-----  1 ggsuser oinstall 199999930 Jul 24 08:07 aa000307769
-rw-r-----  1 ggsuser oinstall 199999761 Jul 24 08:09 aa000307770
-rw-r-----  1 ggsuser oinstall 199999421 Jul 24 08:11 aa000307771
-rw-r-----  1 ggsuser oinstall   7109055 Jul 24 08:11 aa000307772

+ ls /ACFS/ggdata/dirdat/
+ wc -l
986

+ ls -alrt /ACFS/ggdata/dirdat/
+ head
total 194779104
drwxrwxrwx 24 root    root          8192 Feb  9  2018 ..
-rw-r-----  1 ggsuser oinstall 199999285 Mar  8  2018 .fuse_hidden001a3c47000001c5
-rw-r-----  1 ggsuser oinstall 199999896 May 23 00:23 .fuse_hidden000002b500000001
-rw-r-----  1 ggsuser oinstall 199998453 Jul 23 04:55 aa000306742
-rw-r-----  1 ggsuser oinstall 199999657 Jul 23 04:56 aa000306743
-rw-r-----  1 ggsuser oinstall 199999227 Jul 23 04:57 aa000306744
-rw-r-----  1 ggsuser oinstall 199999389 Jul 23 04:59 aa000306745
-rw-r-----  1 ggsuser oinstall 199999392 Jul 23 05:00 aa000306746
-rw-r-----  1 ggsuser oinstall 199999116 Jul 23 05:01 aa000306747

+ ls -alrt /ACFS/ggdata/dirdat/
+ tail
-rw-r-----  1 ggsuser oinstall 199999876 Jul 24 06:48 aa000307719
-rw-r-----  1 ggsuser oinstall 199999751 Jul 24 06:50 aa000307720
-rw-r-----  1 ggsuser oinstall 199999918 Jul 24 06:51 aa000307721
-rw-r-----  1 ggsuser oinstall 199999404 Jul 24 06:52 aa000307722
-rw-r-----  1 ggsuser oinstall 199999964 Jul 24 06:54 aa000307723
-rw-r-----  1 ggsuser oinstall 199999384 Jul 24 06:56 aa000307724
-rw-r-----  1 ggsuser oinstall 199999283 Jul 24 06:57 aa000307725
-rw-r-----  1 ggsuser oinstall 199998033 Jul 24 06:59 aa000307726
-rw-r-----  1 ggsuser oinstall 199999199 Jul 24 07:00 aa000307727

June 15, 2019

Shell Scripting Using set -v

Filed under: shell scripting — mdinh @ 1:22 pm

set -v : Print shell input lines as they are read.

show_gds_status.sh

#!/bin/sh
##############################
# GDSCTL> configure -width 132
# GDSCTL> configure -save_config
##############################
. ~/gsm1.sh
set -evx
gdsctl -show << END
status
databases
services
exit
END
exit

Excute show_gds_status.sh

[oracle@SLC02PNY GDS]$ ./show_gds_status.sh
gdsctl -show << END
status
databases
services
exit
END
+ gdsctl -show
gsm       : GSM1
TNS_ADMIN : /u01/app/oracle/product/18.0.0/gsmhome_1/network/admin
driver    : jdbc:oracle:thin:
resolve   : QUAL_HOSTNAME
timeout   : 150
log_level : OFF
version   : 18.0.0.0.0
width     : 132
verbose   : ON
spool     : OFF
showtime  : OFF
GDSCTL: Version 18.0.0.0.0 - Production on Sat Jun 15 13:01:21 UTC 2019

Copyright (c) 2011, 2018, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

Current GSM is set to GSM1
GDSCTL>
Alias                     GSM1
Version                   18.0.0.0.0
Start Date                15-JUN-2019 12:22:28
Trace Level               off
Listener Log File         /u01/app/oracle/diag/gsm/SLC02PNY/gsm1/alert/log.xml
Listener Trace File       /u01/app/oracle/diag/gsm/SLC02PNY/gsm1/trace/ora_9504_140547635764096.trc
Endpoint summary          (ADDRESS=(HOST=SLC02PNY.localdomain)(PORT=1571)(PROTOCOL=tcp))
GSMOCI Version            2.2.1
Mastership                Y
Connected to GDS catalog  Y
Process Id                9507
Number of reconnections   0
Pending tasks.     Total  0
Tasks in  process. Total  0
Regional Mastership       TRUE
Total messages published  152
Time Zone                 +00:00
Orphaned Buddy Regions:
     None
GDS region                region1
Network metrics:
   Region: region2 Network factor:0

GDSCTL>
Database: "chi" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2
   Service: "prim" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Service: "stby" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     sales%11
Database: "sfo" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Service: "prim" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Service: "stby" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     sales%1

GDSCTL>
Service "prim.sales.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "sales%1", name: "sales", db: "sfo", region: "region1", status: ready.
Service "stby.sales.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "sales%11", name: "sales", db: "chi", region: "region2", status: ready.

GDSCTL>
exit
+ exit
[oracle@SLC02PNY GDS]$

help set

[oracle@SLC02PNY GDS]$ help set
set: set [-abefhkmnptuvxBCHP] [-o option-name] [--] [arg ...]
    Set or unset values of shell options and positional parameters.

    Change the value of shell attributes and positional parameters, or
    display the names and values of shell variables.

    Options:
      -a  Mark variables which are modified or created for export.
      -b  Notify of job termination immediately.
      -e  Exit immediately if a command exits with a non-zero status.
      -f  Disable file name generation (globbing).
      -h  Remember the location of commands as they are looked up.
      -k  All assignment arguments are placed in the environment for a
          command, not just those that precede the command name.
      -m  Job control is enabled.
      -n  Read commands but do not execute them.
      -o option-name
          Set the variable corresponding to option-name:
              allexport    same as -a
              braceexpand  same as -B
              emacs        use an emacs-style line editing interface
              errexit      same as -e
              errtrace     same as -E
              functrace    same as -T
              hashall      same as -h
              histexpand   same as -H
              history      enable command history
              ignoreeof    the shell will not exit upon reading EOF
              interactive-comments
                           allow comments to appear in interactive commands
              keyword      same as -k
              monitor      same as -m
              noclobber    same as -C
              noexec       same as -n
              noglob       same as -f
              nolog        currently accepted but ignored
              notify       same as -b
              nounset      same as -u
              onecmd       same as -t
              physical     same as -P
              pipefail     the return value of a pipeline is the status of
                           the last command to exit with a non-zero status,
                           or zero if no command exited with a non-zero status
              posix        change the behavior of bash where the default
                           operation differs from the Posix standard to
                           match the standard
              privileged   same as -p
              verbose      same as -v
              vi           use a vi-style line editing interface
              xtrace       same as -x
      -p  Turned on whenever the real and effective user ids do not match.
          Disables processing of the $ENV file and importing of shell
          functions.  Turning this option off causes the effective uid and
          gid to be set to the real uid and gid.
      -t  Exit after reading and executing one command.
      -u  Treat unset variables as an error when substituting.
================================================================================
      -v  Print shell input lines as they are read.
================================================================================
      -x  Print commands and their arguments as they are executed.
      -B  the shell will perform brace expansion
      -C  If set, disallow existing regular files to be overwritten
          by redirection of output.
      -E  If set, the ERR trap is inherited by shell functions.
      -H  Enable ! style history substitution.  This flag is on
          by default when the shell is interactive.
      -P  If set, do not follow symbolic links when executing commands
          such as cd which change the current directory.
      -T  If set, the DEBUG trap is inherited by shell functions.
      --  Assign any remaining arguments to the positional parameters.
          If there are no remaining arguments, the positional parameters
          are unset.
      -   Assign any remaining arguments to the positional parameters.
          The -x and -v options are turned off.

    Using + rather than - causes these flags to be turned off.  The
    flags can also be used upon invocation of the shell.  The current
    set of flags may be found in $-.  The remaining n ARGs are positional
    parameters and are assigned, in order, to $1, $2, .. $n.  If no
    ARGs are given, all shell variables are printed.

    Exit Status:
    Returns success unless an invalid option is given.
[oracle@SLC02PNY GDS]$

April 14, 2019

Create Linux Swap File

Filed under: shell scripting,Vagrant,VirtualBox — mdinh @ 1:26 pm

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]#

February 13, 2019

Source Oracle Environment Easily

Filed under: oracle,shell scripting — mdinh @ 3:50 am

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 ~]$

 

 

July 9, 2016

RMAN 12c NF – SQL interface in RMAN


Filed under: 12c,RMAN,shell scripting — mdinh @ 11:51 pm

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;
}

 

May 15, 2016

Automating DG Broker

Filed under: 11g,awk_sed_grep,Dataguard,oracle,shell scripting — mdinh @ 2:11 am

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$

October 4, 2015

Using dynamic variables for shell scripting

Filed under: linux,oracle,shell scripting — mdinh @ 7:21 pm

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
$

December 18, 2012

Shell script – checking for NULL & RMAN backup

Filed under: 11g,RMAN,shell scripting,solaris — mdinh @ 2:13 pm

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>

October 16, 2011

Shell and SQL Script

Filed under: oracle,shell scripting — mdinh @ 10:36 pm

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;

Create a free website or blog at WordPress.com.