Explanation of Parameters of Control file used while creation in Oracle Database

 CREATE CONTROLFILE REUSE DATABASE "ORCLTRN" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '+DATADG/ORCLTRN/ONLINELOG/group_1.262.1201513375',

    '+RECODG/ORCLTRN/ONLINELOG/group_1.257.1201513377'

  ) SIZE 200M BLOCKSIZE 512,

  GROUP 2 (

    '+DATADG/ORCLTRN/ONLINELOG/group_2.263.1201513375',

    '+RECODG/ORCLTRN/ONLINELOG/group_2.258.1201513379'

  ) SIZE 200M BLOCKSIZE 512,

  GROUP 3 (

    '+DATADG/ORCLTRN/ONLINELOG/group_3.264.1201513379',

    '+RECODG/ORCLTRN/ONLINELOG/group_3.259.1201513381'

  ) SIZE 200M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '+DATADG/ORCLTRN/DATAFILE/system.257.1201512975',

  '+DATADG/ORCLTRN/DATAFILE/hr2.268.1201691573',

  '+DATADG/ORCLTRN/DATAFILE/sysaux.258.1201513239',

  '+DATADG/ORCLTRN/DATAFILE/tbs_training.270.1201772629',

  '+DATADG/ORCLTRN/DATAFILE/users.267.1201689687',

  '+DATADG/ORCLTRN/DATAFILE/users.260.1201513275',

  '+DATADG/ORCLTRN/DATAFILE/undotbs2.269.1201693891',

  '+DATADG/ORCLTRN/DATAFILE/tbs_auditors.271.1201779319',

  '+DATADG/ORCLTRN/DATAFILE/tbs_viewers.272.1201779353'

CHARACTER SET AL32UTF8

;

Explanation:

It used to recreate the control file for a database named ORCLTRN. This is typically done during database recovery, duplication, or migration. Let's break down and explain each part:

---

🔧 CREATE CONTROLFILE REUSE DATABASE "ORCLTRN" RESETLOGS ARCHIVELOG

• CREATE CONTROLFILE: Begins the process of creating a new control file, which is critical for Oracle to keep track of database structure.

• REUSE: Tells Oracle to reuse existing control files if they exist—avoiding errors due to file name conflicts.

• DATABASE "ORCLTRN": Specifies the database name.

• RESETLOGS: Resets the redo log sequence numbers. Used after incomplete recovery or restoring a backup.

• ARCHIVELOG: Puts the database into ARCHIVELOG mode, meaning redo logs will be archived for recovery purposes.

---

🔢 Control File Parameters

• MAXLOGFILES 16: Maximum number of redo log groups.

• MAXLOGMEMBERS 3: Max members (multiplexed copies) per redo log group.

• MAXDATAFILES 100: Max number of database datafiles.

• MAXINSTANCES 8: Max RAC (Real Application Clusters) instances.

• MAXLOGHISTORY 292: Max number of archived log entries Oracle can track.

---

🔁 LOGFILE Section

Defines the redo log groups and their multiplexed members:

GROUP 1 (
  '+DATADG/ORCLTRN/ONLINELOG/group_1.262.1201513375',
  '+RECODG/ORCLTRN/ONLINELOG/group_1.257.1201513377'
) SIZE 200M BLOCKSIZE 512,

Each GROUP:

• Has 2 members for redundancy.

• Located on different ASM disk groups: +DATADG and +RECODG.

• SIZE 200M: Size of each log file.

• BLOCKSIZE 512: Block size (default for redo logs).

There are 3 such redo log groups (Group 1, 2, and 3).

---

🗃 DATAFILE Section

Lists all datafiles in the database. These files are located on the +DATADG ASM disk group.

Examples:

• system, sysaux, undotbs2 → core system files

• users → default tablespace for users

• tbs_training, tbs_auditors, tbs_viewers, hr2 → user-defined tablespaces

---

📘 CHARACTER SET AL32UTF8

Defines the database character set, AL32UTF8, which is Unicode-compliant and recommended for international applications.

---

🚫 Missing or Commented Out

-- STANDBY LOGFILE

This line is commented out. It indicates that standby log files (used for Data Guard configuration) are not included in this control file creation.

---

🧠 Summary

This command:

• Recreates the control file for the Oracle database ORCLTRN.

• Uses existing redo log and datafile paths.

• Enables ARCHIVELOG mode.

• Uses multiplexed redo logs on separate disk groups.

• Is likely run after restoring or migrating a database and requires RESETLOGS.

⚠️ Caution: Creating control files is a critical operation. Running this incorrectly or without proper backup can lead to data loss. Always verify file paths, database state, and intended recovery scenario.