A. How to resize or add redo logs.
1. Review information on existing redo logs.
SQL> SELECT a.group#, b.member, a.status, a.bytes FROM v$log a, v$logfile b WHERE a.group#=b.group#;
or
SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size,status from v$log;
GROUP# THREAD# MEMBERS MB_SIZE STATUS
---------- ---------- ---------- ---------- ---------------- 1 1 2 50 CURRENT 2 1 2 50 INACTIVE 3 2 2 50 INACTIVE
SQL> alter system checkpoint;
and you want to resize all your groups. Lets say you want to set 100M instead of 50M.
2. Add new groups
ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;
3. Now you should wait till the Group 1/2/3 will start to be INACTIVE so you would be able to drop them. Also you can speed up this process by executing:
4. Check the status on all redo logs again.
SQL> SELECT a.group#, b.member, a.status, a.bytes FROM v$log a, v$logfile b WHERE a.group#=b.group#;
or
SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size,status from v$log;
5. Drop the online redo log groups that are not needed. You must have the ALTER DATABASE system privilege.
Note: Before dropping an online redo log group, consider the following restrictions and precautions:
a. An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)
b. You can drop an online redo log group only if it is INACTIVE. If you need to drop the current group, first force a log switch to occur.
SQL> alter system switch logfile;
6 . Check that the group is inactive and archived before dropping it .
SQL > SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL>ALTER DATABASE DROP LOGFILE GROUP 2;
SQL>ALTER DATABASE DROP LOGFILE GROUP 3;
Sourece:- Doc ID 602066.1
1. Review information on existing redo logs.
SQL> SELECT a.group#, b.member, a.status, a.bytes FROM v$log a, v$logfile b WHERE a.group#=b.group#;
or
SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size,status from v$log;
GROUP# THREAD# MEMBERS MB_SIZE STATUS
---------- ---------- ---------- ---------- ---------------- 1 1 2 50 CURRENT 2 1 2 50 INACTIVE 3 2 2 50 INACTIVE
SQL> alter system checkpoint;
and you want to resize all your groups. Lets say you want to set 100M instead of 50M.
2. Add new groups
ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;
3. Now you should wait till the Group 1/2/3 will start to be INACTIVE so you would be able to drop them. Also you can speed up this process by executing:
4. Check the status on all redo logs again.
SQL> SELECT a.group#, b.member, a.status, a.bytes FROM v$log a, v$logfile b WHERE a.group#=b.group#;
or
SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size,status from v$log;
5. Drop the online redo log groups that are not needed. You must have the ALTER DATABASE system privilege.
Note: Before dropping an online redo log group, consider the following restrictions and precautions:
a. An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)
b. You can drop an online redo log group only if it is INACTIVE. If you need to drop the current group, first force a log switch to occur.
SQL> alter system switch logfile;
6 . Check that the group is inactive and archived before dropping it .
SQL > SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL>ALTER DATABASE DROP LOGFILE GROUP 2;
SQL>ALTER DATABASE DROP LOGFILE GROUP 3;
Sourece:- Doc ID 602066.1
