DM8主备集群中主备库的拆分与合并
环境准备
搭建一主两备数据库集群[三台服务器分别为dmdb1,dmdb2,dmdb3]
部分集群配置如下:
数据库软件安装目录:/opt/dmdbms
实例数据文件目录:/dmdata
归档日志文件目录:/dmarch
备份路径:/dmbak
数据库名:DMDB
服务名分别为:DMSERVER01,DMSERVER02,DMSERVER03
监视器查看状态
[dmdba@dmdb3 ~]$ /opt/dmdbms/bin/dmmonitor /dmdata/DMDB/dmmonitor_noc.ini
[monitor] 2023-03-27 12:50:38: DMMONITOR[4.0] V8
[monitor] 2023-03-27 12:50:38: DMMONITOR[4.0] IS READY.
[monitor] 2023-03-27 12:50:38: Received message from(DMSERVER03)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-03-27 12:50:38 OPEN OK DMSERVER03 OPEN STANDBY NULL 2 43953 43953
[monitor] 2023-03-27 12:50:38: Received message from(DMSERVER01)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-03-27 12:50:38 OPEN OK DMSERVER01 OPEN PRIMARY VALID 2 43954 43954
[monitor] 2023-03-27 12:50:38: Received message from(DMSERVER02)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-03-27 12:50:38 OPEN OK DMSERVER02 OPEN STANDBY VALID 2 43952 43952
添加数据
新建表空间及用户并赋权
CREATE TABLESPACE "TEST" DATAFILE '/dmdata/DMDB/TEST.DBF' SIZE 128 AUTOEXTEND ON NEXT 1 MAXSIZE 10240;
CREATE USER "TEST" IDENTIFIED BY "TEST123456" DEFAULT TABLESPACE TEST;
GRANT "RESOURCE","PUBLIC","SOI" TO TEST;
创建表 STUDENT 和 COURSE
CREATE TABLE TEST.STUDENT(SNO INT NOT NULL PRIMARY KEY, SNAME VARCHAR(20));
CREATE TABLE TEST.COURSE(CNO INT NOT NULL PRIMARY KEY, CNAME VARCHAR(30));
插入数据并提交
STUDENT 表
BEGIN
FOR I IN 1 .. 600 LOOP
INSERT INTO TEST.STUDENT(SNO,SNAME) VALUES (I, '学生'||I);
END LOOP;
END;
COURSE 表
BEGIN
FOR I IN 1 .. 100 LOOP
INSERT INTO TEST.COURSE(CNO,CNAME) VALUES (I, '课程'||I);
END LOOP;
END;
查询验证数据
SELECT COUNT(*) FROM TEST.STUDENT;
SELECT COUNT(*) FROM TEST.COURSE;
对主库进行在线备份
[dmdba@dmdb1 ~]$ disql
disql V8
username:
password:
Server[LOCALHOST:5236]:mode is primary, state is open
login used time : 2.247(ms)
SQL> BACKUP DATABASE FULL TO BAKFILE;
executed successfully
used time: 00:00:03.715. Execute id is 1300.
SQL> EXIT
[dmdba@dmdb1 ~]$ ll /dmdata/DMDB/bak/
total 4
drwxr-xr-x 2 dmdba dinstall 4096 Mar 27 13:14 DB_DMDB_FULL_20230327_131454_179902
DB_DMDB_FULL_20230327_131454_179902为生成的备份集目录
停止集群
停止守护进程
备库守护
[dmdba@dmdb3 ~]$ DmWatcherServiceDMSERVER03 stop
Stopping DmWatcherServiceDMSERVER03: [ OK ]
[dmdba@dmdb2 ~]$ DmWatcherServiceDMSERVER02 stop
Stopping DmWatcherServiceDMSERVER02: [ OK ]
主库守护
[dmdba@dmdb1 ~]$ DmWatcherServiceDMSERVER01 stop
Stopping DmWatcherServiceDMSERVER01: [ OK ]
停止数据库服务
主库
[dmdba@dmdb1 ~]$ DmServiceDMSERVER01 stop
Stopping DmServiceDMSERVER01: [ OK ]
备库
[dmdba@dmdb2 ~]$ DmServiceDMSERVER02 stop
Stopping DmServiceDMSERVER02: [ OK ]
[dmdba@dmdb3 ~]$ DmServiceDMSERVER03 stop
Stopping DmServiceDMSERVER03: [ OK ]
将主库改成单机做测试
启动主库,修改为单机模式
[dmdba@dmdb1 ~]$ DmServiceDMSERVER01 start
Starting DmServiceDMSERVER01: [ OK ]
[dmdba@dmdb1 ~]$ disql
disql V8
username:
password:
Server[LOCALHOST:5236]:mode is primary, state is mount
login used time : 4.265(ms)
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);
DMSQL executed successfully
used time: 7.552(ms). Execute id is 0.
SQL> ALTER DATABASE NORMAL;
executed successfully
used time: 3.715(ms). Execute id is 0.
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',0);
DMSQL executed successfully
used time: 5.655(ms). Execute id is 1.
SQL> ALTER DATABASE OPEN;
executed successfully
used time: 62.477(ms). Execute id is 0.
创建测试表
CREATE TABLE TEST.SC(SNO INT PRIMARY KEY, CNO INT);
插入数据并提交
BEGIN
FOR I IN 1 .. 600 LOOP
INSERT INTO TEST.SC(SNO,CNO) VALUES (I, DBMS_RANDOM.VALUE(1,100));
END LOOP;
COMMIT;
END;
/
查询验证数据
SELECT COUNT(*) FROM TEST.SC;
测试完成后将主库还原
首先停库
[dmdba@dmdb1 ~]$ DmServiceDMSERVER01 stop
Stopping DmServiceDMSERVER01: [ OK ]
利用步骤3生成的备份集将主库还原
[dmdba@dmdb1 ~]$ dmrman
dmrman V8
RMAN>
RMAN> RESTORE DATABASE '/dmdata/DMDB/dm.ini' FROM BACKUPSET '/dmdata/DMDB/bak/DB_DMDB_FULL_20230327_131454_179902'
RESTORE DATABASE '/dmdata/DMDB/dm.ini' FROM BACKUPSET '/dmdata/DMDB/bak/DB_DMDB_FULL_20230327_131454_179902'
file dm.key not found, use default license!
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:03.038
RMAN> RECOVER DATABASE '/dmdata/DMDB/dm.ini' FROM BACKUPSET '/dmdata/DMDB/bak/DB_DMDB_FULL_20230327_131454_179902'
RECOVER DATABASE '/dmdata/DMDB/dm.ini' FROM BACKUPSET '/dmdata/DMDB/bak/DB_DMDB_FULL_20230327_131454_179902'
Database mode = 1, oguid = 26180
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[45756], file_lsn[45756]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 00:00:02.409
RMAN> RECOVER DATABASE '/dmdata/DMDB/dm.ini' UPDATE DB_MAGIC
RECOVER DATABASE '/dmdata/DMDB/dm.ini' UPDATE DB_MAGIC
Database mode = 1, oguid = 26180
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[49668], file_lsn[49668]
recover successfully!
time used: 00:00:01.018
RMAN>
启动集群
启动数据库服务
[dmdba@dmdb1 ~]$ DmServiceDMSERVER01 start
Starting DmServiceDMSERVER01: [ OK ]
[dmdba@dmdb2 ~]$ DmServiceDMSERVER02 start
Starting DmServiceDMSERVER02: [ OK ]
[dmdba@dmdb3 ~]$ DmServiceDMSERVER03 start
Starting DmServiceDMSERVER03: [ OK ]
启动守护进程
[dmdba@dmdb1 ~]$ DmWatcherServiceDMSERVER01 start
Starting DmWatcherServiceDMSERVER01: [ OK ]
[dmdba@dmdb2 ~]$ DmWatcherServiceDMSERVER02 start
Starting DmWatcherServiceDMSERVER02: [ OK ]
[dmdba@dmdb3 ~]$ DmWatcherServiceDMSERVER03 start
Starting DmWatcherServiceDMSERVER03: [ OK ]
利用监视器查看集群状态
[dmdba@dmdb3 ~]$ /opt/dmdbms/bin/dmmonitor /dmdata/DMDB/dmmonitor_noc.ini
[monitor] 2023-03-27 13:52:44: DMMONITOR[4.0] V8
[monitor] 2023-03-27 13:52:44: DMMONITOR[4.0] IS READY.
[monitor] 2023-03-27 13:52:44: Received message from(DMSERVER03)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-03-27 13:52:44 OPEN OK DMSERVER03 OPEN STANDBY NULL 4 55557 55557
[monitor] 2023-03-27 13:52:44: Received message from(DMSERVER01)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-03-27 13:52:44 OPEN OK DMSERVER01 OPEN PRIMARY VALID 4 55558 55558
[monitor] 2023-03-27 13:52:44: Received message from(DMSERVER02)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-03-27 13:52:44 OPEN OK DMSERVER02 OPEN STANDBY VALID 4 55557 55557
查看数据是否为原始数据
SELECT COUNT(*) FROM TEST.STUDENT;
SELECT COUNT(*) FROM TEST.COURSE;
SELECT * FROM TEST.SC;
注意:此时应只有 STUDENT 表和 COURSE 表,而不会有 SC 表。
以上即 主备集群中将主库的拆分与合并,接下来是备库的拆分与合并。
再次在主库进行在线备份
[dmdba@dmdb1 ~]$ disql
disql V8
username:
password:
Server[LOCALHOST:5236]:mode is primary, state is open
login used time : 2.239(ms)
SQL>
SQL>
SQL> exit
[dmdba@dmdb1 ~]$ disql
disql V8
username:
password:
Server[LOCALHOST:5236]:mode is primary, state is open
login used time : 2.288(ms)
SQL> BACKUP DATABASE FULL TO BAKFILE2;
executed successfully
used time: 00:00:03.998. Execute id is 800.
SQL> EXIT
[dmdba@dmdb1 ~]$ ll /dmdata/DMDB/bak/
total 8
drwxr-xr-x 2 dmdba dinstall 4096 Mar 27 13:43 DB_DMDB_FULL_20230327_131454_179902
drwxr-xr-x 2 dmdba dinstall 4096 Mar 27 14:08 DB_DMDB_FULL_20230327_140811_181960
DB_DMDB_FULL_20230327_140811_181960为生成的备份集目录
再次停止集群
停止守护进程
[dmdba@dmdb3 ~]$ DmWatcherServiceDMSERVER03 stop
Stopping DmWatcherServiceDMSERVER03: [ OK ]
[dmdba@dmdb2 ~]$ DmWatcherServiceDMSERVER02 stop
Stopping DmWatcherServiceDMSERVER02: [ OK ]
[dmdba@dmdb1 ~]$ DmWatcherServiceDMSERVER01 stop
Stopping DmWatcherServiceDMSERVER01: [ OK ]
停止数据库服务
主库
[dmdba@dmdb1 ~]$ DmServiceDMSERVER01 stop
Stopping DmServiceDMSERVER01: [ OK ]
备库
[dmdba@dmdb2 ~]$ DmServiceDMSERVER02 stop
Stopping DmServiceDMSERVER02: [ OK ]
[dmdba@dmdb3 ~]$ DmServiceDMSERVER03 stop
Stopping DmServiceDMSERVER03: [ OK ]
将其中一备库改为单机做测试
启动备库,修改为单机模式
[dmdba@dmdb3 ~]$ DmServiceDMSERVER03 start
Starting DmServiceDMSERVER03: [ OK ]
[dmdba@dmdb3 ~]$ disql
disql V8
username:
password:
Server[LOCALHOST:5236]:mode is standby, state is mount
login used time : 4.088(ms)
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);
DMSQL executed successfully
used time: 7.701(ms). Execute id is 0.
SQL> ALTER DATABASE NORMAL;
executed successfully
used time: 11.530(ms). Execute id is 0.
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',0);
DMSQL executed successfully
used time: 5.639(ms). Execute id is 1.
SQL> ALTER DATABASE OPEN;
executed successfully
used time: 62.479(ms). Execute id is 0.
创建测试表
CREATE TABLE TEST.SC(SNO INT PRIMARY KEY, CNO INT);
插入数据并提交
BEGIN
FOR I IN 1 .. 600 LOOP
INSERT INTO TEST.SC(SNO,CNO) VALUES (I, DBMS_RANDOM.VALUE(1,100));
END LOOP;
COMMIT;
END;
/
查询验证数据
SELECT COUNT(*) FROM TEST.SC;
测试完成后,利用主库备份将备库还原
将步骤8生成的备份集从主库scp到备库
[dmdba@dmdb1 ~]$ scp -r /dmdata/DMDB/bak/DB_DMDB_FULL_20230327_140811_181960/ dmdba@dmdb3:/dmdata/DMDB/bak/
在备库上进行还原恢复
[dmdba@dmdb3 ~]$ dmrman
dmrman V8
RMAN> RESTORE DATABASE '/dmdata/DMDB/dm.ini' FROM BACKUPSET '/dmdata/DMDB/bak/DB_DMDB_FULL_20230327_140811_181960'
RESTORE DATABASE '/dmdata/DMDB/dm.ini' FROM BACKUPSET '/dmdata/DMDB/bak/DB_DMDB_FULL_20230327_140811_181960'
file dm.key not found, use default license!
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:03.021
RMAN> RECOVER DATABASE '/dmdata/DMDB/dm.ini' FROM BACKUPSET '/dmdata/DMDB/bak/DB_DMDB_FULL_20230327_140811_181960'
RECOVER DATABASE '/dmdata/DMDB/dm.ini' FROM BACKUPSET '/dmdata/DMDB/bak/DB_DMDB_FULL_20230327_140811_181960'
Database mode = 1, oguid = 26180
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[55871], file_lsn[55871]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 00:00:02.395
RMAN> RECOVER DATABASE '/dmdata/DMDB/dm.ini' UPDATE DB_MAGIC;
RECOVER DATABASE '/dmdata/DMDB/dm.ini' UPDATE DB_MAGIC;
Database mode = 1, oguid = 26180
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[55887], file_lsn[55887]
recover successfully!
time used: 00:00:01.017
启动集群
启动数据库服务
[dmdba@dmdb1 ~]$ DmServiceDMSERVER01 start
Starting DmServiceDMSERVER01: [ OK ]
[dmdba@dmdb2 ~]$ DmServiceDMSERVER02 start
Starting DmServiceDMSERVER02: [ OK ]
[dmdba@dmdb3 ~]$ DmServiceDMSERVER03 start
Starting DmServiceDMSERVER03: [ OK ]
将还原的备库状态由PRIMARY修改为STANDBY
[dmdba@dmdb3 ~]$ disql
disql V8
username:
password:
Server[LOCALHOST:5236]:mode is primary, state is mount
login used time : 4.057(ms)
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);
DMSQL executed successfully
used time: 7.500(ms). Execute id is 0.
SQL> ALTER DATABASE STANDBY;
executed successfully
used time: 4.071(ms). Execute id is 0.
SQL> EXIT
启动守护进程
[dmdba@dmdb1 ~]$ DmWatcherServiceDMSERVER01 start
Starting DmWatcherServiceDMSERVER01: [ OK ]
[dmdba@dmdb2 ~]$ DmWatcherServiceDMSERVER02 start
Starting DmWatcherServiceDMSERVER02: [ OK ]
[dmdba@dmdb3 ~]$ DmWatcherServiceDMSERVER03 start
Starting DmWatcherServiceDMSERVER03: [ OK ]
利用监视器查看集群状态
[dmdba@dmdb3 ~]$ /opt/dmdbms/bin/dmmonitor /dmdata/DMDB/dmmonitor_noc.ini
[monitor] 2023-03-27 14:32:01: DMMONITOR[4.0] V8
[monitor] 2023-03-27 14:32:01: DMMONITOR[4.0] IS READY.
[monitor] 2023-03-27 14:32:01: Received message from(DMSERVER03)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-03-27 14:32:01 OPEN OK DMSERVER03 OPEN STANDBY NULL 5 58829 58829
[monitor] 2023-03-27 14:32:01: Received message from(DMSERVER01)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-03-27 14:32:01 OPEN OK DMSERVER01 OPEN PRIMARY VALID 5 58830 58830
[monitor] 2023-03-27 14:32:01: Received message from(DMSERVER02)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-03-27 14:32:01 OPEN OK DMSERVER02 OPEN STANDBY VALID 5 58829 58829
查看数据是否为原始数据
SELECT COUNT(*) FROM TEST.STUDENT;
SELECT COUNT(*) FROM TEST.COURSE;
SELECT * FROM TEST.SC;