Shulou(Shulou.com)06/03 Report--

DB2-HADR building

1. Build the system environment.

System version: CentOS-6.7-x86_64-bin-DVD1

1. Select minimal

2. Install and configure IP and hostname (/ etc/hosts / etc/sysconfig/network is changed to the same hostname)

Database version: v9.7fp7_linuxx64_server.tar

3. Install master library erpdb and slave library instances under centos-6.7 system.

4. Synchronization time ntpdate asia.pool.ntp.org (may not be done, depending on the specific time)

5. Install scp yum install-y openssh-clients (do not do it, but do it in order to transmit backup sets)

The following experiment takes the database devdb47 as the main library and devdb49 as the backup library:

1. Back up the main library

Db2 backup db devdb47

2. Transfer the backup set to the slave library

Scp xxx (backup set) 172.16.0.49:/home/db2inst1

3. Restore the backup set from the library

Db2 restore db devdb47

4. Configure the communication port

Both master and slave are modified

Vi / etc/service

Add db2h_erpinst1 70000/tcp

5. Explain the specific meaning of master-slave parameter configuration parameters at the bottom.

Main library:

Db2 update db cfg for devdb47 using hadr_local_host PrimaryNode-1

Db2 update db cfg for devdb47 using hadr_local_svc db2h_erpinst1

Db2 update db cfg for devdb47 using hadr_remote_host PrimaryNode-2

Db2 update db cfg for devdb47 using hadr_remote_svc db2h_erpinst1

Db2 update db cfg for devdb47 using hadr_remote_inst db2inst1

Db2 update db cfg for devdb47 using logindexbuild on

Db2 update db cfg for devdb47 using indexrec restart

Db2 update db cfg for devdb47 using hadr_syncmode SUPERASYNC

From the library:

Db2 update db cfg for devdb47 using hadr_local_host PrimaryNode-2

Db2 update db cfg fordevdb47 using hadr_local_svc db2h_erpinst1

Db2 update db cfg for devdb47 using hadr_remote_host PrimaryNode-1

Db2 update db cfg for devdb47 using hadr_remote_svc db2h_erpinst1

Db2 update db cfg for devdb47 using hadr_remote_inst db2inst1

Db2 update db cfg for devdb47 using logindexbuild on

Db2 update db cfg for devdb47 using indexrec restart

Db2 update db cfg for devdb47 using hadr_syncmode SUPERASYNC

6. Register variable configuration:

Main library:

Db2set db2_HADR_ROS=ON

Db2set db2_STANDBY_ISO=UR

# enable standby database read-only

From the library:

Db2set db2_HADR_ROS=ON

Db2set db2_STANDBY_ISO=UR

7. Start the master-slave database (for the first time, remember that the startup is no longer in use):

From the library:

Db2start

Db2 start HADR on db devdb47 as standby

Main library:

Db2start

Db2 start HADR on db devdb47 as primary

View hadr:

Db2 get db cfg for devdb47 | grep HADR

10. Finally, the main view of the tablespace, the use of tools to view data

Db2 list tablespaces show detail

Startup after the first startup: (with shutdown process)

Master (shutdown process):

Deactivate the primary database and stop the database devdb47

Db2 list applications all # disconnect all connections

Db2 terminate

Db2 deactivate db erpdb

Db2stop force

Standby (shutdown process):

Deactivate standby database and stop database devdb47

Db2 list applications all # disconnect all connections

Db2 terminate

Db2 deactivate db erpdb

Db2stop force

The above shutdown is complete.

Start the startup process (startup after the first startup):

Standby:

1 、 db2start

2. Db2 activate db devdb47 # activate the database

On the primary server, run the following command to verify the HADR role of this server

Db2 get snapshot for db on devdb47 | grep Role

4. On each server, run the following command to verify that the database is synchronized

Db2 get snapshot for database on devdb47 | grep State

5. Check the HADR status (the data viewed is out of sync at this time. Check the data synchronization after inserting the data)

Db2pd-db erpdb-hadr

Lord:

1 、 db2start

2 、 db2 activate db devdb47

On the primary server, run the following command to verify the HADR role of this server

Db2 get snapshot for db on devdb47 | grep Role

4. On each server, run the following command to verify that the database is synchronized

Db2 get snapshot for database on devdb47 | grep State

5. Check the HADR status (the data viewed is out of sync at this time. Check the data synchronization after inserting the data)

Db2pd-db erpdb-hadr

After the HADR is built, you need to import the table structure and its data

1. Import and export table structure (two methods)

Export the table structure in the target table:

Db2look-d devdb47-e-a-x-I db2inst1-w db21234-o devdb47.sql

Import to the usage table:

Db2-tvf devdb47.sql

Since the exported data may not be applied to the same database, you must first modify the database name in the devdb47.sql file

Vi / home/db2inst1/devdb47 modifies devdb47 to the current database name

2. Use the tool to export the table structure SqlDbx and then the current database operation table structure

Export:

Import:

3. Export import table data

Export (backup table contents):

Export all data: (only export table data, not table structure)

Db2move devdb47 EXPORT

Export some tables: (all tables including IP, dim, ods)

Db2move devdb47 EXPORT-tn IP*,DIM*,ODS*-tc db2inst1-l / home/db2inst1

Guide list:

Db2move devdb47 EXPORT-tn DIM_GAME_RESULT-tc db2inst1-l / home/db2inst1/

Guide multiple tables:

Db2move devdb47 EXPORT-tn ODS_BAC_ROUND_RESULT_COMP,ODS_BAC_ROUND_RESULT_DETAIL-tc db2inst1-l / home/db2inst1/game

Import (restore table contents):

Db2move devdb47 IMPORT (slower)

Db2move devdb47 LOAD-lo INSERT-l / home/db2inst1/ (faster)

At this point, the data for the identity column with no generated always option specified is imported into the database, but for all the data to be the same, you need to import the identity column with the generated always option specified.

3. To restore the identity column with the specified generated always option, you need to import load with the identityoverride option.

Export: db2move devdb47 EXPORT-tn ODS_ACCOUNT_BOOK_HISTORY-tc db2inst1-l / home/db2inst1

# you can export alone, or you can use the exported data directly

Import: db2 "LOAD FROM tab98.ixf OF ixf modified BY identityoverride INSERT INTO acct.ODS_ACCOUNT_BOOK_HISTORY"

# tab98.ixf is an exported ixf backup file

After a successful import, you need to manually update the value of the identity field in the target table. To ensure the continuity of the next write data

"alter table acct.ODS_ACCOUNT_BOOK_HISTORY alter column history_id restart with 352120"

Where tab1.ixf is the backup exported ixf format file identityoverride (import data into the target table and specify to use the value of the identity column in the input file) if you want to generate a new identity column, why to use identitygnore as follows:

Db2 "LOAD FROM tab1.ixf OF ixf modified BYidentitygnore INSERT INTO acct.ODS_ACCOUNT_BOOK_HISTORY

4. Before importing data, the column with sequence needs to modify the initial value of the sequence (you must check it before importing the data each time)

Db2 select prevval for SEQ.SEQ_IP_ID from sysibm.sysdummy1

# View the current value of the sequence

Db2 alter sequence SEQ.SEQ_AGENT_ID restart with 5000649

# modify the starting value of the sequence

Db2 values next value for SEQ.SEQ_AGENT_ID

# the next value of the sequence (execution will take you to the next value)

At this point, you can operate on the master library to see if the slave library is synchronized.

SELECT max (OP_ID) FROM U_OPERATOR

View maximum

Select NEXT VALUE for MAMA.SEQ_U_OPERATOR_OP_ID from sysibm.sysdummy1

View the next value

Alter sequence MAMA.SEQ_U_OPERATOR_OP_ID restart with 126

Modify the current starting value

=

=

(HADR) replication operation:

High availability disaster recovery (HADR) uses database logs to copy data from the primary database to the standby database. When replaying the logs on the standby database, some activities may cause the standby database to lag behind the primary database. Some activities require a large number of records, and the large number of log files they generate can cause storage problems. Although using logs to replicate data to a standby database is the core of the availability strategy, the record itself can have a negative impact on the availability of the solution. Properly design maintenance strategies, configure the system to minimize the negative impact of logging, and allow logging to protect your transaction data.

Data definition language (DDL) CREATE ALTER DROP TRUNCATE COMMENT RENAME does not require commit

Data manipulation language (DML) SELECT INSERT UPDATE DELETE MERGE CALL EXPLAIN PLAN LOCK TABLE requires commit

Buffer pool operation

Tablespace operation

Online reassembly records all operations in detail

Offline reorganization usually records operations by hundreds or thousands of affected lines

Metadata for stored procedures and user-defined functions (UDF) (but not related objects or library files)

During the online reorganization process, all operations are recorded in detail. As a result, HADR can replicate operations without making the standby database lag further behind than it does when making more typical database updates. However, because a large number of logging records are generated, this behavior may have a significant impact on the system.

If offline reorganization is not recorded as much as online reorganization, operations are usually recorded by hundreds or thousands of affected lines. This means that the standby database will lag behind because it waits for each log record and then replays many updates immediately. If the offline reorganization is non-clustered, a single log record is generated after the entire reorganization operation. This method affects the ability of the standby database to keep synchronized with the primary database to the greatest extent. After the standby database receives log records from the primary database, the entire reorganization process is performed.

HADR does not copy stored procedures, UDF objects, and library files. Files must be created on the same path in the primary and standby databases. If the standby database cannot find the referenced object or library file, the stored procedure or UDF call on the standby database will fail

(HADR) No replication operations: high availability disaster recovery (HADR) uses database logs to copy data from the primary database to the standby database. The primary database allows operations without logging, but does not replicate such operations to the standby database. If you want to reflect unlogged operations in the standby database (for example, updates to history files), you must perform additional steps to achieve this.

The following are examples of situations in which operations on the primary database are not replicated to the standby database:

Tables created with the NOT LOGGED INITIALLY option specified will not be replicated. Attempting to access such a table after the HADR standby database takes over the primary database will result in an error.

All LOB columns that have been logged will be copied. Unlogged LOB columns will not be copied. However, space will be allocated to them on the standby database, with binary zeros as the value of the column.

Updates made to the database configuration using the UPDATE DATABASE CONFIGURATION (Update Database configuration) and UPDATE DATABASE MANAGER CONFIGURATION (Update Database Management configuration) commands are not replicated.

Database configuration parameters and database manager configuration parameters are not replicated.

For user-defined functions (UDF), changes made to objects outside the database, such as related objects and library files, are not replicated. You need to set them up on the standby database in other ways.

The recovery history file (db2rhist.asc) and changes made to it are not automatically copied from the primary database to the standby database.

By issuing the RESTORE DATABASE command with the REPLACE HISTORY FILE option, you can place the original copy of the history file (obtained from the backup image of the primary database) on the standby database:

RESTORE DB KELLY REPLACE HISTORY FILE

After initializing HADR and then performing backup activities on the primary database, the history file on the standby database is out of date. However, a copy of the history file is stored in each backup image. You can update the history file on the standby database by using the following command to extract the history file from the backup image:

RESTORE DB KELLY HISTORY FILE

Do not use regular operating system commands to copy history files in the database directory from the primary database to the standby database. When copying, if the primary database is updating the history files, those files will be corrupted.

If a takeover operation is performed and the standby database has the latest history file, the backup and restore operations performed on the new primary database generate new records in the history file and are fully mixed with the records generated on the original primary database. If the history file is out of date or missing entries, automatic incremental recovery may not be possible; instead, you will need to perform a manual incremental recovery operation.

=

=

Synchronization method of HADR:

Currently in use: SUPERASYNC (Super Asynchronous)

# db2 update db cfg for devdb47 using hadr_syncmode SUPERASYNC

1. SYNC (synchronization) # the log is written to the master database, and if the master receives a reply from the slave database and has written to the slave database, it is successful to ensure that the master and slave are stored at the same time.

# this approach will avoid transaction loss as much as possible, but using this method will result in the longest transaction response time

2. NEARSYNC (near synchronization) # the log is written to the primary database, and the master receives a reply from the standby database and has been written to the standby database, so it is successful to ensure that the simultaneous storage of the primary and standby storage occurs only when two simultaneous failures occur and the target location does not transfer all received log data to non-volatile memory.

This method has shorter transaction response time than synchronization, but the ability to prevent transaction loss is also relatively low.

3. ASYNC (Asynchronous) # Log writes are considered successful only when log records have been written to the log file on the primary database and passed to the TCP layer of the primary system host, and there is no need to wait for confirmation from the standby database.

4. SUPERASYNC (Super Asynchronous) # HADR pairs that are never in a peer state or disconnected, and are considered successful once the data is written to the master

It has the shortest transaction response time, but if the primary system fails, the transaction is also most likely to be lost. This is useful if you do not want transactions to be blocked or experience long response times due to network outages or congestion

Several states of HADR synchronization:

Attachment: http://down.51cto.com/data/2366625

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.