Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 数据库由于Active Session无法关闭，而导致返回OPEN状态 ...
发新帖
cargoo

0

积分

0

好友

3

主题

[SQL调优] 数据库由于Active Session无法关闭，而导致返回OPEN状态

1#
发表于 2013-1-6 10:30:19 | 查看: 3969| 回复: 5
本帖最后由 cargoo 于 2013-1-6 10:54 编辑

Sat Jan  5 14:33:56 2013
ARC0: Evaluating archive   log 1 thread 1 sequence 26650
ARC0: Beginning to archive log 1 thread 1 sequence 26650
Creating archive destination LOG_ARCHIVE_DEST_1: '/oradata/arch/1_26650.dbf'
ARC0: Completed archiving  log 1 thread 1 sequence 26650
Sat Jan  5 14:40:00 2013
Shutting down instance: further logons disabled
Shutting down instance (normal)
License high water mark = 190
Sat Jan  5 14:45:02 2013
Active process 618900 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 749878 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 803214 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 905686 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 311568 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 721130 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 618506 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 815462 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 819492 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 659962 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 237696 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 499834 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 708618 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 680420 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 700608 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 663860 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 373148 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 532614 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 299360 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 315466 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 475230 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 856360 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 545040 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 323998 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 508208 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 541008 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 110854 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 671916 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 811464 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 606364 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 651758 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 467144 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 557442 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 327760 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 627004 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 553106 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 643526 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 524514 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 684130 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 643114 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 844112 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 569394 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 606476 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 491830 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 639246 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 147516 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 717278 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 598184 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 688588 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 303594 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 790978 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 454892 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 782624 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 766028 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 442430 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 364598 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 450812 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 836028 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 860484 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 582104 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 647562 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 610360 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 291174 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 713000 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 852246 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 827884 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 540920 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 889268 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 426026 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 700792 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 614606 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 479334 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 729464 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 417958 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 675898 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 602478 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 622996 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 868842 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 770532 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 729116 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 331806 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 684414 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 512002 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 491656 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 680006 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 610740 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 328016 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 630976 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 507968 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 749622 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 319504 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 340422 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 569744 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 725414 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 840058 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 737482 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 876966 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 565310 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 733654 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 667748 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 549356 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 762130 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 307682 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 716846 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 528874 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 585770 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 602296 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 438278 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 471168 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 655630 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
Active process 725206 user 'oracle' program 'oracle@shen01 (TNS V1-V3)'
SHUTDOWN: waiting for logins to complete.
Sat Jan  5 15:01:43 2013
Starting ORACLE instance (normal)
Sat Jan  5 15:28:32 2013
Thread 1 advanced to log sequence 26652
Sat Jan  5 15:28:32 2013
  Current log# 2 seq# 26652 mem# 0: /oradata/shen/redo02.log
Sat Jan  5 15:28:32 2013
ARC0: Evaluating archive   log 3 thread 1 sequence 26651
  Current log# 2 seq# 26652 mem# 1: /oradata/shen/redo05.log
Sat Jan  5 15:28:32 2013
ARC0: Beginning to archive log 3 thread 1 sequence 26651
Creating archive destination LOG_ARCHIVE_DEST_1: '/oradata/arch/1_26651.dbf'
ARC0: Completed archiving  log 3 thread 1 sequence 26651
Sat Jan  5 15:40:03 2013
SHUTDOWN: Active sessions prevent database close operation

问题1：这种现象是否是正常的？

问题2：如果使用shutdown immediate，而Active Session也没有手工杀掉，一般要等待多长时间PMON才会杀掉Active Session，如果超时的话，是不是也会出现“SHUTDOWN: Active sessions prevent database close operation“现象。
。
2#
发表于 2013-1-6 10:42:42
Shutting down instance (normal)

回复 只看该作者 道具 举报

3#
发表于 2013-1-6 11:31:59
Jobs Not Executing Automatically [ID 313102.1]

11) Check for unsuccessful shutdowns:

A shutdown immediate may get canceled because active sessions prevent the database close operation.

Please review the alert log for the last two shutdown / startups and the messages:
SHUTDOWN: Active sessions prevent database close operation

Please refer to
Note 434690.1 - Database Jobs Do Not Run After a Failed 'Shutdown Immediate'

回复 只看该作者 道具 举报

4#
发表于 2013-1-6 17:02:13



ps -ef|grep LOCAL | grep -v grep | awk '{print  $2}' | xargs kill -9

若发现shutdown时 alert.log提示说 active session则手动KILL LOCAL=NO 的server process， 同时关闭instance前 stop listener也是一个好习惯。

回复 只看该作者 道具 举报

5#
发表于 2013-1-6 20:46:41
Maclean Liu(刘相兵 发表于 2013-1-6 17:02
ps -ef|grep LOCAL | grep -v grep | awk '{print  $2}' | xargs kill -9

若发现shutdown时 alert.log ...

如果是在rac中只关闭一个节点instance，也可以直接kill 这些server process吗？

回复 只看该作者 道具 举报

6#
发表于 2013-1-6 21:57:18
fluttersnow 发表于 2013-1-6 20:46
如果是在rac中只关闭一个节点instance，也可以直接kill 这些server process吗？

只关自己节点的， 可以

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-16 06:40 , Processed in 0.046918 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569