数据库异常断电,起不起来了,那位大神看一下,怎么解决? 貌似是控制文件的问题。可以nomunt,不能mount;SQL> startup nomount;
ORACLE instance started.Total System Global Area 4.7435E+10 bytes
Fixed Size 2237304 bytes
Variable Size 2.2549E+10 bytes
Database Buffers 2.4830E+10 bytes
Redo Buffers 53473280 bytesSQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 695
Session ID: 570 Serial number: 1
------------------------alter日志文件------------------------
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed May 08 11:19:08 2013
MMNL started with pid=17, OS id=689
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Wed May 08 11:24:01 2013
alter database mount
USER (ospid: 695): terminating the instance
Wed May 08 11:24:06 2013
System state dump requested by (instance=1, osid=695), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/mda/mda/trace/mda_diag_666.trc
Dumping diagnostic data in directory=[cdmp_20130508112406], requested by (instance=1, osid=695), summary=[abnormal instance termination].
Instance terminated by USER, pid = 695------------------------跟踪文件日至信息------------------------
*** 2013-05-08 11:24:01.757
CLOSE #47870066999128:c=0,e=23,dep=0,type=0,tim=1367983441757911
XCTEND rlbk=0, rd_only=1, tim=1367983441758284
=====================
PARSING IN CURSOR #47870066999128 len=20 dep=0 uid=0 oct=35 lid=0 tim=1367983441758535 hv=1913505115 ad='b67f814d8' sqlid='fr02x8dt0vjav'
alter database mount
END OF STMT
PARSE #47870066999128:c=1000,e=535,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1367983441758534*** 2013-05-08 11:24:05.784
Error: kccpb_sanity_check_2
Control file sequence number mismatch!
fhcsq: 59020 bhcsq: 59023 cfn 0
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+461<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+53<-ksuitm()+1325<-kccpb_sanity_check()+341<-kccbmp_get()+309<-kccsed_rbl()+111<-kccocx()+1154<-kccocf()+136<-kcfcmb()+1025<-kcfmdb()+54<-adbdrv()+63122<-opiexe()+18173<-opiosq0()+3993<-kpooprx()+274
<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+1670
----- End of Abridged Call Stack Trace -----*** 2013-05-08 11:24:05.817
USER (ospid: 695): terminating the instance
ksuitm: waiting up to [5] seconds before killing DIAG(666)还需要什么信息,请指教。本人新手。Oracle数据库异常实例SQL
ORACLE instance started.Total System Global Area 4.7435E+10 bytes
Fixed Size 2237304 bytes
Variable Size 2.2549E+10 bytes
Database Buffers 2.4830E+10 bytes
Redo Buffers 53473280 bytesSQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 695
Session ID: 570 Serial number: 1
------------------------alter日志文件------------------------
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed May 08 11:19:08 2013
MMNL started with pid=17, OS id=689
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Wed May 08 11:24:01 2013
alter database mount
USER (ospid: 695): terminating the instance
Wed May 08 11:24:06 2013
System state dump requested by (instance=1, osid=695), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/mda/mda/trace/mda_diag_666.trc
Dumping diagnostic data in directory=[cdmp_20130508112406], requested by (instance=1, osid=695), summary=[abnormal instance termination].
Instance terminated by USER, pid = 695------------------------跟踪文件日至信息------------------------
*** 2013-05-08 11:24:01.757
CLOSE #47870066999128:c=0,e=23,dep=0,type=0,tim=1367983441757911
XCTEND rlbk=0, rd_only=1, tim=1367983441758284
=====================
PARSING IN CURSOR #47870066999128 len=20 dep=0 uid=0 oct=35 lid=0 tim=1367983441758535 hv=1913505115 ad='b67f814d8' sqlid='fr02x8dt0vjav'
alter database mount
END OF STMT
PARSE #47870066999128:c=1000,e=535,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1367983441758534*** 2013-05-08 11:24:05.784
Error: kccpb_sanity_check_2
Control file sequence number mismatch!
fhcsq: 59020 bhcsq: 59023 cfn 0
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+461<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+53<-ksuitm()+1325<-kccpb_sanity_check()+341<-kccbmp_get()+309<-kccsed_rbl()+111<-kccocx()+1154<-kccocf()+136<-kcfcmb()+1025<-kcfmdb()+54<-adbdrv()+63122<-opiexe()+18173<-opiosq0()+3993<-kpooprx()+274
<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+1670
----- End of Abridged Call Stack Trace -----*** 2013-05-08 11:24:05.817
USER (ospid: 695): terminating the instance
ksuitm: waiting up to [5] seconds before killing DIAG(666)还需要什么信息,请指教。本人新手。Oracle数据库异常实例SQL
Error: kccpb_sanity_check_2
Control file sequence number mismatch!
是控制文件出错了,楼主可有控制文件的备份?
没有控制文件备份,在哪创建控制文件恢复?
startup nomount下执行以下SQL语句:CREATE CONTROLFILE SET DATABASE
<<sid>> RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'D:\app\vanjayhsu\oradata\sq\redo01.log' SIZE 100M,--替换为你实际的重做日志文件
GROUP 2 'D:\app\vanjayhsu\oradata\sq\redo02.log' SIZE 100M,
GROUP 3 'D:\app\vanjayhsu\oradata\sq\redo03.log' SIZE 100M
DATAFILE
'D:\app\vanjayhsu\oradata\ssq\sq01.dbf',--替换为你实际数据文件路径
'D:\app\vanjayhsu\oradata\ssq\sysaux01.dbf',
'D:\app\vanjayhsu\oradata\ssq\system01.dbf',
'D:\app\vanjayhsu\oradata\ssq\undotbs01.dbf',
'D:\app\vanjayhsu\oradata\ssq\users01.dbf'
CHARACTER SET ZHS16GBK; 创建成功之后
Alter Database Open resetlogs;如果能顺利打开的话,就能恢复数据库了。建议立即备份相关重要数据,以防万一!
cp /u01/app/oracle/oradata/omrep/control*.ctl /tmp/
alter database backup controlfile to '/tmp/control01.ctl';
alter database backup controlfile to trace as '/tmp/controlfile.sql';
没有控制文件备份,在哪创建控制文件恢复?
startup nomount下执行以下SQL语句:CREATE CONTROLFILE SET DATABASE
<<sid>> RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'D:\app\vanjayhsu\oradata\sq\redo01.log' SIZE 100M,--替换为你实际的重做日志文件
GROUP 2 'D:\app\vanjayhsu\oradata\sq\redo02.log' SIZE 100M,
GROUP 3 'D:\app\vanjayhsu\oradata\sq\redo03.log' SIZE 100M
DATAFILE
'D:\app\vanjayhsu\oradata\ssq\sq01.dbf',--替换为你实际数据文件路径
'D:\app\vanjayhsu\oradata\ssq\sysaux01.dbf',
'D:\app\vanjayhsu\oradata\ssq\system01.dbf',
'D:\app\vanjayhsu\oradata\ssq\undotbs01.dbf',
'D:\app\vanjayhsu\oradata\ssq\users01.dbf'
CHARACTER SET ZHS16GBK; 创建成功之后
Alter Database Open resetlogs;如果能顺利打开的话,就能恢复数据库了。建议立即备份相关重要数据,以防万一!我的系统是Linux的,不知道这个在Linux下有效吗?
没有控制文件备份,在哪创建控制文件恢复?
startup nomount下执行以下SQL语句:CREATE CONTROLFILE SET DATABASE
<<sid>> RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'D:\app\vanjayhsu\oradata\sq\redo01.log' SIZE 100M,--替换为你实际的重做日志文件
GROUP 2 'D:\app\vanjayhsu\oradata\sq\redo02.log' SIZE 100M,
GROUP 3 'D:\app\vanjayhsu\oradata\sq\redo03.log' SIZE 100M
DATAFILE
'D:\app\vanjayhsu\oradata\ssq\sq01.dbf',--替换为你实际数据文件路径
'D:\app\vanjayhsu\oradata\ssq\sysaux01.dbf',
'D:\app\vanjayhsu\oradata\ssq\system01.dbf',
'D:\app\vanjayhsu\oradata\ssq\undotbs01.dbf',
'D:\app\vanjayhsu\oradata\ssq\users01.dbf'
CHARACTER SET ZHS16GBK; 创建成功之后
Alter Database Open resetlogs;如果能顺利打开的话,就能恢复数据库了。建议立即备份相关重要数据,以防万一!我的系统是Linux的,不知道这个在Linux下有效吗?
这个是SQL,跟平台无关的
没有控制文件备份,在哪创建控制文件恢复?
startup nomount下执行以下SQL语句:CREATE CONTROLFILE SET DATABASE
<<sid>> RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'D:\app\vanjayhsu\oradata\sq\redo01.log' SIZE 100M,--替换为你实际的重做日志文件
GROUP 2 'D:\app\vanjayhsu\oradata\sq\redo02.log' SIZE 100M,
GROUP 3 'D:\app\vanjayhsu\oradata\sq\redo03.log' SIZE 100M
DATAFILE
'D:\app\vanjayhsu\oradata\ssq\sq01.dbf',--替换为你实际数据文件路径
'D:\app\vanjayhsu\oradata\ssq\sysaux01.dbf',
'D:\app\vanjayhsu\oradata\ssq\system01.dbf',
'D:\app\vanjayhsu\oradata\ssq\undotbs01.dbf',
'D:\app\vanjayhsu\oradata\ssq\users01.dbf'
CHARACTER SET ZHS16GBK; 创建成功之后
Alter Database Open resetlogs;如果能顺利打开的话,就能恢复数据库了。建议立即备份相关重要数据,以防万一!我的系统是Linux的,不知道这个在Linux下有效吗?
这个是SQL,跟平台无关的
你好,执行以后,提示 控制文件已经存在。
SQL> @/tmp/a.txt
CREATE CONTROLFILE SET DATABASE
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/app/oracle/oradata/mda/control01.ctl'
ORA-27038: created file already exists
Additional information: 1把这个删了以后,还是提示已经存在:
SQL> @/tmp/a.txt
CREATE CONTROLFILE SET DATABASE
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/mda/control02.ctl'
ORA-27038: created file already exists
Additional information: 1
把这个也删除了吗?
MDA RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/mda/redo01.log' SIZE 100M,
GROUP 2 '/u01/app/oracle/oradata/mda/redo02.log' SIZE 100M,
GROUP 3 '/u01/app/oracle/oradata/mda/redo03.log' SIZE 100M,
GROUP 4 '/u01/app/oracle/oradata/mda/redo04.log' SIZE 100M,
GROUP 5 '/u01/app/oracle/oradata/mda/redo05.log' SIZE 100M
DATAFILE
'/u01/app/oracle/oradata/mda/system01.dbf',
'/u01/app/oracle/oradata/mda/sysaux01.dbf',
'/u01/app/oracle/oradata/mda/undotbs01.dbf',
'/u01/app/oracle/oradata/mda/users01.dbf',
'/u01/app/oracle/oradata/mda/tbs_dat_tdrsh.dbf',
'/u01/app/oracle/oradata/mda/tbs_idx_tdrsh.dbf',
'/u01/app/oracle/oradata/mda/tbs_dat_taskresult.dbf',
'/u01/app/oracle/oradata/mda/tbs_idx_taskresult.dbf',
'/u01/app/oracle/oradata/mda/tbs_dat_tdrshcda.dbf',
'/u01/app/oracle/oradata/mda/tbs_temp_tdrshcda.dbf',
'/u01/app/oracle/oradata/mda/zs_mda.dbf',
'/u01/app/oracle/oradata/mda/zs_midresult.dbf',
'/u01/app/oracle/oradata/mda/tbs_queryresult_1.dbf',
'/u01/app/oracle/oradata/mda/tbs_dat_clob.dbf',
'/u01/app/oracle/oradata/mda/tbs_idx_clob.dbf'
CHARACTER SET ZHS16GBK;
2. 故障的具体原因是什么?
3. 做好备份,确保安全
CREATE CONTROLFILE SET DATABASE
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/app/oracle/oradata/mda/control01.ctl'
ORA-27038: created file already exists
Additional information: 1把这个删了以后,还是提示已经存在:
SQL> @/tmp/a.txt
CREATE CONTROLFILE SET DATABASE
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/mda/control02.ctl'
ORA-27038: created file already exists
Additional information: 1
把这个也删除了吗?
pfile只是启动到nomount工程。可以nomount,不是spfile文件的问题。谢谢
同意建议楼主1.确定fast_recovery_area/mda/control02.ctl--备份的控制文件是通过什么操作备份的?什么时候备份的?是否是最新的?
2.故障的原因应该是控制文件损坏,但控制文件损坏之前是否做了什么操作?是否断电?或其他异常操作?3.如果选择通过重建控制文件进行恢复,建议把fast_recovery_area/mda/control02.ctl文件先COPY到一个备份目录。然后执行重建控制文件命令。--应该还有一个备份是fast_recovery_area/mda/control03.ctl,也COPY到备份目录,然后删除02和03.检查该目录下还有无其他备份文件。4.重建完成之后记得Alter Database Open resetlogs;