Create Control file in 11g. Diff between Resetlogs & Noresetlogs when creating control file

In 11g, a new parameter called "diagnostic_dest" determines where files will are created.

In 11g, diagnostic_dest replaces background_dump_dest, user_dump_dest and core_dump_dest.

Two ways to backup Control File:

1. Control file in binary format.

2. Control file in human readable format.

Backing up the contol file in binary format.

sql> alter database backup controlfile to '/some/arbitrary/path';

Backing up the contol file in human readable format.

sql > alter database backup control file to trace;

This will give you control file script.

check you diagnostics location for obtaining the trace file and will be of name SID_ora_pid.trc

If you want trace file to be generated to some other location apart from diagnostics location

sql> alter database backup controlfile to trace as controlfile.txt;

By default this will create controlfile in $ORACLE_HOME/dbs location.

sql> alter database backup controlfile to trace as '/some/arbitrary/path';

This will create control file script the location you have mentioned.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "demo" NORESETLOGS NOARCHIVELOG

    MAXLOGFILES 32

    MAXLOGMEMBERS 2

    MAXDATAFILES 32

    MAXINSTANCES 1

    MAXLOGHISTORY 449

LOGFILE

  GROUP 1 '/path/oracle/dbs/t_log1.f'  SIZE 500K,

  GROUP 2 '/path/oracle/dbs/t_log2.f'  SIZE 500K

# STANDBY LOGFILE

DATAFILE

  '/path/oracle/dbs/t_db1.f',

  '/path/oracle/dbs/dbu19i.dbf',

  '/path/oracle/dbs/tbs_11.dbf',

  '/path/oracle/dbs/smundo.dbf',

  '/path/oracle/dbs/demo.dbf'

CHARACTER SET WE8DEC

;

NOTE : Donot leave any line empty while in control file script otherwise it will throw error SP-

Diff Betwen Reuse and set Database option of control file

REUSE : Specify REUSE to indicate that existing control files identified by the initialization
parameter CONTROL_FILES can be reused and overwritten.

SET : Use SET DATABASE to change the name of the database.

ARCHIVELOG | NOARCHIVELOG

Specify ARCHIVELOG to archive the contents of redo log files before reusing them.

This clause prepares for the possibility of media recovery as well as instance or system failure recovery.

If you omit both the ARCHIVELOG clause and NOARCHIVELOG clause, then Oracle Database chooses noarchivelog mode by default.

After creating the control file, you can change between archivelog mode and noarchivelog mode with the ALTER DATABASE statement.

RESETLOGS

Specify RESETLOGS if you want Oracle Database to ignore the contents of the files listed in the LOGFILE clause.These files do not have to exist. Each redo_log_file_spec in the LOGFILE clause must specify the SIZE parameter.The database assigns all online redo log file groups to thread 1 and enables this thread for public use by any instance.After using this clause, you must open the database using the RESETLOGS clause of the ALTER DATABASE statement.

NORESETLOGS

Specify NORESETLOGS if you want Oracle Database to use all files in the LOGFILE clause as they were when the database was last open.These files must exist and must be the current online redo log files rather than restored backups.The database reassigns the redo log file groups to the threads to which they were previously assigned and reenables the threads as they were previously enabled.

Alter database open Resetlogs/Noresetlogs.

RESETLOGS : resets the current log sequence number to 1 and invalidates all redo entries in the online and

archived redo log files. You must use this option to open the database after performing media recovery

with a backup controlfile. After opening the database with this option you should perform a complete database backup.

NORESETLOGS

leaves the log sequence number and redo log files in their current state.