Author: dbalifeeasy

ORA-00600: internal error code, arguments: [17287]

ORA-00600 error occurs while startup 19c RAC database in ExaCC:

ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [17287], [0x7FA1EDB80130],
[0x22F3A9C18], [], [GSMADMIN_INTERNAL], [DBMS_GSM_CLOUDADMIN], [11], [1], [],
[], [], []
Process ID: 335484
Session ID: 2850 Serial number: 17388

INCIDENT TRACE FILE with CALL STACK

...
..
.
----- Incident Context Dump -----
Address: 0x7fa1f83fa060
Incident ID: 197141
Problem Key: ORA 600 [ORA-00600: internal error code, arguments: [17287], [0x7FA1EDB80130], [0x22F3A9C18], [], [GSMADMIN_INTERNAL], [DBMS_GS]
Error: ORA-600 [ORA-00600: internal error code, arguments: [17287], [0x7FA1EDB80130], [0x22F3A9C18], [], [GSMADMIN_INTERNAL], [DBMS_GSM_CLOUDADMIN], [11], [1], [], [], [], []
] [] [] [] [] [] [] [] [] [] [] []
[00]: dbgexProcessError [diag_dde]
[01]: dbgePostErrorKGE [diag_dde]
[02]: dbkePostKGE_kgsf [rdbms_dde]
[03]: kgeade []
[04]: kgeseml []
[05]: kgesem []
[06]: OCIKSEC []<-- Signaling
[07]: gwm_refresh_params []
[08]: gwm_init []
[09]: gwm_notifier []
[10]: gwm_pdb_notifier []
[11]: kscdnfy [VOS]
[12]: kpdbaSwitchOpenClose [PROGINT]
[13]: kpdbSwitchRunAsSysCbk [PROGINT]
[14]: rpiswu2 [RPI]
[15]: kpdbSwitch [PROGINT]
[16]: kpdbaOpenPdb [PROGINT]
[17]: kpdbaOpenClose [PROGINT]
[18]: kpdbaSwitchStateFromRoot [PROGINT]
[19]: kpdbRestorePdbStates_int [PROGINT]
[20]: kpdbRestorePdbStates [PROGINT]
[21]: kpdbCDBOpen [PROGINT]
[22]: adbdrv_options []
[23]: opiexe [Time_Limit]
[24]: opiosq0 [OPI]
[25]: kpooprx [PROGINT_MISC]
[26]: kpoal8 [PROGINT_MISC]
[27]: opiodr [OPI]
[28]: ttcpip []
[29]: opitsk [OPI]
[30]: opiino [OPI]
[31]: opiodr [OPI]
[32]: opidrv [OPI]
[33]: sou2o []
[34]: opimai_real [OPI]
[35]: ssthrdmain []
[36]: main []
...
..
.

CAUSE

User has run a couple of SQL scripts to have revoked some privileges from PUBLIC for security compliance purpose. Hence there are many invalid objects for SYS, PUBLIC, GSMADMIN_INTERNAL, etc.

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

How to Create SQL Plan Baseline from AWR

In another post How to Create SQL Plan Baseline from Cursor Cache. Here we will demonstrate how to create SQL plan baseline from AWR.

By using the SQL in Find Oracle SQL Execution History Details, we get the best execution plan for sql_id=’6fqb4wf9sgfyv’ and plan_hash_value= 815656277.

Create STS (  SQL Tuning Set )

 BEGIN  
  DBMS_SQLTUNE.CREATE_SQLSET (  
     sqlset_name  => 'STS_6fqb4wf9sgfyv',  
     description  => 'SQL Tuning Set for 6fqb4wf9sgfyv');  
 END;

Load the Good Plan into STS

DECLARE  
  cur sys_refcursor;  
 BEGIN  
  OPEN cur FOR  
   SELECT VALUE(P)  
   FROM TABLE(   dbms_sqltune.select_workload_repository(begin_snap=>101300,end_snap=>101304,basic_filter=>'sql_id = ''6fqb4wf9sgfyv''',attribute_list=>'ALL')  
        ) p;  
    DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_6fqb4wf9sgfyv', populate_cursor=>cur);  
  CLOSE cur;  
 END;  

  /
PL/SQL procedure successfully completed.

Check the Good SQL Loaded into STS

SQL>  select OWNER,NAME,DESCRIPTION,STATEMENT_COUNT from DBA_SQLSET where OWNER='SYS' and NAME='STS_6fqb4wf9sgfyv';

OWNER                          NAME
------------------------------ ------------------------------
DESCRIPTION                                                                                                                                                                                                                                  STATEMENT_COUNT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------
SYS                            STS_6fqb4wf9sgfyv
SQL Tuning Set for 6fqb4wf9sgfyv                                                                                                                                                                                                                   1

SQL> select * from table(dbms_xplan.display_sqlset('STS_6fqb4wf9sgfyv','6fqb4wf9sgfyv'));
...
..
.

Load the Plan From STS

SQL> DECLARE
 good_plans pls_integer;
 BEGIN
  good_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
   sqlset_name => 'STS_6fqb4wf9sgfyv',
   basic_filter=>'plan_hash_value = ''815656277'''
   );
 END;   

 /

PL/SQL procedure successfully completed.

Check the Plan Loaded Successfully

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,REPRODUCED,AUTOPURGE,OPTIMIZER_COST , EXECUTIONS,ELAPSED_TIME,CPU_TIME from DBA_SQL_PLAN_BASELINES where SQL_TEXT like 'SELECT O.ORDER_ID, O.ORDER_RE%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX REP AUT OPTIMIZER_COST EXECUTIONS ELAPSED_TIME   CPU_TIME
------------------------------ ------------------------------ --- --- --- --- --- -------------- ---------- ------------ ----------
SQL_4e7889c88773e98f           SQL_PLAN_4wy49t23r7ucgbe7808e2 YES YES NO  YES YES         229777          1     61311779   53542413

SQL>  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_4wy49t23r7ucgbe7808e2'));

SQL>  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>'SQL_4e7889c88773e98f'));

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.