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;