Alter Session And System Parameter In Both CDB and PDB (Doc ID 2673509.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• ORA-60 error raised after alter session and system parameter in both CDB and PDB as following:
  
  
  SQL> conn / as sysdba
  Connected.
  SQL> shu immediate
  Database closed.
  Database dismounted.
  ORACLE instance shut down.
  SQL>
  SQL> startup
  ORACLE instance started.
  
  Total System Global Area 1543500832 bytes
  Fixed Size                  9135136 bytes
  Variable Size             452984832 bytes
  Database Buffers         1073741824 bytes
  Redo Buffers                7639040 bytes
  Database mounted.
  Database opened.
  SQL> alter pluggable database all open;
  
  Pluggable database altered.
  
  SQL> show pdbs
  
      CON_ID CON_NAME                       OPEN MODE  RESTRICTED
  ---------- ------------------------------ ---------- ----------
           2 PDB$SEED                       READ ONLY  NO
           3 PDB1                           READ WRITE NO
  SQL> select sid, serial# from v$session where sid=(select userenv('sid') from dual);
  
         SID    SERIAL#
  ---------- ----------
           1      52344
  
  SQL> alter session set parallel_max_servers=128 container=all SCOPE=BOTH;
  alter session set parallel_max_servers=128 container=all SCOPE=BOTH
                    *
  ERROR at line 1:
  ORA-02096: specified initialization parameter is not modifiable with this
  option
  
  
  SQL> select type,sid,id1,id2 from v$lock where type='TX';
  
  no rows selected
  
  SQL> alter system set parallel_max_servers=128 container=all SCOPE=BOTH;
  
  System altered.
  
  SQL> select type,sid,id1,id2 from v$lock where type='TX';
  
  TY        SID        ID1        ID2
  -- ---------- ---------- ----------
  TX          1     720922        898
  
  SQL> alter session set container=pdb1;
  
  Session altered.
  
  SQL> select sid, serial# from v$session where sid=(select userenv('sid') from dual);
  
         SID    SERIAL#
  ---------- ----------
           1      52344
  
  SQL> select type,sid,id1,id2 from v$lock where type='TX';
  
  no rows selected
  
  SQL> ALTER SYSTEM SET parallel_max_servers=96 SCOPE=both;
  ALTER SYSTEM SET parallel_max_servers=96 SCOPE=both
  *
  ERROR at line 1:
  ORA-00604: error occurred at recursive SQL level 1
  ORA-00060: deadlock detected while waiting for resource
  
  
  SQL>
  
  
  
• From the trace file of ORA-60, self deadlock happened when update pdb_spfile$ recusively.
  
  
  Deadlock graph:
                                            ------------Blocker(s)-----------  ------------Waiter(s)------------
  Resource Name                             process session holds waits serial  process session holds waits serial
  TX-000A0005-00002173-00000001-00000000         88     459     X        12153      88     459           X  12153
  
  ----- Current SQL Statement for this session (sql_id=2y7cqwmwf43jz) -----
  update pdb_spfile$ set value$=:5, comment$=:6,                         spare2=(decode(bitand(:8,1), 1, :8 - 1, :8)),                          spare1=(case WHEN spare1 < :7 THEN :7 ELSE spare1 END)                 where name=:1 and pdb_uid=:2 and db_uniq_name=:3 and sid=:4
  
        -------------------------------------------------------------------------
         Autonomous transaction frames:
  
  2020-05-09 10:13:40.239*:ksq.c@10787:ksqdmc(): Enqueue Dump      (enqueue) TX-000A0005-00002173-00000001-00000000  DID: ksqlkdid: 0001-0058-00000006
  
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x16
        mode: X, lock_flag: 0x20, lock: 0x42cffa3b8, res: 0x4128d9be8
        own: 0x3d10356d0, sess: 0x3d10356d0, proc: 0x3e0dea390, prv: 0x4128d9bf8
        SGA version=(1,0)
  
  2020-05-09 10:13:40.291*:ksq.c@10787:ksqdmc(): Enqueue Dump        (enqueue) TX-000A0005-00002173-00000001-00000000  DID: ksqlkdid: 0001-0058-00000006
  
          lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x16
          req: X, lock_flag: 0x30, lock: 0x3d2873808, res: 0x4128d9be8
          own: 0x3d10356d0, sess: 0x3d10356d0, proc: 0x3e0dea390, prv: 0x4128d9c08
          SGA version=(1,0)
• Call stack is following:
  
  
       ksedst1 <- ksedst <- dbkedDefDump <- ksedmp <- ksqdld_hdr_dump
          <- 900 <- ksqcmi <- ksqgtlctx <- ksqgelctx <- ktuGetTxForXid
           <- ktcwit1 <- kdddgb <- kdusru <- updrowFastPath <- qerupUpdRow
            <- qerupFetch <- updaul <- updThreePhaseExe <- 340 <- updexe
             <- opiexe <- opiall0 <- opikpr <- opiodr <- rpidrus
              <- skgmstack <- rpidru <- rpiswu2 <- kprball <- ksp_pdb_spfile_set_
               <- swcbk <- kpdbSwitch <- swcbk <- ksp_pdb_spfile_set <- kspcsetsp
                <- kspsetsp <- ksp_set_update_spfile <- kspset1 <- kkyasy_options <- 199
                 <- kkyasy <- kksExecuteCommand <- opiexe <- kpoal8 <- opiodr
                  <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv
                   <- sou2o <- opimai_real <- ssthrdmain <- main <- libc_start_main
                    <- start

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.