[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的引數很容易亂.
--//注意最后的收尾還原!!
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/547565.html
標籤：Oracle
上一篇：資料庫---單表查詢