How to check which version of Oracle Audit Vault is installed

In fact this post is about the things of which version of Oracle Audit Vault is installed. If you go through the post http://arjudba.blogspot.com/2010/01/how-to-know-whether-patches-applied-to.html to know which version and patch is installed in your system. The same thing is applicable to check for Oracle Audit Vault.

- Change your directory to cd $ORACLE_HOME/OPatch
- Issue ./opatch lsinventory

[oracle@av-mrp OPatch]$ cd $ORACLE_HOME/OPatch

[oracle@av-mrp OPatch]$ ./opatch lsinventory
Invoking OPatch 10.2.0.4.7

Oracle Interim Patch Installer version 10.2.0.4.7
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/10.2.3/av_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.7
OUI version       : 10.2.0.5.0
OUI location      : /u01/oracle/product/10.2.3/av_1/oui
Log file location : /u01/oracle/product/10.2.3/av_1/cfgtoollogs/opatch/opatch2011-12-13_13-37-41PM.log

Patch history file: /u01/oracle/product/10.2.3/av_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/oracle/product/10.2.3/av_1/cfgtoollogs/opatch/lsinv/lsinventory2011-12-13_13-37-41PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Audit Vault 10g                                               10.2.3.0.0
Oracle Audit Vault 10g Release 2 Patch Set 2                         10.2.3.2.0
There are 2 products installed in this Oracle Home.


Interim patches (6) :

Patch  8648568      : applied on Fri Dec 09 16:04:10 BDT 2011
Unique Patch ID:  11585139
   Created on 24 Jul 2009, 01:44:15 hrs PST8PDT
   Bugs fixed:
     6964283, 6940487, 6805009, 5679561, 4740049, 6996030, 6954407, 6023472
     4085593, 7581770, 6340297, 7685199, 6726958, 8648568

Patch  8567353      : applied on Fri Dec 09 16:03:45 BDT 2011
Unique Patch ID:  11523582
   Created on 9 Jul 2009, 00:14:42 hrs PST8PDT
   Bugs fixed:
     7510032, 6936368, 8567353

Patch  8562769      : applied on Fri Dec 09 16:03:26 BDT 2011
Unique Patch ID:  11382699
   Created on 4 Jun 2009, 01:42:38 hrs PST8PDT
   Bugs fixed:
     4447712, 8562769, 7022700

Patch  7564548      : applied on Fri Dec 09 16:03:09 BDT 2011
   Created on 2 Nov 2009, 16:14:32 hrs PST8PDT
   Bugs fixed:
     7564548

Patch  7524768      : applied on Fri Dec 09 16:02:55 BDT 2011
   Created on 21 Jan 2009, 22:14:01 hrs US/Pacific
   Bugs fixed:
     7524768

Patch  6720712      : applied on Fri Dec 09 16:02:35 BDT 2011
   Created on 9 Jan 2009, 17:22:18 hrs PST8PDT
   Bugs fixed:
     6720712



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

OPatch succeeded.

[oracle@av-mrp OPatch]$ ./opatch lsinventory -details >/tmp/patch.txt
[oracle@av-mrp OPatch]$ less /tmp/patch.txt
Invoking OPatch 10.2.0.4.7

Oracle Interim Patch Installer version 10.2.0.4.7
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/10.2.3/av_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.7
OUI version       : 10.2.0.5.0
OUI location      : /u01/oracle/product/10.2.3/av_1/oui
Log file location : /u01/oracle/product/10.2.3/av_1/cfgtoollogs/opatch/opatch2011-12-13_13-39-55PM.log

Patch history file: /u01/oracle/product/10.2.3/av_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/oracle/product/10.2.3/av_1/cfgtoollogs/opatch/lsinv/lsinventory2011-12-13_13-39-55PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Audit Vault 10g                                               10.2.3.0.0
Oracle Audit Vault 10g Release 2 Patch Set 2                         10.2.3.2.0
There are 2 products installed in this Oracle Home.


Installed Products (196):

Agent Required Support Files                                         10.2.0.3.0
Agent Required Support Files Patch                                   10.2.0.4.0
Assistant Common Files                                               10.2.0.3.0
Assistant Common Files Patch                                         10.2.0.4.0
Bali Share                                                           1.1.18.0.0
Buildtools Common Files                                              10.2.0.3.0
Character Set Migration Utility                                      10.2.0.3.0
Character Set Migration Utility Patch                                10.2.0.4.0
Database Configuration and Upgrade Assistants                        10.2.0.3.0
Database Configuration and Upgrade Assistants Patch                  10.2.0.4.0
Database SQL Scripts                                                 10.2.0.3.0
Database SQL Scripts Patch                                           10.2.0.4.0
Database Workspace Manager                                           10.2.0.3.0
Database Workspace Manager                                           10.2.0.4.0
DBJAVA Required Support Files                                        10.2.0.3.0
DBJAVA Required Support Files Patch                                  10.2.0.4.0
Enterprise Edition Options                                           10.2.0.3.0
Enterprise Manager Agent Core                                        10.2.0.1.0
Enterprise Manager Agent Core Patch                                 10.2.0.4.0a
Enterprise Manager Agent DB                                          10.2.0.1.0
Enterprise Manager Agent DB                                          10.2.0.4.0
Enterprise Manager Baseline                                          10.2.0.1.0
Enterprise Manager Baseline                                          10.2.0.4.0
Enterprise Manager Common Core Patch                                10.2.0.4.0a
Enterprise Manager Common Files                                      10.2.0.1.0
Enterprise Manager Minimal Integration                               10.2.0.3.0
Enterprise Manager plugin Common Files                               10.2.0.1.0
Enterprise Manager plugin Common Files                               10.2.0.3.0
Enterprise Manager plugin Common Files Patch                         10.2.0.4.0
Enterprise Manager Repository Core                                   10.2.0.1.0
Enterprise Manager Repository Core patch                            10.2.0.4.0a
Enterprise Manager Repository DB                                     10.2.0.1.0
Enterprise Manager Repository DB                                     10.2.0.4.0
Generic Connectivity Common Files                                    10.2.0.3.0
Generic Connectivity Common Files Patch                              10.2.0.4.0
HAS Common Files                                                     10.2.0.3.0
HAS Common Files Patch                                               10.2.0.4.0
HAS Files for DB                                                     10.2.0.3.0
HAS Files for DB Patch                                               10.2.0.4.0
Installation Common Files                                            10.2.0.3.0
Installation Common Files Patch                                      10.2.0.4.0
Installer SDK Component                                              10.2.0.5.0
Java Runtime Environment                                             1.4.2.14.0
JDBC Common Files                                                    10.2.0.3.0
JDBC Common Files                                                    10.2.0.4.0
LDAP Required Support Files                                          10.2.0.3.0
LDAP Required Support Files Patch                                    10.2.0.4.0
OLAP SQL Scripts                                                     10.2.0.3.0
OLAP SQL Scripts Patch                                               10.2.0.4.0
Oracle Advanced Security                                             10.2.0.3.0
Oracle Advanced Security Patch                                       10.2.0.4.0
Oracle Application Express                                          3.2.0.00.27
Oracle Audit Vault 10g                                               10.2.3.0.0
Oracle Audit Vault 10g Patch                                         10.2.3.2.0
Oracle Audit Vault 10g Release 2 Patch Set 2                         10.2.3.2.0
Oracle Audit Vault Common Files                                      10.2.3.0.0
Oracle Audit Vault Common Files Patch                                10.2.3.2.0
Oracle Audit Vault One-Off Patch                                     10.2.3.0.0
Oracle Call Interface (OCI)                                          10.2.0.3.0
Oracle Call Interface (OCI) Patch                                    10.2.0.4.0
Oracle Clusterware RDBMS Files                                       10.2.0.3.0
Oracle Clusterware RDBMS Files Patch                                 10.2.0.4.0
Oracle Code Editor                                                   1.2.1.0.0I
Oracle Containers for Java                                           10.2.0.3.0
Oracle Containers for Java                                           10.2.0.4.0
Oracle Core Required Support Files                                   10.2.0.3.0
Oracle Core Required Support Files Patch                             10.2.0.4.0
Oracle Database 10g                                                  10.2.0.3.0
Oracle Database 10G 32 bit                                           10.2.0.1.0
Oracle Database 10g interMedia Files                                 10.2.0.3.0
Oracle Database 10g interMedia Files Patch                           10.2.0.4.0
Oracle Database 10g Patch                                            10.2.0.4.0
Oracle Database User Interface                                       2.2.13.0.0
Oracle Database Utilities                                            10.2.0.3.0
Oracle Database Utilities Patch                                      10.2.0.4.0
Oracle Database Vault J2EE Application                               10.2.0.3.0
Oracle Database Vault J2EE Application Patch                         10.2.0.4.0
Oracle Database Vault Scripts                                        10.2.0.3.0
Oracle Database Vault Scripts Patch                                  10.2.0.4.0
Oracle Display Fonts                                                  9.0.2.0.0
Oracle Enterprise Manager Console DB                                 10.2.0.1.0
Oracle Enterprise Manager Console DB                                 10.2.0.4.0
Oracle Extended Windowing Toolkit                                    3.4.38.0.0
Oracle Globalization Support                                         10.2.0.3.0
Oracle Globalization Support Patch                                   10.2.0.4.0
Oracle Help For Java                                                  4.2.6.1.0
Oracle Help for the  Web                                             1.1.10.0.0
Oracle Ice Browser                                                    5.2.3.6.0
Oracle interMedia                                                    10.2.0.3.0
Oracle interMedia Annotator                                          10.2.0.3.0
Oracle interMedia Client Option                                      10.2.0.3.0
Oracle interMedia Client Option Patch                                10.2.0.4.0
Oracle interMedia Java Advanced Imaging                              10.2.0.3.0
Oracle interMedia Java Advanced Imaging Patch                        10.2.0.4.0
Oracle interMedia Locator                                            10.2.0.3.0
Oracle interMedia Locator Patch                                      10.2.0.4.0
Oracle interMedia Locator RDBMS Files                                10.2.0.3.0
Oracle interMedia Locator RDBMS Files Patch                          10.2.0.4.0
Oracle interMedia Patch                                              10.2.0.4.0
Oracle Internet Directory Client                                     10.2.0.3.0
Oracle Internet Directory Client Patch                               10.2.0.4.0
Oracle Java Client                                                   10.2.0.3.0
Oracle Java Client Patch                                             10.2.0.4.0
Oracle JDBC Thin Driver for JDK 1.2                                  10.2.0.3.0
Oracle JDBC Thin Driver for JDK 1.2 Patch                            10.2.0.4.0
Oracle JDBC Thin Driver for JDK 1.4                                  10.2.0.3.0
Oracle JDBC Thin Driver for JDK 1.4 Patch                            10.2.0.4.0
Oracle JDBC/OCI Instant Client                                       10.2.0.3.0
Oracle JDBC/OCI Instant Client Patch                                 10.2.0.4.0
Oracle JFC Extended Windowing Toolkit                                4.2.33.0.0
Oracle JVM                                                           10.2.0.3.0
Oracle JVM Patch                                                     10.2.0.4.0
Oracle Label Security                                                10.2.0.3.0
Oracle Label Security Patch                                          10.2.0.4.0
Oracle LDAP administration                                           10.2.0.3.0
Oracle LDAP administration patch                                     10.2.0.4.0
Oracle Locale Builder                                                10.2.0.3.0
Oracle Locale Builder                                                10.2.0.4.0
Oracle Message Gateway Common Files                                  10.2.0.3.0
Oracle Message Gateway Common Files Patch                            10.2.0.4.0
Oracle Net                                                           10.2.0.3.0
Oracle Net Listener                                                  10.2.0.3.0
Oracle Net Listener Patch                                            10.2.0.4.0
Oracle Net Patch                                                     10.2.0.4.0
Oracle Net Required Support Files                                    10.2.0.3.0
Oracle Net Required Support Files Patch                              10.2.0.4.0
Oracle Net Services                                                  10.2.0.3.0
Oracle Notification Service                                          10.1.0.3.0
Oracle Notification Service Patch                                    10.2.0.4.0
Oracle ODBC Driver                                                   10.2.0.3.0
Oracle ODBC Driver Patch                                             10.2.0.4.0
Oracle ODBC Driverfor Instant Client                                 10.2.0.3.0
Oracle OLAP                                                          10.2.0.3.0
Oracle OLAP API                                                      10.2.0.3.0
Oracle OLAP API Patch                                                10.2.0.4.0
Oracle OLAP Patch                                                    10.2.0.4.0
Oracle OLAP RDBMS Files                                              10.2.0.3.0
Oracle OLAP RDBMS Files Patch                                        10.2.0.4.0
Oracle One-Off Patch Installer                                       10.2.0.4.2
Oracle Partitioning                                                  10.2.0.3.0
Oracle Partitioning Patch                                            10.2.0.4.0
Oracle Programmer                                                    10.2.0.3.0
Oracle RAC Required Support Files-HAS                                10.2.0.3.0
Oracle RAC Required Support Files-HAS Patch                          10.2.0.4.0
Oracle Real Application Testing                                      10.2.0.4.0
Oracle Recovery Manager                                              10.2.0.3.0
Oracle Recovery Manager Patch                                        10.2.0.4.0
Oracle Required Support Files 32 bit                                 10.2.0.0.0
Oracle Required Support Files 32 bit Patch                           10.2.0.4.0
Oracle Spatial                                                       10.2.0.3.0
Oracle Starter Database                                              10.2.0.3.0
Oracle Starter Database Patch                                        10.2.0.4.0
Oracle Text                                                          10.2.0.3.0
Oracle Text Patch                                                    10.2.0.4.0
Oracle UIX                                                           2.1.22.0.0
Oracle Universal Installer                                           10.2.0.5.0
Oracle Wallet Manager                                                10.2.0.3.0
Oracle Wallet Manager Patch                                          10.2.0.4.0
Oracle XML Development Kit                                           10.2.0.3.0
Oracle XML Development Kit Patch                                     10.2.0.4.0
Parser Generator Required Support Files                              10.2.0.3.0
Perl Interpreter                                                      5.8.3.0.6
PL/SQL                                                               10.2.0.3.0
PL/SQL                                                               10.2.0.4.0
PL/SQL Embedded Gateway                                              10.2.0.3.0
PL/SQL Embedded Gateway Patch                                        10.2.0.4.0
Platform Required Support Files                                      10.2.0.3.0
Platform Required Support Files                                      10.2.0.4.0
Precompiler Common Files                                             10.2.0.3.0
Precompiler Common Files Patch                                       10.2.0.4.0
Precompiler Required Support Files                                   10.2.0.3.0
Precompiler Required Support Files Patch                             10.2.0.4.0
RDBMS Required Support Files                                         10.2.0.3.0
RDBMS Required Support Files for Instant Client                      10.2.0.3.0
RDBMS Required Support Files for Instant Client Patch                10.2.0.4.0
RDBMS Required Support Files Patch                                   10.2.0.4.0
regexp                                                                2.1.9.0.0
Required Support Files                                               10.2.0.3.0
Secure Socket Layer                                                  10.2.0.3.0
Secure Socket Layer Patch                                            10.2.0.4.0
SQL*Plus                                                             10.2.0.3.0
SQL*Plus                                                             10.2.0.4.0
SQL*Plus Required Support Files                                      10.2.0.3.0
SQL*Plus Required Support Files Patch                                10.2.0.4.0
SQLJ Runtime                                                         10.2.0.3.0
SQLJ Runtime Patch                                                   10.2.0.4.0
SSL Required Support Files for InstantClient                         10.2.0.3.0
SSL Required Support Files for InstantClient Patch                   10.2.0.4.0
Sun JDK                                                              1.4.2.0.8a
Sun JDK extensions                                                   10.1.2.0.0
XDK Required Support Files                                           10.2.0.3.0
XDK Required Support Files Patch                                     10.2.0.4.0
XML Parser for Java                                                  10.2.0.3.0
XML Parser for Java Patch                                            10.2.0.4.0
XML Parser for Oracle JVM                                            10.2.0.3.0
XML Parser for Oracle JVM Patch                                      10.2.0.4.0
There are 196 products installed in this Oracle Home.


Interim patches (6) :

Patch  8648568      : applied on Fri Dec 09 16:04:10 BDT 2011
Unique Patch ID:  11585139
   Created on 24 Jul 2009, 01:44:15 hrs PST8PDT
   Bugs fixed:
     6964283, 6940487, 6805009, 5679561, 4740049, 6996030, 6954407, 6023472
     4085593, 7581770, 6340297, 7685199, 6726958, 8648568
   Files Touched:
     /aud.o --> ORACLE_HOME/lib/libserver10.a
     /kza.o --> ORACLE_HOME/lib/libserver10.a
     /kzft.o --> ORACLE_HOME/lib/libserver10.a
     /kzax.o --> ORACLE_HOME/lib/libserver10.a
     /kzam.o --> ORACLE_HOME/lib/libserver10.a
     /szaud.o --> ORACLE_HOME/lib/libserver10.a
     /zlle.o --> ORACLE_HOME/lib/libserver10.a
     /psdsys.o --> ORACLE_HOME/lib/libserver10.a
     /kspare.o --> ORACLE_HOME/lib/libserver10.a
     catlbacs.sql --> ORACLE_HOME/rdbms/admin/catlbacs.sql
     catnools.sql --> ORACLE_HOME/rdbms/admin/catnools.sql
     dbmsamgt.sql --> ORACLE_HOME/rdbms/admin/dbmsamgt.sql
     catamgt.sql --> ORACLE_HOME/rdbms/admin/catamgt.sql
     prvtamgt.plb --> ORACLE_HOME/rdbms/admin/prvtamgt.plb
     ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/ioracle
   Patch Location in Inventory:
     /u01/oracle/product/10.2.3/av_1/inventory/oneoffs/8648568
   Patch Location in Storage area:
     /u01/oracle/product/10.2.3/av_1/.patch_storage/8648568_Jul_24_2009_01_44_15
Patch  8567353      : applied on Fri Dec 09 16:03:45 BDT 2011
Unique Patch ID:  11523582
   Created on 9 Jul 2009, 00:14:42 hrs PST8PDT
   Bugs fixed:
     7510032, 6936368, 8567353
   Files Touched:
     /kdbl.o --> ORACLE_HOME/lib/libserver10.a
     /kdblc.o --> ORACLE_HOME/lib/libserver10.a
     ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/ioracle
   Patch Location in Inventory:
     /u01/oracle/product/10.2.3/av_1/inventory/oneoffs/8567353
   Patch Location in Storage area:
     /u01/oracle/product/10.2.3/av_1/.patch_storage/8567353_Jul_9_2009_00_14_42

Patch  8562769      : applied on Fri Dec 09 16:03:26 BDT 2011
Unique Patch ID:  11382699
   Created on 4 Jun 2009, 01:42:38 hrs PST8PDT
   Bugs fixed:
     4447712, 8562769, 7022700
   Files Touched:
     /kkfd.o --> ORACLE_HOME/lib/libserver10.a
     /qksht.o --> ORACLE_HOME/lib/libserver10.a
     /prssz.o --> ORACLE_HOME/lib/libserver10.a
     ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/ioracle
   Patch Location in Inventory:
     /u01/oracle/product/10.2.3/av_1/inventory/oneoffs/8562769
   Patch Location in Storage area:
     /u01/oracle/product/10.2.3/av_1/.patch_storage/8562769_Jun_4_2009_01_42_38

Patch  7564548      : applied on Fri Dec 09 16:03:09 BDT 2011
   Created on 2 Nov 2009, 16:14:32 hrs PST8PDT
   Bugs fixed:
     7564548
   Files Touched:
     wwv_flow_worksheet.plb --> ORACLE_HOME/apex/core/wwv_flow_worksheet.plb
   Patch Location in Inventory:
     /u01/oracle/product/10.2.3/av_1/inventory/oneoffs/7564548
   Patch Location in Storage area:
     /u01/oracle/product/10.2.3/av_1/.patch_storage/7564548_Nov_2_2009_16_14_32

Patch  7524768      : applied on Fri Dec 09 16:02:55 BDT 2011
   Created on 21 Jan 2009, 22:14:01 hrs US/Pacific
Bugs fixed:
     7524768
   Files Touched:
     /kzvdve.o --> ORACLE_HOME/lib/libserver10.a
     ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/ioracle
   Patch Location in Inventory:
     /u01/oracle/product/10.2.3/av_1/inventory/oneoffs/7524768
   Patch Location in Storage area:
     /u01/oracle/product/10.2.3/av_1/.patch_storage/7524768_Jan_21_2009_22_14_01

Patch  6720712      : applied on Fri Dec 09 16:02:35 BDT 2011
   Created on 9 Jan 2009, 17:22:18 hrs PST8PDT
   Bugs fixed:
     6720712
   Files Touched:
     /kkpo.o --> ORACLE_HOME/lib/libserver10.a
     ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/ioracle
   Patch Location in Inventory:
     /u01/oracle/product/10.2.3/av_1/inventory/oneoffs/6720712
   Patch Location in Storage area:
     /u01/oracle/product/10.2.3/av_1/.patch_storage/6720712_Jan_9_2009_17_22_18



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

OPatch succeeded.

You can also check same command in order to check version and patch list for any Oracle software environment. For example in my RAC database,

[oracle@DC-DB-01 OPatch]$ ./opatch lsinventory
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-12-13_14-14-16PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2011-12-13_14-14-16PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


Rac system comprising of multiple nodes
  Local node = dc-db-01
  Remote node = dc-db-02

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

OPatch succeeded.

You can also issue avctl show_av_status command from audit vault server like below.

[oracle@av-mrp ~]$ avctl show_av_status
TZ set to Asia/DaccaOracle Audit Vault 10g Database Control Release 10.2.3.2.0
Copyright (c) 2006, 2009 Oracle Corporation.  All rights reserved.
http://av-mrp:5700/av
Oracle Audit Vault 10g is running.
------------------------------------

Logs are generated in directory /u01/oracle/product/10.2.3/av_1/av/log

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Problem Description
Before going through this post be sure you have checked http://arjudba.blogspot.com/2008/06/ora-12514-tnslistener-does-not.html and http://arjudba.blogspot.com/2008/06/ora-12154-tnscould-not-resolve-connect.html which also discusses about ORA-12514 error.

In case of 10.2.0.3 database connect to database using easy name service providing instance name fails with ORA-12514.

C:\>sqlplus arju/a@notebook:1523/a/a

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Dec 11 19:50:01 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Cause of the Problem
The way of connection by adding instance name in EZCONNECT is applicable only with 11g net services. So this connection will work with 11g net services and upwords. With 10g for example, 10.2.0.3 and 10.2.0.5 this method of adding instance name with easy naming service will not work.
Solution of the Problem
In case of 10g don't add instance name.

C:\>sqlplus arju/a@notebook:1523/a

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Dec 11 19:49:57 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

In 11g it works fine with adding instance name.

[oracle@DC-DB-01 ~]$ sqlplus test/t@192.168.100.101:1521/bddipdc.world/bddipdc1

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 11 20:14:56 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,                        Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

Note that, also if inside your password you have $ or @ sign then it will not work. $ will raise
invalid syntax like below.

[oracle@DC-DB-01 ~]$ sqlplus system/dmin123$@192.168.100.101:1521/bddipdc.world/bddipdc1

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 11 20:16:07 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


SQL*Plus: Release 11.2.0.1.0 Production

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements.

Usage 1: sqlplus -H | -V

    -H             Displays the SQL*Plus version and the
                   usage help.
    -V             Displays the SQL*Plus version.

Usage 2: sqlplus [ [] [{logon | /nolog}] [] ]

   is: [-C ] [-L] [-M ""] [-R ] [-S]

    -C    Sets the compatibility of affected commands to the
                   version specified by .  The version has
                   the form "x.y[.z]".  For example, -C 10.2.0
    -L             Attempts to log on just once, instead of
                   reprompting on error.
    -M "" Sets automatic HTML markup of output.  The options
                   have the form:
                   HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
                   [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
    -R      Sets restricted mode to disable SQL*Plus commands
                   that interact with the file system.  The level can
                   be 1, 2 or 3.  The most restrictive is -R 3 which
                   disables all user commands interacting with the
                   file system.
    -S             Sets silent mode which suppresses the display of
                   the SQL*Plus banner, prompts, and echoing of
                   commands.

   is: {[/][@] | / }
              [AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value]

    Specifies the database account username, password and connect
    identifier for the database connection.  Without a connect
    identifier, SQL*Plus connects to the default database.

    The AS SYSDBA, AS SYSOPER and AS SYSASM  options are database
    administration privileges.

     can be in the form of Net Service Name
    or Easy Connect.

      @[ | [//]Host[:Port]/]

         is a simple name for a service that resolves
        to a connect descriptor.

        Example: Connect to database using Net Service Name and the
                 database net service name is ORCL.

           sqlplus myusername/mypassword@ORCL

        Host specifies the host name or IP address of the database
        server computer.

        Port specifies the listening port on the database server.

         specifies the service name of the database you
        want to access.

        Example: Connect to database using Easy Connect and the
                 Service name is ORCL.

           sqlplus myusername/mypassword@Host/ORCL

    The /NOLOG option starts SQL*Plus without connecting to a
    database.

    The EDITION specifies the value for Session Edition.


   is: @|[.] [ ...]

    Runs the specified SQL*Plus script from a web server (URL) or the
    local file system (filename.ext) with specified parameters that
    will be assigned to substitution variables in the script.

When SQL*Plus starts, and after CONNECT commands, the site profile
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
(e.g. login.sql in the working directory) are run.  The files may
contain SQL*Plus commands.

Refer to the SQL*Plus User's Guide and Reference for more information.

You have to use escape character like below while connecting.

[oracle@DC-DB-01 ~]$ sqlplus system/Admin123\$@192.168.100.101:1521/bddipdc.world/bddipdc1

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 11 20:16:23 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

@ will raise ORA-12154 like below.

[oracle@DC-DB-01 ~]$ sqlplus system/Admin123@@192.168.100.101:1521/bddipdc.world/bddipdc1

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 11 20:50:53 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Problem Description
We don't want to display password while connect to database and also we want to use easy connection method. We have provided hostname, port number, service name in the connection string correctly but still on 11g it shows ORA-12504 and on 10g it shows ORA-12514.

[oracle@DC-DB-01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-DEC-2011 19:26:24

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                22-NOV-2011 10:38:49
Uptime                    19 days 8 hr. 47 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/DC-DB-01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.103)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "SYS$SYS.KUPC$C_1_20111118185522.BDDIPDC.WORLD" has 1 instance(s).
  Instance "bddipdc1", status READY, has 1 handler(s) for this service...
Service "SYS$SYS.KUPC$S_1_20111118185522.BDDIPDC.WORLD" has 1 instance(s).
  Instance "bddipdc1", status READY, has 1 handler(s) for this service...
Service "bdafisdc.world" has 2 instance(s).
  Instance "bdafisdc1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "bdafisdc1", status READY, has 1 handler(s) for this service...
Service "bdafisdcXDB.world" has 1 instance(s).
  Instance "bdafisdc1", status READY, has 1 handler(s) for this service...
Service "bddipdc.world" has 2 instance(s).
  Instance "bddipdc1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "bddipdc1", status READY, has 1 handler(s) for this service...
Service "bddipdcXDB.world" has 1 instance(s).
  Instance "bddipdc1", status READY, has 1 handler(s) for this service...
The command completed successfully

Here hostname is 192.168.100.101, port 1521 and service name is bddipdc.world.

[oracle@DC-DB-01 ~]$ sqlplus test@192.168.100.101:1521/bddipdc.world

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 11 19:26:44 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

But in case of 10.2.0.3 the error is different and it throws ORA-12514.

C:\>sqlplus arju@notebook:1523/a

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Dec 11 19:21:18 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Cause of the Problem
While using the EZCONNECT the error "ORA-12504" or "ORA-12514" might happen because the password was not provided in the connection string or double quote was not used if you don't provide password.

Solution of the Problem
If you don't want to provide password then you have to use double quote like below.

[oracle@DC-DB-01 ~]$ sqlplus test@\"192.168.100.101:1521/bddipdc.world\"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 11 19:56:37 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

Alternatively, you can provide password while following easy connect method.

[oracle@DC-DB-01 ~]$ sqlplus test/t@192.168.100.101:1521/bddipdc.world

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 11 19:57:19 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options