Об именах

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

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

• 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 база нормально открылась