Understand the Oracle control file through the Trace file

The alterdatabasebackupcontrolfiletotrace command in oracle is used to back up the control file as a text file with the. trc suffix. The file name format is sid.

The alter database backup controlfile to trace command in oracle is used to back up the control file as a text file with the. trc suffix. The file name format is sid.

The alter database backup controlfile to trace command in Oracle is used to back up the control file as a text file with the. trc suffix. The file name is in the format of sid_ora_pid.trc, which is stored at the location specified by the parameter user_dump_dest. This file stores the SQL statement used to create a new control file. We can use it to get a rough idea about the content in the control file. Orcl @ ORCL> select * from v $ version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0-Production
PL/SQL Release 9.2.0.1.0-Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0-Production
NLSRTL Version 9.2.0.1.0-Production
Orcl @ ORCL> show parameter user_dump_dest; NAME TYPE VALUE
-----------------------------------------------------------------------------
User_dump_dest string D: \ oracle \ admin \ orcl \ udump
Orcl @ ORCL> alter database backup controlfile to trace;

Database altered.

In the Directory D: \ oracle \ admin \ orcl \ udump, find the newly generated file, which is the backup of the control file just now.
Content in this file:

1. Notes

* ** Session id: (9.5) 20:40:22. 000
*** 20:40:22. 000
# The following are current System-scope REDO Log Archival related
# Parameters and can be encoded in the database initialization file.
#
# LOG_ARCHIVE_DEST =''
# LOG_ARCHIVE_DUPLEX_DEST =''
#
# LOG_ARCHIVE_FORMAT = ARC % S. % T
# REMOTE_ARCHIVE_ENABLE = TRUE
# LOG_ARCHIVE_MAX_PROCESSES = 2
# STANDBY_FILE_MANAGEMENT = MANUAL
# STANDBY_ARCHIVE_DEST = % ORACLE_HOME % \ RDBMS
# FAL_CLIENT =''
# FAL_SERVER =''
#
# LOG_ARCHIVE_DEST_1 = 'location = D: \ oracle \ RDBMS'
# LOG_ARCHIVE_DEST_1 = 'mandatory NOREOPEN nodelay'
# LOG_ARCHIVE_DEST_1 = 'arch noaffrem sync'
# LOG_ARCHIVE_DEST_1 = 'noregister noalternate nodependency'
# LOG_ARCHIVE_DEST_1 = 'nomax _ FAILURE NOQUOTA_SIZE NOQUOTA_USED'
# LOG_ARCHIVE_DEST_STATE_1 = ENABLE
#
# Below are two sets of SQL statements, each of which creates a new
# Control file and uses it to open the database. The first set opens
# The database with the NORESETLOGS option and shoshould be used only if
# The current versions of all online logs are available. The second
# Set opens the database with the RESETLOGS option and shocould be used
# If online logs are unavailable.
# The appropriate set of statements can be copied from the trace
# A script file, edited as necessary, and executed when there is
# Need to re-create the control file.
#
2. NORESETLOGS case SQL (including the physical location and size of database log files and data files)

* ** When online logs are available, use the following commands to recreate the control file.

# Set #1. NORESETLOGS case
#
# The following commands will create a new control file and use it
# To open the database.
# Data used by the recovery manager will be lost. Additional logs may
# Be required for media recovery of offline data files. Use this
# Only if the current version of all online logs are available.

STARTUP NOMOUNT
Create controlfile reuse database "ORCL" NORESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
# MAXLOGHISTORY 226
LOGFILE
GROUP 1 'd: \ ORACLE \ ORADATA \ ORCL \ redo01.log' SIZE 100 M,
GROUP 2 'd: \ ORACLE \ ORADATA \ ORCL \ REDO02.LOG 'SIZE 100 M,
GROUP 3 'd: \ ORACLE \ ORADATA \ ORCL \ REDO03.LOG 'size 100 M
-- STANDBY LOGFILE
DATAFILE
'D: \ ORACLE \ ORADATA \ ORCL \ SYSTEM01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ UNDOTBS01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ CWMLITE01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ DRSYS01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ EXAMPLE01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ INDX01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ ODM01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ TOOLS01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ USERS01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ XDB01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ SCOTT_TABLAESPACE.ORA ',
'D: \ ORACLE \ ORADATA \ ORCL \ TEST. DBF'
Character set ZHS16GBK
;
* ********** Rman information *******

# Configure RMAN configuration record 1
Variable recno number;
EXECUTE: RECNO: = SYS. DBMS_BACKUP_RESTORE.SETCONFIG ('retention policy', 'to recovery window of 7 DAYS ');
# Configure RMAN configuration record 2
Variable recno number;
EXECUTE: RECNO: = SYS. DBMS_BACKUP_RESTORE.SETCONFIG ('controlfile AUTOBACKUP ', 'on ');

* ***** Other information ******

# Recovery is required if any of the datafiles are restored backups,
# Or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
Alter database open;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
Alter tablespace temp add tempfile 'd: \ ORACLE \ ORADATA \ ORCL \ TEMP01.DBF'
SIZE 41943040 reuse autoextend on next 655360 MAXSIZE 32767 M;
# End of tempfile additions.
3. RESETLOGS case SQL

Use these SQL statements to construct control files when online logs are unavailable. All online logs will be lost, and all backups will be invalid. The information contained is the same as the same side.

# Set #2. RESETLOGS case
#
# The following commands will create a new control file and use it
# To open the database.
# The contents of online logs will be lost and all backups will
# Be invalidated. Use this only if online logs are damaged.
STARTUP NOMOUNT
Create controlfile reuse database "ORCL" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
# MAXLOGHISTORY 226
LOGFILE
GROUP 1 'd: \ ORACLE \ ORADATA \ ORCL \ redo01.log' SIZE 100 M,
GROUP 2 'd: \ ORACLE \ ORADATA \ ORCL \ REDO02.LOG 'SIZE 100 M,
GROUP 3 'd: \ ORACLE \ ORADATA \ ORCL \ REDO03.LOG 'size 100 M
-- STANDBY LOGFILE
DATAFILE
'D: \ ORACLE \ ORADATA \ ORCL \ SYSTEM01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ UNDOTBS01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ CWMLITE01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ DRSYS01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ EXAMPLE01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ INDX01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ ODM01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ TOOLS01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ USERS01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ XDB01.DBF ',
'D: \ ORACLE \ ORADATA \ ORCL \ SCOTT_TABLAESPACE.ORA ',
'D: \ ORACLE \ ORADATA \ ORCL \ TEST. DBF'
Character set ZHS16GBK
;
# Configure RMAN configuration record 1
Variable recno number;
EXECUTE: RECNO: = SYS. DBMS_BACKUP_RESTORE.SETCONFIG ('retention policy', 'to recovery window of 7 DAYS ');
# Configure RMAN configuration record 2
Variable recno number;
EXECUTE: RECNO: = SYS. DBMS_BACKUP_RESTORE.SETCONFIG ('controlfile AUTOBACKUP ', 'on ');
# Recovery is required if any of the datafiles are restored backups,
# Or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
Alter database open resetlogs;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
Alter tablespace temp add tempfile 'd: \ ORACLE \ ORADATA \ ORCL \ TEMP01.DBF'
SIZE 41943040 reuse autoextend on next 655360 MAXSIZE 32767 M;
# End of tempfile additions.