"控制文件的恢复(一)"中提到利用控制文件的在线镜像备份和自动备份恢复控制文件,因为有自动备份的存在,只要数据库的数据文件结构有了变化,控制文件会自动备份记录下变化,当一个数据文件损坏或者所有数据损坏时可以用“控制文件的恢复(一)”的方法恢复。如果是手动备份控制文件,备份完后数据库数据文件发生了改变,但是改变后没有再次手动备份控制文件,所有的控制文件就全部损坏了,那么控制文件的恢复步骤有些不同,请看例子:
DB:Oracle 11g 11.2.0.3.0 X86_64,OS RHEL 6.3 X86_64,未使用Recovery Catalog
1.1取消控制文件的自动备份
RMAN> configure controlfile autobackup off;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
1.2创建tstest表空间
SYS@orcl>create tablespace tstest datafile '/u01/app/oracle/oradata/orcl/tstest01.dbf' size 10m;
Tablespace created.
SYS@orcl>select tablespace_name,file_id,file_name from dba_tablespaces join dba_data_files using(tablespace_name);
TABLESPACE_NAME FILE_ID FILE_NAME
--------------- ---------- --------------------------------------------------
USERS 4 /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS1 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM 1 /u01/app/oracle/oradata/orcl/system01.dbf
EXAMPLE 5 /u01/app/oracle/oradata/orcl/example01.dbf
TSTEST 6 /u01/app/oracle/oradata/orcl/tstest01.dbf
6 rows selected.
1.3手动备份当前控制文件
RMAN> backup current controlfile;
Starting backup at 02-JAN-13
using channel ORA_DISK_1
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 02-JAN-13
channel ORA_DISK_1: finished piece 1 at 02-JAN-13
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_02/o1_mf_ncnnf_TAG20130102T201806_8g89d1ln_.bkp tag=TAG20130102T201806 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-JAN-13
1.4删除建立的tstest表空间
SYS@orcl>drop tablespace tstest including contents and datafiles;
Tablespace dropped.
1.5破坏所有的控制文件
[oracle@ora 2013_01_02]$ dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/control01.ctl bs=512K count=10;
10+0 records in
10+0 records out
5242880 bytes (5.2 MB) copied, 0.00615938 s, 851 MB/s
[oracle@ora 2013_01_02]$ dd if=/dev/zero of=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl bs=512K count=10;
10+0 records in
10+0 records out
5242880 bytes (5.2 MB) copied, 0.00586527 s, 894 MB/s
[oracle@ora 2013_01_02]$ dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/control03.ctl bs=512K count=10;
10+0 records in
10+0 records out
5242880 bytes (5.2 MB) copied, 0.00577197 s, 908 MB/s
1.6以shutdown abort命令关闭实例
SYS@orcl>shutdown immediate;
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
SYS@orcl>shutdown abort;
ORACLE instance shut down.
1.7启动实例到nomount状态
SYS@orcl>startup;
ORACLE instance started.
Total System Global Area 1887350784 bytes
Fixed Size 2229464 bytes
Variable Size 1124076328 bytes
Database Buffers 754974720 bytes
Redo Buffers 6070272 bytes
ORA-00205: error in identifying control file, check alert log for more info
1.8用1.3手动备份的控制文件还原控制文件
RMAN> restore controlfile from autobackup;
Starting restore at 02-JAN-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/02/2013 20:23:57
RMAN-12010: automatic channel allocation initialization failed
ORA-01507: database not mounted
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_02/o1_mf_ncnnf_TAG20130102T201806_8g89d1ln_.bkp';
Starting restore at 02-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
output file name=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at 02-JAN-13
1.9启动实例到mount状态
RMAN> mount database;
database mounted
released channel: ORA_DISK_1
1.10执行recover database命令恢复数据库
RMAN> recover database;
Starting recover at 02-JAN-13
Starting implicit crosscheck backup at 02-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
Finished implicit crosscheck backup at 02-JAN-13
Starting implicit crosscheck copy at 02-JAN-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-JAN-13
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_02/o1_mf_ncnnf_TAG20130102T201806_8g89d1ln_.bkp
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/02/2013 20:25:17
RMAN-06094: datafile 6 must be restored ---因为6号数据文件是在控制文件备份后删除的,使用备份的控制文件恢复时还记录着6号控制文件的信息,实际上6号文件已经不在,tstest表空间已经删除,所以会报错
1.11还原6号数据文件
RMAN> restore datafile 6;
Starting restore at 02-JAN-13
using channel ORA_DISK_1
creating datafile file number=6 name=/u01/app/oracle/oradata/orcl/tstest01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 02-JAN-13
1.12再次执行recover database命令恢复数据库
RMAN> recover database;
Starting recover at 02-JAN-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/orcl/redo01.log
archived log file name=/u01/app/oracle/oradata/orcl/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:02
Finished recover at 02-JAN-13
1.13以resetloglogs命令打开实例
SYS@orcl>alter database open resetlogs;
Database altered.
SYS@orcl>select tablespace_name,file_id,file_name from dba_tablespaces join dba_data_files using(tablespace_name); ---查询看到tstest表空间不在,但是1.11中还原的数据文件还保留在磁盘上,可以根据需要删除
TABLESPACE_NAME FILE_ID FILE_NAME
--------------- ---------- --------------------------------------------------
USERS 4 /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS1 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM 1 /u01/app/oracle/oradata/orcl/system01.dbf
EXAMPLE 5 /u01/app/oracle/oradata/orcl/example01.dbf
5 rows selected.
还有中恢复方法适用于本例子,就是在1.10步执行recover database命令时使用'recover database skip tablespace tstest',使恢复时不恢复tstest表空间。
另外由于11g在设置控制文件自动备份且数据库数据文件结构发生改变后,并不是马上自动备份控制文件,如果此时所有的控制文件都损坏也可以按照上面的步骤进行控制文件的恢复。