Switching a Multitenant Database to Extended Data Types
With Oracle 12c, Oracle introduced “extended data types”, which raise the limit for CHAR and VARCHAR2 datatypes from 4000 bytes to 32767 bytes. However, this is not the default setting and it takes some steps to change this setting. Especially in a Multitenant environment.
Basically, there are two steps:
- Set the parameter MAX_STRING_SIZE to extended
- run the script utl32k.sql
In a multitenant database, this has to be done on CDB$ROOT and on all pluggable databases.
Let’s start with the root container CDB$ROOT:
oracle@training:~/ [TVD12CDB] sqlplus "/ as sysdba" SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 23 20:06:24 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter string_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_string_size string STANDARD
Change the instance parameter and start the instance in upgrade mode: SQL> alter system set max_string_size=EXTENDED scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade; ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 2923920 bytes Variable Size 452985456 bytes Database Buffers 788529152 bytes Redo Buffers 13852672 bytes Database mounted. Database opened.
Run the utl32k.sql script in the root container (CDB$ROOT) to modify the
data dictionary for the extended string size:
SQL> @?/rdbms/admin/utl32k.sql Session altered. DOC>####################################################################### DOC>####################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the database has not been opened for UPGRADE. DOC> DOC> Perform a "SHUTDOWN ABORT" and DOC> restart using UPGRADE. DOC>####################################################################### DOC>####################################################################### DOC># no rows selected DOC>####################################################################### DOC>####################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the database does not have compatible >= 12.0.0 DOC> DOC> Set compatible >= 12.0.0 and retry. DOC>####################################################################### DOC>####################################################################### DOC># PL/SQL procedure successfully completed. Session altered. 0 rows updated. Commit complete. System altered. PL/SQL procedure successfully completed. Commit complete. System altered. Session altered. PL/SQL procedure successfully completed. No errors. Session altered. PL/SQL procedure successfully completed. Commit complete. Package altered. Package altered.
Now we can shutdown the instance and restart in normal mode:
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 2923920 bytes Variable Size 452985456 bytes Database Buffers 788529152 bytes Redo Buffers 13852672 bytes Database mounted. Database opened. SQL> show parameter string_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_string_size string EXTENDED SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Now we are done with the root container.
But when checking the alert.log-file, we will see that there are issues with the
pluggable databases and that have to execute some additional steps on PDB level:
Database Characterset for PDB$SEED is AL32UTF8 Pdb PDB$SEED hit error 14696 during open read only (2) and will be closed. Sun Oct 23 20:09:37 2016 Errors in file /u00/app/oracle/diag/rdbms/tvd12cdb/TVD12CDB/trace/TVD12CDB_ora_5408.trc: ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database PDB$SEED ALTER SYSTEM: Flushing buffer cache inst=0 container=2 local Could not open PDB$SEED error=14696 Sun Oct 23 20:09:37 2016 Errors in file /u00/app/oracle/diag/rdbms/tvd12cdb/TVD12CDB/trace/TVD12CDB_ora_5408.trc: ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database PDB$SEED Sun Oct 23 20:09:37 2016 Database Characterset for CRM03 is AL32UTF8 Sun Oct 23 20:09:37 2016 Database Characterset for CRM02 is AL32UTF8 Sun Oct 23 20:09:37 2016 Database Characterset for CRM01 is AL32UTF8 Pdb CRM01 hit error 14694 during open read write (1) and will be closed. Sun Oct 23 20:09:38 2016 Errors in file /u00/app/oracle/diag/rdbms/tvd12cdb/TVD12CDB/trace/TVD12CDB_p000_5432.trc: ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local Sun Oct 23 20:09:38 2016 Pdb CRM03 hit error 14694 during open read write (1) and will be closed. Sun Oct 23 20:09:38 2016 Errors in file /u00/app/oracle/diag/rdbms/tvd12cdb/TVD12CDB/trace/TVD12CDB_p002_5436.trc: ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration ALTER SYSTEM: Flushing buffer cache inst=0 container=5 local Sun Oct 23 20:09:38 2016 Pdb CRM02 hit error 14694 during open read write (1) and will be closed. Sun Oct 23 20:09:38 2016
I had configured my PDBS (“ALTER PLUGGABLE DATABASE .. SAVE STATE”) that they will be opened automatically after an instance startup. But now the remain in the mount-state:
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 3 CRM01 MOUNTED 4 CRM02 MOUNTED 5 CRM03 MOUNTED Solution: we have to run utl32.sql in each PDB.
For PDB$SEED it’s a little bit tricky, because usually we cannot open the PDB$SEED
in upgrade mode or read write mode.
But there’s the well known “_oracle_script”-trick:
SQL> alter session set "_oracle_script"=true; Session altered. SQL> alter pluggable database pdb$seed open upgrade; Pluggable database altered. SQL> alter session set container=PDB$SEED; Session altered. SQL> @?/rdbms/admin/utl32k Session altered. .. SQL> alter session set container=cdb$root; Session altered. SQL> alter pluggable database pdb$seed close; Pluggable database altered. SQL> alter pluggable database pdb$seed open read only; Pluggable database altered.
Now we can do the same on all the other PDBs:
SQL> alter pluggable database crm01,crm02,crm03 open upgrade; Pluggable database altered. SQL> alter session set container=crm01; Session altered. SQL> @?/rdbms/admin/utl32k .. SQL> alter session set container=crm02; Session altered. SQL> @?/rdbms/admin/utl32k .. SQL> alter session set container=crm03; Session altered. SQL> @?/rdbms/admin/utl32k .. SQL> alter pluggable database crm01,crm02,crm03 close; Pluggable database altered. SQL> alter pluggable database crm01,crm02,crm03 open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 CRM01 READ WRITE NO 4 CRM02 READ WRITE NO 5 CRM03 READ WRITE NO
MOS-Notes:
- Database Won’t Start After Varchar2(32k) Upgrade (Doc ID 1610329.1)
- How to Increase the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in 12C Database using MAX_STRING_SIZE ? (Doc ID 1570297.1)
Ad (Amazon Link):