[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/book@192.168.100.78:1521/yyyy

继续阅读请点击广告