Коллекции в SQL

Как в SQL можно работать с коллекциями

1. CARDINALITY(x) = COUNT
2. SET(x) = DISTINCT
3. x MULTISET UNION DISTINCT y = UNION
4. x MULTISET UNION y = UNION ALL
5. x MULTISET UNION ALL y = UNION ALL
6. x MULTISET INTERSECT y = INTERSECT
7. x MULTISET INTERSECT DISTINCT y
8. x MULTISET EXCEPT y = MINUS
9. x MULTISET EXCEPT DISTINCT y

НапримерWITH tax AS (
SELECT 13 a FROM dual UNION ALL
SELECT 35 a FROM dual)
SELECT x MULTISET UNION x
FROM (
SELECT cast(collect(a) AS sps.tpt_number) x, 1 flag FROM tax UNION ALL
SELECT cast(collect(a) AS sps.tpt_number) x, 2 flag FROM tax WHERE ROWNUM > 1
) t
WHERE CARDINALITY(t.x) > 0;

10. POWERMULTISET -- работает как CUBE для коллекций, создает всевозможные наборы из элементов
SELECT *
FROM TABLE(POWERMULTISET(tpt_number(1, 2, 3)));

11. POWERMULTISET_BY_CARDINALITY -- тоже, что и POWERMULTISET, но возвращаются строки с заданным количеством элементов
SELECT *
FROM TABLE(POWERMULTISET_BY_CARDINALITY(tpt_number(1, 2, 3), 2));

Ошибка ORA-01031 при подключении с сервера

При подключении с сервера строкой sqlplus / as sysdba выдается ошибка:ORA-01031: insufficient privileges

Проверить
1. Пользователь, под которым запускаем SQL*Plus входит в группу DBA для Unix-систем или ORA_DBA для Windows

2. На сервере в файле sqlnet.ora содержится строка SQLNET.AUTHENTICATION_SERVICES= (NTS) для Windows и SQLNET.AUTHENTICATION_SERVICES= (ALL) для Unix

Если такая ошибка выдается при подключении через Listener (т.е. с указанием @имя_базы), то вполне вероятно, что причина в password-файле.

При ошибке IMP-00058, ORA-1438

При ошибке IMP-00058, ORA-1438 во время импорта
можно прогнать импорт с включенным event="1438 trace name errorstack level 10".

Ошибка чаще всего возникает при разворачивании кластеризованных таблиц или если таблица уже создана с неправильным набором колонок.

Поиск строки по бинарным файлам

Поиск строки по бинарным файла + простейшее форматирование + убираем дубликаты
$ grep --binary-files=text -o "pkg_social_tax.\([a-z_A-Z]\)*(" *.frf | sed 's/.
*:\(.*\)(/\1/' | sort -u

Проблемы с Enterprise Manager

На Oracle 10.2.0.4
После создания новой базы в dbca имеем ошибку:

Error securing Database Control, Database Control has been brought up in non-secure mode. To secure the Database Control execute the following command(s):

1) Set the environment variable ORACLE_SID to rsud
2) D:\oracle\product\10.2.0\db_1\bin\emctl.bat stop dbconsole
3) D:\oracle\product\10.2.0\db_1\bin\emctl.bat config emkey -repos -sysman_pwd < Password for SYSMAN user >
4) D:\oracle\product\10.2.0\db_1\bin\emctl.bat secure dbconsole -sysman_pwd < Password for SYSMAN user >
5) D:\oracle\product\10.2.0\db_1\bin\emctl.bat start dbconsole

Как описано в docid 1222603.1 ошибка в устаревшем с 2011 года сертификате. При этом подключение по http работает прекрасно.

Для исправления ошибки выпущен патч 8350262, который обновляет эти сертификаты. После выполнения патча рекомендовано сделать
emctl secure dbconsole -reset

но я почему-то не пошел по этому пути и пересоздал репозитарий полностью:

emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create


После выполнения этой штуки получил на экране ошибку (в логе ее нет):

java.io.IOException: The handle is invalidEnterprise Manager configuration compl
eted successfully

at java.io.FileInputStream.close0(Native Method)
at java.io.FileInputStream.close(FileInputStream.java:245)
at sun.nio.cs.StreamDecoder$CharsetSD.implClose(StreamDecoder.java:505)
at sun.nio.cs.StreamDecoder.close(StreamDecoder.java:198)
at java.io.InputStreamReader.close(InputStreamReader.java:187)
at java.io.BufferedReader.close(BufferedReader.java:502)
FINISHED EMCA at 19.11.2011 12:17:53 at oracle.sysman.assistants.util.sqlEngi
ne.SQLEngine$ErrorStreamReader.run(SQLEngine.java:2406)

При этом сервис создался и запустился, https поднялся, но при входе в em видим ошибку:

java.lang.Exception: Exception in sending Request :: null

и переходы на другие вкладки тоже падают с ошибкой:

500 Internal Server Error
java.util.MissingResourceException: Can't find resource for bundle oracle.sysman.db.rsc.LoginResource, key connectStringError
at java.util.ResourceBundle.getObject(ResourceBundle.java:325)


Алгоритм починки этой штуки примерно следующий:
1. Возможно стоит указать JAVA_HOME и PATH на те, которые лежат в ORACLE_HOME (шаг я делал, но он скорее всего лишний)
2. Удаляем все

emca -deconfig all db -repos drop

При удалении пользователя SYSMAN все может повиснуть. Как вариант обхода этого можно перезагрузить базу и удалить пользователя после ее поднятия или погуглить по команде drop user sysman, что даст кучу ссылок по удалению.

3. Очищаем папки, которые находятся в ORACLE_HOME/oc4j/j2ee, которые относятся к старым инсталляциям
4. Создаем все заново (ошибка java.io.IOException почему-то все равно осталась, но все заработало)

emca -cofig all db -repos create


После этого все поднялось и заработало.

Как вариант просто отказаться от https:

emctl unsecure dbconsole


Weblogic 10.3.0 и не работающий SSL

На неподдерживаемой версии Java 1.6.0_13 и выше ssl на weblogic не поднимается с ошибкой
unsupported oid in the algorithmidentifier object weblogic

Порывшись в гугле, способ решения ошибки такой:

1. Ищем файл с сертификатами от jre: cacerts
2. Выводим в файл все сертификаты keytool -list -v -keystore cacerts -storepass changeit > cacerts.txt
3. Ищем внутри него все сертификаты SHA256withRSA
4. Удаляем сертификаты keytool -delete -keystore cacerts -alias ... -storepass changeit перезапускаем сервер

Примечание: до этого еще удалил сертификаты ttelesecglobalrootclass2ca, ttelesecglobalrootclass3ca

Удаление пользователей из OIM

Домученный скрипт по удалению удаленных пользователей из OIM. Из замеченных багов -- в отчетах все равно остаются записи об удаленных пользователях, если вместо них создается другой с тем же именем. Вероятно, проще было взять скрипт с forums.oracle.com (если порыться, там можно найти скрипты из нескольких DELETE).

Вполне вероятно в некоторых случаях скрипт работать не будет.

СНЯТЬ БЕКАП ПЕРЕД ЗАПУСКОМ!


set serveroutput on size 1000000

DROP TABLE ref_table;

CREATE TABLE ref_table(
table_name VARCHAR2(30) NOT NULL,
rid rowid NOT NULL PRIMARY KEY,
lvl NUMBER NOT NULL);


DECLARE
lLVL INTEGER := 0;
lROWS_QNT NUMBER;
i PLS_INTEGER;

function build_column_list(aCONSTRAINT_NAME VARCHAR2, aPREFIX VARCHAR2) RETURN VARCHAR2 IS
RESULT VARCHAR2(4000);
BEGIN
FOR rec IN (
SELECT *
FROM user_cons_columns
WHERE constraint_name = aCONSTRAINT_NAME
ORDER BY position
) LOOP
RESULT := RESULT || ',' || aPREFIX || rec.column_name;
END LOOP;
--dbms_output.put_line(LTRIM(RESULT, ','));
RETURN LTRIM(RESULT, ',');
END build_column_list;

FUNCTION process_reference(aLVL NUMBER, aTBL_NAME VARCHAR2, aFK_CONS VARCHAR2, aPAR_TABLE_NAME VARCHAR2, aPK_CONS VARCHAR2) RETURN NUMBER IS
LQNT NUMBER;
BEGIN
dbms_output.put_line('Обработка ссылки на уровне ' || aLVL || ' ' || aTBL_NAME || '.' || aFK_CONS || ' -> ' || aPAR_TABLE_NAME || '.' || aPK_CONS);
IF aTBL_NAME <> aPAR_TABLE_NAME THEN
EXECUTE IMMEDIATE '
UPDATE /*+ NO_INDEX(x)*/ref_table x
SET lvl = (
select max(lvl) + 1
FROM ' || aPAR_TABLE_NAME || ' pt, ref_table rt
WHERE rt.rid = pt.rowid
AND ( ' || build_column_list(aPK_CONS, 'pt.') || ') IN (SELECT ' || build_column_list(aFK_CONS, 't.') || ' FROM ' || aTBL_NAME || ' t)
)
WHERE (rid || '' '') IN (
SELECT /*+ NO_INDEX(t)*/ t.rowid || '' ''
FROM ' || aTBL_NAME || ' t
WHERE ( ' || build_column_list(aFK_CONS, 't.') || ')
IN (SELECT ' || build_column_list(aPK_CONS, 'pt.') || ' FROM ref_table rt, ' || aPAR_TABLE_NAME || ' pt WHERE rt.rid = pt.rowid))';
END IF;

dbms_output.put_line('Обновлено уровней: ' || SQL%ROWCOUNT);
EXECUTE IMMEDIATE '
INSERT INTO ref_table(table_name, rid, lvl)
SELECT ''' || UPPER(aTBL_NAME) || ''', t.rowid, :LVL + 1
FROM ' || aTBL_NAME || ' t
WHERE ( ' || build_column_list(aFK_CONS, 't.') || ')
IN (SELECT ' || build_column_list(aPK_CONS, 'pt.') || ' FROM ref_table rt, ' || aPAR_TABLE_NAME || ' pt WHERE rt.rid = pt.rowid)
AND NOT EXISTS (
SELECT NULL
FROM ref_table rt
WHERE rid = t.rowid)'
USING aLVL;
lQNT := SQL%ROWCOUNT;
dbms_output.put_line('Добавлено строк ' || lQNT);

RETURN lQNT;
END;

FUNCTION process_level(aLVL NUMBER) RETURN NUMBER IS
lQNT NUMBER := 0;
BEGIN
dbms_output.put_line('+++ level = ' || aLVL);
FOR rec IN (
SELECT DISTINCT f.table_name chi_table_name, f.constraint_name chi_table_cons,
pp.table_name par_table_name, pp.constraint_name par_table_cons
FROM user_constraints f, user_constraints pp
WHERE f.constraint_type = 'R'
AND f.r_constraint_name = pp.constraint_name
AND pp.table_name IN (SELECT upper(table_name) FROM ref_table WHERE lvl = aLVL)
) LOOP
lQNT := lQNT + process_reference(aLVL, rec.chi_table_name, rec.chi_table_cons, rec.par_table_name, rec.par_table_cons);
END LOOP;
RETURN lQNT;
END;
BEGIN
INSERT INTO ref_table(table_name, rid, lvl)
SELECT 'USR', u.rowid, 0
FROM usr u
WHERE u.usr_status = 'Deleted';

lROWS_QNT := process_level(lLVL);
dbms_output.put_line('Будет удалено на уровне ' || (lLVL + 1) || ' ' || lROWS_QNT || ' строк');
WHILE lROWS_QNT > 0 LOOP
lLVL := lLVL + 1;
lROWS_QNT := process_level(lLVL);
dbms_output.put_line('Будет удалено на уровне ' || (lLVL + 1) || ' ' || lROWS_QNT || ' строк');
END LOOP;

dbms_output.put_line('=========');
dbms_output.put_line('Удаление');
dbms_output.put_line('=========');

-- Удаляем
FOR rec IN (
SELECT DISTINCT table_name, lvl
FROM ref_table
ORDER BY lvl DESC
) LOOP
dbms_output.put_line('Удаляем записи из ' || rec.table_name || ' lvl = ' || rec.lvl);
EXECUTE IMMEDIATE '
delete from ' || rec.table_name || ' t
where t.rowid IN (SELECT rid FROM ref_table WHERE table_name = :TN AND lvl = :lvl)'
USING rec.table_name, rec.lvl;

dbms_output.put_line('Из таблицы ' || rec.table_name || ' удалено строк ' || SQL%ROWCOUNT);
END LOOP;
END;
/


Как определить кодировку, в которой снят дамп

Кодировку, в которой снят дамп можно определить по 2 и 3 байту дамп-файла.
Подробная таблица тут

Шаблон расположения регионов в APEX

Для того, что бы посмотреть, где расположить регион на странице (display point), можно открыть любой регион, найти поле Display Point и нажать на фонарик рядом с полем

Заполнение ссылки по кнопке в apex

В APEX коряво сделано заполнение ссылки, например с кнопки.
Так, если я хочу для кнопки Печать задать редирект на какую-то страницу, то галочку PRINTER_FRENDLY мне просто не куда будет ставить.

Правило для заполнения линков в APEX такое:
f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly
или, если с заполненными переменными примерно так
f?p=?&APP_ID.:НОМЕР_СТРАНИЧКИ_ДЛЯ_ССЫЛКИ:&SESSION.::&DEBUG.::VAR1,VAR2:&FROM_VAR1.,&FROM_VAR2:YES

Замечаем, что то, что мы пишем в значение поля Request при создании кнопки рассовывается по другим полям.
Поэтому:
1. указываем Page (страницу, куда мы переходим) - заполнение этого эквивалентно заполнению &APP_ID.:НОМЕР_СТРАНИЧКИ_ДЛЯ_ССЫЛКИ:&SESSION.

2. В Request прописываем остаток, например: :&DEBUG.:RIR:P20_SID:&P12_SID.:YES. Это правильно запонит остальные поля, в том числе и PrinterFriendly. Если необходимо, можно так же указать значение для Request в этой строке

Борьба с ошибкой ORA-12560

При подключении sqlplus / as sysdba или с использованием listener sqlplus sys/syspassword@db возникает ошибка:
ORA-12560: TNS:ошибка адаптера протокола

Все шаги в совокупности дали желаемый результат.

1. Проверяем переменную ORACLE_SID (устанавливается или в переменных окружения или в реестре). Если переменная не установлена или установлена не правильно, этот шаг поможет при подключении без листенера sqlplus / as sysdba.

После исправления первого шага, возможно возникновение ошибки:
ORA-01031: insufficient privileges
Самая распространенная причина этого: необходимо прописать SQLNET.AUTHENTICATION_SERVICES = (NTS) в файле sqlnet.ora на сервере.

2. Для подключений через listener
Проверить, что в listener.ora прописаны правильные настройки. У меня все падало из-за неправильно выставленного
(SID_NAME=...)

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

Проблемы с Enterprise manager

На тестовой базе возникли проблемы с EM (хотя не посредстенно после создания базы он работал):

Enterprise Manager is not able to connect to the database instance. The state of the components are listed below.

Предлагаемые в итернете решения немного пугали, пока не найден кардинальный метод:

emca -deconfig dbcontrol db
emca -config dbcontrol db

Об именах

Исследования в статье натолкнули на мысль собственных исследований.

Итак в оракле есть имена и параметры:

• db_name -- имя базы, т.е. физического набора файла данных. Имя базы по словам Кайта прописывается в самих файлах.


• db_domain -- никогда особо не понимал, зачем это нужно


• instance_name -- имя экземпляра, т.е. набора процессов операционной системы и памяти.


• db_unique_name -- уникальное имя базы, если db_name одинаковый (например в Standby). Именно это имя используется в папках во Flash recovery area


• service_name -- имя сервиса, реалезуемое на экземпляре.

Исследования проводим на Oracle 11.1.0.6 путем изменения параметров, перезапуска базы и исследования результатов путем запуска скрипта:

shut immediate
startup nomount pfile=d:\init.ora

set feedback off
alter system register;

--host cls

col "name" format a20
column value format a40
SELECT name, value FROM v$parameter where name IN ('db_name', 'db_unique_name', 'db_domain', 'instance_name', 'service_names');

host lsnrctl services

База изначально создавалась как db1.notebook, т.е.

*.db_name='db1'
*.db_domain='notebook'

1. db_name = db1, остальные параметры не заданы

db_domain
instance_name db1
service_names db1
db_name db1
db_unique_name db1

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "db1" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "db1_XPT" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER

Без указания заданного при создании базы db_domain база нормально открылась

2. db_name = db2 (отличается от того, что задавался при создании)
NAME VALUE
-------------------- ----------------------------------------
db_domain
instance_name db1
service_names db2
db_name db2
db_unique_name db2

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "db2" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "db2_XPT" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

instance_name берется откуда-то еще, возможно из каких-то переменных среды или из сервиса
База не маунтится.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'DB1' in control file is not 'DB2'

3. db_name=db1, db_domain=notebook

NAME VALUE
-------------------- ----------------------------------------
db_domain notebook
instance_name db1
service_names db1.notebook
db_name db1
db_unique_name db1

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "db1.notebook" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "db1_XPT.notebook" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

Таким образом service_name = db_name + db_domain

4. db_name=db1, db_domain=notebook, service_names = my_service

NAME VALUE
-------------------- ----------------------------------------
db_domain notebook
instance_name db1
service_names my_service
db_name db1
db_unique_name db1

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "db1.notebook" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "db1_XPT.notebook" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "my_service.notebook" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

service_names равен соответствующему параметру

5. db_name=db1, db_domain=notebook, instance_name = just_instance

NAME VALUE
-------------------- ----------------------------------------
db_domain notebook
instance_name just_instance
service_names db1.notebook
db_name db1
db_unique_name db1

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "db1.notebook" has 1 instance(s).
Instance "just_instance", status BLOCKED, has 1 handler(s) for this service...

Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "db1_XPT.notebook" has 1 instance(s).
Instance "just_instance", status BLOCKED, has 1 handler(s) for this service...

Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

instance_name поменялся и стал равен соответсвующему параметру.

6. db_name=db1, db_domain=notebook, db_unique_name=uniq

NAME VALUE
-------------------- ----------------------------------------
db_domain notebook
instance_name db1
service_names uniq.notebook
db_name db1
db_unique_name uniq

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 23-APR-2011 02:47
:20

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "uniq.notebook" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "uniq_XPT.notebook" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

service_names = db_unique_name + db_domain

NAME VALUE
-------------------- ----------------------------------------
db_domain notebook
instance_name db1
service_names uniq.notebook
db_name db1
db_unique_name uniq

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "uniq.notebook" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "uniq_XPT.notebook" has 1 instance(s).
Instance "db1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

Выводы

1. service_names = COALESCE(service_names, db_unique_name || db_domain, db_name || db_domain) (точки писать не стал :))
2. instance_name = NVL(instance_name, что-то еще)
3. Без указания заданного при создании базы db_domain база нормально открылась

Отслеживание процессов в Standby

Мониторниг передачи файлов на Primary:

• V$ARCHIVED_LOG -- показывает, какие логи отправляются на STAND BY
• V$ARCHIVE_DEST_STATUS -- состояние arch процессов, куда они отправляют файлы, последний отправленный файл
• V$ARCHIVE_DEST -- аналогичен предыдущей вьюхе
• alert log -- можно смотреть ошибки при отправке

Мониторниг приема и применения файлов в Standby

• V$ARCHIVED_LOG (полученные логи), V$LOG_HISTORY (примененные логи), V$ARCHIVE_DEST_STATUS -- вьюхи, дающие с разным уровнем детализации последний полученный и последний применнный лог. Из последней вьюхи можно узнать ,в каком режиме применяются логи (real time или нет) в колонке RECOVERY_MODE.
• Запущен ли процесс (должен существовать процесс MRP или MRP0)

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

• V$DATAGUARD_STATUS -- информация из alert лога, можно узать полезное что-нибудь.

Для logical standby добавляются представления DBA_LOGSTDBY_*, V$LOGSTDBY_*

 

 

Передача файлов

На StandBy необходимо сохранять следующие файлы:

• архивы redo.log собственных (если logical standby)
• архивы standby redo log (то, что передается с primary)
• архивы переданных с primary архивов (если были проблемы со связью)
• Отправлять никуда ничего не надо, до тех пор, пока база не получит роль Primary

В Primary необходимо

• Делать архивы своих redo.log
• Отправлять в stand by свои redo.log

Для управления этой кухней сделаны VALID_FOR (что сохраняем/отправляем, когда (при какой роли) сохраняем/отправляем)

Для что возможные значения: ONLINE_LOGFILE, STANDBY_LOGFILE, ALL_LOGFILES

Для когда возможные значения: PRIMARY_ROLE, STANDBY_ROLE, ALL_ROLES

Исходя из этого выводим:

1. Необходимо иметь возможность отправлять свои изменения по сети, т.е. должно быть настроено

VALID_FOR(ONLINE_LOGFILE, PRIMARY_ROLE) + указан сетевой сервис отправки

2. В качестве сохранения собственных изменений можно использовать Flash Recovery Area, которая по умолчанию настраивается в log_archive_dest_10. Если верить документации, то для log_archive_dest_10 будут настроены параметры по-умолчанию, т.е ALL_LOGFILES,ALL_ROLES. Но тут опять небольшая загвоздка в документации:

Flash recovery area destinations specified with the STANDBY_ARCHIVE_DEST parameter on logical standby databases (SQL Apply) are ignored

Таким образом, архивные логи, пришедшие с Primary в случае logical standby складировать некуда (если правильно понимаю документацию).

3. Т.к. ситуация со standby логами во flash recovery area в случае logical stand by не совсем понятна, возьмем, что оракл рекомендует использовать в документации:

Для Primary

LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/chicago/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
LOG_ARCHIVE_DEST_2=
'LOCATION=/arch2/chicago/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
STANDBY_ARCHIVE_DEST=/arch2/chicago/

Для Standby

LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/denver/
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=denver'
LOG_ARCHIVE_DEST_2=
'LOCATION=/arch2/denver/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
STANDBY_ARCHIVE_DEST=/arch2/denver

Тут возникают непонятные моменты:

1. Почему log_archive_dest_1 на Primary и StandBy различаются.

2. А не складируются ли у нас Standby логи в Primary 2 раза, после смены ролей. Видимо в Primary должно быть:

LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/chicago/
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)

Или же, если верить, что Flash Recorery Area и так сохраняет в себе все сгенеренные логи, достаточно добавить параметр STANDBY_ARCHIVE_DEST.

Наверное, лучше всего сконфигурировать 3 процесса и разбивать файлы на 2 кучки свои и чужие:

1. Посылатель файлов для Primary role

2. Раскладыватель online для всех ролей (1 кучка)

3. Раскладыватель standby логов для standby роли (вторая кучка). STANDBY_ARCHIVE_DEST, если не задан, будет указывать на вторую кучку аутоматически

Создание Standby общие идеи

На основании документации и статьи

Общие идеи

Подготовка primary

 

1. База находится в режиме архивирования, есть password file.
2. Перевести базу в режим force logging
3. Опционально (если будет использоваться режим maximum protection и maximum aviablity, включен LGWR ASYNC transport mode). Этот случай не исследовался
4. Изменить параметры базы данных. Можно делать alter system, можно через pfile с последующим перезапуском. Параметры применить перед снятием бекапа. Минимум параметров, которые необходимо задавать:
   
   • db_unique_name -- экземпляры открывают одну и туже базу данных, в которых db_name одинаковый. Параметры должны различаться в primary и standby. Что бы не было путаницы, лучше не называть с подчеркиваниями (oradim не может создать базу с sid содержащий подчеркивания), так же заводить в дальнейшем tnsnames с такой же строкой (service_name, совпадающий с идентификатором в tnsnames)
   • log_archive_config='dg_config=(db1_pri,db1_stb)' -- задаются db_unique_name, между которыми происходит обмен логами
   • LOG_ARCHIVE_DEST_1=  'SERVICE=db1stb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=db1_stb'
     LOG_ARCHIVE_DEST_STATE_1=ENABLE - т.к. у меня есть db_recovery_file_dest, то log_archive_dest_2 мне (наверное, перепроверить при измении ролей!!!) не нужен. В SERVICE указывается строка из tnsnames. Указывает, куда отсылаем логи.
   • FAL_SERVER='DB1STB'
     FAL_CLIENT='DB1'
     STANDBY_FILE_MANAGEMENT=auto -- параметры, необходимые при смене ролей primary
   • т.к. структура каталогов standby и primary  у меня совпадает, то параметры log_file_name_convert и db_file_name_convert я не указываю ни в primary, ни в standby. Вместо этого при разворачивании пользуюсь ключиком NOFILENAMECHECK
     
5. Сделать бекап, из которого будем разворачивать standby, включая специальный бекап control file.
6. Настроить tnsnames, прописав туда саму базу и standby-базу. Проверить tnsping правильность.

Подготовка standby

1. Забираем с primary файлы:
   • tnsname.ora -- должен указывать и на primary и на standby
   • password (можно забрать и переименовать, можно создать новый)
   • pfile
   • бекапы - помещаем в тоже место, куда снимались в primary, хотя можно и рекоталагизировать.
   • redo логи, если они были созданы в primary (этот случай не рассматривался)
2. Изменяем pfile
   
   • изменяем db_unique_name, см. примечания к этому в primary
   • control_files -- я закомментировал и он у меня создал контрольник в db_recovery_file_dest. В документации просто изменены пути
   • LOG_ARCHIVE_DEST_1=  'SERVICE=db1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db1_pri'. Куда отправляем файлы в случае смены ролей. Относительно Primary изменилось SERVICE и DB_UNIQUE_NAME
3. Создаем каталоги, аналогичные по структуре primary для указанных в файле параметров путей, а так же файлов базы данных.
4. Создаем экземпляр при помощи oradim, создаем сервис listenera. При использовании oradim не получается создать sid базы с подчеркиванием. Проверить, что службы сами запускаются.
5. Делаем или изменяем password file.
6. Создаем spfile, Запускаем базу в режиме nomount. Если появляется ошибка ora-12560 сделать set oracle_sid = ...
7. Разворачиваем созданный на primary бекап. Если структура каталогов та же, то используем команду duplicate target database for standby dorecover NOFILENAMECHECK, что бы не ругалась на дублирование имен файлов. Запускать rman необходимо на standby, или прописывать базу в листенере, что бы к ней можно было подключиться удаленно.
8. Делаем alter database recover managed standby database disconnect;
9. Проверяем передачу и накат логов.

Про таймеры на заметку

smon times out every 5 minutes, pmon every three seconds.

_small_table_threshold

Давно хотел посмотреть сам на этот механизм, но Льюис уже выложил тесты.

Определение

• Table < (2%*buffer_cache) = _small_table_threshold is located into the middle point of LRU list when loaded into the buffer cache.
• Table > (2%*buffer_cache) = _small_table_threshold is located into the cold end of the LRU list when loaded into the buffer cache.

По результатам тестов Льюиса для Oracle 10.2, проверяется содержимое буферного кеша (x$bh), количество touch и количество блоков для таблиц разных размеров:

1. Для больших таблиц (свыше 10% кеша) touch не увеличивается, используется table scans (long)
2. Для таблиц больше 25% буферного кеша при сканировании буферы переиспользуются
3. Для таблиц меньше 10% буферного кеша touch увеличивается, причем первое сканирование таблицы не увеличивает touch (получается table scans (long)), последующие увеличивают (table scans (short))
4. Для таблиц менее 2% touch count увеличивается каждый раз, используются table scans (short)
5. Для 9-й и менее версий оракла поведение соответствует определению.

Немного о лицензировании

Первая вьюха:
select * from v$license;

SESSIONS_MAX 0
SESSIONS_WARNING 0
SESSIONS_CURRENT 2
SESSIONS_HIGHWATER 7
USERS_MAX 0
CPU_COUNT_CURRENT 1
CPU_CORE_COUNT_CURRENT
CPU_SOCKET_COUNT_CURRENT
CPU_COUNT_HIGHWATER 1
CPU_CORE_COUNT_HIGHWATER
CPU_SOCKET_COUNT_HIGHWATER

Информация по использованию опций:
SELECT * FROM dba_feature_usage_statistics
Вьюха основана на таблицах wri$:
create or replace view dba_feature_usage_statistics as
select samp.dbid, fu.name, samp.version, detected_usages, total_samples,
decode(to_char(last_usage_date, 'MM/DD/YYYY, HH:MI:SS'),
NULL, 'FALSE',
to_char(last_sample_date, 'MM/DD/YYYY, HH:MI:SS'), 'TRUE',
'FALSE')
currently_used, first_usage_date, last_usage_date, aux_count,
feature_info, last_sample_date, last_sample_period,
sample_interval, mt.description
from wri$_dbu_usage_sample samp, wri$_dbu_feature_usage fu,
wri$_dbu_feature_metadata mt
where
samp.dbid = fu.dbid and
samp.version = fu.version and
fu.name = mt.name and
fu.name not like '_DBFUS_TEST%' and /* filter out test features */
bitand(mt.usg_det_method, 4) != 4 /* filter out disabled features */;

Пополняются они один раз в неделю (колонка sample_interval = 604800). Значение интервала берется из таблицы WRI$_DBU_USAGE_SAMPLE, API для изменения этого значения (кроме прямого UPDATE) найти не удалось.

Единственный пакет, который ссылается на эту таблицу: dbms_feature_usage_internal, который содержит 2 интересные процедуры:
EXEC dbms_feature_usage_internal.exec_db_usage_sampling(curr_date => SYSDATE) Заработало только после переподключения сессии, почему-то не обновляет в wri$_dbu_usage_sample колонку last_sample_date, но обновление количества использований после переподключения сессии происходит (до переподключения в трейсе вообще не было UPDATEов)
EXEC dbms_feature_usage_internal.sample_one_feature(feat_name => 'Automatic Workload Repository') - приводит к обновлению dba_feature_usage_statistics, причем дата последнего обновления проставляется в last_sample_date для всех опций (т.к. она одна и берется из таблицы wri$_dbu_usage_sample).

Что делают эти процедуры можно посмотреть, сняв трейс 10046.

Как заставить работать lateral в oracle

ANSI SQL оператор lateral аналогичнен конструкции TABLE в Oracle.
Но просто так он не работает:
sps@v-pc-dev-3>SELECT *
2 FROM dual d1,
3 lateral (
4 SELECT * FROM dual d2 WHERE d2.dummy = d1.dummy
5 )
6 WHERE d1.dummy = 'X';
lateral (
*
ошибка в строке 3:
ORA-00933: SQL command not properly ended
Внимание на условие d2.dummy = d1.dummy внутри lateral.

Но
sps@v-pc-dev-3>alter session set events '22829 trace name context forever';

Сеанс изменен.

sps@v-pc-dev-3>SELECT *
2 FROM dual d1,
3 lateral (
4 SELECT * FROM dual d2 WHERE d2.dummy = d1.dummy
5 )
6 WHERE d1.dummy = 'X';

D D
- -
X X

1 строка выбрана.