Сначала опишу проблему:
Открыли SR в Oracle Metalink. Благо первый специалист оказался шустрым на поиск проблемы и промежуточного решения, установили job_queue_processes=0 и при репликации база падать перестала, но перестало это все дело работать по расписанию, только с ручным запуском. Перенаправили нас из группы по MView группе по джобам. И вот тут появились сферические кони в вакууме...
Кто-нибудь мне может сказать, имеет ли смысл устанавливать параметр /3Gb в винде (дабы дать возможность адресовать до 3Гб на процесс), если на ней крутится инстанс, который при полной нагрузке кушает не более 500-600 Мб? А вот в металинке считают, что имеет. Причем это они решили после недели расспросов и размышлений. Сказали следующее:
We suspect that oracle processes reached the 3Gb Limit.Please cosider setting the /3GB switch then reboot the Windows box and check if the problem is reproducible.
for more information on how to set the /3GB switch please refer to the following
Самое забавное, что подобное решение нам предлагали и по другой проблеме, и ведь тоже не помогло, но там мы общались через испорченный телефон под названием Российский Саппорт Oracle в компании Академия IT. Там тоже были утечки, были треды, которые грузили проц неимоверно, и это были треды от уже закрывшихся сессий. Так вот, решением тогда было выставить SQLNET.EXPIRE_TIME=0. Но это мы узнали уже после того как сами написали в металинк.
Дык вот возникает вопрос: /3Gb - панацея от всех болезней? Опыт показывает, что нет. Так почему это решение предлагают по каждой второй проблеме даже если она ни как не связана с нехваткой памяти процессу?
Пока все пишут о фишках 11g и проблемах 10g многие до сих пор используют 9iR2. Для тех, кто до сих пор (как и мы) использует данный релиз СУБД Oracle, данная запись и написана. А может еще кто из самого Oracle заглянет и шепнет про решение проблемы. Итак, начнем-с.
Прелюдия.
Работаем мы с Oracle 9.2.0.x довольно долго и успешно, по ряду причин руководство тянет с переездом на 10-ку, но уже все больше склоняется к нему. Система разнесена географически на 2 офиса, по этой причине настроена репликация (в оба конца). Репликация создавалась простейшим способом, создали DBLink, создали Materialized View, обновляемые по запросу (на все необходимые таблицы), создали процедуру, выполняющую dbms_mview.refresh, и, наконец, Job, запускающий эту процедуру. Казалось бы проще некуда, но это все казалось до Oracle 9.2.0.5 и первого перевода времени.
Проблема.
Проблема возникла при переводе на летнее время весной 2007 года. Заключалась она в том, что упали оба инстанса, в обоих городах, самое противное, что в 2 часа ночи. Нет, вру, самое противное, что в логах было пусто, ни одной записи насчет падения, реально ничего, будто взял и корректно выключился.
Диагностика.
Ну диагностику я подробно описывать не буду, на некоторые вещи чисто случайно наткнулся, скажу только что проблема оказалась как раз в том, что по какой-то причине переглючили Job'ы, запускающие обновления представлений (которые, кстати, при job_jueue_processes>0 обновляются хитрым способом с использованием пакетных заданий, как и почему не спрашивайте, это скрыто в недрах компании Oracle, я знаю только то, что я знаю). Выглядит это забавно: Job, у которого стоит статус broken='Y' либо по списку сессий видно, что он не выполняется, тикает суммарное время выполнения, оно увеличивается словно задание работает.
Решение.
Решение, увы, не совсем красивое, но не совсем удобна и ситуация. Проблема состоит в том, что после очередного запуска инстанса у нас нет массы времени для раздумий и выполнения ряда действий. У кого как, а у нас инстансы валились в течение максимум 30 секунд после запуска, сопровождаемые утечками памяти.
1 вариант: сразу после запуска инстанса подключаемся как SYS:
sqlplus "/@mydb as sysdba
читаем значение проблемного параметра:
SQL> sho parameter job
после этого запоминаем его (быстро) и устанавливаем его в 0:
SQL> alter system set job_queue_processes=0;
Ищем проблемные задания и пересоздаем их (да, Вам не показалось, именно создаем копии, с теми же вычислениями поля INTERVAL, с тем же значением WHAT), ставим для старых статус BROKEN='Y' либо вообще их прибиваем.
Возвращаем наш параметр в исходное значение, для варианта "по умолчанию" это выглядит так:
SQL> alter system set job_queue_processes=10;
Все! Костыль, но работает и спасает уже 2й раз. Надеюсь, следующий перевод времени будем встречать на десятке.
2 вариант может понадобиться, если инстанс падает слишком быстро. В этом случае сразу после запуска инстанса и подключения к нему SQL*Plus делаем следующее:
SQL> shutdown abort;
...
SQL> startup nomount;
...
SQL> alter system set job_queue_processes=0;
...
SQL> alter database mount;
...
SQL> alter database open;
...
Далее идут описанные в первом варианте действия, начиная с "Ищем проблемные задания и пересоздаем их".
P.S: В Oracle Metalink реквест мы конечно создадим, но в прошлый раз все закончилось тем, что я просто пересоздал аномальные задания, а они закрыли SR с пометкой "решено клиентом самостоятельно".