Thinking Out Loud

September 1, 2022

Delete Trace Files (“*.tr?”) For 44 ORACLE_HOME

Filed under: linux,oracle,shell scripting — mdinh @ 1:31 am

Recently, I had bloged about https://mdinh.wordpress.com/2022/08/21/find-all-oracle-alert-logs

The same concept can be used to delete tracefiles.

There are 44 ORACLE_HOMES for the ExaCS environment.

A lot of manual work was performed to clean up the enviroment.

Since there are so many trace files generated, trace files older than 7 days are removed to avoid any “Argument list too long” errors

[oracle@host1 ~]$  ps -ef|grep -c [p]mon
44
[oracle@host1 ~]$ rm /u02/app/oracle/diag/rdbms/$DB_UNIQUE_NAME/*/trace/*.tr?
-bash: /bin/rm: Argument list too long
[oracle@host1 ~]$ crontab -l
* * * * * /bin/find /u02/app/oracle/diag/rdbms/*/*/trace -name "*.tr?" -type f -mtime +7 -exec rm -fv {} \; > /tmp/rm_trace.txt 2>&1
[oracle@host1 ~]$ 

June 29, 2022

Improved dgmgrl_validate_srl_all.sh

Filed under: Dataguard,dgmgrl,shell scripting — mdinh @ 9:31 pm

The orginal script was not able to handle multiple standbys.

This is the orginal script.

$ cat old_dgmgrl_validate_srl_all.sh
red='\033[0;31m'
green='\033[0;32m'
nc='\033[0m'
ps -ef|grep ora_[d]mon|sort -k8|awk -F "_" '{print $3}'|grep APEX18 > /tmp/sids.txt
arr=(`grep '^[A-Z].*' < /tmp/sids.txt`)
for i in "${arr[@]}"
do
  echo ======================== $i ========================
  . oraenv <<< $i >/dev/null
  stby=`dgmgrl / "show configuration" | grep "Physical standby" | awk  '{print $1;}'`
  echo $stby
  dgmgrl / "validate database verbose '$stby'" | grep -B4 "SRL"
done
exit

This is the orginal result.

$ ./old_dgmgrl_validate_srl_all.sh
======================== APEX181 ========================
APEX18_PHO APEX18_ASH
$

This is the improved script.

$ cat dgmgrl_validate_srl_all.sh
#!/bin/bash
# Created by Vyacheslav Rasskazov
# Updated by Michael Dinh : Add functionality for multiple standbys
red='\033[0;31m'
green='\033[0;32m'
nc='\033[0m'
ps -ef|grep ora_[d]mon|sort -k8 | awk -F "_" '{print $3}'|grep APEX18 > /tmp/sids.txt
arr=(`grep '^[A-Z].*' < /tmp/sids.txt`)
#set -x
for i in "${arr[@]}"
do
  . oraenv <<< $i >/dev/null
  echo ========= $i ":" $ORACLE_HOME =========
  stby=`dgmgrl / "show configuration" | grep "Physical standby" | awk  '{print $1;}'`
  # Add functionality for multiple standbys
  for x in $stby
  do
    echo "STANDBY DB_UNIQUE_NAME: " $x
    dgmgrl / "validate database verbose '$x'" | grep -B4 "SRL"
  done
done
exit
$

This is the improved result.

$ ./dgmgrl_validate_srl_all.sh     
========= APEX181 : /u02/app/oracle/product/18.0.0.0/dbhome_7 =========
STANDBY DB_UNIQUE_NAME:  APEX18_PHO

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (APEX18_ASHBURN)        (APEX18_PHO)
    1         4                       5                       Sufficient SRLs
    2         4                       5                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (APEX18_PHO)            (APEX18_ASHBURN)
    1         4                       5                       Sufficient SRLs
    2         4                       5                       Sufficient SRLs
STANDBY DB_UNIQUE_NAME:  APEX18_ASH

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (APEX18_ASHBURN)        (APEX18_ASH)
    1         4                       5                       Sufficient SRLs
    2         4                       5                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (APEX18_ASH)            (APEX18_ASHBURN)
    1         4                       5                       Sufficient SRLs
    2         4                       5                       Sufficient SRLs
$

April 14, 2022

Gather Oracle ExaData Cloud Service (ExaCS) Region/AD From Hostname

Filed under: Oracle_Cloud,shell scripting — mdinh @ 12:43 am
Here are all the ExaData hosts without the instance# as it does not make a difference in the results.

### cn is Client Name abbreviation.
cnexaashad2x8na-xxxxx 
cnexaashad3x8na-xxxxx 
cnexafraad2x8m-xxxxx-yyyyy 
cnexalonad2x8m-xxxxx-yyyyy 
cnexaphxad2x8m-xxxxx-yyyyy 
cnexasydad1x8m-xxxxx-yyyyy

### Shell Script to extract info:
kucubiwopeto@pc-3fbecf:~$ cat exacs_name.sh
StringVal="cnexaashad2x8na-xxxxx cnexaashad3x8na-xxxxx cnexafraad2x8m-xxxxx-yyyyy cnexalonad2x8m-xxxxx-yyyyy cnexaphxad2x8m-xxxxx-yyyyy cnexasydad1x8m-xxxxx-yyyyy"
for val in $StringVal; do
  cloud_service=${val:2:3}
  region=${val:5:3}
  availability_domain=${val:8:3}
  echo $cloud_service-$region-$availability_domain
done
exit
kucubiwopeto@pc-3fbecf:~$

### Results:
kucubiwopeto@pc-3fbecf:~$ ./exacs_name.sh
exa-ash-ad2
exa-ash-ad3
exa-fra-ad2
exa-lon-ad2
exa-phx-ad2
exa-syd-ad1
kucubiwopeto@pc-3fbecf:~$

January 22, 2022

SIMPLIFY A COMPLICATED PROCESS USING SED

Filed under: automation,awk_sed_grep,shell scripting — mdinh @ 4:08 am

For every PDB, there is a perl script used to report tablespace free the that PDB.

While I am not able to change how the process was implemented, I can make it easier.

Here is the current process.

Edit the script "tablespace_free_PDB_NAME.pl".

Change the PDB name at the below SQL command:
"alter session set container=<Your PDB name_1>"
 
Rename the script to match your PDB name 
like "tablespace_free_<Your PDB name>.pl".

The above instructions work but is time consuming, not scalable, and error prone.

Here’s a demo how to simplify the process.

1. Create template temp_tablespace_free_PDB.pl:

[oracle@ol7-19-dg1 ~]$ cat temp_tablespace_free_PDB.pl
alter session set container=vPDB;

2. Export variable PDB with <Your PDB name>

[oracle@ol7-19-dg1 ~]$ export PDB=SOAP

3. Create tablespace_free_<Your PDB name>.pl

[oracle@ol7-19-dg1 ~]$ ls tablespace_free_$PDB.pl
ls: cannot access tablespace_free_SOAP.pl: No such file or directory

[oracle@ol7-19-dg1 ~]$ sed "s/vPDB/$PDB/g" temp_tablespace_free_PDB.pl > tablespace_free_$PDB.pl

[oracle@ol7-19-dg1 ~]$ ls tablespace_free_$PDB.pl
tablespace_free_SOAP.pl
[oracle@ol7-19-dg1 ~]$

[oracle@ol7-19-dg1 ~]$ cat tablespace_free_$PDB.pl
alter session set container=SOAP;
[oracle@ol7-19-dg1 ~]$

The above solution is better but far from perfect.

If there are a dozen PDBs to implement, then manual work will have to be done a dozen time.

Here is an example using array and for loop.

There are 2 PDBs: SOAP and SCUM.

Here is the template:

[oracle@ol7-19-dg1 ~]$ cat temp_tablespace_free_PDB.pl
alter session set container=vPDB;
select sysdate from dual;
[oracle@ol7-19-dg1 ~]$

1. Create script to loop through list of PDBs:

[oracle@ol7-19-dg1 ~]$ cat create_tablespace_free_PDB.sh
#!/bin/bash
array=( SOAP SCUM )
for i in "${array[@]}"
do
  echo "$i"
  export PDB=$i
  sed "s/vPDB/$PDB/g" temp_tablespace_free_PDB.pl > tablespace_free_$PDB.pl
  ls -l tablespace_free_$PDB.pl
done
exit
[oracle@ol7-19-dg1 ~]$

2. Run create_tablespace_free_PDB.sh:

[oracle@ol7-19-dg1 ~]$ ./create_tablespace_free_PDB.sh
SOAP
-rw-r--r--. 1 oracle oinstall 60 Jan 22 03:52 tablespace_free_SOAP.pl
SCUM
-rw-r--r--. 1 oracle oinstall 60 Jan 22 03:52 tablespace_free_SCUM.pl
[oracle@ol7-19-dg1 ~]$

3. Review results:

[oracle@ol7-19-dg1 ~]$ cat tablespace_free_SOAP.pl
alter session set container=SOAP;
select sysdate from dual;
[oracle@ol7-19-dg1 ~]$

[oracle@ol7-19-dg1 ~]$ cat tablespace_free_SCUM.pl
alter session set container=SCUM;
select sysdate from dual;
[oracle@ol7-19-dg1 ~]$

January 13, 2022

COMPLICATED COMMINGLED DATABASE ENVIRONMENT

Filed under: awk_sed_grep,linux,RMAN,shell scripting — mdinh @ 11:16 pm

I have been reviewing RMAN RAC backup for environment having a total of 15 non-production and production databases on the same host excluding APX and MGMTDB.

That’s not a big deal, as I have once had to managed 28 databases residing on the same host, right?

I am just too lazy and too tedious to change RMAN configuration one database at a time.

Luckily, there is a convention where non-production instances ends with T1 and production instances ends with P1.

This allows me to make the same changes to non-production and production in 2 steps.

Goal is to configure RMAN PARALLELISM 2 for NON-PROD and PARALLELISM 4 for PROD and consistent RECOVERY WINDOW OF 14 DAYS.

### Current configuration is inconsistent across databases:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;

====================
### NON-PROD: 
====================

--- RMAN commands: cat configure.rman:
set echo on
connect target;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
show all;
exit

--- Let's make sure the instances are correct:
$ ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -Ev '\P1'|sort
DB01T1
DB02T1
DB03T1

--- Make the change:
$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -Ev '\P1'|sort); do echo 'RMAN configure' $db; . oraenv <<< $db; rman @configure.rman; done;

====================
### PROD:
====================

--- RMAN commands: cat configure.rman:
set echo on
connect target;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;
show all;
exit

--- Let's make sure the instances are correct:
$ ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -E '\P1'|sort
DB01P1
DB02P1
DB03P1

--- Make the change:
$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -E '\P1'|sort); do echo 'RMAN configure' $db; . oraenv <<< $db; rman @configure.rman; done;

April 6, 2021

Detect Linux Host Restart

Filed under: awk_sed_grep,linux,shell scripting — mdinh @ 3:15 am

Sometime ago I had blogged about Monitor Linux Host Restart

The simple solution: How to email admins automatically after a Linux server starts?

Here is the example from root’s cron:

# crontab -l
@reboot su oracle -c '/home/oracle/scripts/host_restart_alert.sh' > /tmp/host_restart_alert.out 2>&1

Shell script is used because mail cannot be sent from local host and will need to be sent from remote host.

#!/bin/bash -x
MAILFROM=
MAILTO=
SUBJECT="Node reboot detected for $(hostname)"
EMAILMESSAGE="$(hostname) was restarted `uptime -p| awk -F'up' '{print $2}'` ago at `uptime -s`"

# uptime reports minutely and need to sleep for at least 60s after host restart
sleep 63

ssh oracle@remotehost /bin/bash <<EOF
/home/oracle/scripts/send_email.sh "$EMAILMESSAGE" "$SUBJECT" "$MAILFROM" "$MAILTO"
EOF

exit

Why is there a need to detect host restart and isn’t there monitoring for the host?

This is Oracle Exadata Cloud@Customer (ExaCC) environment.

When Oracle support performs patching, they do not provide any sort of communication or status and monitoring is disable for all hosts beforehand.

OPatchAuto to Patch a GI/RAC Environment.

After the patching is complete and your servers are restarted, you should check your product software to verify that the issue has been resolved.

This is why there is a need to detect and be notified for server restart.

April 4, 2021

Linux Find Week# Of Month

Filed under: linux,shell scripting — mdinh @ 9:02 pm

Unfortunately, Linux does not have parameter for Week Number Of Month

I found the solution at https://serverfault.com/questions/383666/how-to-determine-number-of-week-of-the-month

echo $((($(date +%-d)-1)/7+1))

Here is how I have tested.

[oracle@oracle-12201-vagrant ~]$ date -d '20210404'
Sun Apr  4 00:00:00 -05 2021

[oracle@oracle-12201-vagrant ~]$ echo $((($(date -d '20210404' +%-d)-1)/7+1))
1

[oracle@oracle-12201-vagrant ~]$ date -d '20210411'
Sun Apr 11 00:00:00 -05 2021

[oracle@oracle-12201-vagrant ~]$ echo $((($(date -d '20210411' +%-d)-1)/7+1))
2

[oracle@oracle-12201-vagrant ~]$ date -d '20210418'
Sun Apr 18 00:00:00 -05 2021

[oracle@oracle-12201-vagrant ~]$ echo $((($(date -d '20210418' +%-d)-1)/7+1))
3

[oracle@oracle-12201-vagrant ~]$ date -d '20210425'
Sun Apr 25 00:00:00 -05 2021

[oracle@oracle-12201-vagrant ~]$ echo $((($(date -d '20210425' +%-d)-1)/7+1))
4

Why is this even useful?

It can be a continuation of Simplify Log Management For Backup

April 3, 2021

Simplify Log Management For Backup

Filed under: linux,RMAN,shell scripting — mdinh @ 6:39 pm

Currently, there is a cronjob to delete backup logs older than 7 days.

30 23 * * * find /home/oracle/scripts/logs -name "*.log" -mtime +7 -exec rm {} \;

Typically, it not’s a big deal; however, having to update crontab with 50 entries can be cumbersome when not necesary.

$ crontab -l|wc -l
50

Furthermore, there are 1,044 logs accumulated in the directory.

$ ls -l /home/oracle/scripts/logs/*.log|wc -l
1044

Here is an example for level0 / level1 backup log and seems redundant to have timestamp as part of log name.

$ ls -lt backup_$ORACLE_SID_level0*.log
-rw-r--r-- 1 oracle oinstall 1004854 Apr  3 07:04 backup_$ORACLE_SID_level0_sbt_202104030700_Sat.log
-rw-r--r-- 1 oracle oinstall  839713 Mar 28 05:08 backup_$ORACLE_SID_level0_202103280500_Sun.log
-rw-r--r-- 1 oracle oinstall 1292709 Mar 27 07:04 backup_$ORACLE_SID_level0_sbt_202103270700_Sat.log

$ ls -lt backup_$ORACLE_SID_level1*.log
-rw-r--r-- 1 oracle oinstall   31694 Apr  3 05:11 backup_$ORACLE_SID_level1_202104030510_Sat.log
-rw-r--r-- 1 oracle oinstall  801491 Apr  2 07:33 backup_$ORACLE_SID_level1_sbt_202104020730_Fri.log
-rw-r--r-- 1 oracle oinstall   31711 Apr  2 05:11 backup_$ORACLE_SID_level1_202104020510_Fri.log
-rw-r--r-- 1 oracle oinstall  767509 Apr  1 07:33 backup_$ORACLE_SID_level1_sbt_202104010730_Thu.log
-rw-r--r-- 1 oracle oinstall   31587 Apr  1 05:11 backup_$ORACLE_SID_level1_202104010510_Thu.log
-rw-r--r-- 1 oracle oinstall  733961 Mar 31 07:32 backup_$ORACLE_SID_level1_sbt_202103310730_Wed.log
-rw-r--r-- 1 oracle oinstall   32797 Mar 31 05:11 backup_$ORACLE_SID_level1_202103310510_Wed.log
-rw-r--r-- 1 oracle oinstall  700145 Mar 30 07:32 backup_$ORACLE_SID_level1_sbt_202103300730_Tue.log
-rw-r--r-- 1 oracle oinstall   31591 Mar 30 05:11 backup_$ORACLE_SID_level1_202103300510_Tue.log
-rw-r--r-- 1 oracle oinstall  666291 Mar 29 07:32 backup_$ORACLE_SID_level1_sbt_202103290730_Mon.log
-rw-r--r-- 1 oracle oinstall   31731 Mar 29 05:11 backup_$ORACLE_SID_level1_202103290510_Mon.log
-rw-r--r-- 1 oracle oinstall  631891 Mar 28 07:32 backup_$ORACLE_SID_level1_sbt_202103280730_Sun.log
-rw-r--r-- 1 oracle oinstall   32925 Mar 27 05:11 backup_$ORACLE_SID_level1_202103270510_Sat.log
-rw-r--r-- 1 oracle oinstall 1091718 Mar 26 07:33 backup_$ORACLE_SID_level1_sbt_202103260730_Fri.log
-rw-r--r-- 1 oracle oinstall   31523 Mar 26 05:11 backup_$ORACLE_SID_level1_202103260510_Fri.log

Here’s how to improve logging for level0 and level1 backup using date function.

This will keep backup level0 and level1 logs for 7 days.
%a - locale's abbreviated weekday name (e.g., Sun)

$ echo "backup_${ORACLE_SID}_level0_$(date +%a).log"
backup_ORCLCDB_level0_Sat.log

$ echo "backup_${ORACLE_SID}_level0_sbt_$(date +%a).log"
backup_ORCLCDB_level0_sbt_Sat.log

$ echo "backup_${ORACLE_SID}_level1_$(date +%a).log"
backup_ORCLCDB_level1_Sat.log

$ echo "backup_${ORACLE_SID}_level1_sbt_$(date +%a).log"
backup_ORCLCDB_level1_sbt_Sat.log

Basically, the log will be overwritten on a weekly basis.

What will happen Level0 backup failed on the weekend and is performed on Monday?

It’s a one off and will you lose sleep over it?

Next, there are 204 logs for archived log backup.

$ ls -lt backup_$ORACLE_SID_arch*.log|wc -l
204

$ ls -lt backup_$ORACLE_SID_arch*.log|tail
-rw-r--r-- 1 oracle oinstall 14104 Mar 26 08:51 backup_$ORACLE_SID_arch_202103260850_Fri.log
-rw-r--r-- 1 oracle oinstall 14103 Mar 26 07:51 backup_$ORACLE_SID_arch_202103260750_Fri.log
-rw-r--r-- 1 oracle oinstall 14108 Mar 26 06:51 backup_$ORACLE_SID_arch_202103260650_Fri.log
-rw-r--r-- 1 oracle oinstall 13081 Mar 26 05:51 backup_$ORACLE_SID_arch_202103260550_Fri.log
-rw-r--r-- 1 oracle oinstall 14109 Mar 26 04:51 backup_$ORACLE_SID_arch_202103260450_Fri.log
-rw-r--r-- 1 oracle oinstall 14104 Mar 26 03:51 backup_$ORACLE_SID_arch_202103260350_Fri.log
-rw-r--r-- 1 oracle oinstall 14108 Mar 26 02:51 backup_$ORACLE_SID_arch_202103260250_Fri.log
-rw-r--r-- 1 oracle oinstall 14104 Mar 26 01:51 backup_$ORACLE_SID_arch_202103260150_Fri.log
-rw-r--r-- 1 oracle oinstall 14108 Mar 26 00:51 backup_$ORACLE_SID_arch_202103260050_Fri.log
-rw-r--r-- 1 oracle oinstall 14104 Mar 25 23:51 backup_$ORACLE_SID_arch_202103252350_Thu.log

$ ls -lt backup_WEUMIX1_arch*.log|head
-rw-r--r-- 1 oracle oinstall 14094 Apr  3 10:51 backup_$ORACLE_SID_arch_202104031050_Sat.log
-rw-r--r-- 1 oracle oinstall 14091 Apr  3 09:50 backup_$ORACLE_SID_arch_202104030950_Sat.log
-rw-r--r-- 1 oracle oinstall 14092 Apr  3 08:51 backup_$ORACLE_SID_arch_202104030850_Sat.log
-rw-r--r-- 1 oracle oinstall 14096 Apr  3 07:51 backup_$ORACLE_SID_arch_202104030750_Sat.log
-rw-r--r-- 1 oracle oinstall 14100 Apr  3 06:51 backup_$ORACLE_SID_arch_202104030650_Sat.log
-rw-r--r-- 1 oracle oinstall 13631 Apr  3 05:51 backup_$ORACLE_SID_arch_202104030550_Sat.log
-rw-r--r-- 1 oracle oinstall 14082 Apr  3 04:51 backup_$ORACLE_SID_arch_202104030450_Sat.log
-rw-r--r-- 1 oracle oinstall 14084 Apr  3 03:51 backup_$ORACLE_SID_arch_202104030350_Sat.log
-rw-r--r-- 1 oracle oinstall 14104 Apr  3 02:51 backup_$ORACLE_SID_arch_202104030250_Sat.log
-rw-r--r-- 1 oracle oinstall 14093 Apr  3 01:51 backup_$ORACLE_SID_arch_202104030150_Sat.log

Here’s how to improve logging for archived log using date function.

This will keep backup archived logs for 7 days.
%H - hour (00..23)

$ echo "backup_${ORACLE_SID}_arch_$(date +%a"_H"%H).log"
backup_ORCLCDB_arch_Sat_H11.log

What if 2 versions of log are required? Rename current log file and append .1 before creating new one.

What if 3 versions of log are required? Hmm?

How can there be 3 versions for level0 and level1 backup logs when backup logs older than 7 days are deleted?

February 16, 2021

Monitor Linux Host Restart

Filed under: awk_sed_grep,shell scripting — mdinh @ 8:13 pm

The application is not RAC-aware and cannot handle ORA-3113, ORA-25402, or ORA-25409 properly.

Hence, there is requirement to notify the application team to restart the application when database server is restarted.

Initial implementation to monitor reboot was to use cronjob from oracle running every 5m to detect server restart.

While the implementation is effective, it’s not efficient. This was my first attempt.

The script detects if server was restarted X seconds ago by checking /proc/uptime.

If uptime is less than X seconds, then send notification server was restarted.

Here is high level example:

### Scripts accept paramenter with values for seconds
$ /home/oracle/scripts/last_reboot.sh
/home/oracle/scripts/last_reboot.sh: line 10: 1: ---> USAGE: /home/oracle/scripts/last_reboot.sh [in seconds]

### The heart of the script is to check /proc/uptime in seconds
$ egrep -o '^[0-9]+' /proc/uptime
2132607

### Scheduled cron tab to run every 5 minute to determine if server uptime is less that 540 seconds and send notification.
$ crontab -l|grep reboot
##### monitor node reboot #####
*/5 * * * * /home/oracle/scripts/last_reboot.sh 540 > /tmp/last_reboot.cron 2>&1

A more efficient implementation is to run a cronjob automatically after the server restart.

Here is high level example:

### When server is restarted, host_restart_alert.sh will be executed
[root@oracle-12201-vagrant ~]# crontab -l
@reboot su oracle -c '/home/oracle/host_restart_alert.sh' > /tmp/host_restart_alert.out 2>&1

### Here is host_restart_alert.sh
[oracle@oracle-12201-vagrant ~]$ cat host_restart_alert.sh
#!/bin/bash -x
# Script ie being called from root crontab
# uptime reports minutely and need to sleep for at least 60s after host restart
sleep 63
EMAILMESSAGE="$(hostname) was restarted `uptime -p| awk -F'up' '{print $2}'` ago at `uptime -s`"
echo $EMAILMESSAGE > /tmp/restart_$HOSTNAME.log
exit

### Comment from colleague:
### From a bash syntax perspective, it’s not wrong. It’s not great style (don’t use backticks)
printf -v EMAILMESSAGE '%s was restarted %s ago at %s' \
"$(hostname)" \
"$(uptime -p| awk -F'up' '{print $2}')" \
"$(uptime -s)"
echo $EMAILMESSAGE > /tmp/restart_$HOSTNAME.log

### Deconstructing uptime commands:
[oracle@oracle-12201-vagrant ~]$ uptime -p
up 17 hours, 28 minutes

[oracle@oracle-12201-vagrant ~]$ uptime -s
2021-02-15 18:00:51

### Deconstructing message sent:
[oracle@oracle-12201-vagrant ~]$ echo "$HOSTNAME was restarted `uptime -p| awk -F'up' '{print $2}'` ago at `uptime -s`"
oracle-12201-vagrant was restarted  17 hours, 28 minutes ago at 2021-02-15 18:00:51

### Demo:
[root@oracle-12201-vagrant ~]# date
 Tue Feb 16 14:51:18 -05 2021

[root@oracle-12201-vagrant ~]# uptime
  14:51:22 up 1 min,  1 user,  load average: 0.58, 0.23, 0.08

[root@oracle-12201-vagrant ~]# ls -l /tmp/restart
 -rw-r--r--. 1 root   root     271 Feb 16 14:51 /tmp/host_restart_alert.out
 -rw-r--r--. 1 oracle oinstall  71 Feb 16 14:51 /tmp/restart_oracle-12201-vagrant.log

[root@oracle-12201-vagrant ~]# cat /tmp/host_restart_alert.out
 sleep 63
 ++ hostname
 ++ uptime -p
 ++ awk -Fup '{print $2}'
 ++ uptime -s
 printf -v EMAILMESSAGE '%s was restarted %s ago at %s' oracle-12201-vagrant ' 1 minute' '2021-02-16 14:50:02'
 echo oracle-12201-vagrant was restarted 1 minute ago at 2021-02-16 14:50:02
 exit 

[root@oracle-12201-vagrant ~]# cat /tmp/restart_oracle-12201-vagrant.log
 oracle-12201-vagrant was restarted 1 minute ago at 2021-02-16 14:50:02
[root@oracle-12201-vagrant ~]#

Scripts were tested on Oracle Linux Server release 7.8  and 7.9.

May 10, 2020

nohup vs screen

Filed under: linux,shell scripting — mdinh @ 8:12 pm

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.

Next Page »

Create a free website or blog at WordPress.com.