华为GaussDB T数据库主备物理复制搭建过程
【摘要】 本文主要介绍了华为GaussDB T数据库主备物理复制的搭建过程。
背景
主备部署
01
主备机安装数据库软件
[root@DB1 GaussDB_100_1.0.1-DATABASE-KYLINREDHAT-64bit]#./install.py -U omm:dbgrp -R gaussdb/software -D gaussdata/data -C LSNR_ADDR=127.0.0.1,192.168.1.3Checking runner.Checking parameters.End check parameters.Checking user.End check user.Checking old install.End check old install.Checking kernel parameters.Checking directory.Checking integrality of run file...Decompressing run file.Setting user env.Checking data dir and config fileInitialize db instance.Creating database.Creating database succeed.Changing file permission due to security audit.Install successfully, for more detail information see home/omm/zengineinstall.log.02
参数调整
ALTER SYSTEM SET CBO = on;ALTER SYSTEM SET data_buffer_size = 8G;ALTER SYSTEM SET shared_pool_size=4G;ALTER SYSTEM SET temp_buffer_size=2G;ALTER SYSTEM SET buf_pool_num=8;ALTER SYSTEM SET large_pool_size=256M;ALTER SYSTEM SET cr_pool_size=1G;ALTER SYSTEM SET cr_pool_count=8;ALTER SYSTEM SET max_worker_threads=550;ALTER SYSTEM SET optimized_worker_threads=500;ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME UNLIMITED;alter tablespace temp drop datafile '/gaussdata/data/data/temp1_02';alter database datafile '/gaussdata/data/data/temp1_01' resize 20G;alter tablespace users drop datafile '/gaussdata/data/data/user5';alter tablespace users drop datafile '/gaussdata/data/data/user4';alter tablespace users drop datafile '/gaussdata/data/data/user3';alter tablespace users drop datafile '/gaussdata/data/data/user2';alter database datafile '/gaussdata/data/data/user1' resize 100M;alter tablespace temp2 drop datafile '/gaussdata/data/data/temp2_02';alter database datafile '/gaussdata/data/data/temp2_01' resize 1G;alter database datafile '/gaussdata/data/data/temp2_undo' resize 1G;alter database datafile '/gaussdata/data/data/sysaux' resize 5G;alter database datafile '/gaussdata/data/data/system' resize 5G;alter database datafile '/gaussdata/data/data/temp1_01' autoextend off;alter database datafile '/gaussdata/data/data/user1' autoextend off;alter database datafile '/gaussdata/data/data/temp2_01' autoextend off;alter database datafile '/gaussdata/data/data/sysaux' autoextend off;alter system set log_archive_dest_1='location=/gaussarch';alter database delete archivelog all;
03
调整redo
alter database drop logfile('/gaussdata/data/data/log6');alter database drop logfile('/gaussdata/data/data/log5');alter database drop logfile('/gaussdata/data/data/log4');alter database add logfile('/gaussdata/data/data/log4' size 512M,'/gaussdata/data/data/log5' size 512M,'/gaussdata/data/data/log6' size 512M);alter system switch logfile;alter database drop logfile('/gaussdata/data/data/log3');alter database drop logfile('/gaussdata/data/data/log2');alter database drop logfile('/gaussdata/data/data/log1');alter database add logfile('/gaussdata/data/data/log1' size 512M,'/gaussdata/data/data/log2' size 512M,'/gaussdata/data/data/log3' size 512M);04
主库设置远程归档目录
alter system set log_archive_dest_2='local_host=192.168.1.1 service=192.168.1.2:1898 sync affirm primary_role';alter system set repl_port=1898;
05
备库上设置远程归档目录
alter system set log_archive_dest_2='local_host=192.168.1.2 service=192.168.1.1:1898 sync affirm primary_role';alter system set repl_port=1898;
06
重建备库
zctl.py -t start -m nomount[omm@DB2 data]$ zsql as sysdba -qconnected.SQL> build database;GS-00323, RFS is not ready, can not get peer roleSQL> build database;Succeed.
07
测试同步
SQL> create table test(id int);Succeed.SQL> desc test;Name Null? Type ----------------------------------- -------- ------------------------------------ID BINARY_INTEGER数据保护模式
01
最大保护模式
02
最大可用模式
03
最大性能模式
主备switchover
SQL> select DATABASE_ROLE,DATABASE_CONDITION,SWITCHOVER_STATUS from dv_database;DATABASE_ROLE DATABASE_CONDITION SWITCHOVER_STATUS ------------------------------ ------------------ --------------------PRIMARY NORMAL NOT ALLOWED
SQL> select DATABASE_ROLE,DATABASE_CONDITION,SWITCHOVER_STATUS from dv_database;DATABASE_ROLE DATABASE_CONDITION SWITCHOVER_STATUS ------------------------------ ------------------ --------------------PHYSICAL_STANDBY NORMAL TO PRIMARYSQL> alter database switchover;Succeed.SQL> select DATABASE_ROLE,DATABASE_CONDITION,SWITCHOVER_STATUS from dv_database;DATABASE_ROLE DATABASE_CONDITION SWITCHOVER_STATUS ------------------------------ ------------------ --------------------PRIMARY NORMAL NOT ALLOWED
一主两备
SQL> alter system set log_archive_dest_state_2=defer;Succeed.SQL> alter system set log_archive_dest_2='local_host=192.168.1.2 service=192.168.1.1:1898 sync affirm primary_role';Succeed.SQL> alter system set log_archive_dest_state_2=ENABLE;Succeed.主备failover
SQL> alter database failover;Succeed.SQL> select DATABASE_ROLE,DATABASE_CONDITION,SWITCHOVER_STATUS from dv_database;DATABASE_ROLE DATABASE_CONDITION SWITCHOVER_STATUS ------------------------------ ------------------ --------------------PRIMARY NORMAL NOT ALLOWED
SQL> select DATABASE_ROLE,DATABASE_CONDITION,SWITCHOVER_STATUS from dv_database;DATABASE_ROLE DATABASE_CONDITION SWITCHOVER_STATUS------------------------------ ------------------ --------------------PHYSICAL_STANDBY NORMAL TO PRIMARY
SQL> select DATABASE_ROLE,DATABASE_CONDITION,SWITCHOVER_STATUS from dv_database;DATABASE_ROLE DATABASE_CONDITION SWITCHOVER_STATUS------------------------------ ------------------ --------------------PRIMARY NORMAL NOT ALLOWED
SQL> alter database convert to physical standby;Succeed.SQL> select DATABASE_ROLE,DATABASE_CONDITION,SWITCHOVER_STATUS from dv_database;DATABASE_ROLE DATABASE_CONDITION SWITCHOVER_STATUS ------------------------------ ------------------ --------------------PHYSICAL_STANDBY NORMAL TO PRIMARY
监控视图
SQL> select * from dv_ha_sync_info;THREAD# STATUS LOCAL_HOST ROLE_VALID NET_MODE PEER_HOST PEER_PORT LOCAL_SEND_POINT PEER_FLUSH_POINT PEER_BUILDING LOCAL_LFN LOCAL_LSN PEER_LFN PEER_LSN FLUSH_LAG REPLAY_LAG------------ -------------------- ---------------------------------------------------------------- ------------- -------- ---------------------------------------------------------------- ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------1 SHIFTING 192.168.1.1 PRIMARY_ROLE SYNC 192.168.1.2 1898 0-22/85 0-22/86 FALSE 126144 2566593 126144 2566593 0 02 SHIFTING 192.168.1.1 PRIMARY_ROLE SYNC 192.168.1.3 1898 0-22/85 0-22/86 FALSE 126144 2566593 126144 2566593 0 0SQL> select * from dv_archive_gaps;THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#------------ -------------------------------- --------------------------------0 rows fetched.SQL> col FLUSH_POINT for a20;SQL> col PRIMARY_CURR_POINT for a20;SQL> col REPLAY_POINT for a20;SQL> set line 200;SQL> select * from dv_standbys;PROCESS STATUS RESETLOG_ID THREAD# SEQUENCE# FLUSH_POINT PRIMARY_CURR_POINT REPLAY_POINT-------------------- -------------------- ------------ -------------------- ------------ -------------------- -------------------- --------------------RFS STREAMING 3 fffbb02f5f20 32 3-32/8137/146945 3-32/8136/146944MRP OPEN 3 fffbb0345f20 32 3-32/8137/146945ARCH OPEN 3 fffbbd1f5f20 31FAL 0 0
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）