10g release 2的新特性可以看看,不够的时候应该是会自动扩展的, maxdatafiles应该以前的版本就能自动扩展了吧,上限应该是db_files吧


SELECT  records_total,records_used FROM v$controlfile_record_section WHERE TYPE='REDO LOG'

或用SQL>alter database backup controlfile to trace;在生的的脚本中可以得到这个值。



我测试的结果：
　记录数由５，变成了２１：

SQL> SELECT  records_total,records_used FROM v$controlfile_record_section WHERE TYPE='REDO LOG';

RECORDS_TOTAL RECORDS_USED
------------- ------------
            5            5

SQL> ALTER DATABASE
  ADD LOGFILE GROUP 6  ('/u01/app/oracle/oradata/PROD/disk1/redo06.log', '/u01/app/oracle/oradata/PROD/disk2/redo06_02.log')
  2    3        SIZE 100m;

Database altered.

SQL> SELECT  records_total,records_used FROM v$controlfile_record_section WHERE TYPE='REDO LOG';

RECORDS_TOTAL RECORDS_USED
------------- ------------
           21            6


SQL>alter database backup controlfile to trace;在生的的脚本中可以得到这个值：

没有加日志之前的数据：

--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/PROD/disk1/redo01.log'  SIZE 100M,
  GROUP 2 '/u01/app/oracle/oradata/PROD/disk1/redo02.log'  SIZE 100M,
  GROUP 3 '/u01/app/oracle/oradata/PROD/disk1/redo03.log'  SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/PROD/disk1/system01.dbf',
  '/u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf',
  '/u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf'
CHARACTER SET US7ASCII



加了６组日志之后的TRACE:



-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 21
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/PROD/disk1/redo01.log',
    '/u01/app/oracle/oradata/PROD/disk2/redo01_02.log'
  ) SIZE 100M,
  GROUP 2 (
    '/u01/app/oracle/oradata/PROD/disk1/redo02.log',
    '/u01/app/oracle/oradata/PROD/disk2/redo02_02.log'
  ) SIZE 100M,
  GROUP 3 (
    '/u01/app/oracle/oradata/PROD/disk1/redo03.log',
    '/u01/app/oracle/oradata/PROD/disk2/redo03_02.log'
  ) SIZE 100M,
  GROUP 4 (
    '/u01/app/oracle/oradata/PROD/disk1/redo04.log',
    '/u01/app/oracle/oradata/PROD/disk2/redo04_02.log'
  ) SIZE 100M,
  GROUP 5 (
    '/u01/app/oracle/oradata/PROD/disk1/redo05.log',
    '/u01/app/oracle/oradata/PROD/disk2/redo05_02.log'
  ) SIZE 100M,
  GROUP 6 (
    '/u01/app/oracle/oradata/PROD/disk1/redo06.log',
    '/u01/app/oracle/oradata/PROD/disk2/redo06_02.log'




SQL> SELECT  records_total,records_used FROM v$controlfile_record_section WHERE TYPE='REDO LOG';

RECORDS_TOTAL RECORDS_USED
------------- ------------
           21            6





tail -f alert_PROD.log

ALTER DATABASE
  ADD LOGFILE GROUP 6  ('/u01/app/oracle/oradata/PROD/disk1/redo06.log', '/u01/app/oracle/oradata/PROD/disk2/redo06_02.log')
      SIZE 100m
Tue Dec 25 01:30:41 2012
Expanded controlfile section 3 from 5 to 21 records
The number of logical blocks in section 3 remains the same
Completed: ALTER DATABASE
  ADD LOGFILE GROUP 6  ('/u01/app/oracle/oradata/PROD/disk1/redo06.log', '/u01/app/oracle/oradata/PROD/disk2/redo06_02.log')
      SIZE 100m
Tue Dec 25 01:33:44 2012