nohup vs screen

While I have played a little with screen , my preference is nohup since the output from screen is so ugly.

With that being said, it would be nice for application designs to be resumable, e.g. Shocking opatchauto resume works after auto-logout

There were discussions about running many SQLs where each SQL is run manually, check for error before running the next SQL; hence, screen was used.

Why not trap for error and exit vs manually checking?

[oracle@ol7-121-dg3 ~]$ cat error.sql
WHENEVER SQLERROR EXIT SQL.SQLCODE
set echo on
select sysdate from dual;
select * from notable;
select database_role from v$database;
exit

[oracle@ol7-121-dg3 ~]$ sqlplus / as sysdba @ error.sql

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 10 17:48:46 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

OL7-121-DG3:(SYS@hawkc:PRIMARY> select sysdate from dual;

SYSDATE
-------------------
2020-05-10 17:48:46
OL7-121-DG3:(SYS@hawkc:PRIMARY> select * from notable;
select * from notable
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol7-121-dg3 ~]$

Another reason for using screen is to run stored procedure.

But this can be performed using nohup too as demonstrated.

[oracle@ol7-121-dg3 ~]$ export filename=test
[oracle@ol7-121-dg3 ~]$ cat > $filename.sql < set serverout on echo on
> exec dbms_output.put_line('test');
> exit
> EOF

[oracle@ol7-121-dg3 ~]$ ls -l $filename.sql
-rw-r--r--. 1 oracle oinstall 65 May 10 17:30 test.sql
[oracle@ol7-121-dg3 ~]$ cat $filename.sql
set serverout on echo on
exec dbms_output.put_line('test');
exit

[oracle@ol7-121-dg3 ~]$ nohup sqlplus "/ as sysdba" @ $filename.sql > $filename.log 2>&1 &
[1] 8422

[oracle@ol7-121-dg3 ~]$
[1]+  Done                    nohup sqlplus "/ as sysdba" @ $filename.sql > $filename.log 2>&1

[oracle@ol7-121-dg3 ~]$ ls -l $filename.*
-rw-r--r--. 1 oracle oinstall 616 May 10 17:30 test.log
-rw-r--r--. 1 oracle oinstall  65 May 10 17:30 test.sql

[oracle@ol7-121-dg3 ~]$ cat $filename.log
nohup: ignoring input

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 10 17:30:53 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

OL7-121-DG3:(SYS@hawkc:PRIMARY> exec dbms_output.put_line('test');
test
OL7-121-DG3:(SYS@hawkc:PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol7-121-dg3 ~]$

It’s possible I will adopt screen at one point; however, my preference is still nohup.

Copy Or Move File And Append Date Using Bash

bash version 4.2.46(2)-release

[ggs@db-fs-1 ~]$ echo "$SHELL"
/bin/bash
[ggs@db-fs-1 ~]$

[ggs@db-fs-1 ~]$ echo $BASH_VERSION
4.2.46(2)-release
[ggs@db-fs-1 ~]$

Demo 1:

[ggs@db-fs-1 ~]$ touch foo.log
[ggs@db-fs-1 ~]$ file=foo.log
[ggs@db-fs-1 ~]$
[ggs@db-fs-1 ~]$ ls -l $file*; cp -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 foo.log
‘foo.log’ -> ‘foo.log.20200429’
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 foo.log
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:50 foo.log.20200429
[ggs@db-fs-1 ~]$

Demo 2:

[ggs@db-fs-1 ~]$ touch archive.tar.gz
[ggs@db-fs-1 ~]$ file=archive.tar.gz
[ggs@db-fs-1 ~]$
[ggs@db-fs-1 ~]$ ls -l $file*; cp -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz
‘archive.tar.gz’ -> ‘archive.tar.gz.20200429’
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 archive.tar.gz.20200429
[ggs@db-fs-1 ~]$

Demo 3: Rotate ggserr.log (only required because logrotate is not configured)

ggs@db-fs-1 ggs]$ file=ggserr.log
[ggs@db-fs-1 ggs]$ ls -l $file*; cp -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r----- 1 ggs oinstall 534 Apr 29 06:04 ggserr.log
‘ggserr.log’ -> ‘ggserr.log.20200429’
-rw-r----- 1 ggs oinstall 534 Apr 29 06:04 ggserr.log
-rw-r----- 1 ggs oinstall 534 Apr 29 06:05 ggserr.log.20200429

[ggs@db-fs-1 ggs]$ cat /dev/null > ggserr.log
[ggs@db-fs-1 ggs]$ ls -l $file*
-rw-r----- 1 ggs oinstall   0 Apr 29 06:05 ggserr.log
-rw-r----- 1 ggs oinstall 534 Apr 29 06:05 ggserr.log.20200429
[ggs@db-fs-1 ggs]$

Demo 4:

[ggs@db-fs-1 ~]$ file=archive.tar.gz
[ggs@db-fs-1 ~]$ ls -l $file*; mv -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 archive.tar.gz.20200429
‘archive.tar.gz’ -> ‘archive.tar.gz.20200429’
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz.20200429
[ggs@db-fs-1 ~]$

Why is this even important?

Consistent and mindless effort but need to remember syntax.

Try, Try, And Try Again

I hope you don’t judge a post by its title.

Working with vagrant build and experiencing periodic timed out.

    default: ******************************************************************************
    default: Unzip database software. Sat Feb 29 00:34:50 UTC 2020
    default: ******************************************************************************
    default: ‘/vagrant_software/LINUX.X64_193000_db_home.zip’ -> ‘./LINUX.X64_193000_db_home.zip’
    default: cp: error reading ‘/vagrant_software/LINUX.X64_193000_db_home.zip’: Protocol error
    default: cp: failed to extend ‘./LINUX.X64_193000_db_home.zip’: Protocol error
    default: 1
    default:
    default: real       0m3.468s
    default: user       0m0.000s
    default: sys        0m0.558s
    default: unzip:  cannot find or open LINUX.X64_193000_db_home.zip, LINUX.X64_193000_db_home.zip.zip or LINUX.X64_193000_db_home.zip.ZIP.
    default: 9
    default:
    default: real       0m0.006s
    default: user       0m0.001s
    default: sys        0m0.001s
    default: 0
    default:
    default: real       0m0.001s
    default: user       0m0.000s
    default: sys        0m0.001s
    default: ******************************************************************************
    default: Do database software-only installation. Sat Feb 29 00:34:54 UTC 2020
    default: ******************************************************************************
    default: /vagrant/scripts/oracle_db_software_installation.sh: line 15: /u01/app/oracle/product/19.0.0/dbhome_1/runInstaller: No such file or directory
    default: ******************************************************************************

I have probably brought this upon myself since I Create Windows Symlinks to Vagrant Software Folder

However, issue is that it works from GI and not DB software is what agitates me.

Enough of the rant and there is the resolution.

The while loop will repeat the last command until successful.

echo "******************************************************************************"
echo "Unzip database software." `date`
echo "******************************************************************************"

time cp -fv /vagrant_software/${DB_SOFTWARE} ${ORACLE_BASE}
while [ $? -ne 0 ] ; do fc -s ; done
time unzip -oq ${ORACLE_BASE}/${DB_SOFTWARE} -d ${ORACLE_HOME}
while [ $? -ne 0 ] ; do fc -s ; done
time rm -fv ${ORACLE_BASE}/${DB_SOFTWARE}
while [ $? -ne 0 ] ; do fc -s ; done

Somehow, just magically works.

    default: ******************************************************************************
    default: Unzip database software. Sat Feb 29 01:57:23 UTC 2020
    default: ******************************************************************************
    default: ‘/vagrant_software/LINUX.X64_193000_db_home.zip’ -> ‘/u01/app/oracle/LINUX.X64_193000_db_home.zip’
    default:
    default: real       0m11.455s
    default: user       0m0.008s
    default: sys        0m1.409s
    default:
    default: real       1m50.354s
    default: user       0m46.963s
    default: sys        0m8.285s
    default: removed ‘/u01/app/oracle/LINUX.X64_193000_db_home.zip’
    default:
    default: real       0m0.133s
    default: user       0m0.000s
    default: sys        0m0.128s
    default: ******************************************************************************
    default: Do database software-only installation. Sat Feb 29 01:59:25 UTC 2020
    default: ******************************************************************************
    default: Launching Oracle Database Setup Wizard...

Back to work!

Rsync DBFS To ACFS For GoldenGate Trail Migration

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

Shell Scripting Using set -v

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

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

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
$