Friday, November 04, 2011

Maxlogfiles, maxlogmembers etc.

As far as I know, this information is accurate up to and including Oracle 9. When you run the create database command, you can specify various options e.g.
 
Maxlogfiles (the maximum number of redo log groups which the database may have).
Maxlogmembers (the maximum number of members allowed in each redo log group).
Etc.
 
To alter these values subsequently, you need to recreate the control file. Once the database has been created, you can find out what they were set to as follows. One way is to backup the control file:
 
SQL> alter database backup controlfile to trace;
 
Database altered.
 
SQL>
 
This will produce a trace file in user_dump_dest. If you go there straight away and do an ls –t|more (assuming your database is running on UNIX) the trace file will be the first name on the list (unless your database happens to be generating lots of trace files for some reason):
 
TEST9 > ls -t|more
andrew_ora_459234.trc
etc.
 
The value(s) you want will be in this file, just after the create controlfile statement:
 
CREATE CONTROLFILE REUSE DATABASE "ANDREW" RESETLOGS  NOARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 300
    MAXINSTANCES 1
    MAXLOGHISTORY 1470
 
If that is too long-winded, you can see the maxlogfiles setting like this:
 
SQL> select records_total
  2  from v$controlfile_record_section
  3  where type = 'REDO LOG'
  4  /
 
RECORDS_TOTAL
-------------
           16
 
SQL>
 
This SQL gives you maxlogmembers:
 
SQL> conn / as sysdba
Connected.
SQL> l
  1* select dimlm from x$kccdi
SQL> /
 
     DIMLM
----------
         2
 
SQL>
 
The value of maxdatafiles is here:
 
SQL> select records_total
  2  from v$controlfile_record_section
  3  where type = 'DATAFILE'
  4  /
 
RECORDS_TOTAL
-------------
          300
 
SQL>
 
The following query shows maxinstances:
 
  1  select records_total
  2  from v$controlfile_record_section
  3* where type = 'REDO THREAD'
SQL> /
 
RECORDS_TOTAL
-------------
            1
 
SQL>
 
And finally, here is maxloghistory:
 
  1  select records_total
  2  from v$controlfile_record_section
  3* where type = 'LOG HISTORY'
SQL> /
 
RECORDS_TOTAL
-------------
         1470
 
SQL>

No comments: