当前位置:[首页 > 日语资讯 > J.TEST > Oracle 11g中SYS连接阻止关闭数据库]

Oracle 11g中SYS连接阻止关闭数据库

发布: 2017-11-08 10:32 | 来源:www.jptranslate.com | 查 看:

  从后台查询,发现数据库出现错误提示:

SHUTDOWN: Active processes prevent shutdown operation


  检查数据库后台进程,未发现占用大量资源的连接,实际上整个数据库中只有一个本地连接:

bash-3.00$ ps -ef|grep oraclerac11g2
  oracle 22273 22272 0 19:59:51 ? 0:00 oraclerac11g2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
  oracle 29307 22520 0 20:09:28 pts/2 0:00 grep oraclerac11g2


  经过检查发现,似乎是由于当前的SYS连接没有退出,而直接通过HOST命令启动了另一个sqlplus命令,并在其中执行了shutdown immediate操作,这时就会导致shutdown immediate被hang住:

$ sqlplus "/ as sysdba"
  SQL*Plus: Release 11.1.0.6.0 - Production on 星期五 8月 22 19:49:35 2008
  Copyright (c) 1982, 2007, Oracle. All rights reserved.
  连接到:
  Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
  With the Partitioning, Real Application Clusters, OLAP, Data Mining
  and Real Application Testing options
  SQL> shutdown immediate数据库已经关闭。已经卸载数据库。
  ORACLE 例程已经关闭。
  SQL> startup
  ORACLE 例程已经启动。
  Total System Global Area 1.7108E+10 bytes
  Fixed Size 2101632 bytes
  Variable Size 3344420480 bytes
  Database Buffers 1.3757E+10 bytes
  Redo Buffers 4431872 bytes数据库装载完毕。数据库已经打开。


  下面启动另一个会话,并以SYS登陆数据库,然后尝试关闭数据库:

SQL> shutdown immediate数据库已经关闭。已经卸载数据库。
  ORACLE 例程已经关闭。

发现数据库可以正常关闭,然后根据刚才的描述,在sqlplus中切换到主机命令行,然后再新启动一个sqlplus,执行shutdown immediate:


      SQL> startup
  ORACLE 例程已经启动。
  Total System Global Area 1.7108E+10 bytes
  Fixed Size 2101632 bytes
  Variable Size 3344420480 bytes
  Database Buffers 1.3757E+10 bytes
  Redo Buffers 4431872 bytes数据库装载完毕。数据库已经打开。
  SQL> select count(*) from v$session;
  COUNT(*)
  ----------
  36
  SQL> host
  $ sqlplus "/ as sysdba"
  SQL*Plus: Release 11.1.0.6.0 - Production on 星期五 8月 22 19:59:51 2008
  Copyright (c) 1982, 2007, Oracle. All rights reserved.
  连接到:
  Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
  With the Partitioning, Real Application Clusters, OLAP, Data Mining
  and Real Application Testing options
  SQL> shutdown immediate


  和预期的一样,关闭命令被hang住了,检查alert文件就可以看到上面的提示信息。

  退出当前的会话,回到原始会话,并重新连接,就可以正常的关闭数据库了:

SQL> shutdown immediate
  ^CORA-01013: 用户请求取消当前的操作
  SQL> exit从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
  With the Partitioning, Real Application Clusters, OLAP, Data Mining
  and Real Application Testing options 断开
  $ exit
  SQL> shutdown immediate
  ORA-03135: 连接失去联系
  SQL> conn / as sysdba已连接。
  SQL> shutdown immediate数据库已经关闭。已经卸载数据库。
  ORACLE 例程已经关闭。


而920版本的数据库是没有这个问题的:

[oracle@bjtest ~]$ sqlplus "/ as sysdba"
  SQL*Plus: Release 9.2.0.4.0 - Production on 星期六 8月 23 04:14:36 2008
  Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
  已连接到空闲例程。
  SQL> host
  [oracle@bjtest ~]$ ps -ef|grep ora
  root 3804 3792 0 Jul18 ? 00:00:29 hald-addon-storage: polling /dev/hda
  root 26417 26385 0 04:14 pts/1 00:00:00 su - oracle
  oracle 26418 26417 0 04:14 pts/1 00:00:00 -bash
  oracle 26451 26418 0 04:14 pts/1 00:00:00 sqlplus
  oracle 26452 26451 0 04:14 ? 00:00:00 oraclebjtest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
  oracle 26453 26451 0 04:14 pts/1 00:00:00 /bin/bash
  oracle 26481 26453 0 04:14 pts/1 00:00:00 ps -ef
  oracle 26482 26453 0 04:14 pts/1 00:00:00 grep ora
  [oracle@bjtest ~]$ exit
  exit
  SQL> startup
  ORACLE 例程已经启动。
  Total System Global Area 9415145752 bytes
  Fixed Size 755992 bytes
  Variable Size 822083584 bytes
  Database Buffers 8589934592 bytes
  Redo Buffers 2371584 bytes数据库装载完毕。数据库已经打开。
  SQL> host
  [oracle@bjtest ~]$ sqlplus "/ as sysdba"
  SQL*Plus: Release 9.2.0.4.0 - Production on 星期六 8月 23 04:15:08 2008
  Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
  连接到:
  Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
  With the Partitioning, OLAP and Oracle Data Mining options
  JServer Release 9.2.0.4.0 - Production
  SQL> shutdown immediate数据库已经关闭。已经卸载数据库。
  ORACLE 例程已经关闭。
  SQL> exit从Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
  With the Partitioning, OLAP and Oracle Data Mining options
  JServer Release 9.2.0.4.0 - Production中断开
  [oracle@bjtest ~]$ exit
  exit
  SQL> exit
  ERROR:
  ORA-03113: 通信通道的文件结束
  从Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
  With the Partitioning, OLAP and Oracle Data Mining options
  JServer Release 9.2.0.4.0 - Production(情况复杂)中断开