Category: Oracle

dbms_spm.drop_sql_plan_baseline Fails With ‘ORA-14552: Cannot Perform A DDL, Commit Or Rollback Inside A Query Or DML

SYMPTOM

Drop a sql execution plan baseline with following errors:

SQL> select dbms_spm.drop_sql_plan_baseline(sql_handle=>    -
                       'SQL_89d3699974845bdb') from dual;
select dbms_spm.drop_sql_plan_baseline(sql_handle=>        -
                       'SQL_89d3699974845bdb') from dual
*
ERROR at line 1:
ORA-14552: cannot perform a DDL,commit or rollback inside 
           a query or DML
ORA-06512: at "SYS.DBMS_SPM", line 1210
ORA-06512: at "SYS.DBMS_SPM", line 1200
ORA-06512: at line 1

dbms_spm.drop_sql_plan_baseline updates dictionary tables. So you should not use it in a query.

SOLUTION

SQL> exec :xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>   -
                                   'SQL_89d3699974845bdb');

PL/SQL procedure successfully completed.

SQL> select :xx from dual;

:XX
----------
1

SQL> print :xx

XX
----------
1
Drop all sql execution plan baselines.
SQL> set serveroutput on
SQL> declare
     v_count pls_integer;
     v_sql_handle varchar2(50);
     cursor c1 is
     select sql_handle from dba_sql_plan_baselines
         where sql_handle like 'SQL_%';
     begin
        open c1 ;
        loop
            fetch c1 into v_sql_handle;
            exit when c1%notfound;
            v_count := dbms_spm.drop_sql_plan_baseline( 
                             sql_handle=>v_sql_handle );
           dbms_output.put_line(v_count);
      end loop;
  end;

 /
1
1
..
.
PL/SQL procedure successfully completed.

SQL> select count(*) from dba_sql_plan_baselines;

    COUNT(*)
 ----------
         0

How to Download and Deploy OEM Plug-Ins in Offline Mode

Contents

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

How to Apply Oracle RAC PSU/RU/RUR Patches by Using Fleet Maintenance in Oracle Enterprise Manager Cloud Control

It is a recommended new way to apply patches by using fleet maintenance in  Enterprise Manager (EM) Cloud Control .  Fleet maintenance uses the Out of Place Patching option. Also this can be done only via emcli verb and there is no GUI option available.

This post demonstrates how to apply the latest Release Update DB Apr 2020 Release Update 12.2.0.1.200414  and  OJVM Apr 2020 Release Update 12.2.0.1.200414.

Environment:

RAC Database : RACTESTB
RAC Instances: RACTESTB1 / RACTESTB2
Current ORACLE_HOME:    /u01/app/oracle/product/12.2.0/dbhome_1
Referenced ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_g
REF_TARGET_NAME : OraDB12c_Home1_g
New Out of Place ORACLE_HOME:/u01/app/oracle/product/12.2.0/dbhome_2

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Check and Confirm RU and OJVM Applied into CDB/PDB

Check patches have been applied onto CDB.

SQL> connect / as sysdba
SQL> select PATCH_ID,VERSION,STATUS,ACTION,DESCRIPTION 
       from dba_registry_sqlpatch 
      where DESCRIPTION like '%12.2.0.1.200414%';

  PATCH_ID VERSION   STATUS   ACTION  DESCRIPTION
---------- --------- -------- ------- ------------------------------------------------
  30805580 12.2.0.1 SUCCESS  APPLY    OJVM RELEASE UPDATE 12.2.0.1.200414
  30886680 12.2.0.1 SUCCESS  APPLY    DATABASE APR 2020 RELEASE UPDATE 12.2.0.1.200414

Also confirm patches have been applied onto PDB.

SQL> alter session set container=RACTESTBPDB;

Session altered.

SQL> select PATCH_ID,VERSION,STATUS,ACTION,DESCRIPTION 
from dba_registry_sqlpatch 
where DESCRIPTION like '%12.2.0.1.200414%';

PATCH_ID VERSION STATUS ACTION DESCRIPTION
---------- --------- -------- ------- ------------------------------------------
30805580 12.2.0.1 SUCCESS APPLY OJVM RELEASE UPDATE 12.2.0.1.200414
30886680 12.2.0.1 SUCCESS APPLY DATABASE APR 2020 RELEASE UPDATE 12.2.0.1.200414

Check Cluster Database Configuration Updated with New Home

Oracle targets and cluster database are updated with new ORACLE_HOME accordingly.

[oracle@racnode1 ~]$ srvctl config database -d RACTESTB
Database unique name: RACTESTB
Database name: RACTESTB
Oracle home: /u01/app/oracle/product/12.2.0/dbhome_2
...
..
.

Create Gold Image Fails with “A gold image with the name already exists” from OEM 13c Fleet Maintenance

After creating a gold image failed, and then rerun the command again by using Fleet Maintenance in OEM 13c,  the gold image name cannot be reused with following messages:

[oracle@oemnode1 ~]$emcli db_software_maintenance        \
            -createSoftwareImage                         \
            -input_file="data:/home/oracle/createSoftwareImage.txt"
Verb createSoftwareImage unsuccessful
A gold image with the name[GoldImage_12201_2] already exists.
Please use another name or use deleteImage to delete the existing image.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

OEM 13c Create Software Image Using Fleet Maintenance with “ORA-06502: PL/SQL: numeric or value error: character string buffer too small”

While creating software image by using Fleet Maintenance, it fails with “ORA-06502: PL/SQL: numeric or value error: character string buffer too small”

[oracle@oemnode1 ~]$ emcli db_software_maintenance  \
                     -createSoftwareImage           \
          -input_file="data:/home/oracle/createSoftwareImage.txt"

Check the  emoms.trc :

Given working directory location is : /tmp  Profile Schedule is: 
ExpressionEvaluationException: Below error message was returned 
                       while executing this Computational step.
----------------------------------------------------------
Unable to create component. ORA-06502: PL/SQL: numeric or value 
                       error: character string buffer too small
ORA-06512: at "SYSMAN.EM_GOLD_IMAGE", line 1039
ORA-06512: at "SYSMAN.EM_GOLD_IMAGE", line 1039
ORA-06512: at "SYSMAN.EM_GOLD_IMAGE", line 2314
ORA-06512: at line 1

Also check the SYSMAN.EM_GOLD_IMAGE package code:

LINE TEXT
---- --------------------------------------------------------------
1036 BEGIN
1037 l_text := NULL; 
1038  FOR cur_rec IN (SELECT bugs_fixed FROM mgmt$oh_patch WHERE target_name= p_oh_target_name) LOOP
1039    l_text := l_text || ',' || cur_rec.bugs_fixed;
1040  END LOOP;   
1041  l_text := substr(l_text,2,length(l_text));
1042 END;

Let’s run a query to see how many total characters of column bugs_fixed in table mgmt$oh_patch for source target ORACLE_HOME, here it is OraDB12c_Home1.

SQL> show parameter max_string_size 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
max_string_size                      string      STANDARD


SQL> select sum(length(bugs_fixed)) 
     from sysman.mgmt$oh_patch 
     WHERE target_name ='OraDB12c_Home1';

SUM(LENGTH(BUGS_FIXED))
-----------------------
34315

If the MAX_STRING_SIZE is STANDARD , then the maximum size for VARCHAR2 is 4000 bytes.  That is why variable l_text  in SYSMAN.EM_GOLD_IMAGE package code raises “character string buffer too small” exception.

RESOLUTION

It is a bug, and Oracle support has provided patches in which the  variable  l_text will be defined from varchar2  to clob  to solves this problem.

Download and apply the latest Enterprise Manager DB Plugin Patches.