ORA-00600: internal error code, arguments: [kpdbSwitchPreRestore: txn]

Symptoms and cause:

In one of the production databases, there was a query which causes ora-0600 internal error which belongs to AUTO SPACE ADVISOR jobs, whenever the job execute during maintenance window.

Action name comes from ORA$AT_SA_SPC_SY_nnn job name which confirms that the issue is from space advisor. This task is executed from a PDB to the CDB

*** SERVICE NAME:(SYS$USERS) 
*** MODULE NAME:(DBMS_SCHEDULER) 
*** ACTION NAME:(ORA$AT_SA_SPC_SY_365478) 
ORA-00600: internal error code, arguments: [kpdbSwitchPreRestore: txn],
[TOC00003]
----- Current SQL Statement for this session (sql_id=2cytbjtqrry0u) -----
SELECT MAX(CREATION_TIME) FROM SYS.DBA_OUTSTANDING_ALERTS ALT, SYS.TS$ TS WHERE :B1 = TS.TS# AND ALT.OBJECT_NAME = TS.NAME AND REASON LIKE 'Tablespace%'
[TOC00004]

Trace file shows the PL/SQL information related to SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC and also the above sql query fetching the row source from PDB to CDB to get the tablespace information

----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
7000100c24e4421      2182  package body SYS.DBMS_SPACE.ASA_SELECT_TBS
7000100c24e4421      2310  package body SYS.DBMS_SPACE.ASA_SELECT_OBJECTS
7000100c94e4420      2765  package body SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC

Call stack of the error is this

 kpdbSwitchPreRestore <- kpdbcvFetchCbkCon <- kpdbcvFetch <- qerfxFetch <- rwsfcd
   <- qerhnFetch <- qergsFetch <- opifch2 <- opiefn0 <- opipls <- opiodr <- rpidrus <- skgmstack
    <- rpidru <- rpiswu2 <- rpidrv <- psddr0 <- psdnal <- pevm_EXECC <- pfrinstr_EXECC
     <- pfrrun_no_tool <- pfrrun <- plsql_run <- peidxr_run <- peidxexe <- kkxdexe <- kkxmpexe
      <- kgmexwi <- kgmexec <- evapls <- evaopn2 <- kkxmexcs <- opiexe <- kpoal8 <- opiodr <- kpoodr
       <- upirtrc <- kpurcsc <- kpuexec <- OCIStmtExecute <- jslvec_execcb1 <- jslvswu
        <- jslvCDBSwitchUsr <- kpdbSwitch <- jslve_execute0 <- jslve_execute <- kpdbSwitch
         <- jslve_cdb_execute <- rpiswu2 <- kkjex1e_cdb <- kkjsexe <- kkjrdp <- opirip <- opidrv
          <- sou2o <- opimai_real <- ssthrdmain <- main 

Workaround:

This happen to be usually a bug which happened in 12c and earlier versions. But the error occurred in 19c version and hence this should be a new bug. Workaround is to disable the auto space advisor from all the containers.

kIsH@Xhydra<>alter session set container=CDB$ROOT;

Session altered.

kIsH@Xhydra<>EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO SPACE ADVISOR',NULL,NULL);

PL/SQL procedure successfully completed.

kIsH@Xhydra<>select CLIENT_NAME, STATUS from dba_autotask_client;

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

Disable from the PDB’s as well

kIsH@Xhydra<>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBTRNST                       READ WRITE YES
         5 PROX1                          MOUNTED
         9 PROX2                          MOUNTED
kIsH@Xhydra<>alter session set container=PDBTRNST;

Session altered.

kIsH@Xhydra<>EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO SPACE ADVISOR',NULL,NULL);

PL/SQL procedure successfully completed.

kIsH@Xhydra<>select CLIENT_NAME, STATUS from dba_autotask_client;

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

Leave a Reply