通过Trace文件了解Oracle控制文件

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:oracleadminorcludump
orcl@ORCL>alter database backup controlfile to trace;Database altered.我们在D:oracleadminorcludump这个目录下找到最新生成的那个文件就是刚才对控制文件的备份。
这个文件中的内容：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:oracleRDBMS"
# 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:ORACLEORADATAORCLREDO01.LOG" SIZE 100M,
GROUP 2 "D:ORACLEORADATAORCLREDO02.LOG" SIZE 100M,
GROUP 3 "D:ORACLEORADATAORCLREDO03.LOG" SIZE 100M
-- STANDBY LOGFILE
DATAFILE
"D:ORACLEORADATAORCLSYSTEM01.DBF",
"D:ORACLEORADATAORCLUNDOTBS01.DBF",
"D:ORACLEORADATAORCLCWMLITE01.DBF",
"D:ORACLEORADATAORCLDRSYS01.DBF",
"D:ORACLEORADATAORCLEXAMPLE01.DBF",
"D:ORACLEORADATAORCLINDX01.DBF",
"D:ORACLEORADATAORCLODM01.DBF",
"D:ORACLEORADATAORCLTOOLS01.DBF",
"D:ORACLEORADATAORCLUSERS01.DBF",
"D:ORACLEORADATAORCLXDB01.DBF",
"D:ORACLEORADATAORCLSCOTT_TABLAESPACE.ORA",
"D:ORACLEORADATAORCLTEST.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:ORACLEORADATAORCLTEMP01.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:ORACLEORADATAORCLREDO01.LOG" SIZE 100M,
GROUP 2 "D:ORACLEORADATAORCLREDO02.LOG" SIZE 100M,
GROUP 3 "D:ORACLEORADATAORCLREDO03.LOG" SIZE 100M
-- STANDBY LOGFILE
DATAFILE
"D:ORACLEORADATAORCLSYSTEM01.DBF",
"D:ORACLEORADATAORCLUNDOTBS01.DBF",
"D:ORACLEORADATAORCLCWMLITE01.DBF",
"D:ORACLEORADATAORCLDRSYS01.DBF",
"D:ORACLEORADATAORCLEXAMPLE01.DBF",
"D:ORACLEORADATAORCLINDX01.DBF",
"D:ORACLEORADATAORCLODM01.DBF",
"D:ORACLEORADATAORCLTOOLS01.DBF",
"D:ORACLEORADATAORCLUSERS01.DBF",
"D:ORACLEORADATAORCLXDB01.DBF",
"D:ORACLEORADATAORCLSCOTT_TABLAESPACE.ORA",
"D:ORACLEORADATAORCLTEST.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:ORACLEORADATAORCLTEMP01.DBF"
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
# End of tempfile additions.Oracle 的TRACE取得方法Oracle 过滤特殊字符相关资讯 oracle

[INS-32052] Oracle基目录和Oracle （07/22/2014 07:41:41）
Oracle 4个大对象（lobs）数据类型（02/03/2013 12:33:05）
Oracle按时间段分组统计（07/26/2012 10:36:48）

[Oracle] dbms_metadata.get_ddl的（07/12/2013 07:37:30）
Liferay Portal 配置使用Oracle和（07/31/2012 20:07:18）
Concurrent Request:Inactive （07/20/2012 07:44:05）

本文评论查看全部评论（0）