The MAXLOGFILES parameter used in the CREATE DATABASE statement determines the maximum number of groups of redo log files for each database.
Group values can range from 1 to MAXLOGFILES. When the compatibility level is set earlier than 10.2.0, the only way to override this upper limit is to re-create the database or its control file. Therefore, it is important to consider this limit before creating a database. When compatibility is set to 10.2.0 or later, you can exceed the MAXLOGFILES limit, and the control files expand as needed. If MAXLOGFILES is not specified for the CREATE DATABASE statement, then the database uses an operating system specific default value.
Key：compatibility<10.2.0，MAXLOGFILES限制有效，如果要超越这个限制，必须重建控制文件；如果compatibility>=10.2.0，MAXLOGFILES限制无效。
SQL> startup nomount
ORACLE 例程已经启动。Total System Global Area 468758528 bytes
Fixed Size 1333760 bytes
Variable Size 234882560 bytes
Database Buffers 209715200 bytes
Redo Buffers 22827008 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE “TEST” NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 4
3 MAXLOGMEMBERS 2
4 MAXDATAFILES 32
5 MAXINSTANCES 16
6 MAXLOGHISTORY 1752
7 LOGFILE
8 GROUP 1 (
9 ‘D:\ORACLE\ORADATA\TEST\ONLINELOG\O1_MF_1_5G54HL5V_.LOG’,
10 ‘D:\ORACLE\ORADATA\TEST\ONLINELOG\LOG_01_02.LOG’
11 ) SIZE 50M,
12 GROUP 2 (
13 ‘D:\ORACLE\ORADATA\TEST\ONLINELOG\O1_MF_2_5G54HM4W_.LOG’,
14 ‘D:\ORACLE\ORADATA\TEST\ONLINELOG\LOG_02_02.LOG’
15 ) SIZE 50M,
16 GROUP 3 (
17 ‘D:\ORACLE\ORADATA\TEST\ONLINELOG\O1_MF_3_5G54HNHN_.LOG’,
18 ‘D:\ORACLE\ORADATA\TEST\ONLINELOG\LOG_03_02.LOG’
19 ) SIZE 50M
20 — STANDBY LOGFILE
21 DATAFILE
22 ‘D:\ORACLE\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_5G54HPM1_.DBF’,
23 ‘D:\ORACLE\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_5G54HVTC_.DBF’,
24 ‘D:\ORACLE\ORADATA\TEST\DATAFILE\O1_MF_SYS_UNDO_5G54HYF5_.DBF’,
25 ‘D:\ORACLE\ORADATA\TEST\DATAFILE\O1_MF_USERS_5G54HZ3X_.DBF’
26 CHARACTER SET ZHS16GBK
27 ;控制文件已创建。
已用时间: 00: 00: 01.48
SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TI
——- ——– ———- ———- ——– — ———— ————- ——–
1 1 0 52428800 2 YES UNUSED 427634 06-11? -
3 1 0 52428800 2 YES UNUSED 405793 06-11? -
2 1 28 52428800 2 NO CURRENT 427635 06-11? -已用时间: 00: 00: 00.06
SQL> show parameter compaNAME TYPE VALUE
———————————— ———– ————————————–
compatible string 11.0.0
plsql_v2_compatibility boolean FALSESQL> alter database add logfile group 4;
数据库已更改。
已用时间: 00: 00: 04.25
SQL> alter database add logfile group 5;数据库已更改。
已用时间: 00: 00: 04.43
SQL> alter database add logfile group 6;数据库已更改。
已用时间: 00: 00: 04.31
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
——- ——– ———- ———- ——– — ———— ————- ——————-
1 1 29 52428800 2 NO CURRENT 430584 2009-11-06 14:53:22
2 1 28 52428800 2 NO INACTIVE 427635 2009-11-06 13:19:03
3 1 0 52428800 2 YES UNUSED 405793 2009-11-06 11:56:05
4 1 0 104857600 2 YES UNUSED 0
5 1 0 104857600 2 YES UNUSED 0
6 1 0 104857600 2 YES UNUSED 0已选择6行。
警告日志相关记录：
Fri Nov 06 14:50:12 2009
alter database add logfile group 4
Completed: alter database add logfile group 4
alter database add logfile group 5
Expanded controlfile section 3 from 4 to 80 records
The number of logical blocks in section 3 remains the same
Fri Nov 06 14:50:24 2009
Completed: alter database add logfile group 5
alter database add logfile group 6
Completed: alter database add logfile group 6
设置compatible=10.1.0后重建数据库，发现日志组确实不能超越maxlogfiles限制:
Related PostsSQL> startup nomount
ORACLE 例程已经启动。Total System Global Area 468758528 bytes
Fixed Size 1333760 bytes
Variable Size 234882560 bytes
Database Buffers 209715200 bytes
Redo Buffers 22827008 bytes
SQL> create database test
2 maxlogfiles 3
3 maxinstances 1
4 extent management local;数据库已创建。
已用时间: 00: 00: 21.65
SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME
——- ——– ———- ———- ——– —— ———— ————- ———-
1 1 1 104857600 1 NO CURRENT 1 06-11? -09
2 1 0 104857600 1 YES UNUSED 0已用时间: 00: 00: 00.04
SQL> alter database add logfile group 3;数据库已更改。
已用时间: 00: 00: 02.01
SQL> alter database add logfile group 4;
alter database add logfile group 4
*
第 1 行出现错误:
ORA-01185: ?????? 4 ??C:\>set NLS_LANG=american_america
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Nov 6 15:10:00 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsERROR:
ORA-06553: PLS-213: package STANDARD not accessible
SQL> alter database add logfile group 4;
alter database add logfile group 4
*
ERROR at line 1:
ORA-01185: logfile group number 4 is invalidSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
——- ——– ———- ———- ——– — ———— ————- ——————-
1 1 1 104857600 1 NO CURRENT 1 2009-11-06 15:07:18
2 1 0 104857600 1 YES UNUSED 0
3 1 0 104857600 1 YES UNUSED 0Elapsed: 00:00:00.03
SQL> show parameter compaNAME TYPE VALUE
———————————— ———– ————————————————-
compatible string 10.1.0.0SQL> alter system set compatible=’10.2.0′ scope=spfile;
System altered.
Elapsed: 00:00:00.01
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.Total System Global Area 468758528 bytes
Fixed Size 1333760 bytes
Variable Size 234882560 bytes
Database Buffers 209715200 bytes
Redo Buffers 22827008 bytes
Database mounted.
SQL> show parameter compaNAME TYPE VALUE
———————————— ———– ————————
compatible string 10.2.0
plsql_v2_compatibility boolean FALSE
SQL> alter database add logfile group 4;Database altered.
Elapsed: 00:00:02.26