Modify the size of oracle redo log files and oracle redo log files

Modify the size of oracle redo log files and 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;