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.