Oracleredo log Adjustment

Date: 2013060510: 00 environment: Oracle11.2.0.3RAC, dual-node, RedHat5.8 + DS: DELL3600I query the average number of log switches in the past 24 hours,

Date: 20130605 environment: Oracle 11.2.0.3 RAC, dual-node, RedHat 5.8 + DS: DELL 3600I query the average log switching between the past 24 hours,

Date: 20130605

Environment: Oracle 11.2.0.3 RAC, dual-node,

RedHat 5.8 + DS: DELL 3600I

Query the average, maximum, and minimum number of log switches in the past 24 hours: the average time is less than 5 minutes. We recommend that you increase the log file size:

(SELECTthread #, first_time,

Last_first_time,

(First_time

-

* Last_log_time_minutes,

Last_thread #

FROMv $ log_history)

FROM log_history

ANDlast_thread # = thread #

Query results:

MIN_MINUTES MAX_MINUTES AVG_MINUTES

Bytes -------------------------------------------------------------------------------------------------

1 0.03 160.22 2.03

Query logs

SQL> showparameters thread;

NAME TYPE VALUE

-----------------------------------------------------------------------------

Parallel_threads_per_cpu integer 2

Thread integer 2

In the cluster environment, the database instances on each node need their own redo log groups. For example, if the cluster database has three nodes and instances, and each instance has at least two redo log groups, the cluster requires at least six online redo log groups. In general, the two log groups are the minimum limit, and more groups can be assigned to each instance in the project. First, improve efficiency, and second, improve recovery. However, no matter the number of groups, all log files are stored on the shared storage medium and can be accessed by other instances.

The thread parameter in the server parameter file defines the thread Number of Node 1 (rac1) instance as 1, while the thread Number of Node 2 (rac2) instance is 2:

Initracdb1.ora

Racdb1.instance _ number = 1

Racdb2.instance _ number = 2

Query the Current Log File Group and its instance:

GROUP # THREAD # MEMBERS BYTES

1 1 1 2 52428800

2 2 1 2 52428800

3 3 2 52428800

4 4 2 52428800

Next, we will add a group for the instance of thread 1 with the group number of 5 and use the ASM automatic storage mode. Therefore, we do not need to specify the file name and size:
SQL> alter database add logfile thread 1 group 5 size 102400kb;

Next, we will add a group for the instance of thread 2 with the group number of 6 and use the ASM automatic storage mode. Therefore, we do not need to specify the file name and size.
SQL> alter database add logfile thread 2 group 6 size 102400kb;

On each node: Switch the current log to a new log group.
Alter system switch logfile;
Alter system switch logfile;

GROUP # THREAD # MEMBERS BYTES STATUS

1 1 1 2 52428800 ACTIVE

2 2 1 2 52428800 INACTIVE

3 3 2 2 52428800 INACTIVE

4 4 2 2 52428800 ACTIVE

5 5 1 2 104857600 CURRENT

6 6 2 104857600 CURRENT

3. Delete the old log Group

Alter database drop logfile group 2;
Alter database drop logfile group 3;

The following steps add two log groups, 5, 6 to process 1, 2, and modify other log groups to change the size from 50 M to 100 M,

First switch the log from active to inactive, then delete the log group, and then add the log group.

SQL> alterdatabase add logfile thread 1 group 5 size 100 M;

Alter database addlogfile thread 1 group 5 size 100 M

ORA-00933: SQL command ended incorrectly

SQL> alter database add logfile thread 1 group 5;

Database altered

SQL> alter database add logfile thread 2 group 6;

Database altered

SQL> alter system switch logfile;

System altered

SQL> alter database drop logfile group 2;

Database altered

SQL> alter database add logfile thread 1 group 2;

Database altered

SQL> alter database drop logfile group 3;

Database altered

SQL> alter database add logfile thread 2 group 3;

Database altered

SQL> alter system switch logfile;

System altered

SQL> alter database drop logfile group 1;

Database altered

SQL> alterdatabase add logfile thread 1 group 1;

Database altered

SQL> alter database drop logfile group 4;

Database altered

SQL> alter database add logfile thread 2 group 4;

Database altered

SQL> altersystem switch logfile;

System altered

In the preceding log group, oracle rac automatically generates two files according to the file storage rules:

GROUP # status type member IS_RECOVERY_DEST_FILE

1 5 ONLINE + DATA/racdb/onlinelog/group_5.322.817383997 NO

2 5 ONLINE + RECOVERY/racdb/onlinelog/group_5.374.817383999 YES