oracle调整redo日志组步骤

本文档说明普通架构及本文档说明普通架构及 DG 架构下 调整架构下 调整 redo 日志组大小的方日志组大小的方 法 也可作为调整法 也可作为调整 redo 文件位置的方法 文件位置的方法 普通架构普通架构 1 创建 2 个新的日志组 4 5 SQL alter database add logfile group 4 oradata0 redo4a log oradata1 redo4b log size 128MB SQL alter database add logfile group 5 oradata1 redo5a log oradata2 redo5b log size 128MB 2 确认 redo 日志组状态 触发 redolog 归档 令 group1 2 3 状态为非 current selectGROUP SEQUENCE MEMBERS BYTES ARCHIVED status from v log alter system checkpoint local alter system switch logfile 多次 selectGROUP SEQUENCE MEMBERS BYTES ARCHIVED status from v log 3 确认 group1 2 3 状态为非 current 后 删除 group1 2 3 SQL alter database drop logfile group1 SQL alter database drop logfile group2 SQL alter database drop logfile group3 4 重命名 redo log 文件 慎用 rm 删除命令 selectGROUP SEQUENCE MEMBERS BYTES ARCHIVED status from v log mv oradata0 redo1a log oradata0 redo1a log bak mv oradata1 redo1b log oradata1 redo1b log bak mv oradata1 redo2a log oradata1 redo2a log bak mv oradata2 redo2b log oradata2 redo2b log bak mv oradata2 redo3a log oradata2 redo3a log bak mv oradata0 redo3b log oradata0 redo3b log bak 5 重新创建 3 个新的日志组 group1 2 3 SQL alter database add logfile group 1 oradata1 redo1a log oradata2 redo1b log size 128MB SQL alter database add logfile group 2 oradata2 redo2a log oradata3 redo2b log size 128MB SQL alter database add logfile group 3 oradata3 redo3a log oradata1 redo3b log size 128MB 6 确认 redo 日志组状态 触发 redolog 归档 令 group4 5 状态为非 current selectGROUP SEQUENCE MEMBERS BYTES ARCHIVED status from v log alter system switch logfile 多次 selectGROUP SEQUENCE MEMBERS BYTES ARCHIVED status from v log 7 确认 group4 5 状态为非 current 后 删除 group4 5 SQL alter database drop logfile group4 SQL alter database drop logfile group5 8 操作系统物理删除 redo log 文件 rm oradata0 redo4a log rm oradata1 redo4b log rm oradata1 redo5a log rm oradata2 redo5b log DG 架构架构 对于搭建了 DG 架构的数据库 如需调整 redo 日志组 需要主备数据库都同时调整 涉及 停止日志自动应用 对 standby 数据库的 redo 日志组进行 clear 操作等 1 standby 备机备机停止日志应用停止日志应用 SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL SQL select NAME DATABASE ROLE SWITCHOVER STATUS from v database 将 STANDBY FILE MANAGEMENT 设置为 MANUAL SQL alter system set standby file management manual SQL show parameter STANDBY FILE MANAGENT 2 primary 主机主机将将 STANDBY FILE MANAGEMENT 设置为设置为 MANUAL SQL alter system set standby file management manual SQL show parameter STANDBY FILE MANAGENT 3 primary 主机主机检查当前日志组检查当前日志组 SQL select group type member from v logfile GROUP TYPE MEMBER 1 ONLINE redolog1 redo1a log 1 ONLINE redolog2 redo1b log 2 ONLINE redolog2 redo2a log 2 ONLINE redolog3 redo2b log 3 ONLINE redolog3 redo3a log 3 ONLINE redolog1 redo3b log 4 STANDBY redolog1 redo4 log 5 STANDBY redolog2 redo5 log 6 STANDBY redolog3 redo6 log 7 STANDBY redolog1 redo7 log 10 rows selected 删除 standby redo log alter database drop logfile group 4 alter database drop logfile group 5 alter database drop logfile group 6 alter database drop logfile group 7 select group type member from v logfile 物理删除或重命名 4 5 6 7standby redo 日志组相关的文件 mv redolog1 redo4 log redolog1 redo4 log bakstb mv redolog2 redo5 log redolog2 redo5 log bakstb mv redolog3 redo6 log redolog3 redo6 log bakstb mv redolog1 redo7 log redolog1 redo7 log bakstb 重新添加 standby redo log ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 redolog1 stbredo7 log SIZE 300M ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 redolog2 stbredo8 log SIZE 300M ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 redolog3 stbredo9 log SIZE 300M ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 redolog1 stbredo10 log SIZE 300M select group type member from v logfile 4 primary 主机主机调整在线调整在线 redo 日志组日志组 添加 4 5 6 online redo log alter database add logfile group 4 redolog1 redo4a log redolog2 redo4b log size 300m alter database add logfile group 5 redolog2 redo5a log redolog3 redo5b log size 300m alter database add logfile group 6 redolog3 redo6a log redolog1 redo6b log size 300m select group type member from v logfile 删除 1 2 3 online redo log alter system checkpoint local alter system switch logfile select GROUP MEMBERS BYTES STATUS from v log 可多次执行 直至 1 2 3 组全为 inactive 删除 1 2 3 重做日志组 alter database drop logfile group 1 alter database drop logfile group 2 alter database drop logfile group 3 select group type member from v logfile 重命名旧的 online redo logfile mv redolog1 redo1a log redolog1 redo1a log bakold mv redolog2 redo1b log redolog2 redo1b log bakold mv redolog2 redo2a log redolog2 redo2a log bakold mv redolog3 redo2b log redolog3 redo2b log bakold mv redolog3 redo3a log redolog3 redo3a log bakold mv redolog1 redo3b log redolog1 redo3b log bakold 重新添加 1 2 3 online redo log alter database add logfile group 1 redolog1 redo1a log redolog2 redo1b log size 300m alter database add logfile group 2 redolog2 redo2a log redolog3 redo2b log size 300m alter database add logfile group 3 redolog3 redo3a log redolog1 redo3b log size 300m select group type member from v logfile 进行日志切换测试 alter system switch logfile select GROUP MEMBERS BYTES STATUS from v log 将 STANDBY FILE MANAGEMENT 设置为 AUTO SQL ALTER SYSTEM SET STANDBY FILE MANAGEMENT AUTO 5 standby 备机备机检查当前日志组检查当前日志组 SQL select group type member from v logfile GROUP TYPE MEMBER 1 ONLINE redolog1 redo1a log 1 ONLINE redolog2 redo1b log 2 ONLINE redolog2 redo2a log 2 ONLINE redolog3 redo2b log 3 ONLINE redolog3 redo3a log 3 ONLINE redolog1 redo3b log 4 STANDBY redolog1 redo4 log 5 STANDBY redolog2 redo5 log 6 STANDBY redolog3 redo6 log 7 STANDBY redolog1 redo7 log 10 rows selected 删除 standby redo log alter database drop logfile group 4 alter database drop logfile group 5 alter database drop logfile group 6 alter database drop logfile group 7 select group type member from v logfile 物理重命名 standby redo log mv redolog1 redo4 log redolog1 redo4 log bakstb mv redolog2 redo5 log redolog2 redo5 log bakstb mv redolog3 redo6 log redolog3 redo6 log bakstb mv redolog1 redo7 log redolog1 redo7 log bakstb 重新添加 standby redo log ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 redolog1 stbredo7 log SIZE 300M ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 redolog2 stbredo8 log SIZE 300M ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 redolog3 stbredo9 log SIZE 300M ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 redolog1 stbredo10 log SIZE 300M select group type member from v logfile 添加 4 5 6 online redo log alter database add logfile group 4 redolog1 redo4a log redolog2 redo4b log size 300m alter database add logfile group 5 redolog2 redo5a log redolog3 redo5b log size 300m alter database add logfile group 6 redolog3 redo6a log redolog1 redo6b log size 300m select group type member from v logfile 清理 1 2 3 online redo log SQL select group status from v log GROUP STATUS 1 CLEARING CURRENT 3 CLEARING 2 CLEARING 对于 clearing 状态的组 可按以下步骤删除 以 1 为例 ALTER DATABASE CLEAR LOGFILE GROUP 序号 ALTER DATABASE DROP LOGFILE GROUP 序号 对于 clearing current 状态的组 需要开启日志应用进行日志切换 待状态转化为 clearing 之后才能删除 Standby 备机重启日志应用 ALTER SYSTEM SET STANDBY FILE MANAGEMENT AUTO ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION 6 primary 主机主机进行几次的日志切换进行几次的日志切换 alter system switch logfile 7 standby 备机查看 redo 日志状态 SQL alter database recover managed standby database cancel SQL ALTER SYSTEM SET STANDBY FILE MANAGEMENT MANUAL SQL select group status from v log 确保之前状态为 clearing current 的 log 已经变为才 learing 状态 删除 1 2 3 中遗留的组 ALTER DATABASE CLEAR LOGFILE GROUP 序号 ALTER DATABASE DROP LOGFILE GROUP 序号 重命名旧的 online redo logfile mv redolog1 redo1a log redolog1 redo1a log bakold mv redolog2 redo1b log redolog2 redo1b log bakold mv redolog2 redo2a log redolog2 redo2a log bakold mv redolog3 redo2b log redolog3 redo2b log bakold mv redolog3 redo3a log redolog3 redo3a log bakold mv redolog1 redo3b log redolog1 redo3b log bakold 重新添加 1 2 3 online redo log alter database add logfile group