{"id":286,"date":"2020-01-24T12:39:28","date_gmt":"2020-01-24T11:39:28","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/?p=286"},"modified":"2020-01-24T12:41:25","modified_gmt":"2020-01-24T11:41:25","slug":"tales-of-oracle-19c-upgrade-ora-24964-alter-session-set-container-error","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2020\/01\/24\/tales-of-oracle-19c-upgrade-ora-24964-alter-session-set-container-error\/","title":{"rendered":"Tales of Oracle 19c upgrade – ORA-24964: ALTER SESSION SET CONTAINER error"},"content":{"rendered":"\n

At my client we have a nice automation tool that can run scripts on several DBs based on their group on Enterprise Manager. <\/p>\n\n\n\n

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<\/strong> below). Everything worked nice.<\/p>\n\n\n\n

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?<\/p>\n\n\n\n

I look at the code, find the query, run it on SQL Developer and all if fine, no errors.<\/p>\n\n\n\n

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 <\/p>\n\n\n\n

ORA-24964: ALTER SESSION SET CONTAINER error<\/pre>\n\n\n\n
Looking around I could find this Metalink note:<\/p>\n\n\n\n
ORA-24964 – Alter Session To      12.2 PDB From 12.1 Client (Doc ID 2494623.1)<\/a><\/p>\n\n\n\n
The keyword on the “Symptoms” is “Upgraded DB”. The code description states:<\/p>\n\n\n\n
Cause:<\/strong> 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<\/a>)<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
oracle@l-master $ sqlplus sys@\\\"l-oem19c_t\/c_oem_t\\\" as sysdba\n\n SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 23 17:06:27 2020\n Enter password:\n  \n Connected to:\n Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\n  \n SQL> select pdb_name from dba_pdbs;\n PDB_NAME\n ------------------------------\n PDB$SEED\n P_OEM_T \n PDB01\n  \n SQL> alter session set container=PDB01;\n Session altered.\n  \n SQL>  alter session set container=CDB$ROOT;\n Session altered.\n  \n SQL> alter session set container=P_OEM_T;\n ERROR:\n ORA-24964: ALTER SESSION SET CONTAINER error <\/pre>\n\n\n\n
When I tried the same but using a 19c client, it works without problem:<\/p>\n\n\n\n
oracle@l-master-19c $ sqlplus sys@\\\"l-oem19c_t\/c_oem_t\\\" as sysdba\n  \n SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 23 17:08:55 2020\n Version 19.3.0.0.0\n  \n Copyright (c) 1982, 2019, Oracle.  All rights reserved.\n  Enter password:\n  \n Connected to:\n Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\n Version 19.4.0.0.0\n  \n SQL> select pdb_name from dba_pdbs;\n  \n PDB_NAME\n ---------------------------------\n PDB$SEED\n P_OEM_T\n PDB01\n  \n SQL> alter session set container=PDB01;\n Session altered.\n  \n SQL>  alter session set container=CDB$ROOT;\n Session altered.\n  \n SQL> alter session set container=P_OEM_T;\n Session altered.<\/pre>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[39],"tags":[],"class_list":{"0":"post-286","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle-19c","7":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/286","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=286"}],"version-history":[{"count":3,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/286\/revisions"}],"predecessor-version":[{"id":289,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/286\/revisions\/289"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=286"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=286"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=286"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}