Increase the size of oracle redo logs

Redolog is controlled by LGWR. Oracle does not immediately modify data blocks when operating data in DML. Instead, it generates redo records and records them to the online redolog. When the redoLog is full or the global checkpoint is started, or altersystemswitchlogfile; redologgroup will be switched,

Redo logs are controlled by LGWR. Oracle does not immediately modify data blocks when operating data in DML. Instead, it generates redo records and records them to the online redo log. When the redo Log is full, or start the global checkpoint or alter system switch logfile. The redo log group will be switched,

Redo logs are controlled by LGWR. Oracle does not immediately modify data blocks when operating data in DML. Instead, it generates redo records and records them to the online redo log. When the redo Log is full, or start global checkpoint or alter system switch logfile. At this time, the redo log group will be switched. (If the redo log status is current, it is the current redo log, if it is inactive, DBWN has been written into the data block, and Inactive has not yet written.) When the redo log is switched, there is a certain amount of consumption, or sometimes the redo log is too small to cause certain errors, at this time, we need to increase the redo log.

We can take the following steps to achieve our goal:

1. Check the status of the logfile Group
SQL> select. status, B. member,. THREAD #,. GROUP # from v $ log a, v $ logfile B where. 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. Delete inactive redo. log and inactive redo. log have been written into data blocks.
SQL> alter database drop logfile '/opt/oracle/oradata/ocrl/redo03.log'; or directly use
SQL> alter database drop logfile group 3;

3. Add the drop-down redo log group
SQL> alter database add logfile group 3 ('/opt/oracle/oradata/ocrl/redo03.log') size 120 M reuse;

4. cycle 2 and 3 to resize all inactive redo log files

5. for current and active redo log files, you must first make the redo log inactive. As mentioned in the preface, current indicates the redo log file currently being recorded, we need
SQL> alter system switch logfile; switch the log file. However, because the redo log does not immediately trigger DBWR to write data to the database, the redo log status changes from current to active.
SQL> select a. status, a. THREAD #, a. GROUP # from v $ log;

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;

Status thread # GROUP #
--------------
ACTIVE 1 1
CURRENT 1 2
UNUSED 1 3
(Note that group 3 is the newly added redo log group. It is not in use and the status is UNUSED)

5. For the active state, it indicates that the dirty data has not been written to the database. manually add a global checkpoint and urge CKPT to immediately wake up DBWR and write dirty data.
SQL> alter system checkpoint;
View v $ log
Status thread # GROUP #
--------------
INACTIVE 1 1
CURRENT 1 2
UNUSED 1 3

Rebuild group 1 in step 2 and step 3.

6. Perform all active redo logs according to 4, 5.

By now, all the redo logs have been fully changed and the task has been completed.

Note the following two points:
1. Adding redo log group alone does not apply to the size of a single file. In the same group, the file size is consistent.

2. If the archive mode is used, make sure that automatic archiving is completed. If manual archiving is performed, you must use alter system log current to manually archive the file when the alter system switch logfile is locked. Alternatively, use alter system archive log start to enable automatic archiving. Otherwise, when the redo log group switches to the entire groups, it will remain in the waiting status (******).

========================================================== ======================================

Inthirties focuses on Oracle database maintenance, optimization, security, backup, recovery, migration, and troubleshooting