Check whether there is a problem with the Oracle10gRACredo Log Size

Check whether there is a problem with the Oracle10GRACredo Log Size

Determines whether the Oracle 10g rac redo log size is faulty.

Here we will mention two possible problems.

FirstBatch Processing TaskThis task may not have enough space to complete the Redo, or because the speed is fast, the online redo log has been switched before it is archived to the offline redo log (all the redo logs are used, and write the first redo log again ). Online redo logs can only be overwritten after archiving is enabled. Therefore, DML and DDL activities must wait until there are available online logs. At the operating system level, online redo logs are listed based on their latest update dates and times. You can determine whether they are frequently switched. You can also query V $ LOG_HISTORY to obtain the most recent log exchange records (100 in the past and now added ). If the size of online redo logs is increased, sufficient space can be provided for batch processing tasks that execute large INSERT, UPDATE, and DELETE operations. A better solution is to increase the number of online redo logs, so that sufficient space can be provided when there are frequent log switches (small but many online redo logs.

The second question to consider isLong-running tasksThese tasks may take a lot of time to switch online redo logs. When only one online redo log is used for the entire task, this long-running task may be very fast. For online transaction processing (OLTP) Type environment, it is best to use a smaller redo log. My experience isEvery 30 minutes(If you do not consider batch operations that can shorten this period of time, you can switch to online redo logs once. Monitor the date and time when online redo logs occur at the operating system level (or query v $Log_history, You can also query) to determine the size or number of online redo logs, and set an optimized switching interval.

The following query shows the interval between two log switches, so that we can easily determine whether the system is faulty.

1. For a single instance

2. For the online log switching of Node 1 in the cluster ( A. thread # = B. thread # and a. thread # = 1), If Node 2 is also very simple ( A. thread # = B. thread # and a. thread # = 2).

At the operating system level, you can check the size of online redo LOG files or query the V $ LOG and V $ LOGFILE tables to determine the size of these files. The redo log display information is displayed in the queries listed below:

Select a. member, B. * from v $ logfile a, v $ log B where a. group # = B. group #;

3. Other helpful log redo commands

Use the alter database add logfile... command to create large logs and then delete small logs to ADD additional logs. Note that, based on the number of blocks specified for CHECKPOINT_INTERVAL In the init. ora file, a checkpoint is imposed at the time interval of the checkpoint. Therefore, if you increase the size of online redo log files, you must also increase the checkpoint interval. To multiplexing online redo log files (create an image copy), you can use the following command to add log files to an existing group:

Alter database add logfile member '+ RAC/jscn/onlinelog/redo011.dbf' to group 4;
Alter database add logfile member '+ RAC/jscn/onlinelog/redo021.dbf' to group 5;

You can use the following command to delete an online redo log member:

Alter database drop logfile member '+ RAC/jscn/onlinelog/redo021.dbf ';

To add a new online redo log group, run the following command:

Alter database add logfile thread 1 group 5' + RAC/jscn/onlinelog/redo01.dbf' size 512 m;