配置静态监听解决ORA

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

 

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)

 

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/52d46fa5a7a36b2bc261616293b96f5e.html