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

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

SIMPLIFY A COMPLICATED PROCESS USING SED

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

COMPLICATED COMMINGLED DATABASE ENVIRONMENT

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;

Detect Linux Host Restart

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.

Linux Find Week# Of Month

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

Simplify Log Management For Backup

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?

Monitor Linux Host Restart

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.

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!