Ora 01555 слишком старый снимок сегмент отката

ORA-01555 — Причина ошибки — недостаточный размер сегмента отката.

Нужно обеспечить сохранность информации в UNDO на всё время пока выполняется запрос. Для этого нужно иметь достаточный размер параметра UNDO_RETENTION и достаточный размер табличного пространства UNDO.

Т.е. проще говоря, для того чтобы избежать возникновения ORA-01555 нужно увеличивать размер табличного пространства UNDO и параметр UNDO_RETENTION до тех пор пока операция не пройдет без ошибки.

1) Убедиться что UNDO управляется автоматически. Т.е. параметр БД UNDO_MANAGEMENT = AUTO.

Если не так, включить автоматическое управление (требуется перезапуск БД):

ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

После чего перезапустить БД.

2) Настройка табличного пространства UNDO

— Определить какого размера UNDO сейчас

SELECT SUM(a.bytes)/1024/1024 as "UNDO_SIZE_IN_MB"
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND c.tablespace_name = ‘UNDO11’ — для RAC
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

— Табличное пространство UNDO состоит из одного файла.

— На диске где лежит файл табличного пространства UNDO еще есть свободное место. Сколько его.

Здесь главное понять что файлы табличного пространства имеют достаточный размер (лучше сделать их авторасширяемыми) и их достаточное количество и на диске есть месть для авторасширения файлов.

3) Нужно определить наибольшее время выполнения SQL-запроса (т.е. время потраченное на выполнение самого долгого запроса). Для этого есть несколько способов. Если ни один из способов не выявил большого времени выполнения запроса, тогда придется экспериментальным путем устанавливать этот параметр. Можно сразу установить заведомо большое значение, но при этом нужно помнить что чем больше UNDO_RETENTION тем до большего размера может вырасти табличное пространство UNDO и это может закончиться переполнением диска, на котором находятся файлы UNDO.

3.1) Посмотрите alert.log на предмет наличия ошибки ORA-01555 в то время когда выполнялся экспорт. В сообщение об ошибке может быть указанно время в сек. Выполнения операции. Параметр UNDO_RETENTION нужно установить не менее чем это время, а лучше раза в два больше.

ORA-01555 caused by SQL statement below (SQL >Query Duration=224611 sec , SCN: 0x0a0e.44620301):

3.2) Если в alert.log ничего нет, то можно попробовать определить оптимальный начальный UNDO_RETENTION. Выполнять под SYS.

— Покажет колл. секунд выполнения самого долгого запроса за последние 7 дней

SELECT MAX(MAXQUERYLEN) FROM V$UNDOSTAT;

Параметр UNDO_RETENTION нужно установить большим чем полученное значение (можно сделать в два раза больше, чтобы иметь запас). После выполнения запроса, если он не выполняется регулярно, лучше вернуть UNDO_RETENTION исходное значение, чтобы табличное пространство UNDO не разрасталось.

4) Чтобы уменьшить вероятность возникновения ORA-01555 нужно чтобы с БД во время экспорта вообще никто не работал (потому что другие сессии могут также увеличивать UNDO). Идеально если экспорт будет выполняться вообще один. Нужно учесть что с БД могут работать не только пользователи, но и службы и пакетные задания (batcmd). Т.е. на время экспорта лучше остановить все службы, все задания и т.п.

Уважаемые подписчики! Этот выпуск посвящен "знаменитому" сообщению об ошибке ORA-1555: snapshot too old . Как обычно, по мотивам Тома Кайта.

Snapshot too old

Не мог бы ты объяснить, что означает сообщение об ошибке snapshot too old . Когда возникает эта ошибка? По каким причинам? Как избавиться от этой ошибки?

Ответ Тома Кайта

Мне кажется, документ службы поддержки очень хорошо раскрывает эту тему:

Обзор

В этой статье обсуждаются условия, при которых запрос может вернуть сообщение об ошибке ORA-01555 "snapshot too old (rollback segment too small)" . Затем в статье будут обсуждаться действия, которые можно предпринять во избежание этой ошибки и, наконец, будет представлен ряд простых сценариев PL/SQL, иллюстрирующих рассмотренные проблемы.

Терминология

Предполагается, что читатель знаком со стандартными терминами Oracle, такими как "сегмент отката" и "SCN". В противном случае, необходимо сначала прочитать руководство Oracle Server Concepts и другую соответствующую документацию Oracle.

Кроме этого, ниже кратко рассмотрены два ключевых понятия, которые помогут понять причины возникновения ошибки ORA-01555 :

Это понятие описано с руководстве Oracle Server Concepts и поэтому детально не обсуждается. Однако для понимания этой статьи соответствующий раздел руководства надо прочитать и понять, если вы его еще не поняли.

Сервер Oracle обеспечивает многоверсионную согласованность по чтению, ценную возможность, гарантирующую получение согласованного представления данных (отсутствие "грязных чтений").

Это лучше всего проиллюстрировать примером: рассмотрим транзакцию, изменяющую таблицу с миллионом строк. При этом для изменения данных, очевидно, надо посетить большое количество блоков данных. Когда пользователь фиксирует транзакцию, сервер Oracle НЕ проходит повторно по всем этим блокам, чтобы зафиксировать изменения. Это действие выполнит следующая транзакция, которая обратится к любому блоку, затронутому изменением, – она "очистит" его (отсюда и термин " отложенная очистка блока ").

При любом изменении блока базы данных сервером Oracle (блока индекса, таблицы, кластера), он сохраняет указатель в заголовке блока данных, который идентифицирует сегмент отката, использовавшийся для хранения данных отката для изменений, выполненных транзакцией. (Они понадобятся, если в дальнейшем пользователь решит не фиксировать изменения и захочет их "отменить".)

При фиксации сервер просто помечает соответствующую запись в заголовке сегмента отката как зафиксированную. Теперь, когда один из измененных блоков посещается снова, сервер Oracle проверяет заголовок блока данных, показывающий, что блок был изменен в определенный момент. Серверу надо разобраться, было ли это изменение зафиксировано или оно еще не зафиксировано. Для этого сервер Oracle оперделяет, какой сегмент отката использовался предыдущей транзакцией (по заголовку блока), а затем определяет по заголовку этого сегмента отката, была ли транзакция зафиксирована или нет.

Если оказывается, что блок зафиксирован, то заголовок блока данных изменяется так, чтобы при последующем доступе к блоку такая обработка не понадобилась.

Это поведение сервера в очень упрощенном виде проиллюстрировано ниже. Мы пройдем по стадиям изменения блока данных.

СТАДИЯ 1 – Нет изменений

Описание:

Это начальное состояние. В начале блока данных имеется область, используемая для привязки активных транзакций к сегменту отката (часть ‘ tx ‘), а в заголовке сегмента отката имеется таблица, в которой хранится информация о всех последних транзакциях, использовавших этот сегмент отката.

В нашем примере имеется два активных слота транзакций (01 и 02), а следующий свободный слот – слот 03. (Поскольку можно переписывать зафиксированные транзакции.)

СТАДИЯ 2 – Изменяется строка 2

Описание:

Мы изменили строку 2 блока 500. Обратите внимание, что заголовок блока данных изменен и указывает на сегмент отката 5, слот транзакции 3 (5.3), и что транзакция помечена как незафиксированная (Active).

СТАДИЯ 3 – Пользователь фиксирует транзакцию

Описание:

Затем пользователь выполняет фиксацию. Учтите, что при этом изменяется только слот соответствующей транзакции в заголовке сегмента отката – транзакция помечается как зафиксированная. С данными в блоке не делается ничего .

СТАДИЯ 4 – Другой пользователь выбирает данные блока 500

Описание:

Через некоторое время другой пользователь (или тот же) снова обращается к блоку данных 500. Оказывается, что, в соответствии с заголовком блока, в блоке есть незафиксированное изменение.

Сервер Oracle затем использует заголовок блока данных для поиска соответствующего слота таблицы транзакций сегмента отката, проверки, зафиксирована ли транзакция, и изменения блока данных 500 так, чтобы в нем отражалось действительное состояние данных. (Т.е., он выполняет отложенную очистку).

Объяснение ошибки ORA-01555

Есть две основные причины возникновения ошибки ORA-01555 , которые являются следствием попыток сервера Oracle получить "согласованный по чтению" образ данных:

• Сами данные отката перезаписаны, так что сервер Oracle не может откатить (зафиксированные) записи транзакции для получения достаточно давней версии блока.
• Слот транзакции в таблице транзакций сегмента отката (которая хранится в заголовке сегмента отката) перезаписан, и сервер Oracle не может откатить заголовок сегмента отката до такой степени, чтобы можно было получить исходный слот транзакции в сегменте отката.

Обе эти ситуации рассматриваются ниже, вместе с последовательностями шагов, вызывающих получение сообщения об ошибке ORA-01555 . При описании этих шагов упоминается "QENV". "QENV" (сокращение от "Query Environment" – среда запроса) – это среда, существовавшая на момент начала запроса и по отношению к которой сервер Oracle пытается получить согласованный по чтению образ. С этой средой связано значение SCN (System Change Number – номер системного изменения) в соответствующий момент времени, так что QENV 50 – это среда запроса для значения SCN 50.

СИТУАЦИЯ 1 – ДАННЫЕ ОТКАТА ПЕРЕПИСАНЫ

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

1. Сеанс 1 начинает запрос в момент времени T1 при QENV 50
2. Сеанс 1 выбирает блок B1 в ходе этого запроса
3. Сеанс 1 изменяет этот блок при SCN 51
4. Сеанс 1 выполняет другие действия, генерирующие данные отката.
5. Сеанс фиксирует изменения, выполненные на шагах 3 и 4.
   (Теперь другие транзакции могут переписывать соответствующие данные отката)
6. Сеанс повторно обращается к тому же блоку B1 (возможно, в поисках другой строки).

Теперь сервер Oracle может понять по заголвоку блока, что блок был изменен, причеми, позже, чем требуемая QENV (которая соответствовала SCN 50). Поэтому необходимо получить образ блока по состоянию на эту QENV.

Если достаточно старую версию блока можно найти в буферном кэше, она и будет использована, иначе необходимо откатить текущий блок, чтобы сгенерировать другую его версию, соответствующую требуемой среде QENV.

Именно в этом случае сервер Oracle может не найти необходимые данные отката, поскольку другие изменения в сеансе 1 сгенерировали данные отката, которые переписали необходимые серверу данные, и тогда сервер возвращает сообщение об ошибке ORA-1555 .

СИТУАЦИЯ 2 – ПЕРЕПИСАН СЛОТ ТРАНЗАКЦИИ В СЕГМЕНТЕ ОТКАТА

1. Сеанс 1 начинает запрос в момент времени T1 при QENV 50
2. Сеанс 1 выбирает блок B1 в ходе этого запроса
3. Сеанс 1 изменяет этот блок при SCN 51
4. Сеанс 1 фиксирует изменения.
   (Теперь другие транзакции могут переписывать соответствующие данные отката)
5. Сеанс (сеанс 1, другой сеанс или несколько других сеансов) затем использует тот же сегмент отката для выполнения ряда зафиксированных транзакций.

Каждая из этих транзакций использует слот в таблице транзакций сегмента отката, так что со временем придется использовать слоты с начала таблицы (слоты в таблице используются циклически) и все их переписывать. Учтите, что сервер Oracle свободно может повторно использовать эти слоты, потому что все транзакции зафиксированы.

Запрос сеанса 1 затем обращается к блоку, который был изменен с момента установки исходной среды QENV. Поэтому серверу Oracle нгеобходимо получить образ блока на соответствующий момент времени.

Затем, сервер Oracle пытается найти слот транзакции в заголовке сегмента отката, на который указывает заголовок блока данных. При этом сервер понимает, что это слот был преписан и пытается откатить изменения, выполненные в заголовке сегмента отката, чтобы получить исходную запись слота транзакции.

Если сервер Oracle не сможет откатить таблицу транзакций сегмента отката до нужного момента времени, то вернет сообщение об ошибке ORA-1555 , поскольку не может более получить требуемую версию блока данных.

Можно также обнаружить вариант слота транзакции, переписанный при очистке блока. Эта ситуация кратко описана ниже:

Сеанс 1 начинает запрос при QENV 50. После этого другой процесс изменяет блоки, которые понадобятся сеансу 1. Когда сеанс 1 встречает эти блоки, он определяет, что блоки изменились и еще не были очищены (с помощью отложенной очистки блоков). Сеанс 1 должен определить, были ли в дальнейшем изменены строки блока, существовавшие при QENV 50.

Для этого сервер Oracle должен просмотреть соответствующий слот таблицы транзакций сегмента отката, чтобы определить значение SCN для фиксации. Если этот SCN – после QENV, сервер Oracle должен попытаться построить прежнюю версию блока, а если до, достаточно просто выполнить очистку блока, и он будет вполне соответствовать QENV.

Если слот транзакции был переписан и таблицу транзакций нельзя откатить до достаточно давней версии, сервер Oracle не может получить необходимый образ блока, и возвращает сообщение об ошибке ORA-1555 .

( Примечание: Обычно сервер Oracle может использовать алгоритм определения значения SCN блока в ходе очистки блока, даже если транзакции в сегменте отката был переписан. Но в этом случае сервер Oracle не может гарантировать, что версия блока не изменилась с момента начала запроса).

Решения

В этом разделе представлен ряд решений, которые можно использовать, чтобы избежать проблем с получением сообщения об ошибке ORA-01555 , представленных в этой статье. Они относятся к случаям, когда данные в сегменте отката переписываются тем же сеансом, который их сгенерировал, и когда переписывается запись в таблице транзакций сегмента отката.

Следует отметить, что если один сеанс приводит к получению сообщения об ошибке ORA-01555 , и оно не связано со специальными случаями, перечисленными в конце этой статьи, значит, сеанс заведомо использует расширение Oracle, разрешающее выбирать данные в нескольких транзакциях (fetch across commit). Это не согласуется с моделью ANSI, и в тех редких случаях, когда возвращается сообщение об ошибке ORA-01555 , необходимо использовать одно из представленных ниже решений.

СИТУАЦИЯ 1 – ДАННЫЕ ОТКАТА ПЕРЕПИСАНЫ

1. Увеличьте размер сегмента отката, что снизит вероятность переписывания необходимых данных отката.
2. Сократите количество фиксаций (та же причина, что и для решения 1).
3. Выполняйте обработку данных по частям, а не всей таблицы сразу (та же причина, что и для решения 1).
4. Добавьте дополнительные сегменты отката. Это позволит распределить изменения по большему количеству сегментов отката, тем самым, сократив вероятность перезаписи требуемых данных отката.
5. При выборе данных в нескольких транзакциях можно изменить код так, чтобы этого не делать.
6. Сделайте так, чтобы внешний select не обращался к одним и тем же блокам в различные моменты времени по ходу обработки. Для этого:
7. Используйте полный просмотр таблицы вместо поиска по индексу
8. Добавьте избыточную сортировку, так, чтобы все данные извлекались, сортировались, а затем полученные блоки данных просматривались последовательно.

СИТУАЦИЯ 2 – ПЕРЕПИСАН СЛОТ ТРАНЗАКЦИИ В СЕГМЕНТЕ ОТКАТА

1. Используйте любой из представленных выше методов, кроме 6. Это позволит распределить результаты работы транзакций по сегментам отката, уменьшая тем самым вероятность использования всех слотов таблицы транзакций сегмента отката.
2. Если предполагается, что ошибка так или иначе связана с очисткой блока, принудительно вызовите очистку блока до начала транзакции, возвращающей сообщение об ошибке ORA-1555 . Этого можно добиться с помощью следующих операторов в среде SQL*Plus, SQL*DBA или Server Manager: Если происходит обращение к индексам, проблема может быть связана с блоком индекса, и очистку можно вызвать принудительно, если выполнить полный просмотр индекса. Например, если индекс создан по числовому столбцу с минимальным значением 25, следующий запрос вызовет полную очистку индекса:

Ниже представлены примеры на языке PL/SQL, иллюстрирующие описанные выше ситуации examples, приводящие к получению сообщения об ошибке ORA-1555 . Чтобы данные примеры выдавали это сообщение об ошибке, сервер необходимо сконфигурировать следующим образом:

Использовать маленький буферный кэш ( db_block_buffers ).

ПРИЧИНА: Нужно, чтобы сеанс, выполняющий сценарий, не мог найти старые версии блока в буферном кэше, по которым можно было бы получить нужный образ блока без использования данных отката.
Используйте один сегмент отката, не совпадающий с SYSTEM .

ПРИЧИНА: Необходимо гарантировать, что при выполнении транзакции генерируются данные отката, которые будут переписывать необходимые запросу данные отката.
Используйте небольшой сегмент отката.

ПРИЧИНА: См. причину использования одного сегмента отката.

ПЕРЕЗАПИСЬ ДАННЫХ ОТКАТА

ПЕРЕЗАПИСЬ СЛОТА ТРАНЗАКЦИИ В СЕГМЕНТЕ ОТКАТА

Специальные случаи

Есть и другие специальные случаи, которые могут привести к выдаче сообщения об ошибке ORA-01555 . Они перечислены ниже, но крайне редки и в данной статье не обсуждаются:

• Сервер Trusted Oracle может вернуть его, если сконфигурирован в режиме OS MAC. Уменьшение значения LOG_CHECKPOINT_INTERVAL во вторичной базе данных, может решить эту проблему.
• Если запрос обращается к блоку данных, измененному с помощью средств дискретных транзакций Oracle, сервер вернет сообщение ORA-01555 .
• Вполне возможно, что сегмент отката, созданный с опцией OPTIMAL , может привести к возврату запросом сообщения об ошибке ORA-01555 , если в ходе выполнения запроса размер сегмента уменьшится, что вызове потерю данных отката, необходимых для получения согласованных по чтению версий блоков.

Резюме

В этой статье обсуждаются причины возникновения сообщения об ошибке ORA-01555 "Snapshot too old" , представлен список возможных методов предотвращения этой ошибки, а также даны простые сценарии на языке PL/SQL, иллюстрирующие рассмотренные ситуации.

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2002 Oracle Corporation

Результаты опроса подписчиков. Возможно, первый из серии авторских выпусков, посвященных индексам. Или перевод очередного блестящего ответа Тома Кайта. Следите за новостями на сайте проекта Open Oracle.

вторник, 24 февраля 2009 г.

Oracle Сегменты отката

В oracle 8i все было так :

Создание сегмента отката

. Все экстенты сегмента отката в БД должны быть одинаковы

при создании используются следующие параметры:

initial – размер начального экстента сегмента отката

next – размер следующего экстента сегмента отката
указывайте его равным initial

minextents – минимальное число экстентов в сегменте отката
должно быть больше или равно 2

maxextents – максимально разрешенное число экстентов в сегменте отката
ставте число а не unlimited
особенно полезно это для autoextend

optimal – общий оптимальный размер сегмента отката
он не меньше чем initial*minextents, так как initial=next

create rollback segment rollseg01
tablespace orgdbrbs
storage (
initial 10k
next 10k
minextents 20
maxextents 450
optimal 300k
);

alter rollback segment имя online;

Отключить сегмент отката можно только если в нем нет
активных экстентов с незафиксированными транзакциями.

alter rollback segment имя offline;

Изменим параметры
(можно менять все параметры за исключением initial)
изменение параметра next не изменит размер уже существующих
экстентов и делать это не рекомендуется

alter rollback segment rollseg01
storage ( maxextents 200
optimal 310k );

Удалить сегмент отката

drop rollback segment rollseg01;

Вручную сузить область область, отведенную сегменту отката до размера
не меньше чем указано в optimal

alter rollback segment rollseg01 shrink to 220k;

по умолчанию сегмент отката сжимается до optimal

alter rollback segment rollseg01 shrink;

Оракл не уменьшает размер сегмента откатаб если активны его экстенты,
размер которых превышает указанный

select r.usn, r.name, ((sum(e.blocks)+min(s.curblk))*min(p.value))/(1024*1024) Mb
from v$rollname r, v$rollstat s, v$parameter p, dba_extents e
where p.name=’db_block_size’
and s.usn=r.usn
and s.usn in (select xidusn from v$transaction)
and e.segment_type=’ROLLBACK’
and e.segment_name=r.name
and e.extent_ >group by segment_type, segment_name, r.name, r.usn;

select a.name,
b.xacts,
b.extents,
b.rssize,
b.optsize,
b.hwmsize,
b.status
from v$rollname a,
v$rollstat b
where a.usn=b.usn

select a.username,
a.sid,
a.serial#,
b.name as rbsname,
c.used_ublk
from v$session a,
v$rollname b,
v$transaction c
where a.saddr=c.ses_addr
and b.usn=c.xidusn;

Не закоммиченная транзакция
такого пользователя нужно убить
оракл откатит убитую транзакцию

select a.username,
a.sid,
a.serial#,
to_char(b.start_time,’MM/DD/YY HH24:MI:SS’) as start_time,
c.name
from v$session a,
v$transaction b,
v$rollname c,
v$rollstat d
where a.saddr=b.ses_addr
and b.x >and c.usn=d.usn
and ((d.curext=b.start_uext-1) or
((d.curext=d.extents-1) and b.start_uext=0))

select s.username,
s.sid,
s.serial#,
t.start_time, t.xidusn
from v$session s,
v$transaction t,
v$rollstat r
where s.saddr=t.ses_addr
and t.x > and ((r.curext=t.start_uext-1)
or
((r.curext=r.extents-1)
and (t.start_uext=0)));

dba_rollback_segs – информация о всех сегментах отката

v$rollstat – размер, текущее число экстентов, наивысшая точка,
оптимальный размер

v$rollname – имя сегмента отката и его номер,
соответствующий записям в v$rollstat

v$transaction – адрес сеанса и использование сегментов
отката текущими транзакциями

v$session – имя sid и порядковый номер адреса сеанса,
соответствуют адресу сеанса в v$transaction

ORA-01562 insufficient space in rollback segment
недостаточно места в сегменте отката

ORA-01560 insufficient space in the tablespace
недостаточно места в табличной области

ORA-01628 MAXEXTENTS has been reached
достигнуто значение MAXEXTENTS

ORA-01555 snapshot to old (rollback segment to small)
моментальный снимок слишком стар
сегмент отката слишком мал
простое повышение maxextents тут не поможет
тут нужно повысить minextents и optimal пересоздав сегмент отката

select s.username, s.sid, rn.name, rs.extents
,rs.status, t.used_ublk, t.used_urec
,do.object_name
from v$transaction t
,v$session s
,v$rollname rn
,v$rollstat rs
,v$locked_object lo
,dba_objects do
where t.addr = s.taddr
and t.x > and rn.usn = rs.usn
and t.x > and do.object_ >

To monitor rollback usage (waits/gets should be and r.usn = n.usn (+)
order by 1

undo_management=auto and bounce your database:

CREATE UNDO TABLESPACE ts_undo
DATAFILE ‘/u01/oradata/prod/ts_undo.dbf’
SIZE 2M REUSE AUTOEXTEND ON;

Alter system set undo_tablespace=ts_undo;
Alter system set undo_management=auto;