Sometimes, if you kick off a huge load or transformation job, your Archive Destination might run\u00a0full, faster than your RMAN backup job cleans up the destination. To avoid an Archiver Stuck in such situations, the “oradebug suspend\/resume”\u00a0can be helpful, or the UNIX kill command. Usually, the “oradebug suspend” and the UNIX kill command work quite well.<\/p>\n
Before kicking off you SQL script, get all the information you need from your session.<\/p>\n
select s.username as Username, \n s.machine as Machine, \n\t s.client_info as Client_Info, \n\t s.module as Module, \n\t s.action as Action, \n\t s.sid as SessionID,\n\t p.pid as ProcessID,\n\t p.spid as \"UNIX ProcessID\"\nfrom\nv$session s, v$process p\nwhere s.sid = sys_context ('userenv','sid')\nand s.PADDR = p.ADDR;\n\nUSERNAME MACHINE CLIENT_INFO MODULE ACTION SESSIONID PROCESSID UNIX ProcessID\n------------ ------------ ------------ -------------------------------- ------------ ---------- ---------- ------------------------\nSYS oel001 sqlplus@oel001 (TNS V1-V3) 148 69 7186<\/pre>\nIn another SQL session you can now set the PID with setorapid or the Server Process ID with setospid.\u00a0HINT: When the Oracle multiprocess\/multithread feature is enabled, RDBMS processes are mapped to threads\u00a0running in operating system processes, and the SPID identifier is not unique for RDBMS processes.\u00a0When the Oracle multiprocess\/multithread feature is not enabled on UNIX systems,\u00a0the SPID identifier is unique for RDBMS processes.<\/p>\n
SQL> oradebug setorapid 69\nOracle pid: 69, Unix process pid: 7186, image: oracle@oel001 (TNS V1-V3)\nSQL> -- oradebug setospid 7186\nSQL> oradebug suspend\nStatement processed.\n\n-- Now your session is suspended and any command executed by the suspended session is hanging, even select's\n-- SQL> select * from dual;\n\n-- Now you can take your time and clean up the archive destination e.g. by moving all archivelogs\n-- to tape and delete those in the archive destination afterwards \"RMAN> backup archivelog all delete all input;\"\n-- After the job is done, resume your operation.\n\nSQL> oradebug resume\nStatement processed.\n\n-- Now the \"select * from dual\" comes back.\nSQL> select * from dual;\n\nD\n-\nX<\/pre>\nIn 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.<\/p>\n
On Linux you would run:<\/p>\n
$ kill -sigstop $SPID\n$ kill -sigcont $SPID\n\n$ kill -l\n 1) SIGHUP 2) SIGINT 3) SIGQUIT 4) SIGILL 5) SIGTRAP\n 6) SIGABRT 7) SIGBUS 8) SIGFPE 9) SIGKILL 10) SIGUSR1\n11) SIGSEGV 12) SIGUSR2 13) SIGPIPE 14) SIGALRM 15) SIGTERM\n16) SIGSTKFLT 17) SIGCHLD 18) SIGCONT 19) SIGSTOP 20) SIGTSTP\n21) SIGTTIN 22) SIGTTOU 23) SIGURG 24) SIGXCPU 25) SIGXFSZ\n26) SIGVTALRM 27) SIGPROF 28) SIGWINCH 29) SIGIO 30) SIGPWR\n31) SIGSYS 34) SIGRTMIN 35) SIGRTMIN+1 36) SIGRTMIN+2 37) SIGRTMIN+3\n38) SIGRTMIN+4 39) SIGRTMIN+5 40) SIGRTMIN+6 41) SIGRTMIN+7 42) SIGRTMIN+8\n43) SIGRTMIN+9 44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13\n48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13 52) SIGRTMAX-12\n53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9 56) SIGRTMAX-8 57) SIGRTMAX-7\n58) SIGRTMAX-6 59) SIGRTMAX-5 60) SIGRTMAX-4 61) SIGRTMAX-3 62) SIGRTMAX-2\n63) SIGRTMAX-1 64) SIGRTMAX<\/pre>\nOn AIX you would run:<\/p>\n
$ kill -17 $SPID\n$ kill -19 $SPID\n\n$ kill -l\n1) HUP 14) ALRM 27) MSG 40) bad trap 53) bad trap\n2) INT 15) TERM 28) WINCH 41) bad trap 54) bad trap\n3) QUIT 16) URG 29) PWR 42) bad trap 55) bad trap\n4) ILL 17) STOP 30) USR1 43) bad trap 56) bad trap\n5) TRAP 18) TSTP 31) USR2 44) bad trap 57) bad trap\n6) ABRT 19) CONT 32) PROF 45) bad trap 58) RECONFIG\n7) EMT 20) CHLD 33) DANGER 46) bad trap 59) CPUFAIL\n8) FPE 21) TTIN 34) VTALRM 47) bad trap 60) GRANT\n9) KILL 22) TTOU 35) MIGRATE 48) bad trap 61) RETRACT\n10) BUS 23) IO 36) PRE 49) bad trap 62) SOUND\n11) SEGV 24) XCPU 37) VIRT 50) bad trap 63) SAK\n12) SYS 25) XFSZ 38) ALRM1 51) bad trap\n13) PIPE 26) bad trap 39) WAITING 52) bad trap<\/pre>\nBe 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.\u00a0From my point of view, the suspend\/resume feature, either with the Oracle oradebug or the UNIX kill command \u00a0is very useful.<\/p>\n
Cheers,
\nWilliam<\/p>\n","protected":false},"excerpt":{"rendered":"
By William Sescu Sometimes, if you kick off a huge load or transformation job, your Archive Destination might run\u00a0full, faster than your RMAN backup job cleans up the destination. To avoid an Archiver Stuck in such situations, the “oradebug suspend\/resume”\u00a0can be helpful, or the UNIX kill command. Usually, the “oradebug suspend” and the UNIX kill […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[46,96],"type_dbi":[],"class_list":["post-9067","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-linux-unix","tag-oracle"],"acf":[],"yoast_head":"\n
Oracle - Suspending\/Resuming an Oracle PID with "oradebug suspend\/resume" or the OS kill command - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle - Suspending\/Resuming an Oracle PID with "oradebug suspend\/resume" or the OS kill command\" \/>\n<meta property=\"og:description\" content=\"By William Sescu Sometimes, if you kick off a huge load or transformation job, your Archive Destination might run\u00a0full, faster than your RMAN backup job cleans up the destination. To avoid an Archiver Stuck in such situations, the “oradebug suspend\/resume”\u00a0can be helpful, or the UNIX kill command. Usually, the “oradebug suspend” and the UNIX kill […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-10-10T11:23:04+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle – Suspending\/Resuming an Oracle PID with “oradebug suspend\/resume” or the OS kill command\",\"datePublished\":\"2016-10-10T11:23:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/\"},\"wordCount\":263,\"commentCount\":0,\"keywords\":[\"Linux\/UNIX\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/\",\"name\":\"Oracle - Suspending\/Resuming an Oracle PID with \\\"oradebug suspend\/resume\\\" or the OS kill command - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-10-10T11:23:04+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle – Suspending\/Resuming an Oracle PID with “oradebug suspend\/resume” or the OS kill command\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle - Suspending\/Resuming an Oracle PID with \"oradebug suspend\/resume\" or the OS kill command - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/","og_locale":"en_US","og_type":"article","og_title":"Oracle - Suspending\/Resuming an Oracle PID with \"oradebug suspend\/resume\" or the OS kill command","og_description":"By William Sescu Sometimes, if you kick off a huge load or transformation job, your Archive Destination might run\u00a0full, faster than your RMAN backup job cleans up the destination. To avoid an Archiver Stuck in such situations, the “oradebug suspend\/resume”\u00a0can be helpful, or the UNIX kill command. Usually, the “oradebug suspend” and the UNIX kill […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/","og_site_name":"dbi Blog","article_published_time":"2016-10-10T11:23:04+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle – Suspending\/Resuming an Oracle PID with “oradebug suspend\/resume” or the OS kill command","datePublished":"2016-10-10T11:23:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/"},"wordCount":263,"commentCount":0,"keywords":["Linux\/UNIX","Oracle"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/","name":"Oracle - Suspending\/Resuming an Oracle PID with \"oradebug suspend\/resume\" or the OS kill command - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-10-10T11:23:04+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-suspendingresuming-an-oracle-pid-with-oradebug-suspendresume-or-the-os-kill-command\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle – Suspending\/Resuming an Oracle PID with “oradebug suspend\/resume” or the OS kill command"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9067","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9067"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9067\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9067"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9067"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9067"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9067"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}