ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=SPFILE; Archives

This parameter MAX_STRING_SIZE was introduced in Oracle 12c. This is fixed in 12.2

In order to expand the maximum value of varchar2 (also nvarchar2 and raw) datatypes in Oracle 12c and beyond, the max_string_size parameter needs to be set to "extended".  This will change the maximum value from 4k (4096) to 32k (32767).

To change max_string_size from the default of "standard" to "extended" you must bounce the database for the parameter to take effect and then run a utility to change all of the old maximum sizes for VARCHAR2 to the new larger values.

WARNING:  You MUST run utl32k.sql immediately after changing max_string_size=extended, else you risk invalidating the database columns.

Steps for Standalone Database

1. shutdown immediate;
2. startup upgrade;
3. ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=BOTH;
4. Run $ORACLE_HOME/rdbms/admin/utl32k.sql
5. startup;

How to Change MAX_STRING_SIZE on Physical Standby Environment?

1. Environment

Source:

	Platform		: Linuxx86_64
	Server Name		: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
	DB Version		: Oracle 12.1.0.2.0
	File system             : Normal
	Database Name	        : ABC
        DB_UNIQUE_NAME          : ABC
	Oracle Home Path        : /u01/app/oracle/product/12.1.0.2/db_1
		
Target:
		
	Platform		: Linuxx86_64
	Server Name		: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
	DB Version		: Oracle 12.1.0.2.0
	File system             : Normal
	Database Name	        : ABC
        DB_UNIQUE_NAME          : ABC_DG
	Oracle Home Path        : /u01/app/oracle/product/12.1.0.2/db_1

On PRIMARY

2. Check current value max_string_size

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
ABC       READ WRITE           PRIMARY          ABC

SQL>

SQL> show parameter max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL>

3. Test Case

SQL> create table RAJA (COMMENTS VARCHAR2(4000)); <--- 

Table created.

SQL> create table RAJ (COMMENTS VARCHAR2(4001)); 
create table RAJ (COMMENTS VARCHAR2(4001))
                                    *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


SQL>


SQL> ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=BOTH;
ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=BOTH
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration


SQL>

4. Verify Archive log GAP

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     27                    27          0 <--


SQL>

On STANDBY

5. Cancel MRP — Want to protect my standby database

SQL>  select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
ABC       MOUNTED              PHYSICAL STANDBY ABC_DG

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>

On PRIMARY

6. DEFER log_archive_dest_2

-- Want to protect my standby database, the changes from primary will not transfer to standby, can enable later once Primary looks good.

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
ABC       READ WRITE           PRIMARY          ABC

SQL>

SQL> col DEST_NAME for a20
SQL> col ERROR for a10
SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3; 

DEST_ID    DEST_NAME             STATUS     ERROR 
---------- -------------------- --------- ---------- 
1          LOG_ARCHIVE_DEST_1    VALID 
2          LOG_ARCHIVE_DEST_2    VALID 

SQL>

SQL> ALTER SYSTEM SET log_archive_dest_state_2=DEFER SCOPE=BOTH;

System altered.

SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3; 
DEST_ID    DEST_NAME            STATUS    ERROR 
---------- -------------------- --------- ---------- 
1          LOG_ARCHIVE_DEST_1   VALID 
2          LOG_ARCHIVE_DEST_2   DEFERRED <-----
SQL>

7. Change MAX_STRING_SIZE to EXTENDED

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
ABC       READ WRITE           PRIMARY          ABC

SQL>

SQL> SHOW PARAMETER MAX_STRING_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD <----
SQL>

SQL> ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH;
ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

SQL>

7.1 Shutdown Database

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

7.2 Start Database in Upgrade Mode

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1140850688 bytes
Fixed Size                  2923584 bytes
Variable Size             922747840 bytes
Database Buffers          201326592 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.
SQL>
SQL> select status from v$instance;

STATUS
------------
OPEN MIGRATE <---

SQL>

7.3 Change parameter MAX_STRING_SIZE to EXTENDED

SQL> ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH;

System altered.

SQL> SHOW PARAMETER MAX_STRING_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED <----
SQL>

7.4 Run utl32k.sql

SQL> @/u01/app/oracle/product/12.1.0.2/db_1/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.

SQL>

7.5 SHUT IMMEDIATE;

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

7.6 Startup Database;

SQL> STARTUP;
ORACLE instance started.

Total System Global Area 1140850688 bytes
Fixed Size                  2923584 bytes
Variable Size             922747840 bytes
Database Buffers          201326592 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select status from v$instance;

STATUS
------------
OPEN <---

SQL>

7.7 Create table with 40001 bytes

SQL> create table RAJ (COMMENTS VARCHAR2(4001));

Table created. <------

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

On STANDBY

8. Change parameter MAX_STRING_SIZE to EXTENDED

SQL> ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED' SCOPE=SPFILE;

System altered.

SQL>

8.1 Shutdown Database

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


Database dismounted.
ORACLE instance shut down.
SQL>

8.2 Startup Nomount

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1140850688 bytes
Fixed Size                  2923584 bytes
Variable Size             838861760 bytes
Database Buffers          285212672 bytes
Redo Buffers               13852672 bytes
SQL>

8.3 Mount Standby

SQL> alter database mount standby database;

Database altered.

SQL>

8.4 Enable MRP

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

On Primary

9. Enable DEST_ID 2

SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3; 
DEST_ID     DEST_NAME            STATUS   ERROR 
---------- -------------------- --------- ---------- 
1          LOG_ARCHIVE_DEST_1    VALID 
2          LOG_ARCHIVE_DEST_2    DEFERRED 
SQL>


SQL> ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=BOTH;

System altered.

SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3; 
DEST_ID     DEST_NAME            STATUS   ERROR 
---------- -------------------- --------- ---------- 
1           LOG_ARCHIVE_DEST_1   VALID 
2           LOG_ARCHIVE_DEST_2   VALID <-----
SQL>

10. Verify GAP

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     33                    33          0 <----

SQL>

On STANDBY (Optional Steps)

11. Open the Standby Database in READ-ONLY to see changes

-- Need Licence in order use Active Standby

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
ABC       READ ONLY WITH APPLY PHYSICAL STANDBY ABC_DG

SQL>

SQL> DESC RAJ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMMENTS                                           VARCHAR2(4001) <----

SQL>

12. Revert Active Standby (READ-ONLY) to Mount mode

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1140850688 bytes
Fixed Size                  2923584 bytes
Variable Size            1023411136 bytes
Database Buffers          100663296 bytes
Redo Buffers               13852672 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
ABC       MOUNTED              PHYSICAL STANDBY ABC_DG

Reference:

http://www.dba-oracle.com/t_max_string_size.htm
https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Bright DBA

ORACLE DBA – Tips and Techniques

Tag Archives: ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=SPFILE;

How to Change MAX_STRING_SIZE on Physical Standby Environment

How to Change MAX_STRING_SIZE on Physical Standby Environment