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 oracle\product\10.2.0\db_1\bin
D:\oracle\product\10.2.0\db_1\bin>set ORACLE_SID=KAZEMA
D:\oracle\product\10.2.0\db_1\bin>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 12 11:47:48 2018
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
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:\oracle\product\10.2.0\db_1\bin>set ORACLE_SID=KAZEMA
D:\oracle\product\10.2.0\db_1\bin>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 12 12:59:33 2018
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
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:\oracle\product\10.2.0\db_1\bin>set ORACLE_SID=KAZEMA
D:\oracle\product\10.2.0\db_1\bin>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 12 13:34:56 2018
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-12546: TNS:permission denied
Enter user-name:
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:\oracle\product\10.2.0\db_1\bin>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 12 13:35:21 2018
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
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? :P 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
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 SERVER\Administrator 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.
So
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