Infrastructure at your Service

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

[email protected]:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start
Starting Dbvisit Daemon...
Started successfully.

[email protected]:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status
Dbvisit Daemon process is running with pid 11546.

[email protected]:/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.
[[email protected] ~]# vi /etc/systemd/system/dbvlogdaemon.service

[[email protected] ~]# 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

[[email protected] ~]# chmod 644 /etc/systemd/system/dbvlogdaemon.service

[[email protected] ~]# systemctl daemon-reload

[[email protected] ~]# 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 :

[[email protected] ~]# 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 :

[email protected]:/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 :

[email protected]:/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

[email protected]:/u01/app/dbvisit/standby/ [rdbms12201] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status
Dbvisit Daemon process is running with pid 12542.

[email protected]:/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 :

[email protected]:/u01/app/dbvisit/standby/ [rdbms12201] kill -9 12542

After a time you will see that the daemon has been automatically restarted with new PID :

[email protected]:/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

[email protected]:/u01/app/dbvisit/standby/ [rdbms12201] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status
Dbvisit Daemon process is not running.

[email protected]:/u01/app/dbvisit/standby/ [rdbms12201] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status
Dbvisit Daemon process is not running.

[email protected]:/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

[email protected]:/u01/app/dbvisit/standby/ [rdbms12201] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status
Dbvisit Daemon process is running with pid 12940.

[email protected]:/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
[email protected]:/u01/app/dbvisit/standby/ [rdbms12201]

End of november edit

 

Check running daemon

[email protected]:/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

[email protected]:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB_SITE1 -D status
Dbvisit Daemon process is running with pid 4299.

[email protected]:/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

[email protected]:/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.
[email protected]:/oracle/u01/app/dbvisit/standby/conf/ [DBVPDB] pwd
/oracle/u01/app/dbvisit/standby/conf

[email protected]:/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.
[[email protected] ~]# service dbvlogdaemon stop
Redirecting to /bin/systemctl stop dbvlogdaemon.service
[[email protected] ~]# service dbvlogdaemon start
Redirecting to /bin/systemctl start dbvlogdaemon.service

[[email protected] ~]# service dbvlogdaemon stop
Redirecting to /bin/systemctl stop dbvlogdaemon.service
[[email protected] ~]# 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.
[email protected]:/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.
[email protected]:/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.

[email protected]:/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.

[email protected]:/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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Marc Wagner
Marc Wagner

Consultant