Category Archives: RDBMS

Like me, in the future or for testing, you might need a database with varchar2(32k) support. Although most is explained, the current documentation is a bit incomplete regarding how to get a pluggable database environment to support varchar2(32k). Regarding CDB, PDB environments, to get this done, the following is described in the Oracle Documentation for 12.1 (although I now filled an documentation enhancement request, so this may change)…

From the “Oracle Database Reference” Guide for version 12.1:

To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a PDB:

1. Shut down the PDB
2. Reopen the PDB in migrate mode.

Note:
The following SQL statement can be used to reopen a PDB in migrate mode when the current container is the PDB:

3. ALTER PLUGGABLE DATABASE pdb-name OPEN UPGRADE;
4. Change the setting of MAX_STRING_SIZE in the PDB to EXTENDED.
5. Run the rdbms/admin/utl32k.sql script in the PDB. You must be connected AS SYSDBA to run the utl32k.sql script
6. Reopen the PDB in NORMAL mode.

Note:

The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required. The script does not increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns in some views because of the way the SQL for those views is written.

After migrating the CDB…

All good and well, but after you start of with the CDB via the “regular” CDB migrate method, you will be stuck due to the fact that the following error will occur during startup of the CDB, before you can start upgrading/migrating the PDB’s…

SQL> startup
ORACLE instance started.
 
Total System Global Area 1636814848 bytes
Fixed SIZE 2288968 bytes
Variable SIZE 973079224 bytes
DATABASE Buffers 654311424 bytes
Redo Buffers 7135232 bytes
DATABASE mounted.
 
ORA-01092: ORACLE instance TERMINATED. Disconnection forced
ORA-14696: MAX_STRING_SIZE migration IS incomplete FOR pluggable DATABASE
PDB$SEED
Process ID: 2845
SESSION ID: 1 Serial NUMBER: 5

So the CDB has been successfully migrated, but the PDB$SEED (and/or PDB’s) not yet…

Migrating PDB$SEED

You will have to do the following to migrate the PDB$SEED as well before you can start the CDB in a normal expected way. Be very aware “how” you connect, and therefor in which section you actually are, so CDB, PDB$SEED or one of you PDB’s.

SQL> conn / AS sysdba
SQL> startup mount
SQL> ALTER DATABASE OPEN migrate;
 
DATABASE altered.
 
SQL> SELECT con_id, name, open_mode FROM v$pdbs;
 
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED MIGRATE
 
SQL> ALTER SESSION SET container=PDB$SEED;
 
SESSION altered.
 
SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') AS "Container" FROM dual;
 
Container
------------------------
PDB$SEED
 
SQL> ALTER system SET max_string_size = EXTENDED;
 
System altered.
 
SQL> @?/rdbms/admin/utl32k.SQL
 
PL/SQL PROCEDURE successfully completed.

Remaining PDB’s

In principal, you could now shutdown your pluggable database environment and startup, via a CDB connection, the base pluggable database environment. You will notice very quickly that you won’t get an error now via startup, but your pluggable database environments will give you an ORA-14696 error nevertheless.

This is, of course, due to the fact that the CDB and PDB$SEED environment now support the varchar2(32k) option, but your pluggable databases not yet. The following will upgrade your pluggable databases. Notice the small sequential difference in steps (switch to PDB first and then migrate).

Upgrade the remaining PDB’s via finding the names and follow the following procedure for EVERY PDB (if so needed). Otherwise PDB’s can not take part (started) in the pluggable database environment and must be unplugged or kept in mounted state…

SQL> conn / AS sysdba
SQL> startup mount
 
SQL> SELECT con_id, name, open_mode FROM v$pdbs;
 
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
4 PDB01 MOUNT
 
SQL> ALTER SESSION SET container=PDB01;
 
SESSION altered.
 
SQL> ALTER DATABASE OPEN migrate;
 
DATABASE altered.
 
SQL> SELECT con_id, name, open_mode FROM v$pdbs;
 
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
4 PDB01 MIGRATE
 
SQL> ALTER system SET max_string_size = EXTENDED;
 
System altered.
 
SQL> @?/rdbms/admin/utl32k.SQL
 
SQL> conn / AS sysdba
-- (or alter session set container=PDB$ROOT) etc
 
SQL> shutdown immediate
SQL> startup

You will have to repeat these last steps for every PDB in you environment. It sounds plausible (first the “alter session set container=…” and then the “alter database open migrate”) now you know it, but at first it got me spooked (not realizing the exact sequence and all the error messages the environment will return to you).

The enhancement request I filed is not only for the missing pieces in the documentation, but also a request for an enhancement of the migrate script. In my mind set, while migrating a pluggable database environment, at least the CDB and PDB$SEED environments should be migrated in one go, via the script ($ORACLE_HOME/rdbms/admin/utl32k.sql).

In principal you could migrate all the available PDB’s as well via the script, immediately, but I am hesitating if this always the case in a customer environment and therefor this choice should be, might be, for the customer (DBA) to decide. Then again, on the other hand, after migrating CDB/PDB$SEED, the remaining PDB’s are unusable if not migrated as well and/or must be unplugged. Or the whole pluggable database environment supports varchar2(32k) or none. There is no in between situation.

A documentation note was created called “Database Won’t Start After Varchar2(32k) Upgrade (Doc ID 1610329.1)” nn http://support.oracle.com to solve similar issues more quickly in the future.

In all: HTH.