记一次断电恢复ORA-01033错误

来源：互联网发布：二维码标签制作软件编辑：程序博客网时间：2024/05/21 10:20

客户的电脑因为频繁断电,造成orcle无法连接,报ORA-01033:oracle初始化或者关闭错误.

按照传统方法

进入cmd

sqlplus sys/password@user as sysdba

可以连接.关闭数据库

shutdown immediate

ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。

再重新打开,就打不开了,提示 TNS: 监听程序当前无法识别连接描述符中请求的服务

遂修改listener.ora

原始listener.ora文件是这样的:

# listener.ora Network Configuration File: D:\app\LIHE\product\11.2.0\dbhome_2\NETWORK\ADMIN\listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (SID_NAME = CLRExtProc)      (ORACLE_HOME = D:\app\LIHE\product\11.2.0\dbhome_2)      (PROGRAM = extproc)      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\LIHE\product\11.2.0\dbhome_2\bin\oraclr11.dll")    )  )LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = USER-20160412KB)(PORT = 1521))    )  )ADR_BASE_LISTENER = D:\app\LIHE

改成:

# listener.ora Network Configuration File: D:\app\LIHE\product\11.2.0\dbhome_2\NETWORK\ADMIN\listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (SID_NAME = CLRExtProc)      (ORACLE_HOME = D:\app\LIHE\product\11.2.0\dbhome_2)      (PROGRAM = extproc)     )    (SID_DESC=       (GLOBAL_DBNAME=sde)       (ORACLE_HOME = D:\app\LIHE\product\11.2.0\dbhome_2)       (SID_NAME = sde)     )  )LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = USER-20160412KB)(PORT = 1521))    )  )ADR_BASE_LISTENER = D:\app\LIHE

再次重复数据库关闭/打开的操作,这次报 ORA-12528 TNS: 监听程序: 所有适用例程都无法建立新连接

遂修改tnsnames.ora

原始的tnsnames.ora是这样的

# tnsnames.ora Network Configuration File: D:\app\LIHE\product\11.2.0\dbhome_2\NETWORK\ADMIN\tnsnames.ora# Generated by Oracle configuration tools.ORACLR_CONNECTION_DATA =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    )    (CONNECT_DATA =      (SID = CLRExtProc)      (PRESENTATION = RO)    )  )SDE=  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = sde)    )  )LISTENER_SDE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SDE) ) )

修改后:

# tnsnames.ora Network Configuration File: D:\app\LIHE\product\11.2.0\dbhome_2\NETWORK\ADMIN\tnsnames.ora# Generated by Oracle configuration tools.ORACLR_CONNECTION_DATA =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    )    (CONNECT_DATA =      (SID = CLRExtProc)      (PRESENTATION = RO)    )  )SDE =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = sde)      (UR=A)    )  )LISTENER_SDE =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = SDE)    )  )

修改后可以开启数据库,但是又报这个错 ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr]

遂按照如下代码去处理

--尝试直接recover databaseSQL> RECOVER DATABASE ;ORA-00283: 恢复会话因错误而取消ORA-00264: 不要求恢复--提示不用恢复 --再打开数据库，还是kcratr_nab_less_than_odr错误警告SQL> ALTER DATABASE OPEN;ALTER DATABASE OPEN*第 1 行出现错误:ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189],[43531], [43569], [], [], [], [], [], [], [] --尝试不完全恢复SQL> RECOVER DATABASE UNTIL CANCEL;ORA-10879: error signaled in parallel recovery slaveORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误ORA-01152: 文件 1 没有从过旧的备份中还原ORA-01110: 数据文件 1: 'D:\DBDMS\DATA\SYSTEM01.DBF' --重建控制文件SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'D:/1.TXT'; 数据库已更改。 SQL> SHUTDOWN IMMEDIATE;ORA-01109: 数据库未打开  已经卸载数据库。ORACLE 例程已经关闭。SQL> STARTUP NOMOUNT;ORACLE 例程已经启动。 Total System Global Area  417546240 bytesFixed Size                  2176328 bytesVariable Size             268438200 bytesDatabase Buffers          138412032 bytesRedo Buffers                8519680 bytesSQL> CREATE CONTROLFILE REUSE DATABASE "DBDMS" NORESETLOGS  NOARCHIVELOG  2      MAXLOGFILES 16  3      MAXLOGMEMBERS 3  4      MAXDATAFILES 100  5      MAXINSTANCES 8  6      MAXLOGHISTORY 18688  7  LOGFILE  8    GROUP 1 'D:\DBDMS\LOG\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,  9    GROUP 2 'D:\DBDMS\LOG\REDO02.LOG'  SIZE 50M BLOCKSIZE 512, 10    GROUP 3 'D:\DBDMS\LOG\REDO03.LOG'  SIZE 50M BLOCKSIZE 512 11  DATAFILE 12    'D:\DBDMS\DATA\SYSTEM01.DBF', 13    'D:\DBDMS\DATA\SYSAUX01.DBF', 14    'D:\DBDMS\DATA\RBSG01.DBF', 15    'D:\DBDMS\DATA\DATA01.DBF', 16    'D:\DBDMS\DATA\INDX01.DBF', 17    'D:\DBDMS\DATA\DATA02.DBF', 18    'D:\DBDMS\DATA\DATA03.DBF', 19    'D:\DBDMS\DATA\DATA04.DBF', 20    'D:\DBDMS\DATA\INDX02.DBF', 21    'D:\DBDMS\DATA\SYSTEM02.DBF' 22  CHARACTER SET ZHS16GBK 23  ; 控制文件已创建。 --继续尝试恢复SQL> RECOVER DATABASE ;完成介质恢复。SQL> ALTER DATABASE OPEN;

最后一步OPEN的时候又报这个错误ORA-01589错误要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

又按照教程输入如下代码:

SQL> recover database using backup controlfileSQL> recover database using backup controlfile指定日志: {=suggested | filename | AUTO | CANCEL}E:\oracle\oradata\orcl\redo02.log －－－－－－－此处要求给出最新的logfile，到logfile的路径下查看这几个文件，如果能看到时间就直接给出最新的logfile绝对路径，如果看不到就一个个的实验一下就好了；已应用的日志。完成介质恢复。SQL> alter database open resetlogs;

最终重新打开库

0 0