oracle中的alter database backup controlfile to trace;命令用来将控制文件备份为.trc后缀的文本文件。这个文件名的格式为：sid
oracle中的alter database backup controlfile to trace;命令用来将控制文件备份为.trc后缀的文本文件。这个文件名的格式为：sid_ora_pid.trc,存储在参数user_dump_dest指定的位置。这个文件里储存的是创建新控制文件的sql，我们可以通过它来大概了解一下控制文件里都有那些内容。 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.
我们在d:\oracle\admin\orcl\udump这个目录下找到最新生成的那个文件就是刚才对控制文件的备份。
这个文件中的内容：
1.注释
*** session id:(9.5) 2008-03-18 20:40:22.000
*** 2008-03-18 20:40:22.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=d:\oracle\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.
#
2.noresetlogs case sql（包含数据库的日志文件和数据文件的物理位置和大小等信息）
***在联机日志可用的情况下使用下边这些命令重新构建控制文件
# 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 100m,
group 2 'd:\oracle\oradata\orcl\redo02.log' size 100m,
group 3 'd:\oracle\oradata\orcl\redo03.log' size 100m
-- 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信息*******
# 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
# 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 32767m;
# end of tempfile additions.
3。resetlogs case sql
在联机日志不可用的情况下使用这些sql构造控制文件。所有的联机日志将丢失，，所有的备份都失效。包含的信息同上边。
# 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 100m,
group 2 'd:\oracle\oradata\orcl\redo02.log' size 100m,
group 3 'd:\oracle\oradata\orcl\redo03.log' size 100m
-- 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 32767m;
# end of tempfile additions.