Dbvisit Standby version 8 comes with a nice feature, a daemon, which gives the benefit to send and apply the archive log automatically in the background. Bypassing the system scheduling, the daemon will facilitate customer RPO (Recovery Point Objective) and RTO (Recovery Time Objective) fine tuning. Monitoring to apply logs to the Standby only when needed, will also optimize use of resources. Originally available for the Linux based environments, the feature has been made available for the Windows based platforms starting 8.0.06. This blog will cover its implementation and show its benefit.
Demo databases environments have been easily managed thanks to DBI DMK tool.
Environment
DBVP : Primary Server
DBVS : Standby Server
DBVPDB_SITE1 : Primary database
DBVPDB_SITE2 : Physical Standby database
Daemon start/stop/status
oracle@DBVP:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start Starting Dbvisit Daemon... Started successfully. oracle@DBVP:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status Dbvisit Daemon process is running with pid 11546. oracle@DBVP:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D stop Stopping Dbvisit Daemon... Successfully stopped.
Automatic startup
In order to start the daemon automatically at boot, and easily manage its status, we will create a dbvlogdaemon Service.
[root@DBVP ~]# vi /etc/systemd/system/dbvlogdaemon.service [root@DBVP ~]# cat /etc/systemd/system/dbvlogdaemon.service [Unit] Description=DB Visit log daemon Service After=oracle.service [Service] Type=simple RemainAfterExit=yes User=oracle Group=oinstall Restart=always ExecStart=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start ExecStop=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D stop [Install] WantedBy=multi-user.target [root@DBVP ~]# chmod 644 /etc/systemd/system/dbvlogdaemon.service [root@DBVP ~]# systemctl daemon-reload [root@DBVP ~]# systemctl enable dbvlogdaemon.service
Of course this would not avoid impact in case of daemon crash which could be simulated with a kill command.
Edit from November 14, 2019
In order to avoid any impact of daemon crash, use below service configuration :
[root@DBVP ~]# cat /etc/systemd/system/dbvlogdaemon.service [Unit] Description=Dbvisit daemon ConditionFileIsExecutable=/u01/app/dbvisit/standby/dbvctl After=syslog.target network.target [Service] RemainAfterExit=no StartLimitInterval=5 StartLimitBurst=10 ExecStart=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start ExecStop=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D stop User=oracle Group=oinstall Restart=always Type=forking RestartSec=10 [Install] WantedBy=multi-user.target
See below test showing that daemon is started automatically again in case of crash.
Start daemon :
oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] systemctl start dbvlogdaemon.service ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units === Authentication is required to manage system services or units. Authenticating as: root Password: ==== AUTHENTICATION COMPLETE ===
Check that daemon is well started :
oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] systemctl status dbvlogdaemon.service ● dbvlogdaemon.service - Dbvisit daemon Loaded: loaded (/etc/systemd/system/dbvlogdaemon.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2019-11-14 10:34:37 CET; 5s ago Process: 12497 ExecStop=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D stop (code=exited, status=0/SUCCESS) Process: 12521 ExecStart=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start (code=exited, status=0/SUCCESS) Main PID: 12542 (dbvctl) CGroup: /system.slice/dbvlogdaemon.service └─12542 /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status Dbvisit Daemon process is running with pid 12542. oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] ps -ef | grep dbv | grep -v grep oracle 1197 1 0 10:10 ? 00:00:00 /u01/app/dbvisit/dbvnet/dbvnet -d start oracle 1198 1 0 10:10 ? 00:00:00 /u01/app/dbvisit/dbvagent/dbvagent -d start oracle 12542 1 1 10:34 ? 00:00:00 /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start
Kill the daemon process :
oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] kill -9 12542
After a time you will see that the daemon has been automatically restarted with new PID :
oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] ps -ef | grep dbv | grep -v grep oracle 1197 1 0 10:10 ? 00:00:00 /u01/app/dbvisit/dbvnet/dbvnet -d start oracle 1198 1 0 10:10 ? 00:00:00 /u01/app/dbvisit/dbvagent/dbvagent -d start oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status Dbvisit Daemon process is not running. oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status Dbvisit Daemon process is not running. oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] ps -ef | grep dbv | grep -v grep oracle 1197 1 0 10:10 ? 00:00:00 /u01/app/dbvisit/dbvnet/dbvnet -d start oracle 1198 1 0 10:10 ? 00:00:00 /u01/app/dbvisit/dbvagent/dbvagent -d start oracle 12940 1 0 10:37 ? 00:00:00 /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status Dbvisit Daemon process is running with pid 12940. oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201] systemctl status dbvlogdaemon.service ● dbvlogdaemon.service - Dbvisit daemon Loaded: loaded (/etc/systemd/system/dbvlogdaemon.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2019-11-14 10:37:31 CET; 2min 5s ago Process: 12907 ExecStop=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D stop (code=exited, status=0/SUCCESS) Process: 12919 ExecStart=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start (code=exited, status=0/SUCCESS) Main PID: 12940 (dbvctl) CGroup: /system.slice/dbvlogdaemon.service └─12940 /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start oracle@DBVP:/u01/app/dbvisit/standby/ [rdbms12201]
End of november edit
Check running daemon
oracle@DBVP:/u01/app/dbvisit/standby/ [DBVPDB] ps -ef | grep dbvctl | grep -v grep oracle 4299 1 0 08:25 ? 00:00:02 /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start oracle@DBVP:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB_SITE1 -D status Dbvisit Daemon process is running with pid 4299. oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] ps -ef | grep dbvctl | grep -v grep oracle 4138 1 0 08:25 ? 00:00:01 /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB_SITE1 -D status Dbvisit Daemon process is running with pid 4138.
Daemon Parameter
# DMN_DBVISIT_INTERVAL - interval in sec for dbvisit schedule on source # DMN_MONITOR_INTERVAL - interval in sec for log monitor schedule on source # DMN_DBVISIT_TIMEOUT - max sec for a dbvisit process to complete on source # DMN_MONITOR_TIMEOUT - max sec for a monitor process to complete on source # DMN_MONITOR_LOG_NUM - number of logs to monitor on source # DMN_MAX_FAIL_NOTIFICATIONS - max number of emails sent on failure on source # DMN_BLACKOUT_STARTTIME - blackout window start time HH:MI on source # DMN_BLACKOUT_ENDTIME - blackout window end time HH:MI on source # DMN_DBVISIT_INTERVAL_DR - interval in sec for dbvisit schedule on destination # DMN_MONITOR_INTERVAL_DR - interval in sec for log monitor schedule on destination # DMN_DBVISIT_TIMEOUT_DR - max sec for a dbvisit process to complete on destination # DMN_MONITOR_TIMEOUT_DR - max sec for a monitor process to complete on destination # DMN_MONITOR_LOG_NUM_DR - number of logs to monitor on destination # DMN_MAX_FAIL_NOTIFICATIONS_DR - max number of emails sent on failure on destination # DMN_BLACKOUT_STARTTIME_DR- blackout window start time HH:MI on destination # DMN_BLACKOUT_ENDTIME_DR - blackout window end time HH:MI on destination
With the daemon, we can pause the archive send/apply process using the DMN_BLACKOUT parameters.
To setup our lab we will act on the most important parameters :
- DMN_MONITOR_INTERVAL (Primary) and DMN_MONITOR_INTERVAL_DR (Standby).
The Monitor Interval will give the frequency for Dbvisit to check for new archive log and only act if existing. - DMN_DBVISIT_INTERVAL (Primary) and DMN_DBVISIT_INTERVAL_DR (Standby)
The Dbvisit Interval will give the frequency for Dbvisit to force a send/apply process. This action will be dependent of the LOGSWITCH DCC parameter. Recommendation is not to go below 5 minutes.
oracle@DBVP:/oracle/u01/app/dbvisit/standby/conf/ [DBVPDB] pwd /oracle/u01/app/dbvisit/standby/conf oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] egrep 'DMN_DBVISIT_INTERVAL|DMN_MONITOR_INTERVAL' dbv_DBVPDB_SITE1.env # DMN_DBVISIT_INTERVAL - interval in sec for dbvisit schedule on source # DMN_MONITOR_INTERVAL - interval in sec for log monitor schedule on source # DMN_DBVISIT_INTERVAL_DR - interval in sec for dbvisit schedule on destination # DMN_MONITOR_INTERVAL_DR - interval in sec for log monitor schedule on destination DMN_DBVISIT_INTERVAL = 300 DMN_MONITOR_INTERVAL = 60 DMN_DBVISIT_INTERVAL_DR = 300 DMN_MONITOR_INTERVAL_DR = 60
The LOGSWITCH parameter determines if a database log switch (alter system switch logfile) should be trigger at Dbvisit execution.
N (default value) : Only if there are no new archive logs to transfer.
Y : At every execution, independently of the archive log creation.
I(Ignore) : Never. To be use with caution.
A daemon restart is mandatory post DDC configuration file updates.
[root@DBVP ~]# service dbvlogdaemon stop Redirecting to /bin/systemctl stop dbvlogdaemon.service [root@DBVP ~]# service dbvlogdaemon start Redirecting to /bin/systemctl start dbvlogdaemon.service [root@DBVS ~]# service dbvlogdaemon stop Redirecting to /bin/systemctl stop dbvlogdaemon.service [root@DBVS ~]# service dbvlogdaemon start Redirecting to /bin/systemctl start dbvlogdaemon.service
Send and apply archive log demo
Get current date and primary current sequence.
SQL> select sysdate from dual; SYSDATE ------------------- 2018/03/28 12:30:50 SQL> select max(sequence#) from v$log; MAX(SEQUENCE#) -------------- 179
Generate a Dbvisit gap report.
oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i ============================================================= Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21393) dbvctl started on DBVP: Wed Mar 28 12:30:57 2018 ============================================================= Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281230: ------------------------------------------------------------- Destination database on DBVS is at sequence: 178. Source database on DBVP is at log sequence: 179. Source database on DBVP is at archived log sequence: 178. Dbvisit Standby last transfer log sequence: 178. Dbvisit Standby last transfer at: 2018-03-28 12:29:14. Archive log gap for thread 1: 0. Transfer log gap for thread 1: 0. Standby database time lag (DAYS-HH:MI:SS): +00:01:27. ============================================================= dbvctl ended on DBVP: Wed Mar 28 12:31:06 2018 =============================================================
No archive log needs to be send and apply on the standby. Both databases are in sync.
Generate logfile switch
SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered.
Check current date and primary database current sequence.
SQL> select sysdate from dual; SYSDATE ------------------- 2018/03/28 12:31:29 SQL> select max(sequence#) from v$log; MAX(SEQUENCE#) -------------- 182
Generate new Dbvisit gap reports.
oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i ============================================================= Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21454) dbvctl started on DBVP: Wed Mar 28 12:31:38 2018 ============================================================= Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281231: ------------------------------------------------------------- Destination database on DBVS is at sequence: 178. Source database on DBVP is at log sequence: 182. Source database on DBVP is at archived log sequence: 181. Dbvisit Standby last transfer log sequence: 178. Dbvisit Standby last transfer at: 2018-03-28 12:29:14. Archive log gap for thread 1: 3. Transfer log gap for thread 1: 3. Standby database time lag (DAYS-HH:MI:SS): +00:02:27. ============================================================= dbvctl ended on DBVP: Wed Mar 28 12:31:47 2018 =============================================================
We can see that we have 3 new archive logs to transfer and to apply on the standby. There is a 3 sequences lag between both databases.
oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i ============================================================= Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21571) dbvctl started on DBVP: Wed Mar 28 12:32:19 2018 ============================================================= Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281232: ------------------------------------------------------------- Destination database on DBVS is at sequence: 178. Source database on DBVP is at log sequence: 182. Source database on DBVP is at archived log sequence: 181. Dbvisit Standby last transfer log sequence: 181. Dbvisit Standby last transfer at: 2018-03-28 12:32:13. Archive log gap for thread 1: 3. Transfer log gap for thread 1: 0. Standby database time lag (DAYS-HH:MI:SS): +00:02:27. ============================================================= dbvctl ended on DBVP: Wed Mar 28 12:32:27 2018 =============================================================
3 archive logs has been automatically transferred by the daemon to the standby in the next minute.
oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i ============================================================= Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21679) dbvctl started on DBVP: Wed Mar 28 12:33:00 2018 ============================================================= Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281233: ------------------------------------------------------------- Destination database on DBVS is at sequence: 181. Source database on DBVP is at log sequence: 182. Source database on DBVP is at archived log sequence: 181. Dbvisit Standby last transfer log sequence: 181. Dbvisit Standby last transfer at: 2018-03-28 12:32:13. Archive log gap for thread 1: 0. Transfer log gap for thread 1: 0. Standby database time lag (DAYS-HH:MI:SS): +00:01:13. ============================================================= dbvctl ended on DBVP: Wed Mar 28 12:33:09 2018 =============================================================
Another minute later the standby daemon applied the new archive logs. Both databases are on sync.
Conclusion
Dbvisit new daemon feature is adding real flexibility in sending and applying archive logs, and help improving customer RPO and RTO. We still might want to keep a daily crontab gap report with email to be sent to a DBA team. This will ensure to monitor daemon keep alive.
Logswitch and sending archive logs to standby consumes real system resource. Dbvisit daemon will also help fine tuning the use of the resource.
Note that the daemon processes must be restarted after each daylight saving clock change, and stopped during graceful switchover.