Jul 21, 2019

ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED

During cloning a database, I found below error:

ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED

--- duplicate terminated with below message

...
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure. 
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 07/20/2019 11:20:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED.
.....

Then, I tried to start, but not able to open the database and same error came.

e.g.,

SQL> startup;
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2932632 bytes
Variable Size            2634023016 bytes
Database Buffers         1644167168 bytes
Redo Buffers               13844480 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED.
Process ID: 85042
Session ID: 762 Serial number: 59546

When I verified Oracle document, then following thing I found.

This parameter was introduced in Oracle 12c. MAX_STRING_SIZE controls the maximum size of string size in Oracle database. Either we can set it to STANDARD or.EXTENDED The default value is STANDARD

MAX_STRING_SIZE = STANDARD means the maximum size of strings is 4000 bytes for VARCHAR2 and NVARCHAR2

MAX_STRING_SIZE = EXTENDED means maximum size can be upto 32767 byte .

We can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. But not from EXTENDED to STANDARD.

With MAX_STRING_SIZE set to STANDARD , if we try to set the length of column more than 4000, then it will throw ORA-00910 error.

So here, I did below fixes to open the database:

Example:

SQL> startup mount;

SQL> alter system set MAX_STRING_SIZE='EXTENDED' scope=spfile;

System altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2932632 bytes
Variable Size            2634023016 bytes
Database Buffers         1644167168 bytes
Redo Buffers               13844480 bytes
Database mounted.
Database opened.
SQL> 


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

CONCLUSION: Once the max_string_size is changed to EXTENDED,We can revert to original STANDARD value.So before changing the value in critical or production database, Please do thorough testing , As you may hit uninvited bugs.

Translate >>