利用trace文件进行数据库重建
1、将原数据库pifle生成spfile
2、将原数据库控制文件备份成trace文件
备注：上述文件中的路径，需要根据目标数据库的实际情况进行修改，生成实例对应的数据库。
附件：获取生成的trace文件的两种方式：
[oracle@linuxForOra udump]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 5 13:08:43 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn / as sysdba; Connected. SQL> create pfile from spfile; File created. SQL>备注：将给文件拷贝到目标数据库的时候需要针对目标数据的情况修改pifle中的参数值，然后在重新生成spile.
2、将原数据库控制文件备份成trace文件
SQL> conn / as sysdba; Connected. SQL> show user; USER is "SYS" SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SQL> alter database backup controlfile to trace; Database altered. SQL> SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' 2 trace_file FROM (SELECT VALUE 3 4 FROM v$parameter WHERE name = 'user_dump_dest') a, 5 6 (SELECT SUBSTR (VALUE, -6, 1) symbol FROM v$parameter 7 8 WHERE name = 'user_dump_dest') b, (SELECT instance_name FROM v$instance) c, 9 10 (SELECT spid FROM v$session s, v$process p, v$mystat m 11 12 WHERE s.paddr = p.addr AND s.sid = m.sid AND m.statistic# = 0) d; TRACE_FILE -------------------------------------------------------------------------------- /u01/oracle/admin/general10g/udump/general10g_ora_6055.trc SQL> !more /u01/oracle/admin/general10g/udump/general10g_ora_6055.trc /u01/oracle/admin/general10g/udump/general10g_ora_6055.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /u01/oracle/oracle/product/10.2.0/db_1 System name: Linux Node name: linuxForOra Release: 2.6.9-5.EL Version: #1 Wed Jan 5 19:22:18 EST 2005 Machine: i686 Instance name: general10g Redo thread mounted by this instance: 1 Oracle process number: 14 Unix process pid: 6055, image: oracle@linuxForOra (TNS V1-V3) *** SERVICE NAME:(SYS$USERS) 2011-09-05 11:10:17.001 *** SESSION ID:(150.33) 2011-09-05 11:10:17.001 *** 2011-09-05 11:10:17.001 -- 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="general10g" -- -- 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 "GENERAL1" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/oracle/oradata/general10g/redo01.log' SIZE 50M, GROUP 2 '/u01/oracle/oradata/general10g/redo02.log' SIZE 50M, GROUP 3 '/u01/oracle/oradata/general10g/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/u01/oracle/oradata/general10g/system01.dbf', '/u01/oracle/oradata/general10g/undotbs01.dbf', '/u01/oracle/oradata/general10g/sysaux01.dbf', '/u01/oracle/oradata/general10g/users01.dbf', '/u01/oracle/oradata/general10g/example01.dbf' CHARACTER SET ZHS16GBK ; -- Configure RMAN configuration record 1 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON'); -- 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 '/u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_05/o 1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_05/o 1_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 -- All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL; -- 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 '/u01/oracle/oradata/general10g/temp01.dbf' SIZE 22020096 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 "GENERAL1" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/oracle/oradata/general10g/redo01.log' SIZE 50M, GROUP 2 '/u01/oracle/oradata/general10g/redo02.log' SIZE 50M, GROUP 3 '/u01/oracle/oradata/general10g/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/u01/oracle/oradata/general10g/system01.dbf', '/u01/oracle/oradata/general10g/undotbs01.dbf', '/u01/oracle/oradata/general10g/sysaux01.dbf', '/u01/oracle/oradata/general10g/users01.dbf', '/u01/oracle/oradata/general10g/example01.dbf' CHARACTER SET ZHS16GBK ; -- Configure RMAN configuration record 1 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON'); -- 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 '/u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_05/o 1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_05/o 1_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 '/u01/oracle/oradata/general10g/temp01.dbf' SIZE 22020096 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. -- 通过这个trace文件,我们就可以获得创建控制文件的脚本，根据数据库不同状况，你可以选择是使用RESETLOGS/NORESETLOGS来重建控制文件.
注意：运行脚本的时候需要在nomout状态！
注意：运行脚本的时候需要在nomout状态！
[oracle@linuxForOra udump]$ cat createctl.sql STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "GENERAL1" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/oracle/oradata/general10g/redo01.log' SIZE 50M, GROUP 2 '/u01/oracle/oradata/general10g/redo02.log' SIZE 50M, GROUP 3 '/u01/oracle/oradata/general10g/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/u01/oracle/oradata/general10g/system01.dbf', '/u01/oracle/oradata/general10g/undotbs01.dbf', '/u01/oracle/oradata/general10g/sysaux01.dbf', '/u01/oracle/oradata/general10g/users01.dbf', '/u01/oracle/oradata/general10g/example01.dbf' CHARACTER SET ZHS16GBK ;创建成功以后继续运行：
RECOVER DATABASE; ALTER SYSTEM ARCHIVE LOG ALL; ALTER DATABASE OPEN; ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oracle/oradata/general10g/temp01.dbf' SIZE 22020096 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;即可操作完成！
备注：上述文件中的路径，需要根据目标数据库的实际情况进行修改，生成实例对应的数据库。
附件：获取生成的trace文件的两种方式：
第一种： select d.value||b.bias||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from ( select p.spid from sys.v$mystat m,sys.v$session s,sys.v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, ( select t.instance from sys.v$thread t,sys.v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, ( select value from sys.v$parameter where name = 'user_dump_dest') d,(select DECODE(count(BANNER),0,'/','\') bias from v$version where upper(banner) like '%WINDOWS%') b;
第二种： SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file FROM (SELECT VALUE FROM v$parameter WHERE name = 'user_dump_dest') a, (SELECT SUBSTR (VALUE, -6, 1) symbol FROM v$parameter WHERE name = 'user_dump_dest') b, (SELECT instance_name FROM v$instance) c, (SELECT spid FROM v$session s, v$process p, v$mystat m WHERE s.paddr = p.addr AND s.sid = m.sid AND m.statistic# = 0) d
baojian322 
wojingwu 
码头工人 