RMAN on Windows | Backup & Restore 11g R2 database

11g R2? too late to post anything for 11g? Wait, there are thousands of Businesses still using Oracle database 10g. So let us see how RMAN could be simplified on Windows. I hardly see many articles explaining such for Windows platform in general.

Considering you have 11g database already installed & setup for Archive logging, we’ll start by setting up RMAN backup first, then try a restore routine on a different sever.

Like cron jobs on Linux environment, for Windows we will use Task Scheduler for RMAN backups. Here are couple of batch files we will be using for making RMAN backup on Windows.

run
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
crosscheck archivelog all;
crosscheck backup;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'D:\RMAN_BACKUP\PROD_comp_%d_ lvl0_%U' TAG "dailyfull_db_lvl0_bkp" INCLUDE CURRENT CONTROLFILE;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT 'D:\RMAN_BACKUP\archive_%d_lvl0_%U';
DELETE NOPROMPT archivelog all completed before 'sysdate-7';
backup current controlfile format 'D:\RMAN_BACKUP\bkpcontrol_file.ctl_%d_%T'               ;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}

Save this script as “rman_backup.cmd”. You can save it with any other name ending with .cmd or .bat extension. Make sure you change the name in the next script incase if you choose a name other than “rman_backup.cmd”. Please note, I am taking backup on “D:\RMAN_BACKUP” folder, you should change “D:\RMAN_BACKUP” with the exact location that is used for the backup!

Now, we will create another cmd file that will call the above script.

@echo off
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ('time /t') do (set mytime=%%a%%b)
:: echo %mydate%_%mytime%

set filename="D:\RMAN_BACKUP\logs\%mydate%_%mytime%_rman_backup.log"
:: echo %filename%

rman target / nocatalog cmdfile='D:\scripts\rman_backup.cmd' log='%filename%'

Once again you can save this file with anytime, provided the extension is either .cmd or .bat. I saved this cmd file as “callrman.cmd”

Make sure the path ” D:\RMAN_BACKUP\logs” or equivalent exists prior running the scripts.

Now fire up Windows Task scheduler and create a basic job

That’s all. Everyday, at a said time, RMAN backups will be created and kept in the destination folder. We are using 7 days retention policy with the script file, so on the 8th day obsolete backups will be purged. Depending upon the disk space available, you should adjust the retention policy for your backups. Don’t forget to copy those backup pieces to an external medium to insure maximum availability during a crisis.

That completes the simplest form of Oracle RMAN backups on Windows platform.

RMAN Restore

Currently we are discussing about restoring the RMAN backup to same version of Oracle database. If you want to restore RMAN backup to a higher version of Oracle database, it will be an entirely different exercise.

RMAN backups are the fastest, easiest incase if you are doing it right. No hassles like creating tablespaces, schemas and then waiting hours for the import process to complete! The biggest size of the database I ever dealt with is 550GB and a server with 2×4 core processors, 16GB total memory restores the 140GB backup in less than 1.5 hours. I suggest you to start using RMAN backups the soonest possible regardless whether your database is just few megabytes in size.

Today we will see how to restore RMAN backup from Server A to Server B, ie, to a different physical server with a different database name.

It might sound bit unethical to ask you to create a new database using Oracle database creation wizard for this purpose, however this is what we will do for this exercise. This will save us some time figuring out setting up many other parameters for the new database at later stages.

So let us start.

Start DBCA and create a database with a database name that you prefer for the purpose, for eg: “ORCL”

Once the database created, shut it down & delete all the datafiles from the data store. For example, I had all my data files in the folder C:\oracle\oradata\orcl, where orcl is the database name!

Now copy the RMAN backups from Server A to Server B. I’ve copied only one day backups from Server A to Server B and make a note of the exact location where you copied the backup files. In my case, I copied the files to “C:\RMAN”

From an elevated command prompt start SQLPLUS as sysdba & start the database in non mounted mode.

Now, start another elevated command prompt and connect to RMAN in Auxiliary mode, for duplication of the database.

We are all set to restore the RMAN backup to Server B now. However, there are few things we have to insure to make a successful restoration. For example renaming the datafiles. Based on the new datastore location where you are restoring the database files, each data file should be renamed. Please refer the below sample script for the same.

run
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE disk;
duplicate database to "ORCL" backup location 'C:\RMAN' nofilenamecheck
db_file_name_convert=('D:\Oracle\oradata\PROD','C:\Oracle\oradata\orcl')
LOGFILE
GROUP 1 (
'C:\Oracle\oradata\orcl\redo01a.log',
'C:\Oracle\oradata\orcl\redo01b.log'
) SIZE 1000M ,
GROUP 2 (
'C:\Oracle\oradata\orcl\redo02a.log',
'C:\Oracle\oradata\orcl\redo02b.log'
) SIZE 1000M ,
GROUP 3 (
'C:\Oracle\oradata\orcl\redo03a.log',
'C:\Oracle\oradata\orcl\redo03b.log'
) SIZE 1000M ,
GROUP 4 (
'C:\Oracle\oradata\orcl\redo04a.log',
'C:\Oracle\oradata\orcl\redo04b.log'
) SIZE 1000M ;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}

As I am duplicating the database to existing one, I don’t have to go through the tedious process of changing the dbid or database name etc. Once the restore is done, all I need is to disable the archive logging and go online! We will see those in next few steps.

Please give attention to “db_file_name_convert“, Server A had the datafiles in the path “D:\oracle\oradata\PROD” and the Server B, we will be restoring the datafile to C:\oracle\ordata\orcl folder. So we must mention those changes here in the script. The same way we need to rename the logfiles and locations also. I hope the above script is self explanatory on that regard.

You may not need all those channel allocation for a small database, please adjust the number of cannels allocated as per your requirements. Give a try with less or more number of channels incase if you want to learn what happens :)

Execute the above script from the RMAN prompt.

If there are no errors, based on the size of the database & hardware resources RMAN will return to the prompt once the activities are finished.

Before doing anything else, we need to insure that the Archive logging is disabled for the newly restored database. Rush to the previous SQLPlus session that you started with nomount. This session is already disconnected, hence reconnect and shutdown immediate.

Now start the database in mount state and disable the archive logging.

Shutdown the database and restart normally.

That’s all, your Server B “orcl” has all the data from Server A “PROD” database now. If this is a production recovery, you can change the database name using “NID” utility, that is supported from Oracle database 9i onwards. Hope this helps few Oracle database beginners out there.

As we have restored full database with archive logs, there is no need to restore logs. Cross verify the database details

Cheers guys!

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

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.

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.

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.

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.

Oracle Forms & Reports 12c | Publishing a sample application

After a long break, I am going to post about deploying Oracle Forms & Reports based application using Weblogic Server 12c (12.2.1.4).

With my previous posts about Weblogic 12c & Oracle Forms & Reports, I have provided most of the details about configuring the Forms and Reports environments for deploying Forms & Reports based application(S). Well, for a beginner this could post a challenge.

So the post is for beginners and those trying to setup an environment using Weblogic Server for the first time. Please note, you must adjust the paths listed below according to your installation/naming conversions.

To start with, I always install Weblogic Server & Oracle Forms & Reports software on the same HOME. Example: D:\Weblogic\Middleware\Oracle_Home

The advantage of following the same name throughout installations (of same version) is, I can copy the fine tuned configuration files from an existing installation and later use them when I am forced to remove and reconfigure the complete infrastructure.

To host Oracle forms and reports based applications, we need to make few changes with existing configuration files for both Forms and Reports. Prior making those changes, let us run a quick errand for mandatory elements for successful deployment of applications.

HOST Name (aka your computer name). Make sure you have a meaningful name for your computer. If you have installed and configured Weblogic and other software components against a name that looks like a joke, then redo the installation and configurations against a new computer name (eg: RAJESH-PC, JOHN, MARTHA, ORASERVER or anything that is short and could easily be identified). Refer documents those explain you how to change your computer name.

You must have a STATIC IP address for your computer that is going to host your Forms & Reports application. If your computer is part of domain network & supported by a DNS server, you might take a risk of not having STATIC IP, however, please note, Oracle insists for a STATIC IP for the box on which you install database & other server software. Usually at home/small networks, you are connected to a wireless router/router or a computer that issues dynamic IP addresses. If you are at home, you may assign a IP address outside of DHCP scope for you computer. Please google for more details to know how to setup STATIC IP address. If you are new to these basic networking elements, consult someone who could help you.

HOST File modifications. As we are talking about Windows environments, regardless whether client or Server OS, you will find the HOST file under:

C:\Windows\System32\drivers\etc

You can use Notepad to open up this file and add an entry like below. If you are on Windows 7 or later, make sure that you are opening Notepad as Administrator (The whole exercises you have read until were only tested on Windows 10):

Where 192.168.1.65 is the STATIC IP address assigned to my computer, erp-prod.my.home is the fully qualified domain name(FQDN) and erp-prod is the hostname.

At home you don’t need the FQDN, unless you have a DNS configured or you fancy a computer name like “mycomputer.mydomain.com” instead of just “mycomputer”

The above were the least amount of network related settings those you insure to make sure that your application can be accessed from your computer (and your network). Please note, you have to open port 7777 (default OHS) through the firewall so that other computers on the network can access your application. For local testing, you may skip this.

So, I guess we are good with the above mentioned and will move to setting up “stuffs” for our first application. I already have an application that was developed using 11g Forms & Reports and will be using the same for the rest of explanations.

Setup TNS Names. If you are not using environment variable TNS_ADMIN, you should set up the TNS names exclusively for your application deployment.

So, I said I always install Weblogic and other software components in the same place “D:\Weblogic\Middleware\Oracle_Home”. After configuring for a classic domain for Forms & Reports, I can find the tnsnames.ora file under the path:

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig

Where “base_domain” is the default domain name Oracle suggests during the configurations.

I suggest you to make a copy of your tnsnames.ora file prior making changes to the file. The best is, copy the tnsnames.ora file from your database network admin folder after renaming the existing tnsnames.ora file.

Once the TNS names are set, we can quickly setup “formsweb.cfg” file within which our application specific configurations will be defined.

Based on my installation path and classic domain naming, I should find the formsweb.cfg & default.env files under:

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_12.2.1\config

Please make sure that each and every other configuration file that you are going to edit/alter is backed up, so that you can rollback to default configuration easily by replacing the wrongly configured files. I usually put “.original” by the end of backed up files!

Below is a sample configuration for my application & trust me, you need more than half of the parameters listed below

[balaji]
# 
# Entries from source Oracle FMW Domain
# added by the Forms Upgrade Assistant Plugin.
baseHTML=base.htm
baseHTMLjpi=basejpi.htm
HTMLdelimiter=%
envFile=default.env
serverArgs=%escapeparams% module=%form% userid=%userid% debug=%debug% host=%host% port=%port% %otherparams%
escapeparams=true
form=LOGIN.fmx
userid=APPS/APPS@orcl
debug=no
host=
port=
digitSubstitution=context
otherparams=obr=%obr% record=%record% tracegroup=%tracegroup% log=%log% term=%term% ssoProxyConnect=%ssoProxyConnect%
obr=no
record=
tracegroup=
log=
term=
HTMLbodyAttrs=
HTMLbeforeForm=
HTMLafterForm=
serverURL=/forms/lservlet
codebase=/forms/java
imageBase=codebase
mediaBase=codebase
width=750
height=600
separateFrame=true
splashScreen=false
allowAlertClipboard=true
disableValidateClipboard=false
highContrast=false
background=
lookAndFeel=Oracle
colorScheme=swan
customColorScheme=
logo=
guiMode=0
restrictedURLparams=pageTitle,HTMLbodyAttrs,HTMLbeforeForm,HTMLafterForm,log
formsMessageListener=
recordFileName=
serverApp=
archive=frmall.jar
networkRetries=0
java_version=1.7+
legacy_lifecycle=false
ssoDynamicResourceCreate=true
ssoErrorUrl=
ssoCancelUrl=
ssoMode=false
ssoProxyConnect=no
ssoLogOut=false
ssoLogOutRedirect=
logoutTargetURLParamname=
allow_debug=false
allowNewConnections=true
EndUserMonitoringEnabled=false
EndUserMonitoringURL=
applet_name=
enableJavascriptEvent=true
JavaScriptBlocksHeartBeat=false
idleTimeout=
separate_jvm=false

You will learn which parameters are not necessary over the long run. Just make sure that you will create all the new configurations by the dead end of “formsweb.cfg” file to avoid confusions. Please give extreme attention to the parameter “java_version=1.7+”. This means, you can only use JRE versions 1.7 and above (JRE7, 8 and other certified)

Once the formsweb.cfg is saved, we can proceed with editing/altering default.env file, that is in the same path. You can tell the forms runtime to look in this file to locate the forms runtimes. For example, I’ve all the compiled runtimes available at D:\12c folder for the application that I am trying to deploy/publish. The FORMS_PATH is pointed to it & other paths wherever I have applications (eg: mapped path \\RT04\OPMAN12C\main). Make sure each path is separated using a “;”

FORMS_PATH=D:\Weblogic\Middleware\Oracle_Home\forms;D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\FORMS\instances\forms1;D:\12c;\\RT04\OPMAN12C\main;\\RT04\OPMAN12C\OPMAN

Finally we have to edit/alter one more file, “Registry.dat” which controls how the LOVs appear/behave and many other values for the runtime. We will only configure LOV appearance and required field background color settings this time.

You should able to find “Registry.dat” file here:

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_12.2.1\config\oracle\forms\registry
#
# Application level settings to control UI features
#
# app.ui.lovButtons=false
app.ui.lovButtons=true
# app.ui.requiredFieldVA=false
app.ui.requiredFieldVA=true
#    The background color is specified as an RGB triple.
# app.ui.requiredFieldVABGColor=255,0,0
app.ui.requiredFieldVABGColor=255,242,203

Please note, you can make these whole changes using Weblogic EM console. For me this is the fastest way.

Once whole these changes are made, you can start Weblogic server, start Weblogic FORMS server and try to run the application by entering the following URL on Microsoft Internet Explorer. Please note, no browsers other than Internet Explorer allow you to load JRE NPAPI client. We will discuss about using Java Webstart method by the end of this post.

One most important element you should cross verify at this point is whether the JRE installed is 32Bit or 64Bit. If you have installed the complete JDK before Weblogic setup, you should uninstall the JRE 8/higher version that is installed and install JRE 32Bit 7 or higher. Internet explorer will not load 64Bit JRE

http://<hostname>:7777/forms/frmservlet?config=balaji
http://rt04:7777/forms/frmservlet?config=balaji

Now, we will see how to get reports from the application. If you are planning to migrate from Developer 6i to 12c, there is going to be a huge effort involved. I am working with Oracle Applications R12 from last 11 years and have adapted much of the ideas from the application (menu architecture, report submission and many other). You will have to design a totally new approach for your reports, to cut it short. Plus, 12.2.1.4 is the last version of Oracle Reports (according to sources). Hence please start planning for Oracle BI or Crystal Reports integration for your reporting requirements in the future.

If you are switching from Forms/Reports 6i, please note, you will start using Web.Show_Document method for calling reports from Forms. With the given example, I am allowing users to select a report from list of values and storing the report executable value in to :CTRL.REPORT_NAME column and the calling “Web.Show_Document” method.

if (:CTRL.REPORT_NAME IS NOT NULL) then
Web.Show_Document('/reports/rwservlet?usr&desformat=PDF&destype=cache&report='||:CTRL.REPORT_NAME||'&paramform=Yes','_new');

else
	NULL;
end if;

You can pass the report name (‘ABCD.rdf’) for example for the parameter report within the url specified in Web.show_document method.

How to OPEN Forms Developer and Reports 12c??

While Oracle Forms 12c is available within the menu

accessing Report Developer may not be that straight forward. Basically report builder windows executable is wrapped inside multiple windows batch files & you will find the rwbuilder.bat file here:

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\reports\bin

I suggest you not to try to open the executable, as the wrapper setup environment for the executable to run. Interested, the .bat file has the following content:

@REM
@REM Copyright (c) 1999, 2008, Oracle and/or its affiliates.
@REM All rights reserved. 
@REM

@echo off 
@echo Starting Reports 12c Builder...
setlocal

set DOMAIN_HOME=D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain

call %DOMAIN_HOME%\reports\bin\reports.bat

set COMPONENT_NAME=rptools1
set COMPONENT_CONFIG_PATH=%DOMAIN_HOME%\config\fmwconfig\components\ReportsToolsComponent\%COMPONENT_NAME%

set REPORTS_INSTANCE=%COMPONENT_CONFIG_PATH%
set CA_GPREFS=%COMPONENT_CONFIG_PATH%\tools\admin
set CA_UPREFS=%COMPONENT_CONFIG_PATH%\tools\admin


@echo on
start %ORACLE_HOME%\bin\rwbuilder.exe %*

@echo off
endlocal
@echo on

I think I’ve made a decent attempt to make it easier for a sample application deployment using Weblogic Server 12.2.1.4 & always ready to develop and provide a sample application for entirely newbies using Oracle’s sample schemas. Feel free to let me know through the comments 🙏

EBS R12 Cloned Instance | opatch

Not being a DBA has it’s own perks ;) Freedom to try to fix everything that do not look “legit” from all angles.

My last attempt was to rebuild “Central Inventory” for a cloned instance of Oracle Applications R12 (EBS 12.0.6) on Linux. This instance was cloned from a certified platform, to a totally unsupported platform. In addition to, the DBAs those migrated from 10g R2 to 11g R2 left much of the post clone activities pending immediately once after the instance came online.

So, trying to patch Oracle database 11gR2(11.2.0.4) brought me to a situation that never existed:

  • “opatch” was “not” recognized as a command!
  • “opatch lsinventory” run always screamed about corrupt/missing central inventory location.

The “opatch” not being recongized as a command was due to $ORACLE_HOME/OPatch path missing, easily fixed by adding the path to the existing $PATH environment variable for the Oracle user.

One of the other issues you might face up with 11.2.0.4 could be the unsupported opatch version. You need to replace the current opatch version with the latest supported. Please refer to patch #6880880 & Install the patch marked in the below image

Please read the “README” to know how to replace the existing version with the latest supported.

Once the opatch stack taken care of, we still need to fix the central inventory, that keeps reported as being missing.

[oratest@bak clone]$ opatch lsinventory
 Oracle Interim Patch Installer version 11.2.0.3.25
 Copyright (c) 2020, Oracle Corporation.  All rights reserved.
 Oracle Home       : /u01/oratest/TEST/db/tech_st/11.2.0
 Central Inventory : /u01/oratest/TEST/db/tech_st/11.2.0/inventory
    from           : /u01/oratest/TEST/db/tech_st/11.2.0/oraInst.loc
 OPatch version    : 11.2.0.3.25
 OUI version       : 11.2.0.4.0
 Log file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2020-11-11_10-35-04AM_1.log
 LsInventorySession failed: OPatch failed to locate Central Inventory.
 Possible causes are:
     The Central Inventory is corrupted
     The oraInst.loc file specified is not valid.
 OPatch failed with error code 73

Let us see how to rebuild the Central Inventory for the EBS R12 cloned instance now.

If your current server (the one that has the cloned instance of the application) never had an Oracle installation executed from the same, will not have /etc/oraInst.loc file, instead the file must be available under $ORACLE_HOME & the file MUST BE pointing towards the source server paths. For example, my cloned instance oraInst.loc contents are like below:

>cat oraInst.loc
 inventory_loc=/u05/oraprod/oraInventory
 inst_group=oinstall

where inventory_loc path wrong for the current server.

You may create something equivalent to what already exists with the oraInst.loc file, for example (in my case) a path like /u01/oratest/oraInventory as “Oracle” user.

So, you need to amend the oraInst.loc file that is available with $ORACLE_HOME path like below

>cat oraInst.loc
 inventory_loc=/u01/oratest/oraInventory
 inst_group=oinstall

Now you can proceed building the Central Inventory.

logon as Oracle user

SHUTDOWN THE DATABASE & ALL OTHER ORACLE DATABASE RELATED SERVICES

  1. source the environment
  2. switch to $ORACLE_HOME/appsutil/clone
  3. execute ouicli.pl (./ouicli.pl)

Now, We will attach the current ORACLE_HOME with the Central Inventory. Many things could go wrong here, so make sure that you refer the document “How to create, update or rebuild the Central Inventory for Applications R12” in case if you are stuck for reasons those are not mentioned in this post.

As Oracle user

switch to $ORACLE_HOME/oui/bin

Execute

./runInstaller -ignoreSysPrereqs -silent -attachHome -invPtrLoc $ORACLE_HOME/oraInst.loc \
 ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=""

Example:

./runInstaller -ignoreSysPrereqs -silent -attachHome -invPtrLoc $ORACLE_HOME/oraInst.loc \
 ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME="TEST_Oracle_HOME"

If there are no issues, you should get ” ‘AttachHome’ was successful ” confirmation once after the installer finishes the execution.

Test opatch once again

>opatch lsinventory

The above should provide you an output like below:

[oratest@bak bin]$ opatch lsinventory
 Oracle Interim Patch Installer version 11.2.0.3.25
 Copyright (c) 2020, Oracle Corporation.  All rights reserved.
 Oracle Home       : /u01/oratest/TEST/db/tech_st/11.2.0
 Central Inventory : /u01/oratest/oraInventory
    from           : /u01/oratest/TEST/db/tech_st/11.2.0/oraInst.loc
 OPatch version    : 11.2.0.3.25
 OUI version       : 11.2.0.4.0
 Log file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2020-11-11_10-42-05AM_1.log
 Lsinventory Output file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/lsinv/lsinventory2020-11-11_10-42-
 Local Machine Information::
 Hostname: bak.localdomain.com
 ARU platform id: 0
 ARU platform description::
 There are no Interim patches installed in this Oracle Home.
 
 OPatch succeeded.

That’s all folks. You can proceed with your patching for the database now! Hope this helps few certified DBAs out there ;)

rajesh

Install Oracle forms & reports 12.2.1.4 with Weblogic 12c 12.2.1.4

Hi guys

Last reviewed on: 20th January 2021

Linking this document with an article that explains how a sample application could be published using a newly setup Weblogic environment.

Last reviewed on: 17th January 2021

Please do not try to install Weblogic 14.1.1.0, that supports only JAVA deployments as on date. Oracle says they are committed to release a version that supports Oracle Forms and Reports & the stack is under development.

Last reviewed on: 24th December 2020

Few days back I received an interesting message from one of the visitors, having troubles with their WebLogic installation, inquiring about the frequent crashes. My visitor Mr. Rabin Mitra later figured out it was due to using 32Bit JDK with 64Bit WebLogic infrastructure.

After successfully executing "startnodemanger.cmd" and "startWeblogic.cmd", I am able to start the Weblogic Console UI. However, any action (such as clicking on Server button) within the UI crashes Weblogic! The error is as follows:

# There is insufficient memory for the Java Runtime Environment to continue.
# Native memory allocation (malloc) failed to allocate 1210952 bytes for Chunk::new

Mr. Rabin has confirmed that he had to uninstall the complete WebLogic installation & redo as replacing 32Bit JDK with 64Bit was not sufficient enough to address the continued issues with installation. Please go through the below text and links, incase if you are in the same “soup” ;)

I am happy to report that the 32-bit version of JDK on my 64-bit machine was indeed the problem. However, merely replacing the JDK does not solve the problem. The reason for this is that the jar file for WebLogic was extracted using the 32-bit JDK. This implies that the WebLogic must as well be reinstalled. This again in turn means that the Domain must be re-created and since the domain is associated with a repository, the repository must also be reinstalled. So, it turns out that one must dismantle each piece and reinstall it from scratch!


Installing the JDK is the #1 instruction. So, an incorrect choice here pretty much sets you back completely. You may want to provide this as a caution to your readers.

So here briefly is the sequence of events one must follow if they chose an incorrect version of JDK.

This note was followed by few links:

  1. Uninstall Forms and Reports 12c
    https://docs.oracle.com/middleware/1221/formsandreports/install-fnr/deinstall.htm#FRINS301
  2. Stop the Fusion Middleware Environment
    https://docs.oracle.com/middleware/1212/core/ASADM/start.htm#ASADM138
  3. Uninstall the Oracle Enterprise Repository
    https://docs.oracle.com/middleware/1213/oer/install-oer/uninstall.htm#OERIG380
  4. Uninstall Weblogic software
    http://www.catgovind.com/weblogic/how-to-uninstall-weblogic-12c/
  5. Remove Weblogic Domain (For Linux, but it is really deleting a folder). I whacked the entire c:\Weblogic\Middleware\Oracle_home at the root!
    https://www.learn-it-with-examples.com/middleware/weblogic/common-tasks/remove-weblogic-domain.html
  6. Uninstall JDK
  7. https://java.tutorials24x7.com/blog/how-to-uninstall-java-from-windows

Last reviewed on: 13th September 2020.

This time we are going to see how we could install and configure Oracle forms & reports 12.2.1.4, the latest version using WebLogic Server 12c 12.2.1.4

Side Note: Kindly refer this document from Oracle https://docs.oracle.com/en/middleware/fusion-middleware/12.2.1.3/sysrs/system-requirements-and-specifications.pdf

The Visual C++ requirements mentioned for 12.2.1.3 should suffice 12.2.1.4 also, without which you will NOT able to startup OHS instance.

Prior Installing Oracle Server Software on your computer, please make sure that your computer has a proper name (hostname) like “JOHN-PC, RAJESH-PC, ORACLE-SERVER” etc instead of defaults like ‘DESKTOP-jyyytyuukkyjj’ after an installation of Windows. In addition, please make sure that your laptop/Desktop machine has a Static IP address.

As a rule of thumb, regardless whether you are going to install and configure Oracle software on LINUX or Windows operating systems, always update the OS to latest patch levels, prior you start with the Oracle software installation(s). I strongly recommend using Oracle VirtualBox based Virtual Machines for the lab & later you may move the tried out solution to your preferred environments (TEST, Development). I don’t recommend any of the suggestions I make through my blog posts to be applied to PRODUCTION environments without consulting a Professional support entity.

As per Mr. Rabin Mitra’s suggestion, I am adding a clause. “Please double check whether all the software you have downloaded for the installation is of same architecture. The below instructions are for 64Bit Weblogic installation & starting from JDK all the software downloaded must be 64Bit”

Download Software from Oracle

Fusion Middleware Infrastructure Installer (Weblogic)

https://www.oracle.com/middleware/technologies/weblogic-server-installers-downloads.html

Oracle Forms & Reports 12.2.1.4

https://www.oracle.com/middleware/technologies/forms/downloads.html

JAVA SE Development Kit 1.8

https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

Install JDK

I prefer installing JDK on C: drive & Usually my JDK is installed on C:\Java\JDK folder. The shortest name, the best to handle it later. Once the 64Bit JDK is installed, you need to setup either JAVA_HOME environment variable or add a new entry in to the system path. I prefer a system path modification than user path modification.

Okay, there are hell loads of Oracle software installed on my system. Don’t worry, I manage them. Just make sure the JDK installation path is on top of the list and you are good to go

Install Weblogic Server 12.2.1.4

It may take a while for the jar file to extract and start the installer. Please wait patiently. I was setting up the entire infrastructure using Windows 10 VM & due to the limited resources, hence the entire ordeal was kind of time taking this time for me.

You can safely skip the Auto Update

Naming Oracle installation folders is an art. The complex the folder structures, the complex later configurations would be. I usually prefer to install all my Oracle software on D: drive. Please feel comfortable to format the Oracle home as per your requirements. Maintaining the same format helps me to locate and sometimes replace files from previous installations.

Select “Fusion Middleware Infrastructure”

WebLogic or Middleware installation is NOT complex anymore on Windows 10 OS. There are no special hacks needs to complete the installation.

Install forms & reports 12.2.1.4

Extract the downloaded zip file & you MUST start the installer as “Administrator”.

We’ve to install the Forms and Reports stack on an existing Oracle Home. So please select the Oracle Home where you have installed “WebLogic” Server. Alternatively you can always install the stack on a different Oracle Home, which I never tried earlier.

Select “Forms and Reports Deployment”, as the last two versions of Forms & Reports allow you to install a Standalone Forms Builder.

Select the correct JDK Home

Please make sure that you have Visual C++ libraries pre-installed prior proceeding. If you don’t have the VC++ libraries installed, the OHS instance will not start & you might experience additional problems during installation also.

That completes the Forms & Reports 12.2.1.4 installation.

Create a REPO for the Classic Domain

To create a classic domain, you must have a repository available with the database. Weblogic 12c supports Oracle 11G 11.2.0.4 or later database for the repositories & my pick will be always 12c Database as the 11.2.0.4 is only accessible for customers with valid Oracle support. You may refer this document for a detailed “how to” towards installing Oracle 12c database.

From an elevated command prompt, switch to Oracle Home\oracle_common\bin folder & execute “rcu.bat”

Check the below image for details

Choose the defaults

Provide the connection parameters. If the database is on another server, you don’t have to install additional clients, connection parameters will take care of the connection. Refer the below image for details (Adjust according to your database setup)

My 12c Database already has a repository for Forms & Reports 12c 12.2.1.3, hence I created a new prefix “DEV1”. If you are creating a repo for the first time, you can use “DEV” or any other 3 letter combination (make sure you will able to remember it laters).

Select all the components as seen with the below image. The selected are mandatory, especially the Metadata Services.

Use the same password everywhere! Unless you are setting up a production instance, use the same password everywhere. Example, I use “Abc1234” for the schemas, Weblogic Admin account, node manager etc, which makes the administration much easier. For production, follow the Oracle standards.

That completes the repository creation for a Classic domain.

Create a Classic Domain for deploying forms & reports application

From an elevated command prompt, switch to Oracle Home\oracle_common\common\bin (eg: D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin) & execute config.cmd, that will kick start the classic domain configuration wizard.

Select the defaults, unless you have specifics. We will be creating a new domain with the following example.

Refer the below images and select the same components for a successful domain creation for forms and reports.

As I mentioned earlier, use the same password here, so that you don’t have to worry about forgetting & recovering it at later stages.

We will setup the domain for production (never tried Development, so don’t know how exactly both differ from each other). Leave the JDK selection as it is.

Now, your earlier created repository will come in act. Refer the below image(s) and adjust as per your environment

Make sure all the Schema validations are successful. Next step select components as per the below image

Make sure the Server Group is filled the value as seen with the below image. Don’t miss it!

We’ll add an instance OHS now. Click on the add button and refer the below images for more details

Fill in the Listen Address with “localhost” and leave rest to defaults

That finishes the creation of your classic domain.

You should able to access the newly created WebLogic admin server through the URL http://localhost:7001/console

I’m hurrying to publish the POST without further delays, as I am once again caught up with multiple assignments & keeping up with promises are NOT something I look up to at this time. However, I will add up the missing configurations for OHS and setting up the Forms and reports very soon an an annexure as early as possible!

19th June 2020

Today I installed & configured another Weblogic Server environment & realized that I was supposed to amend this post with more details, is a long due!

I chose a Container+PDB 12c database instance for my Weblogic Server this time. It was fun, finally learning few things about Container and pluggable databases & I am hoping to write another post about setting up Weblogic, Forms and Reports with such a setup in near future.

Now without wasting more time, let us see how to finalize the freshly configured classic domain for Oracle Forms and Reports deployment.

Switch to the your newly created domain’s bin folder from an elevated command prompt. Eg:

“D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\bin”. Usually I always installed Oracle software on D: drive & follow the same naming structure for the folders. It helps me to backup and restore configuration & other files that help me to finish setting up the environments faster.

Start the nodemanger now by issuing the command by executing the command “startnodemanger.cmd”

It may take few seconds to minutes until the node manager starts for the first time. Please remember, you shouldn’t close this command window, which will automatically stop the node manager.

Open another elevated command prompt window and switch to your classic domain’s bin folder. Start the Weblogic admin server by issuing the command “startWeblogic.cmd”

Okay, with node manager, 99.9% chances that you may not bite bullet. With Weblogic Admin server, this is not the case. For number reasons, the Server will try to start and give up in the middle. So don’t panic. If the Weblogic Admin Server is not coming up to a “RUNNING” status after extended period of waiting, go ahead and restart your computer. Start node manager and then try to start the weblogic admin server once again.

The initial startup could take up to 5-6 minutes (depending upon your hardware, this time could vary) & you should see a status like below

Remember not to close the window, which will immediately stop the Weblogic Admin Server. The status of the window will keep on updated against the activities happening at the server side. Once started, you can safely keep both node manager and Weblogic Admin Server CMD windows minimized.

You can logon to the Weblogic Admin server from http://localhost:7001/console

Once after successfully logging in, this is the window you are presented with. We’ve to configure both Forms & Report servers now, by trying to start them. This process will keep on adding many elements to your start menu. Let us see we can start Forms (WLS_FORMS) & Reports (WLS_REPORTS) servers now

Click on the “Control” tab, select both “WLS_FORMS” & “WLS_REPORTS” from servers and press the “Start” button. Once start button clicked, you can press the refresh button to monitor the status of the Servers. Do not forget to click the refresh button again to stop refreshing once after the servers show the status “Running”

It could take minutes for both the servers to reach the “Running” stage. Keep watching the Admin Server command window for unexpected errors. If you have followed all the steps as I’ve instructed throughout my thread, I can confidently say that you shouldn’t have a single hick up until this moment.

Once both servers are running, we can complete the OHS setup finally. Open another elevated command prompt. Switch to Oracle_Common\common\bin folder

Eg: D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin and start Weblogic Scripting Tool (wlst)

Now you can issue the command “ohs_updateInstances()” at the wlst prompt & once the configuration completed, exit wlst by issuing “exit()”

You can try to logon to Weblogic Enterprise Manager console and start the newly setup OHS instance now.

Logon to Weblogic Enterprise Manager from http://localhost:7001/em & click the carousal menu by the left hand side

and expand HTTP Server to view your newly created OHS instance. In our case “ohs1”. Click on “ohs1” to open the control panel for the instance

Click the Start up button to start the OHS instance & after showing you message window stating you cannot stop the starting/stopping process, OHS will start. It could take few seconds to minutes depending upon your hardware and available resources.

Give special attention to “Machine Name”.

Now we will see how to bring OHS instance in action by setting up the few other factors like node manager security, configuration files.

Logon to Weblogic console @ http://localhost:7001/console

Lock & Edit the domain security values, please refer the image below for details

nm

Provide a meaningful name for the node manager account and supply a password. I used the same password for the total 12c deployment, unless you are setting up a PRODUCTION instance, you may use the common password across the 12c instances for easy administration of objects when required. Shutdown the services (better). Once the node manager properties are changed, restart the WLS admin server. Please note, always stop all running servers (forms & reports) services prior shutting down the Admin server & I suggest you gracefully shutdown the Admin server by running “StopWeblogic.cmd” instead of using the Weblogic console.

Move to your Oracle Home folder. I have installed Weblogic server in D:\Weblogic\Middleware folder, hence my Oracle home folder is

D:\Weblogic\Middleware\Oracle_Home\

(Adjust your paths accordingly)

Edit forms.conf file in the folder D:\Weblogic\Middleware\Oracle_Home\forms\templates\config\

By default this template file comes with commented examples, like following

#<Location /forms/>
#        SetHandler weblogic-handler
#        WebLogicHost host
#        WeblogicPort port
#</Location>

You need to uncomment one of the blocks starting from <Location /forms> until </Location> & add properties specific to your deployment, for example, my computer’s name is “RAJESH-PC” and I am using default ports for both forms and reports applications, hence I have the properties set like following

<Location /forms/>
SetHandler weblogic-handler
WebLogicHost HOME-THEATER
WebLogicPort 9001
</Location>

Once altered, copy forms.conf to OHS instance “moduleconf” folder. I have copied the file to following location: (Alter according to your setup)

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\OHS\instances\ohs1\moduleconf

Now we will edit the configuration file for reports

Switch to “D:\Weblogic\Middleware\Oracle_Home\reports\conf” folder & edit “reports_ohs.conf” and add a block like following (alter according to your setup)

<Location /reports/>
SetHandler weblogic-handler
WebLogicHost HOME-THEATER
WebLogicPort 9002
</Location>

Copy the edited file to

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\OHS\instances\ohs1\moduleconf

Now the OHS instance know about the deployments and once after restarted, you can try to start the forms based application through the OHS instance, rather than calling it against the default port (9001)

Once the OHS instance is updated with the new information, try to start the OHS instance from a command prompt using storeUserConfig parameter

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\bin>startComponent.cmd ohs1 storeUserConfig

ohs_manually

1st run, you will be prompted for node manager password, supply the password you have setup earlier and OHS instance should start without creating a fuzz in usual scenarios

In future if you want to start the OHS instance, unless made changes to the configurations, just run

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\bin>startComponent.cmd ohs1

Above concludes the 1st of level of configurations for Oracle forms based application. You can use Weblogic 12c Enterprise Manager to configure most of the forms environment related parameters like forms path and other, while I am yet to find out relevant configuration areas for reports.

image

You can manually modify the formsweb.cfg & default.env files to start with as well

Regardless whether you are going to use the in-process report server or a stand alone instance of the reports, many changes should be made to the configuration files, so that you can call the reports server from a Forms module

Generally we will be modifying multiple configuration files, I will try my level best to provide the exact locations for the files which you should change according to your installation preferences

Configuration files path (Please note, through versions, there are minor changes happened to these configuration files. The images below were copied from my previous post about configuring for deployment, however the settings remain the same for 12.2.1.4 (as tested on 19th June 2020))

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\servers\WLS_REPORTS\applications\reports_12.2.1\configuration

  1. rwservlet.properties – Servlet specific properties
  2. rwserver.conf – Server properties
  3. cgicmd.dat – key mapping file

1. As far you are using in-process reports server, nothing much to alter with “rwservlet.properties” file. Basically you should see entries like following in the opened file:

image


2. rwserver.conf (Please check the below image)

image

3. cgicmd.dat: Many values we supply to call a report using Web.Show_Document method could potentially expose vital information like the database details & many other. “cgicmd.dat” file helps us to map such values to keys and later use them wherever applicable. With this example, I will demonstrate, how the database username, password and service name are mapped using a key. Please check the image attached below

image

Once these configurations files are altered, we need to create a report instance for the WLS_REPORT server. Open an elevated command prompt and switch to “D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin”. Start wlst & connect to the server.

Issue the command

createReportsToolsInstance(instanceName='rptools1', machine='AdminServerMachine')

Note, you can choose your own name for the report instance. Once the report instance created, exit wlst and you can try to start the WLS_REPORT server from the weblogic console.

This comprehensive note should help you to quickly start with Oracle Forms & Reports 12c (12.2.1.4)

I am avoiding few elements like publishing a Forms based application for now. If you feel, should be added, please let me know through the comments section.

regards,

rajesh