oracle 控制文件大小51m,Oracle重建控制文件

数据库启动报错：

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.