[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/book@192.168.100.78:1521/yyyy 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/book@192.168.100.78:1521/zzzz 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/book@192.168.100.78:1521/booK 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/book@192.168.100.78:1521/book 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/book@192.168.100.78:1521/yyyy 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/book@192.168.100.78:1521/zzzz 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的参数很容易乱.
–//注意最后的收尾还原!!