配置静态监听解决ORA-12514错误的案例

今天做Linux下DG配置的时候，遇到一个现象，tnsname.ora文件配置都正常，tnsping也正常，监听也正常，但是仍然报ORA-12514错误：

配置静态监听解决ora-12514错误的案例

[日期：2014-10-08] 来源：Linux社区  作者：aaron8219 [字体：]

今天做Linux下DG配置的时候，遇到一个现象，tnsname.ora文件配置都正常，，tnsping也正常，监听也正常，但是仍然报ORA-12514错误：

MimicPC

一个AI驱动的浏览器运行工具，可以通过浏览器在线安装及运行各种开源的AI应用程序

145

查看详情

SQL> set lin 130 pages 130 

SQL> select dest_id,error from v$archive_dest;

   DEST_ID ERROR

---------- -----------------------------------------------------------------

         1

         2 ORA-12514: TNS:listener does not currently know of service

           requested in connect descriptor

         3

         4

         5

         6

         7

         8

         9

        10

--查看主库监听

SQL> !lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:31:46

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prd)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production

Start Date                08-OCT-2014 10:34:51

Uptime                    0 days 1 hr. 56 min. 54 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prd)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "prd" has 1 instance(s).

  Instance "prd", status READY, has 1 handler(s) for this service...

Service "prdXDB" has 1 instance(s).

  Instance "prd", status READY, has 1 handler(s) for this service...

Service "prd_XPT" has 1 instance(s).

  Instance "prd", status READY, has 1 handler(s) for this service...

The command completed successfully

--tnsnames.ora文件内容

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

PRD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prd)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prd)

    )

  )

STD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prd)

    )

  )

--主库tnsping测试网络服务名是否正常

SQL> !tnsping prd

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:32:35

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prd)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd)))

OK (10 msec)

SQL> !tnsping std

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:32:43

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd)))

OK (20 msec)

--查看备库监听

[oracle@std ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:29:52

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=std)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production

Start Date                08-OCT-2014 09:41:41

Uptime                    0 days 2 hr. 48 min. 11 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=std)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "std" has 1 instance(s).

  Instance "prd", status READY, has 1 handler(s) for this service...

Service "std_XPT" has 1 instance(s).

  Instance "prd", status READY, has 1 handler(s) for this service...

The command completed successfully

--查看备库远程归档路径

SQL> set lin 130 pages 130 

SQL> col error for a20

SQL> select dest_id,error,status from v$archive_dest;

   DEST_ID ERROR                STATUS

---------- -------------------- ---------

         1                      VALID

         2                      VALID

         3                      INACTIVE

         4                      INACTIVE

         5                      INACTIVE

         6                      INACTIVE

         7                      INACTIVE

         8                      INACTIVE

         9                      INACTIVE

        10                      INACTIVE

        11                      VALID

--备库tnsping测试网络服务器名是否正常

SQL> !tnsping prd

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:39:40

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prd)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd)))

OK (20 msec)

SQL> !tnsping std

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:39:44

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd)))

OK (10 msec)