ORA-12514 TNS: listener could not resolve SERVICE_NAME given in connect descriptor

This can occur when you directly connecting to database or when you try to access remote database using dblink.

When you encounter this error first thing we should collect the following information from init.ora file.

1.DB_NAME

2.DB_DOMAIN

3.SERVICE_NAME

assume that your DB_NAME value is “smplorcl” ,DB_DOMAIN is “world” and SERVICE_NAME value is “smplorcl.world”

above values also can be obtained using show parameter command from SQLPLUS

Ex: sql> show parameter DB_NAME

Next you need to find out the current services registered with listener.

Lsnrctl services

LSNRCTL> services listener

1. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1545))

2. Services Summary...

3 .Service "smplorcl" has 1 instance(s).

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

5. Handler(s):

6. "DEDICATED" established:0 refused:0 state:ready

7. LOCAL SERVER

8. Service "smplorcl.world" has 1 instance(s).

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

10. Handler(s):

11."DEDICATED" established:0 refused:0 state:ready

12.LOCAL SERVER

Note : I have given lines numbers for each lane in listener services output.

Now compare your service name value you have obtained from init.ora is matching with lane number 8 value . If not

you need to make changes to init.ora or tnsnames.ora (if your init.ora has right service_name then you change your tnsnames.ora SERVICE_NAME parameter value or Vice versa)

this error also can occur when yo accessing remote databases using dblibk. To verify tnsname alias first test the tnsname alias by using it to connect in SQLPLUS directly . If you still get this error , that means you have issues with your tnsnames, first correct tnsnames issue.

If your SQLPLUS connection is successful then you need to verify db_link connection parameter values

query dba_db_links database table.

Select db_link,host from dba_db_links;  from the above output verify your host value is identical to your remote 

TNSNAMES ALIAS parameter values, verify the domain reference also , this should include “world” part in 

the domain name

ex: smplorcl.world.   Also try restarting listener and give some time for listener to register service names .

Hope my notes help you resolving ORA-12514 error.

Reference document URL. 

http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/config_concepts.htm

Undo Management

My Understanding on undo Tablespace and Rollback segments.

Undo table space concept released with Oracle 9 Version , prior to 9 DBA used to manage undo management through rollback segments. It was a cumbersome process for DBAs to manage rollback segments especially sizing rollback segments.

From Oracle 9i onwards Oracle came up with a good solution called undo tablespace where DBA does not require to worry  Undo management.

But Oracle still have the rollback segment in 9i as well as in 10g and 11g  for backward compatibility. In 10g and 11g still oracle use rollback segment concept but this exclusively used by Oracle at the time of database creation. In 10g or 111g rollback segments are created and managed by oracle software and they are stored in SYSTEM tablespace, this is because at the time of database creation they may not be a undo tablespace.

Init.ORA Undo parameters .

The following undo parameter controls undo management .

UNDO_MANAGEMENT :  Possible values are 'AUTO' or 'MANUAL' , you basically speicy which undo management mode oracle database should use . If you specify AUTO database will be starting in automatic undo management mode.

UNDO_TABLESPACE  : You specify the tablespace name as a parameter value if your  undo management mode is automatic. It is not a mandatory parameter though, if you forget to specify undo tablespace name , database still start with out any startup errors but you still see errors/Warnings in the Alert log. If instance starts without undo tablespace user transaction will be using rollback segment created in system to manage transaction undo data. Oracle strongly recommends not use Rollback segments created in System tablespace. Undo tableapce parameter can be modified using Alter system table space command.

UNDO_RETENTION : Undo retention specified in seconds , Number of seonds to keep expired transaction data( undo data). default value is 900 seconds, this can be changed using alter system command.

Will write more undo in the next posting.

Init.ora Data Dictionary Views

he following Data Dictionary Views provides complete list of init.ora file parameters currently set /used in the databases. for all RAC environments query gv$parameter, gv$parameter2, gv$spparameter other environments query  v$parameter ,v$parameter2, v$spparameter

 

List of currently supported parameters can be vies using the following query

 

SELECT name, value
FROM gv$parameter
ORDER BY 1;

 

I strongly recommend to use GV$ views to view all data dictionary data in NON-RAC Environment also, because when you start working on RAC environment you do not have to learn or query a different views.

 

List of all parameters those you can modify using alter system command

 

SELECT name, value
FROM gv$parameter
WHERE isdefault = 'FALSE'
ORDER BY 1;

 

List of obsolete parameters in any environment can be found using

 

SELECT *  FROM gv$obsolete_parameter  ORDER BY 1;

 

You can also use v$spparameter data dictionary view for complete list of parameters set by SPFILE and PFILE

Know your INIT.ora

The "init.ora" file is a parameter file that is used to startup the database. based on the database setup we can modify these parameters. The "init.ora" file is a template file is supplied with oracle software.

This file can be found in

the "ORACLE_HOME/dbs" directory on unix platforms and on Windows this can be found in ORACLE_HOME/database.

 

Filename would be initORACLE_SID.ora file ( here SID means System Identification)

 

How do i find out if my database using IFILE or SPFILE

 

connect as  sqlplus "as sysdba"

at the SQL prompt

SQL> show parameter ifile;

 

SQL> show parameter spfile

 

 

 

Sample Init.ora File

#####################################################################
# Common parms
#####################################################################
ifile = ?/dbs/orabase-dg.ora
#####################################################################
# Structural Parms
#####################################################################
db_domain = psoug
db_block_size = 8192
db_writer_processes = 8
#####################################################################
# Identification & Control Files
#####################################################################
db_name = orabase
control_files = (/app/oracle/product/orabase/control.ctl
/app/oracle/product/databases/orabase/control.ctl)
#####################################################################
# Version Specific
#####################################################################
compatible = 10.2.0
#####################################################################
# Platform Specific
#####################################################################
disk_asynch_io = false
filesystemio_options = directio
use_indirect_data_buffers = true
#####################################################################
# Security, Audit and Resource Limit
#####################################################################
audit_trail    = DB
resource_limit = true
#####################################################################
# NLS Settings
#####################################################################
nls_date_format = DD-MON-YYYY
#####################################################################
# Archive & Redo Logs
#####################################################################
#log_archive_dest = /app/oracle/product/flash_recovery_area/arch
log_buffer                = 4194304 
log_checkpoint_interval   = 1050624
log_archive_max_processes = 4 
#_log_simultaneous_copies = 48
archive_lag_target        = 1800
#####################################################################
# Dump & Output Directories
#####################################################################
audit_file_dest      = /app/oracle/product/admin/orabase/adump
background_dump_dest = /app/oracle/product/admin/orabase/bdump
core_dump_dest       = /app/oracle/product/admin/orabase/cdump
user_dump_dest       = /app/oracle/product/admin/orabase/udump
utl_file_dir         = /app/oracle/product/admin/orabase/output
#####################################################################
# DB & Instance Limits
#####################################################################
db_files     = 512
sessions     = 4000
processes    = 1500 
transactions = 200
#####################################################################
# Process & Session Specific
#####################################################################
open_cursors            = 4000
open_links              = 10
session_cached_cursors  = 40
session_max_open_files  = 30
sort_area_retained_size = 1048576
sort_area_size          = 4194304
#####################################################################
# Buffer Pool
#####################################################################
db_block_buffers       = 300000
db_block_checksum      = true
db_block_checking      = true_db_block_lru_latches  = 2048
_db_block_hash_latches = 65536

#buffer_pool_keep    = (buffers:120000, lru_latches:150)
#buffer_pool_recycle = (buffers:55296, lru_latches:48)
#####################################################################
# Shared Pool & Other "Pools"
# Sort, Hash Joins, Bitmap Indexes
#####################################################################
java_pool_size = 0
large_pool_size = 500M
pga_aggregate_target = 1024M
shared_pool_size = 750M
shared_pool_reserved_size = 96M
streams_pool_size = 0

_shared_pool_reserved_min_alloc = 4000
#####################################################################
# UNDO
#####################################################################
# use automatic undo
undo_management = 'auto'
# which tablespace
undo_tablespace = 'undo_t1'
# keep 8 hours (8*3600)
undo_retention  = 28000
_undo_autotune  = false
#####################################################################
# Parallelism
#####################################################################
parallel_max_servers            = 32
parallel_min_servers            = 0
parallel_threads_per_cpu        = 8
parallel_execution_message_size = 65535
recovery_parallelism            = 16

#parallel_automatic_tuning      = true (deprecated in 10g)
#####################################################################
# Shared Server
#####################################################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orabaseXDB)"
#max_dispatchers    = 40
#max_shared_servers = 150
#mts_service        = orabase
#shared_servers     = 8
#####################################################################
# Job Processing
#####################################################################
job_queue_processes = 8
aq_tm_processes = 1
#####################################################################
# Miscellaneous
#####################################################################
background_core_dump          = partial
db_file_multiblock_read_count = 16
fast_start_parallel_rollback  = FALSE
optimizer_index_caching       = 80
optimizer_index_cost_adj      = 10
recyclebin                    = off

_disable_selftune_checkpointing = true
#####################################################################
# Undocumented Parameters & Temporary Fixes
#####################################################################
_b_tree_bitmap_plans            = false
_shared_pool_reserved_min_alloc = 4000
_small_table_threshold          = 2560
_optim_peek_user_binds          = false
#####################################################################
# Events
#####################################################################
#event = "600 trace name library_cache level 10"
# tracing PMON actions
#event = "10500 trace name context forever"
#event = "10196 trace name context forever"
#event = "10246 trace name context forever"
##event = "4031 trace name errorstack level 10"
#event = "10511 trace name context forever, level 2"
#event = "32333 trace name context forever, level 8"

 

 

Opatch Howto

How to specify different inventory location?

OPatch expects the oraInst.loc file in the standard location namely  /var/opt/oracle/oraInst.loc  however there are some systems where this file is located in a different location other than default location

In order to specify a different inventory location  when you apply a patch to oracle database you can use  inventory API flag invPtrLoc (note the case)

opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc .

 

Errors related Ora inventory locations are

“OPatch would error out saying "Unable to access inventory"  OR "Couldn't find required file liboraInstaller”

 

What is the Default Storage location of OPATCH

Opatch is usually located in the $ORACLE_HOME/OPatch directory.

 

 Opatch logs are placed as follows:

For opatch version 1.0.0.0.xx, logs are written into the patch storage area under$ORACLE_HOME/.patch_storage/ and the log in this case starts with

"_Apply_.log"

For the newer opatch versions for products installed with OUI release 10.2 and after, logs are written into the cfgtoollogs/opatch area under $ORACLE_HOME/ and the log in this case starts with

"opatch.log"

Unable to lock Central Inventory Error

When YOU encounter OPATCH error "Unable to lock Central Inventory. 

Opatch usually throw below error

"

Oracle Home : /u01/app/oraprd/product/10.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.3.2
OUI version : 10.2.0.3.0
OUI location : /u01/app/oraprd/product/10.2.0/db_1/oui
Log file location : /u01/app/oraprd/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2007-09-22_09-50-02AM.log

Unable to lock Central Inventory. OPatch will attempt to re-lock.
Do you want to proceed? [y|n]
n
User Responded with: N
Unable to lock Central Inventory. Stop trying per user-request?
OPatchSession cannot load inventory for the given Oracle Home /u01/app/oraprd/product/10.2.0/db_1. Possible causes are:
No read or write permission to ORACLE_HOME/.patch_storage
Central Inventory is locked by another OUI instance
No read permission to Central Inventory
The lock file exists in ORACLE_HOME/.patch_storage
The Oracle Home does not exist in Central Inventory

So how do i fix this ?

You can do the follwing to fix this error.

Go to $ORACLE_HOME/.patch_stage directory see if you can directory name called 'lock' if yes remove that direcoty and re-run opatch apply command , else go to oracle central inventory directory (ex:/u01/app/oraInventory)  and see if you can find out lock directory , if yes remove this and re-rin opatch apply command.

Else

export OPATCH_DEBUG=TRUE
and then run opatch apply.

this will give you the exact phase where your patch is failing

ORA-02024: database link not found

If you are trying to drop a database link after changing the global_name of the database , you usually end up with ora-02024: database link not found

 

But when you query user_db_links  view you found DB link exists .

 

 

Possible root cause would be

 

Initially when we create a database without domain in the global name, a null value will be used from domain as opposed to .world in Oracle releases 9i and before.

Later on when the global_name is altered to contain the domain part that is “world”,   this domain remains even when the global_name is altered back a name without domain name.

 

 

In order to drop the desired database link 

 

I would recommend to try this solution in Development DB then in Test  

And with a cold backup in production. 

 

 

1)Conn sys as sysdba

 

 

2) find out current global_name value using below sql

 

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------
THESIMPLEORACLE.WORLD

 

Find out the value  of  GLOBAL_NAME FROM props$ TABLE

 

Select name, value$ from  props$ where name = 'GLOBAL_DB_NAME';

 

NAME

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

VALUE$

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

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

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

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

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

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

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

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

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

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

GLOBAL_DB_NAME

THESIMPLEORACLE.WORLD

 

 

Step 3) Update global name value to desired name with our domain name

 

Syntax :

update props$ set value$ = '' where name ='GLOBAL_DB_NAME';

 

 

Example :

 

update props$ set value$='THESIMPLEORACLE’  where name like 'GLOBAL_DB_NAME';

 

SQL>commit;

 

 

3)Then connect as the schema user that owns the DBLINK and try to drop it.

4) Once the database link is dropped, the global_name can be changed back to the
desired name containing domain part using the alter database rename global_name statement

Checking Operating System Version Must be B.11.23 Actual B.11.31

The installer attempts to make sure that you are using a certified combination of product and OS.I have encourted this error while installing Oracle 10g on HP-itanium Server.

If you want to override this, try using:

./runInstaller -ignoreSysPrereqs

ORA-04043: object XDB_DATASTORE_PROC does not ...

When you upgrade(manual/DBUA) the DB to 10.2.0.2 or 10.2.0.3  you might encounter

 

ERROR at line 1:
ORA-04043: object XDB_DATASTORE_PROC does not exist

 

In order to fix this, it is necessary to install Oracle Text and run dbmsxdbt.sql 

Size of a Table

To find out a size of a table in Megabytes use below SQL

select segment_name,(bytes)/1024/1024 MB
  2    from user_segments where
  3    segment_type = 'TABLE'
  4*   and SEGMENT_NAME='TABLE_NAME'

V$DataFile

 V$DATAFILE , V_$DATAFILE , GV$DATAFILE

V_$Datafile  The actual underlaying Dynamic View .V$datafile is a public synonym name of V$_Datafile.

You can use V$datafile to view information about data file related to NON-RAC Environment Use GV$Datafile to view information about datafile related to RAC Environment ,

the difference between V$ and GV$ view is INST_ID column which stores Instance ID information in RAC Environment.

V$DATAFILE reads the data file information from control file, some important information you can obtain from V$DATAFILE are  Data file status( OFFLINE, ONLINE, SYSTEM, RECOVE ), SCN at last checkpoint , Size when created (in bytes)  , weather this data file is enabled for DML operations or Not (see  ENABLED column status)

Weirdness of v$datafile on a standby db.

V$DATAFILE and Standby Database.

On a standby database "STATUS" column wouldn't get updated in some scenarios. All data files created after the standby control file creation timestamp would get "RECOVER" status as they get pushed to the standby database. Solution to this problem is to query V$datafile_header . Whenever we query v$datafile_header, Oracle retrieve the all data file header blocks, and it gives us the right "STATUS" of data files.

Related Tables or Dynamic views

file$

ts$

x$ktfbhc

dba_data_files

dba_temp_files

gv$dbfile

dba_free_space

v$datafile_header

To know the view definition V$DATAFILE you can simple query

OPatch failed with error code 104 when using OPATCH

Possible reasons for this error could be either a  missing oraInst.loc file or permission issues with oraInst.loc file, oraInst.loc located in in /var/opt/oracle  directory on unix platform. Ensure Oracle user have  read/write priviligies on orainst.loc file as well as on the actual path of orainventory location.

 if you could not fix the issue and if you know the inventory location you may want try following solution

"You would hardcode the orainventory location in Opatch apply command "

ex :opatch apply -invPtrLoc /full/path/oraInst.loc

 Here  invPtrLoc is a Opatch Switch .

Opatch Howto

How to specify different inventory location?

OPatch expects the oraInst.loc file in the standard location namely  /var/opt/oracle/oraInst.loc   however there are some systems where this file is located in a different location other than default location

In order to specify a different inventory location  when you apply a patch to oracle database you can use  inventory API flag invPtrLoc (note the case)

opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc .

 

Errors related Ora inventory locations are

“OPatch would error out saying “Unable to access inventory“  OR “Couldn’t find required file liboraInstaller”

 

What is the Default Storage location of OPATCH

Opatch is usually located in the $ORACLE_HOME/OPatch directory.

 

Where are the Opatch logs located and how to check if a patch has been applied successfully ?

 Opatch logs are placed as follows:

For opatch version 1.0.0.0.xx, logs are written into the patch storage area under $ORACLE_HOME/.patch_storage/ and the log in this case starts with

“_Apply_.log”

For the newer opatch versions for products installed with OUI release 10.2 and after, logs are written into the cfgtoollogs/opatch area under $ORACLE_HOME/ and the log in this case starts with

“opatch.log”