Control file is so critical to the operation of the database, so it must be multiplexed (one or more control files can be copied). However, no matter how many copies of the control file are associated with an instance, only one of the control files is designated as primary for purposes of retrieving database metadata.
The ALTER DATABASE BACKUP CONTROLFILE TO TRACE command is one way to back up the control file. It produces a SQL script that you can use to re-create the database control file in case all multiplexed binary versions of the control file are lost due to a catastrophic failure.
This trace file can also be used, for example, to re-create a control file if the database needs to be renamed or to change various database limits that could not otherwise be changed without re-creating the entire database.
Steps to backup controlfile:
Find the path where oracle stores trace files:
[[email protected] trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 13 16:48:33 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> show parameter USER_DUMP_DEST;
NAME TYPE VALUE
———————————— ———– ——————————
user_dump_dest string /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace
SQL>
Go to trace path directory and remove all existing ones:
[[email protected] trace]$ cd /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/
[[email protected] trace]$ rm *
[[email protected] trace]$ ll
total 0
Login in to database as sysdba and execute command ALTER DATABASE BACKUP CONTROLFILE TO TRACE:
[[email protected] trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 13 16:52:40 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Database altered.
SQL>
Go to trace path directory and explore the content of alert_orcl.log
[[email protected] trace]$ cat alert_orcl.log
Tue Aug 13 16:53:15 2013
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Backup controlfile written to trace file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9818.trc
Completed: ALTER DATABASE BACKUP CONTROLFILE TO TRACE
[[email protected] trace]$
You will find where SQL script is stored. Explore the content of orcl_ora_9818.trc. Inside you will find two SQL scripts. First must be used if online logs are available and second must be used if online logs are unavailable. Below you will find the content:
[[email protected] trace]$ cat orcl_ora_9818.trc
Trace file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9818.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: home.test.al
Release: 2.6.32-200.13.1.el5uek
Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 9818, image: [email protected] (TNS V1-V3)
*** 2013-08-13 16:53:15.077
*** SESSION ID:(12.97) 2013-08-13 16:53:15.077
*** CLIENT ID:() 2013-08-13 16:53:15.077
*** SERVICE NAME:(SYS$USERS) 2013-08-13 16:53:15.077
*** MODULE NAME:([email protected] (TNS V1-V3)) 2013-08-13 16:53:15.077
*** ACTION NAME:() 2013-08-13 16:53:15.077
— The following are current System-scope REDO Log Archival related
— parameters and can be included in the database initialization file.
—
— LOG_ARCHIVE_DEST=”
— LOG_ARCHIVE_DUPLEX_DEST=”
—
— LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
—
— DB_UNIQUE_NAME=”orcl”
—
— LOG_ARCHIVE_CONFIG=’SEND, RECEIVE, NODG_CONFIG’
— LOG_ARCHIVE_MAX_PROCESSES=4
— STANDBY_FILE_MANAGEMENT=MANUAL
— STANDBY_ARCHIVE_DEST=?/dbs/arch
— FAL_CLIENT=”
— FAL_SERVER=”
—
— LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST’
— LOG_ARCHIVE_DEST_1=’MANDATORY NOREOPEN NODELAY’
— LOG_ARCHIVE_DEST_1=’ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC’
— LOG_ARCHIVE_DEST_1=’NOREGISTER NOALTERNATE NODEPENDENCY’
— LOG_ARCHIVE_DEST_1=’NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME’
— LOG_ARCHIVE_DEST_1=’VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)’
— 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 should be used only if
— the current versions of all online logs are available. The second
— set opens the database with the RESETLOGS option and should be used
— if online logs are unavailable.
— The appropriate set of statements can be copied from the trace into
— a script file, edited as necessary, and executed when there is a
— need to re-create 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 Recovery Manager will be lost.
— Additional logs may be required for media recovery of offline
— Use this only if the current versions of all online logs are
— available.
— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “ORCL” NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘+DATA/orcl/onlinelog/group_1.261.823264035’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘+DATA/orcl/onlinelog/group_2.262.823264041’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘+DATA/orcl/onlinelog/group_3.263.823264045’ SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘+DATA/orcl/datafile/system.256.823263827’,
‘+DATA/orcl/datafile/sysaux.257.823263829’,
‘+DATA/orcl/datafile/undotbs1.258.823263829’,
‘+DATA/orcl/datafile/users.259.823263829’,
‘+DATA/orcl/datafile/example.265.823264121’,
‘+DATA/orcl/datafile/rman.267.823311675’
CHARACTER SET WE8MSWIN1252
;
— Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE ‘+FRA’;
— ALTER DATABASE REGISTER LOGFILE ‘+FRA’;
— Recovery is required if any of the datafiles are restored backups,
— or if the last shutdown was not normal or immediate.
RECOVER DATABASE
— All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
— 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 ‘+DATA/orcl/tempfile/temp.264.823264093’
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
— End of tempfile additions.
—
— Set #2. RESETLOGS case
—
— The following commands will create a new control file and use it
— to open the database.
— Data used by Recovery Manager will be lost.
— The contents of online logs will be lost and all backups will
— be invalidated. Use this only if online logs are damaged.
— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘+DATA/orcl/onlinelog/group_1.261.823264035’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘+DATA/orcl/onlinelog/group_2.262.823264041’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘+DATA/orcl/onlinelog/group_3.263.823264045’ SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘+DATA/orcl/datafile/system.256.823263827’,
‘+DATA/orcl/datafile/sysaux.257.823263829’,
‘+DATA/orcl/datafile/undotbs1.258.823263829’,
‘+DATA/orcl/datafile/users.259.823263829’,
‘+DATA/orcl/datafile/example.265.823264121’,
‘+DATA/orcl/datafile/rman.267.823311675’
CHARACTER SET WE8MSWIN1252
;
— Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE ‘+FRA’;
— ALTER DATABASE REGISTER LOGFILE ‘+FRA’;
— 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 ‘+DATA/orcl/tempfile/temp.264.823264093’
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
— End of tempfile additions.
—
[[email protected] trace]$
