Thinking Out Loud

April 27, 2021

RMAN Backup & Restore Review

Filed under: Recovery,RMAN — mdinh @ 10:08 pm

I have seen many backup reviews without any information on Recovery Time Objective (RTO) and Recovery Point Objective (RPO).

The environment I am reviewing has backup to tape only.

Here is the RMAN script I am using. Since I don’t know RTO and RPO, I chose an arbitrary RPO.

--- Did not specified device since it is configured from RMAN
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=';


$ cat /tmp/restore_validate_$ORACLE_SID.out

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 27 12:02:26 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> spool log to restore_validate.log
2> set echo on
3> connect target;
4> show all;
5> list backup by file;
6> restore spfile validate;
7> restore controlfile validate;
8> restore database until time "TRUNC(sysdate)+1/24" validate preview summary;
9> report schema;
10> exit

There are six development databases on the host.

After running restore validate here are pertinent results from mining the log.

The following databases do not look to have successful restore since there is referenced to disk.

What do you think is required from disks for restore?

$ grep "scanning archived log" restore_validate*.log|grep ORA_DISK|awk -F ":" '{print $1}'|sort -u
restore_validate_DEVAAAD.log
restore_validate_DEVAAAQ.log
restore_validate_DEVBBBD.log
restore_validate_DEVBBBQ.log

Here is the crontab entries for archived log backup and the time restore testing was performed.

--- Restore until time TRUNC(sysdate)+1/24 will failed since archived logs are backed up every 12H.

FAILED: 
--- Archived logs backup starts 12:30. Restore test started at 10:35; hence, archived logs on disk is required.
30 0,12 * * * /home/oracle/bin/rman_backup.sh DEVAAAD arch
-rw-r--r--    1 oracle   dba           38478 Apr 27 10:05 restore_validate_DEVAAAD.log

--- Archived logs backup starts 12:55. Restore test started at 12:48; hence, archived logs on disk is required.
55 0,12 * * * /home/oracle/bin/rman_backup.sh DEVAAAQ arch
-rw-r--r--    1 oracle   dba           33440 Apr 26 12:48 restore_validate_DEVAAAQ.log

--- Archived logs backup starts 12:10. Restore test started at 12:02; hence, archived logs on disk is required.
10 0,12 * * * /home/oracle/bin/rman_backup.sh DEVBBBD arch
-rw-r--r--    1 oracle   dba           16152 Apr 26 12:02 restore_validate_DEVBBBD.log

--- Archived logs backup starts 12:10. Restore test started at 11:45; hence, archived logs on disk is required.
10 0,12 * * * /home/oracle/bin/rman_backup.sh DEVBBBQ arch
-rw-r--r--    1 oracle   dba           50474 Apr 22 11:48 restore_validate_DEVBBBQ.log


PASSED:
--- Archived logs backup starts 12:55. Restore test started at 13:22.
55 0,12 * * * /home/oracle/bin/rman_backup.sh DEVAAAU arch
-rw-r--r--    1 oracle   dba           38967 Apr 26 13:22 restore_validate_DEVAAAU.log

--- Archived logs backup starts 12:10. Restore test started at 12:16.
10 0,12 * * * /home/oracle/bin/rman_backup.sh DEVBBBU arch
-rw-r--r--    1 oracle   dba           43777 Apr 27 12:16 restore_validate_DEVBBBU.log

Is it acceptable to lose up to 12H of data?

Do You Tag RMAN Backups?

Filed under: RMAN — mdinh @ 7:53 pm

There were discussions if RMAN backups should be tagged.

Basically, I have been tagging backup as L0, L1, AL for backups to disk or tape only.

Then there are backups to both disk & tape and was planning to tag backups as L0_DISK, L1_DISK, AL_DISK, L0_TAPE, L1_TAPE, AL_TAPE.

I was provided the following info.

Tags can’t be used for this purpose. Tag does not indicate if backup is on disk. Tag should be used if you want to create a specific backup for specific purpose, and use this tag for copy, keep or something like this.

The above recommendations differ from Oracle’s documentation – Backup Tags

Next, I was validating backup using – restore database until time “TRUNC(sysdate)+1/24” validate preview summary;

Here is the result for mining the log and noticed the info for backup pieces.

Does it matter or important to know what the backup piece is?

$ grep "piece handle" restore_validate_$ORACLE_SID.log|grep tag|sort -u
channel ORA_SBT_TAPE_1: piece handle=5avt57n5_1_1 tag=LEVEL0
channel ORA_SBT_TAPE_1: piece handle=76vt7sog_1_1 tag=LEVEL1
channel ORA_SBT_TAPE_1: piece handle=78vt8rmb_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=79vt8rmc_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7avt8rmd_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7bvt8rmg_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7cvt8rmh_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7dvt8rmi_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7evt8rmj_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7fvt8rmk_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7gvt8rml_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7hvt8rmn_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7ivt8rmo_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7jvt8rmp_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7kvt8rmq_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7lvt8rmu_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7mvt8rmv_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7nvt8rn0_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7ovt8rn1_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7qvta5sc_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7rvta5t5_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7svta5t8_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7tvta5tc_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7uvta5te_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7vvta5tg_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=80vta5tk_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=81vta5tm_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=82vta5tp_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=83vta5tr_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=84vta5tv_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=85vta5u3_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=86vta5u7_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=87vta5uc_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=88vta5ug_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=89vta5ui_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8avta5un_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8bvta5ur_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8cvta5vb_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8dvta5vg_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8evta600_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8fvta602_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8gvta605_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8hvta609_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=c-3547077149-20210427-01 tag=TAG20210427T034102

April 24, 2021

RMAN list backup

Filed under: RMAN — mdinh @ 2:01 am

I am used to using TAG when listing backup and today I learned something new.

### Change disk to sbt for device type or remove device type for both disk & sbt.
c/disk/sbt

### Change database with any of the followings:
c/database/archivelog all/archivelog sequence/controlfile/spfile/tablespace/datafile

### Notice how summary is inconsistent.
list backup summary device type=sbt;
list backup summary device type=disk;

list backup of database summary device type=disk;

list backup of database summary completed after 'sysdate-1' device type=disk;
list backup of database summary completed between 'sysdate-1' and 'sysdate' device type=disk;

list backup of tablespace 'SYSTEM' summary device type=disk;
list backup of datafile 1 summary device type=disk;

list backup of archivelog sequence between 46270 and 46274 summary device type=disk;
list backup of archivelog sequence 1 summary device type=disk;

list backup of archivelog all summary completed after 'sysdate-1' device type=disk;
list backup of archivelog all summary completed between 'sysdate-1' and 'sysdate' device type=disk;


[oracle@ol7-112-dg1 ~]$ rman checksyntax @ list.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 24 01:54:10 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> set echo on
2> list backup summary device type=disk;
3> list backup of database summary device type=disk;
4> list backup of database summary completed after 'sysdate-1' device type=disk;
5> list backup of database summary completed between 'sysdate-1' and 'sysdate' device type=disk;
6> list backup of tablespace 'SYSTEM' summary device type=disk;
7> list backup of datafile 1 summary device type=disk;
8> list backup of archivelog sequence between 46270 and 46274 summary device type=disk;
9> list backup of archivelog sequence 1 summary device type=disk;
10> list backup of archivelog all summary completed after 'sysdate-1' device type=disk;
11> list backup of archivelog all summary completed between 'sysdate-1' and 'sysdate' device type=disk;
12> exit
The cmdfile has no syntax errors

Recovery Manager complete.
[oracle@ol7-112-dg1 ~]$

### Remove device to show backup for both disk and sbt;
[oracle@ol7-112-dg1 ~]$ rman checksyntax @ list.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 24 10:54:25 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> set echo on
2> list backup summary;
3> list backup of database summary;
4> list backup of database summary completed after 'sysdate-1';
5> list backup of database summary completed between 'sysdate-1' and 'sysdate';
6> list backup of tablespace 'SYSTEM' summary;
7> list backup of datafile 1 summary;
8> list backup of archivelog sequence between 46270 and 46274 summary;
9> list backup of archivelog sequence 1 summary;
10> list backup of archivelog all summary completed after 'sysdate-1';
11> list backup of archivelog all summary completed between 'sysdate-1' and 'sysdate';
12> exit
The cmdfile has no syntax errors

Recovery Manager complete.
[oracle@ol7-112-dg1 ~]$

If you are interested in using TAG then see post Simplify RMAN Restore With Meaningful Tag

April 18, 2021

Use Different Listener For Standby Duplication?

Filed under: Dataguard,standby — mdinh @ 4:59 pm

I know what you are thinking!

Why make this more complicate than it needs to be and is an ounce of prevention worth a pound of cure?

Oracle support started patching Oracle Exadata Cloud@Customer (ExaCC) environment.

After patching, the listener did not start because there were entries in the listener referencing database and oracle home that have been removed.

There are multiple database homes for the same database versions and this is how it was implemented.

Primary database (DB_ASHBURN) was used to create a second standby (DB_PHOENIX).

The primary database (DB_ASHBURN) was switchover to second standby (DB_PHOENIX)

DB_PHOENIX is now the new primary and DB_ASHBURN is the standby.

DB_ASHBURN (standby) was decommissioned and ORACLE_HOME was removed.

Unfortunately, listener.ora was not modified and failed to start after patching was completed.

Here is an example for LISTENER failed to start.

[oracle@ol7-112-dg1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-APR-2021 16:19:49

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
TNS-01201: Listener cannot find executable /u01/app/oracle/product/11.2.0.4/dbhome_2/bin/oracle for SID DB_PHOENIX
[oracle@ol7-112-dg1 admin]$

---------------------------------------------------------
--- ORACLE_HOME may be in /etc/oratab but does not exist.
---------------------------------------------------------
[oracle@ol7-112-dg1 admin]$ sort -u -t : -k 2,2 /etc/oratab | grep -v "^#" | awk -F ":" '{print $2}'
/u01/app/oracle/product/11.2.0.4/dbhome_1
/u01/app/oracle/product/11.2.0.4/dbhome_2

[oracle@ol7-112-dg1 admin]$ ls -ld /u01/app/oracle/product/11.2.0.4/
drwxr-xr-x. 3 oracle oinstall 22 Apr 14 18:29 /u01/app/oracle/product/11.2.0.4/
[oracle@ol7-112-dg1 admin]$

Having separate listeners, LISTENER started without issues

[oracle@ol7-112-dg1 admin]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-APR-2021 16:32:24

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-112-dg1.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-APR-2021 16:32:25
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "hawk" has 1 instance(s).
  Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
Service "hawk_DGMGRL" has 1 instance(s).
  Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ol7-112-dg1 admin]$

What’s your preference, having separate listeners to play it safe or decommissioning the environment properly and completely?

April 11, 2021

Port Forwarding Using SSH Config File

Filed under: Grid Control,linux — mdinh @ 8:03 pm

Here is a good reference SSH config file for OpenSSH client

From a secured server, I am able to connect to 2 different environments which seems counter intuitive but I digress.

Since there are 2 different environments, the same ~/.ssh/config cannot be used as there may be IP overlap.

One environment will use ~/.ssh/config and ssh emhost

Other environment will use ~/.ssh/cbconfig and ssh -F ~/.ssh/cbconfig emhost

The default EM port for both hosts is 7803.

Using firefox https://localhost:7803/em to access EM does not work well when saving username and password as they will be overwritten.

One solution to save sysman’s password is to use URL with different port.

Hence, config will have EM port 7803 forward to 7803 while cbconfig will have EM port 7803 forward to 7804.

========================================
This is on cloud and looks complicated. 
========================================
I did not create the configuration and don't know how many hours it took.
~/.ssh/config

Host emhost
     HostName 10.157.38.66
     LocalForward 7001 10.157.38.66:7001
     LocalForward 7102 10.157.38.66:7102
     LocalForward 7803 10.157.38.66:7803
     LocalForward 9803 10.157.38.66:9803
     LocalForward 9851 10.157.38.66:9851

# DEFAULTS:
Host *
User dinh


========================================
This is on premise and looks simpler. 
========================================
ssh -F ~/.ssh/cbconfig emhost

Host emhost
     HostName 10.10.72.254
     # Forward port need to use IP address.
     # Equivalent to ssh -L 7804:10.10.72.254:7803 mdinh@10.10.72.254
     LocalForward 7804 10.131.28.227:7803

# DEFAULTS:
Host *
User mdinh

April 8, 2021

Who Can Access

Filed under: Uncategorized — mdinh @ 12:22 am

I had a request to list the users who have read access (or greater) to the APP schema.

Base on the results below:

User DINH has SELECT on APP.INTERVAL table (view)

Role APP_ROLE has SELECT/UPDATE on APP.INTERVAL table (view)

User DINH/APP/SYS has APP_ROLE

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1
SQL> show user
USER is "SYS"
SQL> @priv.sql

SQL> select username from dba_users where created  > (select created from v$database) order by 1;

USERNAME
------------------------------
APP
AUDIT_TEST
DINH
DINH099PD
PDBADMIN
WMS099PD

6 rows selected.

SQL> select * from DBA_TAB_PRIVS where owner='APP';

GRANTEE              OWNER                TABLE_NAME                GRANTOR              PRIVILEGE            GRANTABLE HIERARCHY COM TYPE                     INH
-------------------- -------------------- ------------------------- -------------------- -------------------- --------- --------- --- ------------------------ ---
DINH                 APP                  INTERVAL                  APP                  SELECT               NO        NO        NO  TABLE                    NO
APP_ROLE             APP                  INTERVAL                  APP                  SELECT               NO        NO        NO  TABLE                    NO
APP_ROLE             APP                  INTERVAL                  APP                  UPDATE               NO        NO        NO  TABLE                    NO

SQL> --- ROLE_TAB_PRIVS describes table privileges granted to roles.
SQL> --- Information is provided only about roles to which the user has access.
SQL> select * from ROLE_TAB_PRIVS where OWNER='APP';

ROLE                           OWNER                TABLE_NAME                COLUMN_NAME               PRIVILEGE            GRANTABLE COM INH
------------------------------ -------------------- ------------------------- ------------------------- -------------------- --------- --- ---
APP_ROLE                       APP                  INTERVAL                                            UPDATE               NO        NO  NO
APP_ROLE                       APP                  INTERVAL                                            SELECT               NO        NO  NO

SQL> select * from DBA_ROLE_PRIVS where GRANTED_ROLE='APP_ROLE' order by 1;

GRANTEE              GRANTED_ ADMIN DEL DEFAULT COM INH
-------------------- -------- ----- --- ------- --- ---
APP                  APP_ROLE YES   NO  YES     NO  NO
DINH                 APP_ROLE NO    NO  YES     NO  NO
SYS                  APP_ROLE NO    NO  YES     NO  NO

SQL>

I also used Pete Finnigan’s who_can_access.sql for comparison.

Note who_can_access.sql is per object vs per schema.

If there were hundreds / thousands of table, then not sure how this will scale.

who_can_access: Release 1.0.3.0.0 - Production on Wed Apr 07 19:00:04 2021
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK       [USER_OBJECTS]: INTERVAL
OWNER OF THE OBJECT TO CHECK          [USER]: APP
OUTPUT METHOD Screen/File                [S]:
FILE NAME FOR OUTPUT              [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]:
EXCLUDE CERTAIN USERS                    [N]:
USER TO SKIP                         [TEST%]:

Checking object => APP.INTERVAL
====================================================================


Object type is => TABLE (TAB)
        Privilege => SELECT is granted to =>
        Role => APP_ROLE (ADM = NO) which is granted to =>
                User => DINH (ADM = NO)
                User => SYS (ADM = NO)
                User => APP (ADM = YES)
        User => DINH (ADM = NO)
        Privilege => UPDATE is granted to =>
        Role => APP_ROLE (ADM = NO) which is granted to =>
                User => DINH (ADM = NO)
                User => SYS (ADM = NO)
                User => APP (ADM = YES)

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

Did I do this right?

This is not 100% accurate, missing the followings, and does not take into consideration recursive roles grant:

  1. Column grants: GRANT update (column_name) ON table_name TO user_name;
  2. Code based access control (CBAC)
  3. REFERENCES privileges: GRANT references ON table_name to user_name

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?

Blog at WordPress.com.