本文测试一个不太常见的错误 ora-119.
错误解释是：
“invalid specification for system parameter %s”
这个所谓的 ‘system parameter’ 目前常见情况是指某种需要从DB外部获得解析的配置。
常见的如 域名解析 不存在，TNS name 解析不存在 等情况。
出现场景举例如下：
(主机名/IP地址为隐藏替换值 ，非实际值，但是不影响结果)
STEP 1. 确认名称解析初始配置。 这里没有DNS 服务干预，名称解析是通过/etc/hosts 文件。
[root@nascds10 etc]# cat hosts <<<<=============
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
#public
1.2.3.2 nascds10.test.com nascds10
1.2.3.3 nascds11.test.com nascds11
#VIP
1.2.3.4 nascds10-vip.test.com nascds10-vip
1.2.3.5 nascds11-vip.test.com nascds11-vip
#private
10.1.1.1 nascds10-pvt
10.1.1.2 nascds11-pvt
#scan
1.2.3.10 cluster-scan1 <<<<============= it is available .
STEP 2. 修改 hosts 文件，注销 scan 名称解析条目
[root@nascds10 etc]# vi hosts <<<<============= modify it .
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
#public
1.2.3.2 nascds10.test.com nascds10
1.2.3.3 nascds11.test.com nascds11
#VIP
1.2.3.4 nascds10-vip.test.com nascds10-vip
1.2.3.5 nascds11-vip.test.com nascds11-vip
#private
10.1.1.1 nascds10-pvt
10.1.1.2 nascds11-pvt
#scan
#1.2.3.10 cluster-scan1 <<<<============= disable this item.
~
~
“hosts” 19L, 521C written
STEP 3. 确认remote_listener 是指向修改的 scan resolution。并重启，测试重启过程中的初始化参数读取。
[root@nascds10 etc]#
[root@nascds10 etc]# su – oracle
[oracle@nascds10 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 11 00:06:37 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter remote
NAME TYPE VALUE
———————————— ———– ——————————
remote_dependencies_mode string TIMESTAMP
remote_listener string cluster-scan1:1521 <<<<====== remote_listener is referred to /etc/hosts : “1.2.3.10 cluster-scan1”
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
result_cache_remote_expiration integer 0
SQL>
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down. <<<<=============
SQL>quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@nascds10 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 11 00:07:16 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup <<<<=============
ORA-00119: invalid specification for system parameter REMOTE_LISTENER <<<<=============
ORA-00132: syntax error or unresolved network name ‘cluster-scan1:1521’
SQL>
—————————————–
再另开一个session ，观察 alert logs:
[oracle@nascds10 trace]$ tail -f alert_ora11g1.log
……
Sat Nov 11 00:08:07 2017
Adjusting the requested value of parameter parallel_max_servers
from 250 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal) <<<<=============
****************** Large Pages Information *****************
Total Shared Global Region in Large Pages = 0 KB (0%)
Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 4096 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 4096 KB
……
remote_listener = “cluster-scan1:1521”
parallel_min_servers = 50
parallel_max_servers = 135
audit_file_dest = “/u01/app/oracle/admin/ora11g/adump”
audit_trail = “DB”
db_name = “ora11g”
open_cursors = 300
pga_aggregate_target = 48M
parallel_force_local = TRUE
diagnostic_dest = “/u01/app/oracle”
Cluster communication is configured to use the following interface(s) for this instance
169.254.33.67
cluster interconnect IPC version:Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
Sat Nov 11 00:08:17 2017
USER (ospid: 13857): terminating the instance due to error 119 <<<<=============
Instance terminated by USER, pid = 13857
Comments
===================
可见，以上是因为db启动需要解析参数remote_listener， 而无法从/etc/hosts 获得该对应解析 “cluster-scan1”.
~~~~~~~~~~~~~~~~~~~~~~~
STEP 4. 恢复正确解析，并重启， 观察启动过程中读取参数并解析。
SQL> startup
ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name ‘cluster-scan1:1521’
SQL> quit <<<<=============
Disconnected
[oracle@nascds10 ~]$
[oracle@nascds10 ~]$
[oracle@nascds10 ~]$ su – root
Password:
[root@nascds10 ~]#
[root@nascds10 ~]# cat /etc/hosts <<<<=============
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
#public
1.2.3.2 nascds10.test.com nascds10
1.2.3.3 nascds11.test.com nascds11
#VIP
1.2.3.4 nascds10-vip.test.com nascds10-vip
1.2.3.5 nascds11-vip.test.com nascds11-vip
#private
10.1.1.1 nascds10-pvt
10.1.1.2 nascds11-pvt
#scan
#1.2.3.10 cluster-scan1 <<<<=============
[root@nascds10 ~]#
[root@nascds10 ~]#
[root@nascds10 ~]# vi /etc/hosts <<<<=============
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
#public
1.2.3.2 nascds10.test.com nascds10
1.2.3.3 nascds11.test.com nascds11
#VIP
1.2.3.4 nascds10-vip.test.com nascds10-vip
1.2.3.5 nascds11-vip.test.com nascds11-vip
#private
10.1.1.1 nascds10-pvt
10.1.1.2 nascds11-pvt
#scan
1.2.3.10 cluster-scan1 <<<<============= modified hosts to re-enable the SCAN name resolution .
~
“/etc/hosts” 19L, 520C written
[root@nascds10 ~]#
[root@nascds10 ~]# exit
logout
[oracle@nascds10 ~]$ sqlplus / as sysdba <<<<=============
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 11 00:28:54 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
linesize 130
SQL> startup <<<<=============
ORACLE instance started.
Total System Global Area 167387136 bytes
Fixed Size 1343668 bytes
Variable Size 130027340 bytes
Database Buffers 33554432 bytes
Redo Buffers 2461696 bytes
Database mounted.
Database opened. <<<<============= it is okay .
SQL>
===========================
尝试删除TNSNAMES.ora中的 tns name 解析，然后在DB 中指定 local_listener 到这个不存在的 tns name，也会获得同样的错误。
即 ora-119 错误实际上是指无法从DB外部获得‘system’上的某种初始化参数值。