In Oracle Database 19c, the MAX_STRING_SIZE parameter controls the maximum length for VARCHAR2, NVARCHAR2, and RAW columns. By default, it’s set to STANDARD, which limits columns to 4,000 bytes. Setting it to EXTENDED increases the limit to 32,767 bytes—allowing for much larger strings in your database.
If your environment uses Data Guard with a Physical Standby database, you need to follow a specific sequence to make this change safely while keeping your standby in sync.
1. Understanding the Change
– STANDARD → Max 4,000 bytes
– EXTENDED → Max 32,767 bytes
Once you move to EXTENDED, reverting back to STANDARD is not straightforward—it typically requires a database rebuild.
This change updates Oracle’s data dictionary, which must be replicated to your standby via redo logs.
2. Preparation Checklist
Before making the change, verify:
- Current Setting:
SHOW PARAMETER max_string_size;
- ARCHIVELOG Mode Enabled: Required for redo log shipping.
- Primary and Standby in Sync: No lag in redo apply.
- Full Backup Taken: This is a non-reversible metadata change.
3. Step-by-Step Process
Step 1 — Stop Redo Apply on Standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step 2 — Restart Primary in UPGRADE Mode
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
Step 3 — Change Parameter to EXTENDED
ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
Step 4 — Run the UTL32K Script
@$ORACLE_HOME/rdbms/admin/utl32k.sql
Step 5 — Restart Primary in Normal Mode
SHUTDOWN IMMEDIATE;
STARTUP;
Step 6 — Verify on Primary
SHOW PARAMETER max_string_size;
Step 7 — Resume Redo Apply on Standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Step 8 — Confirm Synchronization
SELECT sequence#, applied
FROM v$archived_log
ORDER BY sequence#;
Step 9 — Verify on Standby
SHOW PARAMETER max_string_size;
4. Important Notes
– Do not run utl32k.sql on the standby directly—the changes come from redo logs.
– Standby redo apply must be paused during the upgrade to avoid dictionary corruption.
– If the standby fails to apply redo after the change, you may need to rebuild it from the primary.
Conclusion
Increasing MAX_STRING_SIZE in a Data Guard environment requires careful coordination between the primary and standby databases. By following the correct sequence—pause apply, upgrade primary, resume apply—you can make the change without breaking synchronization. This ensures your standby remains consistent and ready for failover.
