ORACLE11GRACREDOLOG production database expansion

Recently, ORACLE databases in oracle are unstable. by querying the ORACLE alert Log, the following error message is displayed: Thread1cannotallocatenewlog, sequence%922checkpointnotcompletecurrentl

Recently, ORACLE databases in oracle are unstable. by querying the ORACLE alert Log, the following error message is displayed: Thread1cannotallocatenewlog, sequence%922checkpointnotcompletecurrentl

Recently, the ORACLE database in the unit is unstable. On the Hong Kong server, you can query the oracle alert Log and find the following error:

Thread 1 cannot allocate new log, sequence 108922
Checkpoint not complete
Current log #1 seq #108921 mem #0: + DATADG/apts/onlinelog/group_1.262.788509509
Current log #1 seq #108921 mem #1: + ARCHDG/apts/onlinelog/group_1.257.788509511
Thread 1 advanced to log sequence 108922 (LGWR switch)
Current log #2 seq #108922 mem #0: + DATADG/apts/onlinelog/group_2.261.788509511
Current log #2 seq #108922 mem #1: + ARCHDG/apts/onlinelog/group_2.258.788509511
Sat Apr 27 10:25:38 2013
Thread 1 cannot allocate new log, sequence 108923
Checkpoint not complete
Current log #2 seq #108922 mem #0: + DATADG/apts/onlinelog/group_2.261.788509511
Current log #2 seq #108922 mem #1: + ARCHDG/apts/onlinelog/group_2.258.788509511
Thread 1 advanced to log sequence 108923 (LGWR switch)
Current log #1 seq #108923 mem #0: + DATADG/apts/onlinelog/group_1.262.788509509
Current log #1 seq #108923 mem #1: + ARCHDG/apts/onlinelog/group_1.257.788509511

It was preliminarily determined that the ORACLE online redo log is too small, the production database is too busy during the day, and the log switching is frequent. Due to the installation of ORACLE, REDO is installed according to the default settings (the default setting is 2 groups of online logs per instance, two members in each group, 50 MB for each log file). The REDO log is not modified on the server, in the later stage, due to the sharp increase in business volume, the original settings of U.S. servers could not meet the existing needs. Therefore, the log files of the servers must be resized.

The idea is as follows:

1. Three statuses of online redo logs

(1) ACTIVE: ACTIVE

(2) INACTIVE: INACTIVE

(3), CURRENT: CURRENT status (Usage Status)

2. Expand two groups of online logs for each instance to four groups of online logs for each instance.

3. Expand each log file from 50 MB to 200 MB.

Note:

1. When deleting online logs, you must ensure that each instance has two log files.

2. When online logs are added, the thread parameter must be added to the script to identify an instance than that of a single instance.

3. The main dynamic performance views are v $ log and v $ logfile.

4. Online logs must be INACTIVE when deleted.

5. implemented when the business volume drops at night

The procedure is as follows:

1. Use tail-f alert *. the ora command further confirms the above judgment and finds that the online redo log switching time is about 3 minutes. When the log is switched again, the log file status is ACTIVE, not INACTIVE (not in use)

2. query the log group status

Select * from v $ log order by thread #, group #;

Query the member information of a log

Select * from v $ logfile;

3. Backup of necessary files

(1) backup initialization parameter file

Create pfile = '/home/oracle/bak_pfile_20130427_add_redolog.ora' from spfile;

(2) backup control files

Alter database backup controlfile to/home/oracle/bak_controlfile_20130427_add_redolog.ora ';

4. Add online log files

Alter database add logfile thread 1 group 5 ('+ DATADG/apts/onlinelog/group5_1', '+ ARCHDG/apts/onlinelog/group5_2') size 200 M;
Alter database add logfile thread 2 group 6 ('+ DATADG/apts/onlinelog/group6_1', '+ ARCHDG/apts/onlinelog/group6_2') size 200 M;

Alter database add logfile thread 1 group 7 ('+ DATADG/apts/onlinelog/group7_1', '+ ARCHDG/apts/onlinelog/group7_2') size 200 M;
Alter database add logfile thread 2 group 8 ('+ DATADG/apts/onlinelog/group8_1', '+ ARCHDG/apts/onlinelog/group8_2') size 200 M;

5. view the added file information

Select * from v $ log order by thread #, group #;

Query the member information of a log

Select * from v $ logfile;

6. Switch the online log file to the new file, and change the status of the online log files 1, 2, 3, and 4 to INACTIVE.

Alter system switch logfile;

Alter system checkpoint;

7. Delete 1, 2, 3, and 4 groups of online log files

Alter database drop logfile group 1;

Alter database drop logfile group 2;

Alter database drop logfile group 3;

Alter database drop logfile group 4;

A problem is found here. If the online log file is created by yourself, it is possible that the file in the disk array is not deleted after the DELETE command is used, as a result, an error is reported when space is wasted and the group with the same name is rebuilt,

In this case, you need to use the ORACLE-provided ASM maintenance command asmcmd to enter the disk array to delete the response file,

8. Rebuild online logs 1, 2, 4, and allocate MB space.

Alter database add logfile thread 1 group 1 ('+ DATADG/apts/onlinelog/group1_1', '+ ARCHDG/apts/onlinelog/group1_2') size 200 M;
Alter database add logfile thread 2 group 2 ('+ DATADG/apts/onlinelog/group2_1', '+ ARCHDG/apts/onlinelog/group2_2') size 200 M;

Alter database add logfile thread 1 group 3 ('+ DATADG/apts/onlinelog/group3_1', '+ ARCHDG/apts/onlinelog/group3_2') size 200 M;
Alter database add logfile thread 2 group 4 ('+ DATADG/apts/onlinelog/group4_1', '+ ARCHDG/apts/onlinelog/group4_2') size 200 M;

9. Use the command to switch the log file and save the record filing space to check whether the file is normal.

Alter system switch logfile;

Select * from v $ log order by thread #, group #;

Select * from v $ logfile;

Alter system checkpoint;

The operation is complete.

This article is from the "fallen stars" blog. Please keep this source