Infrastructure at your Service

Marc Wagner

Dbvisit Standby Archive Log Daemon

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.

 

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.

 

Leave a Reply

Marc Wagner
Marc Wagner