Oracle Database 19c | Convert non CDB Database to PDB

We’ve our Oracle EBS instance hosted from Linux environment. However I post much for the Windows OS as I see there are not many articles targeting Oracle on Windows. I think for better view counts, I should consider posting the articles for both OS.

In my last post about restoring 1g RMAN online backup to 19c non CDB database, I said my ultimate goal was to convert the upgraded database from non CDB to PDB, that is plugged on to an existing CDB. Let us see how to achieve this goal this time.

Before start, there are few mandatory checks you must do to insure a successful migration from Non CDB to PDB. I will list few of them those came as hurdles for me.

  1. Character Set. Make sure that your CDB and the Non CDB database have the same character set. There are few possible conversions. Please check 12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1) for more details. My CDB was using Arabic character set and the upgraded non CDB database was using a noncompatible set. So I dropped the CDB and created a new CDB without PDB(s). Dropping a CDB will not be always possible because other PDBs are attached to it.
  2. Available storage space. This could be a real concern incase if you are planning to create a PDB, cross verify & then delete the non CDB database. You will need approximately the same size of your non CDB datafiles size storage available to do this activity. My database was less than 5 GB in size, hence it never occurred to me. If your database has a bigger size, plan accordingly.

Let’s do it now.

From and elevated command prompt (Windows Terminal is not yet matured enough to handle such “heavy” tasks) source the non CDB. Shutdown and startup mount exclusive.

C:\Windows\system32>set ORACLE_SID=KAZEMA
C:\Windows\system32>sqlplus / as sysdba
SQL> shu immediate
SQL> startup mount exclusive
SQL> alter database open read only;

Create manifest file for the non CDB now.

SQL> exec dbms_pdb.describe (pdb_descr_file=>'D:\Oracle19c\kazema_manifest_file.xml');
SQL> shu immediate

Source the CDB now.

C:\Windows\system32>set ORACLE_SID=KAZEMACDB
C:\Windows\system32>sqlplus / as sysdba
SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT

Now, we need to see whether non CDB meets the compatibility requirements. There could be multiple elements those you may have to fix before, the non CDB is ready for a successful migration as PDB. You will have to refer PDB_PLUG_IN_VIOLATIONS view for these details. Go ahead, execute the below code block.

SQL>   SET SERVEROUTPUT ON;
SQL>    DECLARE
       Compatible CONSTANT VARCHAR2(3) :=CASE  DBMS_PDB.CHECK_PLUG_COMPATIBILITY
       (pdb_descr_file => 'D:\Oracle19c\kazema_manifest_file.xml')
       WHEN TRUE THEN 'YES'
       ELSE 'NO'
       END;
       BEGIN
          DBMS_OUTPUT.PUT_LINE(compatible);
       END;
     /
YES

My case I didn’t have incompatibilities blocking possible migration. Your case could be different. If the output is “NO”, run the following query (use SQL Developer for better view)

select * from PDB_PLUG_IN_VIOLATIONS where name='<your non CDB name>';

Fix each and every other ERROR & you can safely ignore the “WARNINGS” for now.

Proceed with creating a new PDB from the manifest file that we have created in the 1st step.

SQL> CREATE PLUGGABLE DATABASE KAZEMA USING 'D:\Oracle19c\kazema_manifest_file.xml' COPY FILE_NAME_CONVERT=('D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE','D:\ORACLE19C\DB\ORADATA\KAZEMA');

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 KAZEMA                         MOUNTED

Once the PDB has been created, confirm the same & proceed with balance activities. Run “noncdb_to_pdb.sql”

SQL> alter session set container=KAZEMA;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Based on the resource available, this might take a while for the script to complete execution. Once completed you can try to open the PDB. Check for Errors and Warnings in the PDB_PLUG_IN_VIOLATIONS view. If there are new ERRORS & WARNINGS fix them.

A typical scenario could list you many things like this (reference image only)

After fix attempts, I am still left with one Warning about sys lock issues. This is mainly due to the password file format 12.2. I hope to find a solution soon for it. Status column states whether the Error/Warning was taken care off.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
KAZEMA
READ WRITE


1 row selected.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 KAZEMA                         READ WRITE NO
SQL>

Now you have a non CDB and PDB with the same SID. You can proceed with dropping the non CDB database after cross verifying the concerns.

Reference documents:

Foot Note: If you are plugging a database that was hosted in another server/computer after converting, make sure to verify the “local_listener” parameter for the PDB exist.

D:\Oracle19c>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 10 11:18:33 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_KAZEMACDB
SQL> alter session set container="KAZEMA";

Session altered.

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_KAZEMACDB
SQL>

If the local listener is not reachable, PDB level services will fail to register with listener and the applications those depend on such services will fail to establish connections. This applies mostly to Oracle E-Business Suite environments and could be mapped to sophisticated applications that use specific service names to establish connections to the database.

KB5009543 | Jan 2022 cumulative update breaks IKEv2 VPN Connections

KB5009543 or Cumulative update Jan 2022 for Windows 10 and Windows 11 is currently breaking IKEv2 VPN connections. If you are experiencing an unexpected connection failure with a vague error message like “General processing error”, please follow the below steps.

Go to Program and Features -> Installed Updates and uninstall KB5009543. Reboot the box and the VPN connection should start working normally. Optionally you may disable the Windows Update for a period of time so that the same update will not get installed once again. Please note, Microsoft releases updates to their products quite often to resolve issues those were identified. Few times these updates open new issues, like the one happened with this particular KB.

While there are no straight forward methods available to “stop” Windows updates, you can use multiple methods to disable Windows update completely, which we don’t recommend. One of them is using group policies on the local machine.

If you still consider disabling Windows updates temporarily/long term, please refer this excellent tutorial

Hope this helps.

Connecting Oracle Developer 10g to 11G database takes long time

We migrated to 11G R2 (11.2.0.4) for our Oracle Applications R12 few years back, yes few years back (2017) & lived with one of the worst experiences…

Connecting Oracle Developer 10g (Forms/Reports) suite to 11G database.

I have scavenged through community articles for long time before giving up. I hardly came across a single fix for the connection time that used to hang up the Developer suite at times…

Today, I decided to find a solution for the nagging SSH connection issues from Windows 11 to our LINUX application servers and realized that we didn’t update the DNS settings for them once after we decommissioned a domain controller. Once the SSH issues were rectified and addressed, my next attempt was to find a solution for “frmcmp_batch” taking long time to start compiling modules & I landed on the below post.

Credit: Oracle Applications DBA: Form Compilation Against a 11g Database Hangs or Takes a Very Long Time [ID 880660.1] (appsjagan.blogspot.com)

As we are already on 11G R2 11.2.0.4, patching was not required. All I needed was to alter the hidden parameter “_FIX_CONTROL” as mentioned in the article.

SQL> ALTER SYSTEM SET "_FIX_CONTROL"='8560951:ON';

(Use scope=spfile to make this change permanent. This will require you to restart the database.)

I opted to go without spfile for testing & as soon as applied, the “frmcmp_batch” started compiling the modules instantly, against the usual delay that ran into many minutes other times.

Out of curiosity, I tried to connect to the database from Developer 10g & the connection was instant! within a fraction of a second.

So DNS being one of the most important elements establishing successful connections, patches and fixes also play crucial role in providing stable connections. Were you stuck with the same issue? give the solution a try and let us know whether it helped you also.

Install Oracle Developer 10G on Windows 11

Today I re-installed Windows 11 on my computer. Actually I had upgraded from Windows 10 to Windows 11 on October 5th, 2021 the same day Microsoft released the half baked OS to public & all of a sudden I realized that, it was a blunder. So, after regretting over a month, I decided to re-install the OS and started installing all my development tools & you know what, I had to refer my Windows 7 post for installing Oracle Developer Suite 10g on my Windows 11!

Much of the post below is from the earliest post that I had for Windows 7, explaining hacks to install Oracle Database 10g and the Suite. Oracle Database 10g is not anymore significant, hence I removed the database part and posting the rest for few unfortunate Oracle developers out there. Well, 10g is used by many business application as on date & if you are looking for a method to install Oracle Database 10g on Windows 11, Please search the blog for Database 10g and you will land on multiple posts explaining to how (Refer Windows 10 articles)

Step 1 Setup Virtual Memory

If your computer has 8GB or more physical memory, all you need is to setup the Virtual Memory for the name sake. Limit the virtual memory to 2048 (2G) or max 4096 (4G). Please use the below images only for reference purposes. After changing the Virtual Memory, You will be asked to restart the computer and please restart.

Step 2 Setting up the Developer Suite

Depending upon the media that you are using for the installation, Oracle Developer 10g has 2 discs & I have copied the content from both disks inside a folder on my computer. (As you could see in the image above)

Open the Disk 1 folder and locate “Setup.exe’, right click and change the compatibility to Windows XP Service Pack 2 or 3 (doesn’t make any difference)

That’s all. Right click and run “setup.exe” as Administrator. If you have a JAVA installation already on the computer, you might receive a warning message for missing entry point, that you can safely ignore.

Follow the same compatibility setting for patch sets, if you have any. Let me know about your experience through comments.

Windows | Search & WIFI cannot type

There are situations, especially while using Windows 10 gen OS (Windows 2016+ Servers included) a user might not able to type inside

  • Windows Search Textbox
  • WIFI Password Textbox

For some strange reasons, typing inside these text boxes are supported by “CTF Loader”(C:\Windows\System32\ctfmon.exe)” & that is loaded into computer’s memory normally after a restart/reboot.

This is facilitated by one of the Windows Tasks & certain attempts like using KMSPICO tool to activate Microsoft software, which is ILLEGAL and many times breaks this Task.

So, how do we fix it? There are two different approaches

  • Rebuild the Task
  • Using Registry

We’ll see both methods in detail now.

1# Rebuild the Task

If the KMSpico tool or other reason corrupts the Task, you will not find any entries in the folder “TextServicesFramework”

Edited image for demo.

when an intact task should look like below

So how do we recreate this task, that is a custom handler?

Browse to “C:\WINDOWS\System32\Tasks\Microsoft\Windows\TextServicesFramework” & check whether you can find a file “MsCtfMonitor” inside the folder. If you cannot find the file in this folder, copy the file from another Windows machine that has the same version.

Copy the file to desktop and add the extension “.xml” to it.

Opening the file with notepad or another text editor should look like the following:

Go back to Task Scheduler and right click on “TextServiceFramework” and select “Import Task”

Point to the xml file that you have created in the desktop & apply. This should create a new task under the folder “TextServiceFramework”

Restart and confirm you can type in the search box and WIFI password text boxes. Alternatively you can use the Task Manager to check whether “CTF Loader” has been loaded.

2# Registry

Remember, registry entries might get removed through updates/new versions. However, as a temporary measure you can use the following hack to address the keyboard issues.

Make a full backup for the Registry. You must know, tampering registry could force you to a fresh installation of the OS!

Once a full backup for the Registry is made, browse to “Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run”

Create a new string key and call it anything that you want. Example

I have called the string key as “CTFLoader” and for the Value make sure to enter “ctfmon”=”CTFMON.EXE”

Reboot the computer and check whether the typing issues resolved. Alternatively use Task Manager to check whether CTF Loader has been loaded.

Although both fix the typing issues, the right way is to recreate the missing Task. Choose wisely & don’t pirate! We know many interesting software are pricely and many of us cannot afford them. There are free alternatives for maximum pirated software like

And many more software that you need for Office/School/Home works! Piracy not only breaks the businesses that develop them, helps cyber criminals to unleash attacks like Ransomware through serial number generators and patches.

Hope this has helped you today.

Windows 10 | Windows cannot connect to the printer

There are times when you come across the error “Windows cannot connect to the printer” (error 0x000003e3) while trying to connect to a shared printer that is connected to another Windows 10 computer.

Quite possible that the drivers installed on the source machine is either not compatible with the client/target machine or not getting copied to the target machine. Now, install the correct version of printer driver to the target machine and try to connect again. There could be few other items you must check and insure for a successful connection.

  • Make sure both the computers network property shows the connection as either domain or private.
  • Make sure you have File & Printer sharing enabled for the network

That’s all folks

Oracle Application R12 | Using Microsoft Edge Chrome for versions 11 & 12.0.xx

Updated on 8th October 2021 for Microsoft Edge Version 94.0.x.x & for Windows 11 that comes without Internet Explorer (or it doesn’t allow you to start Internet Explorer)

A much easier method is available with versions later than the one I used earlier.

Step# 1. Go to Edge settings (type edge://settings in the address bar and select “Default Browser”

Change “Allow sites to be reloaded in the Internet Explorer mode” to “Allow”, that will require you to restart the browser. Please restart

Step# 2. Now enter the address of your Oracle Application in the address bar and wait for the login page to load. Once loaded, click on the “three dots” menu by the extreme right of the browser and select “Reload in Internet Explorer mode”

This will open another dialog box as seen with the above image. Toggle “Open this page in Internet Explorer mode next time” and click “Done” button, that will produce more notifications like the one you could see with the below image.

It asks you to leave the Internet Explorer mode and another button “Show in toolbar”. This is an interesting button, clicking that starts showing a small tool button in the tools area and the button will reload the current page in “Internet Explorer mode”.

That’s all. Just click the toolbar button and your Oracle Application login page will be loaded in “Internet Explorer mode” immediately.

Toggling the switch to “Open this page in Internet Explorer mode next time” in the “Step# 2” adds an entry to “Internet Explorer mode pages” area for your current page and the entry is valid for next 30 days. Microsoft is expecting you to modernize your “page” within those next 30 days ;). Once this entry expires, you must get the “Open this page in Internet Explorer mode next time” dialog box once again.

October 8, 2021 update ends here.

After 25 years ever since it was launched, Windows 11 will be the first OS that is not shipping with Internet Explorer.

How does this matter to Businesses those use Oracle Application versions 11 & 12.0.xx? Well, currently Internet Explorer is the only one browser that allows to load Oracle JRE (NPAPI client) for Oracle Forms, on which much of the Oracle module rely.

Oracle Applications has patched the latest releases with a technology called JWS (Java Web Start), that let’s the users to download a jnlp file from the application and Oracle Java Run Time to start in desktop mode (without being loaded in a browser session) loading Oracle Forms. Well, this enhancement is not available for Application versions 11 & 12.0.xx

Microsoft was expecting a huge cry from the Businesses that use Oracle Applications/legacy implementations those cost millions of dollars and fine tuned for Internet Explorer. Cutting these businesses could mean loads for Microsoft, So they have integrated “IE Mode” into their chromium based “Edge browser”, which is the default browser on Windows 11. IE Mode makes Edge Chrome to “act” as if it were Internet Explorer for legacy Web based applications & loads NPAPI clients like JRE.

Today let us see how to configure Microsoft Edge Chrome for Oracle Applications.

Start Microsoft Edge Chrome & open “Settings”

Click on Default Browser & spend a moment to check currently set options.

Let us change few of those settings like shown below.

Let Internet Explorer open sites in Microsoft Edge -> Change to Allow

Allow sites to be reloaded in Internet Explorer mode ->Change to “Allow”. This will require you to restart the browser.

You must add the sites those you want to open in IE mode by clicking “Internet Explorer mode pages” Add button. Such pages will have maximum 30 days validity. Without, JRE will not load and will prompt you to download it from the default location.

Now, let us click the restart button & give it a try.

That’s all folks.

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.

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.