Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor

You can create tuning tasks from the following:

>SQL statement selected by SQL identifier from the cursor cache
>SQL Tuning Set containing multiple statements
>Text of a single SQL statement
>SQL statement selected by SQL identifier from the Automatic Workload Repository.


#Get the snap id using below

select min(snap_id),max(snap_id) from DBA_Hist_SQLStat where SQL_ID ='&SQL_ID';

SET SERVEROUTPUT ON
DECLARE
L_SQL_TUNE_TASK_ID VARCHAR2(100);
BEGIN
L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
BEGIN_SNAP=>30039,
END_SNAP=>30040,
SQL_ID => '03kc2mrhf3xj1',
SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
TIME_LIMIT => 3600,
TASK_NAME => '03kc2mrhf3xj1_task',
DESCRIPTION => 'Tuning task for 03kc2mrhf3xj1');
DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
END;
/

or

SET SERVEROUTPUT ON declare stmt_task VARCHAR2(40); begin stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '03kc2mrhf3xj1'); DBMS_OUTPUT.put_line('task_id: ' || stmt_task ); end; /


#Execute the tuning task.:

Execute dbms_sqltune.Execute_tuning_task (task_name => '03kc2mrhf3xj1_task');


#Check the status of the task using following query:

select status from dba_advisor_log where task_name='03kc2mrhf3xj1_task';


#View the Recommendations

set long 65536 set longchunksize 65536 set linesize 100 select dbms_sqltune.report_tuning_task('03kc2mrhf3xj1_task') from dual;


Reference Doc ID 262687.1

RMAN-06004: Oracle error from recovery catalog database: RMAN-20020: database incarnation not set

Error while taking a backup using catalog database.

One of the reasons for the below error, when you failover the database and performing the backup from the new primary.


RMAN> backup current controlfile;
Starting backup at 19-DEC-2020 23:34:59
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/19/2020 23:34:59
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: Oracle error from recovery catalog database:RMAN-20020: database incarnation not set


RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
22078739750 22078739751 ORCL 1414062372 PARENT 1 19-JUN-2017 15:07:16
22078739750 22196326569 ORCL 1414062372 ORPHAN 141314958309 13-FEB-2019 01:51:05
22078739750 22196311418 ORCL 1414062372 ORPHAN 141342337541 12-FEB-2019 16:12:59
22078739750 22332410992 ORCL 1414062372 ORPHAN 143102602803 05-JUN-2020 12:25:34
22078739750 22389933830 ORCL 1414062372 CURRENT 144028344867 19-DEC-2020 06:38:36


RMAN> unregister database;
database name is "ORCL" and DBID is 1414062372
Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog


RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
22389983696 22389984445 ORCL 1414062372 PARENT 1 19-JUN-2017 15:07:16
22389983696 22389983697 ORCL 1414062372 CURRENT 144028344867 19-DEC-2020 06:38:36


RMAN> backup current controlfile;
Starting backup at 20-DEC-2020 00:06:40
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1060 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1470 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=23 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=437 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=850 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=1264 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=1473 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=27 instance=ORCL1 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 20-DEC-2020 00:06:49
channel ORA_DISK_1: finished piece 1 at 20-DEC-2020 00:06:57
piece handle=/fra1/ORCL/backupset/2020_12_20/o1_mf_ncnnf_TAG20201220T000646__kdnbmzyk_.bkp tag=TAG20201220T000646 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 20-DEC-2020 00:06:57
Starting Control File and SPFILE Autobackup at 20-DEC-2020 00:06:59
piece handle=/fra1/ORCL/controlfile/ora_cfc-1414062372-20201220-00.ctl comment=NONE
Finished Control File and SPFILE Autobackup at 20-DEC-2020 00:07:20

Datafile Shrink

set lines 1000 pages 1000
SELECT '/* '||to_char(CEIL((f.blocks-e.hwm)*(f.bytes/f.blocks)/1024/1024),99999999)||' M */ ' ||
  'alter database datafile '''||file_name||''' resize '||CEIL(e.hwm*(f.bytes/f.blocks)/1024/1024)||'M;' SQL
FROM
  DBA_DATA_FILES f,
  SYS.TS$ t,
  (SELECT ktfbuefno relative_fno,ktfbuesegtsn ts#,
  MAX(ktfbuebno+ktfbueblks) hwm FROM sys.x$ktfbue GROUP BY ktfbuefno,ktfbuesegtsn) e
WHERE
  f.relative_fno=e.relative_fno and t.name=f.tablespace_name and t.ts#=e.ts#
  and f.blocks-e.hwm > 1000
ORDER BY f.blocks-e.hwm DESC
/




set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select
case when autoextensible='YES' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)
||'M after setting autoextensible maxsize higher than current size for file '
|| file_name||' */'
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/