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

 

 


Java runtime 1.8.0.91 | Oracle EBS R12 (12.0.6)

May 2, 2016

Hi guys

You could run into a situation after the latest Java client runtime update, your R12 instance failing to load forms, complaining about the lower version of JRE. This is mainly due to the fact that, if your Windows OS is 64Bit, the latest java runtime update installs both 64Bit & 32Bit and most of the R12 environments run against 32Bit JRE.

Internet explorer (the only one browser that runs the JRE properly for EBS R12) loads the 64Bit java runtime by default from a 64Bit Windows OS & eventually the forms will fail to load.

At present the only possible solution to address this situation is to uninstall the 64bit version of java runtime, using “Programs and features” or “Add remove programs” based on the version of Windows you are using.

Hope this helps few out there :)

regards,

rajesh

 

 


Windows 10 | Hack to dirty desktop

January 17, 2016

 

Hi guys

Starting from Windows 8, Microsoft has clearly switched their target consumers to mobile users and snatched the clean desktop experience from power users.

Windows 10 is basically a huge PHONE OS, however, significant efforts were made by Microsoft to reduce the gap between a mobile OS and pure desktop experience by the addition of a semi effective start button. Yet it satisfy the frustrated me? Not entirely

For example, I have multiple versions of Oracle developer installed in my box, starting from Developer 6i, Developer 10g & Developer 11g in the same box & almost all elements within the developer stacks share the common names like forms developer, reports builder etc. Using the jump list was NEVER my cup of tea as it takes time, efforts to distinguish. Though the new start menu allows one to drag and drop the apps to desktop, send to desktop is a sure missing option which could truly confuse a person who switched from XP/7 recently.

I’m responsible a middle size IT infrastructure and was promoting the usage of classicshell, the free start button/ start menu alternative that brings a kind of Windows XP/7 style start button/menu experience back to both Windows 8.x and Windows 10 OS.

image

You can obtain ClassicShell here

With periodic updates, this freeware remains one of the best solutions for crippled start menu available with Windows 10 OS.

Install it, Open Classic start menu settings, check “Show all settings” & make the few settings as seen with the below images and you are all ready to enjoy a cleaner desktop experience

image

image

You can setup the most comfortable start menu style here

image

Skin allows you to disable the frames around the icons.

image

Once the above you are all good to start experiencing the classic start menu on your Windows 8/10 box

for Windows7bugs

rajesh


Oracle Weblogic | Reports 11g | cgicmd.dat | Key mapping issues

January 16, 2016

 

Hi guys

I’ve been installing and configuring Weblogic 10.3.6 & 12c (recently) and came across a peculiar issue, where the reports server failing to map the keys I’ve specified with cgicmd.dat file.

The best I could recollect from my memories was, I only added the keys to a single instance of cgicmd.dat file, as there are many instances of the same file once after classic domains are created.

Under linux installations you will find the file in 3 different locations once after the domain created and WLS_REPORTS server started.

 

[root@wls Middleware]# find /u01/Oracle/Middleware/ -name cgicmd.dat
/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/servers/WLS_REPORTS/tmp/_WL_user/reports_11.1.2/uxabaw/configuration/cgicmd.dat
/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/config/fmwconfig/servers/WLS_REPORTS/applications/reports_11.1.2/configuration/cgicmd.dat
/u01/Oracle/Middleware/Oracle_FRHome1/reports/conf/cgicmd.dat

[root@wls Middleware]#

image

Under usual scenarios you need to add the keys ONLY to the cgicmd.dat file present in

/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/config/fmwconfig/servers/WLS_REPORTS/applications/reports_11.1.2/configuration/

folder.

With my latest installations of 12c & 11g, I had issue getting the keys mapped properly & after loads and loads of googling I decided to add the same key to all available instances of cgicmd.dat one after another.

So I started with the first instance of cgicmd.dat file, that was found under

/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/servers/WLS_REPORTS/tmp/_WL_user/reports_11.1.2/uxabaw/configuration/cgicmd.dat

which is WLS_REPORTS temporary folder. I added the key, restarted the WLS_REPORTS server & the key was mapped as required!

This could be a nasty bug because I don’t remember adding the keys to multiple files while I tried out forms and reports 11g Release 2 almost 2 years back!

The most interesting part of the entire exercise is, once the key mapping happens successfully, you can remove the keys from cgicmd.dat file that in the temporary location and everything works! Could be the magic of cache Winking smile

So if you come across key mapping issues, you know where to look at for a quick fix.

for Windows7bugs

rajesh


Oracle 12c | Reports | Standalone server

January 14, 2016

 

Hi guys

One of my close associates & a person I respect as a legend, Iudith said the following with one of her recent mails to me:


Regarding all this WebLogic issue … I have a feeling that Oracle “used a cannon for killing a fly” ………
Maybe that complicated setup creates an environment which can support all kinds of other products,I really don’t know because I have mostly just heard about it.

From Oracle’s perspectives, this approach may provide them better scalability, however how far such complexities will appease the developer crowd remains a big concern.

While the modernization of Oracle forms and reports, the maximum sought legacy development means remain a frequently sought and discussed topic, Oracle is definitely NOT doing enough to regain the confidence of mortals who spent decades developing, especially by making each and every newer versions difficult to install and setup.

Less said, let us go back to the topic, how to create and configure a standalone report server instance for 12c environment. This is my 3rd post in a row about installing and configuring 12c products related to Forms and Reports

I can’t exactly remember which document says “In-process reports server has better accountability against a standalone reports instance”. I have referred minimum couple of dozen 12c related documents and tens of blogs to setup stuffs. Without arguing whether in-process server or a standalone instance is better to cater the reports services, let me list the activities one have to perform in order to setup

  1. Create a reports tools instance (Mandatory, if you are planning to use the same box for reports development)
  2. Create a reports server instance (OPTIONAL, only if you want to host a standalone reports instance)

Start WLST(Weblogic Scripting Tool) console, and execute the below commands one after another (Make sure the node manager & the Weblogic admin server services are already started)

 
connect("weblogic","password", "localhost:7001") 
createReportsToolsInstance(instanceName='rptools1', machine='AdminServerMachine') 
createReportsServerInstance(instanceName='repsvr_MYMACHINENAME', machine='AdminServerMachine') 
exit() 

Once successfully completed, restart the WLS_REPORTS application using the weblogic admin console. Once bitten, twice shy…I always restart the weblogic admin server and other servers to make sure nothing is broken in between…(paranoid I must be)

Restarting the WLS_REPORTS server causes the weblogic server to configure the newly created instances of reports tools and reports instance. A successful completion let you

  1. Start using the reports builder
  2. Access the standalone reports server instance

You need minimal configurations for the standalone reports server instance. Just setup the reports (.rdf files) path, disable the security elements and you are online!

Assuming you have already configured the weblogic server components following the previous posts, switch to the reports instance configuration folder

eg: D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\
config\fmwconfig\components\ReportsServerComponent\repsvr_RAJESH-PC

Where repsvr_RAJESH-PC is the name of the reports instance I have created.

image

You need to modify the “rwserver.conf” file, please check the below image for details

image

Save the file and restart the WLS_REPORTS server, so that the last modifications are in place!

Now we have to start the reports instance.

Switch to DOMAIN_HOME\bin folder

eg: D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\bin

and run startComponent.cmd repsvr_RAJESH-PC

Please check the below image for details

image

Once the report server instance starts successfully, you are all set to.

Calling the new instance

http://rajesh-pc:7777/reports/rwservlet?usr&server=repsvr_RAJESH-PC&desformat=PDF&destype=cache&report=LIST_BANKS&paramform=Yes

That’s all folks, as you could see OHS automatically maps the new reports instance and directs the jobs to it.

for Windows7bugs

rajesh