首先Oracle 12C是通过读取spfile(二进制文件spfileoracs133.ora)启动的,被更改后的默认参数信息记录在这个文件中,所以一般情况下spfile是没有local_listener参数的,系统会自动访问LISTENER.ora文件中的监听。BUT,如若更改过监听名(如:alter system alter system set local_listener='LISTENER_ORACS133';),则需要本文所述操作配置监听文件。
sqlplus / as sysdba
sql>create pfile from spfile;
- spfile(spfileSID.ora)不可读,需要通过“sqlplus / as sysdba”启动后,把spfile复制成可读可编辑的pfile(initSID.ora)。
- spfile如果不指定路径,默认就在$ORACLE_HOME/dbs下
- 复制出来的pfile文件,是以“initSID.ora”的方式命名的
此时,我们查看pfile的内容:
Oracle启动时执行spfile文件,依次执行上图命令,执行到“local_listener”(方框处命令,此处没有注释掉),它会去"$ORACLE_HOME/dbs/network/admin/tnsnames.ora"中去寻找'LINSTENER_ORACS133'监听参数(Oracle服务器是哪个啊,端口是哪个啊之类的信息)。
如果tnsnames.ora中没有设置,那么Oracle就找不到特定的监听去连接Oracle服务器,便会出现如标题中所提及的错误。
还有一种方法:不更改tnsnames.ora中的监听,直接在pfile中指出监听的信息,此时Oracle开启的时候使用如下命令,直接通过pfile开启Oracle。
SQL>STARTUP pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initoracs133.ora'
当然,如果想要常规的“sqlplus / as sysdba”开启,可以通过create spfile from pfile把参数直接写死在Oracle系统里。
此时可以通过如下语句检验:
SQL>show parameter local_
但是不推荐最后这种方法。
[oracle@zhangqian admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 2 10:48:32 2018
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> show parameter db_name
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_name string
oracs133
SQL> set linesize 400
SQL> r
SP2-0103: Nothing in SQL buffer to run.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@zhangqian admin]$ cat tnsnames.ora
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.17.133)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =PDB1)
)
)
[oracle@zhangqian admin]$ tnsping pdb1
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 02-AUG-2018 10:49:16
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.17.133)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =PDB1)))
TNS-12541: TNS:no listener
[oracle@zhangqian admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-AUG-2018 10:49:23
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zhangqian)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@zhangqian admin]$ netstat -anpt|grep 1521
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
[oracle@zhangqian admin]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-AUG-2018 10:49:41
Co