Oracle 单实例、RAC 集群数据库修改 REDO 日志组

-------------------------------------------------------------------------------------------------------------------
Oracle single db 修改日志组
重置 redo 大小
创建过渡 redo
alter database add logfile group 4 '/u01/app/oracle/oradata/ORCL/redo04.log' size 300M;
alter database add logfile group 5 '/u01/app/oracle/oradata/ORCL/redo05.log' size 300M;
alter database add logfile group 6 '/u01/app/oracle/oradata/ORCL/redo06.log' size 300M;

切换到过渡redo
alter system switch logfile;

查看是否切换到过渡的redo
select group#,bytes/1024/1024,status from v$log;

删除组群
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

通过操作系统删除老的redo01.log、redo02.log、redo03.log
rm -rf redo01.log、redo02.log、redo03.log

重建redo01.log、redo02.log、redo03.log
alter database add logfile group 1 '/u01/app/oracle/oradata/orcl/redo01.log' size 500M;
alter database add logfile group 2 '/u01/app/oracle/oradata/orcl/redo02.log' size 500M;
alter database add logfile group 3 '/u01/app/oracle/oradata/orcl/redo03.log' size 500M;

切换到新建的 redo 当旧的日志组状态为 inactive 即可删除该日志组。如果状态为current和active 在删除的时候会报错
alter system switch logfile;

alter system switch logfile;
alter system archive log current;    -- 数据库处于归档模式才能执行成功
alter system checkpoint;

查看是否切换到新建的redo 如果没有切换可以再次执行 alter system switch logfile; 命令
select group#,bytes/1024/1024,status from v$log;

删除作为中间过渡的redo
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;

通过操作系统删除老的redo04.log、redo05.log、redo06.log
rm -rf redo04.log、redo05.log、redo06.log
-------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------
Oracle rac 修改日志组
1、查看日志文件位置

col status for a10
col type for a10
col member for a50
select * from v$logfile;

2、根据现有日志组信息，添加新的日志组
alter database add logfile thread 1 group 5 ('+DATA/orcl/onlinelog/group5','+FRA/orcl/onlinelog/group5') size 200M;
alter database add logfile thread 2 group 6 ('+DATA/orcl/onlinelog/group6','+FRA/orcl/onlinelog/group6') size 200M;
alter database add logfile thread 1 group 7 ('+DATA/orcl/onlinelog/group7','+FRA/orcl/onlinelog/group7') size 200M;
alter database add logfile thread 2 group 8 ('+DATA/orcl/onlinelog/group8','+FRA/orcl/onlinelog/group8') size 200M;

3、查看日志组详细信息
select thread#,group#,archived,status from v$log;

4、切换日志(多次切换当旧的日志组状态为 inactive 即可删除该日志组)
alter system switch logfile;

5、再次查看日志组详细信息
select thread#,group#,archived,status from v$log;

6、再次查看日志文件具体位置及信息
select * from v$logfile;

7、删除原有日志组
alter database drop logfile group 1; 
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;

8、查看删除后的日志组文件信息
select * from v$logfile;
select thread#,group#,archived,status from v$log;

9、切换日志进行测试
alter system switch logfile;

10、查看切换后新添加日志组文件信息
select * from v$logfile;

11、注意查看日志组的状态看一下哪个是当前组，哪个是inactive状态的。删除掉inactive的那个组。如果状态为current和active 在删除的时候会报错

12、备份当前最新的控制文件
SQL> alter database backup controlfile to trace resetlogs
-------------------------------------------------------------------------------------------------------------------