[Go] Increase the size of the Oracle redo log

The Redo log (redo log) is controlled by the LGWR. Oracle does not immediately modify the data block for DML operation data, but produces redo records and logs it to the online redo log, when redo log is full, or the global checkpoint is started, or alter system switch LogFile This will switch redo log group, (if the Redo log status is current, is currently redo log, if inactive is already have dbwn write data block, inactive has not been written) redo log to switch, there is a certain consumption , or sometimes because the redo log is too small to cause a certain error, we need to make the redo log adjustment larger.

We can take the following steps to achieve the goal

1. Check the LogFile group status
Sql> Select A.status, B.member, a.thread#, a.group# from V$log A, v$logfile b where a.group#=b.group#;

STATUS MEMBER thread# group#
—————- ————————————————————- ———- ———-
Current/opt/oracle/oradata/ocrl/redo01.log 1 1
Inactive/opt/oracle/oradata/ocrl/redo02.log 1 2
Inactive/opt/oracle/oradata/ocrl/redo03.log 1 3

2. Inactive Redo.log is deleted, inactive's redo.log has been written to the data block.
sql> ALTER DATABASE drop logfile '/opt/oracle/oradata/ocrl/redo03.log '; or directly with
sql> ALTER DATABASE drop logfile Group 3;

3. Add the Redo log group that was dropped above
sql> ALTER DATABASE Add logfile Group 3 ('/opt/oracle/oradata/ocrl/redo03.log ') size 120M reuse;

4. Loop 2,3 all the inactive redo log files are resize off

5. For current and active redo log files, you need to redo log to inactive state, in the preface has been mentioned in the preamble, is currently recorded in the Redo log file, to treat such we need
Sql>alter system switch logfile; Switch log file, but because redo log redo logs will not immediately trigger DBWR write database, so this redo log state from current to active state, we see
Sql> Select A.status, a.thread#, a.group# from V$log A;

STATUS thread# group#
—————- ———- ———-
Current 1 1
INACTIVE 1 2
UNUSED 1 3

Sql>alter system switch logfile;
Sql> Select A.status, a.thread#, a.group# from V$log A;

STATUS thread# group#
—————- ———- ———-
ACTIVE 1 1
Current 1 2
UNUSED 1 3
(Note that group 3 is my newly added redo log group, which is not yet in use, the status is unused)

5. For the active state, that the dirty data here is not written to the database, manually add a global checkpoint, urging Ckpt immediately wake up DBWR write dirty data
Sql>alter system checkpoint;
View V$log
STATUS thread# group#
—————- ———- ———-
INACTIVE 1 1
Current 1 2
UNUSED 1 3

The group 1 is also rebuilt using 2, 3 steps.

6. Follow 4,5 to remove all active redo logs

By this, all redo logs have been completely resized and the task completed.

Two points to note here.
1. Simply add Redo log group the size of a single file does not work, and the file size is consistent in the same group.

2. If it is an archive mode, ensure that it is automatically archived, and if it is manually archived, it needs to be manually archived with alter system log current when the ALTER system switch logfile is locked. Or turn on automatic archiving via alter system archive log start. Otherwise, when the Redo log group switches the full groups, it waits for a state.

[Go] Increase the size of the Oracle redo log