Controlfile is the most important file of Oracle database. It can be backup as trace.
Controlfile 可以使用trace来进行备份。
C:\Documents and Settings\Administrator>sqlplus sys/fvt6eip@icmnlsdb as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Fri Jun 27 05:01:14 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database backup controlfile to trace;
Database altered.
SQL> show parameter user_dump_dest;
NAME TYPE
———————————— ——————————–
VALUE
——————————
user_dump_dest string
C:\ORACLE\PRODUCT\10.2.0\ADMIN
\ICMNLSDB\UDUMP
————————————————————————————–
# HJ’s comments
#Trace file location is C:\ORACLE\PRODUCT\10.2.0\ADMIN\ICMNLSDB\UDUMP
————————————————————————————–
SQL> host dir C:\ORACLE\PRODUCT\10.2.0\ADMIN\ICMNLSDB\UDUMP
Volume in drive C is C_Drive
Volume Serial Number is 34E8-5151
Directory of C:\ORACLE\PRODUCT\10.2.0\ADMIN\ICMNLSDB\UDUMP
06/27/2008 05:06 AM <DIR> .
06/27/2008 05:06 AM <DIR> ..
06/27/2008 05:03 AM 6,537 icmnlsdb_ora_3908.trc
06/27/2008 05:06 AM 0 sbtio.log
3 File(s) 8,033 bytes
2 Dir(s) 39,089,672,192 bytes free
————————————————————————————————-
icmnlsdb_ora_3908.trc 内容：
*** 2008-06-27 05:03:08.781
– 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_%R.%T
–
– DB_UNIQUE_NAME=”icmnlsdb”
–
– LOG_ARCHIVE_CONFIG=’SEND, RECEIVE, NODG_CONFIG’
– 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\product\10.2.0\db_1\RDBMS’
– 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 “ICMNLSDB” NORESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 3403
LOGFILE
GROUP 1 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_REDO1_1.LOG’ SIZE 50M,
GROUP 2 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_REDO1_2.LOG’ SIZE 50M
– STANDBY LOGFILE
DATAFILE
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_SYSTEM_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_UNDOTBS1_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_SYSAUX_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMLFQ32_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMLNF32_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMLSNDX_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMLSTXT_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMSFQ04_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMVFQ04_01.DBF’
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 ‘C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC00001_0652663191.001′;
– 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\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_TEMP_01.DBF’
SIZE 104857600 REUSE AUTOEXTEND ON NEXT 10485760 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 “ICMNLSDB” RESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 3403
LOGFILE
GROUP 1 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_REDO1_1.LOG’ SIZE 50M,
GROUP 2 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_REDO1_2.LOG’ SIZE 50M
– STANDBY LOGFILE
DATAFILE
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_SYSTEM_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_UNDOTBS1_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_SYSAUX_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMLFQ32_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMLNF32_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMLSNDX_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMLSTXT_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMSFQ04_01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_ICMVFQ04_01.DBF’
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 ‘C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC00001_0652663191.001′;
– 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\PRODUCT\10.2.0\ORADATA\ICMNLSDB\ICMNLSDB_TEMP_01.DBF’
SIZE 104857600 REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M;
– End of tempfile additions.
–