1.查看当前系统现有的redo log状况（组数/大小/名称/状态）
SYS@ORCL>desc v$log
Name Null Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
1 50 INACTIVE YES
2 50 INACTIVE YES
3 50 CURRENT NO
SYS@ORCL>desc v$logfile;
Name Null Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SYS@ORCL>select MEMBER from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_b0fh9w87_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_b0fh9wqw_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_b0fh9nk4_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_b0fh9o8x_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_b0fh9drr_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_b0fh9dxx_.log
6 rows selected. --查看日志文件的路径
2.添加3组大小为100M的日志组；
SYS@ORCL>alter database add logfile group 4 '/u01/app/oracle/oradata/ORCL/onlinelog/redo04.log'size 100m;
Database altered.
SYS@ORCL>alter database add logfile group 5'/u01/app/oracle/oradata/ORCL/onlinelog/redo05.log'size 100m;
Database altered.
SYS@ORCL>alter database add logfile group 6'/u01/app/oracle/oradata/ORCL/onlinelog/redo06.log'size 100m;
Database altered.
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
1 50 INACTIVE YES
2 50 INACTIVE YES
3 50 CURRENT NO
4 100 UNUSED YES
5 100 UNUSED YES
6 100 UNUSED YES
6 rows selected.
3.删除原有的日志组；（只操作状态为inactive的日志组）
若要操作日志组为current时，需先进行日志切换：alter system switch logfile;
若为active时，则可强制进行检查点：alter system checkpoint;
SYS@ORCL>alter system switch logfile;
System altered.
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC