Windows | ORA-12560: TNS:protocol adapter error

February 25, 2017

Hi guys

Not many DBAs prefer Windows for their Oracle databases. Linux is most preferred by most of them & most of the DBAs I know setup the bash profile under Oracle user to setup the environment during each logon to the server.

Our legacy business application database runs on Windows 2003 & trust me, we never had a single database crash (Other than the physical hardware failure that forced us to recover the database once). Depending upon how huge the database and application, the choices for hosting the Oracle database differ from one business to other.

We decided to upgrade our Oracle 10g 10.1.x.x 32Bit database to 11g R2 & as usual I have replicated the environment using my home semi-server class desktop, before the Production environment at work.

Installed 10g 32Bit, created the database using dump export file (The total size of the database is less than 7GB, hence I avoided the hectic RMAN backup and restore part)

  1. Configured RMAN against the new database & made full backup for archive logs and database.
  2. Installed 11g 11.2.0.4 64Bit database (Software Only installation)
  3. Created a new Windows Service using oradim
  4. Restored the database from RMAN backups & upgraded the database to 11g

So far so good. I had to restart the computer & after rechecking the database was up and running, tried to access the instance using sqlplus & was presented with

ORA-12560: TNS:protocol adapter error

REG_SID_MISSING

I setup ORACLE_SID=SID at the CMD window & sqlplus was happy after that.

Usually, Windows doesn’t need environment variables set exclusively for the database as Windows registry takes care of it. This is very efficient when the box has only one database running. If you have more than one database or multiple Oracle homes, the scenario changes.

In addition to, Oracle always looks for the executable based on the PATH information it reads. For example my box has 10g,11g,12c database software installed without any databases created during the installation time.

Let us consider the scenario like I didn’t re-order the PATH entries after the latest installation of 12c & try to open SQL or RMAN. The call will find the executable from 12c path entry BIN as default, and a beginner could have enough confusions due to it.

In my case, I needed my 10g instance first, hence I moved the 10g folder as the 1st entry for Oracle products, and once I finished with 10g moved 11g home folder to the 1st position.

SID_Missing

Anyway, after confirming the path settings, my immediate attention was towards registry, as Oracle services completely depend upon the registry values for each service registered.

To my utter surprise, found the 11g Service entry didn’t have ORACLE_SID string created during the instance creation using ORADIM.exe

REG_SID_MISSING

Oracle 11g 11.2.0.4 has a huge bug list and interim patches those should be applied before moving to Production instance. I really don’t know whether the missing ORACLE_SID string entry was due to one of such bugs.

So I stopped the Oracle service, added ORACLE_SID string entry with the value for my database

REG_SID_ADD

Restarted the service & sqlplus connected to the instance happily without setting up the environment variable like set ORACLE_SID=SIDNAME

REG_SID_ADDED

While the easiest solution is to setup both ORACLE_HOME, ORACLE_SID when someone wants to use the sqlplus or RMAN exclusively as a part of the database access, the above method is a definite way to deal with “ORA-12560: TNS:protocol adapter error”

regards,

rajesh


ORA-12546: TNS: permission denied

February 13, 2017

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

1

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!

2

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.

1

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.

2

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


Oracle Database (10g) & Developer 6i settings for Arabic/Urdu support

November 6, 2016

Hi guys

I think it was 2001-2002 our company decided to migrate to Developer 6i based build from Developer 2000 (4.5). We had loads of fun setting up the Arabic part for the forms runtime. I remember setting up the NLS_LANG string as ‘ARMSAWIN1256’ instead of ‘AR8MSWIN1256’ and wasting hours trying to figure out what went wrong.

If not often, I receive requests once in a while, asking about setting up the Arabic (Right to left languages) environment for client server architecture. Today I am “Showing” you a how to, rather than typing the entire stuff here :)

This Video demonstrates the following few

  1. Creating a database using AR8MSWIN1256 character set
  2. Setting up the languages and Unicode parameters to show correct languages
  3. Setting up client OS (Windows Xp, later OS)
  4. Hope the attempt helps few out there :)

 

regards,

rajesh

 

 


Windows 10 | Cumulative Update KB3194496

October 10, 2016

 

Few weeks back I have decided to stop receiving the insider builds for Windows 10 as I hardly found anything interesting that needs such huge volumes of downloads and my precious time! (A bit prudish here). Well it was all easy…not entirely. Even after leaving the Insider program for this particular PC, I kept watching the Windows Update program trying to download the subsequent builds and miserably failing to install them. Fortunately for me, this PC is mostly used as a media center, hooked to my TV.

To stop receiving the newer builds, I unlinked my Live account finally and deleted the C:\Windows\SoftwareDistribution folder after disabling Windows Update for a short period.

image

Then I realized that I had this issue with a cumulative update KB3194496! Each time I tried update, the lengthy process will fail with “we couldn’t complete the updates, undoing changes.”

After few days search, I came across this link through “Bing”, the greatest search engine EVAR invented by a man!

Though there were many other POSTs explaining some different types of fixes, I opted to go with the solution provided by Microsoft, running a small .msi file downloaded from this link

I had a reason for choosing this fix, as I “was” an Insider while the patch was downloaded and failed the first time. Whatever, after running the .msi and answer with “A” to the prompt in the command line, I downloaded and installed the update without any issues. You may try the same, if you are/were an Insider!

 

regards,

rajesh


Windows 10 | Control your Windows Updates

October 10, 2016

 

We have relatively small network & upgraded all possible candidates to Windows 10 professional edition during the free upgrade period, to realize that our 15MBPS (Okay stop mocking us) fiber optic connection was choked & almost no browsing was possible as almost all the machines started downloading the updates. Our WSUS server cannot handle the Windows 10 unless upgraded and we were frantically searching for a method to stop the client machines directly downloading the updates & came across many beautiful blog posts those systematically explained how we could use group policies to control how the clients download updates.

One of the best posts we adopted for our network is available here

We tried the group policy workaround with few of the laptops in the IT and we were able to stop those automatic download of end number of updates those Microsoft pushed everyday to address the numerous issues they were not even able to fix with the anniversary update!

As a user with administrator privileges, start the local group policy editor.

 

image

Navigation –> Computer Configuration->Administrative Templates->Windows Components->Windows Update

image

image

image

image

Find “Configure Automatic Updates” and open it.

image

Here you can choose, suitable for your situation. For me, notifications are fine & then I decide when I should download the updates.

image

Close the Group policy update MSC (Microsoft System Console) and you are all set. From next reboot, you will be given notifications about the downloads, rather than automatically start downloading and clogging your internet connection.

(If you are always connected to WIFI, you can use the “Metered” connection trick to avoid downloading updates automatically. As this trick is not applicable for Ethernet connections, I am not getting into detailed how to this time)

Important Note: You may forget to download and apply the much needed updates, so make sure you will never stop looking at the notification panel for update related.

Regards,

rajesh


Windows 10 | Anniversary update Version 1607

September 5, 2016

Hi guys

There were loads of hype about the first major update Microsoft released for their Windows 10 operating system. While desktops are yet to receive the update through Windows update, many laptops and notebooks were first in the line to receive the update by early August 2016.

As usual, this huge update brings many new features to the relatively new OS that is still evolving. Personally I feel it is a mobile OS, sized for desktops and laptops as Microsoft cannot ignore the corporate/development segments.

One of the major leaps are with the Ubuntu based Linux subsystem, that works perfectly fine & until date, the max I have used it for was to access our Linux servers through SSH. Yes, it works :)

v

So what exactly the objective behind this post? If you have already noticed, I have started this blog to list the bugs of Windows 7 and to post possible solutions to many of the issues Windows 7 survived for years without any proper fixes…then I realized there were better sites dedicated for the OS and slowly shifted my attention towards Oracle based developments and technologies. Once again, as the OS is fresh and buggy, I have decided to choose few topics and to start posting my experiences.

Version 1607, Build 14393.82 AKA anniversary update did break most of the other installed Microsoft, Including Office Suite 2013. The maximum affected suite components were Microsoft Outlook and Excel & as the updates were only available for laptops, we were able to overcome the issues by patching the systems with additional patches provided by Microsoft windows update channel.

While data/internet charges are getting cheaper by date, many may not be bothered by the volume of data used for downloading these additional patches, which are still being pushed to those machines which were updated to 1607, countries like India where majority cannot afford huge downloads will find the update landing them in hot soup, as without the patches to iron out the bugs with 1607, the total OS will be shaky and buggy, making it frustratingly annoying to live with.

Does it mean you shouldn’t update? definitely no. You must update when Microsoft eventually pushes it to for your desktop machines and once after the updates are in place, you have a better “Defender” that started appearing at the right notification area with a shield symbol and does a better job (?)

Hope the above helps few out there to plan the 1607 update properly and to be prepared for huge data consumptions!

 

regards,

rajesh

 

 


Windows 10 | Cannot login because the device is offline?

June 17, 2016

 

Seriously?

Hi guys

Many of you were ditched into “free Windows 10” upgrade from your Windows 7 & 8 and I am sure few out there chose to link their existing local accounts with a Microsoft account (live, outlook and err hotmail accounts)

I have four computers (including my work laptop) running Windows 10 and the 10 years old HP micro tower PC that works as a home theater is my guinea pig for all Windows insider tests.

Windows insider program made it sure that the insider logs on to the computer using a Microsoft account, most probably for the headcount? Anyway they do let the user hook up a live account when the insider program is turned on inside Windows update advanced area. Yet demand the user to logon to the computer using Microsoft account must be for usage statistics.

Anyway, “trusting” Microsoft’s long “proven” privacy protection shit, I agreed to link my local account with my windows insider id and upgraded the box to latest build (Anniversary build? with linux sub-system)

Came the fun, I was told my device was offline so the password cannot be checked and the network connection status icon over the logon screen showed that the connectivity were broken

After few minutes of struggle I remembered that I had my daughter’s account added as family member and quickly managed to logon to the box and the network connection fixed itself (Should be a bug?)

I tried to switch the user account to my local account that is linked to a microsoft account, and I was able to.

So what was going wrong?

A family account (Microsoft account) was able to logon to the box without a live internet connection

A local account linked to Microsoft account was blocked because there were no internet accesses

I am sure I was damn lucky to have another account available with the box to log and to let the connection repair itself & I am yet to come across a valid explanation towards the annoying issues like my experience above.

However, I figured out a way to address such situations by unhooking the local account from Microsoft account, yet to continue receiving the insider builds.

I added my Windows insider Microsoft account as “Other Users” and changed the account type as “Administrator”. (You need to verify this account on the box by validating the account at later stages) Not only this has addressed the issues with device being offline and being locked out, I realized I can add accounts under the family and use the family safety finally against the child accounts!

image

Actually logging on to a computer using ONLY a Microsoft account is a very bad idea incase if you are using your primary account. Anyone (family) who knows the PIN or password that you set for the Microsoft access will able to access your mails and other sensitive information without additional efforts as everything is linked and many places they won’t have to logon to explicitly to access the information.

Hope you will not be locked out of your box, after reading this post :)

regards,

rajesh