Oracle ошибка 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 ID: 738wa64wpd5s2, 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). Т.е. на время экспорта лучше остановить все службы, все задания и т.п.

Источник

Содержание

Oracle error ora 01555
Oracle error ora 01555
ORA-01555 ERROR MESSAGE “SNAPSHOT TOO OLD”
Answers
Oracle / PLSQL: ORA-01555 Error Message
Description
Cause
Resolution
Option #1
Option #2
Option #3
Oracle error ora 01555
Введение
Общие положения
ПРОБЛЕМА ORA-01555 : snapshot too old ( rollback segment to small )

Oracle error ora 01555

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

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 еще есть свободное место. Сколько его.

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

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

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

SELECT MAX(MAXQUERYLEN) FROM V$UNDOSTAT;

Источник

Oracle error ora 01555

Error ORA-01555 contains the message, “snapshot too old.”

This message appears as a result of an Oracle read consistency mechanism. While your query begins to run, the data may be simultaneously changed by other people accessing the data. Oracle cannot access the original copy of the data from when the query started, and the changes cannot be undone by Oracle as they are made. Both committed versions of blocks and uncommitted versions of blocks are maintained to ensure that queries can access the data as it exists in the database at the time of the query. This is referred to as “consistent read” blocks and is maintained by Oracle Automatic Undo Management (AUM).

For example, you may begin your SQL query at 1:00 PM, yet at the same hour, another user may be making changes to the data from another computer. If this occurs, you may encounter error ORA-01555 because the results outputted by Oracle must contain data as it appeared at 1:00PM before changes were made by the other user.

ORA-01555 relates to insufficient rollback segments or undo_retentions parameter values that are not large enough. The modified data by performed commits and rollbacks causes rollback data to be overwritten when the rollback segments are smaller in size and number of the changes being performed at the time.

To resolve this issue, either increase the parameter of UNDO_RETENTION if you are in AUM mode or use larger rollback segments. The latter solution will allow your rollback data for completed transactions to be kept longer.

You may also run into this error when cursors are not being in programs after FETCH and UPDATE statements. Make sure you are closing cursors when you no longer need them. The error can also appear if a FETCH statement is run after a COMMIT statement is issued. If this occurs, you will begin to overwrite earlier records because the number of rollback records created since the last CLOSE will fill the rollback segments.

In summary, follow these practices to avoid seeing error ORA-01555 in the future:

Do not run discrete queries and sensitive queries simultaneously unless the data is mutually exclusive.
If possible, schedule queries during off-peak hours to ensure consistent read blocks do not need to rollback changes.
Use large optimal values for rollback segments.
Use a large database block size to maximize rollback segment transaction table slots.
Reduce transaction slot reuse by performing less commits, especially in PL/SQL queries.
Avoid committing inside a cursor loop.
Do not fetch between commits, especially if the data queried by the cursor is being changed in the current session.
Optimize queries to read fewer data and take less time to reduce the risk of consistent get rollback failure.
Increase the size of your UNDO tablespace, and set the UNDO tablespace in GUARANTEE mode.
When exporting tables, export with CONSISTENT = no parameter.

Источник

ORA-01555 ERROR MESSAGE “SNAPSHOT TOO OLD”

Our production db size is 8 TB and we having to BCV copy of production db (read only mode) on different servers. One of the bcv copy we received above said error. undo setting like

undo_management=auto; undo_retention=5400; undo tablespace free space:=80GB;

All the indexes for error tables was in «valid» state. But as per the oracle recomandation we rebuild the indexes. mainly primary key having the issue.

Till today above error only occurred on BCV copy( but received only at one copy. Another copy doesn’t occurred error ). But today we got above error on production db.(error received at application side. not recorded in alert file).

My question is why this error occured , when all indexes in valid stage , enough free space for undo?

And how to find which indexes are causing the problem in future?

Answers

The alert log should show you the statement that hit the ora-1555, with other details. What does it show? Often tuning the statement (or process it is part of) can be the answer.

(btw, some people here get a bit upset at the idea of rebuilding indexes. Be prepared for a flaming.)

The doco says this:

ORA-01555: snapshot too old: rollback segment number string with name » string » too small

Cause: rollback records needed by a reader for consistent read are overwritten by other writers

Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

Rebuilding the indexes to solve a 1555 error is contentious, and you would need to reference where Oracle recommends to do this.

Thanks John for reply..

below statement find in the alert log file.

ORA-01555 caused by SQL statement below (SQL ID: bwds9kc34zat8, Query Duration=20366 sec, SCN: 0x0013.4c57fcf1):

Come on, man — what use is that? You have the SQL id, so find the SQL statement. If it isn’t in v$sql, it will be in your statspack or AWR reports.

Yes, we have that sql.

ORA-01555 isn’t usually related to rebuilding indexes. Maybe the original BCV scenario changes that.

ORA-01555 usually means that you don’t have the necessary undo to generate a read consistent result set of time T when the query started.

There are a whole bunch of complexities that can add to that generalisation.

The salient point is this:

Query Duration=20366 sec

You don’t have the undo required to provide a read consistent image of over 5.5 hours ago.

Only questions are:

1. Was it really running for that long? Sometimes people open a query in SQL Developer, fetch the first set of results, forget about it, come back hours/days later and page down and the fetch of the next set of results.

2. And if it was running that long, is it a normal query which suddenly got an abnormal execution or just a one-off bit of bad SQL?

If your undo retention is already reasonable and the query execution time is not reasonable then your normal next course of action would be to look at why the query ran so long rather than looking to increase undo.

The SELECT statement is the victim; not the culprit.

The SELECT requires a read consistent view of the data in the table.

When some session does DML against the same table the SELECT must read the UNDO to obtain the «old» values.

When the session that does DML & it also does COMMIT, the COMMIT informs Oracle that these UNDO block no longer need to be protected & reserved for the DML transaction.

Then if/when Oracle decides to reuse the «freed» UNDO block & the SELECT needs to read the UNDO content, it can’t so generates ORA-01555.

So if other session did not do DML & COMMIT, then SELECT would have no problem reading the data.

ORA-01555 results when DML & COMMIT occur & the UNDO gets reused by some session, therefore SELECT can not get a Read Consistent view of the data.

The UNDO exists to protect the DML transaction. The COMMIT allows the UNDO block to be reused, since COMMIT terminates the DML transaction.

The SELECT statement is just collateral damage. As long as no COMMIT is issued, the UNDO remains sacrosanct & SELECT does not throw ORA-01555 error.

Источник

Oracle / PLSQL: ORA-01555 Error Message

Learn the cause and how to resolve the ORA-01555 error message in Oracle.

Description

When you encounter an ORA-01555 error, the following error message will appear:

ORA-01555: snapshot too old (rollback segment too small)

Cause

This error can be caused by one of the problems, as described below.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error can be the result of there being insufficient rollback segments.

A query may not be able to create the snapshot because the rollback data is not available. This can happen when there are many transactions that are modifying data, and performing commits and rollbacks. Rollback data is overwritten when the rollback segments are too small for the size and number of changes that are being performed.

To correct this problem, make more larger rollback segments available. Your rollback data for completed transactions will be kept longer.

Option #2

This error can be the result of programs not closing cursors after repeated FETCH and UPDATE statements.

To correct this problem, make sure that you are closing cursors when you no longer require them.

Option #3

This error can occur if a FETCH is executed after a COMMIT is issued.

The number of rollback records created since the last CLOSE of your cursor will fill the rollback segments and you will begin overwriting earlier records.

Источник

Oracle error ora 01555

Введение

По данной теме сказано и написано немало. Достаточно вспомнить статью Ленг Тана “Наиболее важные аспекты управления транзакциями и сегментами отката” (“Oracle Magazine/Русское Издание” №1, 1997). В данной работе хотелось бы поделиться опытом по решению одной довольно часто встречающейся проблемы – это известная ошибка: ORA-01555: snapshot too old (rollback segment too small) .

Общие положения

Известно, что при совместной работе нескольких транзакций, а именно в случаях одновременного выполнения запросов и изменения одних и тех же таблиц, для обеспечения согласованного чтения (consistent read) должны использоваться “старые” значения данных из строк читаемых таблиц, которые сохраняются в сегментах отката базы данных.

На рис.1 показано, как обеспечивается согласованность по чтению на уровне предложения с использованием данных в сегменте отката, исходя из значения SCN (system change number) — системного номера изменения, то есть последовательного номера состояния базы данных.

Когда запрос входит в фазу выполнения, выбирается текущий SCN (system change number). На рис.1 этот SCN равен 10023. При выборке данных в ходе запроса непосредственно читаются лишь те блоки данных, которые были записаны с меньшим, то есть более старым значением SCN. Блоки с изменившимися в ходе выполнения этого запроса данными, то есть блоки с большими значениями, новыми SCN, реконструируются с помощью данных, сохраненных в сегменте отката. Тем самым запрос возвращает пользователю “старые”, уже зафиксированные в базе на момент начала запроса реконструированные данные. Данные, как завершенных, так и незавершенных транзакций, изменившиеся за время выполнения запроса, не наблюдаются этим запросом, что гарантирует согласованность множества данных, видимых каждым запросом.

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

идентификатор транзакции;
идентификатор файла;
идентификатор блока;
номер строки;
номер колонки;
данные, которые были до изменения.

Обращение к блокам данных сегментов отката происходит через кэш буфера базы данных (buffer cache) SGA, эти блоки переписываются в журнальные файлы (redo log). Они могут понадобиться для процедур отката незавершенных транзакций (rollback) в случае восстановления экземпляра (instance recovery) после аварийного завершения, сбоя, в случае восстановления базы данных (media recovery).

Когда транзакция не находит свободных блоков внутри экстента сегмента отката, используется следующий по порядку экстент этого сегмента. Если текущий экстент является последним в сегменте, тогда данные записываются в первый экстент сегмента отката. Если первый экстент окажется целиком занятым (in use) данными отката незавершенных в настоящий момент транзакций, то выделяется новый экстент, и данные отката записываются в этот новый экстент.

Процесс выбора экстентов проиллюстрирован на рис.2. Допустим, Транзакция А уже использовала четыре экстента (3,4,5,6) для данных отката (рис.2, случай (а)) и ей ещё требуется место для данных отката. Так как транзакция уже использовала последний экстент сегмента, проверяется, не занят ли первый экстент сегмента отката активными данными (active data) отката других незавершенных транзакций. Если экстент свободен, то он будет использован как следующий — пятый экстент для Транзакции А (рис.2.b). Если же первый экстент используется другой незавершенной транзакцией (содержит активные данные), тогда сегмент отката расширяется. Новый экстент 7 будет использован как пятый экстент для Транзакции А (рис.2.с).

ПРОБЛЕМА ORA-01555 : snapshot too old ( rollback segment to small )

В некоторых случаях, когда одновременно выполняются, и запросы, и изменения одних и тех же таблиц, согласованное множество данных (часто называемое снимком — snapshot) не может быть сформировано для долго выполняемого запроса. Этот феномен объясняется тем, что в сегментах отката оказывается недостаточно информации для того, чтобы реконструировать “старые” данные. Допустим, что Транзакция А завершилась. Использованные этой транзакцией блоки сегмента отката помечаются как неактивные (inactive). Данные Транзакции А не удаляются физически из сегмента отката, и могут быть использованы для создания согласованного (consistent) образа данных для Транзакции Б, которая начала выполняться до того, как произошло фиксирование данных Транзакции А. Проблема же заключается в том, что эти блоки могут быть перезаписаны другими транзакциями (в том числе и Транзакцией Б), даже в том случае, если «длинный» запрос еще не успел прочитать нужные “старые” блоки. У сервера Oracle нет информации о том, каким активным транзакциям (еще не завершившимся) могут понадобиться данные в уже неактивных блоках сегмента отката, и для более эффективного использования ресурсов вместо выделения очередного экстента используются освободившиеся (ставшие неактивными) блоки.

В ходе выполнения транзакции Oracle “сбрасывает” на диск модифицированные записи, помечая соответствующие блоки данных. Когда транзакция завершается, Oracle делает быстрый commit, отмечая транзакцию, как inactive, в таблице транзакций (TX table), которая находится в заголовке сегмента отката, но не снимает при этом отметки в блоках данных обычных таблиц. Следующая транзакция, который делает выборку из этих (dirty – грязный, модифицированный) блоков, фактически производит очистку (cleanout) блоков от пометок, сделанных предыдущей транзакцией. Это действие известно, как delayed block cleanout. Каждый блок Oracle содержит ITL — область, состоящая из слотов, в которых записаны сведения о транзакциях (каждый слот занимает 23 байта). Начальное количество слотов ITL определяется значением параметра INITRANS, а реальное — максимальным количеством транзакций, одновременно когда-либо обрабатывающих записи данного блока, но не меньше INITRANS и не больше MAXTRANS. Пусть, например, INITRANS равно 2, а час назад записи блока одновременно обновляли 5 транзакций. В этом случае требуется и выделяется 5 слотов, и после число слотов не уменьшается до 2. Слоты используются циклически.Таким образом, Oracle хранит сведения о пяти (в данном примере) транзакциях для этого блока. Обратим внимание, что это только ссылки на сегменты отката, а SCN, хранящиеся в TX-таблицах сегментов отката, могут быть уже затерты, как могут быть затерты и сами данные в сегментах.

Когда транзакция читает блок с нестертой(тыми) отметкой модификации, Oracle определяет, завершилась ли (по таблицам транзакций ТХ) транзакция, сделавшая эту отметку. Если да, то прочитанные данные зафиксированы (был выполнен commit), данными можно пользоваться, следует стереть отметку модификации. Если нет, то данными пользоваться нельзя, необходимо выбрать “старые” значения, сохраненные в сегменте отката. Если в этом случае запись о транзакции-модификаторе отсутствует в ТХ-таблице, то тогда и возникает ошибка ORA-1555.

[Прим. редактора: Механизм этого явления чуть-чуть тоньше и красивее. Поскольку каждый табличный блок данных заполнен в общем случае по разному, то для разных блоков одной и той же таблицы может быть построено разное число слотов ITL. Из этого, по крайней мере, теоретически (практически это обнаружить, думаю, довольно сложно) следует, что на одной и той же таблице в разные моменты времени одновременно может выполняться разное максимальное число транзакций. Далее. Поскольку единицей действия в базе данных Oracle является запись, то каждая запись в блоке связывается с одним из слотов ITL, хранит его номер, при помощи специального байта в заголовке каждой записи. Отсюда и следует ограничение значения параметра MAXTRANS – 255.]

[Прим. редактора(2): Не успели еще опубликовать эту статью, как по почте уже пришел вопрос, как раз описывающий ситуацию то с “зависанием”, то без “зависания” транзакций на одной и той же таблице, но на разных блоках. Механизм INITRANS сработал во всей красе. Внимание! Изменение значения параметра INITRANS начинает действовать только со следующего распределенного экстета, поэтому если обнаружились “зависания” транзакций по причине недостаточного значения параметра INITRANS, таблицу рекомендуется пересоздать.]

Рассмотрим две таблицы. Одна таблица была изменена, и транзакция по ней завершилась. По этой таблице открыт курсор, и затем в цикле начинает изменяться другая таблица. Казалось бы, здесь все корректно. Но, так как cleanout для первой таблицы не был сделан, для нее может возникнуть ORA-1555, так как блоки, необходимые для cleanout, в сегменте отката могли быть уже затерты транзакцией, модифицирующей вторую таблицу. Для предотвращения подобной проблемы Oracle рекомендует перед открытием курсора осуществлять полное сканирование таблицы, чтобы последующие чтения не осуществляли обращения к сегментам отката.

Существует несколько стандартных способов борьбы с данной сбойной ситуацией:

создание большего числа сегментов отката;
создание большого сегмента отката и выделение длинным транзакциям этих сегментов отката

( SET TRANSACTION USE ROLLBACK SEGMENT );

планирование по времени выполнения приложений — разделение приложений с длинными запросами (режим OLTP) и приложений, выполняющих массовые обновления данных (режим «batch processing»).

Применение этих рекомендаций снижает вероятность появления ошибки «ORA-01555: Snapshot too old». Но эти методы решают проблему не полностью, так как в любой момент необходимые неактивные блоки сегмента отката могут быть перезаписаны. Единственный способ выйти из этой ситуации — это избежать перезаписи в сегменте отката определенного количества экстентов с неактивными блоками данными отката.

Один нестандартный способ был обнаружен в ходе эксплуатации некоего приложения, которое производило массовое обновление данных. Были перепробованы все варианты, но ситуация не становилась более стабильной. Грустное сообщение ORA-01555 время от времени появлялось снова. Был найден фрагмент в коде приложения, который потенциально мог быть причиной сбоя. Открытый курсор читал данные и передавал их в процедуру, которая и занималась собственно загрузкой. При этом после выхода из нее происходила модификация данных, которые попадали в область выборки курсора. Поиск подобных мест — достаточно трудная задача, а изменение логики работы, возможно, даже не разрешимая. Поэтому в качестве последнего варианта для работы этого приложения интуитивно было предложено создать сегмент отката с большим начальным размером, т.е. с большим минимальным количеством экстентов (параметр MINEXTENTS). Замечу, что параметр OPTIMAL, во избежание схлопывания этого сегмента отката, убрали уже давно. Подобная манипуляция сразу привела к положительному результату – загрузка завершилась успешно.

После анализа ситуации пришли к следующему выводам:

Oracle не использует неактивные блоки в экстентах сегмента отката до тех пор, пока есть свободные неиспользованные экстенты до размера MINEXTENTS;
работа транзакций в пределах MINEXTENTS сегмента отката может решить проблему перезаписи (для определенных объемов потребности транзакций), но чрезмерный расход дисковой памяти делает этот подход весьма неэффективным;
наиболее предпочтительным может быть решение, основанное на эффекте “блокирующей транзакции”.

Этот эффект базируется на том, что Транзакция В, которая началась раньше Транзакции А, имеет активные блоки в третьем экстенте (рис.3).

Когда Транзакция А займет все экстенты до MINEXTENTS, включается алгоритм поиска неактивных блоков, и транзакция займет экстент 1 и 2. Занять 5-й экстент транзакция не сможет, так как после 2-го экстента идет 3-й, занятый транзакцией В, который является активным. В результате будет выделен 6-й экстент за границей MINEXTENTS.

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

Известный специалист по технологиям Oracle Стив Адамс (Steve Adams), руководитель консалтинговой компании Ixora Pty Ltd (www.ixora.com.au), предложил красивое решение. Общая идея, излагаемая Адамсом, – это динамическое создание блокирующих транзакций для каждого сегмента отката. Транзакции модифицируют специальную таблицу и не делают commit в течение определенного времени, указанного при запуске. В течение этого времени в базе данных начинают работать все необходимые транзакции, требующие активного использования сегментов отката.

Технология блокирующих транзакций раскрывается в прилагаемых скриптах 1 — 4. После них приводятся еще три скрипта, близкие к рассматриваемой теме.

“Oracle 8i Concepts”, Oracle corp., 1999
“Oracle 8i Administrator’s Guide”, Oracle corp., 1999
“Oracle 8i Application Developer’s Guide–Fundamentals”, Oracle corp.,1999
“Oracle DBA Handbook” Kevin Loney, Oracle Press, 1994
“Oracle Magazine (русское издание)”, 1(3)1997
“Oracle8i Internal services for Waits, Latches, Locks and Memory” Steve Adams, O’Reily,1999.

Борис Скворцов ,
отдел администрирования серверов баз данных,
Главный центр информатизации Банка России
sbg@gci.cbr.ru )

Скрипт 7. Мониторинг использования сегментов отката

Колонка главного редактора:

Oracle открывает третье тысячелетие.

Письмо в редакцию

Человек месяца: Беседа с руководителями ИВЦ АИС и WEB-центра “Омега”
Oracle: от МВД до РПЦ

Источник


	Материал номера: Новый тест для специалистов по Oracle

Источник

�� Oracle �� : �� ORA-01555

�� 26

�� ! �� «��» ��
ORA-1555: snapshot too old. �� , ��
�� …

Snapshot too old

��!

�� , �� snapshot too old.
�� ? �� ? �� ?

�� .

��

�� , �� <Note:40689.1> ��
�� :

ORA-01555 «Snapshot too old» — ��

��

� �� , ��
�� ORA-01555 «snapshot too old (rollback segment too small)».
�� , ��
�� , ��, �� PL/SQL,
�� .

��

��, �� Oracle, ��
«�� » � «SCN». � �� , ��
�� Oracle Server Concepts � �� Oracle.

�� , �� , ��
�� ORA-01555:

1. �� :

�� Oracle Server Concepts � ��
��. ��
�� , �� .

�� Oracle �� , ��
��, ��
(�� «�� »).

2. �� :

�� : �� ,
�� . �� , ��,
�� . ��
��, �� Oracle ��
��, �� . ��
��, �� , �� , — ��
«��» �� (�� «�� «).

�� Oracle (�� , ��,
��), �� , ��
�� , ��
��, �� . (�� , ��
�� «��».)

��
�� . ��, �� ,
�� Oracle �� , ��, ��
�� . �� , ��
�� . �� Oracle ��,
�� (�� ),
� �� , ��
�� .

�� , �� , �� ,
�� .

�� . ��
�� .

�� 1 — ��

��:	�� . � �� , �� �� (�� ‘tx‘), � � �� , � �� � �� , �� . � �� (01 � 02), � �� �� — �� 03. (�� ��.)

���� ������ 500            ��������� �������� ������ 5
+----+--------------+      +----------------------+---------+
| tx | ���          |      | ������ ���������� 01 |ACTIVE   |
+----+--------------+      | ������ ���������� 02 |ACTIVE   |
| ������ 1          |      | ������ ���������� 03 |COMMITTED|
| ������ 2          |      | ������ ���������� 04 |COMMITTED|
| ... ..            |      |     ...     ...   .. |  ...    |
| ������ n          |      | ������ ���������� nn |COMMITTED|
+-------------------+      +--------------------------------+

�� 2 — �� 2

��:	�� 2 �� 500. �� , �� �� 5, �� 3 (5.3), � �� (Active).

���� ������ 500            ��������� �������� ������ 5 
+----+--------------+      +----------------------+---------+
| tx |5.3uncommitted|-+    | ������ ���������� 01 |ACTIVE   |
+----+--------------+ |    | ������ ���������� 02 |ACTIVE   |
| ������ 1          | +--->| ������ ���������� 03 |ACTIVE   |
| ������ 2 *���.*   |      | ������ ���������� 04 |COMMITTED|
| ... ..            |      |     ...     ...   .. |  ...    |
| ������ n          |      | ������ ���������� nn |COMMITTED|
+-------------------+      +--------------------------------+

�� 3 — ��

��:	�� . ��, �� �� �� — �� . � �� ��.

���� ������ 500             ��������� �������� ������ 5 
+----+--------------+       +----------------------+---------+
| tx |5.3uncommitted|--+    | ������ ���������� 01 |ACTIVE   |
+----+--------------+  |    | ������ ���������� 02 |ACTIVE   |
| ������ 1          |  +--->| ������ ���������� 03 |COMMITTED|
| ������ 2 *���.*   |       | ������ ���������� 04 |COMMITTED|
| ... ..            |       |     ...     ...   .. |  ...    |
| ������ n          |       | ������ ���������� nn |COMMITTED|
+-------------------+       +--------------------------------+

�� 4 — �� 500

��:	�� (�� ) �� 500. ��, ��, � �� �� , � �� . �� Oracle �� �� , ��, �� , � �� 500 ��, �� � �� . (�.�., �� �� ).

���� ������ 500            ��������� �������� ������ 5 
+----+--------------+      +----------------------+---------+
| tx | ���          |      | ������ ���������� 01 |ACTIVE   |
+----+--------------+      | ������ ���������� 02 |ACTIVE   |
| ������ 1          |      | ������ ���������� 03 |COMMITTED|
| ������ 2          |      | ������ ���������� 04 |COMMITTED|
| ... ..            |      |     ...     ...   .. |  ...    |
| ������ n          |      | ������ ���������� nn |COMMITTED|
+-------------------+      +--------------------------------+

�� ORA-01555

�� ORA-01555, ��
�� Oracle �� «�� »
�� :

�� , �� Oracle ��
(��) �� .
�� (��
�� ) ��, � �� Oracle ��
�� , ��
�� .

�� , �� ,
�� ORA-01555. ��
�� «QENV». «QENV» (�� «Query Environment» — ��
��) — �� , ��
�� Oracle �� . �
�� SCN (System Change Number — ��
��) � �� , �� QENV 50 — ��
�� SCN 50.

�� 1 — ��

�� : �� ,
�� , � �� ,
�� . �� ,
�� .

��:

�� 1 �� T1 �� QENV 50
�� 1 �� B1 � ��
�� 1 �� SCN 51
�� 1 �� , �� .
�� , �� 3 � 4.

(�� )
�� B1 (��, � �� ).

�� Oracle �� , �� , ��,
��, �� QENV (�� SCN 50). ��
�� QENV.

�� , ��
��, �� , ��
��, �� QENV.

�� Oracle ��
��, �� 1 �� ,
�� , � ��
�� ORA-1555.

�� 2 — ��

�� 1 �� T1 �� QENV 50
�� 1 �� B1 � ��
�� 1 �� SCN 51
�� 1 �� .

(�� )
�� (�� 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).
�� , � ��
(�� , �� 1).
�� . ��
�� , �� , ��
�� .
�� , ��
�� .
�� , �� select ��
� �� . �� :
- ��
- �� , ��, �� ,
  ��, � ��
  ��.

�� 2 — ��

�� , �� 6. ��
�� , ��
�� .
�� , �� ,
�� , ��
�� ORA-1555. ��
� �� SQL*Plus, SQL*DBA �� Server Manager:
```
      alter session set optimizer_goal = rule;
      select count(*) from table_name;
```
�� , ��
��, � �� , ��
��. ��, ��
�� 25, �� :
```
      select index_column from table_name where index_column > 24;
```

��

�� PL/SQL, ��
�� examples, �� ORA-1555.
�� , ��
�� :

�� (db_block_buffers).
��: ��, �� , �� , ��
�� , ��
�� .
�� , �� SYSTEM.
��: �� , ��
�� , �� .
�� .
��: ��. �� .

��

rem * 1555_a.sql - 
rem * ������ ��������� ��������� ora-1555 "Snapshot too old"
rem * �������, �������������� ������ ������, �����������
rem * ��� ������. 

  drop table bigemp;
  create table bigemp (a number, b varchar2(30), done char(1));

  drop table dummy1; 
  create table dummy1 (a varchar2(200));

  rem * ���������� ������ �������.
  begin
   for i in 1..4000 loop
     insert into bigemp values (mod(i,20), to_char(i), 'N');
     if mod(i,100) = 0 then
       insert into dummy1 values ('ssssssssssss');
       commit;
     end if;
   end loop;
   commit;
  end;
  /

  rem * ����������� "�������" �������. 
  select count(*) from bigemp;

  declare
   -- ���������� ������������ ����� �������, ����� �� ����� ���������� �
   -- ����������� ����� � ������ ������ �������. 

   -- ���� ������ ���������� �������� �������, ��� ������� ����� � ��
   -- ������������.
   cursor c1 is select rowid, bigemp.* from bigemp where a < 20;

  begin
   for c1rec in c1 loop

     update dummy1 set a = 'aaaaaaaa';
     update dummy1 set a = 'bbbbbbbb';
     update dummy1 set a = 'cccccccc';
     update bigemp set done='Y' where c1rec.rowid = rowid;
     commit; 
   end loop;
  end;
  /

��

  rem * 1555_b.sql - ������ ��������� ��������� ora-1555 "Snapshot too old"
  rem *              ��� ���������� ����� ���������� � ��������� �������� ������.
  rem *              ��� ���� ������������ ����� ���� �����.

  drop table bigemp;
  create table bigemp (a number, b varchar2(30), done char(1));

  rem * ��������� ������� ���������������� �������.
  begin
   for i in 1..200 loop
     insert into bigemp values (mod(i,20), to_char(i), 'N');
     if mod(i,100) = 0 then
       commit;
     end if;
   end loop;
   commit;
  end;
  /

  drop table mydual;
  create table mydual (a number);
  insert into mydual values (1);
  commit; 

  rem * ������� ���������������� �������.
  select count(*) from bigemp;

  declare

   cursor c1 is select * from bigemp;

  begin

   -- ��������� ��������� ����������, ����� ����������������� ��������, 
   -- �����������, ���� ���� ��������� ������� ������ ������� bigemp. ����
   -- ���������������� (�������������� ����) ��������, ����������� �������, 
   -- �� ����� ����� ���������������� ����� ��������� update � commit, �
   -- �������� ���������� � ������� ORA-1555 ��-�� ������� �����.
   update bigemp set b = 'aaaaa';
   commit;

   for c1rec in c1 loop
     for i in 1..20 loop
       update mydual set a=a;
       commit;
     end loop; 
   end loop; 
  end;
  /

��

�� , ��
�� ORA-01555. �� , ��
�� :

�� Trusted Oracle �� , �� OS MAC.
�� LOG_CHECKPOINT_INTERVAL �� ,
�� .
�� , ��
�� Oracle, �� ORA-01555.
�� , �� , �� OPTIMAL, ��
�� ORA-01555, ��
�� , �� ,
�� .

��

� ��
ORA-01555 «Snapshot too
old», ��
�� , � ��
PL/SQL, �� .

��
��.

� ��

�� . ��, �� , ��
��. �� … ��
�� Open Oracle.

� �� ,

�.�.

Источник

An ORA-01555 error can occur on the Oracle database used by the RSA Identity Governance & Lifecycle product when it is trying to perform a query but does not find the read consistent data it is looking for. This is one of the prominent errors caused by Oracle’s read consistency model.

For more information, please review Oracle Support Note 40689.1 — ORA-01555 «Snapshot too old» — Detailed Explanation.

The following statements are taken from Note 40689.1:

ORA-01555 Explanation

There are two fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a ‘read consistent’ image. These are:

The rollback information itself is overwritten so that Oracle is unable to roll back the (committed) transaction entries to attain a sufficiently old enough version of the block.
The transaction slot in the rollback segment’s transaction table (stored in the rollback segment’s header) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.

The following solutions are from the Oracle Support Note 269814.1 — ORA-01555 Using Automatic Undo Management — Causes and Solutions (login to the Oracle Support portal to access this note).

The UNDO tablespace is too small.
Tune the value of the UNDO_RETENTION parameter.
Enable retention guarantee for the Undo tablespace.
Calculate the size of the UNDO tablespace.

There are also solutions for ORA-01555 errors in specific circumstances, where there are Oracle Support notes available. Given that the steps are quite detailed, please view these notes on the Oracle Support portal.

Oracle Support Note 1950577.1 — IF: ORA-1555 Reported with Query Duration = 0 , or a Few Seconds
Oracle Support Note 846079.1 : LOBs and ORA-01555 troubleshooting and Note 452341.1 : ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption

However, the problem is most likely due to a long-running query, so the Oracle AWR report needs to be generated and examined to identify the long-running query. The ability to generate AWR requires licensing for Oracle Diagnostics Pack; for more details, refer to the article 000037217 — Licensing for Oracle Automatic Workload Repository (AWR) with RSA Identity Governance & Lifecycle.

Once the long-running query has been identified, the solution will be to determine why it is long-running. In some cases, it may be a known issue with the RSA Identity Governance and Lifecycle setup for the Oracle database, or it may be that large Collections need to be re-scheduled. If necessary, please log a case so that an RSA Support engineer can assist.

Please note that all the SQL statements in this section need to be run as SYSDBA, not as the AVUSER account. This is because the tables being accessed and the operations being performed need SYSDBA access. If you are unsure, please consult your Oracle DBA or engage an RSA Support Engineer.

1. The UNDO tablespace is too small

A method for determining the «number of bytes needed to handle a peak undo activity» is detailed in Oracle Support Note 262066.1 — How To Size UNDO Tablespace For Automatic Undo Management (login to the Oracle Support portal to access this note). However, for your convenience, here is the SQL.

For this SQL to return valid results, it needs to be run during peak workload; that is, a time similar to when the ORA-01555 error occurred.

Run the following command and note the output below:


SELECT (UR * (UPS * DBS)) AS "Bytes" FROM (select max(tuned_undoretention) AS UR from v$undostat),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS FROM dba_tablespaces WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
     Bytes 
---------- 
 269519503

The Undo Tablespace would need to be at least the calculated number of bytes. However, allow 10-20% when re-sizing or adding data files to the Undo Tablespace.

Next, show the sizes and Autoextend setting for the current Data Files used by the Undo Tablespace, along with the output. Note that the results below do not show a problem.


COL AUTOEXTENSIBLE FORMAT A14
SELECT FILE_NAME, BYTES/1024/1024 AS "BYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace');
FILE_NAME                                                                        BYTES (MB) AUTOEXTENSIBLE 
-------------------------------------------------------------------------------- ---------- -------------- 
/u01/app/oracle/oradata/AVDB/undotbs01.dbf                                              440 YES 
/u01/app/oracle/oradata/AVDB/undotbs02.dbf                                              128 YES 
/u01/app/oracle/oradata/AVDB/undotbs03.dbf                                              128 YES

Add the required space to the Undo Tablespace, as per Oracle Support Note 1951696.1 — IF: How to Resize the Undo Tablespace. The steps from section 2. Add Space to the Undo Tablespace from Note 1951696.1 have been reproduced here, for your convenience.
1. To resize the existing undo datafile:


col T_NAME for a23
col FILE_NAME for a65
SELECT tablespace_name T_NAME,file_name, bytes/1024/1024 MB FROM dba_data_files WHERE tablespace_name =(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace') ORDER BY file_name;

ALTER DATABASE DATAFILE '<COMPLETE_PATH_OF_UNDO_DBF_FILE>' resize <SIZE>M;

For example,


ALTER DATABASE DATAFILE 'D:ORACLE_DBTESTDBTESTDBUNDOTBS01.DBF' RESIZE 1500M;

Add a new datafile:


ALTER TABLESPACE <UNDO tbs name> ADD DATAFILE '<COMPLETE_PATH_OF_UNDO_DBF_FILE>' SIZE 300M;

For example,


ALTER TABLESPACE UNDOTBS1 ADD DATAFILE 'D:ORACLE_DBTESTDBTESTDBUNDOTBS02.DBF' SIZE 300M;

2. Tune the value of the UNDO_RETENTION parameter

The following is from Oracle Support Note 269814.1 — ORA-01555 Using Automatic Undo Management — Causes and Solutions. It is reproduced here for your convenience.

This is important for systems running long queries. The parameter’s value should at least be equal to the length of the longest-running query on a given database instance. This can be determined by querying V$UNDOSTAT view once the database has been running for a while:


SQL> SELECT MAX(maxquerylen) FROM v$undostat;

The V$UNDOSTAT view holds undo statistics for 10-minute intervals. This view represents statistics across instances, thus each begins time, end time, and statistics value will be a unique interval per instance. This view contains the following columns:

Column name	Meaning
BEGIN_TIME	The beginning time for this interval check
END_TIME	The ending time for this interval check
UNDOTSN	The undo tablespace number
UNDOBLKS	The total number undo blocks consumed during the time interval
TXNCOUNT	The total number of transactions during the interval
MAXQUERYLEN	The maximum duration of a query within the interval
MAXCONCURRENCY	The highest number of transactions during the interval
UNXPSTEALCNT	The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests
UNXPBLKRELCNT	The number of unexpired blocks removed from undo segments to be used by other transactions
UNXPBLKREUCNT	The number of unexpired undo blocks reused by transactions
EXPSTEALCNT	The number of attempts when expired extents were stolen from other undo segments to satisfy a space request
EXPBLKRELCNT	The number of expired extents stolen from other undo segments to satisfy a space request
EXPBLKREUCNT	The number of expired undo blocks reused within the same undo segments
SSOLDERRCNT	The number of ORA-1555 errors that occurred during the interval
NOSPACEERRCNT	The number of Out-of-Space errors

When the columns UNXPSTEALCNT through EXPBLKREUCNT holds non-zero values, it is an indication of space pressure.
If column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.
If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.

To easily determine if the conditions from Note 269814.1 have been met, please use the SQL statements below.

Query to determine if UNDO_RETENTION is properly set, where 0 means that no tuning is needed.


SELECT COUNT(*) as "Tune UNDO_RETENTION" FROM V$UNDOSTAT WHERE SSOLDERRCNT > 0;

If this query returns a non-zero value (count), then it is likely that the UNDO_RETENTION needs to be changed to the maximum value of column v$undostat.maxquerylen (see above).

Query to determine if there is Space Pressure, where 0 means no.


SELECT COUNT(*) AS "Space Pressure" FROM V$UNDOSTAT WHERE UNXPSTEALCNT > 0 OR UNXPBLKRELCNT > 0 OR UNXPBLKREUCNT > 0 OR EXPSTEALCNT	> 0 OR EXPBLKRELCNT > 0 OR EXPBLKREUCNT > 0;

If this query returns a non-zero value (count), then space

may

need to be added to the Undo Tablespace, see Resolution Section 1 (The UNDO tablespace is too small).

Query to determine is there is a serious space problem, where 0 means no.


SELECT COUNT(*) AS "Serious Space Problem" FROM V$UNDOSTAT WHERE NOSPACEERRCNT > 0;

If this query returns a non-zero value (count), then space should be added to the Undo Tablespace, see Resolution Section 1 (The UNDO tablespace is too small).

3. Enable retention guarantee for the Undo tablespace

There are several Oracle Support notes that explain why this is necessary. See:

Oracle Support Note 1100313.1 — Tuned_UndoRetention Can be Less Than Undo_Retention in Init.ora
Oracle Support Note 1579779.1 — Automatic Tuning of Undo Retention Common Issues

The explanation is «In the event of any undo space constraints, the system will prioritize DML operations over undo retention. In such situations, the low threshold may not be achieved and tuned_undoretention can go below undo_retention.».

So, if you see V$UNDOSTAT.TUNED_UNDORETENTION being less than the UNDO_RETENTION, then setting RETENTION GUARANTEE is recommended by Oracle.
For example:


SQL> SHOW PARAMETER undo_retention
NAME                       TYPE       VALUE
-------------------------- ---------- ------------------------------
undo_retention             integer    900

SQL> SELECT MIN(TUNED_UNDORETENTION) FROM V$UNDOSTAT;
MIN(TUNED_UNDORETENTION)
------------------------
511

This solution means that the Undo data will never be overwritten, where according to the algorithm the Undo Tablespace will instead be extended.

Determine the Undo Tablespace name.


SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO';

Using the tablespace name returned by the above query, enable Retention Guarantee on the Undo Tablespace.


ALTER TABLESPACE <tablespace-name> RETENTION GUARANTEE;

4. Calculate the size of the UNDO tablespace

The Oracle advice here is to «Use the formula presented in Document 262066.1 to calculate the size of the UNDO tablespace,» however, this formula has already been presented in section 1. The UNDO tablespace is too small.

Источник

Oracle ошибка ora 01555

Oracle error ora 01555

Oracle error ora 01555

ORA-01555 ERROR MESSAGE “SNAPSHOT TOO OLD”

Answers

Oracle / PLSQL: ORA-01555 Error Message

Description

Cause

Resolution

Option #1

Option #2

Option #3

Oracle error ora 01555

Введение

Общие положения

ПРОБЛЕМА ORA-01555 : snapshot too old ( rollback segment to small )

������� � ���� Oracle �� ������� : ��������� �� ������ ORA-01555

������ 26

Snapshot too old

����� ���� �����

ORA-01555 «Snapshot too old» — ��������� ����������

�����

������������

1. ��������������� �� ������:

2. ���������� ������� �����:

������ 1 — ��� ���������

������ 2 — ���������� ������ 2

������ 3 — ������������ ��������� ����������

������ 4 — ������ ������������ �������� ������ ����� 500

���������� ������ ORA-01555

�������� 1 — ������ ������ ����������

�������� 2 — ��������� ���� ���������� � �������� ������

�������

�������� 1 — ������ ������ ����������

�������� 2 — ��������� ���� ���������� � �������� ������

�������

���������� ������ ������

���������� ����� ���������� � �������� ������

����������� ������

������

� ��������� �������

ORA-01555 Explanation

1. The UNDO tablespace is too small

2. Tune the value of the UNDO_RETENTION parameter

3. Enable retention guarantee for the Undo tablespace

4. Calculate the size of the UNDO tablespace

А вот еще интересные материалы:

�� Oracle �� : �� ORA-01555

�� 26

��

ORA-01555 «Snapshot too old» — ��

��

��

1. �� :

2. �� :

�� 1 — ��

�� 2 — �� 2

�� 3 — ��

�� 4 — �� 500

�� ORA-01555

�� 1 — ��

�� 2 — ��

��

�� 1 — ��

�� 2 — ��

��

��

��

��

��

� ��