ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database

Question

I'm currently testing Oracle 12.1 and I'm trying to extend my data type strings. I have done the following :-

sqlplus /nolog
conn / as sysdba
shutdown imediate;
startup upgrade
alter system set max_string_size=EXTENDED scope=spfile;
@$ORACLE_HOME/rdbms/admin/utl32k.sql
shutdown
startup

I then got the message

ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database

The only doc online I found simply told me to run utl32k.sql - but I've done that and do not know where to go from there. Hopefuly someone can help.

More Info

Todsay I found another web page that said try :-

SQL> @utlrp.sql

This still did not work :-(

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-07-11 06:30:04

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-07-11 06:30:17

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.

...Database user "SYS", database schema "APEX_040200", user# "98" 07:50:32
...Compiled 0 out of 2998 objects considered, 0 failed compilation 07:50:33
...263 packages
...255 package bodies
...453 tables
...11 functions
...16 procedures
...3 sequences
...458 triggers
...1322 indexes
...207 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 07:50:33
...Completed key object existence check 07:50:33
...Setting DBMS Registry 07:50:33
...Setting DBMS Registry Complete 07:50:33
...Exiting validate 07:50:33

PL/SQL procedure successfully completed.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2293928 bytes
Variable Size             562040664 bytes
Database Buffers          272629760 bytes
Redo Buffers                2318336 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: 5878
Session ID: 18 Serial number: 5

Answer 1

this page helped a lot - I hit too the issue on pdb$root.
I wrote a SQL query that I think will help make the process go smoothly. I would run the resulting sql statements individually, not a big cut and paste:

    alter session set container=cdb$root;
    set heading off;
    set linesize 100;
    spool dynamic_pdb_extendable_cols.sql
    set serveroutput on size 999999;
    declare 
    sql_stmt varchar2(100);
    cursor c1 is select  name, open_mode from v$pdbs where name ^= 
      'PDB$SEED' order by name;  
   begin
    dbms_output.put_line('alter system set max_string_size=EXTENDED'||
      ' scope=spfile;');
    dbms_output.put_line('alter session set container = CDB$ROOT;');
    dbms_output.put_line('shutdown immediate;');
    dbms_output.put_line('startup upgrade;');
    dbms_output.put_line('@?/rdbms/admin/utl32k.sql');
    dbms_output.put_line('alter session set container = PDB$SEED;');
    dbms_output.put_line('@?/rdbms/admin/utl32k.sql ;');
    dbms_output.put_line('shutdown immediate;');
    dbms_output.put_line('startup;');

    for c1rec in c1 loop
     dbms_output.put_line('alter session set container = '||c1rec.name|| ';');
     dbms_output.put_line('alter pluggable database '||c1rec.name||
       ' close immediate;');
     dbms_output.put_line('alter pluggable database '||c1rec.name||
      ' open upgrade;');
     dbms_output.put_line('@?/rdbms/admin/utl32k.sql');
     dbms_output.put_line('alter pluggable database '||c1rec.name||
      ' close immediate;');
     dbms_output.put_line('alter pluggable database '||c1rec.name||
      ' open read write;');

    end loop;
    end;
    /
    spool off;

--- tom 

Answer 2

I ran into the same problem first, when discovering, that you only can set MAX_STRING_SIZE in a non-cdb (the standalone form of the database) or in a pluggable. To do this conversion within pdb$seed was somewhat tricky, as the documentation says, it should be in upgrade mode, but you can't 'start' a pluggable database in upgrade mode, because only the container database is started, the pluggables are opened. So I did the following:

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
         3 PDBN1                          MOUNTED

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> alter system set max_string_size = EXTENDED;

System altered.

SQL> @?/rdbms/admin/utl32k.sql

Session altered.
......
...Setting DBMS Registry Complete 09:33:59
...Exiting validate 09:33:59

PL/SQL procedure successfully completed.

SQL>

Alternatively you may create a non-cdb, migrate it and plug it into a cdb. This works without the need to migrate the cdb first.

I don't know, if this is a recommended approach. Be careful: If you clone a database from pdb$seed you will get an error when opening it: the Default for MAX_STRING_SIZE is 'STANDARD' immediately after creation. Do the following:

SQL> create pluggable database pdb1 ADMIN user pdb1asmin identified by password roles=(DBA);

Pluggable database created.

SQL> alter session set container=pdb1;

Session altered.

SQL> alter system set max_string_size = EXTENDED scope=spfile;

System altered.

SQL> alter database open;

Database altered.

I have to get a little more precise: I said, you can't startup a pluggable database in upgrade mode. That's right, but you can open it in upgrade mode:

SQL> alter pluggable database pdb1 open upgrade;

When you have done that, you may set MAX_STRING_SIZE to EXTENDED and run utl32k as explained above