[20230320]oracle各种name参数.txt
--//如果讲oracle的各种name参数,很容易混乱,特别对于初学者.通过一个简单例子概括总结一下.
--//主要集中在DB_NAME, DB_UNIQUE_NAME, SERVICE_NAMES, INSTANCE_NAME参数.
--//首先oracle数据库一旦建立好,db_name就固定下来,不可以更改该参数,除非使用nid程序更改.
--//另外DB_UNIQUE_NAME用来标识主备库.
--//对于单机数据库建立前指定参数ORACLE_SID参数,这样建立数据库db_name=$ORACLE_SID.
--//实际上的情况可以不同,oracle实例是内存的结构,数据库实体(db_name)可以装入不同的实例.
--//通过一些例子演示,测试在单机数据库上进行.
0.建立查询参数脚本:
$ cat pp.txt
column DESCRIPTION format a50
column name format a20
column value format a30
select name,value,DESCRIPTION from v$parameter where name in ('db_name','db_unique_name','service_names','instance_name');
--//先看看缺省不设置的情况.
$ echo $ORACLE_SID
book
SYS@book> @pp.txt
NAME VALUE DESCRIPTION
------------- ----- ------------------------------------------
instance_name book instance name supported by the instance
service_names book service names supported by the instance
db_name book database name specified in CREATE DATABASE
db_unique_nam book Database Unique Name
-//与db_name完全一致.
1.建立参数文件:
$ cd /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ export ORACLE_SID=xxxx
$ cat initxxxx.ora
sga_target=1G
sga_max_size=1G
shared_pool_size=600M
pre_page_sga=false
*.control_files='/mnt/ramdisk/book/control01.ctl','/mnt/ramdisk/book/control02.ctl'#Restore Controlfile
*.compatible='11.2.0.4.0'
db_name=book
--//仅仅指定db_name.启动数据库.注意第一次启动有点慢是正常的.
SYS@xxxx> @ pp.txt
NAME VALUE DESCRIPTION
-------------- ----- ------------------------------------------
instance_name xxxx instance name supported by the instance
service_names book service names supported by the instance
db_name book database name specified in CREATE DATABASE
db_unique_name book Database Unique Name
--//可以发现instance_name=xxxx,等于ORACLE_SID参数.其他还是book。
2.修改DB_UNIQUE_NAME:
$ cat initxxxx.ora
sga_target=1G
sga_max_size=1G
shared_pool_size=600M
pre_page_sga=false
*.control_files='/mnt/ramdisk/book/control01.ctl','/mnt/ramdisk/book/control02.ctl'#Restore Controlfile
*.compatible='11.2.0.4.0'
db_name=book
DB_UNIQUE_NAME=yyyy
--//DB_UNIQUE_NAME=yyyy,启动数据库.
SYS@xxxx> @ pp.txt
NAME VALUE DESCRIPTION
-------------- ----- ------------------------------------------
instance_name xxxx instance name supported by the instance
service_names yyyy service names supported by the instance
db_name book database name specified in CREATE DATABASE
db_unique_name yyyy Database Unique Name
--//DB_UNIQUE_NAME=yyyy,这样缺省service_names=yyyy,也就是改动DB_UNIQUE_NAME参数，其service_names一起改变。
3.修改SERVICE_NAMES:
$ cat initxxxx.ora
sga_target=1G
sga_max_size=1G
shared_pool_size=600M
pre_page_sga=false
*.control_files='/mnt/ramdisk/book/control01.ctl','/mnt/ramdisk/book/control02.ctl'#Restore Controlfile
*.compatible='11.2.0.4.0'
db_name=book
DB_UNIQUE_NAME=yyyy
SERVICE_NAMES=zzzz
--//SERVICE_NAMES=zzzz,启动数据库.
SYS@xxxx> @ pp.txt
NAME VALUE DESCRIPTION
-------------- ----- ------------------------------------------
instance_name xxxx instance name supported by the instance
service_names zzzz service names supported by the instance
db_name book database name specified in CREATE DATABASE
db_unique_name yyyy Database Unique Name
4.修改INSTANCE_NAME:
$ cat initxxxx.ora
db_name=book
sga_target=1G
sga_max_size=1G
shared_pool_size=600M
pre_page_sga=false
*.control_files='/mnt/ramdisk/book/control01.ctl','/mnt/ramdisk/book/control02.ctl'#Restore Controlfile
*.compatible='11.2.0.4.0'
DB_UNIQUE_NAME=yyyy
SERVICE_NAMES=zzzz
INSTANCE_NAME=AAAA
SYS@xxxx> @ pp.txt
NAME VALUE DESCRIPTION
-------------- ----- ------------------------------------------
instance_name AAAA instance name supported by the instance
service_names zzzz service names supported by the instance
db_name book database name specified in CREATE DATABASE
db_unique_name yyyy Database Unique Name
5.至此,可以大致推断oracle关于这些参数的设置:
--//db_name在建库时就设置,写在数据文件与控制文件,不能更改.一般单机的情况下等于ORACLE_SID环境变量.
--//大致设置情况是:
--// $ORACLE_SID -> DB_NAME -> DB_UNIQUE_NAME -> SERVICE_NAMES
--// $ORACLE_SID -> INSTANCE_NAME
--//但是各个参数实际上都可以修改,除了DB_NAME.
--//顺便贴出最后lsnrctl status的结果.
$ lsnrctl stop;lsnrctl start
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-MAR-2023 10:27:18
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 13-MAR-2023 11:51:33
Uptime 6 days 22 hr. 35 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "booK" has 1 instance(s). --//我配置的静态服务.里面的实例book现在不存在.
Instance "book", status UNKNOWN, has 1 handler(s) for this service...
Service "yyyy" has 1 instance(s). --//会建立一个等于db_unique_name的服务名.
Instance "AAAA", status READY, has 1 handler(s) for this service...
Service "zzzz" has 1 instance(s). --//service_names.
Instance "AAAA", status READY, has 1 handler(s) for this service...
The command completed successfully
--//listener.ora存在如下内容:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 32767)
(GLOBAL_DBNAME = booK)
(ARGV0 = myapp0)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME = book)
)
)
--//如果换成SID_NAME =AAAA应该可以.注意大小写.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 32767)
(GLOBAL_DBNAME = booK)
(ARGV0 = myapp0)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME = AAAA)
)
)
$ lsnrctl stop;lsnrctl start
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-MAR-2023 10:36:17
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-MAR-2023 10:36:14
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "booK" has 1 instance(s).
Instance "AAAA", status UNKNOWN, has 1 handler(s) for this service...
Service "yyyy" has 1 instance(s).
Instance "AAAA", status READY, has 1 handler(s) for this service...
Service "zzzz" has 1 instance(s).
Instance "AAAA", status READY, has 1 handler(s) for this service...
The command completed successfully
$ rlsql -s -l scott/[email protected]:1521/yyyy <<<@ver1
PORT_STRING VERSION BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ rlsql -s -l scott/[email protected]:1521/zzzz <<<@ver1
PORT_STRING VERSION BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//但是使用book服务名连接报错!!
$ rlsql -s -l scott/[email protected]:1521/booK <<<@ver1
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
SYS@xxxx> oradebug setmypid
Statement processed.
SYS@xxxx> oradebug ipc
IPC information written to the trace file
SYS@xxxx> @ pp.txt
NAME VALUE DESCRIPTION
-------------- ----- ------------------------------------------
instance_name AAAA instance name supported by the instance
service_names zzzz service names supported by the instance
db_name book database name specified in CREATE DATABASE
db_unique_name yyyy Database Unique Name
SYS@xxxx> @ t
TRACEFILE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/yyyy/xxxx/trace/xxxx_ora_44680.trc
--//检查跟踪文件存在如下:
Handle: 0x7fc7ca1460d0 `/u01/app/oracle/product/11.2.0.4/dbhome_1xxxx'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dump of unix-generic realm handle `/u01/app/oracle/product/11.2.0.4/dbhome_1xxxx', flags = 00000000
--//Handle: 0x7fc7ca1460d0 `/u01/app/oracle/product/11.2.0.4/dbhome_1xxxx'
--//listener.ora改写如下:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 32767)
(GLOBAL_DBNAME = Book)
(ARGV0 = myapp0)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME = xxxx)
)
)
--//注意大小写要一致!!
$ lsnrctl stop;lsnrctl start
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-MAR-2023 11:17:48
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-MAR-2023 11:17:44
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "Book" has 1 instance(s).
Instance "xxxx", status UNKNOWN, has 1 handler(s) for this service... --//instance与下面的不同。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Service "yyyy" has 1 instance(s).
Instance "AAAA", status READY, has 1 handler(s) for this service...
Service "zzzz" has 1 instance(s).
Instance "AAAA", status READY, has 1 handler(s) for this service...
The command completed successfully
$ rlsql -s -l scott/[email protected]:1521/book <<<@ver1
PORT_STRING VERSION BANNER
------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//ok!!是否可以这样理解配置 静态服务的SID_NAME实际上等于$ORACLE_SID.而动态服务使用INSTANCE_NAME参数.
$ rlsql -s -l scott/[email protected]:1521/yyyy <<<@ver1
PORT_STRING VERSION BANNER
------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ rlsql -s -l scott/[email protected]:1521/zzzz <<<@ver1
PORT_STRING VERSION BANNER
------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//OK1!全部可以成功.
--//总之name的参数很容易乱.
--//注意最后的收尾还原!!