Question Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error in Technology Q&A

BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Wed, 13 Jun 2012 14:41:08 GMT

Good afternoon,

Since yesterday we are facing a problem with al DB13 jobs in our system.

We did some investigations and found out that we have a problem connection by SQLPLUS to oracle.

E:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 13 10:28:19 2012

ERROR:

ORA-12560: TNS:protocol adapter error

Enter user-name: ^C

E:\>

Brtools test show instance

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

Additional BRSPACE options for showing database information

1 - Create log file (log) .......... [no]

2 - Confirmation mode (confirm) .... [no]

3 # Extended output (output) ....... [no]

4 - Scrolling line count (scroll) .. [20]

5 - Message language (language) .... [E]

6 - BRSPACE command line (command) . [-p initGEP.sap -c -s 20 -l E -f dbshow -c dbstate]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

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

BR0662I Enter your choice:

BR0280I BRTOOLS time stamp: 2012-06-13 10.30.02

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0291I BRSPACE will be started with options '-p initGEP.sap -c -s 20 -l E -f dbshow -c dbstate'

BR0280I BRTOOLS time stamp: 2012-06-13 10.30.02

BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

BR0280I BRTOOLS time stamp: 2012-06-13 10.30.04

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

###############################################################################

BR1001I BRSPACE 7.20 (22)

BR1002I Start of BRSPACE processing: seitkfps.dbw 2012-06-13 10.30.04

BR0280I BRSPACE time stamp: 2012-06-13 10.30.34

BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement:

'CONNECT / IN SYSDBA MODE'

ORA-12560: TNS:protocol adapter error

BR0310E Connect to database instance GEP failed

BR0700E Fatal errors occurred - terminating processing...

BR1008I End of BRSPACE processing: seitkfps.dbw 2012-06-13 10.30.34

BR0280I BRSPACE time stamp: 2012-06-13 10.30.34

BR1007I BRSPACE terminated with errors

###############################################################################

BR0292I Execution of BRSPACE finished with return code 3

BR0280I BRTOOLS time stamp: 2012-06-13 10.30.34

BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action

BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

When we start SQLPLUS like below

sqlplus /@SID as sysdba

it connects normal.

We have checked TNS_ADMIN, ORACLE_HOME, ORACLE_SID, Oracle registry settings, DBS_ORA_SCHEMA, DBS_ORA_TNSNAME, DBMS_TYPE, TNSPING was OK, R3TRANS is OK.

If someone could help us it would be very nice

PS we can not boot this system very easily

Kind regards,

Leo

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Wed, 13 Jun 2012 14:46:48 GMT

Hi Harmsen,

Execute the command, below and paste the output, here;

lsnrctl status

Best regards,

Orkun Gedik

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Wed, 13 Jun 2012 15:00:18 GMT

Orkun Gedik

here the result

E:\oracle\GEP\11202\BIN>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 13-JUN-2012 16:55:14

Connecting to (ADDRESS=(COMMUNITY=SAP.WORLD)(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1527))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
64-bit Windows Error: 61: Unknown error

E:\oracle\GEP\11202\BIN>

Be aware that our system is a windows 2003 R2 cluster so HA.

The listener is running under controel of the Failsafe manager

leo

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Wed, 13 Jun 2012 15:07:27 GMT

Can you confirm that the listener service is up at the database host on failsafe or failover cluster manager?

Additionally, check the listener.log in order to identify error details

Best regards,

Orkun Gedik

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Wed, 13 Jun 2012 15:47:05 GMT

Orkun Gedik

There is no indication in the log file to be found that makes anny sense. So no errors found.

The listerner is up and running under controle of the failsafe manager.

(coworker with leo)

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Wed, 13 Jun 2012 15:58:33 GMT

Question one: Do you execute the brtools on the database host?

Question two: Did you check the configuration SAPXPG_DBDEST_<HOSTNAME> under TCP/IP connections over SM59? Target host must be refer to the virtual database hostname

Best regards,

Orkun Gedik

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Thu, 14 Jun 2012 07:18:04 GMT

Dear Orkun Gedik

One:

Yes we execute BRTOOLS on the database host

Two:

There are 3 SAPXPG_DBDEST_<HOSTNAME> in our sm59 configuration. The arn't changed since the installation in 2008.

Threre's a entry towards the DB cluster host, containing a gateway host and service port

Two entry's pointing towards the individual cluster hosts. But these entry's do not contain a gateway and service port.

But these entry's havened changed since the beginning

At this point backups are not running so we also posted a call on oss but you are faster.

henk/leo

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Thu, 14 Jun 2012 08:31:49 GMT

Because of the the system is clustered, I suggest you check the destination is using on the system by DB13 -> Goto -> Backend configuration

First, test whether it is running. Then, fill the gateway and service port with the database cluster hostname and port

Best regards,

Orkun Gedik

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Thu, 14 Jun 2012 09:13:29 GMT

we don't think it's SAP related. As the command "sqlplus / as sysdba" reports a similar error.

in the logging of SAP Backup it states that "CONNECT /" isn't possible. Also in the logfile of the backup the creation of the pfile to spfile message is now missing.

to perform the sqlplus / as sysdba the oracle_sid is present in the environment variable's and in the registry.

As brbackup uses the same connect method to the database as sqplus / as sysdba, we think it's Oradcle related

We also checked the sqlnet,tnsnames,listener files but nothing changed nor altered.

could it be related to the OPS$ users???

henk/leo

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Thu, 14 Jun 2012 11:33:41 GMT

Try to TNSPING the db host and update. If possible stop the listener and restart (no high risk in this).

Regards,

Nick Loy

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Thu, 14 Jun 2012 12:10:38 GMT

TNSPING <SID> works perfectly,

Restarting the listener has to wait till later. If something goes wrong than there'll be a big discussion.

However when we set the environ variable local=<SID>, wierd it may be but brtools works.

I put it in the system environment of the database server, now wait for the brbackup to finish and do some further testing.

Somehome I keep thinking that TNS does not report the local SID.

henk/leo

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Wed, 20 Jun 2012 10:25:09 GMT

We have consulted a oracle specialist also and he advised the reboot of windows because somethhing in windows is wrong and that is the cause of our problem.

Info:

When you connect as sqlplus / as sysdba you will use the windows port

When you connect as sqlplus /@<SID> as sysdba you will use the listener port and settings

So in our case the listener settings and port are okay because the last option is workin.

Leo

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Wed, 20 Jun 2012 11:59:59 GMT

>> When you connect as sqlplus / as sysdba you will use the windows port

What is windows port? Did you restarted whole system from the scratch?

>> could it be related to the OPS$ users???

No, I don't think so.

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Wed, 20 Jun 2012 13:35:03 GMT

Hello Orkun,

We haven 't restarted the system at this moment we have to wait until we have a support window this weekend. The intention is to switch the clusternode and reboot the system in controlled "low user" period.

Maybe the word port is not right in the answer before. It is mentioned as that the first option is using windows and the second option is going by the listener.

Leo

Re: BR0301E SQL error -12560 at location BrDbaConnect-2, SQL statement: ORA12560 TNS:protocol adapter error

Former Member — Wed, 20 Jun 2012 13:52:50 GMT

Hi Leo,

>>We haven 't restarted the system at this moment we have to wait until we have a support window this weekend. The intention is to switch the clusternode and reboot the system in controlled "low user" period.

Before go in to the detailed investigation, I strongly recommend to restart whole system, if it is possible. By doing so, we will eliminate unnecessary configurations.

>> Maybe the word port is not right in the answer before. It is mentioned as that the first option is using windows and the second option is going by the listener.

Both connections are established by using listener.

By th way, is the problem solved? Because you signed the correct answer.

Best regards,

Orkun Gedik