- 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
1 2 | 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
1 | 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
1 | SQL> alter database recover managed standby database cancel; |
On PRIMARY
4. DEFER log_archive_dest_2
1 | 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
1 | shut immediate; |
Start Database in Upgrade mode :
1 2 3 4 5 6 7 8 | SQL> startup upgrade;SQL> select status from v$instance;STATUS------------OPEN MIGRATE <---SQL> |
5.3 Change parameter MAX_STRING_SIZE to EXTENDED
1 2 3 4 5 | SQL> ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH;System altered.SQL> SHOW PARAMETER MAX_STRING_SIZE |
5.4 Run utl32k.sql
1 | SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utl32k.sql |
5.5 Shutdown Database :
1 2 3 4 5 | shut immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> |
5.6 Startup Database;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> STARTUP;ORACLE instance started.Total System Global Area 1140850688 bytesFixed Size 2923584 bytesVariable Size 922747840 bytesDatabase Buffers 201326592 bytesRedo Buffers 13852672 bytesDatabase mounted.Database opened.SQL>SQL>SQL> select status from v$instance;STATUS------------OPEN |
5.7 Create table with 4001 bytes
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 EXTENDED1 2 3 4 5 | SQL> ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED' SCOPE=SPFILE;System altered.SQL> |
6.1 Shutdown database
1 2 3 4 5 6 7 | SQL> shut immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> |
6.2 Startup Nomount
1 2 3 4 5 6 7 8 9 | SQL> startup nomount;ORACLE instance started.Total System Global Area 1140850688 bytesFixed Size 2923584 bytesVariable Size 838861760 bytesDatabase Buffers 285212672 bytesRedo Buffers 13852672 bytesSQL> |
6.3 Mount Standby
1 2 3 4 5 | SQL> alter database mount standby database;Database altered.SQL> |
6.4 Enable MRP :
1 2 3 4 5 | SQL> alter database recover managed standby database disconnect from session;Database altered.SQL> |
On Primary
7. Enable DEST_ID 2
1 2 3 4 5 6 7 8 9 10 11 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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; |