DBA Hub

📋Steps in this guide1/23

How to Change MAX_STRING_SIZE on Physical Standby Environment

How to Change MAX_STRING_SIZE on Physical Standby Environment

oracle clusteringintermediate
by OracleDba
12 views
1

Overview

How to Change MAX_STRING_SIZE on Physical Standby Environment On PRIMARY On STANDBY
2

Section 2

On STANDBY On PRIMARY On Primary
3

Section 3

On Primary On STANDBY (OPTIONAL STEPS) 0. Overview
4

Section 4

0. Overview Steps for Standalone Database 1. Environment

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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?

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
5

Section 5

Source: DB Version : Oracle 12.1.0.2.0 Target: DB_UNIQUE_NAME : ABC_DG Oracle Home Path : /u01/app/oracle/product/12.1.0.2/db_1 On PRIMARY
6

Section 6

On PRIMARY 2. Check current value max_string_size 3. Test Case

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>
7

Section 7

create table RAJ (COMMENTS VARCHAR2( 4001 )); ERROR at line 1: ORA-00910: specified length too long for its datatype 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 4. Verify Archive log GAP

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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>
8

Section 8

On STANDBY 5. Cancel MRP — Want to protect my standby database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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>

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>
9

Section 9

select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance; alter database recover managed standby database cancel; On PRIMARY 6. DEFER log_archive_dest_2
10

Section 10

-- Want to protect my standby database, the changes from primary will not transfer to standby, can enable later once Primary looks good. select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance; select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3; ALTER SYSTEM SET log_archive_dest_state_2=DEFER SCOPE=BOTH;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 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>
11

Section 11

<----- 7. Change MAX_STRING_SIZE to EXTENDED 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 7.1 Shutdown Database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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>
12

Section 12

7.2 Start Database in Upgrade Mode 7.3 Change parameter MAX_STRING_SIZE to EXTENDED

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL>
shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

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>
13

Section 13

ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH; 7.4 Run utl32k.sql @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utl32k.sql

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
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>

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>
14

Section 14

7.5 SHUT IMMEDIATE; 7.6 Startup Database;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL>
SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

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>
15

Section 15

7.7 Create table with 40001 bytes On STANDBY 8. Change parameter MAX_STRING_SIZE to EXTENDED

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL>
create table RAJ (COMMENTS VARCHAR2(4001));
Table created. <------
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>
16

Section 16

8. Change parameter MAX_STRING_SIZE to EXTENDED SPFILE 8.1 Shutdown Database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL>
ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED' SCOPE=
SPFILE
;
System altered.

SQL>

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


Database dismounted.
ORACLE instance shut down.
SQL>
17

Section 17

8.2 Startup Nomount 8.3 Mount Standby

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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>

SQL>
alter database mount standby database;
Database altered.

SQL>
18

Section 18

8.4 Enable MRP alter database recover managed standby database disconnect from session; On Primary

Code/Command (click line numbers to comment):

1
2
3
4
5
SQL>
alter database recover managed standby database disconnect from session;
Database altered.

SQL>
19

Section 19

9. Enable DEST_ID 2 ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=BOTH; 10. Verify GAP

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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>
20

Section 20

On STANDBY (Optional Steps) 11. Open the Standby Database in READ-ONLY to see changes

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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>

-- 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>
21

Section 21

READ ONLY WITH APPLY DESC RAJ; 4001 <---- 12. Revert Active Standby (READ-ONLY) to Mount mode
22

Section 22

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. Caution:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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
23

Section 23

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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!