Stay at Home!! Be Safe!! Take Care!!

Thanks all my viewers for your support, I am an Oracle Ace now. For Any queries, please mail us at support@funoracleapps.com

How to find MAXxxxxxx Control file parameters in Data Dictionary in Oracle

No comments

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;



If you like please follow and comment

No comments :

Post a Comment