How to find MAXxxxxxx Control file parameters in Data Dictionary in Oracle
How to find information about the following control file parameters in the data dictionary:
- MAXLOGFILES
- MAXDATAFILES
- MAXLOGHISTORY
- MAXLOGMEMBERS
- MAXINSTANCES
The values of these parameters are set either during CREATE DATABASE or CREATE CONTROLFILE scripts.
In all Oracle versions, the CREATE CONTROLFILE syntax can be regenerated
from the data dictionary using the below command.
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
The trace file will be generated in the location of the diagnostics and will contain the current MAX values for the database.
Example:
CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 SIZE 200 M ,
GROUP 2 SIZE 200 M ,
GROUP 3 SIZE 200 M ,
GROUP 4 SIZE 200 M ,
GROUP 5 SIZE 200 M
We can also find using below view
v$controlfile_record_section
and x$kccdi
SQL> select TYPE,RECORDS_TOTAL from v$controlfile_record_section;
TYPE RECORDS_TO
----------------- ----------
REDO LOG 32 --> MAXLOGFILES DATAFILE 30 --> MAXDATAFILES
CKPT PROG 8 --> MAXINSTANCES
REDO THREAD 8 --> MAXINSTANCES
LOG HISTORY 3317 --> MAXLOGHISTORY
MAXLOGMEMBERS:
--------------
Only MAXLOGMEMBERS is available via "x$kccdi.dimlm".
Query:
select 'MAXLOGMEMBERS'|| dimlm from x$kccdi;
Post a Comment
Post a Comment