停下库

Stop tsprod1, tsprod2  DB

in 181

lsnrctl stop tsprod1

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

sqlplus / as sysdba

sql>shutdown immediate

 

in 182

lsnrctl stop tsprod2

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

sqlplus / as sysdba

sql>shutdown immediate

 

 ##根据活动的进程和sql_id 进行处理。

set head off feedback off
set linesize 2000

select 'alter system kill session ',''''||trim(t2.sid)||','||trim(t2.serial#)||''';'
from v$session t2
where t2.sql_id ='c9wc9q9wc3y35' and status='ACTIVE'; 

 

 

 

---

 

select 'alter system kill session ',''''||trim(t2.sid)||','||trim(t2.serial#)||''';'
  from v$locked_object t1,
       v$session t2 
 where t1.session_id=t2.sid 
 order by t2.logon_time;

 

##根据用户名 进行kill 的

select 'alter system kill session ',''''||trim(t2.sid)||','||trim(t2.serial#)||''';'
from v$session t2
where username ='DBMONOPR';

 

---

##根据用户名 进行kill 的

 

set head off feedback off

spool kill_sess.sql
select 'alter system kill session ',''''||trim(t2.sid)||','||trim(t2.serial#)||''';'
  from v$session t2
 where username !='SYS';

spool off

 

 

--kill from ospid

 

 

##根据用户名 进行kill 的

 

set linesize 120
set pagesize 120
column spid heading 'OSpid' format a8
column pid heading 'Orapid' format 999999
column sid heading 'Sess id' format 99999
column serial# heading 'Serial#' format 999999
column status heading 'Status' format a8
column pga_alloc_mem heading 'PGA alloc'
column username heading 'oracleuser' format a12
column osuser heading 'OS user' format a12
column program heading 'Program' format a28
SELECT
p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
p.pga_alloc_mem,
s.username,
s.osuser,
s.program
FROM
v$process p,
v$session s
WHERE s.paddr(+) = p.addr
and s.sid in
(select trim(t2.sid)
from v$session t2
where username ='DBMONOPR')

 

kill -9

 

 

###sample 2.

NetBear

https://stackoverflow.com/questions/10277528/connecting-to-oracle-db-from-shell-script-with-sysdba

假设测试环境都是oracle。 可以用如下脚本停库。

su - oracle -c 'sqlplus -s / as sysdba <<EOF
   whenever sqlerror exit sql.sqlcode;
   set echo off 
   set heading off

   shutdown immediate
   startup mount
   alter database noarchivelog;
   alter database open;

   exit;
EOF
'


###sample 3
批量停下数据库，是假设数据库账号都是oracle,并且这台主机是配置了免密登陆，可以访问任意的测试环境基础上。跑的脚本
分2被部分，一份是停库本地文件，需要scp到远端，一份是本地expect 命令的脚本

[root@testora_db]# cat shutdown_ora
su - oracle -c 'sqlplus -s / as sysdba <<EOF
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
shutdown immediate
EOF
'

[root@testora_db]# cat shutdown_db.sh
for ip in `cat ./hosts`
do
#scp shutdown_ora root@$ip:/tmp
expect <<EOF
spawn ssh root@$ip
expect "]#"
send "ps -ef|grep pmon\n"
# send "sh -x /tmp/shutdown_ora\n"
send "exit \n"
expect eof
EOF
done