Error opening the PDB db as MAX_STRING_SIZE migration is incomplete for pluggable database PDB$SEED in Oracle 19C DB
Posted by Mir Sayeed Hassan on November 20th, 2024
Error opening the PDB db as MAX_STRING_SIZE migration is incomplete for pluggable database PDB$SEED in Oracle 19C DB
Check the database version
SQL> select name, open_mode, version from v$database, v$instance; NAME OPEN_MODE VERSION -------------------------------------- PEPTEST READ WRITE 19.0.0.0.0
Check the CDB & PDBS
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------------ 2 PDB$SEED MOUNTED 3 TEST_PDB1 READ WRITE NO
Open the PDB DB
SQL> alter pluggable database PDB$SEED open; alter pluggable database PDB$SEED open * ERROR at line 1: ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database PDB$SEED
SQL> shutdown immediate; SQL> startup;
SQL> alter pluggable database all open; alter pluggable database all open * ERROR at line 1: ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
SQL> startup mount
SQL> alter database open migrate; Database altered.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- --------------------------------------- 2 PDB$SEED MIGRATE YES 3 TEST_PDB1 MOUNTED
Run the below script:
Note: – This utl32k.sql script is to increases the maximum size of VARCHAR2, NVARCHAR2 & RAW columns for the views where this is required as needed.
SQL> @?/rdbms/admin/utl32k.sql Session altered. 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. Session altered. Table created. Table created. Table created. Table truncated. 0 rows created. Session altered. PL/SQL procedure successfully completed. STARTTIME ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 11/20/2024 12:17:27.145780000 PL/SQL procedure successfully completed. No errors. Session altered. Session altered. Session altered. 0 rows created. no rows selected no rows selected DOC>####################################################################### 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>####################################################################### DOC># PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Commit complete. Package altered. Session altered.
Error occur while opening of the Pluggable database., This error occur due the MAX_STRING_SIZE has been changed in database before.
To resolve this fallow the below steps
Check the max_string_size parameter
SQL> show parameter max_string_size NAME TYPE VALUE ------------------------------------------ max_string_size string STANDARD
Take a backup of the spfile for saverside
SQL> create pfile='/tmp/pfile_20nov24.ora' from spfile; File created.
Change the MAX_STRING_SIZE from STANDARD to EXTENDED.
SQL> alter system set MAX_STRING_SIZE='EXTENDED' scope=spfile; System altered.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- -------------------------------------- 2 PDB$SEED MIGRATE YES
Shutdown and start up of database
SQL> shutdown immediate SQL> startup
Check the status
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------------ 2 PDB$SEED READ ONLY NO 3 TEST_PDB1 MOUNTED
SQL> show parameter MAX_STRING_SIZE NAME TYPE VALUE ------------------------------------------ max_string_size string EXTENDED
Now we are able to create the Pluggable database
Create a Pluggable Database
SQL> create pluggable database TEST_PDB2 admin user pdb_admin identified by STdbpdb2; Pluggable database created.
After the pluggable database got created now its not allow us to open the other pluggable database from MOUNT to OPEN Mode
Error while opening the other pluggable databases
SQL> alter pluggable database all open; alter pluggable database all open * ERROR at line 1: ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
Therefore you have to revert back to the previous stage of this parameter MAX_STRING_SIZE=’STANDARD’
SQL> alter system set MAX_STRING_SIZE='STANDARD' scope=spfile; System altered.
SQL> shu immediate SQL> startup
Verify the parameter
SQL> show parameter MAX_STRING_SIZE NAME TYPE VALUE ----------------------------------------- max_string_size string STANDARD
Now it will allow us to open the remaining pluggable database
SQL> alter pluggable database all open; Pluggable database altered.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------------- 2 PDB$SEED READ ONLY NO 3 TEST_PDB1 READ WRITE NO 4 TEST_PDB2 READ WRITE NO