DB2 : restore a high availability database
How to refresh tst4ins4
Terminology
Initialize = To create a new HADR cluster.
Reinitialize = To repair an existing HADR cluster.
Refresh = Same as non clustered databases, this means to restore the database from a different database backup image.
1. Start work with the primary (should be on db2tst602)
Verify it is the primary:
db2pd -db wctst10 -hadr
2. Stop HADR on primary:
db2pd -db wctst10 -hadr <-- to verify it is the primary
db2 stop hadr on db wctst10
DB20000I The STOP HADR ON DATABASE command completed successfully.
3. Stop HADR on standby:
[tst3ins3@db2tst601 ~]
$ db2pd -db wctst10 -hadr
[tst3ins3@db2tst601 ~]
$ db2 deactivate db wctst10
DB20000I The DEACTIVATE DATABASE command completed successfully.
[tst3ins3@db2tst601 ~]
$ db2 stop hadr on db wctst10
DB20000I The STOP HADR ON DATABASE command completed successfully.
[tst3ins3@db2tst601 ~]
4. Perform the refresh using the automated method (After stopping HADR)
5. After refreshing the primary do the following on the primary, not connected:
a. terminate your session
db2 terminate
DB20000I The TERMINATE command completed successfully.
b. update the db2 cfg HADR settings
db2 update db cfg for wctst10 using HADR_LOCAL_HOST db2tst602 HADR_LOCAL_SVC 60000 HADR_REMOTE_HOST db2tst601 HADR_REMOTE_SVC 60030 HADR_TARGET_LIST 'NULL' HADR_REMOTE_INST tst3ins3 HADR_TIMEOUT 30 immediate
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
c. Verify HADR settings
db2 get db cfg for wctst10 | grep HADR
*** should look like this on db2tst602 ***
$ db2 get db cfg for wctst10 | grep HADR
db2 get db cfg for wctst10 | grep HADR
*** should look like this on db2tst602 ***
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) = db2tst602
HADR local service name (HADR_LOCAL_SVC) = 60000
HADR remote host name (HADR_REMOTE_HOST) = db2tst601
HADR remote service name (HADR_REMOTE_SVC) = 60030
HADR instance name of remote server (HADR_REMOTE_INST) = tst3ins3
HADR timeout value (HADR_TIMEOUT) = 30
HADR target list (HADR_TARGET_LIST) =
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = AUTOMATIC(12375000)
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 300
HADR SSL certificate label (HADR_SSL_LABEL) =
HADR SSL Hostname Validation (HADR_SSL_HOST_VAL) = OFF
6. to turn on TCPIP on 602:
db2stop
db2set db2comm=tcpip
db2start
db2set -all
7. take a fresh ONLINE backup of the datatabase
db2 backup database wctst10 online to /usr/opt/app/dbdump/tst4ins4/ compress without prompting
8. Ship the backup (as root) over to the standby (should be db2tst601)
a. verify that the backup ownership and permissions are correct
9. Restore the backup into wctst10 without rolling forward
db2 restore database wctst10 from </path/to/backup> taken at <timestamp>
Restore is about 40 mins.
[tst3ins3@db2tst601 tst3ins3]
$ db2start
03/12/2024 13:37:02 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[tst3ins3@db2tst601 tst3ins3]
$ db2 restore database wctst10 from /usr/opt/app/dbdump/db_refresh/ taken at 20230727120722
SQL2539W The specified name of the backup image to restore is the same as the
name of the target database. Restoring to an existing database that is the
same as the backup image database will cause the current database to be
overwritten by the backup version.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
10. Update db cfg HADR settings
a. run update statement
db2 update db cfg for wctst10 using HADR_LOCAL_HOST db2tst601 HADR_LOCAL_SVC 60030 HADR_REMOTE_HOST db2tst602 HADR_REMOTE_SVC 60000 HADR_TARGET_LIST 'NULL' HADR_REMOTE_INST tst4ins4 HADR_TIMEOUT 30 immediate
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
b. verify settings
db2 get db cfg for wctst10 | grep HADR
*** should look like this on db2tst601 ***
HADR database role = STANDBY
HADR local host name (HADR_LOCAL_HOST) = db2tst601
HADR local service name (HADR_LOCAL_SVC) = 60030
HADR remote host name (HADR_REMOTE_HOST) = db2tst602
HADR remote service name (HADR_REMOTE_SVC) = 60000
HADR instance name of remote server (HADR_REMOTE_INST) = tst4ins4
HADR timeout value (HADR_TIMEOUT) = 30
HADR target list (HADR_TARGET_LIST) =
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = AUTOMATIC(12375000)
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 300
HADR SSL certificate label (HADR_SSL_LABEL) =
HADR SSL Hostname Validation (HADR_SSL_HOST_VAL) = OFF
8. Start HADR on standby
db2 start hadr on db wctst10 as standby
DB20000I The START HADR ON DATABASE command completed successfully.
9. Start HADR on primary
db2 start hadr on db wctst10 as primary
10. Wait a minute and verify HADR status
db2pd -db wctst10 -hadr
lssam
notes for 602:
db2 deactivate db wctst10
db2 stop hadr on db wctst10
db2stop
db2set db2comm=tcpip
db2start
db2set -all
db2 start hadr on db wctst10 as standby
db2pd -db wctst10 -hadr
[tst4ins4@db2tst602 ~]
$ db2pd -alldbs -hadr
In order to get the ports for
$ cat /etc/services | grep tst4ins4
DB2_tst4ins4 20022/tcp <-- HADR_LOCAL_SVC (incorrect info per Ian 2024 Oct 16th)
DB2_tst4ins4_1 20023/tcp
DB2_tst4ins4_2 20024/tcp
DB2_tst4ins4_3 20025/tcp
DB2_tst4ins4_4 20026/tcp
DB2_tst4ins4_END 20027/tcp
db2c_tst4ins4 50011/tcp <-- database port
If issues:
we need to:
db2 activate db wctst10
and also on primary
$ db2 start hadr on db wctst10 as primary by force
DB20000I The START HADR ON DATABASE command completed successfully.
On the db2tst602 server:
db2 update alternate server for database wctst10 using hostname db2tst602.gcp.tmw.com port 50000
db2 terminate
db2 list db directory
On the db2tst602 server:
db2 update alternate server for database wctst10 using hostname db2tst602.gcp.tmw.com port 50005
db2 terminate
db2 list db directory
Comments
Post a Comment