Hi all,
Many times it is required to take backup of controlfile or to recreate a controlfile either for cloning a database or restore the database.Hence u can generate a script to backup the controlfile as shown
Log in SQL*PLUS as sys and connect to database
sqlplus
/ as sysdba
Sql> alter database backup controlfile to trace;
Sql> exit
Then go to udump.The udump is genrally placed in $ORACLE_BASE/admin/
& check the latest generated trace file. If u open the file in your favourite text editor u will
find following trace file
Dump file c:\oracle\admin\db1\udump\db1_ora_2604.trc
Tue Apr 03 12:07:32 2007
ORACLE V9.2.0.1.0 – Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Instance name: db1
Redo thread mounted by this instance: 1
Oracle process number: 8
Windows thread id: 2604, image: ORACLE.EXE
*** SESSION ID:(9.3) 2007-04-03 12:07:32.000
*** 2007-04-03 12:07:32.000
# 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=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=C:\oracle\ora92\RDBMS’
# LOG_ARCHIVE_DEST_1=’MANDATORY NOREOPEN NODELAY’
# LOG_ARCHIVE_DEST_1=’ARCH NOAFFIRM 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 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 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 “DB1″ NORESETLOGS NOARCHIVELOG
– SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ‘C:\ORACLE\ORADATA\DB1\REDO01.LOG’ SIZE 100M,
GROUP 2 ‘C:\ORACLE\ORADATA\DB1\REDO02.LOG’ SIZE 100M,
GROUP 3 ‘C:\ORACLE\ORADATA\DB1\REDO03.LOG’ SIZE 100M
– STANDBY LOGFILE
DATAFILE
‘C:\ORACLE\ORADATA\DB1\SYSTEM01.DBF’,
‘C:\ORACLE\ORADATA\DB1\UNDOTBS01.DBF’,
‘C:\ORACLE\ORADATA\DB1\INDX01.DBF’,
‘C:\ORACLE\ORADATA\DB1\TOOLS01.DBF’,
‘C:\ORACLE\ORADATA\DB1\USERS01.DBF’,
‘C:\ORACLE\ORADATA\DB1\DATA01.DBF’
CHARACTER SET WE8MSWIN1252
;
# 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 ‘C:\ORACLE\ORADATA\DB1\TEMP01.DBF’
SIZE 41943040 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.
# 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 “DB1″ RESETLOGS NOARCHIVELOG
– SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ‘C:\ORACLE\ORADATA\DB1\REDO01.LOG’ SIZE 100M,
GROUP 2 ‘C:\ORACLE\ORADATA\DB1\REDO02.LOG’ SIZE 100M,
GROUP 3 ‘C:\ORACLE\ORADATA\DB1\REDO03.LOG’ SIZE 100M
– STANDBY LOGFILE
DATAFILE
‘C:\ORACLE\ORADATA\DB1\SYSTEM01.DBF’,
‘C:\ORACLE\ORADATA\DB1\UNDOTBS01.DBF’,
‘C:\ORACLE\ORADATA\DB1\INDX01.DBF’,
‘C:\ORACLE\ORADATA\DB1\TOOLS01.DBF’,
‘C:\ORACLE\ORADATA\DB1\USERS01.DBF’,
‘C:\ORACLE\ORADATA\DB1\DATA01.DBF’
CHARACTER SET WE8MSWIN1252
;
# 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 ‘C:\ORACLE\ORADATA\DB1\TEMP01.DBF’
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
# End of tempfile additions.
#
U can modify the parameters and create your own script to regenerate the controlfile as shown
CREATE CONTROLFILE REUSE DATABASE “DB1″ NORESETLOGS ARCHIVELOG
– SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ‘C:\ORACLE\ORADATA\DB1\REDO01.LOG’ SIZE 100M,
GROUP 2 ‘C:\ORACLE\ORADATA\DB1\REDO02.LOG’ SIZE 100M,
GROUP 3 ‘C:\ORACLE\ORADATA\DB1\REDO03.LOG’ SIZE 100M
– STANDBY LOGFILE
DATAFILE
‘C:\ORACLE\ORADATA\DB1\SYSTEM01.DBF’,
‘C:\ORACLE\ORADATA\DB1\UNDOTBS01.DBF’,
‘C:\ORACLE\ORADATA\DB1\INDX01.DBF’,
‘C:\ORACLE\ORADATA\DB1\TOOLS01.DBF’,
‘C:\ORACLE\ORADATA\DB1\USERS01.DBF’,
‘C:\ORACLE\ORADATA\DB1\DATA01.DBF’
CHARACTER SET WE8MSWIN1252
running this script from sqlplus when database is nomount state will recreate the controlfile.Besides u can change whatever parameter u want such MAXLOGFILES,MAXLOGMEMBERS etc
In many cases it is realised that maxdatafiles or any other parameter needs to be modified . In that case developing such a script and changing the desired parameter can be useful.
