Change redo log size)

Change redo log size (online redo log size) the Oracle online log and Oracle archive log record the complete change information of the entire database. Is an important part of Oracle architecture. Therefore, online log planning is also very important. However, as the business grows or the current size of online log files becomes a bottleneck in the database, you have to adjust the size of online logs. This article demonstrates how to resize online logs. 1. Demo Environment [SQL] sys @ CNBO1> select * from v $ version where rownum <2; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0-64bit Production sys @ CNBO1> archive log list; database log mode Archive Mode --> already in Archive mode: Automatic archival Enabled archive destination/u02/database/CNBO1/archive/Oldest online log sequence 4881 Next log sequence to Archive 4884 Current log sequence 4884 2. log usage [SQL] sys @ CNBO1> @ log_stat GROUP # MEMBER SEQUENCE # SIZE_MB STATUS ---------- recent ---------- -------- ------- 1/u02/database/CNBO1/redolog /log1aCNBO1. log 4881 20 ACTIVE 1/u02/database/CNBO1/redolog/log1bCNBO1. log 4881 20 ACTIVE 2/u02/database/CNBO1/redolog/log2aCNBO1. log 4882 20 ACTIVE 2/u02/database/CNBO1/redol Og/log2bCNBO1. log 4882 20 ACTIVE 3/u02/database/CNBO1/redolog/log3aCNBO1. log 4883 20 ACTIVE 3/u02/database/CNBO1/redolog/log3bCNBO1. log 4883 20 ACTIVE 4/u02/database/CNBO1/redolog/log4aCNBO1. log 4884 20 ACTIVE 4/u02/database/CNBO1/redolog/log4bCNBO1. log 4884 20 CURRENT 8 rows selected. sys @ CNBO1> select group #, archived, status from v $ log; GROUP # arc status ---------- --- -------------- 1 YES ACTIVE 2 yes active 3 yes active 4 no current 3. Adjust the online log Size [SQL] sys @ CNBO1> alter system archive log all; --> archive all log files alter system archive log all * ERROR at line 1: ORA-00271: there are no logs that need archiving --> the system prompts that no logs need to be archived. sys @ CNBO1> alter database drop logfile group 1; --> because group1 is active, alter database drop logfile group 1 * ERROR at line 1: --> The following prompt crash recovery will use ORA-01624: log 1 Needed for crash recovery of instance CNBO1 (thread 1) ORA-00312: online log 1 thread 1: '/u02/database/CNBO1/redolog/log1aCNBO1. log 'ORA-00312: online log 1 thread 1: '/u02/database/CNBO1/redolog/log1bCNBO1. log 'sys @ CNBO1> alter system switch logfile; --> switch the log System altered. sys @ CNBO1> select group #, archived, status from v $ log; --> the STATUS has changed. GROUP # ARC status ---------- --- ---------------- 1 NO C URRENT 2 yes inactive 3 yes inactive 4 yes active sys @ CNBO1> alter database drop logfile group 2; --> Delete 2nd log groups Database altered. sys @ CNBO1> ho ls/u02/database/CNBO1/redolog/log2 * --> the physical file is not deleted/u02/database/CNBO1/redolog/log2aCNBO1. log/u02/database/CNBO1/redolog/log2bCNBO1. log sys @ CNBO1> alter database add logfile group 2 ('/u02/database/CNBO1/redolog/log2aCNBO1. log', 2'/u02/database/CNBO1/ Redolog/log2bCNBO1. log') size 100 m reuse; --> Add a log group and use the new size Database altered. sys @ CNBO1> alter database drop logfile group 3; Database altered. sys @ CNBO1> alter database add logfile group 3 ('/u02/database/CNBO1/redolog/log3aCNBO1. log', 2'/u02/database/CNBO1/redolog/log3bCNBO1. log') size 100 m reuse; Database altered. sys @ CNBO1> select group #, archived, status from v $ log; --> the newly added log group is in the unused status. --> ignore GROUP number order. When switching logs, the unused log GROUP # arc status ---------- --- ---------------- 1 no current 2 yes unused 3 yes unused 4 yes active sys @ CNBO1> alter system switch logfile; system altered. --> when switching logs, you need to switch the deleted logs to INACTIVE, and then delete and add logs. --> Delete and add the log files one by one according to the preceding method. The subsequent example is omitted. --> The following is the result after all logs are modified. sys @ CNBO1> @ log_stat GROUP # member sequence # SIZE_MB STATUS ---------- logs ---------- -------- ------- 1/u02/database/CNBO1/redolog/ log1aCNBO1. log 4893 100 CURRENT 1/u02/database/CNBO1/redolog/log1bCNBO1. log 4893 100 CURRENT 2/u02/database/CNBO1/redolog/log2aCNBO1. log 4890 100 INACTIVE 2/u02/database/CNBO1/redolog/log2bCNBO1. log 4890 100 INACTIVE 3/u02/database/CNBO1/redolog/log3aCNBO1. log 4891 100 INACTIVE 3/u02/database/CNBO1/redolog/log3bCNBO1. log 4891 100 INACTIVE 4/u02/database/CNBO1/redolog/log4aCNBO1. log 0 100 UNUSED 4/u02/database/CNBO1/redolog/log4bCNBO1. log 0 100 UNUSED 8 rows selected. 4. Summary a. This article demonstrates how to adjust the log file size in archive mode, it is also applicable to non-archive mode B. Archive mode ensures that all logs have been archived (usually archived in scale mode ), then adjust the size of the log file c. Pay attention to the changes in several states of the log file. When the active and current States are not able to be deleted, there are a large number of active transactions in the log. During the crash recovery, the status of the log file will change according to the current status of rollback or rollback e, and the switch log (alter system switch logfile, the cause is that the checkpoint process is triggered.