When an HADR active standby database
is replaying DDL log records or maintenance operations, the standby
enters the replay-only window. When the standby is in
the replay-only window, existing connections to the standby are terminated
and new connections to the standby are blocked (SQL1224N).
New
connections are allowed on the standby after the replay of all active
DDL or maintenance operations has completed.
The only user connections that can remain active on a standby in
the replay-only window are connections that are executing DEACTIVATE
DATABASE or TAKEOVER commands. When applications
are forced off at the outset of the replay-only window, an error is
returned (SQL1224N). Depending on the number of readers
connected to the active standby, there may be a slight delay before
the DDL log records or maintenance operations are replayed on the
standby.
There are a number of DDL statements and maintenance operations
that, when run on the HADR primary, will trigger a replay-only window
on the standby. The following lists are not exhaustive.
- DDL statements
- CREATE, ALTER, or DROP TABLE (except DROP TABLE for DGTT)
- CREATE GLOBAL TEMP TABLE
- TRUNCATE TABLE
- RENAME TABLE
- RENAME TABLESPACE
- CREATE, DROP, or ALTER INDEX
- CREATE or DROP VIEW
- CREATE, ALTER, or DROP TABLESPACE
- CREATE, ALTER, or DROP BUFFER POOL
- CREATE, ALTER, or DROP FUNCTION
- CREATE, ALTER, or DROP PROCEDURE
- CREATE or DROP TRIGGER
- CREATE, ALTER, or DROP TYPE
- CREATE, ALTER, or DROP ALIAS
- CREATE or DROP SCHEMA
- CREATE, ALTER, or DROP METHOD
- CREATE, ALTER, or DROP MODULE
- CREATE, ALTER, or DROP NICKNAME
- CREATE, ALTER, or DROP SEQUENCE
- CREATE, ALTER, or DROP WRAPPER
- CREATE, ALTER, or DROP FUNCTION MAPPING
- CREATE or DROP INDEX EXTENSION
- CREATE or DROP INDEX FOR TEXT
- CREATE or DROP EVENT MONITOR
- CREATE, ALTER, or DROP SECURITY LABEL
- CREATE, ALTER, or DROP SECURITY LABEL COMPONENT
- CREATE, ALTER, or DROP SECURITY POLICY
- CREATE or DROP TRANSFORM
- CREATE, ALTER, or DROP TYPE MAPPING
- CREATE, ALTER, or DROP USER MAPPING
- CREATE or DROP VARIABLE
- CREATE, ALTER, or DROP WORKLOAD
- GRANT USAGE ON WORKLOAD
- REVOKE USAGE ON WORKLOAD
- CREATE, ALTER, or DROP SERVICE CLASS
- CREATE, ALTER, or DROP WORK CLASS SET
- CREATE, ALTER, or DROP WORK ACTION SET
- CREATE, ALTER, or DROP THRESHOLD
- CREATE, ALTER, or DROP HISTOGRAM TEMPLATE
- AUDIT
- CREATE, ALTER, or DROP AUDIT POLICY
- CREATE or DROP ROLE
- CREATE, ALTER, or DROP TRUSTED CONTEXT
- REFRESH TABLE
- SET INTEGRITY
- Maintenance operations
- Classic, or offline, reorg
- Inplace, or online, reorg
- Index reorg (indexes all, individual index)
- MDC and ITC reclaim reorg
- Load
- Bind or rebind
- db2rbind
- Runstats
- Table move
- Auto statistics
- Auto reorg
- Real Time Statistics
- Other operations or actions
- Automatic Dictionary Creation for tables with COMPRESS YES attribute
- Asynchronous Index Cleanup on detached table partition
- Implicit rebind
- Implicit index rebuild
- Manual update of statistics.
- Deferred MDC rollout
- Asynchronous Index cleanup after MDC rollout
- Reuse of a deleted MDC or ITC block on insert into MDC or ITC
table
- Asynchronous background processes updating catalog tables SYSJOBS
and SYSTASKS for inserting, updating, and deleting tasks
Monitoring the replay-only window
You can
monitor the replay-only window using the db2pd command
with the -hadr option (on either the standby
or the primary) or the MON_GET_HADR table function (from the primary).
The standby's status, including information about the replay-only
window, is sent to the primary on every heartbeat.
There are
three pertinent elements to monitor:
- STANDBY_REPLAY_ONLY_WINDOW_ACTIVE, which indicates whether
DDL or maintenance-operation replay is in progress on the standby.
Normally, the value is N, but when the
replay-only window is active, the value is Y.
- STANDBY_REPLAY_ONLY_WINDOW_START, which indicates the time
at which the current replay-only window (if there is one) became active.
- STANDBY_REPLAY_ONLY_WINDOW_TRAN_COUNT, which indicates
the total number of existing uncommitted DDL or maintenance transactions
executed so far in the current replay-only window (if there is one).
To use the table function, issue something similar to
the following query on the primary:
select STANDBY_ID, STANDBY_REPLAY_ONLY_WINDOW_ACTIVE, STANDBY_REPLAY_ONLY_WINDOW_START,
STANDBY_REPLAY_ONLY_WINDOW_TRAN_COUNT from table (mon_get_hadr(NULL))
Here
is an example using the
db2pd command on a standby
that is currently in a replay-only window:
Database Member 0 -- Database HADRDB -- Active -- Up 0 days 00:23:17 -- Date 06/08/2011 13:57:23
HADR_ROLE = STANDBY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = NEARSYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS =
PRIMARY_MEMBER_HOST = hostP.ibm.com
PRIMARY_INSTANCE = db2inst
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = hostS1.ibm.com
STANDBY_INSTANCE = db2inst
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 06/08/2011 13:38:10.199479 (1307565490)
HEARTBEAT_INTERVAL(seconds) = 25
HADR_TIMEOUT(seconds) = 120
TIME_SINCE_LAST_RECV(seconds) = 3
PEER_WAIT_LIMIT(seconds) = 0
LOG_HADR_WAIT_CUR(seconds) = 0.000
LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.006298
LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.516
LOG_HADR_WAIT_COUNT = 82
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 50772
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87616
PRIMARY_LOG_FILE,PAGE,POS = S0000009.LOG, 1, 49262315
STANDBY_LOG_FILE,PAGE,POS = S0000009.LOG, 1, 49262315
HADR_LOG_GAP(bytes) = 0
STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000009.LOG, 1, 49262315
STANDBY_RECV_REPLAY_GAP(bytes) = 0
PRIMARY_LOG_TIME = 06/08/2011 13:49:19.000000 (1307566159)
STANDBY_LOG_TIME = 06/08/2011 13:49:19.000000 (1307566159)
STANDBY_REPLAY_LOG_TIME = 06/08/2011 13:49:19.000000 (1307566159)
STANDBY_RECV_BUF_SIZE(pages) = 16
STANDBY_RECV_BUF_PERCENT = 0
STANDBY_SPOOL_LIMIT(pages) = 0
STANDBY_SPOOL_PERCENT = 0
PEER_WINDOW(seconds) = 0
READS_ON_STANDBY_ENABLED = Y
STANDBY_REPLAY_ONLY_WINDOW_ACTIVE = Y
STANDBY_REPLAY_ONLY_WINDOW_START = 06/08/2011 13:50:23
STANDBY_REPLAY_ONLY_WINDOW_TRAN_COUNT = 5
Recommendations for minimizing the impact of the replay-only
window
Because replay operations on an HADR standby take
priority over readers, frequent read-only windows can be disruptive
to readers connected to or attempting to connect to the standby. To
avoid or minimize this impact, consider the following recommendations:
- Run DDL and maintenance operations during a scheduled maintenance
window, preferably at off-peak hours.
- Run DDL operations collectively rather than in multiple groups.
- Run REORG or RUNSTATS only
on the required tables instead of all tables.
- Terminate applications on the active standby using the FORCE
APPLICATION command with the ALL option
before running the DDL or maintenance operations on the primary. Monitor
the replay-only window to determine when it is inactive, and redeploy
the applications on the standby.