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