Ora 12546 tns permission denied ошибка - Не ошибается лишь тот, кто ничего не делает!

Update: 12th March 2018

I’ve given upon 10.2.0.4 last year, until I revived one of my Windows 10 VMs recently. As I had a legal copy of the OS, I did a OS upgrade to latest build 1709, did all the patching and was eager to do a 10g to 11g R2 (11.2.0.4) upgrade for learning purposes. Luckily I found the Oracle 10g installed in this particular VM and without checking the version of the database, tried to start the instance, facing the dreaded error “ORA-12546”

As I had much more experience with setting up instances gathered during last few months when I was doing hacked migrations & other activities, I decided to give it another try to resolve the “ORA-12546: TNS: permission denied” error, and I must say I succeeded to start the instance.

So here comes the minor tweaks those fixed the TNS errors

(All these exercises were performed on Windows environment, should be same on UNIX environments as well. I am using GUI tools for the fixes)

My issues with the TNS errors were much simpler to address, as I had IP address instead of hostname, which was not being resolved. Check the images below, if you are on Windows try to adjust the values and restart the Windows listener service

If you have multiple Oracle products installed and environment variables are not set for ORACLE_SID, ORACLE_HOME, TNS_ADMIN etc, switch to command prompt & source the ORACLE_SID. Then try to connect to database

[code language=”sql” gutter=”false”]

D:>cd oracleproduct10.2.0db_1bin

D:oracleproduct10.2.0db_1bin>set ORACLE_SID=KAZEMA

D:oracleproduct10.2.0db_1bin>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 12 11:47:48 2018

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

[/code]

Although my case was primarily related to HOSTNAME entry, I rectified more issues after a restart. As usual I started the Windows service for Oracle database, started listener service & as my Windows service for database doesn’t start the instance automatically, switched command prompt and after sourcing ORACLE_SID tried to startup the database, ending up with the same TNS error:

[code language=”sql” gutter=”false”]

D:oracleproduct10.2.0db_1bin>set ORACLE_SID=KAZEMA

D:oracleproduct10.2.0db_1bin>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 12 12:59:33 2018

ERROR:
ORA-12546: TNS:permission denied

[/code]

Painfully I realized the truth that, there are NO straight forward solution to this error for the version in concern which is 10.2.0.4!

As I managed to start the instance successfully prior a restart, I tried to recollect the sequence of starting and stopping the Windows services. So, I got back to services panel and stopped the Oracle service, followed by restating the listener service. After waiting couple of minutes, started the Oracle service once again & tried to connect to the idle instance using sqlplus / as sysdba & this time I was able to connect to the idle instance without facing the TNS error.

I wanted to confirm that restarting the Windows services address this dreaded situation, hence again restarted the Virtual Machine & did the following

Started the listener service & through network resource monitor insure that the port 1521 is listening
Started Windows service for oracle database

Switched to command prompt & tried to connect to the idle instance

[code language=”sql” gutter=”false”]

D:oracleproduct10.2.0db_1bin>set ORACLE_SID=KAZEMA

D:oracleproduct10.2.0db_1bin>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 12 13:34:56 2018

ERROR:
ORA-12546: TNS:permission denied

Enter user-name:
ERROR:
ORA-12546: TNS:permission denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

[/code]

Stopped the Windows Service for Oracle database, waited couple of minutes and again tried to connect to the instance in the same CMD session

[code language=”sql” gutter=”false”]

D:oracleproduct10.2.0db_1bin>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 12 13:35:21 2018

Connected to an idle instance.

SQL>

[/code]

That’s all.

Hope this helps few out there, at the same time throwing a question “Whether it is really worth for a production instance when 10.2.0.5 addresses such complications?”

rajesh

Update: 16th Feb 2017

Sorry folks, I’ve given upon 10.2.0.4 & moved to 10.2.0.5 patch upgrade that resolves the intermittent service startup issues of Oracle on Windows OS (Uncertified OS like Windows 10/2008R2)

I’m sure, if at all needed an upgrade, your DBA will choose a later version of Oracle database, when a client server architecture may not be flexible with Oracle 12c. Please make sure you try the upgrade using a virtual machine, spend some time analyzing logs and if you feel, it is worth taking a risk for the production, do the production patching using 10.2.0.5 patch. You may have to drop and recreate the Enterprise Manager repository (if you are using it at all)

Update: 14th Feb 2017

A right day to present something that’s kinda disappointing? Well, Even though I managed to get stuffs running, after a reboot over night, realized that the “net start Oracle serviceName” in consequent attempts brings back the dreaded “ORA-12546: TNS: permission denied” error message logged in oradim.log

forco

the only one logic I could reach to this intermittent issues with starting service is, read & execute permissions on Oracle executables are not being triggered always. Finish!

My further digging using “Dependency walker” for both Oracle.exe & TNSLSNR.exe has exposed that both the executable were making reference to Microsoft APIs those are depreciated!

Finally: I’m doing a hack install of 10.2.0.3 on Windows 10. Updating the instance using 10.2.0.4 patch & expecting a smooth sail. I’m totally wrong, I can test it, hope it works (not always) & I shouldn’t ever attempt the same in a Production environment.

Positive thing: Once the bin folder has correct permissions & setting up the Oracle services to start automatically, the service starts without any issues and shuts down gracefully when Windows is shut down.

Hi guys

I’m in the midst of compiling a huge post about RMAN backup & restore for NOT DBAs & at the same time trying to fix “ORA-12546: TNS: permission denied” errors I was getting from my 10.2.0.3 upgraded to 10.2.0.4 (32Bit) on a Windows 10 virtual machine using Oracle VirtulBox (My RMAN backup was restored from 10.2.0.3 Production to this upgraded instance)

My scenario:

Windows Service for Oracle Service is set as manual. After a reboot, I try to start the service manually. Sometimes it starts, some other time I have start/stop/start/stop a number of times until the database gets started. I asked google to give me tip & found a post answered by Tom (AskTom) bombarded with questions about “ORA-12546: TNS: permission denied” after machine restarts & there were no real solutions. Few of them even claimed that they managed to start the Oracle service by turning off the firewall(?)

Then I asked google another question “fix ORA-12546: TNS: permission denied” & landed on http://www.dba-oracle.com/t_ora_12546_tns_permission_denied.htm & from that page to http://www.dba-oracle.com/security/removing_permissions.htm

Basically the 2nd page is purely for Linux, and if you are familiar with file permissions, easy to translate for Windows

I browsed the bin folder & realized that, even though the SERVERAdministrator account has full access to the oracle.exe executable, somehow my user “rajesh” who is the default administrator was not inheriting the execute right on the same.

I gave myself full rights on the executable and tried to restart the service & without making any complaints the Oracle database Service started and the database was opened. I was having a look at both alert log and oradim log files to insure that “ORA-12546: TNS: permission denied” not being appearing once again.

Then I gave my user account rajesh full rights on the bin folder (Over reaction) to make sure that I am not going to get hammered with the dreaded, without much explanations available “ORA-12546: TNS: permission denied” error once again.

I think during the upgrade process much of the file permissions were changed as part of the patching process & eventually the permissions were not reset to the actuals. This issue could happen with any Oracle upgrade processes. So if you are that another unlucky one, please give the above hack a try.

regards,

rajesh

Источник

- Forums
- - Advancing Life & Work
  - Alliances
  - Around the Storage Block
  - HPE Ezmeral: Uncut
  - OEM Solutions
  - Servers & Systems: The Right Compute
  - Tech Insights
  - The Cloud Experience Everywhere
  - HPE Blog, Austria, Germany & Switzerland
  - Blog HPE, France
  - HPE Blog, Italy
  - HPE Blog, Japan
  - HPE Blog, Latin America
  - HPE Blog, Poland
  - HPE Blog, Hungary
  - HPE Blog, Turkey
  - HPE Blog, UK, Ireland, Middle East & Africa
- Blogs
- Information
Forums

Blogs
- Advancing Life & Work
- Alliances
- Around the Storage Block
- HPE Ezmeral: Uncut
- OEM Solutions
- Servers & Systems: The Right Compute
- Tech Insights
- The Cloud Experience Everywhere
- HPE Blog, Austria, Germany & Switzerland
- Blog HPE, France
- HPE Blog, Italy
- HPE Blog, Japan
- HPE Blog, Latin America
- HPE Blog, UK, Ireland, Middle East & Africa
- HPE Blog, Poland
- HPE Blog, Hungary
- HPE Blog, Turkey
Information
English

10 REPLIES 10

Previous
- 1
- 2
Next

Источник

From the documentation, ORA_12546 is:

ORA-12546: TNS:permission denied
Cause: User has insufficient privileges to perform the requested operation.
Action: Acquire necessary privileges and try again.

Which isn’t entirely helpful, but various forum and blog posts (way too many to link to, Googling for the error shows a lot of similar advice) mention permissions on a particular part of the installation, $ORACLE_HOME/bin/oracle, which is a crucial and central part of most of the services.

Normally the permissions on that file would be -rws-r-s--x, with the file owned by oracle:dba, and this error can occur when the word-writable flag — the final x in that pattern — is not set. Anyone in the dba group will still be able to execute it, but those outside will not.

Your listener seems to be fine as you can connect remotely, by specifying @dbname in the connect string. The listener runs as oracle (usually, could be grid with HA, RAC or ASM) so it is in the dba group and can happily hand-off connections to an instance of the oracle executable.

When you connect without going via the listener, you have to be able to execute that file yourself. It appears that root cannot execute it (or possibly some other file, but this is usually the culprit, apparently), which implies the world-writable bit is indeed not set.

As far as I can see you have three options:

set the world-writable bit, with chmod o+x $ORACLE_HOME/bin/oracle; but that opens up the permissions for everyone, and presumably they’ve been restricted for a reason;
add root to the dba group, via usermod or in the /etc/group; which potentially weakens security as well;
use SQL*Net even when you don’t specify @dbname in the connect string, by adding export TWO_TASK=dbname to the root environment.

You said you don’t have this problem on another server, and that the file permissions are the same; in which case root might be in the dba group on that box. But I think the third option seems the simplest and safest. There is a fourth option I suppose, to install a separate instant client, but you’d have to set TWO_TASK anyway and go over SQL*Net, and you’ve already ruled that out.

I won’t dwell on whether it’s a good idea to run sqlplus (or indeed the application that needs it) as root, but will just mention that you’d could potentially have a script or function called sqlplus that switches to a less privileged account via su to run the real executable, and that might be transparent to the application. Unless you switch to the oracle account though, which is also not a good idea, you’d have the same permission issue and options.

Источник

TNS-12546: TNS:permission denied

I got TNS-12546 when I tried to start the listener as oracle.

oracle@solaris11vm:~$ lsnrctl start


LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 31-JAN-2018 21:01:57



  


                       
  




Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/solaris11vm/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12546: TNS:permission denied
 TNS-12560: TNS:protocol adapter error
  TNS-00516: Permission denied
   Solaris Error: 13: Permission denied
Listener failed to start. See the error message(s) above...

This is because there were some files locked by root, oracle can do nothing about it.

root@solaris11vm:~# cd /var/tmp root@solaris11vm:/var/tmp# ls -al total 11 drwxrwxrwt 3 root sys 3 Jan 31 21:01 . drwxr-xr-x 37 root sys 44 Jan 22 22:46 .. drwxrwxrwt 2 root root 6 Jan 31 21:01 .oracle

So I removed all the files in this directory .oracle by root.

root@solaris11vm:/var/tmp# rm -rf .oracle/

Try it again by user oracle. Will we see TNS-12546 this time?

oracle@solaris11vm:~$ lsnrctl start


LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 01-FEB-2018 00:08:33
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/solaris11vm/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=solaris11vm)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
Start Date                01-FEB-2018 00:08:33
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/solaris11vm/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

It’s working well. No TNS-12546.

Источник

I recently faced this
error message while trying to connect with the database (running on my local
Windows host) using a TNS service. Although connecting without TNS service was
working fine, however, connection was failing with ORA-12546 when I tried to
connect using TNS service. Following is what I faced.

SQL> host tnsping salman12

TNS Ping Utility for 64-bit Windows: Version
12.1.0.2.0 — Production on 23-OCT-2019 13:48:57

Used parameter files:

d:oracleproduct12.1.0dbhome_1networkadminsqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST
= (ADDRESS = (PROTOCOL = TCP)(HOST = SALMQURE-SG)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = salman12)))

OK (140 msec)

ERROR:

ORA-12546: TNS:permission denied

There could be several different reasons of this
error as I found after searching on google. I am sharing one of the causes of
this error. For my case, I was actually connected to a VPN using Cisco
AnyConnect and this was causing the whole issue. After I disconnected from the
VPN, I was able to connect with the database using TNS service. For your case,
an antivirus, a firewall or permissions on oracle software files may also cause
this error message.

TNS-12546: TNS:permission denied

Popular Posts — All Times