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:

  1. Set the parameter MAX_STRING_SIZE to extended
  2. 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)
This entry was posted in Oracle 12c. Bookmark the permalink.