Oracle DB异常时关闭与启动
1. 生成OS级kill -9脚本
set head off
set feedback off
set pagesize 0
spool /tmp/kill_9_scripts.sh
select ' kill -9 ' || spid from (select spid from v$process where addr in(select paddr from v$session where status='ACTIVE' and username is not null));
spool off
2.生成DB级kill session脚本
set head off
set feedback off
set pagesize 0
spool /tmp/kill_sess_scripts.sql
select 'alter system kill session ' || '''' || sid || ',' || serial# || '''' || ';'from v$session where status='ACTIVE' and username is not null;
spool off
3.执行kill_sess_scripts.sql脚本
SQL>@/tmp/kill_sess_scripts.sql
如果session kill之后不能释放执行,那么在OS级执行kill -9脚本
sh /tmp/kill_9_scripts.sh
4. 如果发现kill session无效无法恢复应用进行如下操作
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
lsnrctl stop listener_xxx
sqlplus “/as sysdba”
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 10
等1分钟
oradebug dump systemstate 10
等1分钟
oradebug dump systemstate 10
Exit
如果数据库处于HANG状态:
sqlplus “/as sysdba”
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
等1分钟
oradebug hanganalyze 3
oradebug dump systemstate 10
等1分钟
oradebug dump systemstate 10
等1分钟
oradebug dump systemstate 10
Exit
该步骤超过15分钟,停止做systemstate dump
再次生成OS级kill -9脚本,KILL所有ORACLE用户进程
再次生成DB级kill session脚本,kill所有ORACLE用户进程
Select group#, status, archived from v$log where status in ('ACTIVE', 'CURRENT');
select RECOVERY_ESTIMATED_IOS,ACTUAL_REDO_BLKS,TARGET_REDO_BLKS,LOG_FILE_SIZE_REDO_BLKS,TARGET_MTtr,ESTIMATED_MTTR from v$Instance_recovery;
如果group处于active 状态的个数超过1个,并且v$instance_recovery.estimated_mttr大于600秒则最好延迟数据库的关闭,等待I/O量下降,
等到v$instance_recovery.estimated_mttr小于v$instance_recovery.target_mttr,否则数据库启动的时间会达到v$instance_recovery.estimated_mttr的时间。
(如果v$instance_recovery.estimated_mttr小于600秒,启动数据库采用方法一,否则采用方法二。)
Alter system checkpoint;
Alter system switch logfile;
Shutdown immediate
等5分钟,关闭还未结束,alert.log中没有相关的关闭信息
shutdown abort
ps -ef|grep LOCAL|grep oracle"$ORACLE_SID"
ps -ef|grep ora_|grep "$ORACLE_SID"
使用kill -9 pid
ipcs –a |grep ORACLE_USER
查找信号灯是否未释放,如果未释放,使用ipcrm清理信号灯:
ipcrm -m shmid
ipcrm -s semid
6 startup mount
7 alter database open
8 检查数据库状态
spool /tmp/sess_startup.log
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sid, serial#, username, machine, program, module, last_call_et,logon_time from v$session;
select s.sid, s.serial#, s.username, s.machine,s.program,sw.event
from v$session_wait sw, v$session s
where sw.sid=s.sid
and sw.event not like 'SQL%'
and sw.event not like 'rdbms%'
and sw.event not like '%timer';
select status from v$instance;
spool off
9 检查当前正在进行的回滚事务
Select ktuxeusn,to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),ktuxesiz,ktuxesta from x$ktuxe Where ktuxecfl='DEAD';
ktuxesiz为0表示事务回滚结束,如果不等于0也不必等待。
10 启动监听
lsnrctl start $ORACLE_SID