Duplicate From Active Database Errors : ORA-17629 and ORA-17627: ORA-12154: Tns:Could Not Resolve

Duplicate From Active Database Errors : ORA-17629 and ORA-17627: ORA-12154: Tns:Could Not Resolve

The duplicate database from active failed with the following errors : 

ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

Cause 1:

For Active Duplication you need to connect to the target and Auxiliary DB using net service name ,even if you are running RMAN at the Auxiliary DB.
The net service name to connect to the Auxiliary DB should also be configured at the target Database.

In the tnsnames.ora on the machine of the TARGET database, there are 2 services created to connect to auxiliary database that have the same "service name" but "different" configuration.

Possible Cause 2:

Mismatch of Parameters DEFAULT_DOMAIN and DB_DOMAIN

db_domain=PROD.world         # init.ora
default_domain=PROD.WORLD    # sqlnet.ora

# DEFAULT_DOMAIN defined on sqlnet.ora is 'case' sensitive and when defined must match
   DB_DOMAIN initialization parameter

Solution:

1. To be sure that the tnsnames.ora file of each machine (machine where is the source database and machine where is the auxiliary database) has the service name to connect to auxiliary database.

2. Verify that there are not duplicate service_name with different configuration in the tnsnames.ora file.

Listener Fails to start TNS-12557,12560,00527 , ORA-12547: TNS:lost contact -- Windows Environment

We have faced very wired issue with Oracle database 11g Listener in Windows environment. For some reason 

it's not starting up and giving  following errors. We have tried all the solutions to resolve this issue, like starting/stopping listener through different Oracle Home, configuring timeout in sqlnet.ora , amending hosts files,creating another listener through netca , even try installing new oracle client with different ORACLE_HOME, but none of this solutions helped. 

But one thing i forget to mentioned here , we have also got another Oracle product on same machine.

WEBLOGIC  10.3.3
FMW 11.1.1.3 

It mean Our Windows Registry have some many key/value for the Oracle product. And we didn't have any clue what was causing problem.

But at last we tried one very simple thing and it works. That I have mentioned in Solution part. 

And guess what , it works !! 

And therefor I guess, one good Oracle DBA hates Windows for their databases.

Errors

TNSListener fails to startup.

Errors include

ORA-12547: TNS:lost contact

TNS-12557: TNS:protocol adapter not loadable

TNS-12560: TNS:protocol adapter error

TNS-527: Protocol Adapter not loadable

Also seen with

Failed to start service, error 997.
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error

Solution

Remove the ORACLE_HOME system environment variable.
From Start button, navigate to Control Panel.  Then select the System icon and click the Advanced tab.
Click the Environment Variables button
Find the ORACLE_HOME in the system environment variables and delete it.
Re-boot the machine.

How To Resolve TNS-12535 or TNS-00505 Operation Timed Out Errors

While examining logs I have found lots of “TNS Operation Timeout Errors” in sqlnet.log file

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 10.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
  Time: 06-MAY-2009 18:54:41
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=*.*.*.*)(PORT=3448))


The same error message was repeating during whole day almost for every application server.

I’ve finally found what was causing the problem. Our database is behind a firewall. Firewall has an “idle session timeout” value. If a connection remains idle for more than the “idle session timeout” value it drops the connections. 

Application developers usually configure their connection pools to remain alive for a long time, which is more than the “idle session timeout” value of the firewall. Not all the connections in the pool are used. Some stays idle. After sometime the firewall drops them and I get those operation timed out problems.

Solution:

Add the following line to the sqlnet.ora file on the server.
SQLNET.EXPIRE_TIME=10


In this configuration database will probe the application servers every 10 minutes to see if they are up. In fact this is a mechanism to determine stale connections by database. Because it sends packets to application servers every ten minutes, the connections are detected as active by firewalls and they are not broken.

Troubleshooting ORA-03135: connection lost contact issue

Environment: Oracle database 10.2.0.3 RAC 64bit, websphere application server on RHEL4.5 use JDBC driver,Juniper firewalls (failover)
Problem: application server got error "java.sql.SQLException: ORA-03135: connection lost contact" and connection timeout from database
Objective: to fix the connection lost issue

Steps:
1. search the error message to find out the root cause in Oracle support and google:

Troubleshooting ORA-3135 Connection Lost Contact [ID 787354.1]
Resolving Problems with Connection Idle Timeout With Firewall [ID 257650.1]
Achive Log shipments failing in environment with Juniper firewall [ID 1075432.1]
Logs are not shipped to the physical standby database [ID 1130523.1]
ORA-03135: connetion lost contact while shipping from Primary server to standby server [ID 739522.1]

According to Oracle doc ID 787354.1, this ORA-3135 might be caused by one of the following:

(1) Transparent Application Failover (TAF)
(2) Idle Connection Timeout
(3) Firewall Manipulating  Sqlnet Data.

Otherwise, it might need further analysis.
Also, according to ORA-3135 connection lost contact tips: http://www.dba-oracle.com/t_ora_03135_connection_lost_contact.htm, it might be due to customized profile.

sqlplus / as sysdba
sql> set line 32000
sql> select profile, resource_name, limit
from dba_profiles where resource_name IN ('IDLE_TIME', 'CONNECT_TIME');

note: some commands:
sql> create profile myprof limit connect_time 30;


From application side, to avoid this idle connection timeout, refer to -   Putting a firewall between your AppServer and DBMS -  http://www.websphere-world.com/modules.php?name=News&file=article&sid=522to

2. check firewall idle connection timeout and ALG settings
Checked Juniper firewall SQL ALG setting and idle connection timeout

# check alg
# check service ssh
# check service SQL*Net V2         

And some articles:
a. What is Idle timeouts when any service set - J-Net Community - http://forums.juniper.net/t5/ScreenOS-Firewalls-NOT-SRX/What-is-Idle-timeouts-when-Any-service-set/td-p/163

b. Viewing list of ALGs and disabling an ALG differs on screenos versions - http://kb.juniper.net/InfoCenter/index?page=content&id=KB13509
c. Issues with SQL database applications when traffic traverses SRX - http://kb.juniper.net/InfoCenter/index?page=content&id=KB21550
d. Increasing the session idle timeout of a particular service - http://kb.juniper.net/InfoCenter/index?page=content&id=KB4652&actp=LIST
e. How do I verify the NCP settings? (for SQL ALG) - http://kb.juniper.net/InfoCenter/index?page=content&id=KB9283
f. [SRX]what conditions are required for SQL ALG? - http://kb.juniper.net/InfoCenter/index?page=content&id=KB22418&cat=JUNOS&actp=LIST
g. Users losing connections when the IVE Active/Passive Cluster Fails over - http://kb.juniper.net/InfoCenter/index?page=content&id=KB8539&cat=SSL_VPN&actp=LIST

3. We have disabled SQL ALG and application server seemed busy at the time of connection lost.
Our monitoring shows Firewall actually failed over to another one at the time of connection lost and switched back, so the application server got "ORA-03135: connection lost contact" error twice and need to restart application.

ORA-12547: TNS:lost contact

Very simple issue but took some amount of time in troubleshooting so thought about posting it here. May be it proves to be useful for someone.

Scenario was: Oracle is installed from “oracle” user and all runs well. There is a new OS user “test1″ that also needs to use sqlplus. So granted the necessary permissions on ORACLE_HOME to test1. Tried to connect sqlplus scott/tiger@DB and yes it works. But while trying sqlplus scott/tiger it throws:

1 2 3 4 5 6 7 8 9 10 11 12

$ sqlplus scott/tiger SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 18 09:32:35 2011 Copyright (c) 1982, 2010, Oracle.  All Rights Reserved. ERROR: ORA-12547: TNS:lost contact Enter user-name: ^C $

Did a lot of troubleshooting including checking tnsnames.ora, sqlnet.ora, listener.ora and so on. Nothing was hitting my mind so finally raised an SR. And it has to do with the permissions of the $ORACLE_HOME/bin/oracle binary. The permissions of oracle executable should be rwsr-s–x or 6751 but they were not. See below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

$ id uid=241(test1) gid=202(users) groups=1(staff),13(dba) $ $ cd $ORACLE_HOME/bin $ ls -ltr oracle -rwxr-xr-x    1 oracle   dba       136803483 Mar 16 20:32 oracle $ $ chmod 6751 oracle $ ls -ltr oracle -rwsr-s--x    1 oracle   dba       136803483 Mar 16 20:32 oracle $ $ sqlplus scott/tiger SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 18 10:23:27 2011 Copyright (c) 1982, 2010, Oracle.  All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "SCOTT" SQL>