OceanBase的备份恢复流程和涉及日志归档的操作

最新推荐文章于 2025-10-03 05:04:03 发布

原创最新推荐文章于 2025-10-03 05:04:03 发布 · 2.3k 阅读

21 ·

CC 4.0 BY-SA版权

文章标签：

#数据库开发 #oceanbase #sql #阿里云

本文围绕OceanBase数据库展开，介绍了系统配置项查看、备份目标NFS共享目录设置及挂载权限注意事项。阐述了日志开启、查看备份状态、合并日志等操作，还提及停止日志的方法，包括强制取消命令，若不行则需重启集群，此外还有租户与资源查询、清空及备份记录查询等内容。

1.配置
查看系统配置项
show parameters like
备份目标 NFS共享目录
show parameters like ‘backup_dest’
注意设置挂载目录权限

2.操作
开启日志
ALTER SYSTEM ARCHIVELOG
查看日志备份状态
SELECT * FROM CDB_OB_BACKUP_ARCHIVELOG
合并日志
ALTER SYSTEM MAJOR FREEZE
查询合并状态
SELECT * FROM __all_zone WHERE name=‘merge_status’ order by gmt_modified asc
查询可恢复时间点
SELECT incarnation, log_archive_round, tenant_id, CASE WHEN min_first_time " +
"= ‘’ THEN usec_to_time(0) ELSE min_first_time END AS min_first_time, " +
"CASE WHEN max_next_time = ‘’ THEN usec_to_time(0) ELSE max_next_time END AS max_next_time, " +
"status, CASE WHEN max_next_time = ‘’ THEN 0 ELSE (time_to_usec(now()) - time_to_usec(max_next_time)) / 1000000 " +
"END AS delay, now(6) as check_time FROM CDB_OB_BACKUP_ARCHIVELOG_SUMMARY WHERE tenant_id = 1 " +
“AND log_archive_round = (SELECT MAX(log_archive_round) FROM CDB_OB_BACKUP_ARCHIVELOG_SUMMARY WHERE tenant_id = 1)”

3.停止
停止日志（清空 INTERRUPTED 的状态）
ALTER SYSTEM NOARCHIVELOG
有的时候此命令无法停止归档，需要用到下面这个强制命令
强制取消，会停止日志归档
ALTER SYSTEM CANCEL ALL BACKUP FORCE;
此时还不行的话，只能重启集群
查询租户与资源
SELECT * FROM oceanbase.gv $t e nan t; SE L ECT * FROM oce anba se . gv$ unit;
SELECT * FROM __all_unit_config;
清空租户与资源
DROP TENANT res1 FORCE;
DROP RESOURCE POOL restore_pool;
DROP RESOURCE UNIT box_unit;
查询备份记录
SELECT * FROM CDB_OB_BACKUP_SET_DETAILS order by BS_KEY desc limit 10;

mount挂载
showmount -e 11.11.15.210
sudo mount -tnfs4 -o rw,nfsvers=4.1,sync,lookupcache=positive,hard,timeo=600,wsize=1048576,rsize=1048576,namlen=255 11.11.15.210:/obbackup /obbackup