By William Sescu

Sometimes, if you kick off a huge load or transformation job, your Archive Destination might run full, faster than your RMAN backup job cleans up the destination. To avoid an Archiver Stuck in such situations, the “oradebug suspend/resume” can be helpful, or the UNIX kill command. Usually, the “oradebug suspend” and the UNIX kill command work quite well.

Before kicking off you SQL script, get all the information you need from your session.

select s.username as Username, 
       s.machine as Machine, 
	   s.client_info as Client_Info, 
	   s.module as Module, 
	   s.action as Action, 
	   s.sid as SessionID,
	   p.pid as ProcessID,
	   p.spid as "UNIX ProcessID"
from
v$session s, v$process p
where s.sid = sys_context ('userenv','sid')
and s.PADDR = p.ADDR;

USERNAME     MACHINE      CLIENT_INFO  MODULE                           ACTION        SESSIONID  PROCESSID UNIX ProcessID
------------ ------------ ------------ -------------------------------- ------------ ---------- ---------- ------------------------
SYS          oel001                    sqlplus@oel001 (TNS V1-V3)                           148         69 7186

In another SQL session you can now set the PID with setorapid or the Server Process ID with setospid. HINT: When the Oracle multiprocess/multithread feature is enabled, RDBMS processes are mapped to threads running in operating system processes, and the SPID identifier is not unique for RDBMS processes. When the Oracle multiprocess/multithread feature is not enabled on UNIX systems, the SPID identifier is unique for RDBMS processes.

SQL> oradebug setorapid 69
Oracle pid: 69, Unix process pid: 7186, image: oracle@oel001 (TNS V1-V3)
SQL> -- oradebug setospid 7186
SQL> oradebug suspend
Statement processed.

-- Now your session is suspended and any command executed by the suspended session is hanging, even select's
-- SQL> select * from dual;

-- Now you can take your time and clean up the archive destination e.g. by moving all archivelogs
-- to tape and delete those in the archive destination afterwards "RMAN> backup archivelog all delete all input;"
-- After the job is done, resume your operation.

SQL> oradebug resume
Statement processed.

-- Now the "select * from dual" comes back.
SQL> select * from dual;

D
-
X

In case you are running 11.2.0.2, it might happen that you see an ORA-600 after running oradebug suspend. No problem, in those cases we can achieve the same thing with the UNIX kill command as well.

On Linux you would run:

$ kill -sigstop $SPID
$ kill -sigcont $SPID

$ kill -l
 1) SIGHUP       2) SIGINT       3) SIGQUIT      4) SIGILL       5) SIGTRAP
 6) SIGABRT      7) SIGBUS       8) SIGFPE       9) SIGKILL     10) SIGUSR1
11) SIGSEGV     12) SIGUSR2     13) SIGPIPE     14) SIGALRM     15) SIGTERM
16) SIGSTKFLT   17) SIGCHLD     18) SIGCONT     19) SIGSTOP     20) SIGTSTP
21) SIGTTIN     22) SIGTTOU     23) SIGURG      24) SIGXCPU     25) SIGXFSZ
26) SIGVTALRM   27) SIGPROF     28) SIGWINCH    29) SIGIO       30) SIGPWR
31) SIGSYS      34) SIGRTMIN    35) SIGRTMIN+1  36) SIGRTMIN+2  37) SIGRTMIN+3
38) SIGRTMIN+4  39) SIGRTMIN+5  40) SIGRTMIN+6  41) SIGRTMIN+7  42) SIGRTMIN+8
43) SIGRTMIN+9  44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13
48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13 52) SIGRTMAX-12
53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9  56) SIGRTMAX-8  57) SIGRTMAX-7
58) SIGRTMAX-6  59) SIGRTMAX-5  60) SIGRTMAX-4  61) SIGRTMAX-3  62) SIGRTMAX-2
63) SIGRTMAX-1  64) SIGRTMAX

On AIX you would run:

$ kill -17 $SPID
$ kill -19 $SPID

$ kill -l
1) HUP                  14) ALRM                 27) MSG                  40) bad trap             53) bad trap
2) INT                  15) TERM                 28) WINCH                41) bad trap             54) bad trap
3) QUIT                 16) URG                  29) PWR                  42) bad trap             55) bad trap
4) ILL                  17) STOP                 30) USR1                 43) bad trap             56) bad trap
5) TRAP                 18) TSTP                 31) USR2                 44) bad trap             57) bad trap
6) ABRT                 19) CONT                 32) PROF                 45) bad trap             58) RECONFIG
7) EMT                  20) CHLD                 33) DANGER               46) bad trap             59) CPUFAIL
8) FPE                  21) TTIN                 34) VTALRM               47) bad trap             60) GRANT
9) KILL                 22) TTOU                 35) MIGRATE              48) bad trap             61) RETRACT
10) BUS                 23) IO                   36) PRE                  49) bad trap             62) SOUND
11) SEGV                24) XCPU                 37) VIRT                 50) bad trap             63) SAK
12) SYS                 25) XFSZ                 38) ALRM1                51) bad trap
13) PIPE                26) bad trap             39) WAITING              52) bad trap

Be very careful, different UNIX systems have different mappings between the signal number and the signal itself. Make sure you look it up first with “kill -l” to get the correct one. From my point of view, the suspend/resume feature, either with the Oracle oradebug or the UNIX kill command  is very useful.

Cheers,
William