数据库启动报错:ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
原因为控制文件损坏,之前清理文件的时候误删了,无奈网上找了个文章硬着头皮上,重建控制文件
1、查询数据库版本SQL> select * from v$version;
BANNER
--------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
2、查询控制文件位置SQL> show parameter control_files
NAME TYPE VALUE
--------------------
control_files string /oradata/posp/control01.ctl, /oracle/app/oracle/fast_recovery_area/posp/control02.ctl
3、删除控制文件rm /oradata/posp/control01.ctl
rm /oracle/app/oracle/fast_recovery_area/posp/control02.ctl
4、启动SQL> shutdown abort;
SQL> startup nomount;
5、查询语言SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------
AMERICAN_AMERICA.US7ASCII
6、在服务器上查询表空间文件ls -lh
total 98G
-rw-r----- 1 oracle oinstall 4.1G Feb 29 05:44 nlcmp_data01.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 06:05 posp_data01.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data02.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data03.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data04.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data05.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data06.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data07.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data08.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data09.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data10.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data11.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data12.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data13.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data14.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_data15.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 08:13 posp_data16.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_idx01.dbf
-rw-r----- 1 oracle oinstall 5.1G Feb 29 05:44 posp_idx02.dbf
-rw-r----- 1 oracle oinstall 51M Feb 29 08:11 redo01.log
-rw-r----- 1 oracle oinstall 51M Feb 29 01:54 redo02.log
-rw-r----- 1 oracle oinstall 51M Feb 29 05:39 redo03.log
-rw-r----- 1 oracle oinstall 981M Feb 29 08:16 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1.7G Feb 29 08:16 system01.dbf
-rw-r----- 1 oracle oinstall 30M Feb 29 06:00 temp01.dbf
-rw-r----- 1 oracle oinstall 211M Feb 29 08:16 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5.1M Feb 29 05:44 users01.dbf
7、生成脚本vi /tmp/CreateControlFile.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "posp" NORESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/oradata/posp/redo01.log' SIZE 50M,
GROUP 2 '/oradata/posp/redo02.log' SIZE 50M,
GROUP 3 '/oradata/posp/redo03.log' SIZE 50M
DATAFILE
'/oradata/posp/system01.dbf',
'/oradata/posp/sysaux01.dbf',
'/oradata/posp/undotbs01.dbf',
'/oradata/posp/users01.dbf',
'/oradata/posp/posp_data01.dbf',
'/oradata/posp/posp_data02.dbf',
'/oradata/posp/posp_data03.dbf',
'/oradata/posp/posp_data04.dbf',
'/oradata/posp/posp_data05.dbf',
'/oradata/posp/posp_data06.dbf',
'/oradata/posp/posp_data07.dbf',
'/oradata/posp/posp_data08.dbf',
'/oradata/posp/posp_data09.dbf',
'/oradata/posp/posp_data10.dbf',
'/oradata/posp/posp_data11.dbf',
'/oradata/posp/posp_data12.dbf',
'/oradata/posp/posp_data13.dbf',
'/oradata/posp/posp_data14.dbf',
'/oradata/posp/posp_data15.dbf',
'/oradata/posp/posp_data16.dbf',
'/oradata/posp/posp_idx01.dbf',
'/oradata/posp/posp_idx02.dbf'
CHARACTER SET US7ASCII;
执行脚本SQL> @/tmp/CreateControlFile.sql
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 285212672 bytes
Redo Buffers 6549504 bytes
Control file created.
8、查询数据库状态SQL> select status from v$instance;
STATUS
--------------------
MOUNTED
9、打开数据库SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oradata/posp/system01.dbf'
10、恢复数据库SQL> recover database;
Media recovery complete.
11、再次打开数据库SQL> alter database open;
Database altered.