oracle Oracles 'alter system set local_listener' 究竟做了什么

What does Oracles 'alter system set local_listener' do exactly

提问by Code Novice

Why I am curious:

为什么我很好奇：

I have always had issues when installing an Oracle Express Database. As in I have never had it work right the first time round. The next time I boot my computer after installing Oracle Express I consistently receive the common error:

我在安装 Oracle Express 数据库时总是遇到问题。因为我从来没有第一次就让它正常工作。下次在安装 Oracle Express 后启动计算机时，我始终收到常见错误：

TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

TNS-12505：TNS:listener 当前不知道连接描述符中给出的 SID

I have managed to fix this issue using various methods however I have learned that the best method that works for me these days is to Reconfigure the Listener using SQL Plus. When the Listener fails SQL Plus is the only way I can connect to the OracleXE Server and after searching the Internet I'm not alone here.

我已经使用各种方法设法解决了这个问题，但是我了解到现在最适合我的方法是使用 SQL Plus 重新配置监听器。当侦听器失败时，SQL Plus 是我可以连接到 OracleXE 服务器的唯一方法，并且在搜索 Internet 后，我​​并不孤单。

The DDL that fixes this error - run from SQL Plus:

修复此错误的 DDL - 从 SQL Plus 运行：

alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))' scope=both;

The Question:

问题：

Why does re-configuring the Listener work? What does it do? I monitored my listener.ora file along with my TNSNames.ora file and they remain the same before and after I run the above code. So what did it do?

为什么重新配置监听器会起作用？它有什么作用？我监控了我的 listener.ora 文件和我的 TNSNames.ora 文件，它们在我运行上面的代码之前和之后都保持不变。那么它做了什么？

I read this entire Oracle LISTENERDocumentation and I did not come out of it enlightened. Can anyone enlighten me?

我阅读了整个 Oracle LISTENER文档，但并没有从中得到启发。任何人都可以启发我吗？

回答by Alex Poole

Why does re-configuring the Listener work?

为什么重新配置监听器会起作用？

You are notreconfiguring the listener. That's why you don't see the listener configuration file change. You are changing the database configuration. The spfileis updated because the command you used had scope=both, which means the change is applied immediately - in memory - and written to that file, so it persists on database restart.

您不是在重新配置侦听器。这就是为什么您看不到侦听器配置文件更改的原因。您正在更改数据库配置。在spfile因为使用过的命令更新scope=both内存- -这意味着变化，将立即应用，并写入该文件，因此它仍然存在于数据库重新启动。

From the docs:

从文档：

LOCAL_LISTENERspecifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that run on the same system as this instance).

LOCAL_LISTENER指定解析为 Oracle Net 本地侦听器（即与此实例在同一系统上运行的侦听器）的地址或地址列表的网络名称。

and the default is:

默认值为：

(ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))where hostnameis the network name of the local host.

(ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))其中hostname是本地主机的网络名称。

See also the alter system REGISTERclause:

另见的alter system REGISTER条款：

Specify REGISTERto instruct the PMON background process to register the instance with the listeners immediately. If you do not specify this clause, then registration of the instance does not occur until the next time PMON executes the discovery routine. As a result, clients may not be able to access the services for as long as 60 seconds after the listener is started.

指定REGISTER以指示 PMON 后台进程立即向侦听器注册实例。如果您不指定此子句，则直到 PMON 下次执行发现例程时才会注册实例。因此，在侦听器启动后的 60 秒内，客户端可能无法访问服务。

So what this means is that when the database starts, and then periodically, it tries to register its service name(s) with the listener; and it gets the info about the listener from the local_listenerparameter. (There's also a remote_listenerfor data guard, not relevant here.)

所以这意味着当数据库启动时，然后定期尝试向侦听器注册其服务名称；它从local_listener参数中获取有关侦听器的信息。（还有一个remote_listener数据保护，这里不相关。）

Because the default is hostnamethe PC name is used directly in that parameter, and that will often resolve to the PCs LAN IP address (which can change each reboot to add to the confusion, if the IP is assigned by DHCP), which could be stored instead. If you're lucky the hostname will resolve to the same as localhost, but that isn't the case for you.

因为默认是主机名，PC 名称直接在该参数中使用，这通常会解析为 PC 的 LAN IP 地址（如果 IP 是由 DHCP 分配的，则每次重新启动都会更改以增加混乱），这可能是改为存储。如果幸运的话，主机名将解析为与 localhost 相同，但对您而言并非如此。

So... the database looks up its init parameter, and as a result tries to register with a listener at say 192.168.1.2. But the listener has started on localhost so it is listening on 127.0.0.1. The DB fails to register its service name, as it can't reach a listener; though that is only apparent if you look in the alert log. If you run lsnrctl servicesit won't show anything.

所以……数据库查找它的 init 参数，结果尝试在 192.168.1.2 处注册一个监听器。但是侦听器已在 localhost 上启动，因此它正在侦听 127.0.0.1。DB 无法注册其服务名称，因为它无法访问侦听器；虽然这只有在您查看警报日志时才会明显。如果你运行lsnrctl services它不会显示任何东西。

When you change the init parameter you are telling the DB to try to register against a listener on localhost instead - and as that is where it is actually listening, registration now works, and the listener recognises the service name on subsequent connection attempts. (Your question refers to the error message about SID, which is different, and not solved by your change.) Running lsnrctl serviceswill now show the service name too. But that is a runtime, dynamic thing via the registration - not a change to the permanent configuration of the listener.

当您更改 init 参数时，您是在告诉 DB 尝试在 localhost 上的侦听器上注册 - 由于这是它实际侦听的地方，注册现在可以工作，并且侦听器在随后的连接尝试中识别服务名称。（您的问题是指有关 SID 的错误消息，这是不同的，并且您的更改无法解决。）lsnrctl services现在运行也会显示服务名称。但这是一个运行时，通过注册动态的事情 - 而不是对侦听器的永久配置的更改。

---

---

It's possible to have the listener listen on more than one address. The main thing is that the listener.ora, tnsnames.ora(if you use TNS aliases) and init parameter use consistent host names or IP addresses, so they are all resolving to and referring to the same thing, whether that is localhost (only reachable from that PC), or a LAN address (reachable across the nwtwork) or both.

可以让听众监听多个地址。主要的是listener.ora, tnsnames.ora（如果您使用 TNS 别名）和 init 参数使用一致的主机名或 IP 地址，因此它们都解析并引用相同的东西，无论是 localhost（只能从那台 PC 访问），或 LAN 地址（可通过 nwtwork 访问）或两者兼而有之。

You can also have an entry in the tnsnames.orafor the listener itself, rather than just for DBs. You can then use that TNS alias as the local_listenertarget, instead of spelling put the address and port, possibly making it easier to change later if needed.

您还可以tnsnames.ora在侦听器本身中创建一个条目，而不仅仅是数据库。然后，您可以使用该 TNS 别名作为local_listener目标，而不是拼写输入地址和端口，这可能使以后在需要时更容易更改。