做网站没流量/惠州seo排名外包

ORA-01092: ORACLE 例程终止。强行断开连接

今天测试部门的人叫我过去，说是数据库当了，起不来了。[@more@]

我过去看了看情况，做了如下操作

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

Oracle 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

数据库装载完毕。

ORA-01092: ORACLE 例程终止。强行断开连接

表面上看不出问题，我查看alert_oracas.log文件

SYS auditing is disabled

Starting up ORACLE RDBMS Version: 9.2.0.1.0.

System parameters with non-default values:

processes = 150

timed_statistics = TRUE

shared_pool_size = 50331648

large_pool_size = 8388608

Java_pool_size = 33554432

control_files = f:oracleoradataoracasCONTROL01.CTL, f:oracleoradataoracasCONTROL02.CTL, f:oracleoradataoracasCONTROL03.CTL

db_block_size = 8192

db_cache_size = 25165824

compatible = 9.2.0.0.0

db_file_multiblock_read_count= 16

fast_start_mttr_target = 300

undo_management = AUTO

undo_tablespace = UNDOSTB1

undo_retention = 10800

remote_login_passWordfile= EXCLUSIVE

db_domain =

instance_name = oracas

dispatchers = (PROTOCOL=TCP) (SERVICE=oracasXDB)

job_queue_processes = 10

hash_join_enabled = TRUE

background_dump_dest = f:oracleadminoracasbdump

user_dump_dest = f:oracleadminoracasudump

core_dump_dest = f:oracleadminoracascdump

sort_area_size = 524288

db_name = oracas

open_cursors = 300

star_transformation_enabled= FALSE

query_rewrite_enabled = FALSE

pga_aggregate_target = 19922944

aq_tm_processes = 1

PMON started with pid=2

DBW0 started with pid=3

LGWR started with pid=4

CKPT started with pid=5

SMON started with pid=6

RECO started with pid=7

CJQ0 started with pid=8

QMN0 started with pid=9

Mon Apr 18 17:30:25 2005

starting up 1 shared server(s) ...

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

Mon Apr 18 17:30:26 2005

ALTER DATABASE MOUNT

Mon Apr 18 17:30:30 2005

SUCcessful mount of redo thread 1, with mount id 2424210674.

Mon Apr 18 17:30:30 2005

Database mounted in Exclusive Mode.

Completed: ALTER DATABASE MOUNT

Mon Apr 18 17:30:30 2005

ALTER DATABASE OPEN

Mon Apr 18 17:30:32 2005

Thread 1 opened at log sequence 105

Current log# 2 seq# 105 mem# 0: F:ORACLEORADATAORACASREDO02.LOG

Successful open of redo thread 1.

Mon Apr 18 17:30:32 2005

SMON: enabling cache recovery

Mon Apr 18 17:30:34 2005

Errors in file f:oracleadminoracasudumporacas_ora_3404.trc:

ORA-30012: 撤消表空间 'UNDOSTB1' 不存在或类型不正确

Mon Apr 18 17:30:34 2005

Error 30012 happened during db open, shutting down database

USER: terminating instance due to error 30012

Mon Apr 18 17:30:35 2005

Errors in file f:oracleadminoracasbdumporacas_smon_996.trc:

ORA-30012: undo tablespace '' does not exist or of wrong type

Mon Apr 18 17:30:35 2005

Errors in file f:oracleadminoracasbdumporacas_pmon_3500.trc:

ORA-30012: undo tablespace '' does not exist or of wrong type

Instance terminated by USER, pid = 3404

ORA-1092 signalled during: ALTER DATABASE OPEN...

于是我查看了以下信息来确认undo表空间的问题

SQL> conn as sysdba

已连接到空闲例程。

SQL> startup mount

ORACLE 例程已经启动。

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

数据库装载完毕。

SQL> select name from v$datafile;

NAME

------------------------------------------------------------------------

F:ORACLEORADATAORACASSYSTEM01.DBF

F:ORACLEORADATAORACASUNDOTBS01.DBF

F:ORACLEORADATAORACASCWMLITE01.DBF

F:ORACLEORADATAORACASDRSYS01.DBF

F:ORACLEORADATAORACASEXAMPLE01.DBF

F:ORACLEORADATAORACASINDX01.DBF

F:ORACLEORADATAORACASODM01.DBF

F:ORACLEORADATAORACASTOOLS01.DBF

F:ORACLEORADATAORACASUSERS01.DBF

F:ORACLEORADATAORACASXDB01.DBF

D:ORADATAORACASTSCAS11.DBF

E:ORADATAORACASTFCAS12.DBF

D:ORADATAORACASTSCAS21.DBF

E:ORADATAORACASTFCAS22.DBF

D:ORADATAORACASTSCAS31.DBF

E:ORADATAORACASTFCAS32.DBF

D:ORADATAORACASTSCAS41.DBF

E:ORADATAORACASTFCAS42.DBF

D:ORADATAORACASTSCASINDX1.DBF

E:ORADATAORACASTSCASINDX2.DBF

D:ORADATAORACASTFCAS13.DBF

D:ORADATAORACASTFCAS14.DBF

D:ORADATAORACASTFCAS23.DBF

D:ORADATAORACASTFCAS24.DBF

E:ORADATAORACASTSCASINDX12.DBF

E:ORADATAORACASTSCASINDX13.DBF

E:ORADATAORACASTSCASINDX24.DBF

E:ORADATAORACASTSCASINDX25.DBF

C:SNAPSHOT01.DBF

C:TSCAS1.DBF

已选择30行。

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- -----------------------

undo_management string AUTO

undo_retention integer 10800

undo_suppress_errors boolean FALSE

undo_tablespace string UNDOSTB1

SQL> select name from v$tablespace;

NAME

------------------------------

CWMLITE

DRSYS

EXAMPLE

INDX

ODM

SYSTEM

TOOLS

UNDOTBS1

USERS

XDB

TEMP

TSCAS1

TSCAS2

TSCAS3

TSCAS4

TSCASINDX1

TSCASINDX2

SNAPSHOT_TS

已选择18行。

当时没有自己看问题，就以为是undo文件损坏了，决定重建。

注意：后来才发现这里以后的操作是饶了一个解决问题的弯路。

由于不open没有办法重新创建undo 表空间，所以决定先用系统默认的undo表空间来启动，然后重建undo

表空间，具体操作如下：

SQL> create pfile from spfile;

文件已创建。

修改pfile文件

#*.undo_management='AUTO'

#*.undo_tablespace='UNDOSTB1'

undo_management=manual

undo_tablespace='system'

关闭数据库，并且从目录F:oracleora92database下去掉SPFILEORACAS.ORA文件

重新启动

SQL> shutdown immediate

ORA-01109: 数据库未打开

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup mount

ORACLE 例程已经启动。

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

数据库装载完毕。

SQL> alter database open;

数据库已更改。

查看表空间信息

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string MANUAL

undo_retention integer 10800

undo_suppress_errors boolean FALSE

undo_tablespace string system

SQL> select name from v$tablespace;

NAME

------------------------------

CWMLITE

DRSYS

EXAMPLE

INDX

ODM

SYSTEM

TOOLS

UNDOTBS1

USERS

XDB

TEMP

TSCAS1

TSCAS2

TSCAS3

TSCAS4

TSCASINDX1

TSCASINDX2

SNAPSHOT_TS

并且打开图形界面查看，这时候才发现undo表空间的名字是UNDOTBS1

其实早就能发现的，大家以后分析问题一定不要太快下结论，要自己分析。。。

于是做以下操作改变undo 表空间的名字

SQL> create spfile from pfile;

文件已创建。

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string MANUAL

undo_retention integer 10800

undo_suppress_errors boolean FALSE

undo_tablespace string system

SQL> alter database set undo_management=auto;

alter database set undo_management=auto

*

ERROR 位于第 1 行:

ORA-02231: 缺少或无效的 ALTER DATABASE 选项

SQL> alter database set undo_management=auto scope=spfile;

alter database set undo_management=auto scope=spfile

*

ERROR 位于第 1 行:

ORA-02231: 缺少或无效的 ALTER DATABASE 选项

SQL> alter system set undo_management=auto scope=spfile;

系统已更改。

SQL> alter system set undo_tablespace='undotbs1' scope=spfile;

重启验证：

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

数据库装载完毕。

数据库已经打开。

SQL> create fpile from spfile;

create fpile from spfile

*

ERROR 位于第 1 行:

-

ORA-00901: 无效 CREATE 命令

SQL> create pfile from spfile;

文件已创建。

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 10800

undo_suppress_errors boolean FALSE

undo_tablespace string undotbs1

发现已经好使了

教训总结：其实只要一开始分析下v$tablespace就能发现是undo表空间的名字错了，很快就能解决的。

不过我上面这种方法可以用于undo表空间文件corrupt的情况：)