数据库无法Shut Down的案例[Oracle]

最新推荐文章于 2022-07-09 14:44:10 发布

yz394777014

最新推荐文章于 2022-07-09 14:44:10 发布

阅读量2.1k

点赞数

文章标签： oracle 数据库 thread database system archive

本文链接：https://blog.csdn.net/yz394777014/article/details/4406925

版权

(1) 数据库无法shutdown，一直挂着不动。我问是正常关闭么，答曰shutdown immediate。他问有无应急的办法让其关闭后重启，我说你就等等吧，immediate方式关闭的话很快能正常shutdown的。（许久后）我问 down下来了么答曰没。不对劲呀！

telnet上去一看发觉alert日志中有如下信息：
Fri Jun 15 13:48:26 2007
Shutting down instance: further logons disabled
Fri Jun 15 13:49:06 2007
Shutting down instance (immediate)
License high water mark = 990
Fri Jun 15 13:54:14 2007
Active call for process 16590 user 'oracle' program 'oracle@HP381 (TNS V1-V3)'
SHUTDOWN: waiting for active calls to complete.
Fri Jun 15 13:55:42 2007

用top查看了一下系统进程，如下：
Memory: 926732K (87056K) real, 1710592K (197156K) virtual, 331688K free Page# 1/11

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
1 ? 2981 oracle 154 20 21268K 1896K sleep 487:45 4.52 4.51 tnslsnr
0 ? 16590 oracle 181 20 538M 2236K run 11:33 3.75 3.75 oracleora9i
0 ? 2987 oracle 148 20 551M 10440K sleep 65:21 1.62 1.61 ora_dbw0_ora9i
1 ? 35 root 152 20 8064K 8064K run 47:43 0.59 0.59 vxfsd

一看就觉得16590这个oracle用户进程可疑。在发出shutdown命令后，这个进程对应的session事务一定在作回滚操作，导致数据库无法正常关闭。这个事务回滚也太久了吧，有点不正常，无奈现已无法跟踪到这个进程对应的session信息了。立即执行kill －9命令后数据库立马关闭了。
最后，总结一下数据库的正常关闭流程：
先停监听，然后停业务系统。接着ps -ef§grep ora看看还有那些(LOCAL=NO)的oracle用户进程，如果不能及时关闭的将其kill掉后发出shutdown immediate。
最多等待10分钟左右，不行就直接abort方式shutdown了。（到这一步基本没有问题，oracle还是比较安全的）

(2)环境：HP-UX , ORACLE 9I

这是电信的一个生产库。需要修改OS的时间。为了安全起见，需要重启DB。
登陆到数据库服务器之后，输入
sql>conn / as sysdba
sql>shutdown immediate
就卡在这里，等了约一个多小时，db都停不下来。
查看oracle log，显示
Thread 1 advanced to log sequence 220616
Current log# 2 seq# 220616 mem# 0: /dev/vgora/rlv_log2
Fri May 30 01:14:43 2008
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 268
Fri May 30 01:19:55 2008
Active call for process 15693 user 'oracle' program 'oracle@BimsDB1 (J000)'       -----这个job在运行
SHUTDOWN: waiting for active calls to complete.
Fri May 30 01:20:23 2008
Thread 1 advanced to log sequence 220617
Current log# 3 seq# 220617 mem# 0: /dev/vgora/rlv_log3
查看os top，发现一个进程号为15693的oracle job正在运行。
kill -9 15693
db还是停不下来。查看oracle log，还是和kill job之前一样。
已经折腾一个多小时了，只有使用最后一招：shutdown abort了
sql>conn / as sysdba
sql>shutdown abort
几秒的时间，db就停了下来。
调整了OS的时间之后，正常启动db，一切顺利。
启动时，oracle log
Fri May 30 01:53:38 2008
Shutting down instance (abort)
License high water mark = 268
Instance terminated by USER, pid = 19793
Fri May 30 02:40:08 2008
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 1
Fri May 30 02:40:33 2008
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.8.0.
System parameters with non-default values:
processes                = 500
sessions                 = 555
timed_statistics         = TRUE
shared_pool_size         = 536870912
sga_max_size             = 10739491216
large_pool_size          = 117440512
java_pool_size           = 117440512
control_files            = /dev/vgora/rlv_ctrl1, /dev/vgora/rlv_ctrl2, /dev/vgora/rlv_ctrl3
db_block_size            = 8192
db_writer_processes      = 2
db_cache_size            = 8589934592
compatible               = 9.2.0.0.0
db_file_multiblock_read_count= 16
fast_start_mttr_target   = 300
transactions             = 500
undo_management          = AUTO
undo_tablespace          = UNDOTBS1
undo_retention           = 43200
remote_login_passwordfile= EXCLUSIVE
db_domain                =
instance_name            = BIMS
job_queue_processes      = 10
hash_join_enabled        = TRUE
background_dump_dest     = /oracle/admin/BIMS/bdump
user_dump_dest           = /oracle/admin/BIMS/udump
core_dump_dest           = /oracle/admin/BIMS/cdump
open_links               = 8
open_links_per_instance = 8
sort_area_size           = 524288
db_name                  = BIMS
open_cursors             = 300
star_transformation_enabled= FALSE
query_rewrite_enabled    = FALSE
pga_aggregate_target     = 2147483648
aq_tm_processes          = 1
PMON started with pid=2, OS id=20212
DBW0 started with pid=3, OS id=20215
DBW1 started with pid=4, OS id=20218
LGWR started with pid=5, OS id=20220
CKPT started with pid=6, OS id=20224
SMON started with pid=7, OS id=20226
RECO started with pid=8, OS id=20230
CJQ0 started with pid=9, OS id=20232
QMN0 started with pid=10, OS id=20234
Fri May 30 02:40:34 2008
ALTER DATABASE   MOUNT
Fri May 30 02:40:38 2008
Successful mount of redo thread 1, with mount id 2048087522
Fri May 30 02:40:38 2008
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Fri May 30 02:40:38 2008
ALTER DATABASE OPEN
Fri May 30 02:40:39 2008
Beginning crash recovery of 1 threads           --------oracle开始自动对REDO LOG进行恢复
Fri May 30 02:40:39 2008
Started redo scan
Fri May 30 02:40:41 2008
Completed redo scan
116710 redo blocks read, 18790 data blocks need recovery    ------还好，需要恢复的data blocks不是很多
Fri May 30 02:41:06 2008
Started recovery at
Thread 1: logseq 220617, block 95794, scn 0.0
Fri May 30 02:41:06 2008
Recovery of Online Redo Log: Thread 1 Group 3 Seq 220617 Reading mem 0
Mem# 0 errs 0: /dev/vgora/rlv_log3
Fri May 30 02:41:07 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 220618 Reading mem 0
Mem# 0 errs 0: /dev/vgora/rlv_log1
Fri May 30 02:41:10 2008
Completed redo application
Fri May 30 02:41:11 2008
Ended recovery at
Thread 1: logseq 220618, block 82459, scn 4.938028078
18790 data blocks read, 18790 data blocks written, 116710 redo blocks read
Crash recovery completed successfully            ---------完成REDO LOG恢复
Fri May 30 02:41:12 2008
Thread 1 advanced to log sequence 220619
Thread 1 opened at log sequence 220619
Current log# 2 seq# 220619 mem# 0: /dev/vgora/rlv_log2
Successful open of redo thread 1
Fri May 30 02:41:12 2008
SMON: enabling cache recovery
Fri May 30 02:41:12 2008
Successfully onlined Undo Tablespace 1.
Fri May 30 02:41:12 2008
SMON: enabling tx recovery
Fri May 30 02:41:12 2008
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN            -------------数据库成功OPEN
Fri May 30 02:44:30 2008
Thread 1 advanced to log sequence 220620

总结：在生产库中，需要关闭oracle数据库的时候，首先使用的应该是shutdown immediate.
（根据相关的帖子介绍，可以先运行alter system archive all，alter system switch logfile ，alter system checkpoint。
不过不知道花费的时间长不长。那天晚上我手忙脚乱，都没有得试这几个命令）
shutdown immediate会对当前正在运行的事务进行回滚，从而保证数据库的安全关闭。
要是在等待了较长时间，shutdown immediate还是无法关闭数据库，可以使用shutdown abort.
因为shutdown abort不会对事务进行回滚，因此可以迅速的关闭oracle。但是，也是因为没有
对事务进行回滚，启动数据库的时候，oracle会自动对REDO LOG进行recover。
为了减少recover的时间，shutdown abort之前，可以先运行alter system archive all，alter system switch logfile ，alter system checkpoint。
PS：这个数据库的redo log设置得较小，大概200M这样。这大概也是这次可以迅速恢复的原因吧。