修改oracle重做日志文件大小

修改oracle重做⽇志⽂件⼤⼩
1. 创建3个新的⽇志组
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 500M; SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') SIZE 500M; SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 500M;
查看⽇志组
SQL> select * from v$logfile;
2.切换当前⽇志到新的⽇志组
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
切到要删除为INCACTIVE才⾏
SQL> select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 139 ******** 1 INACTIVE
2 137 ******** 1 INACTIVE
3 138 ******** 1 INACTIVE
4 140 524288000 1 ACTIVE
5 141 524288000 1 CURRENT
6 136 524288000 1 INACTIVE
3.删除旧的⽇志组
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
查看是否删除了⽇志组
SQL> select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
4 140 524288000 1 INACTIVE
5 141 524288000 1 CURRENT
6 136 524288000 1 INACTIVE
4.操作系统删除原⽇志组1、2、3中的⽂件
[oracle@oracle122 log]$ cd /u01/app/oracle/oradata/orcl
[oracle@oracle122 orcl]$
[oracle@oracle122 orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@oracle122 orcl]$ ls
control01.ctl example01.dbf redo01.log redo02.log redo03.log redo04.log redo05.log redo06.log sysaux01.dbf system01.dbf
temp01.dbf undotbs01.dbf users01.dbf
[oracle@oracle122 orcl]$ rm -rf redo01.log
[oracle@oracle122 orcl]$ rm -rf redo02.log
[oracle@oracle122 orcl]$ rm -rf redo03.log
5.重建⽇志组1、2、3
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 500M;
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 500M;
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 500M;
7.删除中间过渡⽤的⽇志组4、5、6 ，在删除时查询select group#,sequence#,bytes,members,status from v$log; 删除组是否为INACTIVE，如果不是INACTIVE 就要执⾏alter system switch logfile;
查询看⼀下
SQL> select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 148 524288000 1 CURRENT
2 14
3 524288000 1 INACTIVE
3 14
4 524288000 1 INACTIVE
4 146 524288000 1 INACTIVE
5 147 524288000 1 INACTIVE
6 145 524288000 1 INACTIVE
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
8.到操作系统删除组4、5、6
[oracle@oracle122 orcl]$ rm -rf redo04.log
[oracle@oracle122 orcl]$ rm -rf redo05.log
[oracle@oracle122 orcl]$ rm -rf redo06.log
9.备份当前最新的控制⽂件
SQL> alter database backup controlfile to trace resetlogs ;。