Modify the size of Oracle redo log files

Modify the size of Oracle redo log files

Modify the size of Oracle redo log files

1. Create 3 new log groups

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 500 M;

SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') SIZE 500 M;

SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 500 M;

View log groups

SQL> select * from v $ logfile;

2. Switch the current log to the new log Group

SQL> alter system switch logfile;

SQL> alter system switch logfile;

SQL> alter system switch logfile;

Switch to INCACTIVE

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

GROUP # SEQUENCE # BYTES MEMBERS STATUS
--------------------------------------------------------
1 139 52428800 1 INACTIVE
2 137 52428800 1 INACTIVE
3 138 52428800 1 INACTIVE
4 140 524288000 1 ACTIVE
5 141 524288000 1 CURRENT
6 136 524288000 1 INACTIVE

3. Delete the old log Group

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Check whether a log group is deleted.

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

GROUP # SEQUENCE # BYTES MEMBERS STATUS
--------------------------------------------------------
4 140 524288000 1 INACTIVE
5 141 524288000 1 CURRENT
6 136 524288000 1 INACTIVE

4. The operating system deletes the files in the original log group 1, 2, and 3.

[Oracle @ oracle122 log] $ cd/u01/app/oracle/oradata/orcl
[Oracle @ oracle122 orcl] $
[Oracle @ oracle122 orcl] $ pwd
/U01/app/oracle/oradata/orcl
[Oracle @ oracle122 orcl] $ ls
Control01.ctl example01.dbf redo01.log redo02.log redo03.log redo04.log redo05.log redo06.log sysaux01.dbf system01.dbf temp01.dbf login users01.dbf
[Oracle @ oracle122 orcl] $ rm-rf redo01.log
[Oracle @ oracle122 orcl] $ rm-rf redo02.log
[Oracle @ oracle122 orcl] $ rm-rf redo03.log

5. Rebuild log groups 1, 2, and 3

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 500 M;

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 500 M;

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 500 M;

7. delete the log groups 4, 5, and 6 for intermediate transition. When deleting a log group, query select group #, sequence #, bytes, members, status from v $ log. If the deleted log group is INACTIVE, if it is not INACTIVE, execute alter system switch logfile;

Query

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

GROUP # SEQUENCE # BYTES MEMBERS STATUS
--------------------------------------------------------
1 148 524288000 1 CURRENT
2 143 524288000 1 INACTIVE
3 144 524288000 1 INACTIVE
4 146 524288000 1 INACTIVE
5 147 524288000 1 INACTIVE
6 145 524288000 1 INACTIVE

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

8. Delete groups 4, 5, and 6 in the operating system.

[Oracle @ oracle122 orcl] $ rm-rf redo04.log
[Oracle @ oracle122 orcl] $ rm-rf redo05.log
[Oracle @ oracle122 orcl] $ rm-rf redo06.log

9. Back up the latest Control File

SQL> alter database backup controlfile to trace resetlogs;

Increase the size of online redo log files in Oracle

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian