Windows Subsystem for Linux | WSL | Ubuntu 18.04 with Xfce & Xrdp

Hello guys

As many of us know, starting from Windows 10 1803, Microsoft has made Windows subsystem for Linux running in the background even after the console windows are closed.

Definitely this is a gain for those developers who want to have a Linux environment available always, regardless whether the console windows open or not. My requirements towards the same is pretty limited, as I have live Linux systems for almost all my experiments. However, was never less motivated to explore the WSL environments.

Last time I posted a thread about installing and configuring Ubuntu 18.04 WSL and setting up apache, MySQL.

This time we will see how you could enhance your Ubuntu 18.04 WSL with a proper GUI (Desktop environment). I am sure, majority of the Windows users will appreciate a GUI to interact with Linux against the Linux geeks who prefer command line interactions. In addition, few editors like geany or gedit ONLY could be used in a GUI environment.

We will be using Xfce (light weight desktop manager) & XRDP to achieve the GUI requirements on Ubuntu 18.04 WSL

Fire up your Ubuntu 18.04, and update the instance to the latest

sudo apt update && sudo apt upgrade

This could take a while to complete depending upon your internet connection speed

Once the update & upgrade finished, we will install both Xfce & Xrdp in a single session

sudo apt install xfce4 xrdp

The installation should be pretty smooth and within few minutes everything should complete. Not a single attempt from my end failed to install both Xfce or Xrdp

Once the installation is over, we MUST make sure that the XRDP doesn’t use port 3389 which is used by Microsoft RDP (in case if your Windows 10 is already configured for RDP). Hence we will edit the XRDP configuration file with a different port, in our case 3390

sudo vi /etc/xrdp/xrdp.ini

Change the port from 3389 to 3390 & save the file.

Now we will try start the XRDP service

rajesh@rt04:~$ sudo service xrdp start
* Starting Remote Desktop Protocol server [20190509-12:13:49] [DEBUG] Testing if xrdp can listen on 0.0.0.0 port 3390.
[20190509-12:13:49] [DEBUG] Closed socket 6 (AF_INET6 :: port 3390)
[ OK ]
rajesh@rt04:~$

That’s it

Open Remote Desktop Connection from your Windows Machine & log on!

Supply your WSL username and password. You can use ONE of the existing WSL usernames, in case if your WSL have more than one user.

One of the main issues I found using Xfce4 was few goodies like print screen should be installed by the user prior screenshot capturing could be made (I took screenshots from the remote session & copied them to Windows for this post). This will include adding new keyboard shortcut keys.

We’ll quickly see how to resolve the above said

First install missing “xfce4-screenshooter”

sudo apt install xfce4-screenshooter -y

Once the package installation finished, we will define a new keyboard shortcut that binds “Print Screen” physical button on the keyboard to Xfce4 screenshot shooter package.

Launch Applications -> Settings -> Keyboard

and using the “Add” button, Create a new shortcut. Xfce4 supports different switches to be used with screenshooter package. I’m using the “-f”, for full screen captures.

Be careful when you define the keyboard shortcuts. There is a space between the command and switches, for example

xfce4-screenshooter -f

There is a space between xfce4-screenshooter & the switch “-f”. Once after entering the command and pressing OK will ask you to tap the physical keyboard button that you want to bind with the command. Tap “PrtScr” or other button of your choice & that’s all. Your screen capture should work now.

So why one would even need a GUI over WSL. Well, my answer is short. I always dealt with systems those came with GUI. Many business applications could only be installed over a GUI equipped system. Plus, I am not a geek & after hundreds of attempts I cannot deal with a console based editor like VIM. I feel comfortable with geany/gedit than feeling geeky at mind.

Cheers guys, now your WSL have a GUI. Start think about what YOU can’t do with WSL!

Happy developing!

regards,

rajesh

Oracle Reports Developer 10g (10.1.2.3)| Layout designer hangs

Hello guys

Update: 25th April 2019

It looks like, setting up the compatibility for Windows 7 solves the Reports Builder shaking, freezing behavior to a greater extend. Going on with the “Fix” as NOTHING else could be done with a product that is NOT anymore supported by Oracle.

====

I “trust” Microsoft and I do update my Work/Home/VMs regularly. Ahum! 99.9% of the times I never had to go back and fix anything, that .1% times, things went awfully bad.

I cannot confirm which update, certainly one update within last 2 months have made Oracle Reports Developer 10g/6i loads messier than earlier. Now both version of reports developer will NOT load the layout designer properly.

The interesting factor about the layout designer is:

It hangs more often when you have multiple reports opened from a single instance of the reports developer & you are switching between the layouts.

For example, if you opened Report 1’s layout & closed it, then opening the Report 2’s layout arrests the designer & all the toolbar icons and rest go astray (as you could see with above image) which will force you to end task the developer using Task Manager/or other known measures.

It is irky, shaky, yet you can do your work with multiple sessions of reports developer opened with only one report loaded. There are no guarantees that this IS the ultimate solution. I often have to terminate the report builder and restart it to do my development(s) :(

I’ve more news for you. I did a reinstall routine of Patch 3 for 10g developer & remembering Windows 7 was the last OS certified with the developer suite, changed the compatibility for the Report Developer and set it as Windows 7. I am not sure whether it acts as a mediator, my report builder is much stable as I am jolting these few lines. Keeping fingers crossed.

regards,

Linux Subsystem for Windows | WordPress

Hi guys

Almost a year back I posted about Linux Subsystem for Windows & interesting that I am revisiting the subject in the same period once again. Within an year time loads have changed with the Linux subsystem. Starting from Windows 10 version 1803, once started the Linux Subsystem is available in the background until a reboot! Sounds great?

This time we will see such an enhancement could be utilized on Windows for some extended “developments”, and I will walk you through setting up “WordPress” using WSL. Please note, I am opting “WordPress” as target ONLY for few reasons like:

  • “WordPress” is developed using PHP
  • “WordPress” mostly uses MySQL for database
  • “WordPress” is mostly hosted on LINUX environments

Windows SubSystem for Linux Installation

You must enable “Windows SubSystem for Linux ” through “Turn Windows Features on or off”, after which you will have to reboot your Windows box

Open “Windows Store” and search for Linux, that should bring up many results as seen with the image below

We’ll be using Ubuntu 18.04 LTS this time as I had pretty difficult issues with Pengwin & SUSE. If you are familiar with Linux, Ubuntu is a debian based Linux distribution & widely used for desktop systems. For our limited tests Ubuntu is a right choice.

Total download of Ubuntu 18.04 is around 270MBs & should be installed without giving any troubles.

Once “Ubuntu App” is installed you will be notified and you can immediately “launch” the app.

Ubuntu takes few minutes to finalize the installation & will prompt you to enter a username & password. Make sure you remember the username and password, forgetting may force you to reinstall Ubuntu.

Immediately after setting up a Linux username and password, we will make sure that the Subsystem is upgraded with all new packages.

(While apt & apt-get do almost the same thing, why “apt” is preferred? please read about it here)

Issue the command

[code language=”bash” gutter=”false”]
$sudo apt upade
[/code]

Once the update process finishes with the list of packages those could be upgrade, go ahead with upgrading the instance

[code language=”bash” gutter=”false”]
$sudo apt upgrade
[/code]

Depending upon the speed of your internet connection this could take a while.


Installation of PHP, Apache, MySQL & phpMyadmin on WSL

Install PHP

[code language=”bash” gutter=”false”]
$sudo apt install php libapache2-mod-php
[/code]

It shouldn’t take more than couple of minutes before PHP is installed.

Install Apache2 (Web Server)

Issue the following command at prompt($)

[code language=”bash” gutter=”false”]
$sudo apt install apache2 or sudo apt-get install apache2
[/code]

Apache installation should not take more than few minutes & we will see how to configure it bit later.

MySQL installation

I faced multiple issues installing MySQL on my Ubuntu based WSL. Few were merely because I was ignoring few pre-requisites like checking for already open ports on my Windows & other were due to lack of adequate amount of experiences with installing software on Linux

After a couple of failed attempts, I landed upon this page, which neatly explains how to use MySQL PPA in the place of Ubuntu’s default repositories for MySQL

I will make some fresh screenshots, that is for recent versions of MySQL installation using the MySQL PPA, because there are few more MySQL versions are supported currently using the same PPA

Configure MySQL PPA

Issue the following commands:

[code language=”bash” gutter=”false”]
wget http://repo.mysql.com/mysql-apt-config_0.8.10-1_all.deb
[/code]

followed by

[code language=”bash” gutter=”false”]
$sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb
[/code]

This will provide you a graphical interface to select correct MySQL version for your requirements

Now, you must run update command once again

[code language=”bash” gutter=”false”]
$sudo apt update
[/code]

Now we have a conflict to resolve. As on date, the MySQL 5.7 repository signature is invalid, for the same reason the repository will be disabled. (I don’t know what kind of issues this could produce, so not taking any risks). Next step we will get a valid signature for the disabled repository and re-enable it

Issue the command (read more here https://tecadmin.net/expired-key-expkeysig-with-apt/)

[code language=”bash” gutter=”false”]
$sudo apt-key adv –keyserver keys.gnupg.net –recv-keys 8C718D3B5072E1F5
[/code]

Make sure the key that you want to resign is fed in correct. You MUST insure that the command completes successfully.

As we have enabled the repository by getting the new key, we can proceed to next step.

We will force the udpate process once again

[code language=”bash” gutter=”false”]
$sudo apt update
[/code]

You should get a notice that 1 package could be upgraded. So we will go ahead with upgrade.

[code language=”bash” gutter=”false”]
$sudo apt upgrade
[/code]

This will auto start the configuration window, all you need is to select ok and proceed

Make sure there are no new packages after the setup by issuing

[code language=”bash” gutter=”false”]
$sudo apt update
[/code]

Now we can start installing the MySQL 5.7 Server.
Please note, if you already have an instance of MySQL running on your Windows, the port 3306 will be in use (unless custom ports are used) & you WILL never able to complete the MySQL installation on WSL. Many undocumented errors will stop the MySQL installation, apparently without giving you the least ideas of what went wrong. So, make sure that the MySQL server is stopped and the port is not listening to port 3306. I don’t know another way to address this situation.

Issue the command

[code language=”bash” gutter=”false”]
$sudo apt install mysql-server
[/code]

You will be prompted for MySQL root account. I suggest using the same password across this particular setup so that you will not mess them up later stages.

Confirm the MySQL root password

That completes the MySQL 5.7 Server installation.

Now, let us try to start the MySQL 5.7 Server service.

MySQL 5.7 service is started now. So we can proceed to installing phpmyadmin, MySQL manipulation software.

Install phpmyadmin

Issue the following command

[code language=”bash” gutter=”false”]
$sudo apt-get install phpmyadmin php-mbstring php-gettext
[/code]

As we are using Apache2 as web server, make the selection by tapping the spacebar and Ok

You will be asked whether the database for phpmyadmin should be configured, You should select Yes, unless you want to do it later.

You will be asked to provide the a password now, that is for the phpmyadmin, use the same MySQL root password here (you can use a new password, just to avoid confusion, I use the same password everywhere)

You have to confirm the password.

Now you will be asked to enter the MySQL administrator(root) password, supply it

Confirm & wait for the phpmyadkin to complete the installation

Installation will notify you about Apache not being running, you can ignore the notice.

Now we will start the Apache Server. Couple of things you should take care with Apache & I will try to list few of them below:

As the WSL runs as another software on Windows, it is bind to experience conflicts. For example, if you already have IIS server listening to port 80, without changing the port information for Apache, will not able to start the server until the ports information is corrected within the Apache configuration file.

If you already have MS SQL server installed with reporting, the reporting engine listens to port 80, so even if you would stop or disable the IIS/WWP services, there is a possibility that port conflicts will arise. This will also prompt you to alter the default port mentioned in the Apache configuration file

So let us see how we are going to address such conflicts:

Edit the Apache ports configuration file

[code language=”bash” gutter=”false”]
$sudo vi /etc/apache2
[/code]

I opted for port 8088 (You can choose a port that is NOT used by Windows. Issue netstat -aon to get a list of ports open/listening on Windows from CMD

100% sure that you are going get presented with an annoying notice “
Protocol not available: AH00076: Failed to enable APR_TCP_DEFER_ACCEPT” which is easily addressable.

We will edit the apache configuration file to address this issue

issue the command

[code language=”bash” gutter=”false”]
$sudo vi /etc/apache2/apache2.conf
[/code]

& add “AcceptFilter http none” by the end of the file.

Save the configuration file & restart the Apache service.

As we have completed all the installations and managed to start the services successfully after fixing the configuration files, it is time to reboot.

WSL unfortunately doesn’t let you use “init 0”, or “init 6” to shutdown or restart the instance, instead you must use the Windows Services MMC for the same.

This will close all open Ubuntu “app” windows. You can restart the Ubuntu from the app list.

Once Ubuntu restarted, let us start the services

[code language=”bash” gutter=”false”]
$sudo service apache2 start
$sudo service mysql start
[/code]

Once the services are started, we will try phpmyadmin. As I mentioned already, I have set the port 8088 for Apache, hence I can access the phpmyadmin from the link http://localhost:8088/phpmyadmin

You can safely ignore the warnings, as the current version of phpmyadmin we installed from Ubuntu repos have certain conflicts with PHP 7.2

Install “WordPress”

“WordPress” is one of the leading CMS (Content Management Systems) with unlimited possibilities. Recently we switched our decade long joomla based corporate website to “WordPress” & after few days of interactions with the technology, I feel I would never look anywhere else.

Get “WordPress”

Issue the command

[code language=”bash” gutter=”false”]
$wget https://wordpress.org/latest.tar.gz
[/code]

This will download the latest “WordPress” to your home folder

Now we have to extract the downloaded file to right htdoc root. We are talking about the default configurations available with apache configuration files. Hence the wordpress should be extracted to ‘/var/www/hmtl/’ folder, which is a protected path. We’ll change the folder permissions temporarily to facilitate the extracting

[code language=”bash” gutter=”false”]
$sudo chown 777 -R /var/www
[/code]

now switch to ‘/var/www/html/’

Issue the command

[code language=”bash” gutter=”false”]
$tar -xvzf /home/rajesh//latest.tar.gz
[/code]

NB: Replace the pathname, please

Now we will create a new database for the “WordPress” using phpmyadmin

Create a new MySQL user

Give the newly created user database privileges, in my case, I created a new datbase called “intranet”.

Set all privileges. On a production environment, YOU are expected to set them wisely.

As all set with the database, we will install “WordPress” now

Access the link (please changed according to your setups)

http://localhost:8088/wordpress/wp-admin/install.php, that will take you to “WordPress” installation page immediately.

Supply all requested details

Now we have a problem, “WordPress” installation cannot write to “wp-config.php” file. This is due to linux file/folder permissions. Hence we will again change the permissions over ‘/var/www/’ folder

[code language=”bash” gutter=”false”]
$sudo chmod 777 -R /var/www
[/code]

Now, go back to the previous page, furnish the details and “WordPress” should happily move ahead. Run the installation

That completes what we tried to achieve. “WordPress” running successfully from a WSL environment.

Now, it is time to ask the questions like “Why so much pain, when we can achieve the entire “WordPress” thing on Windows?”

Well, primarly “WordPress” performs better on Linux enviorment (personal observation), second & most important for me. I just love learning something new everyday. You are welcome!

Microsoft has added WSL with it’s Server 2016 (Version 1803) & the vision is pretty clear. Microsoft “wants” to provide a LINUX environment within Windows so that in the future, a separate LINUX Server requirement will NOT truly hurt the customers. Currently the WSL is NOT equipped with desktops/GUI & Pengwin like initiatives are sure making traction. If my guess will NOT go wrong, soon Windows Servers will be running “Linux” side by side & those Linux Servers are going to be as good as physical/Virtualized. We’ll wait eagerly, together.

regards,

Windows | Monitors do not wake up after “long” sleep

Hi guys

Recently I faced similar issues with 2 of my new monitors. Both were Samsung S2Nf350 Slim series LEDs (S22F350 & S24F350) & the issue was same.

After a long sleep/idle time the monitors will not wakeup with a mere keypress or mouse movement. I had to reach to the back of the monitors and press the power on button to awake the monitors!

I had chats with Samsung support & they asked me to take the monitors to nearest workshop as the LEGENDS at 1st level support decided that it was a hardware issue.

Then I decided to go through the user manual & came to an area where it narrated about a feature called “Off Timer”. This MUST be a new feature that is available with new generation LED monitors, helping them to turn off automatically after a pre-set period (default 4 hours)

Turning the feature to OFF state has resolved my weeks long “issues” with the monitor not awakening from a sleep.

May be that’s your solution also.

regards,

Install Oracle 18c on Windows 10

Hello guys

I am kind of disappointed, to be frank. After long planning, I decided to post about installing Oracle 18c on Windows 10 & started hearing about 19c. Well, shit happens and we will continue with what we were planning to do.

Before getting in to the real business, let me remind you. Oracle stack depends upon hell loads of VC++ components, and missing those packages could make things pretty difficult on Windows. So, as a thumb rule, please make sure that you have all available VC++ packages installed with your Windows box. Never forget that .Net 3.5 is also required by multiple Oracle software on Windows. Finally, do update your Windows.

You may refer this image for the VC++ & .Net components those are installed in my Windows 10 computer.

You can download Oracle 18c installation media for Windows 64Bit from the following link

https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-windows-180000-5066774.html

Please spend a moment to read what Oracle says just below the media link, “Do not install Oracle Database from a directory that contains a space character in it”

That means, while extracting the downloaded ZIP file, make sure you are extracting the archive to a folder like “D:\Oracle18c” NOT “D:\Oracle 18c” or “D:\Oracle Database 18c”

Oracle never liked “space” character in the directory name & if you are going to ignore this, you are going to have true troubles waiting for you mate.

What I have came to conclusion about Oracle 18c installation is:

Oracle is NOT anymore copying all the source files to a new destination. For example, usually when you install a software on Windows, there is installation source, from which necessary files are copied to a new destination (installed folder). This time Oracle is approaching the installation in a different fashion.

It ONLY configures the database.

That means, Once you extract the media, say to a folder called “D:\Oracle18c”, this is the same folder where the database will be installed! You will only be asked for Oracle base location, where the diagnostics and others will be configured.

The above is MY PERSONAL observation & I am yet to refer the installation documents from Oracle for clarify against what I found. Feel free to prove me wrong.

With the below installation example, I have extracted the 18c source media to a folder called “D:\Oracle_Installers\18c”, which will be the installation folder also

Run “setup.exe” as “Administrator”, that brings up a console sort of Windows that lists the installation progress (minimalistic)

We’ll create a database along with the installation, which is quick and effective (for this TEST)

Although we are installing the software on Windows 10, We will select “Server class” for System Class to “experience” the load stress.

Usually I choose “Advanced Install”, so that I can minutely access the different options available for the database like character set, memory etc

Database Edition should be “Enterprise” to experience the same “Cloud” version capabilities.

I’ve one Windows user account “Oracle” that I use for Oracle installations and setup. I suggest you also to create a non-administrator windows user “oracle” or something equivalent specifically for Oracle software.

As I have mentioned in the beginning, “The Software directory is the Oracle Database home directory”, ie, the folder from which you start the setup.exe will be the Database home directory. The path that specified by you will be the Oracle base & usually the diagnostics elements will be placed within.

As a rule, I always select the same password for all vital accounts & the pattern is something like ‘Abcxyz123’. For a TEST lab, this saves me unwanted complexities & less documentation. For a production instance, please follow Oracle’s recommendations.

My laptop boasts one i7 8th generation processor, 16GB DDR-4 memory and 512GB Samsung 860PRO SSD. Yet, the installation takes it’s own time. So if you have less hardware configurations please wait patiently.

You know what to do with JAVA security prompts!

Once the installation over, you will be provided the link to Oracle Enteprise Manager Database Express, which is a minimalistic EM for the database. You can log in and view the database details and interact the database parameters through this interface.

I know you are smart enough to find out that I altered the below image a little bit…Well it looks something like this…

My laptop doesn’t have FLASH installed, hence I am using Internet Explorer for accessing the EM. I have to check whether I can access the EM using Firefox after installing Flash.

That’s all folks. The installation is pretty straight forward other than for the installation folder/directory design part. For a production environment, you may need to truly plan how the folder/directory name is going to be.

Ah, finally! My laptop is lagging beyond expected levels with the database services running.

Have comments & suggestions, please let them come in.

regards,

rajesh

Oracle APEX 18.x Installation | Windows

Hello guys

Referenced documents:

https://matthiashoys.wordpress.com/2012/04/06/uninstallation-of-apex-from-a-default-oracle-11gr2-database/

https://www.oracledbwr.com/oracle-apex/oracle-application-express-18-1-installation/

Let us see how Oracle APEX (Which will eventually replace Oracle FORMS (regardless whether you like that fact or not) for small to medium applications) could be installed on your Windows 10/Server this time.

We’ll be trying the latest version of ORACLE APEX 18.2 & Windows 10 Pro running the latest version 1809 (as on date, fully patched) and Oracle database 11g R2 (11.2.0.4). Oracle depends upon VC++ components loads, so make sure your Windows box is fully patched.

Step #1

Download Oracle APEX installer from APEX repository. Please note, for APEX 18.x, you MUST have 11G R2 11.2.0.4 or later database. 11g R2 11.2.0.4 is only available for customers with valid support, hence if you are a student, freelancer, you best bet is 12c database, that is still freely available to download.

Step #2

Unzip the downloaded .zip file to a finely structured directory. For example, you can extract the zip file to D:\APEX, that will create another “apex” folder within “D:\APEX\”. Check the attached image

I usually keep all the Oracle Installers under a single folder, called “D:\Oracle_Installers”. So basically the rest of the instructions, I will be using the same directory structure. Please don’t get confused.

Step #3

Switch to the apex folder from an elevated command prompt, in my case to “D:\Oracle_Installers\apex_18.2\apex”. Source the database environment (eg: SET ORACLE_SID=ORCL) in case if you have multiple Oracle products installed in the same box

Step #4

Start SQLPLUS as sysdba

A default installation of 11g R2 installs APEX, which you should remove at this stage. If you are too eager to know which version is installed, you my run the following query

select version from dba_registry where comp_id='APEX';

If you haven’t removed the APEX installation, it should return you a row with installed version information of APEX

You should remove the existing APEX prior installing the 18.x version(s). To remove the installed version, please execute as following

@apxremov.sql

Wait for the uninstallation to complete, exit SQL and start SQLPLUS once again as sysdba (Mandatory)

Now we can start the installation of APEX 18.x. Prior starting the installation let us create a dedicated tablespace for APEX.

CREATE TABLESPACE APEX DATAFILE 'D:\ORADATA\ORCL\apex01.db' SIZE 1G;

Now execute the SQL for installing APEX 18.x

@apexins APEX APEX TEMP /i/

(Note the forward slash "/")

Wait for the installation to finish. My box has a SSD, hence the installation completes in few minutes time (3-5 minutes I believe). It may take bit more time, depending upon the hardware resources available.

Once the installation completes successfully, we have to configure the APEX. To configure execute “apex_epg_config.sql” that is available in the current path. Please note, here is the catch, as with the installation, Oracle expects forward slashes around the parent folder where you have the image files. So, I have extracted the installation files to “D:\Oracle_Installers\apex_18.2\” & this is the PARENT directory for apex installer. Hence we will call the EPG script as below (correct your path appropriately)

@apex_epg_config.sql D:/Oracle_Installers/apex_18.2/

(Note all back slashes are replaced with forward slashes)

Once the script finally exists after successfully copying the image files to APEX repositories, you need to unlock the following Oracle database schemas

ALTER USER anonymous ACCOUNT UNLOCK;
ALTER USER xdb ACCOUNT UNLOCK;
ALTER USER apex_public_user ACCOUNT UNLOCK;
ALTER USER flows_files ACCOUNT UNLOCK;

Now, go ahead with the last configuration by executing

@apxconf.sql

This script will ask for ADMIN user details, Select ADMIN as admin user and provide an email address. Please note, you must provide a punctuation symbol for the password and allowed punctuations are as below

(!"#$%&()``*+,-/:;?_).

That’s all folks. If there are no errors, you are all good to go. You may need to configure your database server for better performances, which we will discuss bit later.

Now try to access the APEX installation. Unless you have changed the ports, you should able to access the portal by visiting http://localhost:8080/apex/apex_admin

regards,

rajesh

Oracle Linked Server | MS SQL

Updated on: 20th November 2020

I see many visits to this thread from Microsoft Technet references & one of the visitors left me the following comment:

For 12c client, I tried applying the steps you described and it did not work.
 I found that there is a bug affecting Oracle Provider for OLE DB – Version 12.1.0.2 and later
 Oracle OLEDB Throws Error “DBCOLUMNFLAGS_ISFIXEDLENGTH” to Handle CHAR(n BYTE) From Linked Server With MS SQL Server (Doc ID 2484393.1)

Thanks Larisa for your kindness to share the information & found that the error is tightly related to Oracle database version 12.1.x.x. Oracle note provides a workaround using OPENQUERY. I don’t think a “workaround” as a permanent solution, Instead, if possible, upgrade your database to 12.2 or higher versions as a possible permanent fix. Oracle claims this bug was addressed by “Windows Database Bundle Patch 26720785 and later versions”. If you don’t have Oracle’s valid support contract, will not able to download the patch & the only possible solution could be upgrading your 12c database while the download repositories still holding the 12c media. So hurry up :)

As Organizations grow, will end up with multiple software solutions taking care of different areas of the business. Based on the availability, there would be situations where business will end up with different database technologies and data exchanges between such will become a mandatory element to avoid duplication and additional efforts.

Today we will discuss about a requirement that involves MS SQL Server and Oracle database. While Microsoft has well established solutions called as Linked Servers, that allows the MS SQL to connect with heterogeneous database technologies, Oracle’s approach is pretty tedious and may require more efforts that one could anticipate. Yes, I am talking about Oracle “Golden Gate”, which allows an Oracle database to access other database technologies through a “gateway”

Instead of discussing the complex “Golden Gate”, we will see how simple (& complex at the same time) it is to setup Linked Servers from Microsoft SQL Server (for Oracle)

Install the client

Depending upon your OS architecture(32Bit/64Bit), you need to install the appropriate Oracle client. I suggest, install 64Bit Oracle Client on 64Bit OS and 32Bit client over 32Bit OS. You save loads of efforts by doing so. I always do a full installation of the Oracle client and later add missing components those are mandatory for “Distributed Transactions”. So what is a distributed transaction?

  • Your MS SQL Database table receives a row (record)
  • Your expectation is to replicate the same row to Oracle table
  • You have an “after insert” trigger defined with the MS SQL Table

A full installation of Oracle client (Administration) doesn’t install the mandatory component to facilitate the above requirement. You must install “Oracle Services for Microsoft Transaction Server” in order to do such a distributed transaction from MS SQL to Oracle database, after invoking the Client Installer. Make sure while the installer kicks in, you select the already existing Oracle home to make sure that the installation will not create another home for the additional components those you will install. Cross check whether the Oracle OLE DB driver is installed & install the Oracle Services for Microsoft Transaction Server component.

Analyze your Oracle provider and make changes to the stack

Make sure your provider, in our case, “OraOLEDB.Oracle” is configured prior you create linked servers.

Enable the following options

  • Allow inprocess
  • Nested Queries
  • Support “Like” operator

and disable

  • Index as access path. Disabling this option is mandatory for “Distributed Transaction”. If your Oracle table has indexes and this option is not disabled, an insert attempt from table trigger will fail with the following error

The OLE DB provider “OraOLEDB.Oracle” for linked server “ERPPROD” returned a “NON-CLUSTERED and NOT INTEGRATED” index “XXFPPUNCHM_N1” with the incorrect bookmark ordinal 0.

Create a linked Server

Right click on the “Linked Server” node and select “New Linked Server…”. I am providing you details of a linked server that is already created at my end. Adjust your linked server details accordingly. Make sure, the Oracle client installation folder is your “PATH” & that the tnsnames.ora file has an entry for the Oracle service (that you will enter in “Provider string” column.

If you have entered the mandatory elements correct, you have successfully created a linked server. If anything goes wrong you will be prompted about it, and address them. Please note, you can always revisit and change the Server options at later stages. However, options under “General” cannot be modified. If requires modifications, you need to drop and recreate the linked server once again.

Test the newly created linked Server

The actual issues starts from now. Please note, my laptop that I use for all developments has multiple versions of Oracle database and clients installed in addition to .Net development tools, Android, PHP etc to name few. If you are planning to implement the linked server solution for a production environment, make sure you have only one Oracle product installed along with MS SQL server. On the other hand you are going to have end number of complexities, few of which are not easily addressed.

For example, my development machine has:

  • Oracle database 11g
  • Oracle Client 12c (12.1)
  • Oracle Database 12c (12.2)

Which is a more than complex situation to address when it is all about MS SQL linked servers. One of the toughest issues to address is the following error

The OLE DB provider “OraOLEDB.Oracle” for linked server “” supplied inconsistent metadata for a column. The column “” (compile-time ordinal 2) of object “””.””” was reported to have a “DBCOLUMNFLAGS_ISFIXEDLENGTH” of 16 at compile time and 0 at run time.

It took me almost 1.5 days to figure out what could be wrong as a simple SQL query like following against the newly created linked server continuously provided me the above error.

Select count(*) from [ERPTEST]..[APPS].[XXFPPUNCHM] 
 go

I’ve come across a post over stackoverflow.com which said, this could be due multiple Oracle products being installed in the same box & there were few instructions to overcome, which didn’t workout for me. However, I was successful with the production server, in which I only had the Oracle 11g client installed. To insure the real time replication of the data from MS SQL to Oracle database, I had to alter few registry values & restart the server. To my utter surprises, the same scenario I tested over three different boxes & all three experiences were different from each other.

  • The production server where I have SQL Server 2014 standard edition would not post rows to Oracle database, that is a part of “Distributed Transaction” without the registry hacks.
  • My development laptop wouldn’t even fetch rows from Oracle database without tweaking the PATH environment element & registry with proper .dll paths
  • My home PC does everything without having to tweak the path of registry where I have almost the same setup like my development laptop. The ONLY one difference with my home PC is, instead of Oracle client 12c, I have 11g client.

Now we will address each of such situations. Please note, the following exercises require you to make registry changes, so please make sure that you take a full backup of the registry prior attempting any given possible solutions. (If you are having ONLY one Oracle product installed (Database or Client, please move to Step#2 )

Step#1: Register Oracle OLEDB driver (This is to insure that we are using the same stack across the solution). Only one version of OLEDB driver could be activated at a time, regardless how many Oracle products are installed. If you had 12c installed after 11g, you must be having the 12c OLEDB driver activated.

From an elevated command prompt, switch to Oracle Client/Database BIN folder (eg: D:\oracle\product\11.2.0\dbhome_1\BIN)

Issue the following command

regsvr32 OraOLEDB11.dll

This should register the OLE DB driver for you.

Step#2: Check your OS PATH environment element, your client/database bin path must be the first Oracle product entry, eg:

This will insure that tnsnames.ora will be sought in this path, in addition to Oracle dlls. As we are using Oracle database 11g as the first product from the list of other Oracle products installed in the environment element PATH (refer the image above), we will hack the registry with all elements related to the specific product (Again, please make a backup of the registry, minimum the specific key)

Registry key: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

Refer the image above and adjust the entries as per your Oracle installation. Once the registry is modified, restart your computer (mandatory)

Once the box restarted, try to insert a row into the Oracle table. Example

insert into [PRODBAK]..[APPS].[XXFPPUNCHM]
 (PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE)
 VALUES(GETDATE(),'IT Office','0046','0')
 go

SQL Management studio must stop complaining about “The OLE DB provider “OraOLEDB.Oracle” for linked server “” supplied inconsistent metadata for a column. The column “” (compile-time ordinal 2) of object “””.””” was reported to have a “DBCOLUMNFLAGS_ISFIXEDLENGTH” of 16 at compile time and 0 at run time.” right after setting up the correct .dll files and the relevant paths in the registry.

Now create your table trigger, through which you want to push a row to the Oracle table. A simple after insert trigger could be defined like following:

create trigger addRecordsToERPTable2 on [UNIS].[dbo].[tRajesh]
 after insert
 as
 begin
 insert into [PRODBAK]..[APPS].[XXFPPUNCHM]
 (PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE)
 Select PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE
 FROM inserted
 end
 go

You may come across an error while a distributed transaction is initiated, Management Studio complaining about “Msg 8501, Level 16, State 3, Procedure addRecordsToERPTable2, Line 13
MSDTC on server ” is unavailable.” This is a pretty simple error to address. Open the Windows Services & check whether the service “Distributed Transaction Coordinator” has started. My development machine initially had this service startup mode set as “Manual”, I changed it to “Automatic delayed start” and started the service. Adjust according to your situation.

Basically the above few things should address most of the common issues you would face with Oracle Linked Server from MS SQL.

Finally, Oracle clearly states, there are limitations using their driver for Linked Servers from MS SQL. So, expect for unexpected while using such a setup. For me, it was simple transactions. If you are expecting rapid replications based on complex business requirements, please do test your scenarios as much as possible prior adapting the above hacks.

regards,

rajesh


kernel-power|Event id 41 | Windows 10

Hi guys

So? You have few years old hardware & opted to upgrade to Windows 10, when it was offered free? don’t worry, you made a wise choice. Although the OS is not perfect, it is better than Windows 8.x, definitely feature richer than predecessors…until your box starts rebooting once in few minutes or few times per day.

Your event logs for system will plainly say “kernel-power” and about an unclean restart, which is a critical event.

There could be a number of reasons, starting from the OS’s new power management “features” conflicting with your legacy hardware, components like additional audio or video cards behaving strangely after being there for last many years etc. This post is not a single stop solution for all your “kernel-power” issues, instead, few guidelines those may help you to narrow down the issues and resolve them in a timely manner.

My semi-server type desktop machine is 6+ years old. I never bought myself a branded desktop machine as I always needed powerful (please read affordable to my budgets) boxes for database development & virtual machines. I opted to go with the free Windows 10 upgrade when it was offered and never had many issues with the box or OS until last month. My kids (who are using the desktop mostly now days for their entertainment) started complaining about the machine rebooting quite often & I suspected the poor wall point (which truly required a replacement along with the extension board) & immediately changed it. Things were better for a while and then came the October update build 1809. The unexpected reboots were sporadic, sometimes once in just few minutes time! I scavenged through the event logs and found multiple “kernel-power” event entries. So I started going through many articles those were patiently explaining what are the factors those could cause “kernel-power” events

  • Bad power supply units
  • Conflicting Audio components
  • Old base boards (motherboards)
  • Bad power sockets
  • and Windows power management

I was sure that my XFX 850W PSU cannot be wrong ;), so without wasting much time, I moved on to audio. My motherboard has RealTek audio built-in and I have extended my box with a Creative Sound Blaster Zx card almost year back when my Audigy gave up. So I removed the Creative sound card and the box started behaving properly for approximately one and half days before giving up to random reboots.

Slowly I started building up a pattern for the reboots. I noticed that the machine only rebooted when I was streaming youtube videos and ONLY while I played videos the box rebooted. My box had a nvidia GT 520 card to play the only one game ever owned “Swat 4”!

I opened up the box again and removed the PCIE2.0x16 card out to realize that the card was totally packed with dust. Without giving it another chance booted the box with on-board display to insure that the issues were related to the GPU (This was the wild guess part)

Well, for me, all the kernel-power events stopped ever since I removed the nvidia card from the box. Not a single reboot after hours of streaming or playing downloaded video clips.

So, is my nvidia display card really faulty? I don’t know for sure. Nvidia didn’t push new drivers for this card from last many months & the issues could be from conflicting software components between the OS and the card driver (could be!). I’ve to find some time very soon to be sure!

In the same scenarios, you can try the following in case if you also own an extended desktop that experiences “kernel-power” events once after upgrading to recent builds of Windows 10

  • Remove all additional cards from the box
  • Remove all software related to the cards those you have removed from the box
  • Make sure to dust off your box from inside (Use a powerful blower, and from a safe distance to dust off)
  • Insure all the cables are tightened securely in places
  • Keep playing videos for extended periods of time (To insure there are no reboots)

In addition to above few, a computer could start acting weird when any  of the component used within starts having hardware or software issues. If none of the above tests resolves your issues, may be it is the time to start thinking about a new box (unfortunately)

I hope few out there find the above useful!

regards,

rajesh



Flickering white dots on screen | Windows 10

Hi guys

My nvidia GT-520 PCIE 2.0X16 card has finally failed & that altogether started a new story, a new finding and possibly a lousy solution as well.

My OEM motherboard from Intel ( DH67CL) has both Video & Audio integrated along with one NIC. More than enough to start with a PC without additional cards, which is NOT always what you want. You want to game, you need a card. You want to listen to your games, you need better audio processors, so you need another card.

Now, there are no guarantees that these cards will “Work for anyone” forever. The vendors will stop developing drivers, the product itself start having technical difficulties and your BOX as a computer will start experiencing issues as the box itself ages.

So after I removed the nvidia graphics card from my box and hooked up the Samsung LED monitor to the on-board intel HD display(HD 2000), I noticed many sparkling white dots

especially over the taskbar area & almost everywhere the box tends to see gray or darker shades. I knew that the HDMI port on board is version 1.1, when the cable that I used and the monitor are HDMI 2.0 capable. So I hooked up a normal VGA cable and was surprised to see that nothing changed! The sparkling white dots were persistent and I started googling, to find a proper solution.

Almost all solutions were about securing the cables, checking the driver, assuming the MB being faulty & even raising fingers at the PSU!

I wanted to check Windows 10 settings and to find out something to reset the display settings (I was hoping to find some) & after few minutes I landed on Settings -> Colors

I kept on toggling switches and finally toggled the “Transparency Effects” switch, which was ON. Immediately the white sparkling dots disappeared (99% as I could still see few of them against pitch black BOLD letters)

Color settings

It looks like Windows 10’s video processing has issues with older hardware, especially with older base boards. Hope this is a “kinda” solution for you also, if you are reading this post :)

regards,


Notepad++ 7.5.x | Plugin Manager

Hi guys

Starting from Notepad++ 7.5.x “Plugin Manager” is not bundled with this editor & you would find why and associated reasons here

So how do we have this “Plugin” configured for managing the plugins? Well the solution is pretty straight forward, download the latest version of the plugin manager from here

Please note, after downloading the plugin which is a zip archive, you need to extract it and there will be two folders, one for the Plugin Manager plugin & other one for the updater. Check the below image:

npplus-pm

Note: The instructions given below are specific to 32-Bit version of Notepad++

Now, copy “PluginManager.dll” from plugins folder to “C:\Program Files (x86)\Notepad++\plugins” (default installation location, adjust to suite your environment)

Copy “gpup.exe” from “updater” folder to “C:\Program Files (x86)\Notepad++\updater” (Mandatory)

Now you are all set to start using Plugin Manager once again.

regards,

rajesh