Principal Performance Engineering, Database, SQL, PLSQL and Fusion Middleware Learnings: How to change max_string_size in oracle RAC container database and pluggable databases

Change max_string_size in oracle RAC container database and pluggable databases:

Steps:

1: create a pfile from spfile.

2. set max_string_size=EXTENDED

3. set cluster_database=FALSE

4. startup upgrade

5. @?/rdbms/admin/utl32k.sql - run it in CDB.

6. set cluster_database=TRUE

7. Restart CDB with srvctl commands and ensure all the instances are up and running.

8. Now we are done with the root container.

9. run utl32.sql in each PDB exactly the way we ran in CDB. please note PDB has to be closed and need to open in upgrade mode.

10. Steps are given below, see the commands and their output carefully.

bash-4.2$ sqlplus "/ as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 20 20:09:13 2020

Version 19.8.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL>

SQL> show parameter string_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string STANDARD

SQL> alter system set max_string_size=EXTENDED scope=spfile sid='*';

System altered.

SQL> exit

srvctl stop database -d PRODEXA1 -o immediate

bash-4.2$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 20 20:18:41 2020

Version 19.8.0.0.0

Enter password:

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 8.1068E+10 bytes

Fixed Size 30385984 bytes

Variable Size 1.1006E+10 bytes

Database Buffers 6.9793E+10 bytes

Redo Buffers 238047232 bytes

SQL> SQL> SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

SQL> SQL>

SQL>

SQL> alter system set cluster_database=FALSE scope=spfile sid='*';

System altered.

SQL>

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>

startup upgrade

ORACLE instance started.

Total System Global Area 8.1068E+10 bytes

Fixed Size 30385984 bytes

Variable Size 1.1006E+10 bytes

Database Buffers 6.9793E+10 bytes

Redo Buffers 238047232 bytes

Database mounted.

Database opened.

SQL>

SQL> show parameter string_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string EXTENDED

SQL>

SQL> @?/rdbms/admin/utl32k.sql

Session altered.

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

no rows selected

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

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.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

--------------------------------------------------------------------------------

12/20/2020 20:21:27.778469000

PL/SQL procedure successfully completed.

No errors.

Session altered.

0 rows created.

no rows selected

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if we encountered an error while modifying a column to

DOC> account for data type length change as a result of enabling or

DOC> disabling 32k types.

DOC>

DOC> Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Session altered.

SQL> alter system set cluster_database=TRUE scope=spfile sid='*';

System altered.

SQL>

SQL> shutdown immediate;

bash-4.2$ srvctl stop database -d PRODEXA1 -o immediate

PRCC-1016 : PRODEXA1 was already stopped

bash-4.2$ srvctl start database -d PRODEXA1

bash-4.2$ srvctl status database -d PRODEXA1

Instance PRODEXA11 is running on node host1.my.domain.com

Instance PRODEXA12 is running on node host2.my.domain.com

bash-4.2$

bash-4.2$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 20 20:23:29 2020

Version 19.8.0.0.0

Enter password:

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL>

SQL> show parameter cluster_database

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cluster_database boolean TRUE

SQL> show parameter max_string_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string EXTENDED

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MOUNTED

3 EXA MOUNTED

4 MC1F6791 MOUNTED

SQL>

SQL> alter pluggable database pdb$seed open upgrade;

Pluggable database altered.

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> @?/rdbms/admin/utl32k

Session altered.

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

no rows selected

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

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.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

--------------------------------------------------------------------------------

12/20/2020 20:24:43.903946000

PL/SQL procedure successfully completed.

No errors.

Session altered.

0 rows created.

no rows selected

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if we encountered an error while modifying a column to

DOC> account for data type length change as a result of enabling or

DOC> disabling 32k types.

DOC>

DOC> Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Session altered.

SQL> alter session set container=cdb$root;

Session altered.

SQL> sho pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MIGRATE YES

3 EXA MOUNTED

4 MC1F6791 MOUNTED

SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open read only;

Pluggable database altered.

SQL> sho pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 EXA MOUNTED

4 MC1F6791 MOUNTED

SQL> alter pluggable database EXA,MC1F6791 open upgrade;

Pluggable database altered.

SQL> alter session set container=EXA;

Session altered.

SQL> @?/rdbms/admin/utl32k

Session altered.

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

no rows selected

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

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.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

--------------------------------------------------------------------------------

12/20/2020 20:26:16.600081000

PL/SQL procedure successfully completed.

No errors.

Session altered.

0 rows created.

no rows selected

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if we encountered an error while modifying a column to

DOC> account for data type length change as a result of enabling or

DOC> disabling 32k types.

DOC>

DOC> Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Session altered.

SQL> alter session set container=MC1F6791;

Session altered.

SQL> @?/rdbms/admin/utl32k

Session altered.

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

no rows selected

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

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.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

--------------------------------------------------------------------------------

12/20/2020 20:26:41.482322000

PL/SQL procedure successfully completed.

No errors.

Session altered.

0 rows created.

no rows selected

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if we encountered an error while modifying a column to

DOC> account for data type length change as a result of enabling or

DOC> disabling 32k types.

DOC>

DOC> Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Session altered.

SQL> SQL> SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

bash-4.2$ srvctl stop database -d PRODEXA1 -o immediate

bash-4.2$ srvctl start database -d PRODEXA1

bash-4.2$ srvctl status database -d PRODEXA1

Instance PRODEXA11 is running on node host1.my.domain.com

Instance PRODEXA12 is running on node host2.my.domain.com

bash-4.2$

connect to CDB


  
  
SQL>  alter pluggable database all close immediate instances=all;

Pluggable database altered.

  
SQL> alter pluggable database all open instances=all;

Pluggable database altered.

SQL>

Principal Performance Engineering, Database, SQL, PLSQL and Fusion Middleware Learnings

Sunday, December 20, 2020

How to change max_string_size in oracle RAC container database and pluggable databases

No comments:

Post a Comment

Sites I often visit...

Popular Posts

Blog Archive

Recent Posts

Visits

Labels

Featured Post

BACKUP AND RESTORE DATABASE WITH RMAN IN ORACLE