SQL> select GROUP# ,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
1 2 50 INACTIVE YES
2 2 50 CURRENT NO
3 2 50 INACTIVE YES
4 2 50 INACTIVE YES
SQL> select MEMBER from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/PRD/origlogA/log_g11m1.dbf
/oracle/PRD/mirrlogA/log_g11m2.dbf
/oracle/PRD/origlogB/log_g12m1.dbf
/oracle/PRD/mirrlogB/log_g12m2.dbf
/oracle/PRD/origlogA/log_g13m1.dbf
/oracle/PRD/mirrlogA/log_g13m2.dbf
/oracle/PRD/origlogB/log_g14m1.dbf
/oracle/PRD/mirrlogB/log_g14m2.dbf
切换当前日志到新的日志组:
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
直到旧的日志组 status为'INACTIVE'
删除Status为'INACTIVE'的旧日志组1,2,3,4,5,6,7,8:
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;
在OS下删除旧的日志物理文件:
rm /oracle/SZP/origlogA/log_g11m1.dbf
rm /oracle/SZP/mirrlogA/log_g11m2.dbf
rm /oracle/SZP/origlogB/log_g12m1.dbf
rm /oracle/SZP/mirrlogB/log_g12m2.dbf
rm /oracle/SZP/origlogA/log_g13m1.dbf
rm /oracle/SZP/mirrlogA/log_g13m2.dbf
rm /oracle/SZP/origlogB/log_g14m1.dbf
rm /oracle/SZP/mirrlogB/log_g14m2.dbf
rm /oracle/SZP/origlogA/log_g15m1.dbf
rm /oracle/SZP/mirrlogA/log_g15m2.dbf
rm /oracle/SZP/origlogB/log_g16m1.dbf
rm /oracle/SZP/mirrlogB/log_g16m2.dbf
rm /oracle/SZP/origlogA/log_g17m1.dbf
rm /oracle/SZP/mirrlogA/log_g17m2.dbf
rm /oracle/SZP/origlogB/log_g18m1.dbf
rm /oracle/SZP/mirrlogB/log_g18m2.dbf
重建日志组1,2,3,4,5,6:
alter database add logfile group 1('/oracle/SZP/origlogA/log_g11m1.dbf','/oracle/SZP/mirrlogA/log_g11m2.dbf') size 150m;
alter database add logfile group 2('/oracle/SZP/origlogB/log_g12m1.dbf','/oracle/SZP/mirrlogB/log_g12m2.dbf') size 150m;
alter database add logfile group 3('/oracle/SZP/origlogA/log_g13m1.dbf','/oracle/SZP/mirrlogA/log_g13m2.dbf') size 150m;
alter database add logfile group 4('/oracle/SZP/origlogB/log_g14m1.dbf','/oracle/SZP/mirrlogB/log_g14m2.dbf') size 150m;
alter database add logfile group 5('/oracle/SZP/origlogA/log_g15m1.dbf','/oracle/SZP/mirrlogA/log_g15m2.dbf') size 150m;
alter database add logfile group 6('/oracle/SZP/origlogB/log_g16m1.dbf','/oracle/SZP/mirrlogB/log_g16m2.dbf') size 150m;