sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-00283 ORA-00600 2663原因和MOS官方解决办法

时间:2016-11-02 20:30   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库无法启动并报错ORA-00283 ORA-00600 2663,结合MOS官方文档分析原因为SCN异常导致
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

SQL> alter database open resetlogs upgrade;
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2663], [0], [1257159], [0],[1257178], [], [], [], [], [], [], []
进程 ID: 5312
会话 ID: 191 序列号: 1

1 环境介绍

d:\wendang\SkyDrive\rs2\sql>uname -a
windows32 luoping 2.6.1 7601 i686-pc Intel unknown MinGW
朋友的版本为11.2.0.1,我的环境的版本是11.2.0.4
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 64-bit Windows: Version 11.2.0.4.0 – Production
NLSRTL Version 11.2.0.4.0 – Production

2,修改参数文件

db_name=’XZ’
memory_target=1G
processes = 150
audit_file_dest=’d:\app\luoping\admin\xz\adump’
audit_trail =’none’
db_block_size=8192
db_domain=”
db_recovery_file_dest=’d:\app\luoping\fast_recovery_area’
db_recovery_file_dest_size=2G
diagnostic_dest=’d:\app\luoping\’
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)’
open_cursors=300
remote_login_passwordfile=’EXCLUSIVE’
undo_tablespace=’UNDOTBS1′
control_files = ‘d:\app\luoping\oradata\xz\CONTROL01.CTL’
compatible =’11.2.0′

3,创建服务

d:\wendang\SkyDrive\rs2\sql>oradim -NEW -SID xz -STARTMODE MANUAL -PFILE pfile=’d:\pfile.ora’
Instance created.

4 开始数据库恢复

4.1 修改数据文件与日志文件路径
由于我的环境与朋友环境路径不一样,所以这里需要手动修改文件路径名
oracleplus.net> alter database rename file ‘D:\ORACLE\PRODUCT\ORADATA\XZ\SYSTEM01.DBF’ to ‘d:\app\luoping\oradata\xz\SYSTEM01.DBF’ ;
Database altered.

oracleplus.net> alter database rename file ‘D:\ORACLE\PRODUCT\ORADATA\XZ\SYSAUX01.DBF’ to ‘d:\app\luoping\oradata\xz\SYSAUX01.DBF’ ;
Database altered.

oracleplus.net> alter database rename file ‘D:\ORACLE\PRODUCT\ORADATA\XZ\UNDOTBS01.DBF’ to ‘d:\app\luoping\oradata\xz\UNDOTBS01.DBF’;
Database altered.

oracleplus.net> alter database rename file ‘D:\ORACLE\PRODUCT\ORADATA\XZ\USERS01.DBF’ to ‘d:\app\luoping\oradata\xz\USERS01.DBF’ ;
alter database rename file ‘D:\ORACLE\PRODUCT\ORADATA\XZ\USERS01.DBF’ to ‘d:\app\luoping\oradata\xz\USERS01.DBF’
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 4 – new file ‘d:\app\luoping\oradata\xz\USERS01.DBF’ not found
ORA-01110: data file 4: ‘D:\ORACLE\PRODUCT\ORADATA\XZ\USERS01.DBF’
ORA-27041: unable to open file
OSD-04002:
O/S-Error: (OS 2)
这里是由于USERS01.DBF这个数据文件,朋友没有传给我,估计里面有业务数据

oracleplus.net> alter database rename file ‘D:\ORACLE\PRODUCT\ORADATA\XZ\SP02.DBF’ to ‘d:\app\luoping\oradata\xz\SP02.DBF’ ;
Database altered.

oracleplus.net> alter database rename file ‘D:\ORACLE\PRODUCT\ORADATA\XZ\REDO03.LOG’ to ‘d:\app\luoping\oradata\xz\REDO03.LOG’;
Database altered.

oracleplus.net> alter database rename file ‘D:\ORACLE\PRODUCT\ORADATA\XZ\REDO02.LOG’ to ‘d:\app\luoping\oradata\xz\REDO02.LOG’;
Database altered.

oracleplus.net> alter database rename file ‘D:\ORACLE\PRODUCT\ORADATA\XZ\REDO01.LOG’ to ‘d:\app\luoping\oradata\xz\REDO01.LOG’;
Database altered.

oracleplus.net> alter database datafile 4 offline;
Database altered.

4.2 ORA-16433错误,手动创建控制文件
这里recover数据库的时候报ORA-16433,这个错误其实在open resetlogs后,很容易出来的,只需要重建控制文件就可以了
oracleplus.net> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

oracleplus.net> CREATE CONTROLFILE REUSE DATABASE “XZ” NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 ‘d:\app\luoping\oradata\xz\REDO01.LOG’ SIZE 50M,
9 GROUP 2 ‘d:\app\luoping\oradata\xz\REDO02.LOG’ SIZE 50M,
10 GROUP 3 ‘d:\app\luoping\oradata\xz\REDO03.LOG’ SIZE 50M
11 DATAFILE
12 ‘d:\app\luoping\oradata\xz\SYSTEM01.DBF’,
13 ‘d:\app\luoping\oradata\xz\SYSAUX01.DBF’ ,
14 ‘d:\app\luoping\oradata\xz\UNDOTBS01.DBF’,
15 ‘d:\app\luoping\oradata\xz\SP02.DBF’
16 CHARACTER SET ZHS16GBK;

Control file created.

oracleplus.net> recover database using backup controlfile until cancel;
ORA-00279: 更改 1257137 (在 10/21/2014 16:09:30 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:\APP\LUOPING\FAST_RECOVERY_AREA\XZ\ARCHIVELOG\2014_10_21\O1_MF_1_1_%U_.ARC
ORA-00280: 更改 1257137 (用于线程 1) 在序列 #1 中


指定日志: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘D:\APP\LUOPING\ORADATA\XZ\SYSTEM01.DBF’

ORA-01112: 未启动介质恢复

oracleplus.net> recover database using backup controlfile until cancel;
ORA-00279: 更改 1257137 (在 10/21/2014 16:09:30 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:\APP\LUOPING\FAST_RECOVERY_AREA\XZ\ARCHIVELOhttp://www.oracleplus.netG\2014_10_21\O1_MF_1_1_%U_.ARC
ORA-00280: 更改 1257137 (用于线程 1) 在序列 #1 中

指定日志: {=suggested | filename | AUTO | CANCEL}
d:\app\luoping\oradata\xz\REDO01.LOG
已应用的日志。
完成介质恢复

4.3 触发ORA-00600 2663错误
oracleplus.net> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2663], [0], [1257153], [0],[1257178], [], [], [], [], [], [], []
进程 ID: 7148
会话 ID: 191 序列号: 1

oracleplus.net> alter session set events ‘10015 trace name adjust_scn level 1’;
会话已更改。

oracleplus.net> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2663], [0], [1257159], [0],
[1257178], [], [], [], [], [], [], []
进程 ID: 5312
会话 ID: 191 序列号: 1
2663上面提示的SCN值,可以通过下面查询到
FILE# RFILE# STATUS CHECKPOINT_CHANGE# OFFLINE_CHANGE# LAST_CHANGE# NAME
—– —— ————— —————— —————- —————- ————————————————————
1 1 SYSTEM 1257137 1257133 D:\ORACLE\PRODUCT\ORADATA\XZ\SYSTEM01.DBF
2 2 ONLINE 1257137 1257133 D:\ORACLE\PRODUCT\ORADATA\XZ\SYSAUX01.DBF
3 3 ONLINE 1257137 1257133 D:\ORACLE\PRODUCT\ORADATA\XZ\UNDOTBS01.DBF
4 4 ONLINE 1257137 1257133 D:\ORACLE\PRODUCT\ORADATA\XZ\USERS01.DBF
6 6 ONLINE 1257137 1257133 D:\ORACLE\PRODUCT\ORADATA\XZ\SP02.DBF
忘记了,我的版本是11.2.0.4,不能通过event或者参数来推进scn,可能通过oradebug来实现

oracleplus.net> oradebug poke 0x149876FA0 4 2257178;
BEFORE: [149876FA0, 149876FA4) = 00000000
AFTER: [149876FA0, 149876FA4) = 0022711A

4.4 数据库正常打开
oracleplus.net> alter database open resetlogs upgrade;
数据库已更改。

问题已经解决,剩下的工作就是temp表空间等操作。
本文固定链接: http://www.htz.pw/2014/10/21/11g%e6%95%b0%e6%8d%ae%e5%ba%93%e5%90%af%e5%8a%a8%e6%8a%a5ora-00283-ora-00600-2663%e9%94%99%e8%af%af.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-00283 ORA-00600 2663原因和MOS官方解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1143.html

Oracle研究中心

关键词:

ORA-00600

ORA-00283

通过oradebug推进SCN的方法

ORA-16433解决方法笔记