1.部分丢失控制文件
解决方法:
关闭数据库
复制存在的给予丢失的
启动数据库
2.丢失所有控制文件
解决方法:
利用备份恢复
解决方法:
关闭数据库
复制存在的给予丢失的
启动数据库
2.丢失所有控制文件
解决方法:
利用备份恢复
利用脚本重建控制文件
--实验
--1.部分丢失
SYS@PROD1> select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/control01.ctl
/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
SYS@PROD1> ho rm /u01/app/oracle/oradata/PROD1/control01.ctl --删除一个控制文件
SYS@PROD1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 7 20:28:46 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD1/control01.ctl' --显示控制文件丢失
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
[oracle@ocm1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 7 20:28:53 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@PROD1> shutdown abort --强制关闭数据库
ORACLE instance shut down.
SYS@PROD1> startup
ORACLE instance started.
Total System Global Area 732352512 bytes
Fixed Size 1347456 bytes
Variable Size 318767232 bytes
Database Buffers 411041792 bytes
Redo Buffers 1196032 bytes
ORA-00205: error in identifying control file, check alert log for more info
SYS@PROD1> show parameter control_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/PROD1/
control01.ctl, /u01/app/oracle
/fast_recovery_area/PROD1/cont
rol02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SYS@PROD1> ho ls /u01/app/oracle/oradata/PROD1/control01.ctl --确认哪个控制文件丢失
ls: /u01/app/oracle/oradata/PROD1/control01.ctl: No such file or directory
SYS@PROD1> ho ls /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
SYS@PROD1> ho cp /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl /u01/app/oracle/oradata/PROD1/control01.ctl
--copy控制文件
SYS@PROD1> alter database mount;
Database altered.
SYS@PROD1> alter database open;
Database altered.
--2.完全丢失
RMAN> backup current controlfile; --先备份一下当前的控制文件
Starting backup at 07-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting compressed 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 07-DEC-16
channel ORA_DISK_1: finished piece 1 at 07-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_07/o1_mf_ncnnf_TAG20161207T203249_d4j0cm0v_.bkp tag=TAG20161207T203249 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 07-DEC-16
Starting Control File and SPFILE Autobackup at 07-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_07/o1_mf_s_929997172_d4j0cnyb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-DEC-16
[oracle@ocm1 ~]$ rm /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl --删除所有控制文件
[oracle@ocm1 ~]$ rm /u01/app/oracle/oradata/PROD1/control01.ctl
[oracle@ocm1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 7 20:33:53 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD1/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
[oracle@ocm1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 7 20:33:58 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@PROD1> shutdown abort
ORACLE instance shut down.
SYS@PROD1> startup
ORACLE instance started.
Total System Global Area 732352512 bytes
Fixed Size 1347456 bytes
Variable Size 318767232 bytes
Database Buffers 411041792 bytes
Redo Buffers 1196032 bytes
ORA-00205: error in identifying control file, check alert log for more info
SYS@PROD1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm1 ~]$ ls /u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_07/o1_mf_ncnnf_TAG20161207T203249_d4j0cm0v_.bkp --查看备份的控制文件
/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_07/o1_mf_ncnnf_TAG20161207T203249_d4j0cm0v_.bkp
[oracle@ocm1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 7 20:35:02 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_07/o1_mf_ncnnf_TAG20161207T203249_d4j0cm0v_.bkp'; --恢复控制文件
Starting restore at 07-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/PROD1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
Finished restore at 07-DEC-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 07-DEC-16
Starting implicit crosscheck backup at 07-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 28 objects
Finished implicit crosscheck backup at 07-DEC-16
Starting implicit crosscheck copy at 07-DEC-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 07-DEC-16
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/PROD1/archivelog/1_98_916724026.dbf
File Name: /u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_07/o1_mf_ncnnf_TAG20161207T203249_d4j0cm0v_.bkp
File Name: /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_07/o1_mf_s_929997172_d4j0cnyb_.bkp
File Name: /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_07_09/o1_mf_s_916722818_cr05w3h5_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 380 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD1/redo02.log
archived log file name=/u01/app/oracle/fast_recovery_area/PROD1/redo02.log thread=1 sequence=380
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-DEC-16
RMAN> alter database open resetlogs; --以前的备份全部无效需要重新备份
database opened