2010-02-27

SQLNET.VALID_NODE_CHECKING

Фича, которая позволяет запрещать подключение клиентов через LISTENER.

Изначально имеем пустой файл sqlnet.ora на сервере:
> cat sqlnet.ora
>


С клиента у нас есть возможность подключиться к базе
C:>sqlplus sps/sps@v-pc-dev-3
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 27 20:36:28 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL>

Добавим в файл sqlnet.ora строки
tcp.validnode_checking=yes
tcp.excluded_nodes=(sphaera144)

и перезагружаем листенер командой
lsnrctl reloadПосле перезагрузки при подключении с клиента имеем:
C:\>sqlplus sps/sps@v-pc-dev-3
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 27 20:40:29 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12547: TNS:lost contact

Таким образом мы запретили подключаться к серверу с машины sphaera144. С остальных машин подключение разрешено.

ПРИМЕЧАНИЕ: если на сервере файла sqlnet.ora вообще не было и мы его создаем заново, то lsnrctl reload не хватает. Необходимо использовать lsnrctl stop/start.

Изменим строку в файле sqlnet.ora на сервере с tcp.excluded_nodes=(sphaera144) на tcp.invited_nodes=(sphaera144).
и перезапустим листенер lsnrctl reload.
Пробуем подключится с машины sphaera144
C:\>sqlplus sps/sps@v-pc-dev-3
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 27 20:45:43 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
С другой машины имеем
C:\>sqlplus sps/sps@v-pc-dev-3
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 27 20:40:29 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12547: TNS:lost contact


Подключение разрешено только с SPHAERA144. Со всех остальных машин подключение запрещено.

Теперь добавим обе строки:
tcp.invited_nodes=(sphaera144)
tcp.excluded_nodes=(sphaera144)

После подключения:
C:\>sqlplus sps/sps@v-pc-dev-3
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 27 20:45:43 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

INVITED_NODES имеем преимущество перед EXCLUDED_NODES

выводы
1. Если нет файла sqlnet.ora на сервере, то listener необходимо перезагружать полностью. В остальных случаях хватает lsnrctl reload
2. При добавлении адреса в invited_nodes все остальные адреса запрещены.
3. invited_nodes имеет преимущество перед excluded_nodes -- подключиться можно только с тех адресов, которые разрешены.

2010-02-07

Параметр OS_AUTHENT_PREFIX и подключение

Если приводить выдержку из документации:
OS_AUTHENT_PREFIX specifies a prefix that Oracle uses to authenticate users attempting to connect to the server. Oracle concatenates the value of this parameter to the beginning of the user's operating system account name and password. When a connection request is attempted, Oracle compares the prefixed username with Oracle usernames in the database.

Итак попробуем:

oracle@v-pc-dev-3:~> sqlplus /
ERROR:
ORA-01017: invalid username/password; logon denied

oracle@v-pc-dev-3:~> sqlplus / as sysdba
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> show parameter os_authent_prefix

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$

SQL> grant create session to ops$oracle identified by pass;
Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production

oracle@v-pc-dev-3:~> sqlplus /
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL>
Таким образом, создав пользователя OPS$ORACLE нам удалось подключится к локальному экземпляру без пароля.

Теперь попробуем поменять значение префикса (для этого придется перезапускать экземпляр)
SQL> show parameter OS_AUTHENT_PREFIX

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string XYZ$
SQL> grant create session to XYZ$ORACLE identified by pass;
Мы создали пользователя Oracle, соответствующего данному префиксу. Теперь подключаемся:

oracle@v-pc-dev-3:~> sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 7 15:05:04 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied
Упс. Не получилось.
Попробуем создать пользователя не с паролем, а внешнего:

SQL> create user XYZ$ORACLE identified externally;
User created.
SQL> grant create session to XYZ$ORACLE;
Grant succeeded.
и подключаемся снова

oracle@v-pc-dev-3:~> sqlplus /
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

Выводы
1. При локальном подключении, если OS_AUTHENT_PREFIX отличен от OPS$, то пользователь в Oracle должен быть заведен identified externally
2. Если OS_AUTHENT_PREFIX=OPS$, то пользователь может быть заведен как внешний, так и с паролем.

Подключение по сети
А теперь попробуме подключится к серверу из вне без ввода пароля. Для этого (хоть Oracle этого так и не рекомендует, установим параметр REMOTE_OS_AUTHENT=TRUE.
Теперь попробуем варианты:
1. OS_AUTHENT_PREFIX <> OPS$, identified externally

SQL> create user "XYZ$ANDREY.ZAYTSEV" identified externally;

SQL> grant connect to "XYZ$ANDREY.ZAYTSEV";
Grant succeeded.

/>sqlplus /@v-pc-dev-3
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
ПОЛУЧИЛОСЬ
2. OS_AUTHENT_PREFIX <> OPS$, identified by password

SQL> create user "XYZ$ANDREY.ZAYTSEV" identified by pass;
User created.

SQL> grant connect to "XYZ$ANDREY.ZAYTSEV";
Grant succeeded.

/>sqlplus /@v-pc-dev-3
ERROR:
ORA-01017: invalid username/password; logon denied
НЕ ПОЛУЧИЛОСЬ

3. OS_AUTHENT_PREFIX = OPS$, identified by password

SQL> create user "OPS$ANDREY.ZAYTSEV" identified by pass;
User created.

SQL> grant connect to "OPS$ANDREY.ZAYTSEV";
Grant succeeded.

C:\Documents and Settings\andrey.zaytsev>sqlplus /@v-pc-dev-3
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
ПОЛУЧИЛОСЬ
Таким образом для внешнего подключения при REMOTE_OS_AUTHENT=TRUE поведение такое же, как и для внутреннего.

Построчный вывод в SQL*Plus

Для построчного вывода в SQL*Plus:
SQL> set pause "Hit Enter"
SQL> set pagesize 1
SQL> set pause on
SQL> select rownum from all_objects where rownum <=5;
Hit Enter

1
Hit Enter

2
Hit Enter

3
Hit Enter

4
Hit Enter

5
Кроме того может пригодится ограничить количество выбираемых за раз строк:
SQL> SET ARRAYSIZE 1Данная возможность может быть полезна для проверки поведения в конкурирующих сессиях.

2010-02-06

EVENTS in oracle

EVENTS в основном применяются для снятия трейсов, дампов, включения/выключения различных фич, патчей и т.д.
Задаются при помощи параметра инициализации EVENT - например для трассировки служебных процессов при старте экземпляра, утилиты ORADEBUG или с помощью команды
alter session/system SET EVENTS 'event_number trace name context forever, level event_level'
forever означает, что событие будет действовать пока мы его не отключим. Если forever нет, то событие повторяется ровно 1 раз. Так
ALTER SESSION SET EVENTS '10046 trace name context level 1'запишет в трейс ровно 1 команду ALTER и отключится.
Перечень событий можно посмотреть:
1. В интернете, например тут
2. В файле $ORACLE_HOME/rdbms/mesg/oraus.msg или используя oerr (для linuxов)

>;oerr ora 10046
10046, 00000, "enable SQL statement timing"
// *Cause:
// *Action:
Номера большинства event от 10000 до 10999.

Интересные события
ALTER SYSTEM SET EVENTS '10231 trace name context forever, level 10';
пропуск бед-блоков при скинировании таблицы. Может помочь, если база вдруг нагнулась.

Снятие дампов
EVENTS можно использовать для снятия различных дампов, в случае возникновения ошибки:
ALTER {SESSION|SYSTEM} SET EVENTS 'error_code TRACE NAME dump_name LEVEL lvl'
Список доступных дампов посмотреть oradebug dumplist
Дамп "просто так":
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME dump_name LEVEL lvl'например
ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10';

P.S. Примеры взяты из книжки Norbert Debes "Secrets of the Oracle Database". Книга очень достойная