11.09.20 控制文件的跟踪副本实例

---

如下命令创建控制文件的跟踪副本：
SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

跟踪备份的创建位置由USER_DUMP_DEST 初始化参数指定，文件名为sid_ora_pid.trc。

---

SQL>SHOW PARAMETERS USER_DUMP_DEST

结果为：/home/u01/oracle/admin/orcl/udump

到该目录下

 [oracle@orcl udump]$ cat orcl_ora_16801.trc

具体内容如下：

/home/u01/oracle/admin/orcl/udump/orcl_ora_16801.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /home/u01/oracle/product/10.2.0/db_1 System name: Linux Node name: orcl.oracle.com Release: 2.6.18-238.el5 Version: #1 SMP Thu Jan 13 16:24:47 EST 2011 Machine: i686 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 19 Unix process pid: 16801, image: oracle@orcl.oracle.com (TNS V1-V3) *** SERVICE NAME:(SYS$USERS) 2011-09-19 11:02:52.887 *** SESSION ID:(147.36461) 2011-09-19 11:02:52.887 *** 2011-09-19 11:02:52.887 -- 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=2 -- STANDBY_FILE_MANAGEMENT=MANUAL -- STANDBY_ARCHIVE_DEST=?/dbs/arch -- FAL_CLIENT='' -- FAL_SERVER='' -- -- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' -- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY' -- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY' -- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' -- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' -- LOG_ARCHIVE_DEST_STATE_10=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 NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/home/u01/oracle/oradata/orcl/redo01.log' SIZE 50M, GROUP 2 '/home/u01/oracle/oradata/orcl/redo02.log' SIZE 50M, GROUP 3 '/home/u01/oracle/oradata/orcl/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/home/u01/oracle/oradata/orcl/system01.dbf', '/home/u01/oracle/oradata/orcl/undotbs01.dbf', '/home/u01/oracle/oradata/orcl/sysaux01.dbf', '/home/u01/oracle/oradata/orcl/users01.dbf', '/home/u01/oracle/oradata/orcl/test1.dbf' CHARACTER SET ZHS16GBK ; -- 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 '/home/u01/oracle/flash_recovery_area/ORCL/archivelog/2011_09_19/o1_mf_1_1_%u_.arc'; -- 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 '/home/u01/oracle/oradata/orcl/temp01.dbf' 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 NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/home/u01/oracle/oradata/orcl/redo01.log' SIZE 50M, GROUP 2 '/home/u01/oracle/oradata/orcl/redo02.log' SIZE 50M, GROUP 3 '/home/u01/oracle/oradata/orcl/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/home/u01/oracle/oradata/orcl/system01.dbf', '/home/u01/oracle/oradata/orcl/undotbs01.dbf', '/home/u01/oracle/oradata/orcl/sysaux01.dbf', '/home/u01/oracle/oradata/orcl/users01.dbf', '/home/u01/oracle/oradata/orcl/test1.dbf' CHARACTER SET ZHS16GBK ; -- 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 '/home/u01/oracle/flash_recovery_area/ORCL/archivelog/2011_09_19/o1_mf_1_1_%u_.arc'; -- 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 '/home/u01/oracle/oradata/orcl/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. --

   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26121819/viewspace-707907/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26121819/viewspace-707907/