- 03 Sep, 2023
- 0 Comments
- 3 Mins Read
How to Change MAX_STRING_SIZE on Physical Standby Environment
In this blog we will see how to Change MAX_STRING_SIZE on Physical Standby Environment.
On PRIMARY
- Check current value max_string_size
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance; SQL> show parameter max_string_size
2. 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;
On STANDBY
3. Cancel MRP
SQL> alter database recover managed standby database cancel;
On PRIMARY
4. DEFER log_archive_dest_2
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;
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;
SQL> ALTER SYSTEM SET log_archive_dest_state_2=DEFER SCOPE=BOTH;
SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3;
5. Change MAX_STRING_SIZE to EXTENDED
SQL> SHOW PARAMETER MAX_STRING_SIZE
5.1 Shutdown Database
shut immediate;
Start Database in Upgrade mode :
SQL> startup upgrade; SQL> select status from v$instance; STATUS ------------ OPEN MIGRATE <--- SQL>
5.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
5.4 Run utl32k.sql
SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utl32k.sql
5.5 Shutdown Database :
shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL>
5.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
5.7 Create table with 4001 bytes
SQL> create table TEST (COMMENTS VARCHAR2(4001)); SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL>
On StandBy
6. Change parameter MAX_STRING_SIZE to EXTENDEDSQL> ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED' SCOPE=SPFILE; System altered. SQL>
6.1 Shutdown database
SQL> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL>
6.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>
6.3 Mount Standby
SQL> alter database mount standby database; Database altered. SQL>
6.4 Enable MRP :
SQL> alter database recover managed standby database disconnect from session; Database altered. SQL>
On Primary
7. 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.
8. 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;