看看当前数据库的控制文件位置。一个在/u01/oradata/practice/下，另一个在快速恢复区，彼此互为镜像。

SYS@practice >show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/oradata/practice/control0
                                                 1.ctl, /u01/fast_recovery_area
                                                 /practice/control02.ctl

控制文件的备份方式有以下几种

1，RMAN自动备份
在rman中将controlfile autobackup设置为on即可。用RMAN执行备份命令就会触发控制文件自动备份，或者数据库物理结构发生变化之后，比如添加数据文件，添加日志文件组等。值得注意的是即使没有开启自动备份，用RMAN执行系统表空间SYSTEM备份的时候也会自动备份控制文件。

开启控制文件自动备份功能
RMAN> configure controlfile autobackup on;

users表空间比较小，备份一下users表空间看看触发自动备份控制文件的效果。

RMAN> report schema;

Report of database schema for database with db_unique_name PRACTICE

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/oradata/practice/system01.dbf
2    540      SYSAUX               ***     /u01/oradata/practice/sysaux01.dbf
3    100      UNDOTBS1             ***     /u01/oradata/practice/undotbs01.dbf
4    5        USERS                ***     /u01/oradata/practice/users01.dbf
5    313      EXAMPLE              ***     /u01/oradata/practice/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       /u01/oradata/practice/temp01.dbf

执行结果如下，蓝色字体部分为控制文件同参数文件被自动备份到快速恢复区的autobackup/2014_10_06目录下
RMAN> backup tablespace users;

Starting backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/oradata/practice/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-OCT-14
channel ORA_DISK_1: finished piece 1 at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_nnndf_TAG20141006T125042_b35l32qt_.bkp tag=TAG20141006T125042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-14

Starting Control File and SPFILE Autobackup at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-OCT-14

使用list backup of controlfile命令查看备份过的控制文件。显示的备份结果就是刚才自动备份的控制文件。
RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:00     06-OCT-14
        BP Key: 2   Status: AVAILABLE Compressed: NO Tag: TAG20141006T125043
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp
Control File Included: Ckp SCN: 1001360      Ckp time: 06-OCT-14

2，RMAN手动备份控制文件
在RMAN中可以使用backup current control file命令来备份控制文件。输入结果中蓝色字体部分为控制文件备份地址。在手动备份之后控制文件的自动备份被触发，从而又一次备份了控制文件和参数文件。
RMAN> backup current controlfile;

Starting backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 06-OCT-14
channel ORA_DISK_1: finished piece 1 at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp tag=TAG20141006T132827 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-14
（下面是控制文件和参数文件的自动备份，又RMAN的配置参数来控制）
Starting Control File and SPFILE Autobackup at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-OCT-14

3，SQLPLUS手动备份控制文件

3-1备份控制文件到指定目录
SYS@practice >alter database backup controlfile to '/home/oracle/control.ctl’;

3-2备份控制文件到trace文件
SYS@practice >alter database backup controlfile to trace;

注意Defualt Trace File首字母要大些
SYS@practice >select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/diag/rdbms/practice/practice/trace/practice_ora_12710.trc

3-3查看trace文件，关注norestlogs部分，为了便于阅读，删除注释部分内容。
cat /u01/diag/rdbms/practice/practice/trace/practice_ora_12710.trc

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRACTICE" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/practice/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/practice/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/practice/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/oradata/practice/system01.dbf',
'/u01/oradata/practice/sysaux01.dbf',
'/u01/oradata/practice/undotbs01.dbf',
'/u01/oradata/practice/users01.dbf',
'/u01/oradata/practice/example01.dbf'
CHARACTER SET AL32UTF8
;
（蓝色字体部分同步了在RMAN中手动开启controlfile autobackup on选项）
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/practice/temp01.dbf'
     SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

查看全部控制文件备份信息
RMAN> list backup of controlfile;

List of Backup Sets
===================
（备份Users表空间是自动备份的控制文件）
BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:00     06-OCT-14
        BP Key: 2   Status: AVAILABLE Compressed: NO Tag: TAG20141006T125043
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp
Control File Included: Ckp SCN: 1001360      Ckp time: 06-OCT-14
（RMAN手动备份的控制文件）
BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.33M      DISK        00:00:01     06-OCT-14
        BP Key: 3   Status: AVAILABLE Compressed: NO Tag: TAG20141006T132827
        Piece Name: /u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp
Control File Included: Ckp SCN: 1005439      Ckp time: 06-OCT-14
（手动备份后触发的自动备份控制文件）
BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    9.36M      DISK        00:00:00     06-OCT-14
        BP Key: 4   Status: AVAILABLE Compressed: NO Tag: TAG20141006T132829
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp
Control File Included: Ckp SCN: 1005449      Ckp time: 06-OCT-14

控制文件的恢复过程
1，从备份中还原控制文件-restore
2，用重做日志介质恢复数据库-recover
3，重设日志的方式打开数据库-resetlogs

情景一，控制文件采用多路镜像。至少有一个控制文件是可用的，其它的出现了损坏导致实例崩溃。我们无法定位具体是那个文件损坏了。这时候可以采用简单的排除法来解决。
1，启动数据库到nomount状态下

2，create pfile='/home/oracle/pfile' from spfile;

3，vi打开/home/oracle/pfile，修改*.control_files='/u01/oradata/practice/control01.ctl','/u01/fast_recovery_area/practice/control02.ctl’部分，只保留一个控制文件地址，其它的暂时去掉。

4，使用修改过的pfile启动实例并尝试mount数据库
startup nomount pfile='/home/oracle/pfile';

alter database mount;

5,如果mount成功证明目前参数文件中的控制文件是好的。可以再关闭实例后将该控制文件cp到其它控制文件的路径并修改相应的名称。如果mount不成功，再重复刚才的步骤来修改参数文件，尝试保留下一个控制文件。知道能mount成功。

情景二，采用自动备份的控制文件来恢复

RMAN> shutdown immediate;

RMAN> startup nomount;

RMAN> restore controlfile from autobackup;
（RMAN选择了最新的一次自动备份控制文件来恢复）
Starting restore at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /u01/fast_recovery_area
database name (or database unique name) used for search: PRACTICE
channel ORA_DISK_1: AUTOBACKUP /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20141006
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/practice/control01.ctl
output file name=/u01/fast_recovery_area/practice/control02.ctl
Finished restore at 06-OCT-14

RMAN> mount database;

RMAN> recover database;

Starting recover at 06-OCT-14
Starting implicit crosscheck backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 06-OCT-14

Starting implicit crosscheck copy at 06-OCT-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-OCT-14

searching for all files in the recovery area
cataloging files...
cataloging done
（自动读取所需的归档日志来恢复数据库）
List of Cataloged Files
=======================
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_5_b35g8x44_.arc
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/oradata/practice/redo03.log
archived log file name=/u01/oradata/practice/redo03.log thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-OCT-14

RMAN> alter database open resetlogs;

情景三，迁移数据库时控制文件是采用backup database include current control file命令备份的。在恢复控制文件时需要找到控制文件所在的备份片，并采用from关键字来指出位置。

1，执行全库备份，控制文件采用include 方式备份。
RMAN> run{
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> backup database format '/home/oracle/full_%d_%s_%T_%p.bak'
5> include current controlfile;
6> sql 'alter system archive log current';
7> backup archivelog all format '/home/oracle/arch_%d_%s_%T_%p.bak';
8> release channel c1;
9> release channel c2;
10> }

2，控制文件备份在/home/oracle/full_PRACTICE_9_20141006_1.bak备份片中
RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:00     06-OCT-14
        BP Key: 2   Status: AVAILABLE Compressed: NO Tag: TAG20141006T125043
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp
Control File Included: Ckp SCN: 1001360      Ckp time: 06-OCT-14

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.33M      DISK        00:00:01     06-OCT-14
        BP Key: 3   Status: AVAILABLE Compressed: NO Tag: TAG20141006T132827
        Piece Name: /u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp
Control File Included: Ckp SCN: 1005439      Ckp time: 06-OCT-14

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    9.36M      DISK        00:00:01     06-OCT-14
        BP Key: 4   Status: AVAILABLE Compressed: NO Tag: TAG20141006T142002
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860250002_b35qblyy_.bkp
Control File Included: Ckp SCN: 1011114      Ckp time: 06-OCT-14

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    9.33M      DISK        00:00:01     06-OCT-14
        BP Key: 7   Status: AVAILABLE Compressed: NO Tag: TAG20141006T143909
        Piece Name: /home/oracle/full_PRACTICE_9_20141006_1.bak
Control File Included: Ckp SCN: 1013438      Ckp time: 06-OCT-14

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    9.36M      DISK        00:00:00     06-OCT-14
        BP Key: 8   Status: AVAILABLE Compressed: NO Tag: TAG20141006T143918
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251158_b35rgpms_.bkp
Control File Included: Ckp SCN: 1013447      Ckp time: 06-OCT-14

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12      Full    9.36M      DISK        00:00:00     06-OCT-14
        BP Key: 12   Status: AVAILABLE Compressed: NO Tag: TAG20141006T143922
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251162_b35rgt84_.bkp
Control File Included: Ckp SCN: 1013480      Ckp time: 06-OCT-14

3，指定目录恢复控制文件
RMAN> run{
2> startup force nomount;
3> restore controlfile from '/home/oracle/full_PRACTICE_9_20141006_1.bak';
4> mount database;
5> recover database;
6> alter database open resetlogs;
7> }

Oracle instance started

Total System Global Area     580395008 bytes

Fixed Size                     2255392 bytes
Variable Size                411043296 bytes
Database Buffers             163577856 bytes
Redo Buffers                   3518464 bytes

Starting restore at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/practice/control01.ctl
output file name=/u01/fast_recovery_area/practice/control02.ctl
Finished restore at 06-OCT-14

database mounted
released channel: ORA_DISK_1

Starting recover at 06-OCT-14
Starting implicit crosscheck backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 06-OCT-14

Starting implicit crosscheck copy at 06-OCT-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-OCT-14

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35rgr0g_.arc
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251162_b35rgt84_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251158_b35rgpms_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35rgr0g_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/oradata/practice/redo03.log
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35rgr0g_.arc thread=1 sequence=1
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc thread=1 sequence=2
archived log file name=/u01/oradata/practice/redo03.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-OCT-14

database opened

控制文件的恢复到此结束。

ITPub博客

备份与恢复系列十一控制文件的备份与恢复

最新文章

ITPub博客

备份与恢复系列 十一 控制文件的备份与恢复

最新文章

备份与恢复系列十一控制文件的备份与恢复