DBA Hub

📋Steps in this guide1/6

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.

oracle configurationintermediate
by OracleDba
13 views
1

Overview

In this blog we will see how to Change MAX_STRING_SIZE on Physical Standby Environment. - Check current value max_string_size On STANDBY 3. Cancel MRP

Code/Command (click line numbers to comment):

1
2
3
4
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;
SQL> show parameter max_string_size

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;
2

Section 2

On PRIMARY 4. DEFER log_archive_dest_2 SQL> col DEST_NAME for a20 SQL> col ERROR for a10

Code/Command (click line numbers to comment):

1
2
3
SQL> alter database recover managed standby database cancel;

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

Section 3

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

Code/Command (click line numbers to comment):

1
shut immediate;
4

Section 4

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
SQL> startup upgrade;
SQL> select status from v$instance;

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

SQL>

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

System altered.

SQL> SHOW PARAMETER MAX_STRING_SIZE

SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utl32k.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> create table TEST (COMMENTS VARCHAR2(4001));
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>
5

Section 5

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

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>

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

Database altered.

SQL>

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.
6

Section 6

Code/Command (click line numbers to comment):

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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!