【监听】【异常】启动数据库 ORA-00119: invalid specification for system parameter LOCAL_LISTENER
程序员文章站
2024-03-24 16:18:52
...
首先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_但是不推荐最后这种方法。
[aaa@qq.com 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
[aaa@qq.com admin]$ cat tnsnames.ora
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.17.133)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =PDB1)
)
)
[aaa@qq.com 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
[aaa@qq.com 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
[aaa@qq.com 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.)
[aaa@qq.com admin]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-AUG-2018 10:49:41
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/product/oracle/12.1.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/product/oracle/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/product/oracle/diag/tnslsnr/zhangqian/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zhangqian)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zhangqian)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 02-AUG-2018 10:49:43
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/product/oracle/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/product/oracle/diag/tnslsnr/zhangqian/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zhangqian)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[aaa@qq.com admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-AUG-2018 10:49:46
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zhangqian)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 02-AUG-2018 10:49:43
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/product/oracle/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/product/oracle/diag/tnslsnr/zhangqian/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zhangqian)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[aaa@qq.com admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 2 10:49:49 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> alter system register;
System altered.
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
[aaa@qq.com admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-AUG-2018 10:50:11
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zhangqian)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 02-AUG-2018 10:49:43
Uptime 0 days 0 hr. 0 min. 27 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/product/oracle/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/product/oracle/diag/tnslsnr/zhangqian/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zhangqian)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=zhangqian)(PORT=5500))(Security=(my_wallet_directory=/u01/product/oracle/admin/oracs133/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "oracs133" has 1 instance(s).
Instance "oracs133", status READY, has 1 handler(s) for this service...
Service "oracs133XDB" has 1 instance(s).
Instance "oracs133", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "oracs133", status READY, has 1 handler(s) for this service...
The command completed successfully
[aaa@qq.com admin]$ tnsping pdb1
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 02-AUG-2018 10:50:15
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)))
OK (10 msec)
[aaa@qq.com admin]$ sqlplus scott/aaa@qq.com
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 2 10:50:26 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Wed Aug 01 2018 11:30:16 +08:00
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 user
USER is "SCOTT"
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
[aaa@qq.com admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 2 10:51:10 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 parmeter local_listener
SP2-0158: unknown SHOW option "parmeter"
SP2-0735: unknown SHOW option beginning "local_list..."
SQL> show paremter local
SP2-0158: unknown SHOW option "paremter"
SP2-0158: unknown SHOW option "local"
SQL> show parameter local
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
local_listener string
parallel_force_local boolean
FALSE
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
[aaa@qq.com admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/product/oracle/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
▽
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.17.133)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =PDB1)
)
)
LISTENER_zq=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = zhangqian)(PORT = 1521)))
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"tnsnames.ora" 11L, 266C written
(ADDRESS = (PROTOCOL = TCP)(HOST = zhangqian)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
[aaa@qq.com admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 2 10:52:15 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> alter system set local_listener='listener_zq';
alter system set local_listener='listener_zq'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'listener_zq'
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
[aaa@qq.com admin]$ cat nt
cat: nt: No such file or directory
[aaa@qq.com admin]$ cat tnsnames
cat: tnsnames: No such file or directory
[aaa@qq.com admin]$ cat tnsnames.ora
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.17.133)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =PDB1)
)
)
LISTENER_PDB1=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = zhangqian)(PORT = 1521)))
[aaa@qq.com admin]$ vi tnsnames.ora
[aaa@qq.com admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 2 10:53: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> alter system set local_listener='listener_zq';
System altered.
SQL> show parameter local_listener
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
local_listener string
listener_zq
SQL> shutdonw immediate
SP2-0734: unknown command beginning "shutdonw i..." - rest of line ignored.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
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
[aaa@qq.com admin]$ ls
listener.ora samples shrept.lst tnsnames18080111AM1524.bak tnsnames.ora
[aaa@qq.com admin]$ cd ../../
[aaa@qq.com db_1]$ ls
addnode cfgtoollogs dbs dv javavm log odbc ord precomp rest sqlpatch usm
apex clone dc_ocm has jdbc md olap oui QOpatch root.sh sqlplus utl
assistants crs deinstall hs jdk mgw OPatch owm R scheduler srvm wwg
bin css demo install jlib network opmn perl racg slax suptools xdk
ccr ctx diagnostics instantclient ldap nls oracore plsql rdbms sqldeveloper sysman
cdata cv dmu inventory lib oc4j oraInst.loc plugins relnotes sqlj ucp
[aaa@qq.com db_1]$ cd dbs
[aaa@qq.com dbs]$ ls
hc_oracs133.dat init.ora initoracs133.ora lkORACS133 orapworacs133 spfileoracs133.ora
[aaa@qq.com dbs]$ strings spfileoracs133.ora
oracs133.__data_transfer_cache_size=0
oracs133.__db_cache_size=603979776
oracs133.__java_pool_size=16777216
oracs133.__large_pool_size=33554432
oracs133.__oracle_base='/u01/product/oracle'#ORACLE_BASE set from environment
oracs133.__pga_aggregate_target=637534208
oracs133.__sga_target=956301312
oracs133.__shared_io_pool_size=50331648
oracs133.__shared_pool_size=234881024
oracs133.__streams_pool_size=0
*.audit_file_dest='/u01/product/oracle/admin/oracs133/adump'
*.audit_trail='db'
ompatible='12.1.0.2.0'
*.control_files='/u01/product/oracle/oradata/ORACS133/controlfile/o1_mf_fp09r44z_.ctl','/u01/product/oracle/fast_recovery_area/ORACS133/controlfile/o1_mf_fp09r48c_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/product/oracle/oradata'
*.db_domain=''
*.db_name='oracs133'
*.db_recovery_file_dest='/u01/product/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/product/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracs133
XDB)'
*.enable_pluggable_database=true
*.local_listener='listener_zq'
*.memory_target=1508m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[aaa@qq.com dbs]$ cat initoracs133.ora
oracs133.__data_transfer_cache_size=0
oracs133.__db_cache_size=553648128
oracs133.__java_pool_size=16777216
oracs133.__large_pool_size=150994944
oracs133.__oracle_base='/u01/product/oracle'#ORACLE_BASE set from environment
oracs133.__pga_aggregate_target=637534208
oracs133.__sga_target=956301312
oracs133.__shared_io_pool_size=0
oracs133.__shared_pool_size=218103808
oracs133.__streams_pool_size=0
*.audit_file_dest='/u01/product/oracle/admin/oracs133/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/product/oracle/oradata/ORACS133/controlfile/o1_mf_fp09r44z_.ctl','/u01/product/oracle/fast_recovery_area/ORACS133/controlfile/o1_mf_fp09r48c_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/product/oracle/oradata'
*.db_domain=''
*.db_name='oracs133'
*.db_recovery_file_dest='/u01/product/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/product/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracs133XDB)'
*.enable_pluggable_database=true
*.memory_target=1508m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[aaa@qq.com dbs]$ ls
hc_oracs133.dat init.ora initoracs133.ora lkORACS133 orapworacs133 spfileoracs133.ora
[aaa@qq.com dbs]$ spfileoracs133.ora
The network connection was aborted by the local system.上述排除代码中常用命令：
SQL> show parameter db_name
SQL> set linesize 400
SQL> show pdbs
//查看是否已开启监听
[aaa@qq.com admin]$ tnsping pdb1
[aaa@qq.com admin]$ lsnrctl status
[aaa@qq.com admin]$ netstat -anpt|grep 1521
//手动开启监听，如果start后面没有选项，则开启LINSTENETR.ora中的默认监听
[aaa@qq.com admin]$ lsnrctl start
SQL> alter system register;
//使用scott用户登录
[aaa@qq.com admin]$ sqlplus scott/aaa@qq.com
//更改监听
SQL> alter system set local_listener='listener_zq';
SQL> show parameter local_listener
[aaa@qq.com dbs]$ strings spfileoracs133.ora