Featured

We are going COM

Hello guys

We just completed 10 years of blogging with WORDPRESS & it was a great experience! Although we’ve started the blog ONLY to vent out our frustrations with Windows 7 OS, over the years we’ve discussed many other technology areas.

Well…err hmm, about many things those WERE not significant at all, however many of you have seen them ;) . Now you may ask why switching from a life long free blog to a COM (commercial site). Answer is simple, WE are going to have few advertisements running over the blog, which might fetch us some income!

We’ve been enthusiastically doing many charities and currently planning to setup an educational fund for under privileged expat students in Kuwait. We will utilize every penny that comes through the ads for this purpose.

So if you find an interesting ad on our pages, please check them out. We’ve never commercialized any of our efforts & will remain to provide you the hacks/solutions free.

regards,

Featured

Active Directory Listing Software

Hi guys

Today I am going to share a compact software that could generate moderately detailed information about the Windows active directory member computers, servers and users from a single console, that I started developing many years back.

This software helps you to export the fetched details to an excel sheet for future references. While there are no restrictions exclusively attached to the usage of this software, the source code for the application is ONLY available against written requests. Refer the readme.txt file for more details.

Please download the zip file from this link and extract to a single folder. Double click open “WpfWMI” to run the software (No installations required). We are using open source EPPlus.dll for the excel export and you must insure that the dll file is within the same folder of the executable file (WpfWMI.exe)

Pre-requisites: The computer you are running this software MUST have .Net framework 4.5 installed.

Expected bugs:

  • When you are not connected to a domain, software will show you a message “Not connected to Domain, Aborting”. However, the main application will load and trying to load objects will crash. I’ll fix this the earliest possible.
  • If your internal & external domains have same name, for example internal domain name is “abc.com” and your public website is “www.abc.com”, you may experience the above mentioned crash while connected to your network over a VPN connection.
  • Poorly designed About, help sections. I apologize for the same as I never had intensions to release it for public usage. Will try to accommodate as much with next public release.

Test it & post your comments and suggestions.

regards,

rajesh

Featured

Asking questions, following them up

Hello Guys

Not all situations, however for certain interesting cases, I spend loads of time out of my professional and personal life to nail down the concerns, situations you highlight through the comments and I feel neglected and insulted when YOU don’t respond to my queries on timely manner. No, it doesn’t mean I am not going to respond to your specific questions, however I may not follow up the progresses you have made unless I see a keen involvement.

Further, many instances I contact you through the emails provided with your comments and other than 1-2 instances, I have never received replies. I send you emails to insure that your private information are not being visible through the comments area. Hence, please make sure that you will be using regularly used email accounts while commenting or asking questions.

Please follow the below guidelines if you are keen about getting answered

  • Provide me an email address that you are frequently checking
  • Regularly check for the comment replies (You get a notification on email when I reply to your comment)

If I am not supported by YOU as the initiator, please be informed that, I do have better things to do :)

Sorry for the “Attitude”, however, that is going to save me some precious time!

regards,

 

 

Featured

Windows7bugs | Script Copying Instructions

Hello guys

Once in a while we receive complaints about scripts copied from our blog causing issues. We regret those issues and apologizing to everyone who suffered due to. Our blog is totally free until date and unfortunately, different browsers behave differently while parsing the code tags.

With reference to above said, we request you to kindly make sure that the scripts those you copy from our posts are thoroughly checked for special characters prior applied. Especially registry related scripts as such attempts could completely wreck your Windows registry databases.

We hope you read us clearly and continue supporting us.

Regards,

Oracle 19c | Applying Patch

Much of the times I use latest Oracle technologies for learning & they hardly make to any of the PRODUCTION environments those I support at work. Recently I wanted to see how to patch Oracle 19c installation on Windows and to be frank, it was buttery smooth.

Let us quickly see how to apply a patch to a Windows installation of Oracle 19c database.

Shutdown all instances & Windows services for Oracle. Make sure you have taken adequate number of backups for the database(s).

Check you computer’s PATH variable. Insure you have %ORACLE_HOME%\perl\bin, in my case “D:\Oracle\19c\perl\bin” as the first entry for PERL.

This is how the PATH information before I make the above changes to it.

Here 11g 32Bit client is the first Oracle software in the PATH, we will modify it like below

Once the patching done, you can rollback the PATH variable according to your requirements.

Visit Oracle support and download the patch, the latest patch for Oracle Database 19c is “32409154”. Patch is only available to customers with a valid support contract.

Extract the archive.

I’ve extracted the archive within the Download folder and the path is

C:\Users\xxxxxxx\Downloads\Oracle19c_Patch\p32409154_190000_MSWIN-x86-64

Now open an elevated command prompt and switch to the folder with patch number as it’s name. Check the below image for details.

Now we are ready to apply the patch.

Call the opatch utility from here, like given example below.

Microsoft Windows [Version 10.0.19043.985]
(c) Microsoft Corporation. All rights reserved.

C:\Oracle19c_Patch\p32409154_190000_MSWIN-x86-64\32409154>d:\Oracle\19c\OPatch\opatch apply
Oracle Interim Patch Installer version 12.2.0.1.15
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : d:\Oracle\19c
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.15
OUI version       : 12.2.0.7.0
Log file location : d:\Oracle\19c\cfgtoollogs\opatch\opatch2021-06-23_10-47-41AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32409154

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'd:\Oracle\19c')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '32409154' to OH 'd:\Oracle\19c'
ApplySession: Optional component(s) [ oracle.assistants.usm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.has.deconfig, 19.0.0.0.0 ] , [ oracle.swd.oui, 19.0.0.0.0 ] , [ oracle.has.cfs, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.ons.daemon, 19.0.0.0.0 ] , [ oracle.options.olap.awm, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.usm, 19.0.0.0.0 ] , [ oracle.swd.oui.core.min, 19.0.0.0.0 ] , [ oracle.tomcat.crs, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.has.crs, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.has.cvu, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.sdo, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.aspnet_2, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.ntoramts, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.blaslapack, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...

Patching component oracle.tfa.db, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.precomp.common, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.rsf, 19.0.0.0.0...

Patching component oracle.precomp.common.core, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.install.deinstalltool, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.ntoledb.odp_net_2, 19.0.0.0.0...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.rdbms.lbac, 19.0.0.0.0...

Patching component oracle.usm.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.ntoledb, 19.0.0.0.0...

Patching component oracle.has.common, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.has.db, 19.0.0.0.0...

Patching component oracle.rdbms.plsql, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.xdk.xquery, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.rdbms.olap, 19.0.0.0.0...

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...

Patching component oracle.ldap.owm, 19.0.0.0.0...

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.mgw.common, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.clrintg.ode_net_2, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

Patching component oracle.has.rsf, 19.0.0.0.0...

Patching component oracle.odbc.ic, 19.0.0.0.0...

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.has.common.cvu, 19.0.0.0.0...
Patch 32409154 successfully applied.
Log file location: d:\Oracle\19c\cfgtoollogs\opatch\opatch2021-06-23_10-47-41AM_1.log

OPatch succeeded.

As usual, based on your computer’s hardware capabilities, the patch would finish sooner or later. There will be 2 prompts those you need to say Yes in order to progress the patching.

Simple & neat right? Share your experience in the comments area.

Oracle PL/SQL | Exiting without executing rest of the code block

One of the major confusions for a PL/SQL programming beginners is how to exit a code block when a specific exception happens. Today we will see how we can handle these situation using user defined exceptions.

A developer can define as many exceptions for the code block and raise them as and when needed, than the default exception block for a BEGIN..END; block

Here I am trying to explain how a PL/SQL developer could exit the execution as soon as the first exception happens.

SET SERVEROUTPUT ON;

DECLARE
    l_number NUMBER := 11;
    myexp EXCEPTION;
BEGIN
    BEGIN
        IF l_number = 10 THEN
            RAISE myexp;
        END IF;
    EXCEPTION
        WHEN myexp THEN
            dbms_output.put_line('Okay, let us exit');
            RETURN;
    END;

    BEGIN
        dbms_output.put_line('Looks like there were no exceptions, let us show this!');
    END;
END;

The above example is trying to demonstrate how the exceptions are handled by dividing the code into multiple BEGIN..END blocks. So here I am doing a check whether the “l_number” is 10 and to raise an user defined exception and by merely calling RETURN exit the complete PL/SQL block! As simple as it looks here.

Give it a try by change the l_number value to 10 at the declaration level and see yourself. Happy coding!

ASP.NET Core | HttpContext | Get current windows username

Special Note: If you want ONLY get the currently logged in Windows username (useful for Windows domain networks) all you need is to change the website’s authentication to Windows & calling “User.Identity.Name”. The below example mostly looking at how to implement HttpContext in a project.

Recently we decided to retire our Classic ASP intranet application & opted ASP.NET Core for the upgrade. As a Business, we are totally in to Oracle technologies and hardly had much exposure towards .NET development. With the help of netizens, blogs and forums, we figured out the basics of CRUD operations using ASP.NET Core. However, were totally bowled out while getting currently logged in Windows usernames (domain accounts) for the application. Then we came across this post

Using the above post, we managed to figure out way to “get” the Windows username using HttpContext & realizing the above link (although helped us), was too technical for beginners and decided to make a post that simplifies the instructions further a level. So, here it is.

Please note, I am using 2019 community editions for both Visual Studio and MS SQL Database. If you are using a previous versions of Visual Studio, may not able to open the sample solution provided with this post. A note of caution. We are as fresh as possible with ASP.NET Core development & would appreciate pointing out our mistakes instead lamenting. Thank you.

So let us start creating a new ASP.NET Core project that uses C# (ASP.NET Core Web App (Model-View-Controller)

Give a meaningful name for your solution

Select “Windows Authentication” for your solution (must)

Your project explorer should look like this

Open Startup.cs file and modify “ConfigureServices” method like below

 public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllersWithViews();
            //Rajesh Added the below
            services.AddHttpContextAccessor();


        }

To keep it clean and simple, we will use a class specific to establish HttpContext.

Add a new class to Models, and let us call it “WindowsUserClass”

Add Microsoft.AspNetCore.Http namespace to the class before adding the below properties initializing the class.

using Microsoft.AspNetCore.Http;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace GetWindowsUserName.Models
{
    public class WindowsUserClass
    {

        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly string _userName;
        public WindowsUserClass(IHttpContextAccessor httpContextAccessor)
        {
            _httpContextAccessor = httpContextAccessor;
            _userName = httpContextAccessor.HttpContext.User.Identity.Name;
        }

        public string GetUserName()
        {
            return _userName;
        }
    }
}

We will use the Controller construct dependency injection (read it, going to be difficult to understand if you are a beginner like us :) ) to initialize the WindowsUserClass by passing IHttpContextAccessor as a parameter.

HomeController constructor before adding IHttpContextAccessor

After modification

You have to add Microsoft.AspNetCore.Http namespace to the controller also.

Now we should able to call the GetUserName() function from WindowsUserClass from the HomeController! Let us give it a try

We’ll modify the Index view call slightly by passing the “yourusername” string.

We’ll modify “Index” view to show our Windows username now. You can call the view directly referring the GetUserName() function also. That will avoid an additional variable declaration.

return View("Index", windowsUserClass.GetUserName());
@model string
@{
    ViewData["Title"] = "Home Page";
}

<div class="text-center">
    <h1 class="display-4">Welcome @Model</h1>
    <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p>
</div>

Before executing your project, we must change one more existing file!

Open launchSettings.json file and modify the content like below

You must change the windowsAuthentication string value to “true” and anonymousAuthentication to “false”. Save the changes.

You can build the solution and run to debug to check whether the solution is working as per expectations. One of the most important things you MUST understand now, for HttpContext to fetch you the intended results, your website should not be configured for Anonymous authentication. Here is the screen once after you enter the windows username and password when prompted!

You must change the website’s authentication methods prior publishing using IIS. For example

Hope this helps few out there! If the situation permits, I might record a video with instructions soon for the same. Stay tuned folks. For those who cannot open the Visual Studio 2019 solution using their versions of VS, find the page codes below. This way of calling HttpContextAccessor is supported from ASP.NET Core 3.1

Startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace GetWindowsUserName
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllersWithViews();
            //Rajesh Added the below
            services.AddHttpContextAccessor();


        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
                // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
                app.UseHsts();
            }
            app.UseHttpsRedirection();
            app.UseStaticFiles();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllerRoute(
                    name: "default",
                    pattern: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

HomeController.cs

using GetWindowsUserName.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;

namespace GetWindowsUserName.Controllers
{
    public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger;

        WindowsUserClass windowsUserClass = null;
        public HomeController(ILogger<HomeController> logger, IHttpContextAccessor httpContextAccessor)
        {
            _logger = logger;

            windowsUserClass = new WindowsUserClass(httpContextAccessor);
        }

        public IActionResult Index()
        {

            string yourusername = windowsUserClass.GetUserName();

            return View("Index",yourusername);
        }

        public IActionResult Privacy()
        {
            return View();
        }

        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }
    }
}

WindowsUserClass.cs

using Microsoft.AspNetCore.Http;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace GetWindowsUserName.Models
{
    public class WindowsUserClass
    {

        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly string _userName;
        public WindowsUserClass(IHttpContextAccessor httpContextAccessor)
        {
            _httpContextAccessor = httpContextAccessor;
            _userName = httpContextAccessor.HttpContext.User.Identity.Name;
        }

        public string GetUserName()
        {
            return _userName;
        }
    }
}

You may download the sample solution from this link.

Microsoft ODBC Driver 17: A previous installation required a reboot of the machine for changes to take effect.

Today I was trying to install SSMS SQL Server Management Studio 18.9.1 & the installation was stopped with a message “Microsoft ODBC Driver 17: A previous installation required a reboot of the machine for changes to take effect”

Abiding, I went ahead to reboot & instead pressed the shutdown button. After restarting the PC, tried to install SSMS once again and the installer gave up mentioning the same message.

All I needed was “reboot” the machine. Microsoft is sometimes, grr.

Publish nopCommerce using IIS

nopCommerce is one of the free and open-source eCommerce platforms that you can instantly publish and go online with. Please follow the below steps to publish nopCommerce using IIS

Download nopCommerce. If you are not going to develop the suite further, better download package without source code.

Unzip the package.

Based on your choice, you can move this folder to C:\inetpub\wwwroot\. For example, I renamed the folder and current path for nopCommerce is C:\inetpub\wwwroot\nop

You can have this folder anywhere in your computer. Just make sure that you changed the permissions as specified below. Full control is only recommended for development environments.

The most important element of publishing nopCommerce successfully is how you setup the folder access to IIS_IUSRS. Right click on the folder and change the permissions to either Full Control or Read, Write. Failing will fail the nopCommerce suite from launching. You will keep getting “HTTP Error 500.30 – ASP.NET Core app failed to start” until you change the folder permissions.

Now, install DOTNET Core Hosting Bundle. While writing this article, the latest stable version was 5.0.5 and you can download the same from here. Just make sure that you download the correct Hosting Bundle.

Install the bundle, that takes hardly couple of minutes. Once the bundle is installed, restart the IIS service.

Create a database on your SQL Server, exclusively dedicated for nopCommerce. You have to provide these details while setting up nopCommerce for the first time. You may skip creating the database yourself, as the installer offers to create a new database during the installation. However, you must be ready with the connection credentials. nopCommerce supports both integrated security and user/password based security approaches.

Create a new Application pool. Make sure the Application pool has .NET CLR Version set as “No Managed Code”. I can confirm that nopCommerce works with .NET CLR Version set as well. However, all the documents are asking you to setup “No Managed Code”

Finally you can publish nopCommerce. You can either publish nopCommerce as an application with an existing website or create a new website and publish it. For my development machine, I opted to publish it with existing website.

Open your favorite browser and point to http://localhost/nop or http://localhost based on how you published the application & you must be taken the nopCommerce installation. Follow the instructions & I suggest you to select sample items during installation. This will give you an idea of how the items are presented over your portal. You can remove those sample items anytime. Make sure that you will enable the https switch in the application before implementing https for the application with IIS.

That’s all folks.

Microsoft SQL Server | Reduce disk usage

Recently we wanted to shutdown a server for RAID rebuild and a copy of the MS SQL database was restored on my laptop machine. While checking the vitals of the database (3rd party configured), I noticed that the database was using FULL Recovery model and the Transaction logs were consuming 97.6% of the total reserved space. As I didn’t want to waste expensive SSD storage from my laptop, decided to change the recovery model to “Simple”, which doesn’t really release the free space.

MS SQL provides a functionality called shrinking, using that you can shrink both database and transaction log files. I will not recommend you to use shrinking in a production environment, however you can always try “stuffs” on a TEST environment.

Let us see how the shrinking is initiated and how does it change the disk space usage. Before shrinking, make sure that you have changed the database Recovery model to “Simple” from “Full”

Right click on the target database and select “Tasks->Shrink->Files”. Cross verify that you selected “Files”

From the Database Files and filegroups section, for File Type Select Log from the dropdown. Verify rest of the defaults and click “OK”. This will initiate shrinking the Transaction Logs and based on the size of the file(s), this may take a while to finish. You can re-run the reports to verify that disk space usage.

After the shrinking finishes, you will see that all the unused space reserved for the Transactional Logs were released by running the Disk Usage standard report.

Although I asked you not to use shrinking in a production environment, feel free to do it from your local system. Learn what breaks and what makes systems, so that you can take a better decisions while handling production instances.

Oracle Inventory | Aging Analysis

Quite often auditors could come with some strange requirements (especially when they do not understand the business) & we had to formalize on foot couple of years back. From those days, I always wanted to put together few things and post this, which is happening today.

Our requirements were to list the receipts in 7 buckets, ie, within last 120 days, between 121-180 days, beween 181-1 year, between 1-2 Years, between 2-3 Years and 3-5 Years and 5 Years+ (and still in stock!)

Please note, I am using a custom function “omsconcorgqty_f (code provided below, that concatenates the subinventory quantities into a single column. This function could raise an exception when the column length exceeds 4000 characters.

Minimum requirements: Your EBS instance must be using Oracle database 11gR2 to use “LISTAGG” that is used with the custom function. In addition to, I am using PIVOT, that is supported from 11g.

The below example considers a situation where business has a WMS enabled organization. You can pass NULL to :P_WAREHOUSE_ID when not applicable.

If your organization uses multiple UOM for items, you must adjust the below query accordingly, especially for the cost part.

The average cost for the item is picked using the following logic.

If the :P_ORG_ID has item quantities, then the cost for the item will be picked from the same organization. If the :P_ORG_ID organization doesn’t have quantities and warehouse has, then the cost from :P_WAREHOUSE_ID organization will be picked. So there could be inventory value differences and you shouldn’t use the inventory values derived from this exercises if the above said conditions apply.

One of the challenges you are going to face is to identifying the transaction types those should be considered as a receipt. For example, we receive materials in stock through the following transactions.

SELECT a.transaction_type_id
       , a.transaction_type_name
       , a.transaction_source_type_id
       , b.transaction_source_type_name
       , a.transaction_action_id
       , c.meaning
    FROM mtl_transaction_types a
       , mtl_txn_source_types b
       , mfg_lookups c
   WHERE a.transaction_source_type_id = b.transaction_source_type_id
     AND a.transaction_action_id = c.lookup_code
     AND c.lookup_type = 'MTL_TRANSACTION_ACTION'
     AND a.transaction_type_id  IN (12,15,18,40,41,42)
ORDER BY transaction_type_id;

If your organization uses additional transaction types for receiving stock to the stores, please include them to the solution. Here the solution is about scanning the entire MTL_MATERIAL_TRANSACTIONS table & based on the volume of data/hardware resources, it could be a very painful ordeal. My suggestion is to run the query when there are least numbers of “issues” are expected.

I will try to explain the approach step by step, for better understanding.

At the first level we will pick all items for the organization(s) from the MTL_ONHAND_QUANTITIES & use this list for picking up items from the MTL_MATERIAL_TRANSACTIONS.

with ohqty_data as (
select inventory_item_id, sum(transaction_quantity) oh_qty from 
mtl_onhand_quantities 
where 
organization_id in (:p_org_id,:p_warehouse_id)
group by inventory_item_id
)

The above code portion will pick all the items from quantities table that has on-hand quantities.

inv_data as (
select inventory_item_id, qty, age_in_days,
sum(qty) over (partition by inventory_item_id order by age_in_days) running_balance,
sum(qty) over (partition by inventory_item_id order by age_in_days)-qty previous_balance
from (
select inventory_item_id, sum(transaction_quantity) qty, 
age_in_days from (
select inventory_item_id, transaction_quantity,
case 
when (trunc (sysdate) - trunc (mmt.transaction_date)) <= 120 then 1
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 121 and 180 then 2
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 181 and 365 then 3
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 366 and 730 then 4
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 731 and 1095 then 5
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 1096 and 1825 then 6
when (trunc (sysdate) - trunc (mmt.transaction_date)) > 1825 then 7
end age_in_days
from mtl_material_transactions mmt
where 
1=1
and inventory_item_id in (select distinct inventory_item_id from ohqty_data)
and organization_id in (:p_org_id,:p_warehouse_id)
and transaction_type_id in (12,15,18,40,41,42)
)
group by inventory_item_id, age_in_days
)
)

This code portion groups data into multiple age buckets, based on business requirements. We will be using few Analytical functions to determine balance in individual age buckets. The final SQL block will look like below.

with ohqty_data as (
select inventory_item_id, sum(transaction_quantity) oh_qty from 
mtl_onhand_quantities 
where 
organization_id in (:p_org_id,:p_warehouse_id)
group by inventory_item_id
),
inv_data as (
select inventory_item_id, qty, age_in_days,
sum(qty) over (partition by inventory_item_id order by age_in_days) running_balance,
sum(qty) over (partition by inventory_item_id order by age_in_days)-qty previous_balance
from (
select inventory_item_id, sum(transaction_quantity) qty, 
age_in_days from (
select inventory_item_id, transaction_quantity,
case 
when (trunc (sysdate) - trunc (mmt.transaction_date)) <= 120 then 1
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 121 and 180 then 2
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 181 and 365 then 3
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 366 and 730 then 4
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 731 and 1095 then 5
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 1096 and 1825 then 6
when (trunc (sysdate) - trunc (mmt.transaction_date)) > 1825 then 7
end age_in_days
from mtl_material_transactions mmt
where 
1=1
and inventory_item_id in (select distinct inventory_item_id from ohqty_data)
and organization_id in (:p_org_id,:p_warehouse_id)
and transaction_type_id in (12,15,18,40,41,42)
)
group by inventory_item_id, age_in_days
)
)
select inventory_item_id, age_in_days, age_bucket_balance from (
select inventory_item_id, age_in_days,
case when (running_balance <= oh_qty) then qty
else nvl(oh_qty - previous_balance, oh_qty)
end age_bucket_balance from( 
select a.*, b.oh_qty from inv_data a
inner join ohqty_data b on b.inventory_item_id = a.inventory_item_id
order by a.inventory_item_id, a.age_in_days
))
where age_bucket_balance > 0;

Problem & The solution.

Oracle inventory issues the quantities following FIFO method. Say you received 10 pieces of a material in January 2021 and additional 10 pieces of the same item on February 2021. Think of a transaction that issues 8 quantities in March 2021. The balance sheet for the item after the transaction will look like below

The given code block does this calculation! I will find some time in near future for step by step explanation for the logic & solution. I’ve many people to thank for this solution, especially my colleague Sherin Thomas Mathew, Iudith Menzel and our sales team that stood with me through a month long experiments and wrong outputs.

Now let us go to the final solution.

Create a table

 CREATE TABLE OMSINVSTAGE 
   (	INVENTORY_ITEM_ID NUMBER, 
	AGE_IN_DAYS NUMBER, 
	CLOSE_BALANCE NUMBER
   )  ;

Let us minutes change the code block with an insert

INSERT INTO OMSINVSTAGE(INVENTORY_ITEM_ID, AGE_IN_DAYS, CLOSE_BALANCE)
with ohqty_data as (
select inventory_item_id, sum(transaction_quantity) oh_qty from 
mtl_onhand_quantities 
where 
organization_id in (:p_org_id,:p_warehouse_id)
group by inventory_item_id
),
inv_data as (
select inventory_item_id, qty, age_in_days,
sum(qty) over (partition by inventory_item_id order by age_in_days) running_balance,
sum(qty) over (partition by inventory_item_id order by age_in_days)-qty previous_balance
from (
select inventory_item_id, sum(transaction_quantity) qty, 
age_in_days from (
select inventory_item_id, transaction_quantity,
case 
when (trunc (sysdate) - trunc (mmt.transaction_date)) <= 120 then 1
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 121 and 180 then 2
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 181 and 365 then 3
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 366 and 730 then 4
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 731 and 1095 then 5
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 1096 and 1825 then 6
when (trunc (sysdate) - trunc (mmt.transaction_date)) > 1825 then 7
end age_in_days
from mtl_material_transactions mmt
where 
1=1
and inventory_item_id in (select distinct inventory_item_id from ohqty_data)
and organization_id in (:p_org_id,:p_warehouse_id)
and transaction_type_id in (12,15,18,40,41,42)
)
group by inventory_item_id, age_in_days
)
)
select inventory_item_id, age_in_days, age_bucket_balance from (
select inventory_item_id, age_in_days,
case when (running_balance <= oh_qty) then qty
else nvl(oh_qty - previous_balance, oh_qty)
end age_bucket_balance from( 
select a.*, b.oh_qty from inv_data a
inner join ohqty_data b on b.inventory_item_id = a.inventory_item_id
order by a.inventory_item_id, a.age_in_days
))
where age_bucket_balance > 0;

Commit & don’t forget to delete rows before NEXT run. Use a global temporary table for best results.

Once the rows are populated to our table, we can execute the final code block to generate the Ageing report.

WITH DATASET AS(
Select pivot_data.* from (
select 
inv_data.INVENTORY_ITEM_ID,
inv_data.close_balance TYPE_QTY,
inv_data.AGE_IN_DAYS from OMSINVSTAGE inv_data
where 
1=1
--inv_data.inventory_item_id = 27626
and inv_data.close_balance > 0
)
PIVOT (sum(type_qty) FOR AGE_IN_DAYS IN (1 as "120 Days",2 "121-180 Days",3 "181-365 Days",4 "1Yr-2Yr",5 "2Yr-3Yr",6 "3Yr-5Yrs",7 "5Yrs+")) pivot_data)
SELECT msi.concatenated_segments,  msi.description,msi.primary_uom_code,
CASE WHEN nvl((Select sum(transaction_quantity) from mtl_onhand_quantities moq where moq.inventory_item_id = msi.inventory_item_id and moq.organization_id=:P_ORG_ID
group by moq.inventory_item_id,moq.organization_id),0) > 0 then (
Select item_cost from cst_item_costs cic 
where cic.inventory_item_id = msi.inventory_item_id and cic.organization_id = msi.organization_id and cic.cost_type_id = 2)
ELSE
(Select item_cost from cst_item_costs cic 
where cic.inventory_item_id = msi.inventory_item_id and cic.organization_id = :P_WAREHOUSE_ID and cic.cost_type_id = 2)
END item_cost,
dataset.*,
OMSCONCORGQTY_f(:P_ORG_ID, :P_WAREHOUSE_ID, dataset.inventory_item_id, msi.primary_uom_code) org_quantity FROM DATASET
INNER JOIN mtl_system_items_kfv MSI ON MSI.INVENTORY_ITEM_ID = DATASET.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID=:P_ORG_ID
ORDER BY  msi.concatenated_segments;

omsconcorgqty_f function

This function adds (W) before the subinventories from :P_WAREHOUSE_ID organization for easier identification.

CREATE OR REPLACE FUNCTION omsconcorgqty_f (
    p_org_id        IN  NUMBER,
    p_warehouse_id  IN  NUMBER,
    p_item_id       IN  NUMBER,
    p_uom           IN  VARCHAR2
) RETURN VARCHAR2 IS
    l_qty_string VARCHAR2(4000);
BEGIN
    SELECT
        LISTAGG(subqty, ',') WITHIN GROUP(
            ORDER BY
                subqty
        )
    INTO l_qty_string
    FROM
        (
            SELECT
                inventory_item_id,
                CASE
                    WHEN organization_id = p_warehouse_id THEN
                        '(W)'
                        || subinventory_code
                        || '('
                        || to_char(SUM(transaction_quantity))
                        || ')'
                    ELSE
                        subinventory_code
                        || '('
                        || to_char(SUM(transaction_quantity))
                        || ')'
                END subqty
            FROM
                mtl_onhand_quantities_detail moq
            WHERE
                    moq.inventory_item_id = p_item_id
                AND moq.transaction_uom_code = p_uom
                AND organization_id IN ( p_org_id, p_warehouse_id )
            GROUP BY
                inventory_item_id,
                subinventory_code,
                organization_id
        )
    GROUP BY
        inventory_item_id;

    RETURN l_qty_string;
END;

Hope this helps

Windows | Upgrade Oracle Database 12c to 19c

Our business application is hosted on Linux. I love the way the OS is fine tuned by Oracle for the database & without arguments, I must say, Oracle is perfect on Linux.

Does that stop me from installing the latest Oracle database & experimenting them on Windows? Nay, from the known days I’ve used Windows OS for PC & I am sure, will be retiring using one. I’ve made many attempts to install and configure many unsupported Oracle products on Windows & have a VERY good success record ;)

Yet, I never ever upgraded Oracle databases on Windows to next version as I never had a database that needed to be backed up or restored when a new version was tried out. This time I decided to change that habit and to upgrade my existing 12c CDB database to 19c following some documents specifically written for Linux!

Well, regardless whether you are a Linux or Windows person, trust me, the instructions provided for either platform is 99% applicable on both. So, translating the instructions on Linux for Windows are NOT truly difficult, in case if you are IN for some geeky stuffs.

So let us quickly assess my environment. I had 12c CDB with one PDB, which I don’t remember whether I created during the software installation or later. As usual, the following instructions are for a lab setup. That means, you must make sure that you make proper backups prior moving them to a Production environment. My TEST database didn’t have any data, just the sample schemas.

Install Oracle Database 19c. Choose “Software Only” option. That means do not create a database along with the installation. You may refer my earlier post for Oracle 19c installation here.

Now, let us see how to upgrade our 12c database to 19c. There are many prerequisites for a successful upgrade. Oracle provides many tools to insure a successful upgrade.

Start your 12c database and open an elevated command prompt.

From your 12c database home aka %ORACLE_HOME%\bin folder execute prerequisites collection program. Example

D:\Oracle\product\12.2.0\dbhome_1\bin>java -jar D:\Oracle\product\19.3.0\rdbms\admin\preupgrade.jar TEXT TERMINAL

TEXT TERMINAL will force the program to output all the instructions to the current console. You should copy the output to a text file & go through the findings. Against the pre-upgrade instructions, you must satisfy each one of them & re-run the prerequisites collection once again. If you are upgrading a Container database with pluggable databases, the post-upgrade activities will be listed per database.

A Sample output as below:

D:\Oracle\product\12.2.0\dbhome_1\bin>java -jar D:\Oracle\product\19.3.0\rdbms\admin\preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-20T17:45:58

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CDB12C
     Container Name:  CDB$ROOT
       Container ID:  1
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Microsoft Windows x86 64-bit
      Timezone File:  26
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  3.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             470 MB       500 MB
      SYSTEM                             800 MB       915 MB
      TEMP                                33 MB       150 MB

      Minimum tablespace sizes for upgrade are estimates.

  4.  No action needed.

      Using default parallel upgrade options, this CDB with 2 PDBs will first
      upgrade the CDB$ROOT, and then upgrade at most 2 PDBs at a time using 2
      parallel processes per PDB.

      The number of PDBs upgraded in parallel and the number of parallel
      processes per PDB can be adjusted as described in Database Upgrade Guide.

  5.  Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container CDB$ROOT
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  6.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  7.  To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.

      Some directory object path names may currently contain symbolic links.

      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.

  8.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  9.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container CDB$ROOT
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/postupgrade_fixups.sql


Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-20T17:46:22

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CDB12C
     Container Name:  PDB$SEED
       Container ID:  2
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Microsoft Windows x86 64-bit
      Timezone File:  26
  Database log mode:  NOARCHIVELOG
           Readonly:  TRUE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  3.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             330 MB       500 MB
      SYSTEM                             250 MB       358 MB
      TEMP                                64 MB       150 MB
      UNDOTBS1                           100 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container PDB$SEED
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  4.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  5.  To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.

      Some directory object path names may currently contain symbolic links.

      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.

  6.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  7.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container PDB$SEED
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/postupgrade_fixups.sql


Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-20T17:46:22

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CDB12C
     Container Name:  SCT
       Container ID:  3
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Microsoft Windows x86 64-bit
      Timezone File:  26
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  3.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             350 MB       500 MB
      SYSTEM                             250 MB       358 MB
      TEMP                                64 MB       150 MB
      UNDOTBS1                           100 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container SCT
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  4.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  5.  To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.

      Some directory object path names may currently contain symbolic links.

      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.

  6.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  7.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container SCT
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
  D:\Oracle\cfgtoollogs\cdb12c\preupgrade\preupgrade.log
  D:\Oracle\cfgtoollogs\cdb12c\preupgrade\preupgrade_fixups.sql
  D:\Oracle\cfgtoollogs\cdb12c\preupgrade\postupgrade_fixups.sql

Execute fixup scripts across the entire CDB:

Before upgrade:

1. Execute preupgrade fixups with the below command
%ORACLE_HOME%\perl\bin\perl -I%ORACLE_HOME%\perl\lib -I%ORACLE_HOME%\rdbms\admin %ORACLE_HOME%\rdbms\admin\catcon.pl -l D:\Oracle\cfgtoollogs\cdb12c\preupgrade\ -b preup_cdb12c D:\Oracle\cfgtoollogs\cdb12c\preupgrade\preupgrade_fixups.sql

2. Review logs under D:\Oracle\cfgtoollogs\cdb12c\preupgrade\

After the upgrade:

1. Execute postupgrade fixups with the below command
%ORACLE_HOME%\perl\bin\perl -I%ORACLE_HOME%\perl\lib -I%ORACLE_HOME%\rdbms\admin %ORACLE_HOME%\rdbms\admin\catcon.pl -l D:\Oracle\cfgtoollogs\cdb12c\preupgrade\ -b postup_cdb12c D:\Oracle\cfgtoollogs\cdb12c\preupgrade\postupgrade_fixups.sql

2. Review logs under D:\Oracle\cfgtoollogs\cdb12c\preupgrade\

Preupgrade complete: 2020-11-20T17:46:32

D:\Oracle\product\12.2.0\dbhome_1\bin>

Once all the prerequisites are met, we can use the GUI tool “Database Upgrade Assistant” to upgrade our existing database(s). For this exercise, I will be upgrading my 12c Container database with one PDB

Select the 12c database that you want to upgrade. Please note, my database hardly had any data & was not on archive log mode. If you are planning to upgrade a production database that has archive log enabled, take proper backups prior proceeding.

I’ve an exclusive user account “oracle” for all Oracle installations. If you have used another user account, provide it at this step.

As my database doesn’t have archive logging, my best choice is to create an Offline RMAN backup. You may choose another option from the available.

My 12c database had a LISTENER that is listening to 1521 & by mistake I have selected a PORT 5500. Please adjust your PORT number.

Above I said how I wrongly selected a PORT number for LISTENER, I’ve selected the same PORT number for the Enterprise Manager also. Well, later I corrected the mistake. So be careful while you select PORT numbers.

Sit back, watch a movie or go for a walk. The following activities are going to take considerable time. Bigger your database, more time needed to complete the upgrade. I’ve pretty a beast as desktop and whole upgrade took almost couple of hours. Based on your available resources, the upgrade might take longer times.

Okay, I missed taking the last Screenshot. Once the upgrade finished, you can start with post-upgrade activities as they are listed with the prerequisites output.

That’s all folks. Have comments? Please send them to me.

Install Oracle Database 19c on Windows 10

This time let us see how to install Oracle database 19c on Windows 10. This installation is not much different from Oracle 18c installation.

You can download the 19c Installation media for Windows from the below link.

Database Software Downloads | Oracle

Once downloaded, you have to extract the package to a folder. Please note, the extracted folder or the folder from which you will execute “Setup.exe” is going to be 19c Oracle Home. So, name the folder wisely. For example, I have extracted the files to a folder D:\Oracle\19c

You can start the installation by right clicking the Setup.exe and running as “Administrator”.

This will kick start the installation.

We will not create a database along with the software installation. Hence select “Set Up Software Only”

I suggest you to create a normal user (no administrator privileges) “oracle”, that will be used for all your future Oracle software installations. If you don’t have an account explicitly created yet for Oracle, the installation gives you an interface to create one. It’s your choice.

Please remember, earlier I said, the folder where you have extracted the installation media is going to be Oracle 19c home, aka Oracle Home. Rename the folder with an appropriate name before going ahead with the installation.

You will be asked to provide a location for Oracle Base. To content within the Oracle Home, you can specify a path like “D:\Oracle\19c\database” or “D:\Oracle\19c\db” or a folder name of your choice.

After few basic checks, the installation will proceed now. I strongly suggest you to assign a STATIC IP for your computer, prior continuing.

That’s it. No hacks, no modifications to config files or anything such to get the software installed on Windows!.

You can use “Database Configuration Assistant” for creating new database. We will see how to create a new database now. This time we will be creating a CDB (Container Database) and one PDB (Pluggable Database). You may go ahead with creating a single instance database when asked to choose between.

Select “Advanced Configuration”

You can choose between a normal database or Container database with PDB at next step. If you don’t wish to create the database as a container, uncheck the option “Create as Container database”. If you chose to continue with creating your database as container, I suggest you to leave the defaults as it is, other than the PDB name. If you need more PDBs, may add later to the container.

Leave the defaults, unless you know what you are doing.

If you are setting up the database for the LAB/Learning purposes, you may ignore these. However, for production instances, you must configure the Recovery area & setup archiving.

You can create a LISTENER for your database now. If you other Oracle database LISTENERs already running, choose a port number that wouldn’t raise a conflict.

Optionally you can setup Database Vault & Label Security at this step ;)

Okay, we need your attention here. Oracle automatically allocates 40% of total available physical memory for the newly created database. As I mentioned in the previous step, if you have more than one database running from the same computer, you must adjust the available memory to avoid choking the resources.

You may leave the defaults untouched here & you can change this value as sys admin when needed.

WebLogic Repos insist upon “AL32UTF8” Character Set & unless you have specific reasons to change the Character set, leave the defaults. If you are planning to migrate a legacy application that uses a different character set like Arabic (AR8MSWIN1256), please read this post

As per the post, you can use a different character set for a PDB starting from Oracle database versions 12.2.

We do love Scott bit too much, if yes, go with sample schemas (Trust me, you need it)

Enterprise Manager configuration is totally optional. If you choose to, will install an Express edition of Oracle Enterprise Manager (means more resources)

As usual, for a LAB environment I will go with same password for all accounts.

I would suggest you to accept the defaults at next step & change the passwords and unlock accounts as and when needed.

That should complete the installation and creation of database for 19c. Have comments? Please post them to me.