堕落不振功业废,勤耕不辍日月新

Oracle重建控制文件

C/C++ hailen 10℃

数据库启动报错:
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.

参考资料:
http://blog.itpub.net/30162081/viewspace-1677995/

转载请注明:我是IT » Oracle重建控制文件

喜欢 (0)or分享 (0)