1. SQL> alter system reset "_time_based_rcv_ckpt_target" scope = spfile sid='aaa'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE
2. SQL> alter system reset "_bct_public_dba_buffer_size" sid='aaa' scope=spfile ;
System altered.
3. 😶
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 !
