[20230320]oracle各种name参数.txt

[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的参数很容易乱.
--//注意最后的收尾还原!!

热门相关:无量真仙   致灿烂的你   薄先生,情不由己   仗剑高歌   情生意动