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;

本文来源：https://www.bwwdw.com/article/240r.html