昨天启动数据库服务以后,用plsql连接时,出现这样的问题:
ORA-12528:all appropriate instances are blocking在网上搜索了一些资料,最后在listener.ora文件中添加如下代码
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = e:\oracle\product\10.1.0\db_1)
(SID_NAME = orcl)
)重启监听和数据库服务以后,使用plsql 超级用户(sys)可以连接上,单击左边的菜单文件时,出现以下问题:
ORA-01219 :database not open :queries allowed on fixed tables/views only
也就是说,只能连上数据库,但是不能做其他操作。当我使用sqlplus连接到数据库时,执行shutdown 和 startup时出现以下问题
SQL> shutdown immediate
ORA-01507: database not mountedSQL> startup
ORACLE 例程已经启动。Total System Global Area 364904448 bytes
Fixed Size 788912 bytes
Variable Size 258733648 bytes
Database Buffers 104857600 bytes
Redo Buffers 524288 bytes
ORA-00227: corrupt block detected in controlfile: (block 1, # blocks 1)
ORA-00202: controlfile: 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL'网上搜索了一下,此问题是由于控制文件损坏造成的,
解决方法:将备份文件拷贝到目标文件下,替换掉原文件即可。
此时问题又来了,我在创建数据库实例时,没有启用备份,所以就不能使用以上方法了。但是可以通过命令语句重新生成控制文件,操作如下:
SQL>create controlfile reuse database sbtzit noresetlogs archivelog
Logfile group 1 'e:\oracle\oradata\sbtzit\redo01.log',
group 2 'e:\oracle\oradata\sbtzit\redo02.log',
group 3 'e:\oracle\oradata\sbtzit\redo03.log',
datafile 'e:\oracle\oradata\sbtzit\system01.dbf',
'e:\oracle\oradata\sbtzit\users01.dbf',
'e:\oracle\oradata\sbtzit\temp01.dbf',
'e:\oracle\oradata\sbtzit\tools01.dbf',
'e:\oracle\oradata\sbtzit\indx01.dbf',
'e:\oracle\oradata\sbtzit\dr01.dbf',
'e:\oracle\oradata\sbtzit\rbs01.dbf'; ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file 'e:\oracle\oradata\orcl\system01.dbf'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) ??????????????????????今天早上弄了一上午,竟然得到这样的结果,我很崩溃.我对ORACLE数据库配置方面了解甚少,希望有高手可以帮我解决下这个问题,在此谢谢大家了。
ORA-12528:all appropriate instances are blocking在网上搜索了一些资料,最后在listener.ora文件中添加如下代码
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = e:\oracle\product\10.1.0\db_1)
(SID_NAME = orcl)
)重启监听和数据库服务以后,使用plsql 超级用户(sys)可以连接上,单击左边的菜单文件时,出现以下问题:
ORA-01219 :database not open :queries allowed on fixed tables/views only
也就是说,只能连上数据库,但是不能做其他操作。当我使用sqlplus连接到数据库时,执行shutdown 和 startup时出现以下问题
SQL> shutdown immediate
ORA-01507: database not mountedSQL> startup
ORACLE 例程已经启动。Total System Global Area 364904448 bytes
Fixed Size 788912 bytes
Variable Size 258733648 bytes
Database Buffers 104857600 bytes
Redo Buffers 524288 bytes
ORA-00227: corrupt block detected in controlfile: (block 1, # blocks 1)
ORA-00202: controlfile: 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL'网上搜索了一下,此问题是由于控制文件损坏造成的,
解决方法:将备份文件拷贝到目标文件下,替换掉原文件即可。
此时问题又来了,我在创建数据库实例时,没有启用备份,所以就不能使用以上方法了。但是可以通过命令语句重新生成控制文件,操作如下:
SQL>create controlfile reuse database sbtzit noresetlogs archivelog
Logfile group 1 'e:\oracle\oradata\sbtzit\redo01.log',
group 2 'e:\oracle\oradata\sbtzit\redo02.log',
group 3 'e:\oracle\oradata\sbtzit\redo03.log',
datafile 'e:\oracle\oradata\sbtzit\system01.dbf',
'e:\oracle\oradata\sbtzit\users01.dbf',
'e:\oracle\oradata\sbtzit\temp01.dbf',
'e:\oracle\oradata\sbtzit\tools01.dbf',
'e:\oracle\oradata\sbtzit\indx01.dbf',
'e:\oracle\oradata\sbtzit\dr01.dbf',
'e:\oracle\oradata\sbtzit\rbs01.dbf'; ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file 'e:\oracle\oradata\orcl\system01.dbf'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) ??????????????????????今天早上弄了一上午,竟然得到这样的结果,我很崩溃.我对ORACLE数据库配置方面了解甚少,希望有高手可以帮我解决下这个问题,在此谢谢大家了。
控制文件有3个,显示控制文件1损坏了,把CONTROL01.CTL 删除掉,把CONTROL02.CTL 复制一份,并重命名为CONTROL01.CTL, 在启动下数据库看看. 如果能启动,就ok了,不行在重建控制文件 .
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
感谢您的帮助,我照你的方法做了,还是同样的问题,我的数据库服务是可以启动的,超级用户sys也是可以连接的,就是sys不能进行任何操作,查询的时候弹“ORA-01219 :database not open :queries allowed on fixed tables/views only ”个错误,重建控制文件也会报错,我的帖子上有说明。请你应该如何重建控制文件?
你先用sys 连接之后,在startup 数据库看看。 顺便把你的alert log日志 最后200行贴出来看看. 数据库没有启动, 自然不能查询...------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
Dump file e:\oracle\product\10.1.0\admin\sbtzit\bdump\alert_sbtzit.log
Wed Dec 09 16:01:47 2009
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Windows Server 2003 Version V5.2 Service Pack 1
CPU : 2 - type 586, 1 Physical Cores
Process Affinity: 0x00000000
Memory (A/P) : PH:1182M/2046M, PG:2682M/3946M, VA:1957M/2047M
Wed Dec 09 16:01:47 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
KCCDEBUG_LEVEL = 0
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
Dynamic strands is set to TRUE
Running with 2 shared and 18 private strand(s). Zero-copy redo is FALSE
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 10.1.0.2.0.
System parameters with non-default values:
processes = 150
shared_pool_size = 188743680
large_pool_size = 12582912
java_pool_size = 54525952
control_files = E:\ORACLE\PRODUCT\10.1.0\ORADATA\SBTZIT\CONTROL01.CTL, E:\ORACLE\PRODUCT\10.1.0\ORADATA\SBTZIT\CONTROL02.CTL, E:\ORACLE\PRODUCT\10.1.0\ORADATA\SBTZIT\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 104857600
compatible = 10.1.0.2.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = E:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=SBTZITXDB)
job_queue_processes = 10
background_dump_dest = E:\ORACLE\PRODUCT\10.1.0\ADMIN\SBTZIT\BDUMP
user_dump_dest = E:\ORACLE\PRODUCT\10.1.0\ADMIN\SBTZIT\UDUMP
core_dump_dest = E:\ORACLE\PRODUCT\10.1.0\ADMIN\SBTZIT\CDUMP
sort_area_size = 65536
db_name = SBTZIT
open_cursors = 300
pga_aggregate_target = 125829120
PMON started with pid=2, OS id=5772
MMAN started with pid=3, OS id=5776
DBW0 started with pid=4, OS id=5788
LGWR started with pid=5, OS id=5792
CKPT started with pid=6, OS id=5796
SMON started with pid=7, OS id=5800
RECO started with pid=8, OS id=5804
CJQ0 started with pid=9, OS id=5808
Wed Dec 09 16:01:48 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Wed Dec 09 16:01:48 2009
alter database mount exclusive
Wed Dec 09 16:01:49 2009
Controlfile identified with block size 16384
Wed Dec 09 16:01:49 2009
Hex dump of (file 0, block 1) in trace file e:\oracle\product\10.1.0\admin\sbtzit\udump\sbtzit_ora_5824.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.00001006 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x8c56
computed block checksum: 0x1670
Informational message:
Controlfile 0 with seq# 4102 has a fractured header
Hex dump of (file 0, block 1) in trace file e:\oracle\product\10.1.0\admin\sbtzit\udump\sbtzit_ora_5824.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.00001006 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x8c56
computed block checksum: 0x1670
Informational message:
Controlfile 1 with seq# 4102 has a fractured header
Hex dump of (file 0, block 1) in trace file e:\oracle\product\10.1.0\admin\sbtzit\udump\sbtzit_ora_5824.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.00001006 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x8c56
computed block checksum: 0x1670
Informational message:
Controlfile 2 with seq# 4102 has a fractured header
Wed Dec 09 16:01:49 2009
Errors in file e:\oracle\product\10.1.0\admin\sbtzit\udump\sbtzit_ora_5824.trc:
ORA-00202: controlfile: 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\SBTZIT\CONTROL01.CTL'
Wed Dec 09 16:06:07 2009
Shutting down instance: further logons disabled
Wed Dec 09 16:06:07 2009
Stopping background process CJQ0
Wed Dec 09 16:06:07 2009
Shutting down instance (normal)
License high water = 2
Wed Dec 09 16:06:07 2009
Stopping Job queue slave processes
Wed Dec 09 16:06:07 2009
Job queue slave processes stopped
Wed Dec 09 16:06:28 2009
All dispatchers and shared servers shutdown
Wed Dec 09 16:06:28 2009
ALTER DATABASE CLOSE NORMAL
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ARCH: Archival disabled due to shutdown: 1090
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1090
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Wed Dec 09 16:06:36 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
KCCDEBUG_LEVEL = 0
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
Dynamic strands is set to TRUE
Running with 2 shared and 18 private strand(s). Zero-copy redo is FALSE
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 10.1.0.2.0.
System parameters with non-default values:
processes = 150
shared_pool_size = 188743680
large_pool_size = 12582912
java_pool_size = 54525952
control_files = E:\ORACLE\PRODUCT\10.1.0\ORADATA\SBTZIT\CONTROL01.CTL, E:\ORACLE\PRODUCT\10.1.0\ORADATA\SBTZIT\CONTROL02.CTL, E:\ORACLE\PRODUCT\10.1.0\ORADATA\SBTZIT\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 104857600
compatible = 10.1.0.2.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = E:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=SBTZITXDB)
job_queue_processes = 10
background_dump_dest = E:\ORACLE\PRODUCT\10.1.0\ADMIN\SBTZIT\BDUMP
user_dump_dest = E:\ORACLE\PRODUCT\10.1.0\ADMIN\SBTZIT\UDUMP
core_dump_dest = E:\ORACLE\PRODUCT\10.1.0\ADMIN\SBTZIT\CDUMP
sort_area_size = 65536
db_name = SBTZIT
open_cursors = 300
pga_aggregate_target = 125829120
PMON started with pid=2, OS id=3340
MMAN started with pid=3, OS id=2784
DBW0 started with pid=4, OS id=2196
LGWR started with pid=5, OS id=2992
CKPT started with pid=6, OS id=2904
SMON started with pid=7, OS id=1012
RECO started with pid=8, OS id=636
CJQ0 started with pid=9, OS id=1024
Wed Dec 09 16:06:37 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Wed Dec 09 16:06:37 2009
ALTER DATABASE MOUNT
Wed Dec 09 16:06:37 2009
Controlfile identified with block size 16384
Wed Dec 09 16:06:37 2009
Hex dump of (file 0, block 1) in trace file e:\oracle\product\10.1.0\admin\sbtzit\udump\sbtzit_ora_3784.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.00001006 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x8c56
computed block checksum: 0x1670
Informational message:
Controlfile 0 with seq# 4102 has a fractured header
Hex dump of (file 0, block 1) in trace file e:\oracle\product\10.1.0\admin\sbtzit\udump\sbtzit_ora_3784.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.00001006 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x8c56
computed block checksum: 0x1670
Informational message:
Controlfile 1 with seq# 4102 has a fractured header
Hex dump of (file 0, block 1) in trace file e:\oracle\product\10.1.0\admin\sbtzit\udump\sbtzit_ora_3784.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.00001006 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x8c56
computed block checksum: 0x1670
Informational message:
Controlfile 2 with seq# 4102 has a fractured header
Wed Dec 09 16:06:37 2009
Errors in file e:\oracle\product\10.1.0\admin\sbtzit\udump\sbtzit_ora_3784.trc:
ORA-00202: controlfile: 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\SBTZIT\CONTROL01.CTL'ORA-227 signalled during: ALTER DATABASE MOUNT...
Wed Dec 09 16:07:24 2009
create controlfile reuse database sbtzit noresetlogs archivelog
Logfile group 1 'e:\oracle\oradata\sbtzit\redo01.log',
group 2 'e:\oracle\oradata\sbtzit\redo02.log',
group 3 'e:\oracle\oradata\sbtzit\redo03.log',
datafile 'e:\oracle\oradata\sbtzit\system01.dbf',
'e:\oracle\oradata\sbtzit\users01.dbf',
'e:\oracle\oradata\sbtzit\temp01.dbf',
'e:\oracle\oradata\sbtzit\tools01.dbf',
'e:\oracle\oradata\sbtzit\indx01.dbf',
'e:\oracle\oradata\sbtzit\dr01.dbf',
'e:\oracle\oradata\sbtzit\rbs01.dbf'
Wed Dec 09 16:07:25 2009
WARNING: Default passwords for SYS and SYSTEM will be used.
Please change the passwords.
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Wed Dec 09 16:07:25 2009
Errors in file e:\oracle\product\10.1.0\admin\sbtzit\udump\sbtzit_ora_3784.trc:
ORA-01565: error in identifying file 'e:\oracle\oradata\sbtzit\system01.dbf'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) 系统找不到指定的路径。ORA-1503 signalled during: create controlfile reuse database sbtzit noresetlo...
ORA-00202: controlfile: 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\SBTZIT\CONTROL01.CTL'
ORA-227 signalled during: alter database mount exclusive.1. 尝试复制CONTROL03.CTL 并重名为 CONTROL01.CTL。
2. c:>sqlplus /nolog
sql>conn sys/pwd as sysdba
SQL> STARTUP看看这样可行? 控制文件重建参考:
Oracle 控制文件
http://blog.csdn.net/tianlesoftware/archive/2009/12/09/4974440.aspx如果数据库有备份的话,还是恢复吧。 这样块点...
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
谢谢你的帮助,我应用了你告诉我的方法,还是不行,最后选择了数据库恢复。你对ORACLE控制文件的总结非常好,学习了,收藏了。
问题应该是你的控制文件换了,可以尝试下最后一个控制文件(control03.ctl),如果还不行可以考虑重建控制文件,在进行这些操作之前,建议对这些文件备份下,以免意外。根据你重建控制文件时的报错信息,系统找不到'e:\oracle\oradata\orcl\system01.dbf 请确认下该文件是否存在或者目录是否正确?最后一步可能就需要恢复了!