暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / OB425黑屏搭建集群以及三种方式搭建备库（包含主备切换）

OB425黑屏搭建集群以及三种方式搭建备库（包含主备切换）

原创 张瑞远 2024-11-22
479

本文是个超长篇的实操笔记，具体的命令和详情官网也都有，汇总和记录下。

黑屏创建OB集群

1.所需软件包

oceanbase-4.2.5.0-100000082024102022.el7.x86_64.rpm --observer软件包
obproxy-4.3.2.0-432024100811.el7.x86_64.rpm --obproxy软件包
tsar-master.zip --tsar软件包
t-oceanbase-antman-1.3.9-1932687.alios7.x86_64.rpm ---t-antman软件包
obclient-2.2.5-20231227133752.el7.x86_64.rpm ---客户端软件包

2.环境准备

环境按照observer准备初始化，这也是我使用antman的原因。

3.单节点搭建observer

安装前置准备

####安装observer软件包#########
rpm -ivh  oceanbase-4.2.5.0-100000082024102022.el7.x86_64.rpm
cd  /home/admin
chown -R admin:admin oceanbase
#####创建所需目录#########
su - admin
mkdir -p  /home/admin/oceanbase/store/obzry/
mkdir -p /data/1/obzry/{etc3,sstable,slog}
mkdir -p /data/log1/obzry/{clog,etc2}
#######创建软连接###########
for t in {etc3,sstable,slog};
do
ln -s /data/1/obzry/$t /home/admin/oceanbase/store/obzry/$t;
done

for t in {clog,etc2};
do
ln -s /data/log1/obzry/$t /home/admin/oceanbase/store/obzry/$t;
done
#########检查目录##########
cd  /home/admin/oceanbase
[admin@obzrycs01 oceanbase]$ tree store
store
└── obzry
    ├── clog -> /data/log1/obzry/clog
    ├── etc2 -> /data/log1/obzry/etc2
    ├── etc3 -> /data/1/obzry/etc3
    ├── slog -> /data/1/obzry/slog
    └── sstable -> /data/1/obzry/sstable

启动进程

Su - admin
cd  /home/admin/oceanbase && /home/admin/oceanbase/bin/observer -I 10.10.10.111 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store/obzry -r '10.10.10.111:2882:2881' -c 1 -n obzry -o "config_additional_dir=/data/1/obzry/etc3;/data/log1/obzry/etc2"
#########检查#########
[admin@obzrycs01 oceanbase]$ ps -ef|grep observer
admin      65306       1 46 15:26 ?        00:00:07 /home/admin/oceanbase/bin/observer -I 10.10.10.111 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store/obzry -r 10.10.10.111:2882:2881 -c 1 -n obzry -o config_additional_dir=/data/1/obzry/etc3;/data/log1/obzry/etc2
admin      65993   64118  0 15:26 pts/0    00:00:00 grep --color=auto observer
[admin@obzrycs01 oceanbase]$ netstat -ntlp
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -                   
tcp        0      0 0.0.0.0:2881            0.0.0.0:*               LISTEN      65306/observer      
tcp        0      0 0.0.0.0:2882            0.0.0.0:*               LISTEN      65306/observer      
tcp        0      0 127.0.0.1:199           0.0.0.0:*               LISTEN      -                   
tcp6       0      0 :::22                   :::*                    LISTEN      -                   
tcp6       0      0 :::2882                 :::*                    LISTEN      65306/observer

集群bootstrap

obclient -h127.0.0.1 -uroot -P2881
set session ob_query_timeout=720000000000;
alter system bootstrap zone 'zone1' server '10.10.10.111:2882';
obclient [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| LBACSYS            |
| mysql              |
| oceanbase          |
| ORAAUDITOR         |
| SYS                |
| sys_external_tbs   |
| test               |
+--------------------+
8 rows in set (0.003 sec)
####设置密码###########
obclient [(none)]> alter user root identified by 'weddvfD324@@@';
Query OK, 0 rows affected (0.036 sec)

########创建proxyro用户##########
create  user proxyro identified by "OxxxxxBxxx##123";
grant select  on *.* to proxyro;
###########查询sha1转化的密码（启动proxy的时候用）#############
select sha1('OxxxxxBxxx##123')
+------------------------------------------+
| sha1('OxxxxxBxxx##123')                  |
+------------------------------------------+
| 5b5b18b0f24996458bbcfd60575e0252bc0552bc |
+------------------------------------------+
obclient [(none)]> select sha1('OxxxxxBxxx##321');
+------------------------------------------+
| sha1('OxxxxxBxxx##321')                  |
+------------------------------------------+
| 40f912e7f41137f46136894d7daed35933b8fbf8 |
+------------------------------------------+
1 row in set (0.000 sec)

4.安装proxy

软件安装

####修改系统文件########
chmod  u+w /etc/sudoers
echo 'admin ALL=(ALL) NOPASSWD:ALL'  >> /etc/sudoers
chmod  u-w /etc/sudoers
#########解压软件############
rpm -ivh  obproxy-4.3.2.0-432024100811.el7.x86_64.rpm
#######创建软连接##########
[root@obzrycs01 home]# cd /opt/taobao/install/
[root@obzrycs01 install]# ls
obproxy-4.3.2.0
[root@obzrycs01 install]# ln -s obproxy-4.3.2.0 obproxy
chown -R admin:admin obproxy

启动proxy

su - admin
 cd /opt/taobao/install/obproxy 
./bin/obproxy -p 2883 -s 2885 -r '10.10.10.111:2881' -n obproxy_zry -o observer_sys_password=5b5b18b0f24996458bbcfd60575e0252bc0552bc,obproxy_sys_password=40f912e7f41137f46136894d7daed35933b8fbf8,enable_cluster_checkout=false,proxy_id=1,client_session_id_version=2 -c obzry

登录

obclient -h10.10.10.111 -uroot@sys#obzry -P2883 -p'weddvfD324@@@'

备集群搭建

1.环境准备

环境准备与上面操作一致，集群名称可以任意取

2.主库创建主租户和测试数据


CREATE RESOURCE UNIT S1_unit_config
MEMORY_SIZE = '100G',
MAX_CPU = 40, MIN_CPU = 40,
LOG_DISK_SIZE = '200G',
MAX_IOPS = 100000, MIN_IOPS = 100000, IOPS_WEIGHT=1;


CREATE RESOURCE POOL mq_pool_01 
UNIT='S1_unit_config', 
UNIT_NUM=1, 
ZONE_LIST=('zone1'); 


CREATE TENANT IF NOT EXISTS oracle_zry1 
PRIMARY_ZONE='zone1', 
RESOURCE_POOL_LIST=('mq_pool_01')
SET OB_TCP_INVITED_NODES='%',
ob_compatibility_mode='oracle';

obclient -h10.10.10.111 -usys@oracle_zry1#obzry -P2883
alter user sys identified by "weddvfD324@@@";      
obclient [SYS]> create table  zrycs (a varchar2(20),b int);
Query OK, 0 rows affected (0.132 sec)

obclient [SYS]> insert into  zrycs values ('张瑞远',150);
Query OK, 1 row affected (0.033 sec)

obclient [SYS]> 
obclient [SYS]> 
obclient [SYS]> commit;
Query OK, 0 rows affected (0.001 sec)

3.备集群搭建

（一）空白租户创建备集群

创建空备租户适用于主租户为新创建，或您可以确认该主租户当前拥有租户自创建后的完整日志的场景。

1.主库查看主租户上的日志流对比信息，sys租户执行。
obclient [oceanbase]> (SELECT LS_ID FROM oceanbase.CDB_OB_LS_HISTORY WHERE TENANT_ID = 1001) EXCEPT (SELECT LS_ID FROM oceanbase.CDB_OB_LS WHERE TENANT_ID = 1002);
Empty set (0.017 sec)

obclient [oceanbase]> 
obclient [oceanbase]> SELECT LS_ID, BEGIN_LSN FROM oceanbase.GV$OB_LOG_STAT WHERE TENANT_ID = 1002 AND ROLE = 'LEADER' ;
+-------+-----------+
| LS_ID | BEGIN_LSN |
+-------+-----------+
|     1 |         0 |
|  1001 |         0 |
+-------+-----------+
2 rows in set (0.006 sec)
BEGIN_LSN 表示当前日志流副本保存的最早的日志 LSN（Log Sequence Number），如果 BEGIN_LSN的值为 0，则表示当前日志流副本拥有自创建以来完整的日志。根据查询结果，日志流副本所对应的 BEGIN_LSN 的值为 0，表示当前日志流副本拥有自创建以来完整的日志；如果该租户的所有日志流副本对应的 BEGIN_LSN 的值均为 0，则表示该租户的所有日志流均拥有完整日志，可以通过 CREATE STANDBY TENANT 语句创建空备租户

计算备租户资源（不必完全按照）


obclient [oceanbase]> CALL DBMS_OB_LIMIT_CALCULATOR.CALCULATE_MIN_PHY_RES_NEEDED_BY_STANDBY_TENANT(PRIMARY_TENANT_ID => 1002, STANDBY_TENANT_UNIT_NUM => 1);
+------------------------+------------+
| PHYSICAL_RESOURCE_NAME | MIN_VALUE  |
+------------------------+------------+
| memstore               |          0 |
| memory                 | 4294967296 |
| data_disk              |          0 |
| clog_disk              | 2147483648 |
| cpu                    |          0 |
+------------------------+------------+
5 rows in set (0.007 sec)
Query OK, 0 rows affected (0.007 sec)

创建同步账号


obclient -h10.10.10.111 -usys@oracle_zry1#obzry -P2883 -p'weddvfD324@@@'

CREATE USER rep_user IDENTIFIED BY "rep_user123";
GRANT STANDBY_REPLICATION TO rep_user;

创建空白备租户资源

obclient -h10.10.10.222 -uroot@sys#obzry_bak -P2883 -p'weddvfD324@@@'
CREATE RESOURCE UNIT S1_unit_config
                MEMORY_SIZE = '100G',
                MAX_CPU = 40, MIN_CPU = 40,
                LOG_DISK_SIZE = '200G',
                MAX_IOPS = 100000, MIN_IOPS = 100000, IOPS_WEIGHT=1;


CREATE RESOURCE POOL mq_pool_01 
                UNIT='S1_unit_config', 
                UNIT_NUM=1, 
                ZONE_LIST=('zone1');

主库查询备库创建日志源需要的信息

obclient [SYS]> select  * from DBA_OB_ACCESS_POINT;
+-----------+-------------+---------------+----------+
| TENANT_ID | TENANT_NAME | SVR_IP        | SQL_PORT |
+-----------+-------------+---------------+----------+
|      1002 | oracle_zry1 | 10.10.10.111 |     2881 |
+-----------+-------------+---------------+----------+

备库执行创建备库操作


obclient [oceanbase]> CREATE STANDBY TENANT IF NOT EXISTS oracle_zry1 LOG_RESTORE_SOURCE = "SERVICE=10.10.10.111:2881 USER=rep_user@oracle_zry1 PASSWORD=rep_user123" RESOURCE_POOL_LIST=('mq_pool_01');
Query OK, 0 rows affected (14.958 sec)

检查租户状态

obclient [oceanbase]> SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'oracle_zry1';
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
| TENANT_NAME | TENANT_TYPE | CREATE_TIME                | STATUS | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
| oracle_zry1 | USER        | 2024-11-20 16:44:48.016641 | NORMAL | STANDBY     | 2024-11-20 16:46:57.541396 |
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
1 row in set (0.010 sec)
根据查询结果，TENANT_ROLE的值为 STANDBY, 且 STATUS 的值为 NORMAL，则表示备租户状态正常，备租户已创建成功。

验证备租户

obclient -h10.10.10.222 -usys@oracle_zry1#obzry_bak -P2883 -p'weddvfD324@@@'

obclient [SYS]> select  * from  zrycs;
+-----------+------+
| A         | B    |
+-----------+------+
| 张瑞远    |  150 |
+-----------+------+
1 row in set (0.041 sec)
obclient [SYS]> insert into  zrycs values ('张正赟',120);
ORA-16000: standby tenant is read only

主库执行

insert into  zrycs values ('张正赟',120);
commit

备租户查询

obclient [SYS]> select  * from  zrycs;
+-----------+------+
| A         | B    |
+-----------+------+
| 张瑞远    |  150 |
| 张正赟    |  120 |
+-----------+------+
2 rows in set (0.000 sec)

（二）使用备份恢复（带日志）功能创建备租户

归档介质使用 NFS，归档目的端路径为 /nfs_zry/zry425/sh_archive/

主租户下执行，或者主sys租户指定tenant

ALTER SYSTEM SET LOG_ARCHIVE_DEST = 'LOCATION=file:///nfs_zry/zry425/sh_archive';
ALTER SYSTEM ARCHIVELOG;
########ALTER SYSTEM ARCHIVELOG TENANT = mysql;
#####SELECT DEST_ID, ROUND_ID, DEST_NO, STATUS, CHECKPOINT_SCN, CHECKPOINT_SCN_DISPLAY, PATH FROM oceanbase.CDB_OB_ARCHIVELOG;
obclient [SYS]> SELECT DEST_ID, ROUND_ID, DEST_NO, STATUS, CHECKPOINT_SCN, CHECKPOINT_SCN_DISPLAY, PATH FROM sys.dba_OB_ARCHIVELOG;
+---------+----------+---------+-----------+---------------------+---------------------------------+--------------------------------+
| DEST_ID | ROUND_ID | DEST_NO | STATUS    | CHECKPOINT_SCN      | CHECKPOINT_SCN_DISPLAY          | PATH                           |
+---------+----------+---------+-----------+---------------------+---------------------------------+--------------------------------+
|    1001 |        1 |       0 | BEGINNING | 1732151691642371000 | 21-NOV-24 09.14.51.642371000 AM | file:///nfs_zry/zry425/sh_archive |
+---------+----------+---------+-----------+---------------------+---------------------------------+--------------------------------+
1 row in set (0.007 sec)

主租户查看主租户的归档进度

## SELECT * FROM oceanbase.CDB_OB_ARCHIVELOG\G 
obclient [SYS]> SELECT * FROM sys.dba_OB_ARCHIVELOG\G
*************************** 1. row ***************************
DEST_ID: 1001
ROUND_ID: 1
INCARNATION: 1
DEST_NO: 0
STATUS: DOING
START_SCN: 1732151691642371000
START_SCN_DISPLAY: 21-NOV-24 09.14.51.642371000 AM
CHECKPOINT_SCN: 1732151691642371001
CHECKPOINT_SCN_DISPLAY: 21-NOV-24 09.14.51.642371001 AM
COMPATIBLE: 1
BASE_PIECE_ID: 1
USED_PIECE_ID: 1
PIECE_SWITCH_INTERVAL: 86400000000
UNIT_SIZE: 1
COMPRESSION: none
INPUT_BYTES: 59202014
INPUT_BYTES_DISPLAY: 56.46MB
OUTPUT_BYTES: 59202014
OUTPUT_BYTES_DISPLAY: 56.46MB
COMPRESSION_RATIO: 1
DELETED_INPUT_BYTES: 0
DELETED_INPUT_BYTES_DISPLAY: 0MB
DELETED_OUTPUT_BYTES: 0
DELETED_OUTPUT_BYTES_DISPLAY: 0MB
COMMENT: NULL
PATH: file:///nfs_zry/zry425/sh_archive
1 row in set (0.033 sec)

设置主租户的备份目的端

###ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///data/1/sh_databackup' TENANT = mysql;
ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///nfs_zry/zry425/sh_databackup';
备份
###ALTER SYSTEM BACKUP TENANT = mysql;
主租户备份
ALTER SYSTEM BACKUP DATABASE;

--新增测试数据
insert into  zrycs values ('张芮遠',100);
commit;
确认数据备份状态
##SELECT * FROM oceanbase.CDB_OB_BACKUP_JOBS;
oracle:
SELECT * FROM SYS.DBA_OB_BACKUP_JOBS;
历史
##SELECT * FROM oceanbase.CDB_OB_BACKUP_JOB_HISTORY;
obclient [SYS]> SELECT * FROM SYS.DBA_OB_BACKUP_JOB_HISTORY;
+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+---------------------+---------------------+-----------+--------+---------+-------------+-----------------------------------+
| JOB_ID | INCARNATION | BACKUP_SET_ID | INITIATOR_TENANT_ID | INITIATOR_JOB_ID | EXECUTOR_TENANT_ID | PLUS_ARCHIVELOG | BACKUP_TYPE | JOB_LEVEL   | ENCRYPTION_MODE | PASSWD | START_TIMESTAMP     | END_TIMESTAMP       | STATUS    | RESULT | COMMENT | DESCRIPTION | PATH                              |
+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+---------------------+---------------------+-----------+--------+---------+-------------+-----------------------------------+
|      1 |           1 |             1 |                1002 |                0 | 1002               | OFF             | FULL        | USER_TENANT | NONE            | NULL   | 2024-11-21 09:43:37 | 2024-11-21 09:45:26 | COMPLETED |      0 | NULL    | NULL        | file:///nfs_zry/zry425/sh_databackup |
+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+---------------------+---------------------+-----------+--------+---------+-------------+-----------------------------------+
1 row in set (0.034 sec)

--（可选）主租户关闭归档模式

创建新备租户资源

obclient -h10.10.10.222 -uroot@sys#obzry_bak -P2883 -p'weddvfD324@@@'
CREATE RESOURCE UNIT S2_unit_config
                MEMORY_SIZE = '100G',
                MAX_CPU = 40, MIN_CPU = 40,
                LOG_DISK_SIZE = '200G',
                MAX_IOPS = 100000, MIN_IOPS = 100000, IOPS_WEIGHT=1;


CREATE RESOURCE POOL zry_pool_01 
                UNIT='S2_unit_config', 
                UNIT_NUM=1, 
                ZONE_LIST=('zone1');

主库sys租户查询


obclient [oceanbase]> select * from oceanbase.cdb_OB_ARCHIVELOG\G
*************************** 1. row ***************************
TENANT_ID: 1002
DEST_ID: 1001
ROUND_ID: 1
INCARNATION: 1
DEST_NO: 0
STATUS: DOING
START_SCN: 1732151691642371000
START_SCN_DISPLAY: 2024-11-21 09:14:51.642371
CHECKPOINT_SCN: 1732154211467090004
CHECKPOINT_SCN_DISPLAY: 2024-11-21 09:56:51.467090
COMPATIBLE: 1
BASE_PIECE_ID: 1
USED_PIECE_ID: 1
PIECE_SWITCH_INTERVAL: 86400000000
UNIT_SIZE: 1
COMPRESSION: none
INPUT_BYTES: 65875798
INPUT_BYTES_DISPLAY: 62.82MB
OUTPUT_BYTES: 65875798
OUTPUT_BYTES_DISPLAY: 62.82MB
COMPRESSION_RATIO: 1.00
DELETED_INPUT_BYTES: 0
DELETED_INPUT_BYTES_DISPLAY: 0.00MB
DELETED_OUTPUT_BYTES: 0
DELETED_OUTPUT_BYTES_DISPLAY: 0.00MB
COMMENT: 
PATH: file:///nfs_zry/zry425/sh_archive
1 row in set (0.017 sec)

从主租户的带有归档日志的完整数据集中恢复出备租户

ALTER SYSTEM RESTORE oracle_zry3 FROM 'file:///nfs_zry/zry425/sh_databackup,file:///nfs_zry/zry425/sh_archive' until time='2024-11-21 09:56:51.467090' WITH 'pool_list=zry_pool_01';

确认恢复是否完成


obclient [(none)]> SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN)  FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'oracle_zry3';
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
| TENANT_NAME | TENANT_TYPE | CREATE_TIME                | STATUS | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
| oracle_zry3 | USER        | 2024-11-21 10:00:34.100851 | NORMAL | STANDBY     | 2024-11-21 09:56:51.467090 |
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
1 row in set (0.025 sec)

设置日志恢复源

ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=10.10.10.111:2881 USER=rep_user@oracle_zry1 PASSWORD=rep_user123' TENANT = oracle_zry3;
设置成功后，系统租户（sys租户）或用户租户可以分别通过CDB_OB_LOG_RESTORE_SOURCE 视图和 DBA_OB_LOG_RESTORE_SOURCE 视图，确认日志恢复源是否修改成功


obclient [oceanbase]> select  * from  oceanbase.CDB_OB_LOG_RESTORE_SOURCE;
+-----------+------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| TENANT_ID | ID   | TYPE    | VALUE                                                                                                                                                                                                  | RECOVERY_UNTIL_SCN  |
+-----------+------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
|      1008 |    1 | SERVICE | IP_LIST=10.10.10.111:2881,USER=rep_user@oracle_zry1,PASSWORD=85AA3F6D6E11152BF7624AD553A454E209221D105D6DB782A787B9EF0AF865CD,TENANT_ID=1002,CLUSTER_ID=1,COMPATIBILITY_MODE=ORACLE,IS_ENCRYPTED=true | 4611686018427387903 |
|      1010 |    1 | SERVICE | IP_LIST=10.10.10.111:2881,USER=rep_user@oracle_zry1,PASSWORD=436D7278F9D06984854D2C9D178C8AA88225DE21A7088668F6C7DB9BEBC36FE0,TENANT_ID=1002,CLUSTER_ID=1,COMPATIBILITY_MODE=ORACLE,IS_ENCRYPTED=true | 1732154211467090000 |
+-----------+------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
2 rows in set (0.014 sec)

日志链（主库查询）

SELECT * FROM oceanbase.GV$OB_LOG_TRANSPORT_DEST_STAT;

验证同步

obclient -h10.10.10.222 -usys@oracle_zry3#obzry_bak -P2883 -p'weddvfD324@@@'
obclient [SYS]>  select  * from  zrycs ;      
+-----------+------+
| A         | B    |
+-----------+------+
| 张瑞远    |  150 |
| 张正赟    |  120 |
| 张芮遠    |  100 |
+-----------+------+
3 rows in set (0.015 sec)

主库：
obclient -h10.10.10.111 -usys@oracle_zry1#obzry -P2883 -p'weddvfD324@@@'
insert into zrycs values('张锐源','60');
commit;
备库：
obclient [SYS]> select  * from  zrycs ;  
+-----------+------+
| A         | B    |
+-----------+------+
| 张瑞远    |  150 |
| 张正赟    |  120 |
| 张芮遠    |  100 |
+-----------+------+
3 rows in set (0.001 sec)

设置日志恢复终点

##ALTER SYSTEM RECOVER STANDBY [TENANT = tenant_name] UNTIL UNLIMITED;
obclient [SYS]> ALTER SYSTEM RECOVER STANDBY  UNTIL UNLIMITED;
Query OK, 0 rows affected (0.006 sec)

obclient [SYS]> select  * from  zrycs ;  
+-----------+------+
| A         | B    |
+-----------+------+
| 张瑞远    |  150 |
| 张正赟    |  120 |
| 张芮遠    |  100 |
| 张锐源    |   60 |
+-----------+------+
4 rows in set (0.001 sec)

（三）使用 BACKUP DATABASE PLUS ARCHIVELOG 功能创建备租户

主租户开启归档模式并执行数据备份  --因上一步已经开启归档了就不重复了

主租户使用 BACKUP DATABASE PLUS ARCHIVELOG 功能执行数据备份

改下备份目录

##ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///data/1/sh_databackup1' TENANT = mysql;
##ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///nfs_zry/zry425/sh_databackup1';
ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///data/1/sh_databackup1';

##ALTER SYSTEM BACKUP TENANT = oracle_zry1 PLUS ARCHIVELOG; 
obclient [SYS]> ALTER SYSTEM BACKUP DATABASE PLUS ARCHIVELOG;
Query OK, 0 rows affected (0.009 sec)

确认数据备份状态

obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_BACKUP_JOBS;
oracle:
obclient [SYS]> SELECT * FROM SYS.DBA_OB_BACKUP_JOBS;
+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+---------------------+---------------+--------+--------+---------+-------------+-------------------------------+
| JOB_ID | INCARNATION | BACKUP_SET_ID | INITIATOR_TENANT_ID | INITIATOR_JOB_ID | EXECUTOR_TENANT_ID | PLUS_ARCHIVELOG | BACKUP_TYPE | JOB_LEVEL   | ENCRYPTION_MODE | PASSWD | START_TIMESTAMP     | END_TIMESTAMP | STATUS | RESULT | COMMENT | DESCRIPTION | PATH                          |
+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+---------------------+---------------+--------+--------+---------+-------------+-------------------------------+
|      3 |           1 |             3 |                1002 |                0 | 1002               | ON              | FULL        | USER_TENANT | NONE            | NULL   | 2024-11-21 10:38:10 | NULL          | DOING  |      0 | NULL    | NULL        | file:///data/1/sh_databackup1 |
+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+---------------------+---------------+--------+--------+---------+-------------+-------------------------------+
1 row in set (0.007 sec)

查询备份历史

SELECT * FROM oceanbase.CDB_OB_BACKUP_JOB_HISTORY;
*************************** 3. row ***************************
JOB_ID: 3
INCARNATION: 1
BACKUP_SET_ID: 3
INITIATOR_TENANT_ID: 1002
INITIATOR_JOB_ID: 0
EXECUTOR_TENANT_ID: 1002
PLUS_ARCHIVELOG: ON
BACKUP_TYPE: FULL
JOB_LEVEL: USER_TENANT
ENCRYPTION_MODE: NONE
PASSWD: NULL
START_TIMESTAMP: 2024-11-21 10:38:10
END_TIMESTAMP: 2024-11-21 10:42:02
STATUS: COMPLETED
RESULT: 0
COMMENT: NULL
DESCRIPTION: NULL
PATH: file:///data/1/sh_databackup1
3 rows in set (0.007 sec)

文件打包传输到备集群的机器

su - admin
cd /data/1/
tar -czvf sh_databackup1.tar.gz sh_databackup1
传输到备集群解压
tar -xzvf sh_databackup1.tar.gz

--（可选）主租户关闭归档模式

备库创建资源

obclient -h10.10.10.222 -uroot@sys#obzry_bak -P2883 -p'weddvfD324@@@'
CREATE RESOURCE UNIT S3_unit_config
                MEMORY_SIZE = '100G',
                MAX_CPU = 40, MIN_CPU = 40,
                LOG_DISK_SIZE = '200G',
                MAX_IOPS = 100000, MIN_IOPS = 100000, IOPS_WEIGHT=1;

CREATE RESOURCE POOL zry_pool_02 
                UNIT='S3_unit_config', 
                UNIT_NUM=1, 
                ZONE_LIST=('zone1');

执行恢复

ALTER SYSTEM RESTORE oracle_zry4 FROM 'file:///data/1/sh_databackup1' WITH 'pool_list=zry_pool_02';

确认恢复是否完成


obclient [oceanbase]>  SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN)  FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'oracle_zry4';
+-------------+-------------+----------------------------+---------+-------------+----------------------------+
| TENANT_NAME | TENANT_TYPE | CREATE_TIME                | STATUS  | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-------------+----------------------------+---------+-------------+----------------------------+
| oracle_zry4 | USER        | 2024-11-21 11:09:44.383241 | RESTORE | RESTORE     | 1970-01-01 08:00:00.000000 |
+-------------+-------------+----------------------------+---------+-------------+----------------------------+
1 row in set (0.022 sec)

obclient [oceanbase]>  SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN)  FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'oracle_zry4';
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
| TENANT_NAME | TENANT_TYPE | CREATE_TIME                | STATUS | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
| oracle_zry4 | USER        | 2024-11-21 11:09:44.383241 | NORMAL | STANDBY     | 2024-11-21 11:01:48.133777 |
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
1 row in set (0.013 sec)

设置日志恢复源


ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=10.10.10.111:2881 USER=rep_user@oracle_zry1 PASSWORD=rep_user123' TENANT = oracle_zry4;
设置成功后，系统租户（sys 租户）或用户租户可以分别通过 CDB_OB_LOG_RESTORE_SOURCE 视图和 DBA_OB_LOG_RESTORE_SOURCE 视图确认日志恢复源是否修改成功


obclient [oceanbase]> select  * from  oceanbase.CDB_OB_LOG_RESTORE_SOURCE;
+-----------+------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| TENANT_ID | ID   | TYPE    | VALUE                                                                                                                                                                                                  | RECOVERY_UNTIL_SCN  |
+-----------+------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
|      1008 |    1 | SERVICE | IP_LIST=10.10.10.111:2881,USER=rep_user@oracle_zry1,PASSWORD=85AA3F6D6E11152BF7624AD553A454E209221D105D6DB782A787B9EF0AF865CD,TENANT_ID=1002,CLUSTER_ID=1,COMPATIBILITY_MODE=ORACLE,IS_ENCRYPTED=true | 4611686018427387903 |
|      1010 |    1 | SERVICE | IP_LIST=10.10.10.111:2881,USER=rep_user@oracle_zry1,PASSWORD=436D7278F9D06984854D2C9D178C8AA88225DE21A7088668F6C7DB9BEBC36FE0,TENANT_ID=1002,CLUSTER_ID=1,COMPATIBILITY_MODE=ORACLE,IS_ENCRYPTED=true | 4611686018427387903 |
|      1012 |    1 | SERVICE | IP_LIST=10.10.10.111:2881,USER=rep_user@oracle_zry1,PASSWORD=8C0C773131341E21819EFBA6B94DCC8D7384815F2CDD1D977507EB458382FBF6,TENANT_ID=1002,CLUSTER_ID=1,COMPATIBILITY_MODE=ORACLE,IS_ENCRYPTED=true | 1732158108133777000 |
+-----------+------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+

日志链主库查询


obclient [(none)]> SELECT * FROM oceanbase.GV$OB_LOG_TRANSPORT_DEST_STAT;
+-----------+---------------+----------+-------+---------------+------------+------------------+-------------+----------------------------+----------------------------+------------------+--------------------------+----------------------+----------------------+---------------------+--------------------------+------------------------+---------------------------+---------------------------+-----------------------------+
| TENANT_ID | SVR_IP        | SVR_PORT | LS_ID | CLIENT_IP     | CLIENT_PID | CLIENT_TENANT_ID | CLIENT_TYPE | START_SERVE_TIME           | LAST_SERVE_TIME            | LAST_READ_SOURCE | LAST_REQUEST_TYPE        | LAST_REQUEST_LOG_LSN | LAST_REQUEST_LOG_SCN | LAST_FAILED_REQUEST | AVG_REQUEST_PROCESS_TIME | AVG_REQUEST_QUEUE_TIME | AVG_REQUEST_READ_LOG_TIME | AVG_REQUEST_READ_LOG_SIZE | AVG_LOG_TRANSPORT_BANDWIDTH |
+-----------+---------------+----------+-------+---------------+------------+------------------+-------------+----------------------------+----------------------------+------------------+--------------------------+----------------------+----------------------+---------------------+--------------------------+------------------------+---------------------------+---------------------------+-----------------------------+
|      1002 | 10.10.10.111 |     2882 |     1 | 10.10.10.222 |      39515 |             1010 | STANDBY     | 2024-11-21 10:12:48.841463 | 2024-11-21 11:14:51.480241 | ONLINE           | SEQUENTIAL_READ_PARALLEL |            127647744 |  1732158891331754000 | NULL                |                        5 |                    104 |                         4 |                      1904 | 36.07KB/S                   |
|      1002 | 10.10.10.111 |     2882 |     1 | 10.10.10.222 |      39515 |             1008 | STANDBY     | 2024-11-20 16:55:28.110781 | 2024-11-21 11:14:51.480395 | ONLINE           | SEQUENTIAL_READ_PARALLEL |            127647744 |  1732158891331754000 | NULL                |                        4 |                     97 |                         3 |                      1904 | 36.07KB/S                   |
|      1002 | 10.10.10.111 |     2882 |  1001 | 10.10.10.222 |      39515 |             1008 | STANDBY     | 2024-11-20 16:55:33.277167 | 2024-11-21 11:14:51.480119 | ONLINE           | SEQUENTIAL_READ_PARALLEL |             84992000 |  1732158891256454000 | NULL                |                        4 |                     89 |                         3 |                      2085 | 39.51KB/S                   |
|      1002 | 10.10.10.111 |     2882 |  1001 | 10.10.10.222 |      39515 |             1010 | STANDBY     | 2024-11-21 10:12:48.842884 | 2024-11-21 11:14:51.480332 | ONLINE           | SEQUENTIAL_READ_PARALLEL |             84992000 |  1732158891256454000 | NULL                |                        4 |                    102 |                         4 |                      2085 | 39.51KB/S                   |
+-----------+---------------+----------+-------+---------------+------------+------------------+-------------+----------------------------+----------------------------+------------------+--------------------------+----------------------+----------------------+---------------------+--------------------------+------------------------+---------------------------+---------------------------+-----------------------------+

验证主备同步


obclient -h10.10.10.222 -usys@oracle_zry4#obzry_bak -P2883 -p'weddvfD324@@@'
obclient [SYS]>  select  * from  zrycs ; 
+-----------+------+
| A         | B    |
+-----------+------+
| 张瑞远    |  150 |
| 张正赟    |  120 |
| 张芮遠    |  100 |
| 张锐源    |   60 |
+-----------+------+
4 rows in set (0.030 sec)
主库：
obclient -h10.10.10.111 -usys@oracle_zry1#obzry -P2883 -p'weddvfD324@@@'
insert into zrycs values('张蕊缘','240');
commit;
备库：
obclient [SYS]> select  * from  zrycs ;  
+-----------+------+
| A         | B    |
+-----------+------+
| 张瑞远    |  150 |
| 张正赟    |  120 |
| 张芮遠    |  100 |
| 张锐源    |   60 |
+-----------+------+
4 rows in set (0.001 sec)

设置日志恢复终点


##ALTER SYSTEM RECOVER STANDBY [TENANT = tenant_name] UNTIL UNLIMITED;
obclient [SYS]> ALTER SYSTEM RECOVER STANDBY  UNTIL UNLIMITED;
Query OK, 0 rows affected (0.005 sec)

obclient [SYS]> select  * from  zrycs ;                       
+-----------+------+
| A         | B    |
+-----------+------+
| 张瑞远    |  150 |
| 张正赟    |  120 |
| 张芮遠    |  100 |
| 张锐源    |   60 |
| 张蕊缘    |  240 |
+-----------+------+
5 rows in set (0.001 sec)

主备切换

（一）Switchover


1.  基于网络传输的切换

>主切成备

登录主租户或主租户所在集群的 sys 租户

执行 SWITCHOVER TO STANDBY VERIFY 命令，验证 SWITCHOVER 命令是否可以执行成功。
###obclient> ALTER SYSTEM SWITCHOVER TO STANDBY TENANT = mysql VERIFY;
obclient [SYS]> ALTER SYSTEM SWITCHOVER TO STANDBY VERIFY;
Query OK, 0 rows affected (0.005 sec)

验证通过后，执行主切备命令，将主租户切换为备租户。

主租户所在集群的 sys 租户将主租户切换为备租户

###ALTER SYSTEM SWITCHOVER TO STANDBY TENANT = mysql;
ALTER SYSTEM SWITCHOVER TO STANDBY;

查询 DBA_OB_TENANTS 视图，确认主租户是否已切换为备租户


obclient [(none)]> SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS;
+-------------+-------------+-------------+-------------------+
| TENANT_NAME | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-------------+-------------+-------------+-------------------+
| sys         | SYS         | PRIMARY     | NORMAL            |
| META$1002   | META        | PRIMARY     | NORMAL            |
| oracle_zry1 | USER        | STANDBY     | NORMAL            |
+-------------+-------------+-------------+-------------------+
3 rows in set (0.014 sec)
根据查询结果，TENANT_ROLE为 STANDBY，且 SWITCHOVER_STATUS 为 NORMAL，则表示主切备成功。
>将备租户切换为主租户。（基于网络）

obclient [oceanbase]> ALTER SYSTEM SWITCHOVER TO PRIMARY TENANT = oracle_zry2 VERIFY;
Query OK, 0 rows affected (0.046 sec)
###obclient> ALTER SYSTEM SWITCHOVER TO PRIMARY VERIFY;

验证通过后，执行备切主命令，将备租户切换为主租户

备租户所在集群的 sys 租户将备租户切换为主租户


obclient [oceanbase]> ALTER SYSTEM SWITCHOVER TO PRIMARY TENANT = oracle_zry2;
Query OK, 0 rows affected (0.172 sec)
########ALTER SYSTEM SWITCHOVER TO PRIMARY;

查询DBA_OB_TENANTS 视图，确认备租户是否已切换为主租户

备租户所在集群的 sys 租户查询视图

###########SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM SYS.DBA_OB_TENANTS;obclient [oceanbase]> SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS;+-------------+-------------+-------------+-------------------+| TENANT_NAME | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |+-------------+-------------+-------------+-------------------+| sys         | SYS         | PRIMARY     | NORMAL            || META$1008   | META        | PRIMARY     | NORMAL            || oracle_zry2 | USER        | PRIMARY     | NORMAL            || META$1010   | META        | PRIMARY     | NORMAL            || oracle_zry3 | USER        | STANDBY     | NORMAL            || META$1012   | META        | PRIMARY     | NORMAL            || oracle_zry4 | USER        | STANDBY     | NORMAL            |+-------------+-------------+-------------+-------------------+7 rows in set (0.013 sec)
根据查询结果可知，主租户的TENANT_ROLE 已变为 PRIMARY，且 SWITCHOVER_STATUS 变为 NORMAL，则表示备切主成功。

为原主租户设置日志恢复源

设置日志恢复源的操作可以在主备切换操作之前执行，也可以在主备切换操作之后执行。

原主租户所在集群的 sys 租户设置原主租户的日志恢复源

obclient [oceanbase]> ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=10.10.10.222:2881 USER=rep_user@oracle_zry2 PASSWORD=rep_user123' TENANT = oracle_zry1;
Query OK, 0 rows affected (0.083 sec)

设置日志恢复终点


##ALTER SYSTEM RECOVER STANDBY [TENANT = tenant_name] UNTIL UNLIMITED;
obclient [SYS]> ALTER SYSTEM RECOVER STANDBY  UNTIL UNLIMITED;
Query OK, 0 rows affected (0.004 sec)

验证主备同步

###新主obclient -h10.10.10.222 -usys@oracle_zry2#obzry_bak -P2883 -p'weddvfD324@@@' obclient [SYS]> select * from  zry;Empty set (0.008 sec)obclient [SYS]> insert into zry values('张瑞远','150');Query OK, 1 row affected (0.002 sec)obclient [SYS]> commit;Query OK, 0 rows affected (0.002 sec)####原主obclient -h10.10.10.111 -usys@oracle_zry1#obzry -P2883 -p'weddvfD324@@@' obclient [SYS]> select  * from  zry;+-----------+------+| A         | B    |+-----------+------+| 张瑞远    |  150 |+-----------+------+1 row in set (0.009 sec)###其他备obclient -h10.10.10.222 -usys@oracle_zry3#obzry_bak -P2883 -p'weddvfD324@@@' obclient [SYS]> select  * from  zry;+-----------+------+| A         | B    |+-----------+------+| 张瑞远    |  150 |+-----------+------+1 row in set (0.008 sec)

2.  基于日志的主备切换

这部分没有单独测试（在下一部分级联环境切换中有相关步骤）步骤整理如下

备租户开启日志归档模式--> 将主租户切换为备租户--> 将备租户切换为主租户1.备租户开启归档模式

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/1/standby2_archive2/ BINDING=optional' TENANT = standby2;

ALTER SYSTEM ARCHIVELOG TENANT = standby2;

备租户所在集群的 sys 租户查询视图

SELECT DEST_ID, ROUND_ID, DEST_NO, STATUS, CHECKPOINT_SCN, CHECKPOINT_SCN_DISPLAY, PATH FROM oceanbase.CDB_OB_ARCHIVELOG;

根据查询结果，STATUS 的值为 DOING，表示归档处于正常工作状态，CHECKPOINT_SCN 和 CHECKPOINT_SCN_DISPLAY 为当前的归档进度信息。



2.将主租户切换为备租户

ALTER SYSTEM SWITCHOVER TO STANDBY VERIFY;ALTER SYSTEM SWITCHOVER TO STANDBY;

查询DBA_OB_TENANTS 视图，确认主租户是否已切换为备租户。

主租户所在集群的 sys 租户查询视图

SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUSFROM oceanbase.DBA_OB_TENANTSWHERE TENANT_NAME='mysql2'

主租户的TENANT_ROLE 已变为 STANDBY，且 SWITCHOVER_STATUS 变为 NORMAL，则表示主切备成功。

查询V$OB_ARCHIVE_DEST_STATUS 视图中的 SYNCHRONIZED 字段，等待主租户归档完成。

基于日志归档的物理备库场景中，备租户在切换为主租户之前，需要从原主租户的日志归档上读取完整的日志。

由于日志归档为异步同步模式，因此在原主租户切换为备租户后，需要检查原主租户上的日志归档是否完整。

SELECT * FROM SYS.V$OB_ARCHIVE_DEST_STATUS WHERE TENANT_ID = 1004;

根据查询结果，如果SYNCHRONIZED 字段显示为 YES，则表示主租户已归档完成。



3.将备租户切换为主租户

ALTER SYSTEM SWITCHOVER TO PRIMARY VERIFY;ALTER SYSTEM SWITCHOVER TO PRIMARY;

查询DBA_OB_TENANTS 视图，确认备租户是否已切换为主租户。

备租户所在集群的 sys 租户查询视图

obclient> SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUSFROM oceanbase.DBA_OB_TENANTSWHERE TENANT_NAME='standby2';

设置新备租户（原主租户）的恢复源，接收新主租户的归档日志。

如果原主租户在执行切换操作前，未设置过日志恢复源，则其日志同步位点会停留在执行切换操作的时间点。因此，需要为原主租户设置日志恢复源，且日志恢复源指向新的主租户。

新备租户设置本租户的恢复源

ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=file:///data/1/standby2/archive2' TENANT = mysql2;

原主租户所在集群的 sys 租户查询视图

SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS, SCN_TO_TIMESTAMP(SYNC_SCN), RECOVERY_UNTIL_SCN FROM oceanbase.DBA_OB_TENANTS;

（二）Faildover

注意事项：

  1. 执行 Failover 时，要求备租户所有日志流的副本均在线，否则需要等待对应的副本永久下线。
  2. 备租户或备租户所在集群的sys 租户可以分别通过DBA_OB_LS 视图或 CDB_OB_LS 视图查询所有日志流副本是否在线，有关日志流副本的详细说明信息
  3. 执行 Failover 时，不会检查对应主租户的状态，因此除主租户故障的容灾需求外，Failover 操作也可以用于独立的一个主租户在某一个时间点的快照并用于后续业务验证的需求场景。
  4. 不允许在通过服务名建立的 Session 中执行 Failover 命令。
  5. Failover 操作只会对日志文件做修改，不会对数据文件做修改。由于 OceanBase 数据库的每个租户均有多个日志流，而 Failover 操作需要在操作执行完成后达到数据一致的状态，故系统会选择所有日志流的同步位点中 SCN 最小的值作为Failover 的执行位点。执行Failover操作后，租户下的所有日志流都会统一回退到该位点。

操作前测试

obclient -h10.10.10.222 -usys@oracle_zry3#obzry_bak -P2883 -p'weddvfD324@@@'
obclient [SYS]> insert into zry values('张惢渊','120');
ORA-16000: standby tenant is read only

执行 ACTIVATE STANDBY VERIFY 命令，验证 ACTIVATE STANDBY 命令可以执行成功后执行failedover命令。


obclient [oceanbase]> ALTER SYSTEM ACTIVATE STANDBY TENANT = oracle_zry3 VERIFY;
Query OK, 0 rows affected (0.005 sec)

obclient [oceanbase]> ALTER SYSTEM ACTIVATE STANDBY TENANT = oracle_zry3;
Query OK, 0 rows affected (0.131 sec)

检查

备租户查询视图

obclient [oceanbase]> SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS;
+-------------+-------------+-------------+-------------------+
| TENANT_NAME | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-------------+-------------+-------------+-------------------+
| sys         | SYS         | PRIMARY     | NORMAL            |
| META$1008   | META        | PRIMARY     | NORMAL            |
| oracle_zry2 | USER        | STANDBY     | NORMAL            |
| META$1010   | META        | PRIMARY     | NORMAL            |
| oracle_zry3 | USER        | PRIMARY     | NORMAL            |
| META$1012   | META        | PRIMARY     | NORMAL            |
| oracle_zry4 | USER        | PRIMARY     | NORMAL            |
+-------------+-------------+-------------+-------------------+
7 rows in set (0.014 sec)
#####验证
obclient [SYS]> insert into zry values('张惢渊','100');
Query OK, 1 row affected (0.001 sec)

Failover 后的注意事项

  1. 由于 Failover 操作会将所有日志流已同步的数据恢复到一致性位点，保证该位点之前的所有日志流的数据是完整的。故执行 Failover 操作后不支持原主租户降备后接入成为新主租户的备租户。
  2. 对于第三方备租户，取决于备租户同步的日志，如果同步的日志超过一致性位点，则不支持接入作为新主租户的备租户。为了便于管理，不建议第三方备租户接入作为新主租户的备租户。

（三）特殊的主备切换方案

主要测试下网络和日志的混合级联备库的切换

现在架构如下

oracle_zry2#obzry_bak(主) -> oracle_zry1#obzry(网络传输) -> oracle_zry4#obzry_bak(日志传输)

目标是切换oracle_zry4#obzry_bak为新主 oracle_zry2#obzry_bak为备，从oracle_zry1#obzry网络传输

完成切换后的架构如下

oracle_zry4#obzry_bak(新主) -> oracle_zry1#obzry(日志传输) -> oracle_zry2#obzry_bak(网络传输)

需要按照如下步骤变更

1.oracle_zry4#obzry_bak（二级备）开启归档

ALTER SYSTEM SET LOG_ARCHIVE_DEST = 'LOCATION=file:///nfs_zry/zry425/cs_archive' TENANT = oracle_zry4;
ALTER SYSTEM ARCHIVELOG TENANT = oracle_zry4;
obclient [oceanbase]> SELECT DEST_ID, ROUND_ID, DEST_NO, STATUS, CHECKPOINT_SCN, CHECKPOINT_SCN_DISPLAY, PATH FROM oceanbase.CDB_OB_ARCHIVELOG;
+---------+----------+---------+---------+----------------+------------------------+--------------------------------+
| DEST_ID | ROUND_ID | DEST_NO | STATUS  | CHECKPOINT_SCN | CHECKPOINT_SCN_DISPLAY | PATH                           |
+---------+----------+---------+---------+----------------+------------------------+--------------------------------+
|    1001 |        1 |       0 | PREPARE |              0 | NULL                   | file:///nfs_zry/zry425/cs_archive |
+---------+----------+---------+---------+----------------+------------------------+--------------------------------+
1 row in set (0.012 sec)

2.oracle_zry2#obzry_bak （主）  切换备

ALTER SYSTEM SWITCHOVER TO STANDBY TENANT = oracle_zry2 VERIFY;
Query OK, 0 rows affected (0.003 sec)
ALTER SYSTEM SWITCHOVER TO STANDBY TENANT = oracle_zry2;
obclient [oceanbase]> SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS;
+-------------+-------------+-------------+-------------------+
| TENANT_NAME | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-------------+-------------+-------------+-------------------+
| sys         | SYS         | PRIMARY     | NORMAL            |
| META$1008   | META        | PRIMARY     | NORMAL            |
| oracle_zry2 | USER        | STANDBY     | NORMAL            |
| META$1010   | META        | PRIMARY     | NORMAL            |
| oracle_zry3 | USER        | STANDBY     | NORMAL            |
| META$1012   | META        | PRIMARY     | NORMAL            |
| oracle_zry4 | USER        | STANDBY     | NORMAL            |
+-------------+-------------+-------------+-------------------+
7 rows in set (0.014 sec)

3.oracle_zry1#obzry（一级备） 确认日志完整

obclient [SYS]> SELECT * FROM SYS.V$OB_ARCHIVE_DEST_STATUS WHERE TENANT_ID = 1002;
+-----------+---------+--------------------------------+--------+---------------------+--------------+---------+
| TENANT_ID | DEST_ID | PATH                           | STATUS | CHECKPOINT_SCN      | SYNCHRONIZED | COMMENT |
+-----------+---------+--------------------------------+--------+---------------------+--------------+---------+
|      1002 |    1001 | file:///nfs_zry/zry425/sh_archive | DOING  | 1732180488893078000 | NO           | NULL    |
+-----------+---------+--------------------------------+--------+---------------------+--------------+---------+
1 row in set (0.039 sec)

如果 SYNCHRONIZED 字段显示为 YES，则表示主租户已归档完成


obclient [SYS]> SELECT * FROM SYS.V$OB_ARCHIVE_DEST_STATUS WHERE TENANT_ID = 1002;
+-----------+---------+--------------------------------+--------+---------------------+--------------+---------+
| TENANT_ID | DEST_ID | PATH                           | STATUS | CHECKPOINT_SCN      | SYNCHRONIZED | COMMENT |
+-----------+---------+--------------------------------+--------+---------------------+--------------+---------+
|      1002 |    1001 | file:///nfs_zry/zry425/sh_archive | DOING  | 1732180520476199000 | YES          | NULL    |
+-----------+---------+--------------------------------+--------+---------------------+--------------+---------+
1 row in set (0.026 sec)

4.oracle_zry1#obzry（一级备） 修改日志恢复源到 oracle_zry4#obzry_bak（二级备）

ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'location=file:///nfs_zry/zry425/cs_archive' TENANT = oracle_zry1;
ALTER SYSTEM RECOVER STANDBY TENANT = oracle_zry1 UNTIL UNLIMITED;

5.oracle_zry2#obzry_bak(主)  修改日志恢复源到oracle_zry1#obzry（一级备）


ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=10.10.10.111:2881 USER=rep_user@oracle_zry1 PASSWORD=rep_user123' TENANT = oracle_zry2;
ALTER SYSTEM RECOVER STANDBY TENANT = oracle_zry2 UNTIL UNLIMITED;

6.oracle_zry4#obzry_bak（二级备） 切换主

obclient [oceanbase]> ALTER SYSTEM SWITCHOVER TO PRIMARY TENANT = oracle_zry4 VERIFY;
Query OK, 0 rows affected (0.109 sec)

obclient [oceanbase]> ALTER SYSTEM SWITCHOVER TO PRIMARY TENANT = oracle_zry4;
Query OK, 0 rows affected (0.440 sec)
obclient [oceanbase]> SELECT TENANT_NAME, TENANT_TYPE, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS;
+-------------+-------------+-------------+-------------------+
| TENANT_NAME | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-------------+-------------+-------------+-------------------+
| sys         | SYS         | PRIMARY     | NORMAL            |
| META$1008   | META        | PRIMARY     | NORMAL            |
| oracle_zry2 | USER        | STANDBY     | NORMAL            |
| META$1010   | META        | PRIMARY     | NORMAL            |
| oracle_zry3 | USER        | STANDBY     | NORMAL            |
| META$1012   | META        | PRIMARY     | NORMAL            |
| oracle_zry4 | USER        | PRIMARY     | NORMAL            |
+-------------+-------------+-------------+-------------------+
7 rows in set (0.014 sec)

7.验证

####（新主）
obclient -h10.10.10.222 -usys@oracle_zry4#obzry_bak -P2883 -p'weddvfD324@@@' 
obclient [SYS]>  insert into zry values('张正赟','120');
Query OK, 1 row affected (0.001 sec)

obclient [SYS]> commit;
Query OK, 0 rows affected (0.002 sec)
#####(新一级备)
--确认位点更新
select  TENANT_NAME,TENANT_ID,TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN) from  oceanbase.dba_ob_Tenants where TENANT_NAME like 'oracle_zry%'; 
+-------------+-----------+-------------+----------------------------+
| TENANT_NAME | TENANT_ID | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-----------+-------------+----------------------------+
| oracle_zry1 |      1002 | STANDBY     | 2024-11-21 17:24:55.577904 |
+-------------+-----------+-------------+----------------------------+
1 row in set (0.014 sec)
obclient -h10.10.10.111 -usys@oracle_zry1#obzry -P2883 -p'weddvfD324@@@' 
obclient [SYS]> select  * from  zry;
+-----------+------+
| A         | B    |
+-----------+------+
| 张正赟    |  120 |
| 张瑞远    |  150 |
+-----------+------+
2 rows in set (0.001 sec)
#####(新二级备)
obclient -h10.10.10.222 -usys@oracle_zry2#obzry_bak -P2883 -p'weddvfD324@@@' 
obclient [SYS]> select  * from  zry;
+-----------+------+
| A         | B    |
+-----------+------+
| 张正赟    |  120 |
| 张瑞远    |  150 |
+-----------+------+
2 rows in set (0.002 sec)
黑屏搭建OB以及proxy单节点，三种方式搭建备库，以及两种主备切换方式还有混合级联备库的切换，到这里就记录完了。

行之所向，莫问远方

oceanbaseselectlogscnoracle
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论