RMAN refresh incremental backup

Below are the steps to perform a Rolling forward for standby database using RMAN incremental backup when primary and standby are not in sync and archives are deleted before they have been applied it on standby database.

– On the standby database, stop the Managed Recovery Process (MRP)
SQL> alter database recover managed standby database cancel;

– On the standby database, find the SCN which will be used for incremental backup at the primary database.
SQL> select current_scn from v$database;

– Connect to the primary database using RMAN and create an incremental backup from the SCN derived in the previous step.
$ export ORACLE_SID=primary
$ rman target /

RMAN> backup incremental from scn ‘scn_no’ database format ‘/bkp/standby_%U’;

– Take the backup of control file on primary database.
SQL> alter database create standby controlfile as ‘/bkp/control.ctl’;

– Transfer all backup sets and control file to standby Server.
$ scp /bkp/*

– Shutdown the standby database.
SQL> shutdown immediate;

– Take the backup of controlfiles on standby database.
$ mv control01.ctl control01.ctl_old
$ mv control02.ctl control02.ctl_old
$ mv control03.ctl control03.ctl_old

– Now copy the new controlfile in the controlfile location.
$ cd /restore
$ cp control.ctl /d01/primary/controlfile/control01.ctl
$ cp control.ctl /d01/primary/controlfile/control02.ctl
$ cp control.ctl /d01/primary/controlfile/control03.ctl

– Mount the standby database using backup controlfile.
SQL> startup nomount
SQL> alter database mount standby database;

– Catalog the backupieces which you have copied from primary server to standby server using below command on standby server.

$ rman target /
RMAN> catalog start with ‘/restore’;
searching for all files that match the pattern /restore/standby_
List of Files Unknown to the Database
File Name: /restore/standby_0jnha03l_1_1
File Name: /restore/standby_0inha000_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
File Name: /restore/standby_0jnha03l_1_1
File Name: /restore/standby_0inha000_1_1

– Restore any newly created datafiles.
If you have created any new datafile, manually scp that particular datafile to standby server at appropriate location.

– Recover the standby database with the cataloged incremental backup.

– Now check whether the standby database is properly synched or not using the below queries.
– On Primary database
SQL> select max(sequence#) from v$archived_log;

– On Standby database
SQL> select max(sequence#) from v$log_history;

– On the standby database, start the MRP process.
SQL> alter database recover managed standby database disconnect from session;

That’s it, the standby database is again in sync with primary database.


Recover Standby Database Using RMAN Incremental Backup !!!


RMAN Duplicate with compression

RMAN does not de-encrypt TDE data when backing it up. When using column encryption, these columns as well as other data in the tables are encrypted again during the backup. If you are using tablespace encryption these are not further encrypted during backups. If you have RMAN encryption turned off but are using tablespace encryption on the database then the functionality is the same. RMAN can just back them up as they are without any decryption/re-encryption overhead. Wallet is not needed for this case.

However it is different for the compressed backup case. Encrypted blocks when fed into the compression algorithm directly will not benefit from compression because of the random characteristic of ciphertext. Hence, for compressed backup on an encrypted tablespace, RMAN must decrypt all the blocks, compress them, and encrypt them using RMAN encryption. In order for RMAN to complete this task, the wallet must be available.

In short, for compressed backups of encrypted backups the wallet must be available for backup and restore of the target. The wallet must also exist on the auxiliary if such backup is used by the RMAN duplicate. If the wallet is not available, rman will return errors like ORA-19913: unable to decrypt backup and/or ORA-28365: wallet is not open.

The above relates only to restore. Recovery is a bit more complicated command. For encrypted tablespaces without compression, even though the RESTORE itself doesn’t need the wallet to decrypt/re-encrypt the blocks, the RECOVER step would need the wallet to selectively apply archived logs to encrypted blocks (i.e. bring the encrypted blocks to current). Therefore configuring wallet on the AUX instance will be necessary for DUPLICATE, as it restores and recovers the clone created. The difference is not only that recovery is needed, also the backup can be different … for example on active duplication, instead of a backup, RMAN will do a copy then compression will not be in place.

Using auto-login wallet is the simplest solution. However, setting ENCRYPTION_WALLET_LOCATION to the wallet path in the sqlnet.ora is another option. This is for the TARGET during backups and restores but also for the AUXILIARY instance in case of a DUPLICATE.



For RMAN to restore the encrypted backups, we need to open the wallet so that the encrypted data can be decrypted. Usually, a wallet is opened by running below ALTER SYSTEM command:

(In below example, the wallet password that would specified when you setup TDE on the database where the backup is taken)


If you are performing a manual restore/recover in RMAN instead of RMAN Duplicate, it is sufficient to open the wallet as above on the instance where restore/recovery is being performed (If restore is being performed on another server, you need to copy the wallet file as per steps 1 and 2 below before opening the wallet).

However above approach will not work with RMAN DUPLICATE since during the course of duplicate, the clone database instance is shutdown and startup multiple times by RMAN and it is not in our control to open the wallet after each instance startup. So, for rman duplicate, we have to follow another approach i.e. we need to make the wallet as AUTO_LOGIN so that we need not explicitly open the wallet each time the instance is started. When a wallet is made AUTO_LOGIN, it is opened automatically by Oracle whenever encrpytion/decryption is needed.

Below are the detailed steps to configure AUTO_LOGIN wallet:

1. Copy the wallet file (ewallet.p12) from source database server to clone database server. You can check the wallet file location on source database from sqlnet.ora file of the source database ORACLE_HOME

$ scp oracle@prod-serv:/<target path>/ewallet.p12 /<auxiliary path>/.

2. Modify sqlnet.ora file in clone database ORACLE_HOME to reflect the location of the wallet file:

(DIRECTORY = /<auxiliary path>)

3. Invoke orapki utility on the clone database server to make the wallet auto-login:

$ orapki wallet create -wallet /<auxiliary path>/ -pwd “<wallet password>" -auto_login

In above example:
/<auxiliary path>/ is the location on clone server when the source wallet file was copied the wallet password that would have been specified when you first setup TDE on source

4. If above step is successful, you should find a new file is created in the same directory: cwallet.sso

5. Now, STARTUP NOMOUNT the auxiliary instance and try the duplicate command.


+ If you are performing the duplication on the same server and the clone database will use the same ORACLE_HOME as the source database, skip step 1 and 2 above.

+ If you are already using AUTO_LOGIN wallet on the source database, you will already have ewallet.p12 and cwallet.sso files on the source. In this case, do not copy cwallet.sso file to auxiliary server. Copy only the ewallet.p12 file to auxiliary and run Step 3 above to create cwallet.sso file explicitly for the auxiliary server.

+ In above


Linux TOP

TOP – A Best Tool to Monitor Linux System Performance

Atop – Monitor real time system performance, resources, process & check resource utilization history

How To Monitor Disk I/O Activity Using iotop And iostat Commands In Linux?

Netutils-Linux : A Set Of Tools To Simplify Linux Network Troubleshooting And Performance Tuning

Gtop – Awesome Graphical System Monitoring Dashboard For Terminal

Cockpit – An Easy Way to Administer Multiple Remote Linux Servers via a Web Browser

rtop – A Nifty Tool to Monitor Remote Server Over SSH

nload – Monitor Network Traffic And Bandwidth Usage In Real Time

CoreFreq – A Powerful CPU monitoring Tool for Linux Systems

bmon – Real Time Bandwidth Monitor and Rate Estimator in Linux


nmon – A Nifty Tool To Monitor System Resources On Linux

vnStat – A lightweight (Command Line) Network Traffic Monitoring Tool







Linux Monitoring Scripts

# vi /opt/scripts/os-log-alert.sh
#Set the variable which equal to zero
count=$(grep -i “`date –date=’yesterday’ ‘+%b %e’`" /var/log/messages | egrep -wi ‘warning|error|critical’ | wc -l)
if [ “$prev_count" -lt “$count" ] ; then
# Send a mail to given email id when errors found in log
SUBJECT="WARNING: Errors found in log on “`date –date=’yesterday’ ‘+%b %e’`""
# This is a temp file, which is created to store the email message.
echo “ATTENTION: Errors are found in /var/log/messages. Please Check with Linux admin." >> $MESSAGE
echo “Hostname: `hostname`" >> $MESSAGE
echo -e “\n" >> $MESSAGE
echo “+————————————————————————————+" >> $MESSAGE
echo “Error messages in the log file as below" >> $MESSAGE
echo “+————————————————————————————+" >> $MESSAGE
grep -i “`date –date=’yesterday’ ‘+%b %e’`" /var/log/messages | awk ‘{ $3=""; print}’ | egrep -wi ‘warning|error|critical’ >> $MESSAGE
mail -s “$SUBJECT" “$TO" < $MESSAGE

# crontab -e
0 7 * * * /bin/bash /opt/scripts/os-log-alert.sh
ATTENTION: Errors are found in /var/log/messages. Please Check with Linux admin.
Error messages in the log file as below
Jul 3 02:40:11 ns1 kernel: php-fpm[3175]: segfault at 299 ip 000055dfe7cc7e25 sp 00007ffd799d7d38 error 4 in php-fpm[55dfe7a89000+3a7000]
Jul 3 02:50:14 ns1 kernel: lmtp[8249]: segfault at 20 ip 00007f9cc05295e4 sp 00007ffc57bca1a0 error 4 in libdovecot-storage.so.0.0.0[7f9cc04df000+148000]
Jul 3 15:36:09 ns1 kernel: php-fpm[17846]: segfault at 299 ip 000055dfe7cc7e25 sp 00007ffd799d7d38 error 4 in php-fpm[55dfe7a89000+3a7000]
Jul 3 15:45:54 ns1 pure-ftpd: (?@ [WARNING] Authentication failed for user [daygeek]
Jul 3 16:25:36 ns1 pure-ftpd: (?@ [WARNING] Sorry, cleartext sessions and weak ciphers are not accepted on this server.#012Please reconnect using TLS security mechanisms.
Jul 3 16:44:20 ns1 kernel: php-fpm[8979]: segfault at 299 ip 000055dfe7cc7e25 sp 00007ffd799d7d38 error 4 in php-fpm[55dfe7a89000+3a7000]

Bash Script to Monitor Messages Log (Warning, Error and Critical) on Linux

*/5 * * * * /usr/bin/cat /proc/loadavg | awk ‘{print $1}’ | awk ‘{ if($1 > 80) printf(“Current CPU Utilization is: %.2f%\n"), $0;}’ | mail -s “High CPU Alert" daygeek@gmail.com

# vi /opt/scripts/cpu-alert.sh
cpuuse=$(cat /proc/loadavg | awk ‘{print $1}’)
if [ “$cpuuse" > 80 ]; then
SUBJECT="ATTENTION: CPU Load Is High on $(hostname) at $(date)"
echo “CPU Current Usage is: $cpuuse%" >> $MESSAGE
echo “" >> $MESSAGE
echo “+——————————————————————+" >> $MESSAGE
echo “Top CPU Process Using top command" >> $MESSAGE
echo “+——————————————————————+" >> $MESSAGE
echo “$(top -bn1 | head -20)" >> $MESSAGE
echo “" >> $MESSAGE
echo “+——————————————————————+" >> $MESSAGE
echo “Top CPU Process Using ps command" >> $MESSAGE
echo “+——————————————————————+" >> $MESSAGE
echo “$(ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10)" >> $MESSAGE
mail -s “$SUBJECT" “$TO" < $MESSAGE
rm /tmp/Mail.out

# crontab -e
*/10 * * * * /bin/bash /opt/scripts/cpu-alert.sh

CPU Current Usage is: 80.51%

Top CPU Process Using top command
top – 13:23:01 up 1:43, 1 user, load average: 2.58, 2.58, 1.51
Tasks: 306 total, 3 running, 303 sleeping, 0 stopped, 0 zombie
%Cpu0 : 6.2 us, 6.2 sy, 0.0 ni, 87.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 18.8 us, 0.0 sy, 0.0 ni, 81.2 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu2 : 50.0 us, 37.5 sy, 0.0 ni, 12.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu3 : 5.9 us, 5.9 sy, 0.0 ni, 88.2 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu4 : 0.0 us, 5.9 sy, 0.0 ni, 94.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu5 : 29.4 us, 23.5 sy, 0.0 ni, 47.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu6 : 0.0 us, 5.9 sy, 0.0 ni, 94.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu7 : 5.9 us, 0.0 sy, 0.0 ni, 94.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 16248588 total, 223436 free, 5816924 used, 10208228 buff/cache
KiB Swap: 17873388 total, 17871340 free, 2048 used. 7440884 avail Mem

8867 daygeek 20 2743884 440420 360952 R 100.0 2.7 1:07.25 /usr/lib/virtualbox/VirtualBoxVM –comment CentOS7 –startvm 002f47b8-2af2-48f5-be1d-67b67e03514c –no-startvm-errormsgbox
9119 daygeek 20 36136 784 R 46.7 0.0 0:00.07 /usr/bin/CROND -n
1057 daygeek 20 889808 487692 461692 S 13.3 3.0 4:21.12 /usr/lib/Xorg vt2 -displayfd 3 -auth /run/user/1000/gdm/Xauthority -nolisten tcp -background none -noreset -keeptty -verbose 3
3098 daygeek 20 1929012 351412 120532 S 13.3 2.2 16:42.51 /usr/lib/firefox/firefox -contentproc -childID 6 -isForBrowser -prefsLen 9236 -prefMapSize 184485 -parentBuildID 20190521202118 -greomni /us+
1 root 20 188820 10144 7708 S 6.7 0.1 0:06.92 /sbin/init
818 gdm 20 199836 25120 15876 S 6.7 0.2 0:01.85 /usr/lib/Xorg vt1 -displayfd 3 -auth /run/user/120/gdm/Xauthority -nolisten tcp -background none -noreset -keeptty -verbose 3
1170 daygeek 9 -11 2676516 16516 12520 S 6.7 0.1 1:28.30 /usr/bin/pulseaudio –daemonize=no
8271 root 20 I 6.7 0:00.21 [kworker/u16:4-i915]
9117 daygeek 20 13528 4036 3144 R 6.7 0.0 0:00.01 top -bn1

Top CPU Process Using ps command
8.8 8522 daygeek /usr/lib/virtualbox/VirtualBox
86.2 8867 daygeek /usr/lib/virtualbox/VirtualBoxVM –comment CentOS7 –startvm 002f47b8-2af2-48f5-be1d-67b67e03514c –no-startvm-errormsgbox
76.1 8921 daygeek /usr/lib/virtualbox/VirtualBoxVM –comment Ubuntu-18.04 –startvm e8c32dbb-8b01-41b0-977a-bf28b9db1117 –no-startvm-errormsgbox
5.5 8080 daygeek /usr/bin/nautilus –gapplication-service
4.7 4575 daygeek /usr/lib/firefox/firefox -contentproc -childID 12 -isForBrowser -prefsLen 9375 -prefMapSize 184485 -parentBuildID 20190521202118 -greomni /usr/lib/firefox/omni.ja -appomni /usr/lib/firefox/browser/omni.ja -appdir /usr/lib/firefox/browser 1525 true tab
4.4 3511 daygeek /usr/lib/firefox/firefox -contentproc -childID 8 -isForBrowser -prefsLen 9308 -prefMapSize 184485 -parentBuildID 20190521202118 -greomni /usr/lib/firefox/omni.ja -appomni /usr/lib/firefox/browser/omni.ja -appdir /usr/lib/firefox/browser 1525 true tab
4.4 3190 daygeek /usr/lib/firefox/firefox -contentproc -childID 7 -isForBrowser -prefsLen 9237 -prefMapSize 184485 -parentBuildID 20190521202118 -greomni /usr/lib/firefox/omni.ja -appomni /usr/lib/firefox/browser/omni.ja -appdir /usr/lib/firefox/browser 1525 true tab
4.4 1612 daygeek /usr/lib/firefox/firefox -contentproc -childID 1 -isForBrowser -prefsLen 1 -prefMapSize 184485 -parentBuildID 20190521202118 -greomni /usr/lib/firefox/omni.ja -appomni /usr/lib/firefox/browser/omni.ja -appdir /usr/lib/firefox/browser 1525 true tab
4.2 3565 daygeek /usr/bin/../lib/notepadqq/notepadqq-bin

Linux Shell Script To Monitor CPU Utilization And Send Email

*/5 * * * * /usr/bin/free | awk ‘/Mem/{printf(“RAM Usage: %.2f%\n"), $3/$2*100}’ | awk ‘{print $3}’ | awk ‘{ if($1 > 80) print $0;}’ | mail -s “High Memory Alert" 2daygeek@gmail.com

# vi /opt/scripts/memory-alert.sh
ramusage=$(free | awk ‘/Mem/{printf(“RAM Usage: %.2f\n"), $3/$2*100}’| awk ‘{print $3}’)
if [ “$ramusage" > 20 ]; then
SUBJECT="ATTENTION: Memory Utilization is High on $(hostname) at $(date)"
echo “Memory Current Usage is: $ramusage%" >> $MESSAGE
echo “" >> $MESSAGE
echo “——————————————————————" >> $MESSAGE
echo “Top Memory Consuming Process Using top command" >> $MESSAGE
echo “——————————————————————" >> $MESSAGE
echo “$(top -b -o +%MEM | head -n 20)" >> $MESSAGE
echo “" >> $MESSAGE
echo “——————————————————————" >> $MESSAGE
echo “Top Memory Consuming Process Using ps command" >> $MESSAGE
echo “——————————————————————" >> $MESSAGE
echo “$(ps -eo pid,ppid,%mem,%cpu,cmd –sort=-%mem | head)" >> $MESSAGE
mail -s “$SUBJECT" “$TO" < $MESSAGE
rm /tmp/Mail.out

# crontab -e
*/5 * * * * /bin/bash /opt/scripts/memory-alert.sh

Bash Script to Monitor Memory Usage on Linux

# vi /opt/script/disk-usage-alert.sh

df -Ph | grep -vE ‘^Filesystem|tmpfs|cdrom’ | awk ‘{ print $5,$1 }’ | while read output;
echo $output
used=$(echo $output | awk ‘{print $1}’ | sed s/%//g)
partition=$(echo $output | awk ‘{print $2}’)
if [ $used -ge 60 ]; then
echo “The partition \"$partition\" on $(hostname) has used $used% at $(date)" | mail -s “Disk Space Alert: $used% Used On $(hostname)" 2daygeek@gmail.com

# crontab -e
*/10 * * * * /bin/bash /opt/script/disk-usage-alert.sh

# vi /opt/script/disk-usage-alert-1.sh

df -Ph | grep -vE ‘^Filesystem|tmpfs|cdrom’ | awk ‘{ print $5,$1 }’ | while read output;
echo $output
used=$(echo $output | awk ‘{print $1}’)
partition=$(echo $output | awk ‘{print $2}’)
if [ ${used%?} -ge ${max%?} ]; then
echo “The partition \"$partition\" on $(hostname) has used $used at $(date)" | mail -s “Disk Space Alert: $used Used On $(hostname)" 2daygeek@gmail.com

*/10 * * * * df -Ph | sed s/%//g | awk ‘{ if($5 > 60) print $0;}’ | mail -s “Disk Space Alert On $(hostname)" 2daygeek@gmail.com

# vi /opt/script/disk-usage-alert-2.sh

used=$(df -Ph | grep ‘/dev/mapper/vg_2g-lv_dbs’ | awk {‘print $5’})
if [ ${used%?} -ge ${max%?} ]; then
echo “The Mount Point “/DB" on $(hostname) has used $used at $(date)" | mail -s “Disk space alert on $(hostname): $used used" 2daygeek@gmail.com

Linux Shell Script To Monitor Disk Space Usage And Send Email

ASM mapping

# vi asm_disk_mapping.sh
ls -lh /dev/oracleasm/disks > /tmp/asmdisks1.txt
for ASMdisk in `cat /tmp/asmdisks1.txt | tail -n +2 | awk ‘{print $10}’`
minor=$(grep -i “$ASMdisk" /tmp/asmdisks1.txt | awk ‘{print $6}’)
major=$(grep -i “$ASMdisk" /tmp/asmdisks1.txt | awk ‘{print $5}’ | cut -d"," -f1)
phy_disk=$(ls -l /dev/* | grep ^b | grep “$major, *$minor" | awk ‘{print $10}’)
echo “ASM disk $ASMdisk is associated on $phy_disk [$major, $minor]"

/etc/init.d/oracleasm listdisks > /tmp/asmdisks.txt
while read -r ASM_disk
major="$(/etc/init.d/oracleasm querydisk -d $ASM_disk | awk -F[ ‘{ print $2 }’| awk -F] ‘{ print $1 }’ | cut -d"," -f1)"
minor="$(/etc/init.d/oracleasm querydisk -d $ASM_disk | awk -F[ ‘{ print $2 }’| awk -F] ‘{ print $1 }’ | cut -d"," -f2)"
phy_disk="$(ls -l /dev/* | grep ^b | grep “$major, *$minor" | awk ‘{ print $10 }’)"
echo “ASM disk $ASM_disk is associated on $phy_disk [$major, $minor]"
done < /tmp/asmdisks.txt

How To Map Oracle ASM Disk Against Physical Disk And LUNs In Linux?