Monday, November 16, 2009

How can I increase redo log file size

How can I increase redo log file size (no archive mode)
=======================================
1.
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;


GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 YES INACTIVE
3 NO CURRENT

2. Whichever is not current , drop that group and recreate it with appropriate size.
-----------------------------------------------------------------------------------------------

ALTER DATABASE DROP LOGFILE GROUP 1;

3. Manually drop the physical log file.

ALTER DATABASE ADD LOGFILE GROUP 1 ('G:\ORACLE\PRODUCT\10.2.0\ORADATA\BEFTN\REDO02.LOG') SIZE 60M;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE ADD LOGFILE GROUP 2 ('G:\ORACLE\PRODUCT\10.2.0\ORADATA\BEFTN\REDO03.LOG') SIZE 60M;


4. Then switch the logfile
--------------------------

alter system switch logfile

---alter system checkpoint ----for doing inactive

----ALTER SYSTEM ARCHIVE LOG CURRENT; ---when archive mode ---Wait for the next group to be archived.

5. Then recreate the remaining group.
-----------------------------------

ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('F:\Oracle\Oradata\NEDARR\REDO03A') SIZE 60M;

No comments: