Pages

Monday, August 8, 2016

Automated SQL Tuning Task Failed with ORA-00600: [kpdbSwitchPreRestore: Txn]

Database: Oracle 12.1.0.2 RAC + Proactive Bundle Patch 12.1.0.2.160419
Operation System:  AIX 7.1


Automated SQL Tuning Task (SYS_AUTO_SQL_TUNING_TASK) failed and alert log shows errors:
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
2016-08-07 22:00:21.020000 -05:00
 <<Message truncated>>
2016-08-07 22:10:53.145000 -05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/trace/CDB01_1_j004_31719682.trc  (incident=109991) (PDBNAME=PDB01):
ORA-00600: internal error code, arguments: [kpdbSwitchPreRestore: txn], [0x7000101ECFA4F00], [0], [2685403136], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/incident/incdir_109991/CDB01_1_j004_31719682_i109991.trc
2016-08-07 22:11:11.467000 -05:00
Dumping diagnostic data in directory=[cdmp_201608071221111], requested by (instance=1, osid=85983625 (J004)), summary=[incident=109991].


The issue was not seen in non-cdb databases. According to Oracle Support explanation, the task is executed from a pluggable database but also accessing the container database through dblink to tune a remote query which triggers a bug. The bug can be fixed by patch 21283337 if it is available for your platform.

Before the patch is available or the Automated SQL Tuning Task is not really needed, as a workaround, the task (client_name is 'sql tuning advisor' in view dba_autotask_client) can be disabled,
system@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               ENABLED

system@PDB01> select client_name,operation_name,status from dba_autotask_operation;

CLIENT_NAME                              OPERATION_NAME                           STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection          auto optimizer stats job                 ENABLED
auto space advisor                       auto space advisor job                   ENABLED
sql tuning advisor                       automatic sql tuning task                ENABLED

system@PDB01> begin
  2  dbms_auto_task_admin.disable(client_name=>'SQL TUNING ADVISOR',
  3                               operation=>null,
  4                               window_name=>null);
  5  end;
  6  /

PL/SQL procedure successfully completed.

system@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
sql tuning advisor                                               DISABLED
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED

system@PDB01> select client_name,operation_name,status from dba_autotask_operation;

CLIENT_NAME                              OPERATION_NAME                           STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection          auto optimizer stats job                 ENABLED
auto space advisor                       auto space advisor job                   ENABLED
sql tuning advisor                       automatic sql tuning task                DISABLED


Usage notes for dbms_auto_task_admin.disable,

 * If operation and window_name are both NULL, the client is disabled.
 * If operation is not NULL, window_name is ignored and the operation is disabled
 * If operation is NULL and window_name is not NULL, the client is disabled in the specified window.

Note: If disabling the task as workaround, we have to disable it in all containers (PDB & root),

SYSTEM@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               DISABLED

SYSTEM@PDB01> alter session set container=cdb$root;

Session altered.

SYSTEM@CDB$ROOT> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               ENABLED

SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;

    CON_ID CLIENT_NAME                                                      STATUS 
---------- ---------------------------------------------------------------- --------

         1 auto optimizer stats collection                                  ENABLED
         1 auto space advisor                                               ENABLED
         1 sql tuning advisor                                               ENABLED
         3 auto optimizer stats collection                                  ENABLED
         3 auto space advisor                                               ENABLED
         3 sql tuning advisor                                               DISABLED

6 rows selected.

SYSTEM@CDB$ROOT> exec dbms_auto_task_admin.disable('SQL TUNING ADVISOR',null,null);

PL/SQL procedure successfully completed.

SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;

    CON_ID CLIENT_NAME                                                      STATUS  
---------- ---------------------------------------------------------------- --------

         1 auto optimizer stats collection                                  ENABLED
         1 auto space advisor                                               ENABLED
         1 sql tuning advisor                                               DISABLED
         3 auto optimizer stats collection                                  ENABLED
         3 auto space advisor                                               ENABLED
         3 sql tuning advisor                                               DISABLED

6 rows selected.

No comments: