Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: MAXDATAFILES - How to tell what files constitute this?
Tom
The value for MAXDATAFILES is ONLY for data files, not log files etc. This includes files that you may have dropped (probably the situation you've hit). To fix, you can recreate the control file using the syntax:
CREATE CONTROLFILE [REUSE]
DATABASE name
[LOGFILE filespec [, filespec] ...]
RESETLOGS | NORESETLOGS
[MAXLOGFILES integer] [DATAFILE filespec [, filespec] ...] [MAXDATAFILES integer] [MAXINSTANCES integer] [ARCHIVELOG | NOARCHIVELOG] [SHARED | EXCLUSIVE]
Parameters:
REUSE: If present the control files may already exist. The
new control files will overwrite the existing files. If this option is missing, the new control files must not yet exist. As in CREATE DATABASE, the names of the control files are determined by the init.ora parameter control_files. DATABASE: Must match the database names in the data and log files. LOGFILE: This clause lists all the online logs that will be used for this database. If not specified the port dependent defaults will be assumed. The interpretation of the filespecs depends on the next parameter. RESETLOGS: If this flag is present the current contents of the online logs are ignored. The new control files will contain flags requiring ALTER DATABASE OPEN RESETLOGS, which initializes the logs. Media recovery may be applied as needed before the open. Note that either RESETLOGS or NORESETLOGS must be specified. It is safest to choose RESETLOGS and follow it with normal media recovery. NORESETLOGS: If specified, the log files must be the current online logs. They must not be restored backups, and all log files must be listed. Their headers are read to construct the control file entries. They are used for recovery. If archiving is enabled all the online logs must be archived, even if they were already archived. The SIZE option in the filespecs, if present, will be used to validate the size of the file named. MAXLOGFILES: Same as for CREATE DATABASE. May be different than the value in the original control file, but it may not be smaller the maximum number of log files the database ever contained - including ones that have been dropped. Set it greater than or equal to the value used at CREATE DATABASE time. DATAFILE: To ensure proper behavior, all datafiles for the database must be listed. It is possible to omit a non system tablespace file only if media recovery is enabled and you will not be doing an open reset logs on the first open after the create controlfile. If the omitted file(s) contain(s) active rollback segments, the open will most likely fail, in which case the missing datafile(s) must be found, and the controlfile recreated. All datafiles listed must be accessible since they are assumed to be online. They MAY be backup copies needing recovery. Their headers are read to construct the control file records. The SIZE option in the filespecs, if present, is used to validate the size of the file named. The reuse option is ignored. The next database open validates that all the files are specified and that the sizes match. MAXDATAFILES: Same as for CREATE DATABASE. May be different than the value in the original control file, but it may not be smaller the maximum number of data files the database ever contained - including ones that have been dropped. MAXINSTANCES: Same as for CREATE DATABASE. May be different than the value in the original control file. [NO]ARCHIVELOG: Same as for CREATE DATABASE. May be different than the value in the original control file. If you wish to archive logs, it is recommended that the ARCHIVELOG option be used with CREATE CONTROLFILE even though the option can later be enabled with an ALTER DATABASE command. NOARCHIVELOG is the default.
SHARED: Same as for CREATE DATABASE.
EXCLUSIVE: Same as for CREATE DATABASE.
HTH. Pete
Tom A wrote:
> The parameter MAXDATAFILES that is defined at database creation and in part
> determines how large the controlfile, PGA, etc.. is easy enough to
> understand.
>
> My question is what files make up the number that goes towards MAXDATAFILES
> in Oracle 7.3?
>
> We ran up against this limit the other day - no problem fixing it - but I
> would like to be able to determine if we are getting close to that limit in
> the other databases by periodically querying the databases. The name
> implies 'datafiles' but there were only 20 datafiles (count(*) on
> DBA_DATA_FILES).
>
> Old situation:
>
> MAXDATAFILES = 30
> 20 datafiles. select count(*) from dba_data_files
> 6 log files. select count(*) from v$logfiles - or something like that
> 2 control files. select count(*) from v$controlfiles - or something like
> that
> 2 init.ora files select ?????????????? Is this info stored in the database
> somewhere?
>
> This makes 30 but is it a coincidence?
>
> TIA,
> Tom.
--
Regards
Pete
Peter Sharman Email: psharman_at_us.oracle.com WISE Course Development Manager Phone: +1.650.607.0109 (int'l) Worldwide Internal Services Education (650)607 0109 (local)San Francisco
SQL> select standard_disclaimer, witty_remark 2 from company_requirements;
Opinions are mine and do not necessarily reflect those of Oracle Corporation
"Controlling application developers is like herding cats."
Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!"
Bruce Pihlamae, long term ORACLE DBA
Received on Wed Mar 10 1999 - 11:12:21 CST