Быстрый Logminer

1) Создай пакет DBMS_LOGMNR
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus system/password @dbms.sql

2) Определи файлы, которые будут анализироваться логмайнером (в примере далее это файлы ASM типа +DBGROUP1/ccdb/onlinelog/group_2.262.593698089), например, вытащи активные журналы через

SELECT distinct member LOGFILENAME FROM V$LOGFILE;

3) Добавь полученные файлы в поле видимости пакета DBMS_LOGMNR
BEGIN
sys.DBMS_LOGMNR.ADD_LOGFILE
('+DBGROUP1/ccdb/onlinelog/group_2.262.593698089'); --замени на свои имена файлов
sys.DBMS_LOGMNR.ADD_LOGFILE
('+DBGROUP1/ccdb/onlinelog/group_3.263.593698095');
sys.DBMS_LOGMNR.ADD_LOGFILE
('+DBGROUP1/ccdb/onlinelog/group_1.261.593698085');
END;

4) Запусти начало анализа журналов
BEGIN
sys.DBMS_LOGMNR.START_LOGMNR
(options =>
dbms_logmnr.dict_from_online_catalog);
END;

5) Найди проблемную операцию и вытащи UNDO-запрос через запрос к V$LOGMNR_CONTENTS
Например,
select username,to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp,
seg_type_name, seg_name, table_space, sql_redo,sql_undo, session# SID, serial#
from V$LOGMNR_CONTENTS
where operation = 'DELETE' -- Если нужно поменяй отслеживаемую операцию!!!
and table_space like 'USERS' -- Если нужно поменяй или убери ТС!!!
and to_char(timestamp,'mm/dd/yy hh24:mi:ss')> '06/27/07 17:00:00' -- Здесь задай свою дату!!!
order by 2 desc;

6) Остановить Logmnr-процесс
BEGIN
sys.Dbms_Logmnr.end_logmnr;
END;

Быстро сфлэшбэчиться 2 (to before drop;)

В продолжение своей темки "Быстро сфлэшбэчиться" провел тестики с "корзиной" при удалении тестовой таблички adm_office на основе "Using Oracle's recycle bin, by Natalka" и "Ретроспективная (Flashback) таблица", где Arup неплохо вставил - "В некоторых случаях этим пользователем-неудачником можете оказаться и вы – АБД!". С чем я согласен :)

drop table adm_office; -- случайное удаление таблицы
select * from tab; -- видим строку с переименованной таблицей в системную
select * from recyclebin; --удостоверимся, что это наша удаленная таблица
--если в sqlplus, то можно SQL> show recyclebin
flashback table adm_office to before drop; -- сфлэшбэчим удаленную табличку

Итак я представил, что кто-то из юзеров удалил таблицу в своей схеме и позвонил мне. Я зашел под system'ом и не увидел удаленной таблицы в recyclebin. Тогда делаю запрос
select owner, object_name, original_name, type, droptime, can_undrop, can_purge from dba_recyclebin;
и вижу то, что нужно - схему, имя таблицы, время ее удаления и возможность восстановления!
Можно проверить эту табличку запросом к переименованной табличке, например:
select * from СХЕМА."BIN$kffqEdSnQd2u6rEnet3Pzg==$0"
Теперь осталось сфлэшбэчить данную табличку с указанием ее схемы-владельца
flashback table СХЕМА.adm_office to before drop;

Тесты c Logminer

Сегодня провел тесты с logminer на основе статьи Раскрываем секреты – глубинный анализ журналов пока на своей Винде в тестовой базе.
Сначала нужно инсталлировать пакет DBMS_LOGMNR (далее шаг 1)
Затем выдать привилегию юзеру, который будет анализировать журнал (шаг 2 - сразу родился вопрос - когда я попытался сделать анализ от юзера adm, которому дал роль по статье, получил ошибку "Привилегий недостаточно" - поэтому вопрос с привилегиями пока открыт).

Тест в винде - если что-то удалено недавно и изменения сохранились в оперативных журналах (имена журналов мы возьмем из SELECT distinct member LOGFILENAME FROM V$LOGFILE;)

1) C:\>sqlplus / as sysdba
2) SQL> c:\oracle\product\10.2.0\db_1\rdbms\admin\dbmslm.sql;
3) SQL> GRANT EXECUTE_CATALOG_ROLE TO adm;
4) SQL> CREATE PUBLIC SYNONYM DBMS_LOGMNR FOR SYS.DBMS_LOGMNR;
5) SQL> SELECT distinct member LOGFILENAME FROM V$LOGFILE;
6) BEGIN
DBMS_LOGMNR.ADD_LOGFILE
('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLWIN\REDO03.LOG');
DBMS_LOGMNR.ADD_LOGFILE
('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLWIN\REDO02.LOG');
DBMS_LOGMNR.ADD_LOGFILE
('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLWIN\REDO01.LOG');
END;
8) BEGIN
DBMS_LOGMNR.START_LOGMNR
(options =>
dbms_logmnr.dict_from_online_catalog);
END;

здесь dbms_logmnr.dict_from_online_catalog - как я понял из доки использование логмайнером словаря БД для анализа не только онлайн-журналов (dbms_logmnr.DICT_FROM_REDO_LOGS), но и заархивированных.

9) select username,to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp,
seg_type_name, seg_name, table_space, sql_redo,sql_undo, session# SID, serial#
from V$LOGMNR_CONTENTS
where operation = 'DELETE'
and table_space like 'USERS'
and to_char(timestamp,'mm/dd/yy hh24:mi:ss')> '06/27/07 17:00:00'
order by 2 desc;

10) Нахожу нужную строку и из SQL_UNDO выбираю и выполняю соответствующий запрос, чтобы вернуть как было.
Прим. Cтолбец SQL_REDO – показывает оператор, который может быть выполнен, чтобы вызвать то же самое изменение, сделанное пользователем, и столбец SQL_UNDO – оператор, который может быть выполнен для отмены изменения.


Но что делать если произошло пакетное удаление или изменение? Думаю, pl/sql с курсорами, хотя не уверен. Надо будет протестить.

Не используйте нулевой сектор raw-девайса для Oracle ASM в Solaris 10

Вчера коллега натолкнулся на проблему, что сырой девайс не определялся ASM-инстансом Oracle в числе CANDIDATE-дисков. Сегодня утром он нашел проблему и поделился опытом. Он сделал дамп этого раздела dd-ой и увидел, что Solaris 10 R2 записал свою служебную инфу в нулевой сектор этого девайса. Очевидно, что для ASM-инстанса это и послужило проблемой. Тогда он пересоздал сырой раздел не с нулевого, а с первого сектора и ASM увидел CANDIDATE-диск.
Вывод: выделяя девайс под раздел для Oracle ASM в Solaris 10 начните его не с нулевого, а с первого сектора. На других ОС-ях пока не было возможности проверить это. Ссылка на man dd

CPU Apr 2007 на Solaris 10 (Sparc 64-bit)

Сегодня ночью установил CPU Apr 2007 на Oracle 10gR2 на базе Solaris 10 (Sparc 64-bit). Данный апрельский Critical Patch Update ставился впервые и проделав загодя тесты с его установкой на Linux x86 (Centos 4.4) на выполнение работ по План-графику выделил 1 час с 9.00 до 10.00 утра. Т.к. на Линухе на установку данного CPU я затратил примерно полчаса. Но этой ночью ввиду бессоницы (укладывал себя спать с пол 12-го ночи раза три) и возможно неуверенности, что одного часа утром может и не хватить (а надо уложиться именно за это время), решил начать установку из дома и не пожалел об этом.... Т.к. на все работы по установке этого CPU на Solaris 64-bit ушло 2 часа 15 мин. - с 3.45 час. до 6.00 час. Причем свежий opatch 10.2.0.2.2, который требовался для установки этого CPU, был уже установлен и резервные копии базы и бинарников Oracle сделаны из cron.
В конце установки CPU пришлось поволноваться, когда получил это:
Inventory check OK: Patch ID 5901881 is registered in Oracle Home inventory with proper meta-data.
Verification of 'ar' actions failed. This is a known issue for this platform. Pl ease check the note 353150.1 on Metalink for the process to manually verify the files.
Files check OK: Files from Patch ID 5901881 are present in Oracle Home

Но следование упомянутой ноте 353150.1 показало, что "... all Source files are 4 bytes smaller than their respective Destination files AND the patch has successfully applied". Тесты с базой прошли успешно и она уже чуть более часа как в "плавании".

В dbca не вижу базы, созданной вручную

Провел тест с созданием базы вручную на основе модифицированного скрипта, сгенерированного раннее при помощи dbca. База успешно поднялась. Но когда я решил взглянуть на нее через dbca, с удивлением обнаружил, что ее нет среди инстансов ... Хотя база поднята и работает.
Хм... пока тема остается открытой.

Error 45 initializing SQL*Plus

Сегодня утром на свежеустановленном тестовом сервере Centos 4.4 + Oracle 10gR1 получил ошибку Error 45 initializing SQL*Plus

[oracle@centest ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Sat Jun 9 10:19:14 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Error 45 initializing SQL*Plus
Internal error

Судя по металинку эта бага обнаруживается на разных платформах и версиях Oracle, нашел упоминание о Bug 3039738 (Oracle Cluster File System (OCFS) ) и в форуме данная проблема присутствует. Заглянул в первое попавшееся и когда вычитал реплику автора о подобной проблеме "Even I faced the same problem. But when I set the oracle_home,nls_lang and path environment variables it worked fine" сразу проверил свои переменные окружения и обалдел, когда увидел, что HOSTNAME=my_server_name.other_domain.ru :)
Заглянул в /etc/hosts, /etc/resolv.conf, /etc/sysconfig/network - так и есть, там прописан не мой, а "левый" домен.
Исправил, ребутнулся - ошибка осталась :(
Проблема остается открытой ...

grant select on все таблицы одной схемы другой схеме?

К моему удивлению, не нашел как это сделать в SQL Reference и сделал через запуск скрипта, полученного из
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
spool grants2user2.sql
select 'grant select on '''||owner||'.'||table_name||''' to user2;' from dba_tables where owner='USER1';
spool off;
exit;

Вывод списка ролей (пример использования %ROWTYPE)

declare
cursor get_roles is -- Объявляем курсор
select * from dba_roles;
v_roles get_roles%ROWTYPE; --Объявляем переменную, работающую с курсором
begin
open get_roles;
loop
exit when get_roles%NOTFOUND; --Оператор цикла для просмотра всех записей таблицы
dbms_output.enable;
fetch get_roles into v_roles; --Фетчим курсор в переменную
dbms_output.put_line(TO_CHAR(v_roles.role));
end loop;
close get_roles;
end;
/

Создание скрипта сохранения и изменения паролей

set pause off;
set pages 0;
set linesize 140;
set feedback off;
set termout off;
column cmd_line format a80
column tick format a3
column sum_bytes format 999,999,999,999
spool return_pass.spool.sql;
select 'alter user "'||username||'" identified by values '''||password||''';' from dba_users;
spool off;
spool change_pass.spool.sql;
select 'alter user "'||username||'" identified by qqq;' from dba_users;
spool off;
exit;

Open Relay Checker etc.

Онлайн-чекеры Интернет-серверов
http://tests.nettools.ru/
http://www.mxtoolbox.com/blacklists.aspx

IP-калькуляторы
http://ipcalc.dewil.ru
http://www.cotse.com/networkcalculator.html

create_roles.sql

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool roles.sql
select 'create role ' || r.role || ';'
from dba_roles r;
spool off;
exit;

Bastille

После установки Bastille если есть конфиг, натравить на него можно через
#bastille -b
Проверить можно через vsftpd, если он установлен
На команду #service vsftpd status
Ответ: vsftpd dead but subsys locked

Ошибки создания JOBs при импорте схем под system

При импорте множества схем под system объекты job валятся в схему system, а не в схемы, которым они принадлежат. Пока не нашел как решить эту проблему. А пока нужно было удалить кучу созданных объектов JOB и попросить разработчиков создать эти JOB-ы.
Написал такой код для удаления этих job-ов, который сработал.
declare
j number;
begin
for j in (select job from dba_jobs where log_user in ('SYSTEM'))
loop begin
DBMS_JOB.REMOVE(j.job);
COMMIT;
end;
end loop;
end;

Полезняшки

select u.sid, substr(u.username,1,12) user_name, s.sql_text
from v$sql s, v$session u
where s.hash_value = u.sql_hash_value
and sql_text not like '%from v$sql s, v$session u%'
order by u.sid;

Тест с установкой Oracle на CentOS 5

Пока есть время (в преддверии установки нового сервера для разработчиков) решил протестить установку Oracle 10gR2 на CentOS 5 (хотя как я понял 5-я версия RHEL еще не тестировалась Oracle). Первая грабля поджидает сразу после запуска runInstaller'а на сообщении
"Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2". Загляните в /etc/redhat-release и поменяйте версию. Подсказку нашел на одной из
ссылок по установке Oracle 10gR2 на Red Hat 5.
Следующая грабля связана с rawdevices, на базе которых поднимается Oracle ASM.
Я не нашел ни /etc/init.d/rawdevices, ни /etc/sysconfig/rawdevices. Помог FAQ How do I add raw device mapping in Red Hat Enterprise Linux 5?. Ну и ссылка на русскую доку по RHEL возможно пригодится
Установил Oracle и создал базу на базе ASM успешно. Все работает, но когда попробовал перелить несколько схем из другой БД при помощи impdp с network_link, но получил ошибки
ORA-31626: job does not exist
ORA-31650: timeout waiting for master process response

.....Думаю, тестировать далее нет смысла - если будет время продолжим тесты, но сейчас я откажусь от CentOS 5 в своих работах с Oracle 10gR2.

dmesg в crontab.root

На установленной системе с HP-UX 11.11 PA-RISC установил Oracle 10g и базу на базе ASM. Экземпляр ASM был построен на asm_diskstring='/dev/rdsk/c18t0*, /dev/rdsk/c20t1*', права на которые принадлежали oracle:dba. Дак вот время от времени права на /dev/rdsk/c20t1* слетали на sys:bin, из-за чего ASM постепенно сыпался. Понятно было, что какой-то процесс вызывает переинициализацию дисков в системе. При этом в /var/spool/cron/crontabs/root никаких процессов кроме скриптов бэкапа нет.
Написал скриптик-чекер, которые проверял права на диски ежеминутно из крона и высылал на эл.почту уведомление об их изменении. Время от времени такие сообщения начали сыпаться на почту и я заметил в, что /tmp появляется файлик ioscan.out именно в то же время когда прилетает уведомление.
Оказалось, что в /var/spool/cron/crontab.root прописана строка 05,15,25,35,45,55 * * * * /usr/sbin/dmesg - >>/var/adm/messages

man dmesg сказал
dmesg - collect system diagnostic messages to form error log
....If the - argument is specified, dmesg computes (incrementally) the new messages since the last time it was run and places these on the standard output....
Отключение этой строки из крона помогло.
Т.е. crontab -e позволяет управлять лишь /var/spool/cron/crontabs/user, а не всем кроном юзера user.

Block change tracking (Сказочно, но проверить!)

Отслеживание измененных блоков для оптимизации инкрементального резервирования за счет устранения необходимости полного просмотра файлов данных в ходе резервирования.
Информация о физическом расположении всех изменений, заносится в файл отслеживания изменений change tracking file фоновым процессом CTWR (change tracking writer).
При включенном режиме отслеживания файл измененных блоков автоматически используется RMAN-ом.

Для просмотра включен ли режим отслеживания измененных блоков
sys@ORCL> select status from v$block_change_tracking;

STATUS
----------
DISABLED

Для того, чтобы включить отслеживание измененных блоков без указания имени файла нужно удостовериться установлен ли параметр db_create_file_dest

sys@ORCL> show parameter db_create_file_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +ASM_DATA

Если параметр включен как в примере выше, то включение без указания имени файла.
sys@ORCL> alter database enable block change tracking;

sys@ORCL> select status,filename from v$block_change_tracking;

STATUS FILENAME
---------- --------------------------------------------------
ENABLED +ASM_DATA/orcl/changetracking/ctf.303.623334603

При выключенном параметре parameter db_create_file_dest
sys@ORCL> alter database enable block change tracking
2> using file '/mydir/rman_change_track.f'
3> REUSE;

/dev/async, см. 393314.1

Symptoms
Database storage is ASM on an HP-UX system.
A disk fails in an ASM diskgroup.
ASM does not recover and I/Os hang indefinitely.
All types of SQL statements requiring I/O can be affected.
Asynchronous I/O is enabled.

Solution
To implement the solution enable minor device number 4 for device /dev/async, i.e.
crw-rw-rw- 1 bin bin 101 0x000004 Sep 12 2006 /dev/async

Major and minor device numbers are assigned to a character device when the device is created with mknod. In order to change minor device number, /dev/async needs to be recreated.

$ /sbin/mknod /dev/async c 101 0x0

ограничения network_link

При при заливке при помощи ipmdp с network_link не поддерживаются партиционированные таблицы и таблицы типа xmltype
- определить есть ли партиционированные таблицы в схеме-источнике можно через
select distinct(owner) from dba_objects where object_type like 'TABLE PARTITION';

или, например, для схем User1, User2, User3
select * from dba_tab_partitions where table_owner in ('USER1','USER2','USER1');

- определить есть ли таблицы xmltype схемы-источника можно через
select distinct(owner) from dba_xml_tables;

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

select owner,table_name,column_name,data_type from dba_tab_columns
where owner in ('USER1','USER2','USER1') and data_type='LONG' order by 2;

и сделать импорт этих таблиц через DUMPFILE: