Shulou(Shulou.com)05/31 Report--

Editor to share with you how Oracle adjusts the number and members of redo log groups online. I hope you will get something after reading this article. Let's discuss it together.

1. Resize the redo log group

Reason for operation:

Redo logs are generally set to take 10-20 minutes for log conversion. Switching too frequently will affect performance. Usually no more than 6 times per hour! If the log file switch (checkpoint incomplete) in AWR (Automated Workload Repository automatic load Information Base) report is relatively large, it means that the switching between redo log is too frequent. The switching frequency can be reduced by increasing the redo log size. However, if the redo log size is too large, there may be a risk of data loss during database recovery. So choose redo log size as a compromise, which defaults to 50m.

Oracle does not provide a way to directly change the size of the configuration file, but you can add a new log group to replace the old log group.

Example: there are now three log members with a size of 50m and want to change it to 100m.

1. View current log group members

SQL > select member from v$logfile

two。 View current log group status

SQL > select group#,members,bytes/1024/1024,status from v$log

3. Add log group

SQL > alter database add logfile group 4 ('/ u01ax oradata size) size 100m

SQL > alter database add logfile group 5 ('/ u01Universe oradata size) size 100m

SQL > alter database add logfile group 6 ('/ u01Universe oradata) size 100m

4. Switch to the new log group

SQL > alter system switch logfile

(can be executed multiple times until CURRENT points to the newly created log group)

5. View current log group status

SQL > select group#,members,bytes/1024/1024,status from v$log

The meaning of various states:

A.CURRENT refers to the current log file, which is required for instance recovery.

B.ACTIVE refers to active non-current logs that are used during instance recovery. The Active status means that the Checkpoint is not yet complete, so the log file cannot be overwritten. You can't drop at this time, you should execute alter system checkpoint;-- enforce the checkpoint; then do it.

"c.INACTIVE is an inactive log that is no longer needed when the instance is restored, but may be required when the media is restored."

D.UNUSED indicates that the log was never written, maybe it was just added, or it was reset after RESETLOGS.

6. Delete the old log group

SQL > alter database drop logfile group 1

SQL > alter database drop logfile group 2

SQL > alter database drop logfile group 3

7. Delete redolog log files under the operating system

Mv / u01/oracle/oradata/orcl/redo0 [1-3] / tmp

Or delete the abandoned log files directly.

Note: you can first create a new log group as a transitional log group, then delete the original log group and modify the size to rebuild it.

Add new members to the existing log group

Reason for operation: redo log redundancy to improve data recoverability and integrity.

Note: if you are in the production library, put the newly created member on a different disk. All log members should be the same size.

1. Add a group member to a specified log group

SQL > alter database add logfile member'/ u01 to group to group 4

two。 View current log group members

SQL > select member from v$logfile

3. View current log group status

SQL > select group#,members,bytes/1024/1024,status from v$log

After reading this article, I believe you have a certain understanding of "how Oracle adjusts the number and membership of redo log groups online". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.