oracle_dataguard下扩日志文件

原创于 2021-09-01 16:20:19 发布 · 396 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

ORACLE工具箱专栏收录该内容

14 篇文章

订阅专栏

本文详细描述了如何在Oracle数据库中通过Data Guard进行日志文件大小调整，包括增加过渡日志、删除旧日志组、重建日志组及standby日志的管理，涉及alter database语句和维护过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景

dataguard下的将日志文件从50M 扩大到60M

主库上联机日志操作

主库增加过渡日志 2 组，大小1G左右

alter database add logfile thread 1 group 201 ('+DATA/orcl/onlinelog/redo201.log','+ARCH/orcl/onlinelog/redo201.log') size 50M;

alter database add logfile thread 1 group 202 ('+DATA/orcl/onlinelog/redo202.log','+ARCH/orcl/onlinelog/redo202.log') size 50M;

alter database add logfile thread 2 group 203 ('+DATA/orcl/onlinelog/redo203.log','+ARCH/orcl/onlinelog/redo203.log') size 50M;

alter database add logfile thread 2 group 204 ('+DATA/orcl/onlinelog/redo204.log','+ARCH/orcl/onlinelog/redo204.log') size 50M;

删除INACTIVE 或 UNUSED的日志组

alter database drop logfile group 1;

select * from v$log; --查询哪个日志组状态为INACTIVE 或 UNUSED

alter system archive log current; --切换日志文件

alter system checkpoint;--提交到磁盘

备注：删除完检查目录下文件是否已删掉，可能需要手工删除文件

删除后重新建日志组

alter database add logfile thread 1 group 1 ('+DATA/orcl/onlinelog/redo01.log','+ARCH/orcl/onlinelog/redo01.log') size 60M;

alter database add logfile thread 1 group 2 ('+DATA/orcl/onlinelog/redo02.log','+ARCH/orcl/onlinelog/redo02.log') size 60M;

alter database add logfile thread 1 group 3 ('+DATA/orcl/onlinelog/redo03.log','+ARCH/orcl/onlinelog/redo03.log') size 60M;

alter database add logfile thread 1 group 4 ('+DATA/orcl/onlinelog/redo04.log','+ARCH/orcl/onlinelog/redo04.log') size 60M;

alter database add logfile thread 2 group 5 ('+DATA/orcl/onlinelog/redo05.log','+ARCH/orcl/onlinelog/redo05.log') size 60M;

alter database add logfile thread 2 group 6 ('+DATA/orcl/onlinelog/redo06.log','+ARCH/orcl/onlinelog/redo06.log') size 60M;

alter database add logfile thread 2 group 7 ('+DATA/orcl/onlinelog/redo07.log','+ARCH/orcl/onlinelog/redo07.log') size 60M;

alter database add logfile thread 2 group 8 ('+DATA/orcl/onlinelog/redo08.log','+ARCH/orcl/onlinelog/redo08.log') size 60M;

删除过渡文件

alter database drop logfile group 204;

select * from v$log; --查询哪个日志组状态为INACTIVE 或 UNUSED

alter system archive log current; --切换日志文件

alter system checkpoint;--提交到磁盘

主库上standby日志操作

删除所有日志

alter database drop standby logfile group 11;

alter database drop standby logfile group 12;

alter database drop standby logfile group 13;

alter database drop standby logfile group 14;

alter database drop standby logfile group 15;

alter database drop standby logfile group 16;

alter database drop standby logfile group 17;

alter database drop standby logfile group 18;

alter database drop standby logfile group 19;

alter database drop standby logfile group 20;

select * from v$standby_log;--验证是否已经删除

新增日志

alter database add standby logfile thread 1 group 11 '+DATA/orcl/onlinelog/standby11.dbf' size 60M;

alter database add standby logfile thread 1 group 12 '+DATA/orcl/onlinelog/standby12.dbf' size 60M;

alter database add standby logfile thread 1 group 13 '+DATA/orcl/onlinelog/standby13.dbf' size 60M;

alter database add standby logfile thread 1 group 14 '+DATA/orcl/onlinelog/standby14.dbf' size 60M;

alter database add standby logfile thread 1 group 15 '+DATA/orcl/onlinelog/standby15.dbf' size 60M;

alter database add standby logfile thread 2 group 16 '+DATA/orcl/onlinelog/standby16.dbf' size 60M;

alter database add standby logfile thread 2 group 17 '+DATA/orcl/onlinelog/standby17.dbf' size 60M;

alter database add standby logfile thread 2 group 18 '+DATA/orcl/onlinelog/standby18.dbf' size 60M;

alter database add standby logfile thread 2 group 19 '+DATA/orcl/onlinelog/standby19.dbf' size 60M;

alter database add standby logfile thread 2 group 20 '+DATA/orcl/onlinelog/standby20.dbf' size 60M;

按主库建立备库联机日志

改为手动并停止复制

alter system set STANDBY_FILE_MANAGEMENT= manual;

alter database recover managed standby database cancel;

建联机日志（thread group编号保持一致）

alter database add logfile thread 1 group 1 ('/u01/oradata/onlinelog/redo01.log','/u01/archive/onlinelog/redo01.log') size 60M;

alter database add logfile thread 1 group 2 ('/u01/oradata/onlinelog/redo02.log','/u01/archive/onlinelog/redo02.log') size 60M;

alter database add logfile thread 1 group 3 ('/u01/oradata/onlinelog/redo03.log','/u01/archive/onlinelog/redo03.log') size 60M;

alter database add logfile thread 1 group 4 ('/u01/oradata/onlinelog/redo04.log','/u01/archive/onlinelog/redo04.log') size 60M;

alter database add logfile thread 2 group 5 ('/u01/oradata/onlinelog/redo05.log','/u01/archive/onlinelog/redo05.log') size 60M;

alter database add logfile thread 2 group 6 ('/u01/oradata/onlinelog/redo06.log','/u01/archive/onlinelog/redo06.log') size 60M;

alter database add logfile thread 2 group 7 ('/u01/oradata/onlinelog/redo07.log','/u01/archive/onlinelog/redo07.log') size 60M;

alter database add logfile thread 2 group 8 ('/u01/oradata/onlinelog/redo08.log','/u01/archive/onlinelog/redo08.log') size 60M;

删除的时候状态如果为clearing，需要如下语句

alter database clear logfile group 1;

alter database drop logfile group 1;

alter database clear logfile group 2;

alter database drop logfile group 2;

alter database clear logfile group 3;

alter database drop logfile group 3;

alter database clear logfile group 4;

alter database drop logfile group 4;

按主库建立备库上standby日志

删除所有日志

alter database drop standby logfile group 11;

alter database drop standby logfile group 12;

alter database drop standby logfile group 13;

alter database drop standby logfile group 14;

alter database drop standby logfile group 15;

alter database drop standby logfile group 16;

alter database drop standby logfile group 17;

alter database drop standby logfile group 18;

alter database drop standby logfile group 19;

alter database drop standby logfile group 20;

备注：删除完检查目录下文件是否已删掉，可能需要手工删除文件

新增日志

alter database add standby logfile thread 1 group 11 '/u01/oradata/onlinelog/standby11.dbf' size 60M;

alter database add standby logfile thread 1 group 12 '/u01/oradata/onlinelog/standby12.dbf' size 60M;

alter database add standby logfile thread 1 group 13 '/u01/oradata/onlinelog/standby13.dbf' size 60M;

alter database add standby logfile thread 1 group 14 '/u01/oradata/onlinelog/standby14.dbf' size 60M;

alter database add standby logfile thread 1 group 15 '/u01/oradata/onlinelog/standby15.dbf' size 60M;

alter database add standby logfile thread 2 group 16 '/u01/oradata/onlinelog/standby16.dbf' size 60M;

alter database add standby logfile thread 2 group 17 '/u01/oradata/onlinelog/standby17.dbf' size 60M;

alter database add standby logfile thread 2 group 18 '/u01/oradata/onlinelog/standby18.dbf' size 60M;

alter database add standby logfile thread 2 group 19 '/u01/oradata/onlinelog/standby19.dbf' size 60M;

alter database add standby logfile thread 2 group 20 '/u01/oradata/onlinelog/standby20.dbf' size 60M;

改为自动并启动复制

alter system set STANDBY_FILE_MANAGEMENT= auto;

alter database recover managed standby database using current logfile disconnect from session;

同步是否正常检查建议

SQL> select dest_name,status,applied_scn from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

dest_name status applied_scn

LOG_ARCHIVE_DEST_2 VALID 2376535

SQL> select current_scn from v$database;

current_scn

2376521

查询结果的status字段有两个ACTIVE

SQL> select * from v$standby_log;

查询结果的status字段有两个CURRENT

SQL> select * from v$ log;