Daily Archives: 24.01.2020


Tales of Oracle 19c upgrade – ORA-24964: ALTER SESSION SET CONTAINER error

At my client we have a nice automation tool that can run scripts on several DBs based on their group on Enterprise Manager.

Last week I’ve migrated+upgraded OEM repository from 12.2 to 19c, using Refreshable PDB to copy the PDB from a 12.2 CDB to a 19c CDB + running dbupgrade script on the PDB (p_oem_t below). Everything worked nice.

My colleague yesterday tells me the automation tool is not working, giving “ORA-0942 Table or view does exist error” for a query on the OEM repository. This means to me, it can connect to the new repository, as the old one is shutdown. But where can the problem be?

I look at the code, find the query, run it on SQL Developer and all if fine, no errors.

This until I activate the debug modus of the automation tool and I see that it connects to the CDB as SYSDBA and then changes container to the right PDB. Just above the ORA-0942 error, there was a

ORA-24964: ALTER SESSION SET CONTAINER error

Looking around I could find this Metalink note:

ORA-24964 – Alter Session To 12.2 PDB From 12.1 Client (Doc ID 2494623.1)

The keyword on the “Symptoms” is “Upgraded DB”. The code description states:

Cause: An attempt to switch to a PDB with different settings such as character set, time zone or time zone file version on an Oracle 12c Release 1 (12.1) or earlier client failed. (see Docs)

In fact, our automation tool uses an old 11.2 client. To test it, I’ve created on the same 19c CDB also new fresh PDB (PDB01) and tried to connect remotely:

oracle@l-master $ sqlplus sys@\"l-oem19c_t/c_oem_t\" as sysdba

 SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 23 17:06:27 2020
 Enter password:
  
 Connected to:
 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  
 SQL> select pdb_name from dba_pdbs;
 PDB_NAME
 ------------------------------
 PDB$SEED
 P_OEM_T 
 PDB01
  
 SQL> alter session set container=PDB01;
 Session altered.
  
 SQL>  alter session set container=CDB$ROOT;
 Session altered.
  
 SQL> alter session set container=P_OEM_T;
 ERROR:
 ORA-24964: ALTER SESSION SET CONTAINER error 

When I tried the same but using a 19c client, it works without problem:

oracle@l-master-19c $ sqlplus sys@\"l-oem19c_t/c_oem_t\" as sysdba
  
 SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 23 17:08:55 2020
 Version 19.3.0.0.0
  
 Copyright (c) 1982, 2019, Oracle.  All rights reserved.
  Enter password:
  
 Connected to:
 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Version 19.4.0.0.0
  
 SQL> select pdb_name from dba_pdbs;
  
 PDB_NAME
 ---------------------------------
 PDB$SEED
 P_OEM_T
 PDB01
  
 SQL> alter session set container=PDB01;
 Session altered.
  
 SQL>  alter session set container=CDB$ROOT;
 Session altered.
  
 SQL> alter session set container=P_OEM_T;
 Session altered.