ORA-13757: Can’t drop SQL Tuning Set

The following errors occur when dropping a SQL tunning set:

SQL> exec dbms_sqltune.drop_sqlset('STS_TEST','SYSTEM');
BEGIN dbms_sqltune.drop_sqlset('STS_TEST','SYSTEM'); END;
*
ERROR at line 1:
ORA-13757: "SQL Tuning Set" "STS_TEST" owned by user "SYSTEM" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13226
ORA-06512: at "SYS.DBMS_SQLTUNE", line 4563
ORA-06512: at line 1

OR

On OEM console when you try to remove it, you get the error as follows:

Error
This SQL Tuning Set cannot be deleted because there are advisors tasks dependent on it.

CAUSE

The SQL Tuning Advisor have been created under the STS, and hence it is not allowing to drop the STS.

SOLUTION

1.Check for the STS view references to the sqlset:

SQL> select description, created, owner
    from DBA_SQLSET_REFERENCES
    where sqlset_name = 'STS_TEST';

DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATED           OWNER
----------------- ------------------------------
created by: SQL Tuning Advisor - task: SQL_TUNING_1664512487897
20220930-14:35:51 SYSTEM

2.Drop the dependent SQL tasks under the STS:

SQL>  exec DBMS_SQLTUNE.DROP_TUNING_TASK('SQL_TUNING_1664512487897');

PL/SQL procedure successfully completed.

3. Now you can drop the STS.

SQL> exec dbms_sqltune.drop_sqlset('STS_TEST','SYSTEM');

PL/SQL procedure successfully completed.

RMAN-06429: RCVCAT database is not compatible with this version of RMAN

While trying to register 19c database into RMAN catalog, the following errors occur:

$ rman target / catalog rman/xxxxxxxx@rman

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 28 09:43:02 2022
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=9998108000)
recovery catalog database Password:
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 12.02.00.01. in RCVCAT database is too old

RMAN> register database;

PL/SQL package RMAN.DBMS_RCVCAT version 12.02.00.01. in RCVCAT database is too old
PL/SQL package RMAN.DBMS_RCVCAT version 12.02.00.01. in RCVCAT database is too old
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 09/28/2022 09:43:21
RMAN-06429: RCVCAT database is not compatible with this version of RMAN

SOLUTION

In this situation, The RMAN Oracle database version is 12.1.0.2, the catalog version is 12.02.00.01, and target database to be registered version is 19.10.0.0.0. Since the catalog version must be equal to or greater than the target version, so upgrade catalog is required.

SQL> select * from rman.rcver;

VERSION
---------------
VERSION
------------
12.02.00.01
RMAN> upgrade  catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN>  UPGRADE CATALOG;

recovery catalog upgraded to version 19.10.00.00.00
DBMS_RCVMAN package upgraded to version 19.10.00.00
DBMS_RCVCAT package upgraded to version 19.10.00.00.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
SQL> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 28 11:22:41 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select distinct status from dba_objects where owner='RMAN';

STATUS
-------
VALID

SQL> select * from rman.rcver;

VERSION
---------------
19.10.00.00.00

ORA-39181: Only partial table data may be exported due to fine grain access control

Oracle data pump tries to export some tables with the following errors:

...
..
.
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMAN"."BRL"
. . exported "RMAN"."BRL"                                    0 KB       0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMAN"."RLH"
. . exported "RMAN"."RLH"                                    0 KB       0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMAN"."ROUT"
. . exported "RMAN"."ROUT"                                   0 KB       0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMAN"."BP"
. . exported "RMAN"."BP"                                     0 KB       0 rows
...
..
.

Workaround

Grant “EXEMPT ACCESS POLICY” privilege to data pump user :

SQL> grant EXEMPT ACCESS POLICY to expuser;

Grant succeeded.

How to upgrade DBAAS Cloud Tooling using dbaascli

Logon as root user

$ sudo -s
#

 Check your current version of cloud tooling

#  rpm -qa|grep -i dbaastools
dbaastools_exa-1.0-1+22.2.1.1.0_220713.1149.x86_64

Check whether any cloud tooling updates are available

# sudo dbaascli admin showLatestStackVersion
DBAAS CLI version 22.2.1.1.0
Executing command admin showLatestStackVersion
Job id: 7cf1c287-3617-4d9b-990b-7b2147d1e164
{
  "description" : "DBAAS Tools for Oracle Public Cloud",
  "version" : "22.3.1.0.1_220831.2106",
  "baseVersion" : "22.3.1.0.1"
}
dbaascli execution completed

# dbaascli patch tools list
DBAAS CLI version 22.2.1.1.0
Executing command patch tools list
Checking Current tools on all nodes

exaccnode1: Patchid : 22.2.1.1.0_220713.1149

Available Patches
Patchid : 22.3.1.0.1_220831.2106(LATEST)

Install the latest version of dbaastools by using the command: dbaascli admin updateStack

exaccnode2: Patchid : 22.2.1.1.0_220713.1149

Available Patches
Patchid : 22.3.1.0.1_220831.2106(LATEST)

Install the latest version of dbaastools by using the command: dbaascli admin updateStack

exaccnode3: Patchid : 22.2.1.1.0_220713.1149

Available Patches
Patchid : 22.3.1.0.1_220831.2106(LATEST)

Install the latest version of dbaastools by using the command: dbaascli admin updateStack

exaccnode4: Patchid : 22.2.1.1.0_220713.1149

Available Patches
Patchid : 22.3.1.0.1_220831.2106(LATEST)

Install the latest version of dbaastools by using the command: dbaascli admin updateStack

All Nodes have the same tools version

Upgrading Prechecks

# dbaascli admin updateStack --prechecksOnly --version LATEST
DBAAS CLI version 22.2.1.1.0
Executing command admin updateStack --prechecksOnly --version LATEST
INFO : Review log file => /var/opt/oracle/log/tooling/Update/Update_yyyy-mm-dd_hh24:mi:ss.nnnnnnnnnnnn.log 
============ Starting RPM update operation =========== 
Loading PILOT...
Session ID of the current execution is: 274
Log file location: /var/opt/oracle/log/tooling/Update/pilot_mm-dd_hh24:mi:ss-PM
-----------------
Running Plugin_initialization job
Completed Plugin_initialization job
-----------------
Running Default_value_initialization job
Completed Default_value_initialization job
-----------------
Running Rpm_version_validation job
Completed Rpm_version_validation job
-----------------
Running Rpm_source_validation job
Completed Rpm_source_validation job
-----------------
Running Disk_space_download_validation job
Completed Disk_space_download_validation job
-----------------
Running Rpm_download job
Skipping. Job is detected as not applicable.
-----------------
Running Rpm_validation job
Skipping. Job is detected as not applicable.
-----------------
Running Rpm_disk_space_validation job
Skipping. Job is detected as not applicable.
-----------------
Running Rpm_local_installation job
Skipping. Job is detected as not applicable.
-----------------
Running Rpm_remote_installation job
Skipping. Job is detected as not applicable.
-----------------
Running Installed_rpm_backup job
Skipping. Job is detected as not applicable.
-----------------
Running Cleanup_downloaded_rpm job
Skipping. Job is detected as not applicable.
-----------------
Running Cleanup_legacy_rpms job
Skipping. Job is detected as not applicable

Upgrade Cloud Tooling

# dbaascli admin updateStack --version LATEST
...
..
.

Check your current version of cloud tooling

# rpm -qa --last|egrep -i "dbaastools_exa|dbcs"
dbaastools_exa-1.0-1+22.3.1.0.1_220831.2106.x86_64 Fri 16 Sep 2022 02:14:38 PM AEST
dbcs-agent-update-exacc-22.2.1.1.0-220713.1149.x86_64 Thu 28 Jul 2022 01:25:22 PM AEST
dbcs-agent-exacc-2.8OL7-21.1.1.0.2_210408.1900.x86_64 Wed 07 Jul 2021 08:07:29 PM AEST

Check Available Updates

# dbaascli admin showLatestStackVersion
DBAAS CLI version 22.3.1.0.1
Executing command admin showLatestStackVersion
Job id: 886e630e-f911-425d-9219-936d62e5ab5e
{
  "description" : "DBAAS Tools for Oracle Public Cloud",
  "version" : "22.3.1.0.1_220831.2106",
  "baseVersion" : "22.3.1.0.1"
}
dbaascli execution completed


# dbaascli patch tools list
DBAAS CLI version 22.3.1.0.1
Executing command patch tools list
Checking Current tools on all nodes

exaccnode1: Patchid : 22.3.1.0.1_220831.2106
No applicable tools patches are available

exaccnode2: Patchid : 22.3.1.0.1_220831.2106
No applicable tools patches are available

exaccnode3: Patchid : 22.3.1.0.1_220831.2106
No applicable tools patches are available

exaccnode4: Patchid : 22.3.1.0.1_220831.2106
No applicable tools patches are available

All Nodes have the same tools version

Site StandbyDb returned ORA-16664

The ORA-16664 occur4es in Data Guard log files as following:

...
..
.
Site StandbyDb returned ORA-16664.
...
..
.

Solution

a) Check both alert and data guard logs of primary database

b) Check both alert and data guard logs of standby database with following errors:

ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925

...
..

.
OS Audit file could not be created; failing after 6 retries

...

After cleaned the old audit files, then everything is fine.

DGMGRL> show database verbose "STANDBYDB";

...
..
.
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds (computed 0 seconds ago)
  Apply Lag:               0 seconds (computed 1 second ago)
  Average Apply Rate:      4.38 MByte/s
  Active Apply Rate:       1.11 MByte/s
  Maximum Apply Rate:      46.39 MByte/s
..
..
.
Database Status:
SUCCESS

DGMGRL>