MAX_STRING_SIZE=EXTENDED, ORA-00910, ORA-14694

Появилась задача переключить одну БД в MAX_STRING_SIZE=EXTENDED.
Много лет назад уже приходилось выполнять эту операцию на 12.1, она была проблемной, скрипт utl32k.sql не доходил до конца, заводили SR и долго разбирались в проблеме.
Много времени утекло с тех пор и сейчас уже 19.12.

Что такое MSS и как оно работает: в БД имеется параметр MAX_STRING_SIZE который может иметь два значения STANDARD или EXTENDED.

В стандартной конфигурации (MSS=STANDARD) максимальная длина строки VARCHAR2 = 4000 байт. Возьмёшь на один байт больше - и получишь ошибку:

SQL> create table MSS (col_MSS varchar2(4001));

Error starting at line : 1 in command -
create table MSS (col_MSS varchar2(4001))
Error report -
ORA-00910: specified length too long for its datatype
00910. 00000 -  "specified length too long for its datatype"
*Cause:    for datatypes CHAR and RAW, the length specified was > 2000;
           otherwise, the length specified was > 4000.
*Action:   use a shorter length or switch to a datatype permitting a
           longer length such as a VARCHAR2, LONG CHAR, or LONG RAW

При MSS=EXTENDED длина VARCHAR2 может достигать 32767 байт.
Для демонстрации возможностей MSS=EXTENDED сделаем простой тест: создаём таблицу с столбцом VARCHAR2 длиной более 4000 байт и вставим в эту таблицу стрки длиной 4000 байт.
Вначале выполним пример на тестовой БД со стандартными настройками MSS=STANDARD (она у меня single instance версии 19.17 + MRP1, nonCDB ) :

drop table mss;
show parameter MAX_STRING_SIZE
create table MSS (col_MSS varchar2(4000));
insert into MSS values (rpad('x',4000));
insert into MSS values (rpad('x',4001));
insert into MSS values (rpad('x',32767));
select rownum, length(col_MSS) from MSS;

SQL> show parameter MAX_STRING_SIZE
NAME            TYPE   VALUE    
--------------- ------ --------
max_string_size string STANDARD

SQL> create table MSS (col_MSS varchar2(4000));
Table created.

SQL> insert into MSS values (rpad('x',4000));
SQL> insert into MSS values (rpad('x',4001));
SQL> insert into MSS values (rpad('x',32767));

   ROWNUM    LENGTH(COL_MSS)
_________ __________________
        1               4000
        2               4000
        3               4000

Интересный получается результат! Ошибок нет, но длина строки обрезается по 4000 байт.
При INSERT можно потерять данные и не узнать об этом!

И еще одно подтверждение:
SQL> select length (rpad('x',32767)) from dual;

   LENGTH(RPAD('X',32767))
__________________________
                      4000

---------------------------------------------
Тестовая миграция

Миграция на MSS=EXTENDED заключается в выполнении двух шагов:
1) установить в файле параметров MSS=EXTENDED
2) запустить БД startup upgrade и выполнить скрипт utl32k.sql

И применительно к промышленной миграции возникают ряд вопросов:
- вопрос про Fallback-стратегию: как после установки MAX_STRING_SIZE=EXTENDED вернуть БД в MAX_STRING_SIZE=STANDARD, если в ходе миграции что-то пойдёт не по плану?
- как устанавливать MSS=EXTENDED на стендбай? Надо ли устанавливать этот параметр до того, как мы установили его на основной БД или после? И будет ли стендбай с MSS=STANDARD накатывать редо от БД с MSS=EXTENDED ?

Попробуем выполнить MSS=EXTENDED на тестовой БД ( версия 19.17 + MRP1, nonCDB, single instance) и затем откатить БД к точке восстановления.

Есть нота
2383964.1 After changing parameter Max_string_size=extended can I revert back to Standard
которая категорично утверждает, что возврат к MSS=STANDARD - только путём восстановления из бэкапа:

"This is not supported per the Oracle documentation." 
"The only way to revert is to restore the database from backup ..."

Но эта нота ничего не пишет про FLASHBACK TO RESTORE POINT. Можно ли использовать  Flashback DB для возврата к MSS=STANDARD? Т.е. после миграции на MAX_STRING_SIZE=EXTENDED откатить БД на заранее созданную точку восстановления и вернуть  MAX_STRING_SIZE=STANDARD ?

-- Попробуем проверить
-- Создаём точку восстановления BEFORE_MSS
SQL> create restore point BEFORE_MSS guarantee flashback database;
Restore point created.

RMAN> list restore point all;

SCN              RSP Time            Type       Time                Name
---------------- ------------------- ---------- ------------------- ----
2359859                              GUARANTEED 2022.12.11 15:55:35 BEFORE_MSS


-- Выполняем миграцию
-- Поскольку данная миграция затрагивает код приложений, то предварительно необходимо собрать информацию об инвалидных объектах.
-- А после миграции посмотреть - не появилось дополнительно что-то инвалидное ?

SQL> conn / as sysdba
Connected.
SQL> ALTER SYSTEM SET max_string_size=EXTENDED scope=spfile;
System SET altered.

SQL> shutdown immediate
SQL> startup upgrade
SQL> @?/rdbms/admin/utl32k
-- во время первого прогона случайно нажал Ctrl+C, скрипт свалился и был запущен повторно, никаких проблем поле этого не было, похоже, что можно запускать этот скрипт несколько раз

-- Поскольку в процессе миграции на 32К некоторые объекты могут стать инвалидными, то откомпилируем весь софт в БД еще раз
SQL> @?/rdbms/admin/utlrp

SQL> shutdown immediate
SQL> startup

-- Теперь наша БД с MSS=EXTENDED
-- С помощью нашего тестового примера проверим как работает 32K

SQL> show parameter max_string
NAME            TYPE   VALUE    
--------------- ------ --------
max_string_size string EXTENDED

drop table mss;
create table MSS (col_MSS varchar2(32767));
insert into MSS values (rpad('x',4000));
insert into MSS values (rpad('x',4001));
insert into MSS values (rpad('x',32767));
select rownum, length(col_MSS) from MSS;
select length (rpad('x',32767)) from dual;

SQL> select rownum, length(col_MSS) from MSS;

   ROWNUM    LENGTH(COL_MSS)
_________ __________________
        1               4000
        2               4001
        3              32767

SQL> select length (rpad('x',32767)) from dual;

   LENGTH(RPAD('X',32767))
__________________________
                     32767
-- Вполне ожидаемый результат: VARCHAR2 длиной до 32767 байт.


-- Переходим к FLASHBACK TO RESTORE POINT:

SQL> alter system set max_string_size='STANDARD' scope=spfile;
SQL> shutdown immediate
$ rman target /
RMAN> startup mount
RMAN> flashback database to restore point BEFORE_MSS;

Starting flashback at 11-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished flashback at 11-DEC-22

RMAN> alter database open resetlogs;


SQL> show parameter max_string
NAME            TYPE   VALUE    
--------------- ------ --------
max_string_size string STANDARD


-- Создадим таблицу и посмотрим что получится:
drop table mss;
create table MSS (col_MSS varchar2(32767));
insert into MSS values (rpad('x',4000));
insert into MSS values (rpad('x',4001));
insert into MSS values (rpad('x',32767));
select rownum, length(col_MSS) from MSS;
select length (rpad('x',32767)) from dual;

SQL> drop table mss;

Table MSS dropped.

-- Возникает ожидаемая ошибка:
SQL> create table MSS (col_MSS varchar2(32767));

Error starting at line : 1 in command -
create table MSS (col_MSS varchar2(32767))
Error report -
ORA-00910: specified length too long for its datatype
00910. 00000 -  "specified length too long for its datatype"
*Cause:    for datatypes CHAR and RAW, the length specified was > 2000;
           otherwise, the length specified was > 4000.
*Action:   use a shorter length or switch to a datatype permitting a
           longer length such as a VARCHAR2, LONG CHAR, or LONG RAW

SQL> create table MSS (col_MSS varchar2(4000));

Table MSS created.

SQL> insert into MSS values (rpad('x',4000));

1 row inserted.

SQL> insert into MSS values (rpad('x',4001));

1 row inserted.

SQL> insert into MSS values (rpad('x',32767));

1 row inserted.

SQL> commit;

Commit complete.

SQL> select rownum, length(col_MSS) from MSS;

   ROWNUM    LENGTH(COL_MSS)
_________ __________________
        1               4000
        2               4000
        3               4000

SQL> select length (rpad('x',32767)) from dual;

   LENGTH(RPAD('X',32767))
__________________________
                      4000


-- Поведение полностью повторяет то, что было до миграции.
-- Подтверждаем, что откат на точку восстановления возможен.

-- Не забудьте удалить точку восстановления:
SQL> drop restore point BEFORE_MSS ;