[20180509]配置靜態監聽服務與ORA-12514
[20180509]配置靜態監聽服務與ORA-12514錯誤.txt
--//昨天幫別人解決ORA-12514錯誤問題.主要靜態監聽服務名不正確,
--//我一般配置靜態監聽服務名與例項名一樣.而對方配置的服務名使用地點(比如南大樓,北大樓之類的名字),還真不習慣.
--//透過例子說明,有一些概念還是很容易混亂的.
1.環境:
SYS@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
SYS@book> create pfile='/tmp/@.ora' from spfile;
File created.
--//修改/tmp/book.ora檔案:
*.service_names='BOOK'
$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
#SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=32767)
(GLOBAL_DBNAME = book123)
(ARGV0=myapp0)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME=book)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(ARGV0=LLLLLL)(HOST = 0.0.0.0)(PORT = 1521)(ARGV0=KKKKK))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SECURE_REGISTER_LISTENER = (TCP)
ADR_BASE_LISTENER = /u01/app/oracle
DIAG_ADR_ENABLED_LISTENER=OFF
USE_NS_PROBES_FOR_DCD=true
#logging_listener=off
#DYNAMIC_REGISTRATION_LISTENER = off
#local_os_authentication_LISTENER=off
INBOUND_CONNECT_TIMEOUT_LISTENER=10
#CONNECTION_RATE_LISTENER=5
--//注:監聽配置的靜態服務名為book123.監聽已經重啟.
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 09-MAY-2018 11:20:56
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 03-MAY-2018 16:44:12
Uptime 5 days 18 hr. 36 min. 44 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
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 "book", status READY, has 2 handler(s) for this service...
Service "book123" has 1 instance(s).
Instance "book", status UNKNOWN, has 1 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ lsnrctl status
...
Services Summary...
Service "book123" has 1 instance(s).
Instance "book", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
--//你可以Service book,bookXDB已經不存在的,因為資料庫已經關閉,僅僅剩下靜態監聽服務名.
--//這也是為什麼status UNKNOWN的原因,因為這個例項可以不存在.正是存在這樣的服務,你可以在
--//資料庫沒有啟動的情況下遠端連線例項,啟動資料庫例項.
R:\>sqlplus sys/oracle@192.168.100.78:1521/book123 as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 9 11:26:39 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@192.168.100.78:1521/book123>
--//把/tmp/book.ora複製到本地的R盤.
SYS@192.168.100.78:1521/book123> startup pfile=r:/book.ora
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
$ lsnrctl status
...
Services Summary...
Service "book" has 1 instance(s).
Instance "book", status READY, has 2 handler(s) for this service...
Service "book123" has 1 instance(s).
Instance "book", status UNKNOWN, has 1 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully
--//這樣可以發現2個服務book,bookXDB. 注意status READY,表示動態註冊.
R:\>sqlplus -l scott/book@192.168.100.78:1521/Book123
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 9 11:38:22 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@192.168.100.78:1521/Book123>
--//可以發現使用服務名book123 ok.(注:我故意大小寫混用).如果使用一個不存在的服務名如下:
R:\>sqlplus -l scott/book@192.168.100.78:1521/bookshare
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 9 11:02:58 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
--//對方的錯誤就是在這裡,服務名拼寫錯誤!!
--//可以發現如果要使用bookshare,2種方式,一種配置靜態監聽,一種方式寫入資料庫引數檔案加入對應的服務名.
--//配置靜態監聽必須修改引數*.service_names='BOOK','BOOKSHARE'.
SYS@book> alter system set service_names='book','bookshare' scope=memory;
System altered.
SYS@book> show parameter service_names
NAME TYPE VALUE
------------- ------ ---------------
service_names string book, bookshare
--//注意我服務名使用引號,這樣建立的是小寫的服務名.如果alter system set service_names=book,bookshare scope=memory;
--//建立的就是大寫的服務名.不過好像在具體執行時不區分大小寫.
$ lsnrctl status | grep -A20 "^Services Summary."
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
Instance "book", status READY, has 1 handler(s) for this service...
Service "book" has 1 instance(s).
Instance "book", status READY, has 2 handler(s) for this service...
Service "book123" has 1 instance(s).
Instance "book", status UNKNOWN, has 1 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully
--//oracle有時候很奇怪,這裡看到的Service BOOKSHARE(大寫!!)
--//補充測試:
--//修改/tmp/book.ora檔案:
*.service_names='BOOK','bookshare'
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup pfile=/tmp/book.ora
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
SYS@book> show parameter service_names
NAME TYPE VALUE
------------- ------ ---------------
service_names string BOOK, bookshare
$ lsnrctl status | grep -A20 "^Services Summary."
Services Summary...
Service "book" has 1 instance(s).
Instance "book", status READY, has 2 handler(s) for this service...
Service "book123" has 1 instance(s).
Instance "book", status UNKNOWN, has 1 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
Instance "book", status READY, has 1 handler(s) for this service...
Service "bookshare" has 1 instance(s).
Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully
--//這樣配置bookshare是小寫.
R:\>sqlplus -l scott/book@192.168.100.78:1521/BookShare
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 9 11:45:08 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@192.168.100.78:1521/BookShare>
--//好在服務名不區分大小寫,問題不大.
--//修改監聽配置檔案listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=32767)
(GLOBAL_DBNAME = booK123)
(ARGV0=myapp0)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME=book)
)
)
$ lsnrctl stop;lsnrctl start
SYS@book> alter system register;
System altered.
$ lsnrctl status | grep -A20 "^Services Summary."
Services Summary...
Service "booK123" has 1 instance(s).
Instance "book", status UNKNOWN, has 1 handler(s) for this service...
Service "book" has 1 instance(s).
Instance "book", status READY, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
Instance "book", status READY, has 1 handler(s) for this service...
Service "bookshare" has 1 instance(s).
Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully
R:\>sqlplus -l scott/book@192.168.100.78:1521/Book123
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 9 11:49:20 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@192.168.100.78:1521/Book123>
--//亂..
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2154220/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle靜態監聽Oracle
- [20190306]靜態監聽配置sid大小寫問題.txt
- Nginx靜態服務Nginx
- 靜態路由原理與配置路由
- Nginx配置靜態檔案服務從入門到精通Nginx
- oracle監聽不到例項服務Oracle
- Nginx服務系列——靜態資源web服務NginxWeb
- centos 7 下搭建zabbix監聽服務CentOS
- Blazor靜態服務端呈現（靜態SSR）身份認證Blazor服務端
- 寶塔部署靜態服務遇到問題與解決
- Oracle靜態監聽中SID_NAME區分大小寫嗎？Oracle
- [20200115]監聽中沒有xdb服務.txt
- 靜態域與靜態方法
- QTcpServer實現web靜態資源服務QTTCPServerWeb
- 靜態網站託管服務平臺網站
- [重慶思莊每日技術分享]-監聽的靜默配置安裝
- 【監聽配置】Oracle如何靜默執行NETCA，使用netca.rsp檔案Oracle
- Rpc-實現Client對ZooKeeper的服務監聽RPCclient
- Windows平臺如何修改監聽的服務名稱？Windows
- Oracle監聽器中的XDB、XPT和PLSExtProc服務Oracle
- go-zero微服務框架的靜態檔案服務Go微服務框架
- CentOS配置靜態IPCentOS
- linux靜態ip 配置Linux
- 2.5.2. 監聽程式（listener）配置——2.5.2.3. 手工編輯監聽器配置檔案
- net 靜態方法與非靜態方法
- JavaScript 靜態屬性與靜態方法JavaScript
- 《前端運維》二、Nginx--3靜態資源服務、跨域與其他前端運維Nginx跨域
- npmscript命令同時開啟多個監聽服務concurrentlyNPM
- 網路配置2：靜態路由配置路由
- 靜態路由規則配置路由
- 網站偽靜態配置網站
- Linux中配置靜態IPLinux
- Apache靜態快取配置Apache快取
- Liunx配置靜態ip VMware
- Etcd叢集靜態配置
- Ubuntu 16.04 配置靜態 ipUbuntu
- nginx 配置單IP無域名多埠實現靜態檔案和node服務並存Nginx
- rac監聽不能動態註冊