OraFAQ Forum: Networking and Gateways

Home » RDBMS Server » Networking and Gateways » ORA-12154

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12154 [message #508403]

Fri, 20 May 2011 17:25

aminelhamzaoui
Messages: 17
Registered: May 2011
Location: maroc

Junior Member

Report message to a moderator

Re: DATA GUARD [message #508406 is a reply to message #508403]

Fri, 20 May 2011 17:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Since Networking is involved, the basic assumption is that two distinct systems are involved.

For System1 post hostname, IP#, Operating System name & version & Oracle s/w version to 4 decimal places.
For System2 post hostname, IP#, Operating System name & version & Oracle s/w version to 4 decimal places.
Clarify which hostname is associated with DataBase (DB) Server system & which hostname with SQL Client.

From DB Server post content of listener.ora & sqlnet.ora (if either exist)
From DB Server post results from following commands
lsnrtcl status
lsnrctl service
sqlplus scott/tiger

From SQL Client post content of tnsnames.ora & sqlnet.ora (if either exist)
From SQL Client post results from following commands
tnsping <DB_alias>
ping <DBhostname>
sqlplus scott/tiger@<DB_alias>

ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.

Report message to a moderator

ORA-12154 [message #508451 is a reply to message #508403]

Sat, 21 May 2011 07:22

aminelhamzaoui
Messages: 17
Registered: May 2011
Location: maroc

Junior Member

hi;
i'm a trainee for my PFE and i should to implement a physical standby database, i need your help
i chosed this tuto "www.oracle-class.com/?p=276" but in step 10 when i do SELECT * FROM v$archive_dest where dest_id=2 ; i have i message error
ORA-12154: TNS: could not resolve the connect identifier specified

can any one help me !!

primary db in :
-- system Fedora 10
-- i use oracle 10g
-- hostname aminos
-- ip eth1 10.0.0.174
-- oracle_home /u01/app/oracle/product/10.2.0/db_1
-- oracle_SID OSS
-- oracle base /u01/app/oracle

standby db in :
-- system Fedora 10
-- i use oracle 10g
-- hostname amin
-- ip eth1 10.0.0.173
-- oracle_home /u01/app/oracle/product/10.2.0/db_1
-- oracle_SID ORCL
-- oracle base /u01/app/oracle

//------test tnsping from primary to standby------//
[root@aminos oracle]#tnsping 10.0.0.173
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 21-MAY-2011 00:48:56

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

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=10.0.0.173))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.173)(PORT = 1521)))
OK (140 msec)

//------test tnsping from standby to primary------//
[root@amin oracle]#tnsping 10.0.0.174
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 21-MAY-2011 00:49:56

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

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=10.0.0.174))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.174)(PORT = 1521)))
OK (140 msec)

//-------And in the primary database when o do--------//

SQL> alter system switch logfile;

System altered.
SQL> SELECT error FROM v$archive_dest where dest_id=2 ;

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

//-------- start the standby database---------------//
SQL>startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1218556 bytes
Variable Size 71305220 bytes
Database Buffers 134217728 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-16004: backup database requires recovery
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:'/u01/app/oracle/oradata/oss/system01.dbf'

SQL>

i already seach a lot but i don't have solution.

regards

Report message to a moderator

Re: ORA-12154 [message #508454 is a reply to message #508451]

Sat, 21 May 2011 08:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

A number of things are wrong on both systems

>ORA-11254: TNS:could not resolve the connect identifier specified
Obviously COPY & PASTE are broken for you.

From PRIMARY post content of following files
pfile (if pfile does not exist make a new one from spfile)
tnsnames.ora
sqlnet.ora

STANDBY needs to be rebuilt from fresh & complete backup of PRIMARY

Report message to a moderator

Re: ORA-12154 [message #508455 is a reply to message #508454]

Sat, 21 May 2011 08:51

aminelhamzaoui
Messages: 17
Registered: May 2011
Location: maroc

Junior Member

//------------ the initOSS.ora in the primary database ------//

control_files=('/u01/app/oracle/oradata/oss/control1.ctl',
'/u01/app/oracle/oradata/oss/control2.ctl',
'/u01/app/oracle/oradata/oss/control3.ctl')
db_name=OSS
db_unique_name=OSS
log_archive_config='DG_CONFIG=(OSS,ORCL)'
service_names=OSS
db_block_size=8192
pga_aggregate_target=25M
sga_target=200M
processes=200
open_cursors=1000
undo_management=auto
undo_tablespace=UNDOTBS1
compatible=10.2.0
log_archive_start=TRUE
log_archive_dest_1='LOCATION=/u01/app/oracle/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=OSS'
log_archive_dest_2='SERVICE=ORCL lgwr async
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORCL'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format='arch_%t_%s_%r.arc'
log_archive_max_processes=2
log_archive_min_succeed_dest=1
remote_login_passwordfile='exclusive'
open_cursors=300
sessions=205
nls_language=AMERICAN
nls_territory=FRANCE
fal_server=ORCL
fal_client=OSS
db_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/OSS'
log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/OSS'
user_dump_dest='/u01/app/oracle/udump'
utl_file_dir='/home/oracle/my_logminer'
standby_file_management=auto
//----------------------------------------------------------//
//--------------tnsnames.ora in the primary database----------//
ORCL =
( DESCRIPTION =
(ADDRESS_LIST =
( ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.173)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = OSS)
)
)
OSS =
( DESCRIPTION =
(ADDRESS_LIST =
( ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.174)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = OSS)
)
)
//------------ sqlnet.ora in the primary database--------------//

all the lines are in comment, so i have nothing !!

regards

Report message to a moderator

Re: ORA-12154 [message #508456 is a reply to message #508455]

Sat, 21 May 2011 08:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

from standby post results from following OS commands

lsnrctl status
lsnrctl service

Report message to a moderator

Re: ORA-12154 [message #508458 is a reply to message #508456]

Sat, 21 May 2011 09:08

aminelhamzaoui
Messages: 17
Registered: May 2011
Location: maroc

Junior Member

//------------------------lsnrctl status--------------------//

[root@amin oracle]# lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 21-MAY-2011 10:59:32

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 21-MAY-2011 08:46:42
Uptime 0 days 2 hr. 12 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amin)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCL_XPT" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "OSS" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully
//------------------------------------------------------------//
//------------------------lsnrctl service--------------------//

[root@amin oracle]# lsnrctl service

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 21-MAY-2011 11:00:45

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ORCL_XPT" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "OSS" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

Report message to a moderator

Re: ORA-12154 [message #508465 is a reply to message #508458]

Sat, 21 May 2011 11:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SNAFU!
tnsnames.ora on PRIMARY for ORCL alias the SID=OSS
however no such instance exists on STANDBY
but I would have expected a different error.

Report message to a moderator

Re: ORA-12154 [message #508476 is a reply to message #508465]

Sat, 21 May 2011 18:42

aminelhamzaoui
Messages: 17
Registered: May 2011
Location: maroc

Junior Member

hi BlackSwan
i have an instance in standby (SID ORCL) ,it's a backup of primary database by cold backup.
do you have any idea about ??

thank's a lot for your help

regard

Report message to a moderator

Previous Topic:	Database Link not working between Oralce 9i and Oracle 11g
Next Topic:	Connecting Oracle to SQL Server via database link

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 11 15:28:48 CST 2024