今天早晨在上班的公交车上客户打电话过来系统无法连接到数据库,到公司通过QQ远程控制连接到客户机器,启动cmd,输入sqlplus sys/password as sysdba,连接到oracle输入
sql>select status from v$instance;查询数据库状态
sql>status
mounted
唉,怎么在mount状态,open一下应该就可以了
sql>alter database open;
报一些控制文件数据文件相关的错误,google一下错误码,控制文件损坏导致无法打开数据库。之后根据http://chinaitpower.com/A/2004-10-15/158033.html处所说的步骤操作,具体如下:
1.ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
这时候会在udump目录下生成SID_ora_*.trc文件,根据你是在归档还是非归档模式下,选择一段内容
建立创建脚本
我是在非归档模式下,选择第一段内容
2.从SID_ora_*.trc文件中拷贝 Set #1段(非归档模式,归档模式选择Set #2段),新建一个文本文件,命名为create.sql
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:/ORACLEXE/APP/ORACLE/FLASH_RECOVERY_AREA/XE/ONLINELOG/O1_MF_1_3SFJSWF5_.LOG' SIZE 50M,
GROUP 2 'C:/ORACLEXE/APP/ORACLE/FLASH_RECOVERY_AREA/XE/ONLINELOG/O1_MF_2_3SFJSY00_.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'C:/ORACLEXE/ORADATA/XE/SYSTEM.DBF',
'C:/ORACLEXE/ORADATA/XE/UNDO.DBF',
'C:/ORACLEXE/ORADATA/XE/SYSAUX.DBF',
'C:/ORACLEXE/ORADATA/XE/USERS.DBF',
'C:/ORACLEXE/APP/ORACLE/PRODUCT/10.2.0/SERVER/DATABASE/CHENDA01.DBF',
'C:/ORACLEXE/ORADATA/XE/SYSTEM02.DBF'
CHARACTER SET AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'C:/ORACLEXE/APP/ORACLE/FLASH_RECOVERY_AREA/XE/ARCHIVELOG/2009_04_10/O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'C:/ORACLEXE/APP/ORACLE/FLASH_RECOVERY_AREA/XE/ARCHIVELOG/2009_04_10/O1_MF_1_1_%U_.ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:/ORACLEXE/ORADATA/XE/TEMP.DBF' REUSE;
-- End of tempfile additions.
3.输入shutdown abort 关闭数据库。
4.运行2中创建的create.sql脚本
数据库恢复正常.