Oracle changes the size of the redo log

Because the Data Warehouse ETL process, the execution time of a mapping more than one hours,

Select event,count(*) fromv$session_wait  Groupbyorder Bycount (*)  desc

Find that log switching takes a lot of time to try to increase the size of the redo log.

(1) The size of redo log can affect DBWR and checkpoint;

(2) Larger redo log files provide better performance. Undersized logfiles increase checkpoint activity and reduce performance.

Large log file can provide better performance, small logfile will increase checkpoint and reduce performance;

(3) A Rough Guide was to switch log files at the most once every minutes. (It is recommended that the log switch should not be 20 minutes long).

By viewing I have three groups of Redolog 1/2/3 each group two Member States are normal size 50m

Select *  from v$log ;

The status has several values, respectively:

1. UNUSED (has not been used yet);
2. Current (in use);
3. ACTIVE (log isactive but isn't the current Log. It is needed for crash recovery);
4. INACTIVE (Log is nolonger needed for instance recovery),

viewing log files

Select *  from V$logfile;

Since Oracle does not provide a resize-like parameter to resize the redo LOG file, it is only possible to delete the file before rebuilding it. Because Oracle requires at least two sets of log files to be used, it cannot be deleted directly, and the Redo log log group must be created for intermediate transitions.

1. Create 3 new log groups

ALTER DATABASE ADDLOGFILEGROUP 4('/usr/oracle/app/oradata/orcl/redo04a.log','/usr/oracle/app/oradata/orcl/redo04b.log') SIZE 2048M;ALTER DATABASE ADDLOGFILEGROUP 5('/usr/oracle/app/oradata/orcl/redo05a.log','/usr/oracle/app/oradata/orcl/redo05b.log') SIZE 2048M;ALTER DATABASE ADDLOGFILEGROUP 6('/usr/oracle/app/oradata/orcl/redo06a.log','/usr/oracle/app/oradata/orcl/redo06b.log') SIZE 2048M;

2. Switch the current log to a new log group

Alter system switch logfile; Alter system switch logfile; alter system switch logfile;

3. Delete the old log group

View the status of a log group see which is the current group and which is the inactive state.

SQL>Select* from v$log;

Delete the group that inactive out. If the status is current and active is deleted, the error will be

Alter Database Drop Group 1 ; Alter Database Drop Group 2 ; Alter Database Drop Group 3;

4. Delete files from the original log group 1, 2, and 3 under the operating system

Note: Deleting the drop operation every step requires manually deleting the entity files in the operating system.

5. Rebuilding the Log group 1, 2, 3

ALTER DATABASE ADDLOGFILEGROUP 1('/usr/oracle/app/oradata/orcl/redo01a.log','/usr/oracle/app/oradata/orcl/redo01b.log') SIZE 2048M; ALTER DATABASE ADDLOGFILEGROUP 2('/usr/oracle/app/oradata/orcl/redo02a.log','/usr/oracle/app/oradata/orcl/redo02b.log') SIZE 2048M; ALTER DATABASE ADDLOGFILEGROUP 3('/usr/oracle/app/oradata/orcl/redo03a.log','/usr/oracle/app/oradata/orcl/redo03b.log') SIZE 2048M;

6. Toggle Log Group

Alteralter alter system switch logfile;

7, delete the intermediate transition with the log Group 4, 5, 6

Alter Database Drop Group 4  alterdatabasedropGroup5alter Database Drop Group 6;

8, to the operating system delete the original log Group 4, 5, 6 of the file 9, back up the current latest control files

SQL>alterdatabase to trace Resetlogs

Oracle changes the size of the redo log