• Table of contents
• Database Mirroring on remote site
  • Introduction
  • Full Database Backup + Transaction Logs
  • Test Tools

Database Mirroring on remote site¶

Introduction¶

To mirror production database(s) to remote site, there are a number of approaches:

1. Use (full) database backup
2. Use (full + cumulative) database backup
3. Use (full + delta) database backup
4. Use (full + cumulative+ delta) database backup - not recommended because it is too complex
5. Use (full database backup + transaction logs)

the approach 1-4 are described in Using DB2 incremental backup

We will only discuss about the last approach, full + transaction logs in the coming sections. Before that, let's do a quick compare for these approaches:

	full	full + cumulative	full + delta	full + cumulative + delta	full + transaction logs
Backup Image Size	Biggest	Small	Smallest	In between Small and Smallest	Smaller
Backup Time	Longest	Depends on how the data is stored across tablespaces. If all data is stored in one tablespace then it won't make any difference	Depends on how the data is stored across tablespaces. If all data is stored in one tablespace then it won't make any difference	Depends on how the data is stored across tablespaces. If all data is stored in one tablespace then it won't make any difference	Fast
Easy To Recover	Easiest	Easy	Difficult	Very Difficult	Easy
Need To Revise Backup Strategies	No	Yes	Yes	Yes	No
Resources Utilization	High	High to Medium	High to Medium	High to Medium	Low
DB is usable after first restored	Yes	No	No	No	No
Functions Used	backup + restore + rollforward	backup + restore + rollforward	backup + restore + rollforward	backup + restore + rollforward	backup + restore + rollforward + rsync

Full Database Backup + Transaction Logs¶

Below diagram describes how this approach works:

Sample steps:

1. Like any other approaches, you must perform a full database backup (either offline or online)
   • e.g.:
     

     $ db2 backup db prod_db online

2. Transfer the full backup image to the standby site and perform a database restoration:
   • e.g.:
     

     $ db2 restore db prod_db replace history file

3. Use the rsync command to do an one way incremental sync from remote (production) site to standby site.
   • e.g.:
     

     $ rsync -var --delete db2inst1@db-prod:/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/ /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR

4. Execute the rollfoward on the standby database: (do not include the complete option)
   • e.g.:
     

     $ db2 rollforward db prod_db to end of logs

5. If want to bring up the standby database, just execute this command (change the database alias):
   

   $ db2 rollforward db prod_db complete

The biggest advantage of this approach is there's little or no need to revise current backup strategies, however, the mirror database cannot be used until the rollforward process is terminated (also happens on the other db incremental backup approaches).

Test Tools¶