Snapshot Delete Worker Processes Do Not Complete - Hangs (Doc ID 356230.1)

Last updated on SEPTEMBER 05, 2024

Applies to:

Symptoms

While running an ASCP plan, the MRP Snapshot Workers and MRP Snapshot Delete Workers were hanging. When checking the setups of the Standard Manager (20) and the MRP: Snapshot Worker profile options (5), everything was setup correctly. We found that the statement that was hanging was:

declare
ret_code number := 0;
snp_exception exception;
snp_timed_out exception;

begin
ret_code := db
ms_pipe.receive_message(:ipname,10);

if ret_code = 1 then
raise snp_timed_out;
end if;

if ret_code <> 0 then
raise snp_exception;
end if;

dbms_pipe.unpack_message(:opname);
dbms_pipe.unpack_message(:request_id);
dbms_pipe.unpack_message(:msg_type);
dbms_pipe.unpack_message(:file_type);

if dbms_pipe.next_item_type = 9 then
dbms_pipe.unpack_message(:DAT);
dbms_pipe.unpack_message(:CTL);
dbms_pipe.unpack_message(:DIS);
dbms_pipe.unpack_message(:BAD);
end if;

exception
when snp_timed_out then
:time_out :=1;
when others then
:err_msg := '( ' || to_char (ret_code) || ') '|| sqlerrm;
:err_code := sqlcode;

end;

Using <note 186472.1>, I was able to verify the issue by running the following SQL statement
multiple times while the workers were running...

SQL> select /*+ ORDERED */ sql_text
2 from v$session s, v$sqltext t
3 where t.address = s.sql_address
4 and s.sid = 189 --&1
5 order by piece
6 /
Enter value for 1: 189

The output returned was the same SQL statement identified in the issue verification.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.