--查询备份 set line222 set pagesize100 col status for a10 col input_type for a20 col INPUT_BYTES_DISPLAY for a10 col OUTPUT_BYTES_DISPLAY for a10 col TIME_TAKEN_DISPLAY for a10
select input_type, status, to_char(start_time, 'yyyy-mm-dd hh24:mi:ss'), to_char(end_time, 'yyyy-mm-dd hh24:mi:ss'), input_bytes_display, output_bytes_display, time_taken_display, COMPRESSION_RATIO from v$rman_backup_job_details where start_time >date'2021-08-10' orderby3desc;
set linesize 260 pagesize 10000 column sess format a21 heading "SESSION" column program format a18 column clnt_pid format a8 column machine format a25 column username format a12 column osuser format a13 column event format a32 column waitsec format 999999 column start_time format a18 column sql_id format a15 column clnt_user format a10 column svr_ospid format a10
ALTER SESSION SET NLS_DATE_FORMAT ='yyyy/mm/dd hh24:mi:ss';
set feedback off set echo off
set head off select chr(9) from dual; select'Waiting Transactions'||chr(10)||'===================='from dual; set head on select/*+ rule */ lpad(nvl(s.username,' '),8)||'('||s.sid||','||s.serial#||')'as sess, p.spid as svr_ospid, nvl(osuser,' ') as clnt_user, s.process as clnt_pid, substr((case instr(s.PROGRAM, '@') when0then s.program else case instr(s.PROGRAM, '(TNS V1-V3)') when0then substr(s.program, 1, instr(s.PROGRAM, '@') -1) || substr(s.program, instr(s.PROGRAM, '(') -1) else substr(s.program, 1, instr(s.PROGRAM, '@') -1) end end), 1, 18) as program, (case when length(s.MACHINE) >8then substr(s.machine,1,8)||'~' else s.machine end ) ||'('||nvl(s.client_info, 'Unknown IP')||')'as machine, s.sql_id, substr(s.event, 1, 32) as event, s.seconds_in_wait as waitsec from v$transaction t,v$session s,v$process p where t.ses_addr=s.saddr and s.paddr=p.addr orderby s.seconds_in_wait, s.program, s.machine;
可以通过 SESSION 字段来杀掉事务：
1
altersystem kill session '1841,44697';
如果杀不掉，则使用 svr_ospid 系统层进行 kill：
1
kill -9 27353
确认没有长事务后，继续下一步操作。
源端获取 scn 号
1 2
col current_scn format 9999999999999999 select current_scn from v$database;
#!/bin/bash if [ -f ~/.bash_profile ]; then . ~/.bash_profile fi count=`ps -ef|grep sp_cop|wc -l` if [ "${count}" -ne 2 ]; then ps -ef|grep sp_cop > /tmp/sp_cop.log mail -s "Shareplex sp_cop process shutdown" 邮箱地址 < /tmp/sp_cop.log fi
2、监控 shareplex 队列是否存在异常：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
#!/bin/bash if [ -f ~/.bash_profile ]; then . ~/.bash_profile fi rm -rf /data/quest/error.log echo"show "|sp_ctrl|grep "Idle" >> /data/quest/error.log echo"show "|sp_ctrl|grep "Stopped" >> /data/quest/error.log
# -s 文件大小非0时为真 if [ ! -s /data/quest/error.log ] then rm -rf /data/quest/error.log #文件大小为0 删除 fi
if [ -s /data/quest/error.log ] then mail -s "Shareplex error" 邮箱地址 < /data/quest/error.log fi
