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

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

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

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

今天做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)