Oracle RAC+DG 调整redo/standby log file

2023年 11月 16日 37.5k 0

Oracle RAC+DG 调整redo/standby log file

Oracle 12.2 RAC+DG ,其中主库为两节点RAC,备库为single
调整redo/standby log file大小到 1g。
规划主库调整 online 为 6+6 组 1g,online 为 7+7 组 1g,备库同主库数量及大小一致:12组 online 、14组 standby,均为 1g

– 整体步骤:

1、备库增加 1g 的 standby redo,删除老的 standby redo;
2、主库增加 1g 的 online redo,删除老的 online redo;
3、备库增加 1g online redo,删除老的 online redo;
4、主库增加 1g 的 standby redo,删除老的 standby redo;

– 注意事项

1、确认ASM、LVM等磁盘空间充足;
2、调整完成后,启动mrp0前,还原 standby_file_management 为 auto;

– 操作步骤、命令

– 检查 online & standby 日志组状态

col first_change# for 99999999999999;
col next_change# for 99999999999999999999999999999;
col SEQUENCE# for 99999999;
SELECT distinct f.TYPE,l.THREAD#,l.GROUP#,l.SEQUENCE#,l.BYTES/1024/1024 AS SIZE_MB,l.STATUS,l.FIRST_CHANGE#,l.FIRST_TIME,l.NEXT_CHANGE# FROM V$LOG l join v$logfile f on l.GROUP# = f.GROUP# union all select distinct f.TYPE,s.THREAD#,s.GROUP#,null,s.BYTES/1024/1024 MB,s.STATUS,null,null,null from v$standby_log s join v$logfile f on s.GROUP# = f.GROUP# order by 1,3,2;

– 查看自动管理文件设置

SQL> show parameter create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string +DATA
db_create_online_log_dest_1 string +DATA
db_create_online_log_dest_2 string +FRA

– 调整 standby_file_management 为 manual

show parameter standby_file_management
alter system set standby_file_management=MANUAL scope=both;
show parameter standby_file_management

– 断开备库备库日志应用

select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

一、备库调整 standby 日志组

– 添加 14组 1g 大小的 standby

-- alter system set db_create_online_log_dest_1='/oradata/';
alter database add standby logfile group 51 size 1g;
alter database add standby logfile group 52 size 1g;
alter database add standby logfile group 53 size 1g;
alter database add standby logfile group 54 size 1g;
alter database add standby logfile group 55 size 1g;
alter database add standby logfile group 56 size 1g;
alter database add standby logfile group 57 size 1g;
alter database add standby logfile group 58 size 1g;
alter database add standby logfile group 59 size 1g;
alter database add standby logfile group 60 size 1g;
alter database add standby logfile group 61 size 1g;
alter database add standby logfile group 62 size 1g;
alter database add standby logfile group 63 size 1g;
alter database add standby logfile group 64 size 1g;

-- 删除所有 UNASSIGNED 状态的已有 standby, ACTIVE 先不处理
alter database drop standby logfile group 10;
alter database drop standby logfile group 11;
alter database drop standby logfile group 12;
……

二、主库调整 online redo

– 删除非 ACTIVE 或 CURRENT 的 online redo,每个实例至少留 2 组限制,可添加新文件后继续删除

alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
……

– 每个实例分别添加 6 组 1g 大小的 online redo(注意先添加不在使用的group,尽量保证 group 1-12 即可)

alter database add logfile thread 1 group 1 ('+DATA','+FRA') size 1g;
alter database add logfile thread 2 group 2 ('+DATA','+FRA') size 1g;
alter database add logfile thread 1 group 3 ('+DATA','+FRA') size 1g;
alter database add logfile thread 2 group 4 ('+DATA','+FRA') size 1g;
alter database add logfile thread 1 group 5 ('+DATA','+FRA') size 1g;
alter database add logfile thread 2 group 6 ('+DATA','+FRA') size 1g;
alter database add logfile thread 1 group 7 ('+DATA','+FRA') size 1g;
alter database add logfile thread 2 group 8 ('+DATA','+FRA') size 1g;
alter database add logfile thread 1 group 9 ('+DATA','+FRA') size 1g;
alter database add logfile thread 2 group 10 ('+DATA','+FRA') size 1g;
alter database add logfile thread 1 group 11 ('+DATA','+FRA') size 1g;
alter database add logfile thread 2 group 12 ('+DATA','+FRA') size 1g;

三、主库处理 ACTIVE 或 CURRENT 的 online redo,及对应的备库 standby

-- 主库切换redo,使 current 到新增加日志组上,并查看备库 ACTIVE standby 为新增加的日志组
alter system switch logfile;
-- 做检查点,刷新redo,使未调整日志状态为 INACTIVE
alter system checkpoint;

同上一、二步骤,操作主、备库剩余日志组

四、备库添加 12 组新 online redo,删除原日志组(循环操作,尽量保证日志组编号 1-12)

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
……

-- alter system set db_create_online_log_dest_1='/oradata/';
alter database add logfile group 1 size 1g;
alter database add logfile group 2 size 1g;
alter database add logfile group 3 size 1g;
alter database add logfile group 4 size 1g;
alter database add logfile group 5 size 1g;
alter database add logfile group 6 size 1g;
alter database add logfile group 7 size 1g;
alter database add logfile group 8 size 1g;
alter database add logfile group 9 size 1g;
alter database add logfile group 10 size 1g;
alter database add logfile group 11 size 1g;
alter database add logfile group 12 size 1g;

五、主库调整 standby(新增group,每组1个文件即可)

– 添加新的 standby 日志组

alter database add standby logfile thread 1 group 51 ('+FRA') size 1g;
alter database add standby logfile thread 2 group 52 ('+FRA') size 1g;
alter database add standby logfile thread 1 group 53 ('+FRA') size 1g;
alter database add standby logfile thread 2 group 54 ('+FRA') size 1g;
alter database add standby logfile thread 1 group 55 ('+FRA') size 1g;
alter database add standby logfile thread 2 group 56 ('+FRA') size 1g;
alter database add standby logfile thread 1 group 57 ('+FRA') size 1g;
alter database add standby logfile thread 2 group 58 ('+FRA') size 1g;
alter database add standby logfile thread 1 group 59 ('+FRA') size 1g;
alter database add standby logfile thread 2 group 60 ('+FRA') size 1g;
alter database add standby logfile thread 1 group 61 ('+FRA') size 1g;
alter database add standby logfile thread 2 group 62 ('+FRA') size 1g;
alter database add standby logfile thread 1 group 63 ('+FRA') size 1g;
alter database add standby logfile thread 2 group 64 ('+FRA') size 1g;

-- 删除老的 standby 日志组
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;
……

六、参数还原及开启实时同步

– 还原 standby_file_management 参数配置

show parameter standby_file_management
alter system set standby_file_management=AUTO scope=both;
show parameter standby_file_management

– 启用备库日志实时应用

select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
alter database recover managed standby database using current logfile disconnect from session;
select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论