Database Mirroring (DB2) on remote site » History » Revision 10
Revision 9 (chin-yeh, 03/22/2012 09:09 AM) → Revision 10/23 (chin-yeh, 03/22/2012 09:59 AM)
{{toc}} h1. Database Mirroring on remote site h2. Introduction To mirror production database(s) to remote site, there are a number of approaches: # Use <code>(full)</code> database backup # Use <code>(full + cumulative)</code> incremental)</code> database backup # Use <code>(full + delta)</code> database backup # Use <code>(full + cumulative+ incremental + delta)</code> database backup - _not recommended because it is too complex_ # Use <code>(full database backup + transaction logs)</code> > the approach 1-4 are described in "Using DB2 incremental backup":http://www.ibm.com/developerworks/data/library/techarticle/dm-0910db2incrementalbackup/ We The following sections will only discuss about describe how the last approach, _full + transaction logs_ in the coming sections. approach actually works. 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 restored*|Yes|No|No|No|No| |*Functions Used*|backup + restore|backup + restore + rollforward|backup + restore + rollforward|backup + restore + rollforward|backup + restore + rollforward + rsync| compare: !database_mirroring_approach.png! h2. Full Database Backup + Transaction Logs Below diagram describes how this approach works: !full_transaction_logs_approach.png! _Sample steps:_ # Like any other approaches, you must perform a full database backup _(either offline or online)_ ** e.g.: <pre>$ db2 backup db prod_db online</pre> # Use the <code>rsync</code> command to do an one way incremental sync from remote (production) site to standby site. ** e.g.: <pre>$ rsync -var --delete db2inst1@db-prod:/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/ /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR</pre> # Execute the <code>rollfoward</code> on the standby database: (*do not* include the *complete* option) ** e.g.: <pre>$ db2 rollforward db prod_db to end of logs</pre> # If want to bring up the standby database, just execute this command (_change the database alias_): <pre>$ db2 rollforward db prod_db complete</pre> 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 <code>rollforward</code> process is terminated (_also happens on the other db incremental backup approaches_). terminated. h2. Test Tools