Oracle RAC+DG 调整redo/standby log file

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;