oracle12c重建控制文件

一:环境:

windows server2008

oracle 12c Enterprise Edition Release 12.1.0.2.0

二:问题

vmware station虚拟机,挂起后再开机,启动oracle无法mount和open

具体报错信息如下:

ORA-01079 Oracle database was not properly created, operation aborted

查询oracle官方手册如下:

在如下目录能看到两个控制文件

D:\PROJECTS\APP\ADMINISTRATOR\
ORADATA\ORCL\CONTROL01.CTL

D:\PROJECTS\APP\ADMINISTRATOR\FA
ST_RECOVERY_AREA\ORCL\CONTROL0
2.CTL

三:方法

采取重建控制文件

1.启动实例到nomount

sql>startup nomount

SQL> show parameter control_files

**将存在到control_file移到其他目录

2.生成文本格式的参数文件；

SQL> create pfile from spfile;

3.查看文件,获取数据实例名,默认生成的路径:

D:\projects\app\Administrator\product\12.1.0\dbhome_1\database\INITorclsid.ORA

*.db_domain=''
*.db_name='orcl'

4.获取数据的字符集

SQL> select userenv('language') from dual;

US7ASCII

 5.编写创建控制文件脚本createcontrol.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS  ARCHIVELOG 
 MAXLOGFILES 5
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 1
 MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:\projects\app\Administrator\oradata\orcl\REDO01.LOG' SIZE 50M,
GROUP 2 'D:\projects\app\Administrator\oradata\orcl\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\projects\app\Administrator\oradata\orcl\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
 'D:\projects\app\Administrator\oradata\orcl\CONDIS.DBF',
 'D:\projects\app\Administrator\oradata\orcl\GD110SJJZ.DBF',
 'D:\projects\app\Administrator\oradata\orcl\GDSJJZDATA.DBF',
 'D:\projects\app\Administrator\oradata\orcl\GDSJJZDATA_INDEX.DBF',
CHARACTER SET US7ASCII
;

 6.执行重建控制文件

需要确保数据是在关闭状态执行

SQL>@D:\projects\app\Administrator\oradata\orcl\createcontrol.sql

 在这里,遇到以下的报错提示

1. ORA-01503: CREATE CONTROLFILE failed
2. ORA-01192: must have at least one enabled thread

修改文件

 STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "orcl" RESETLOGS FORCE LOGGING ARCHIVELOG 
 MAXLOGFILES 5
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 1
 MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:\projects\app\Administrator\oradata\orcl\REDO01.LOG' SIZE 50M,
GROUP 2 'D:\projects\app\Administrator\oradata\orcl\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\projects\app\Administrator\oradata\orcl\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
 'D:\projects\app\Administrator\oradata\orcl\CONDIS.DBF',
 'D:\projects\app\Administrator\oradata\orcl\GD110SJJZ.DBF',
 'D:\projects\app\Administrator\oradata\orcl\GDSJJZDATA.DBF',
 'D:\projects\app\Administrator\oradata\orcl\GDSJJZDATA_INDEX.DBF',
CHARACTER SET US7ASCII
;

 再次执行文件

SQL> @D:\projects\app\Administrator\oradata\orcl\createcontrol.sql
ORACLE instance started.

Total System Global Area 1.0301E+10 bytes
Fixed Size                  3842760 bytes
Variable Size            1778388280 bytes
Database Buffers         8489271296 bytes
Redo Buffers               29708288 bytes

Control file created.

 7.恢复数据

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-01610: 使用BACKUP CONTROLFILE选项的恢复必须已完成 

 此处需要注意,NORESETLOGS和RESETLOGS选项的区别

使用SQL> recover database using backup controlfile until cancel;语句进行恢复

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 8489570397 generated at 03/31/2024 15:39:41 needed for thread
1
ORA-00289: suggestion :
D:\PROJECTS\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2024_09_26\O1_M

F_1_2821991_%U_.ARC
ORA-00280: change 8489570397 for thread 1 is in sequence #2821991

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

##在这里有几个选项
##suggested:在上面ORA-00289: 建议:....会按这个文件去恢复
##filename:自己指定日志文件
##auto:自动选择
##cancel:这个可以不从介质恢复。
auto #此处是手动输入
ORA-00308: ????????
'D:\PROJECTS\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2024_09_26\O1_

MF_1_2821991_%U_.ARC'
ORA-27041: ??????
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????

ORA-00308: ????????
'D:\PROJECTS\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2024_09_26\O1_

MF_1_2821991_%U_.ARC'
ORA-27041: ??????
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????

ORA-01547: ??: RECOVER ??? OPEN RESETLOGS ???????
ORA-01194: ?? 1 ?????????????
ORA-01110: ???? 1: 'D:\PROJECTS\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'

 到这步,我发现我的在线日志的目录都是空的..........................................

强制切换到open状态,报错如下:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'D:\PROJECTS\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'

 分析:根据错误码,查阅资料,应该是控制文件记录的SCN不一致，致使数据库无法OPEN

解决:修改对应的参数

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

 再次启动

SQL> startup
ORACLE instance started.

Total System Global Area 1.0301E+10 bytes
Fixed Size                  3842760 bytes
Variable Size            1778388280 bytes
Database Buffers         8489271296 bytes
Redo Buffers               29708288 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orclsid          OPEN

至此,重建控制文件完成