這篇文章主要介紹“Oracle怎么連接配置數(shù)據(jù)庫”，在日常操作中，相信很多人在Oracle怎么連接配置數(shù)據(jù)庫問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”O(jiān)racle怎么連接配置數(shù)據(jù)庫”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！
從連接端講起。
1、連接數(shù)據(jù)庫的方式
oracle的連接串有幾部分構(gòu)成，這里就按sqlplus為例，一個完成的連接串遵循下面格式
sqlplus 用戶名/密碼@主機(jī):端口號/SID 可選as sysdba
下面先講只有l(wèi)istener沒有tns的情況
2、listener
使用listener連接需要配置完整連接信息，這里分為兩種連接方式，我們看一個listener的例子：
(帶sid的listener使用netmgr增加listener的datavase services即可出現(xiàn)sid的配置)
LISTENER2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = iZbp1d4tisi44j6vxze02fZ)(PORT = 1522))
)
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = gdn1400)
(ORACLE_HOME = /fdisk1/oracle1400/base/dbhome_1)
(SID_NAME = orcl1400)
)
)
ADR_BASE_LISTENER2 = /fdisk1/oracle1400/base
LISTENER1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = iZbp1d4tisi44j6vxze02fZ)(PORT = 1521))
)
ADR_BASE_LISTENER1 = /fdisk1/oracle1400/base
這里有兩個listener，有sid的叫做靜態(tài)listener，沒有sid的叫做動態(tài)listener。在查看狀態(tài)時存在區(qū)別：
lsnrctl status listener1
...
Services Summary...
Service "orcl1400" has 1 instance(s).
Instance "orcl1400", status READY, has 1 handler(s) for this service...
Service "orcl1400XDB" has 1 instance(s).
Instance "orcl1400", status READY, has 1 handler(s) for this service...
The command completed successfully
lsnrctl status listener2
...
Services Summary...
Service "gdn1400" has 1 instance(s).
Instance "orcl1400", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
注意對于lsnrctl來說，service的名字是global database name
我們在看一個連接串：
sqlplus sys/password@iZbp1d4tisi44j6vxze02fZ:1521/orcl1400 as sysdba
這個連接串中最需要關(guān)注的就是服務(wù)名了，這里是orcl1400。
注意!：這個服務(wù)名必須由listener中的某一個提供，這里listener2的服務(wù)名提供的是gdn1400，而listener1沒有提供服務(wù)名。那么如何連接數(shù)據(jù)庫呢?答案就是走listener1的連接會去數(shù)據(jù)庫中動態(tài)的查詢服務(wù)名(所以叫做動態(tài)連接)
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl1400
也就是為什么使用orcl1400能連上數(shù)據(jù)庫。這里我們使用靜態(tài)連接試一下，也是可以連接數(shù)據(jù)庫的(注意端口號和動態(tài)的不同)
sqlplus sys/password@iZbp1d4tisi44j6vxze02fZ:1522/gdn1400 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu May 30 20:51:00 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
3、TNS
那么tns是什么呢?我們看下這個連接串。
sqlplus sys/password@iZbp1d4tisi44j6vxze02fZ:1521/orcl1400 as sysdba
@后面的信息很多，能否省略呢?比如
sqlplus sys/password@tns1400 as sysdba
這樣看起來簡潔很多也便于管理，TNS即實現(xiàn)了這個功能，我們看一個tnsnames.ora的配置：
NSN1522 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = iZbp1d4tisi44j6vxze02fZ)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = gdn1400)
)
)
NSN1521 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = iZbp1d4tisi44j6vxze02fZ)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl1400)
)
)
這里可以看到，最左面的NSN1522和NSN1521就是我們可以@的服務(wù)名字。內(nèi)部的映射信息是指向listener的，這里的servicename要和上面的global database name對應(yīng)上!
NSN1522, iZbp1d4tisi44j6vxze02fZ, 1522, gdn1400 -----> listener2
NSN1521, iZbp1d4tisi44j6vxze02fZ, 1521, orcl1400 -----> listener1
兩個別名指向了兩個不同的listener，連接測試：
# sqlplus sys/password@nsn1521 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu May 30 20:58:51 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
# sqlplus sys/password@nsn1522 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu May 30 20:58:55 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
注意：tns依賴lsnrctl使用，可以理解為hostname這樣的概念，注意tns的SERVICE_NAME等信息必須和listener關(guān)聯(lián)才能連接!
到此，關(guān)于“Oracle怎么連接配置數(shù)據(jù)庫”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
分享名稱：Oracle怎么連接配置數(shù)據(jù)庫-創(chuàng)新互聯(lián)
網(wǎng)站URL：http://weahome.cn/article/gpeej.html
重慶分公司
重慶分公司
