Oracle-ORA-12514-动态及静态监听

    设置数据库归档模式时，提示如下错误。问题原因是因为动态监听在实例关闭时会被注销。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    监听在服务启动后会自动获取已启动的服务，但是重启时无法获取服务，数据库停止时监听就断开了。需要在listener.ora增加静态监听配置，如下图：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oggtarget)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = oggtarget)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = oggtest)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = oggtest)
    )
  )

E:\app\Administrator\product\11.2.0\dbhome_1是ORACLE_HOME

  这时再次启动监听，lsnrtcl-start，就会发现监听服务出现在列表当中，重新挂载数据库成功。

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
Log messages written to e:\app\administrator\diag\tnslsnr\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=y.com.cn)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ya.com.cn)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                18-NOV-2019 13:09:43
Uptime                    0 days 0 hr. 0 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
Listener Log File         e:\app\administrator\diag\tnslsnr\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=y.com.cn)(PORT=1521)))
Services Summary...
Service "oggtarget" has 1 instance(s).
  Instance "oggtarget", status UNKNOWN, has 1 handler(s) for this service...
Service "oggtest" has 1 instance(s).
  Instance "oggtest", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

    此时再次挂载数据库，并启动归档成功，如下：

SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             553648592 bytes
Database Buffers          511705088 bytes
Redo Buffers                4603904 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>

    关于动态监听和静态监听：

    动态只有在数据库启动的情况下，才能连接。

    静态则无论数据库是开启还是关闭均能连接。

    另：

　　启动监听控制lsnrctl

　　查看监听状态status

　　启动start

　　关闭stop