10 марта 2017

Замена DBTIMEZONE на Oracle 11 без потери данных


Эта статья имеет отношение к Oracle Database

Потенциальные потребители информации:

  • Банки и финансовые институты, которые хранят котировки рынка валют в строках с полями TIMESTAMP WITH LOCAL TIME ZONE (далее TSLTZ);
  • Предприятия электро- и теплоэнергетики, которые хранят показания счётчиков в таких полях;
  • Иные организации, у которых DBTIMEZONE <> 'GMT' и которые хотят обновить файл таймзон timezlrgXX.dat.

Имеем следующую проблему.

Пару лет назад создавался сервер Oracle 11.2.0.4 для клиента, расположенного на Украине. Соответственно, часовой пояс сервера Windows — Киев.
А параметр DBTIMEZONE, насколько я заметил за пару лет наблюдения, меняется, летом это "+03:00", зимой "+02:00".

К сожалению, когда устанавливался сервер, не была выполнена рекомендация Oracle, описанная в Database Globalization Support Guide (http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG004)

Там написано
The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle recommends that you set the database time zone to UTC (0:00) to avoid data conversion and improve performance when data is transferred among databases. This is especially important for distributed databases, replication, and exporting and importing.

Без выполнения рекомендаций этой статьи, при обновлении файла timezlrgXX.dat значения в полях TSLTZ "поедут" на пару часов вперёд-назад, особенно для российских часовых поясов.

Гугление показывает, что единственным путём изменение DBTIMEZONE является экспорт таблиц с полями TSLTZ, их удаление, и последующий импорт. Но при этом значение опять же таки "поедут".

В системе моего клиента очень много полей с типом TSLTZ.
Пока клиенты украинские, поскольку на Украине пертурбаций со сдвигом часовых поясов не было, это всё работало.
Но сейчас это надо исправить, во первых, потому что появляются международные клиенты (в т.ч. российские), во вторых компания растёт и в ближайшее время будет Oracle Support, а значит будет заменён файлик таймзон timezlrgXX.dat, в том числе для корректного отображения времени для России с учётом многократного переименования и передвижения таймзон за последние несколько лет.

Прямая команда предсказуемо отказывается выполняться

SQL> alter database set TIME_ZONE='GMT';
alter database set TIME_ZONE='GMT'
*
ERROR at line 1:

ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

Это и хорошо, иначе все TSLTZ поля были бы испорченными

Будем делать по своему...

Что нам может здесь помешать:

1) Участие полей типа TSLTZ первичных ключах.

Найти такие таблицы и поля можно с помощью запроса

select /*+ no_query_transformation */ a.CONSTRAINT_TYPE, a.OWNER, a.TABLE_NAME, c.COLUMN_NAME, a.OWNER, a.CONSTRAINT_NAME
from DBA_CONSTRAINTS a, DBA_IND_COLUMNS b, DBA_TAB_COLUMNS c
where a.CONSTRAINT_TYPE in ('P', 'U')
  and a.INDEX_OWNER = b.INDEX_OWNER and a.INDEX_NAME = b.INDEX_NAME
  and b.TABLE_OWNER = c.OWNER and b.TABLE_NAME = c.TABLE_NAME and b.COLUMN_NAME = c.COLUMN_NAME
  and c.DATA_TYPE like 'TIMESTAMP%WITH LOCAL TIME ZONE'
order by a.OWNER, a.TABLE_NAME

Если запрос ничего не возвращает — ничего делать не нужно с PK и UK
  
В рамках процедуры апдейта мы будем некоторые поля ставить в значения NULL, а для первичных ключей это недопустимо.
Такие ключи или IOT-таблицы придётся пересоздавать, со всеми констрейнтами.
Я рекомендую IOT-таблицы преобразовать в обычные HEAP, чтобы для них корректно прошла процедура трансляции, описанная ниже, а по завершении вернуть таблицы к виду IOT.

Этот запрос отображает также констрейнты UNIQUE.
Для UNIQUE установка в NULL допустима, если на них не ссылаются никакие FK. Проверьте это сами

Этот вопрос должен быть решен вами самостоятельно ДО выполнения данной процедуры

2) Некоторые индексы.

В процедуре ниже мы будем менять типы некоторых полей с TIMESTAMP WITH LOCAL TIME ZONE на TIMESTAMP
Есть вероятность получить вот такую ошибку

ORA-30556: either functional or bitmap join index is defined on the column to
be modified

или

Oracle Error: ORA-30556
Error Description: Functional index is defined on the column to be modified
Error Cause: An ALTER TABLE MODIFY COLUMN was issued on a column on which a functional index exists.
Action: Drop the functional index before attempting to modify the column.

Эта проблема будет решаться в рамках описанной ниже процедуры. Будет предложено забэкапировать определения индексов, в которых участвуют поля TSLTZ и которые имеют тип не NORMAL, и в конце процедуры пересоздать их

3) Триггеры на UPDATE, которые апдейтят поля, например LAST_UPDATED_TSLTZ

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


План работы:

1) Сделаем backup БД;
2) Составим список всех таблиц и полей типа TSLTZ;
3) Переведём БД в RESTRICTED MODE;
4) Удалим индексы типов не NORMAL с полями TSLTZ;
5) Временно запретим триггеры на UPDATE;
6) Cоздадим клоны полей с суффиксом _#GMT в каждой таблице, тип поля -- TIMESTAMP (N) WITH TIME ZONE;
7) Запишем в эти клоны время в таймзоне GMT;
8) Для оригинальных полей TSLTZ, которые объявлены как NOT NULL, изменим определение на NULL;
9) Обнулим все поля TSLTZ;
10) Изменим всем полям TSLTZ тип на TIMESTAMP;
11) Изменим DBTIMEZONE на GMT;
12) Выполним реверт шага 10 — опять сделаем поля TSLTZ;
13) Заполним эти поля данными;
14) Опять объявим некоторые из них как NOT NULL (реверт шага 8);
15) Удалим клоны полей;
16) Включим триггеры;
17) Восстановим индексы;
18) Переведём БД в обычный режим;
19) Протестируем и через некоторое время удалим DATABASE RESTORE POINT.

Везде далее в тексте, команды, которые формируют скрипты, удобнее выполнять в оболочке, например SQL Developer или TOAD, а результат или выполнять в SQL*Plus или в другой сессии TOAD как скрипт, чтобы отследить возможные ошибки


Шаг 1. Сделаем FULL BACKUP БД

Можно сделать либо BACKUP средствами RMAN,
либо воспользоваться FLAHSBACK

SQL> create restore point RP_BEFORE_DBTIMEZONE_CHANGE guarantee flashback database;

Restore point created.

Я рекоменду.ю сделать сначала первое, потом второе


Шаг 2. Переведём БД в RESTRICTED MODE

Работаем через SQL*Plus
Отключаем планировщик

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
PL/SQL procedure successfully completed.

Перестартуем БД в защищённом режиме (чтобы пользователи не ютились)

SQL> shu immediate;

 Database closed.
Database dismounted.
ORACLE instance shut down.

 SQL> startup restrict quiet

 ORACLE instance started.
Database mounted.

Database opened.

SQL> set serverout on

 SQL> spool 'C:\TEMP\TSLTZ.LOG'
Дальнейшие действия лучше производить в какой-либо оболочке, которая умеет отображать сетку — SQL Developer или TOAD (под SYS'ом нам удастся подключиться даже в RECTRICTED MODE)


Шаг 3. Составим список всех таблиц и полей типа TSLTZ

 Создадим таблицу в схеме SYS под пользователем SYS со списком интересующих нас полей
(Все дальнейшие операции придётся выполнять под SYS'ом)

create table TSLTZ as
select OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_SCALE
from DBA_TAB_COLUMNS
where DATA_TYPE like '%TIMESTAMP%WITH LOCAL TIME ZONE' and (OWNER, TABLE_NAME) in (select OWNER, TABLE_NAME from DBA_TABLES)
order by 1, 2, 3


Шаг 4. Удалим индексы типов не NORMAL, в которых участвуют поля TSLTZ

Вот запрос, который позволяет найти индексы типов не NORMAL, в которых участвуют поля TSLTZ:

select OWNER, INDEX_NAME, INDEX_TYPE, 'drop index ' || OWNER || '.' || INDEX_NAME || ';' as CMD
from DBA_INDEXES a
where a.INDEX_TYPE not in ('NORMAL', 'NORMAL/REV', 'IOT - TOP', 'LOB')
  and (a.TABLE_OWNER, a.TABLE_NAME) in (select distinct OWNER, TABLE_NAME from TSLTZ)
union all
select null, null, null, null from dual
  
Забэкапируйте определения индексов через "Extract DDL" или в TOAD "Create script".
После этого выполните все команды, которые отобразились в сетке в последней колонке

Шаг 5. Временно запретим триггеры

Выполните оператор в сетке

select 'alter trigger '||OWNER||'.'||TRIGGER_NAME||' disable;' as STMT_DISABLE
     , 'alter trigger '||OWNER||'.'||TRIGGER_NAME||' enable;'  as STMT_ENABLE
from DBA_TRIGGERS a
where a.BASE_OBJECT_TYPE = 'TABLE' and (a.TABLE_OWNER, a.TABLE_NAME) in (select distinct OWNER, TABLE_NAME from TSLTZ)
  and a.STATUS = 'ENABLED' and a.TRIGGERING_EVENT like '%UPDATE%'
union all
select null, null from dual


Вторую колонку, которая разрешает триггеры — сохраните в отдельном файле. Мы её выполним в конце процедуры.
Первую колонку скопируйте и выполните в SQL*Plus

Шаг 6. Cоздадим клоны полей с суффиксом _#GMT в каждой таблице, тип поля — TIMESTAMP (N) WITH TIME ZONE

 Выполните оператор в сетке

select 'alter table ' || OWNER || '.' || TABLE_NAME || ' add ' || COLUMN_NAME || '_#GMT timestamp ('||DATA_SCALE||') with time zone;' as CMD
from TSLTZ a where (OWNER, TABLE_NAME) in (select OWNER, TABLE_NAME from DBA_TABLES)
union all
select ' ' from dual

Выполните результат команды в SQL*Plus


Шаг 7. Запишем в эти клоны время в таймзоне GMT

Выполните оператор в сетке

          select 'begin' as CMD from dual
union all select '    dbms_output.enable(30000);' from dual
union all select '    rollback;' from dual
union all select '    update ' || OWNER || '.' || TABLE_NAME || ' set ' || COLUMN_NAME || '_#GMT' || ' = ' || COLUMN_NAME || ' at time zone ''GMT'';' as CMD from TSLTZ a
union all select '    commit;' from dual
union all select '    dbms_output.put_line (''Success!'');' from dual
union all select 'exception when others then' from dual
union all select '    rollback;' from dual
union all select '    dbms_output.put_line (''Oops! Something nasty happened. Transaction rolled back. Analyze log...'');' from dual
union all select '    raise;' from dual
union all select 'end;' from dual
union all select '/' from dual
union all select '' from dual

Выполните результат команды в SQL*Plus.

Все апдейты выполняются в одной транзакции — всё или ничего.
Если возникла ошибка — проанализируйте.
Эти апдейты можно запускать многократно, после устранения ошибки.
Если неуспешно — возвращайтесь к точке восстановления с помощью FLASHBACK.

Шаг 8. Для оригинальных полей, которые объявлены как NOT NULL, изменим определение на NULL

 Выполните оператор в сетке

select 'alter table ' || OWNER || '.' || TABLE_NAME || ' modify (' || COLUMN_NAME || ' null);' as CMD
from TSLTZ where NULLABLE = 'N'
union all
select ' ' from dual;

Выполните результат команды в SQL*Plus
Смотрите в спул. Ошибок быть не должно.

Шаг 9. Обнулим все поля TSLTZ

 Выполните оператор в сетке

          select 'begin' as CMD from dual
union all select '    dbms_output.enable(30000);' from dual
union all select '    rollback;' from dual
union all select '    update ' || OWNER || '.' || TABLE_NAME || ' set ' || COLUMN_NAME || ' = NULL;' as CMD from TSLTZ a
union all select '    commit;' from dual
union all select '    dbms_output.put_line (''Success!'');' from dual
union all select 'exception when others then' from dual
union all select '    rollback;' from dual
union all select '    dbms_output.put_line (''Oops! Something nasty happened. Transaction rolled back. Analyze log...'');' from dual
union all select '    raise;' from dual
union all select 'end;' from dual
union all select '/' from dual
union all select '' from dual

Выполните результат команды в SQL*Plus.

Шаг 10. Изменим им тип на TIMESTAMP

 Выполните оператор в сетке

select 'alter table ' || OWNER || '.' || TABLE_NAME || ' modify (' || COLUMN_NAME || ' timestamp);' as CMD
from TSLTZ
union all
select ' ' from dual;

Выполните результат команды в SQL*Plus

Шаг 11. Изменим DBTIMEZONE на GMT

Выполните в SQL*Plus

 SQL> alter database set TIME_ZONE='GMT';

 Database altered.

 SQL> shu immediate

 У меня этот SHUTDOWN выполнялся долго

 Database closed.
Database dismounted.
ORACLE instance shut down.

 SQL> startup restrict quiet

 ORACLE instance started.
Database mounted.
Database opened.

 Проверим таймзону сервера

 SQL> select DBTIMEZONE from dual;

 DBT
---
GMT


Шаг 12. Выполним реверт шага 9 — опять сделаем поля TSLTZ

 Выполните команду в сетке. Нужно будет переконнектиться, из-за рестарта сервера.

select 'alter table ' || OWNER || '.' || TABLE_NAME || ' modify (' || COLUMN_NAME || ' timestamp ('||DATA_SCALE||') with local time zone);' as CMD
from TSLTZ
union all
select ' ' from dual;

Выполните результат команды в SQL*Plus

Шаг 13. Заполним эти поля данным

 Выполните команду в сетке

          select 'begin' as CMD from dual
union all select '    dbms_output.enable(30000);' from dual
union all select '    rollback;' from dual
union all select '    update ' || OWNER || '.' || TABLE_NAME || ' set ' || COLUMN_NAME || ' = ' || COLUMN_NAME || '_#GMT;' as CMD from TSLTZ a
union all select '    commit;' from dual
union all select '    dbms_output.put_line (''Success!'');' from dual
union all select 'exception when others then' from dual
union all select '    rollback;' from dual
union all select '    dbms_output.put_line (''Oops! Something nasty happened. Transaction rolled back. Analyze log...'');' from dual
union all select '    raise;' from dual
union all select 'end;' from dual
union all select '/' from dual
union all select '' from dual

Выполните результат команды в SQL*Plus.

Шаг 14. Опять объявим некоторые из них как NOT NULL (реверт шага 7)

Выполните команду в сетке

select 'alter table ' || OWNER || '.' || TABLE_NAME || ' modify (' || COLUMN_NAME || ' not null);' as CMD
from TSLTZ where NULLABLE = 'N'
union all
select ' ' from dual;

Выполните результат команды в SQL*Plus.

Шаг 15. Удалим клоны полей

Выполните команду в сетке

select 'alter table ' || OWNER || '.' || TABLE_NAME || ' drop column ' || COLUMN_NAME || '_#GMT;' as CMD
from TSLTZ
union all
select ' ' from dual;

Выполните результат команды в SQL*Plus


Шаг 16. Включим триггеры

Выполните в SQL*Plus команды по включению триггеров, которые вы сохранили на шаге 5.

Шаг 17. Восстановим индексы

Пересоздайте индексы, определения которых вы сохранили на шаге 4

Шаг 18. Переведём БД в обычный режим

Выполните в SQL*Plus

SQL> shu immediate

 Database closed.
Database dismounted.
ORACLE instance shut down.

 SQL> startup quiet

 ORACLE instance started.
Database mounted.
Database opened.

 И не забываем включить планировщик

 SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');

 PL/SQL procedure successfully completed.

Шаг 19. Тестирование и удаление checkpoint

После того, как тестирование будет завершено, удаляем чекпоинт

SQL> drop restore point RP_BEFORE_DBTIMEZONE_CHANGE;

 Restore point dropped.


DBTIMEZONE изменена без потери и уплывания данных

Автор: на

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)