利用trace文件進行數據庫重建

1、將原數據庫pifle生成spfile

[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>
</pre>備注：將給文件拷貝到目標數據庫的時候需要針對目標數據的情況修改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
</b>
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_ARCHIVEFORMAT=%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.
--</b>

--     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_11%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_05/o
1_mf_11%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_11%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_05/o
1_mf_11%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.
</b></pre>
通過這個trace文件,我們就可以獲得創建控制文件的腳本，根據數據庫不同狀況，你可以選擇是使用RESETLOGS/NORESETLOGS來重建控制文件.
注意：運行腳本的時候需要在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

</div>


                    

                        
 本文由用戶 baojian322 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
                        
 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
                        
 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！
                        
 本文地址：http://www.baiduhome.net/blog/5035946818190387622.html
                        
 bootstrap模板 CSS3 Drop Shadows