DBMS_WORKLOAD_CAPTURE: cannot import STS for capture with ID... in the alert.log

This error encountered in the alert log file of  replay system. The replay was being using to assess the consequences of Oracle database upgrade from 12.2 to 19.16 version.

Actually I found this error trying to figure out the absence of SQL tuning set, generated during capture system, on the replay system. It was signaled in the alert.log during executing DBMS_WORKLOAD_CAPTURE.IMPORT_AWR procedure. This led to inability of generating 'compare sqlsets report' after replaying the captured load.

Moving on to searching the cause, I found out the dump wcr_ca_sts.dmp
 file of STS in the <replay_directory>/cap directory. Inspecting it with help if impdp (using parameters SQLFILE and MASTER_ONLY=true), it turned out that dump file contained the only table "SYSTEM"."WRRSTSTAB", which was a stage table for STS from the capture side. And this table existed in the replay database already (created after executing IMPORT_AWR procedure).

Next I tried to unpack stage table and assign STS to different owner :

SQL> begin dbms_sqltune.unpack_stgtab_sqlset (sqlset_name => '%', sqlset_owner => 'NEW_OWNER', replace => false, staging_table_name => 'WRRSTSTAB', staging_schema_owner => 'SYSTEM') ; end ;
/

It generated the error 

ORA-19377: no "SQL Tuning Set" with name like "%" exists for owner like "NEW_OWNER"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_SQLTUNE", line 10526
ORA-06512: at line 1
19377. 00000 -  "no \"SQL Tuning Set\" with name like \"%s\" exists for owner like \"%s\""
*Cause:    The user specified a filter to a pack/unpack function for the SQL
           Tuning Set that targets no STS in the SYS schema or the staging
           table, respectively
*Action:   Provide a different filter after checking the state of the system

I didn't try to change sqlset_name, I changed sqlset_owner instead to owner from the source database (SYS). After that execution of unpack_stgtab_sqlset procedure was succeeded.

At that point the last step was :

SQL> update wrr$_captures
    set sqlset_owner = 'SYS', sqlset_name = 'sqlset_name' where id = <capture_id> ;

I queried dba_workload_replays view to confirm changes from previous update. Then I was able to generate the report comparing performance using SQL tuning sets from capture and replay processes :

set pages 100 lines 30000 serveroutput on long 2000000000 longchunksize 99999 trim on trimspool on termout off head off

var sql_r clob  

spool replay_compare_sqlset_report.html

--alter session set events '19119 trace name context forever, level 0x400000';

declare
r clob ;
a varchar2 (20) ;
begin
 r := 'test string' ;
 a := dbms_workload_replay.compare_sqlset_report (replay_id1 => &1, replay_id2 => null, format => 'HTML', result => :sql_r) ;
--  dbms_output.put_line (r) ;
--  :sql_r := r ;
end ;
/

print :sql_r

spool off

exit

Good Luck !