oracle丢失损坏控制文件ORA-00227 ORA-00205没有备份如何恢复

1、oracle启动

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

[oracle@oracle1 orcl]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 11 14:18:33 2023   Copyright (c) 1982, 2013, Oracle.  All rights reserved.   Connected to an idle instance.   SQL> startup ORACLE instance started.   Total System Global Area 4977278976 bytes Fixed Size                  2261768 bytes Variable Size            1006636280 bytes Database Buffers         3959422976 bytes Redo Buffers                8957952 bytes ORA-00205: error in identifying control file, check alert log for more info   或者   ORA-00227:控制文件中检测到损坏的块：（块40，#块 1）

2、另外一台oracle生成模板

?

1	alter database backup controlfile to trace as '/tmp/cfop.ctl';

3、修改配置、修改库名称、修改日志文件（大小必须和以前一样）、数据文件路径

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

[oracle@oracle1 orcl]$ cat /tmp/cfop.ctl STARTUP NOMOUNT FORCE; CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 3     MAXDATAFILES 100     MAXINSTANCES 8     MAXLOGHISTORY 1168 LOGFILE   GROUP 1 '/u01/oracle/oradata/orcl/redo01.log'  SIZE 512M BLOCKSIZE 512,   GROUP 2 '/u01/oracle/oradata/orcl/redo02.log'  SIZE 512M BLOCKSIZE 512,   GROUP 3 '/u01/oracle/oradata/orcl/redo03.log'  SIZE 512M BLOCKSIZE 512 DATAFILE  '/u01/oracle/oradata/orcl/users01.dbf',  '/u01/oracle/oradata/orcl/undotbs01.dbf',  '/u01/oracle/oradata/orcl/sysaux01.dbf',  '/u01/oracle/oradata/orcl/system01.dbf',  '/u01/oracle/oradata/orcl/test01.dbf',  '/u01/oracle/oradata/orcl/test02.dbf',  '/u01/oracle/oradata/orcl/test03.dbf',  '/u01/oracle/oradata/orcl/test04.dbf',  '/u01/oracle/oradata/orcl/test05.dbf',  '/u01/oracle/oradata/orcl/part_Data1.dbf',  '/u01/oracle/oradata/orcl/part_Data2.dbf',  '/u01/oracle/oradata/orcl/part_Data3.dbf' CHARACTER SET AL32UTF8 ;   RECOVER DATABASE   ALTER DATABASE OPEN;

4、还原启动数据库：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

[oracle@oracle1 orcl]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 11 14:24:52 2023   Copyright (c) 1982, 2013, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> @/tmp/cfop.ctl ORACLE instance started.   Total System Global Area 4977278976 bytes Fixed Size                  2261768 bytes Variable Size            1006636280 bytes Database Buffers         3959422976 bytes Redo Buffers                8957952 bytes   Control file created.   Database altered.   SQL> select status from v$instance;   STATUS ------------ OPEN