Running Oracle on Windows: An Island Unto Itself

For many DBAs, the thought of running Oracle on a Windows OS induces a collective cringe. Even for someone like me, with a career spanning both Microsoft and Oracle technologies, it’s a combination I typically avoid.

However, there are scenarios—driven by licensing, software requirements, or other factors—where deploying Oracle on Windows becomes the logical choice.

I recently encountered such a scenario while assisting a team I enjoy working with at Redgate. The task was to refresh and streamline our demo virtual machine (VM) image, which included Oracle running on Windows. Here was my planned approach:

1. Export any data that needed to be retained.
2. Create a new Oracle 19c home and apply the latest Release Update (RU) patch.
3. Organize datafile locations, as files had been scattered across unexpected directories over time.
4. Drop and recreate databases with proper naming conventions, migrating data and installing sample schemas as required.
5. Upgrade to the latest version of SQL Developer.
6. Update start/stop and management scripts for the new databases.
7. Clean up miscellaneous items as requested by the team and hand over scripts for ongoing support.

Revisiting Oracle on Windows

It had been years since I worked with Oracle on Windows. At Microsoft, we always ran Oracle on Linux—either Oracle Linux or Red Hat. While I had recently installed Oracle 23c on my laptop, that was through the GUI, a method I seldom use in my daily workflow.

For anyone managing Oracle across both Linux and Windows environments, my first recommendation is to install Linux-like tools on the Windows VM. The number of times I instinctively typed commands like vi or ls at the Windows command prompt, only to receive an error, was amusing but frustrating. 😊

The second key difference to note is that Oracle on Windows operates as a multi-threaded application, unlike its multi-process architecture on Linux. This means all Oracle components run as services, and managing background processes or starting instances requires working with the relevant Windows services.

Challenges with Patching

After installing a new Oracle home, I attempted to apply the October 2024 RU patch. Troubleshooting on Windows is a different beast, and I encountered several errors. These included:

• File directory permission issues requiring adjustments.
• Registry conflicts caused by MySQL, which was also installed on the VM and shared some registry keys with Oracle’s inventory.

To avoid disrupting the MySQL environment critical for this demo server, I reverted to the original Oracle home. While jokes comparing the risks of mixing “beer with wine” were inevitable, the underlying issue highlighted the challenges of coexisting database platforms.

Adventures with Pluggable Databases and Oracle Managed Files

I exported the two databases we wanted to retain and began experimenting with pluggable database (PDB) creation, as I hadn’t previously worked extensively with Oracle Managed Files (OMF). This led to some interesting discoveries.

OMF, either by design or a quirk, disregards the db_file_creation_dest setting if a file already exists in its preferred directory. Instead, it creates a new directory with a 20-character string as its name, often leading to chaos in file organization. Once OMF files are created, you cannot move them. Additionally, exporting to XML and recreating the database doesn’t allow renaming OMF files.

To maintain order, I found a workaround:

1. Set the db_file_creation_dest parameter to blank.
2. Create a new seed database.
3. Use the file_name_convert parameter to enforce clear directory and file naming conventions.

Here’s an example of the creation process:

CREATE PLUGGABLE DATABASE PDB2 
ADMIN USER pdb1dba IDENTIFIED BY "<psswrd>" 
DEFAULT TABLESPACE USERS 
DATAFILE 'C:\ORACLE\ORADATA\ORCL\PDB2\users01.dbf' SIZE 500M AUTOEXTEND ON 
FILE_NAME_CONVERT=('C:\ORACLE\ORADATA\ORCL\PDBSEED\', 'C:\ORACLE\ORADATA\ORCL\PDB2\');

After importing the sample schema data and retained data into the new PDB, I used it to clone additional databases as needed:

CREATE PLUGGABLE DATABASE PDBT FROM PDB2 
FILE_NAME_CONVERT=('C:\Oracle\oradata\ORCL\PDB2', 'C:\Oracle\oradata\ORCL\PDBT');

Finally, I cleaned up the old, disorganized PDBs, dropping datafiles and manually removing empty directories left behind by OMF:

ALTER PLUGGABLE DATABASE OLDDEV CLOSE IMMEDIATE;


ALTER PLUGGABLE DATABASE OLDDEV DROP INCLUDING DATAFILES;


Wrapping Up

With the new databases in place and organized, the next steps include finalizing the database scripts to automate starting and stopping the instances during demos and installing the latest version of SQL Developer. Despite the challenges, the VM image is shaping up nicely, and the experience has been a rewarding exercise in adaptability.