Install Oracle 21c database on Windows

If you have already installed Oracle 19c following instructions available here, installing Oracle database 21c is not much different. Basically 21c is “Innovation Release” & 19c is going to be the long supported version as on date. It plainly means, there is no need to hurry to upgrade your 19c to 21c as next release will be the next long supported version.

Let us see how to install Oracle 21c on Windows (Installed on Windows 10 21H1) & the instructions are same for Windows 11 also.

You can download the 21c Windows installation media from Oracle Database 21c Download for Microsoft Windows x64

Extract the .zip and change the root folder to something like “Oracle12c” (or a name that prefer. Please avoid using spaces with the folder name, example: “Oracle 21c”)

I used a virtual machine with single drive, hence the zip file was extracted and I renamed the extracted folder root to “Oracle21c”

Once the folder is renamed to your choice, open the folder and execute the “setup.exe” as administrator. Please note, you must start the setup as administrator, regardless whether your Windows user account has administrator privileges on the system.

This will kick start the installation process and depending upon the resources available it could take minutes before the installation GUI appears for you.

Choose “Server class” regardless whether you are installing it on a Server OS or client OS. It’s all about managing the resources.

Now, this is a very interesting perspective. “The software directory is the Oracle Database home directory” & the path that you will mention in the edit box is going to be the Oracle base. I hope, now you understood why renaming the media extracted folder to a very meaningful name is important. As usual Oracle suggests you the defaults, however I recommend you to follow a pattern that you can feel comfortable with.

As you could see, the default installation sets up one pdb. You may rename the default PDB at this level.

Here I left everything to defaults as my intentions were pretty limited to installation. Unless you have a reason to set up Automatic Memory management, go ahead with the well trusted manual management, that gives you granular level control on how the memory is utilized by the database.

You can change the database character set to another from the “Choose from the following list of character sets” (Specifically for Arabic and other left to right languages)

You may choose a different location for the database data files. Just make sure that the current user has full access to the path.

Use the same password for a TEST installation & follow the Oracle recommendations for PRODUCTION environments. Please note, it’s better to use the complex passwords with expiry disabled for the default profile.

That’s all folks. It’s not like the earlier times when one had to hack the installation files to install Oracle database on newer Windows OS. Starting from 11g, Oracle database gets installed on Windows 7 and later OS without complaining as long as the OS is fully patched with updates and .NET components.

Hope this post helps few newbies (& me)

Oracle “directory” object | OS level permissions

We are using Oracle EBS R12 and for a custom module, wanted to log the Oracle seeded API outputs to custom log files for later scrutiny & error corrections.

Following the standard procedure, create the folder like below

create or replace directory OMS_LOGS as '/u01/applmgr/oms_logs'

and started testing the directory using SQL Developer where I am logged in as APPS user

I kept getting the below errors

ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 8

and the detailed errors said this could be due to OS level access permissions. Luckily I landed on a stackoverflow discussion & one of the answers clearly said this error “could” be due to the file/folder permissions for user “oracle”.

As “root” I changed the permissions for the path “/u01/applmgr/oms_logs” like below

chmod g+w /u01/applmgr/oms_logs

and that fixed the invalid file operation errors. Hope this helps someone out there!

Windows 11 | Dial a VPN Connection using PowerShell script

I started blogging once after upgrading to Windows 7 & posted mostly about the ridiculous bugs Microsoft exported with that OS. Gradually Windows 7 got matured (hardly ever fixing the yellow triangle network icon issue) & my entire attention switched to what I do for salaries, Oracle development & later much of my posts were about the stack.

Now Microsoft has released another half cooked OS, Windows 11. From a layman perspectives I cannot understand how someone could make such decisions that affect the established stability and ease of use of an OS that creates huge disappointments for general userbase!

Other than cosmetic changes and revamped settings area, I cannot defer Windows 11 from Windows 10, plus the disappointment of losing the start menu that I was getting used to after loads of patience and efforts. Among many of such grievances, connecting to VPN at work is so ridiculous, requiring 4 mouse clicks! So I decided to go with a cmd/powershell script this time to avoid those 4 mouse clicks.

After weighing the possibilities of extending , I decided to go with PowerShell (Version 5)

So let us check how it works. Copy the following in to a text file

rasdial.exe "Your VPN Name"

Save the file as “Dial VPN.ps1” or any other name you prefer with extension “.ps1”.

Make sure you wrap the VPN name using double quotes. “rasdial.exe” is not a powershell cmdlet, an old Windows OS friendly dialer. This executable is generally found in the Windows\System32 folder and there is no need to specify the path, unless you modified the PATH environment variable. Now create a new shortcut on the desktop and type/copy the following (Please adjust the file path as per your setup) as command for the shortcut

powershell.exe -File "C:\scripts\Dial-VPN.ps1"

Usually I keep all my scripts in a folder named “Scripts” on the C: drive. Hence the -File parameter clearly mentions the script’s path.

That’s all, you can double click and open the pre-configured VPN connection without going through the 4 click hassles on Windows 11 (or multiple clicks on other Windows OS versions)

Now, let us look at a fancier version of the above. What if you want to connect if not connected and disconnect if already connected? Please note, this script could be extended beyond this level depending upon specific requirements. I have started with the skeleton and will be happy to receive better scripts from you.

#https://community.spiceworks.com/topic/2271983-auto-connect-vpn
#http://woshub.com/popup-notification-powershell/
            
$vpnname = "Your VPN Name"
$vpnusername = "YOURUSERNAME"
$vpnpassword = "YOURPASSWORD"
$cmd = $env:WINDIR + "\System32\rasdial.exe"
            
try{
$vpn = Get-VpnConnection -Name $vpnname -ErrorAction Stop
Write-Host $vpn.ConnectionStatus
}
catch {
$message = $_
$wshell = New-Object -ComObject Wscript.Shell
$Output = $wshell.Popup("Failed to retrieve Connection status. $message",0,"VPN Connection Status",64)
}
            
if ($vpn.ConnectionStatus -eq "Disconnected")
{
$expression = "$cmd ""$vpnname"" "
Invoke-Expression -Command $expression 
$wshell = New-Object -ComObject Wscript.Shell
$Output = $wshell.Popup("VPN Connected",0,"VPN Connection Status",64)
}
else {
$wshell = New-Object -ComObject Wscript.Shell
$Output = $wshell.Popup("Your VPN Connection will be disconnected, Are you sure?",0,"VPN Connection Status",4+32)

if($Output -eq 6){
$expression = "$cmd ""$vpnname"" /DISCONNECT"
Write-Host $expression
Invoke-Expression -Command $expression 
$wshell = New-Object -ComObject Wscript.Shell
$Output = $wshell.Popup("VPN Disconnected",0,"VPN Connection Status",64)
}

}
#start-sleep -seconds 30
 

Please note, I am a beginner with PowerShell(Also) and always will be. As usual, I have provided the links to original codes & possible other links those helped me to device the above.

The above can, dial your VPN connection, warn you before you disconnect etcetera. For my ease, I prefer to use the extended version of the script as I keep on switching the connections. You may able to extend the script once again by accepting the VPN connection name, so that you can use the same script for dialing different VPN connections (if you have many)

Have suggestions? please pass them to me through the comments section.

Windows 11 | Remote Server Administration Tools (RSAT)

A wonderful article about installing RSAT (Remove server administration tools) on Windows 11 is available at How To Install RSAT On Windows 11 PCs HTMD Blog (anoopcnair.com)

I would like reiterate something Anoop has mentioned in his article. If you are using Microsoft SCCM or Windows Update Services (WU), then you must enable the local group policy as he has mentioned clearly in his post.

The image attached by Anoop could be bit confusing for the local group policy setup, hence I am uploading one with better visibility. The path is “Computer Configuration->Administrative Templates->System” & you need to open “Specify settings for optional component installation and component repair”

No need to reboot the computer for installing RSAT components. Follow Anoop’s thread and be a happy Administrator!

Linux | File Cleaner | bash script

Recently we setup a Linux server for keeping backups & decided not to use certain switches while the backups were synched from Windows machines. This created an additional situation like maintaining the storage space based on different business requirements and using our own solutions. So the following script was developed. Please note, this script has been tested on CentOS/RHEL/OEL 7 environment & executed with root privileges.

#!/bin/bash

# Cleanup tool for Linux Samba Server
# Rajesh Thampi
# Date: Sep 2021
# Instructions
# Copy the script to a file with .sh extension
# Make it executable (eg: chmod +x filecleaner.sh)
# Execute! (eg:./filecleaner.sh 1>filecleaner.log 2>filecleaner.err
# And be careful :)


function purgeit(){
# local DIRNAME="$1"
# local FILETYPE="$2"

cd "$1"
echo "Entered Directory: ${PWD}"
#Logic
#Check whether $3 number of files matching the patterns provided by $2 are present those were created within $4 days, then delete all files older than $4 days
 
if [ $(find -maxdepth 1 -name "$2" -type f -mtime -"$4" | wc -l) -ge $3 ]; then
local OBSFILECNT=$(find -maxdepth 1 -name "$2" -type f -mtime +"$4" | wc -l)
echo "There are ${OBSFILECNT} files & will be purged"
local obsfiles=$(find -maxdepth 1 -name "$2" -type f -mtime +"$4")

#The below loop is ONLY for logging purpose
#We'll delete all files matching the pattern in a single line command using "find"

        if [ $OBSFILECNT -gt 0 ]; then
        echo "Below Files will be deleted"
        for eachfile in "$obsfiles"
                do
                echo "$eachfile"
                done
                #find and delete will eliminate the need to treat files with space and other escape characters in the filenames.
                find -maxdepth 1 -name "$2" -type f -mtime +"$4" -exec rm -rf {} \;
        fi
fi
}

#Call the function passing four variables: path, type of the files to purge, number of files to keep & age of the files
#those need to be deleted

#syntax: purgeit "/backup/server_sql" ("*.txt" OR "my*.php" OR "*" OR "*.*") 4 5
#example: purgeit "/backup/server_sql" "*.zip" 4 5
#You can call this function N number of times passing different paths and other values

Now let us see the logic in details.

Consider you have a path “/backup/server_sql” where your Microsoft SQL Server is uploading a full backup daily. As we are synching the backup files using ROBOCOPY from the Windows server without mirroring, the daily full backup files will start mounting in the Linux files server. Then we came up with a business plan to:

  • Keep minimum 4 number of most recent full backups for the SQL server in the Linux path those were created within last N number of days. If there are no files for last N days found, existing files will not deleted (gives an opportunity to investigate why there are no files uploaded to Linux file server)
  • Delete files those are 5 days or older from the Linux path after insuring minimum N number of files are within the repository.
  • Combined with a function send alert emails, this small snippet could function as both a storage maintenance and monitoring tool.

Interested about including email alerts? Let us know and will share the additional code with you exclusively.