备份控制文件到trace文件中

1、查看控制文件的位置：

SQL> show parameter control_file

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time         integer     7
control_files                 string     +DATA/or11g/controlfile/curren
                         t.261.986827313, +FRA/or11g/co
                         ntrolfile/current.263.98682731
                         3
SQL>

2、确定将要产生的trace文件：

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
   /

3、生成trace文件

SQL> alter database backup controlfile to trace;

数据库已更改。

SQL>

4、然后找到刚刚查出的trc文件，然后打开该文件，找到创建控制文件的语句：

分两种情况：

4.1、采用noresetlogs方式打开数据库：

STARTUP NOMOUNT  
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+DATA/orcl/onlinelog/group_1.261.928247759',
    '+FRA/orcl/onlinelog/group_1.259.928247759'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '+DATA/orcl/onlinelog/group_2.269.928247759',
    '+FRA/orcl/onlinelog/group_2.258.928247759'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '+DATA/orcl/onlinelog/group_3.268.928247759',
    '+FRA/orcl/onlinelog/group_3.257.928247759'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/orcl/datafile/system.266.928247691',
  '+DATA/orcl/datafile/sysaux.256.928247691',
  '+DATA/orcl/datafile/undotbs1.264.928247691',
  '+DATA/orcl/datafile/users.263.928247691',
  '+DATA/orcl/datafile/example.260.928247763'
CHARACTER SET ZHS16GBK
;

4.2、采用resetlogs方式打开数据库：

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+DATA/orcl/onlinelog/group_1.261.928247759',
    '+FRA/orcl/onlinelog/group_1.259.928247759'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '+DATA/orcl/onlinelog/group_2.269.928247759',
    '+FRA/orcl/onlinelog/group_2.258.928247759'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '+DATA/orcl/onlinelog/group_3.268.928247759',
    '+FRA/orcl/onlinelog/group_3.257.928247759'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/orcl/datafile/system.266.928247691',
  '+DATA/orcl/datafile/sysaux.256.928247691',
  '+DATA/orcl/datafile/undotbs1.264.928247691',
  '+DATA/orcl/datafile/users.263.928247691',
  '+DATA/orcl/datafile/example.260.928247763'
CHARACTER SET ZHS16GBK
;