DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, September 14, 2015

Steps to add,drop and resize online redo log files



Please change all the below name & values as per your environment:

> sqlplus /nolog

SQL> connect / as sysdba

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
DOYENSYS READ WRITE

Please check the below first:
-------------------------------------------
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ---------------------------------------- ------------------------------
3 ONLINE /m03/oradata/DOYENSYS/redo03.log NO
2 ONLINE /m02/oradata/DOYENSYS/redo02.log NO
1 ONLINE /m01/oradata/DOYENSYS/redo01.log NO


Please do the below steps one by one:
---------------------------------------------------
SQL> alter database add logfile group 4 '/redo/oradata/DOYENSYS/redo04.log' size 800m;
Database altered.
SQL> alter database add logfile group 5 '/redo/oradata/DOYENSYS/redo05.log' size 800m;
Database altered.
SQL> alter database add logfile group 6 '/redo/oradata/DOYENSYS/redo06.log' size 800m;
Database altered.
SQL> alter database add logfile group 7 '/redo/oradata/DOYENSYS/redo07.log' size 800m;
Database altered.


Then check the below one :
-----------------------------------

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 51315 314572800 512 1 YES
INACTIVE 3.0910E+11 17-JUN-14 3.0910E+11 17-JUN-14
2 1 51316 314572800 512 1 NO
CURRENT 3.0910E+11 17-JUN-14 2.8147E+14
3 1 51314 314572800 512 1 YES
INACTIVE 3.0910E+11 17-JUN-14 3.0910E+11 17-JUN-14
4 1 0 838860800 512 1 YES
UNUSED 0 0
5 1 0 838860800 512 1 YES
UNUSED 0 0
6 1 0 838860800 512 1 YES
UNUSED 0 0
7 1 0 838860800 512 1 YES
UNUSED 0 0


From the above we can see log group 2 is current, and this is one of the
groups we must drop. Therefore let’s switch out of this group into
one of the newly created log groups.


Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:
SQL> alter system switch logfile;
** repeat as necessary until group 4 is CURRENT **


Run the query again to verify the current log group is group 4:

SQL> select group#, status from v$log;

GROUP# STATUS
——— —————-
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED

Note: redo log Group 1 or 2 or 3 can be active after “alter system switch log file” which means could not be dropped, in this case,

you need to do “alter system checkpoint” to make redo log groups 1,2 and 3 inactive.

Now drop redo log groups 1, 2, and 3:
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;


Verify the groups were dropped, and the new groups’ sizes are correct.
Please check the alertlog and if everything seems to be fine.

Please remove the old redolog files from OS level also.

No comments: