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.

Oracle | Thread 1 cannot allocate new log, Private strand flush not complete

Hello guys

Okay, I was silent for couple of months. I took a much needed break and back to work now. As few of you may already know, I am not a DBA (Certified), yet I have dealt with Oracle databases throughout my career & today was “another day” when I came across something new after restoring RMAN backup to a TEST environment.

Actually the entire “how to document” was provided by a APPS DBA friend (thanks to such geeks who are never bothered about someone else “learning the tricks” and challenging them! Geeks remain geeks) & without giving much attention to few elements, I “successfully” duplicated the 11g R2 database. Once after the database came online, I realized that the instance was pretty slow & immediately monitored the alert logs.

I started reading few entries like following:

Thread 1 cannot allocate new log, sequence 56
Private strand flush not complete
  Current log# 3 seq# 55 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo03a.log
  Current log# 3 seq# 55 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo03b.log
Beginning log switch checkpoint up to RBA [0x38.2.10], SCN: 5986177240123
Thread 1 advanced to log sequence 56 (LGWR switch)
  Current log# 4 seq# 56 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo04a.log
  Current log# 4 seq# 56 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo04b.log
Completed checkpoint up to RBA [0x38.2.10], SCN: 5986177240123
Thu Oct 04 12:14:14 2018
Beginning log switch checkpoint up to RBA [0x39.2.10], SCN: 5986177240998
Thread 1 advanced to log sequence 57 (LGWR switch)
  Current log# 1 seq# 57 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo01a.log
  Current log# 1 seq# 57 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo01b.log
Thread 1 cannot allocate new log, sequence 58
Private strand flush not complete
  Current log# 1 seq# 57 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo01a.log
  Current log# 1 seq# 57 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo01b.log
Thu Oct 04 12:14:25 2018
Beginning log switch checkpoint up to RBA [0x3a.2.10], SCN: 5986177241136
Thread 1 advanced to log sequence 58 (LGWR switch)
  Current log# 2 seq# 58 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo02a.log
  Current log# 2 seq# 58 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo02b.log
Thu Oct 04 12:14:47 2018
Thread 1 cannot allocate new log, sequence 59

I landed over a discussion at https://community.oracle.com/thread/364032?start=0&tstart=0 & few others, and at many places I read suggestions towards the redo log files getting filled too fast because of the smaller sizes allocated. I check the production instance, the redo log files were of size 1000M when the TEST instance log files were of size 100M!

So my next requirement was to resize the redo log files without damaging the database.

I came across an excellent post here https://uhesse.com/2010/01/20/how-to-change-the-size-of-online-redologs/ that explains how to create new redo log files and to drop the old ones without affecting the database (or users). The best part is, you don’t even have to take the database offline for any of the suggested activities.

So if you ever face such a situation, give it a try. You would be happy like me :)

regards,

rajesh

 

 

Classic ASP with Oracle database 12c 64Bit

Hi guys

Yesterday I was contacted by one of the visitors after referring my posts about Classic ASP connection to Oracle databases in general & I revisited this “area” after a long time. It took a while for me to setup everything, however the results were pretty awesome. So here comes one more post about Classic ASP with Oracle database, this time Oracle 12c 64Bit.

OS: Windows 10/64Bit Windows Server OS

Prerequisites

  • Setup IIS for publish classic ASP with Oracle 12c database

Software requirement(s):

  • Oracle client 32Bit, 12c (for best connectivity)

Launch IIS and create a new application pool, as shown with the image.

clip_image002

Now go to the advanced settings for the application pool that you have created & switch “Enable 32-Bit Applications” from “false” to “True”

clip_image004

Connection possibilities

There are two ways to connect to Oracle database from Classic ASP

  1. Using DSN (Data Source Name)
  2. DSN less connections. Here we will use the Oracle tnsnames.ora file to identify and connect to the database. We have to insure that the TNS_HOME directory is set at the environment level, or the client that is used is 1st entry in the environment variable “PATH” for Oracle products in case of multi Oracle homed hosts.

Create a DSN

Classic ASP could interact with only 32Bit environment, hence the IIS server hosting machine must have the latest Oracle 32-Bit client installed (older clients may not connect to later databases properly). As I am writing this post, Oracle 12c 12.2.0 is the latest client available.

Logged with an account having “Administrator” privileges, Open ODBC Data Source (32-bit)

clip_image005

Please note, my box has multiple Oracle products, hence don’t get confused by the names in next few screens.

clip_image007

If your installation for the Oracle client was correct, you should able to see an entry for the Oracle driver like the one you could see the image above.

Click “Finish”. This will pop up another window. Under the “System DSN” tab we have to create our new DSN

clip_image008

Provide a name of your choice for the “Data Source Name” and “Description”

Make sure you have the “TNS Service Name” already available with the listener.ora file. Oracle 12c passwords are case sensitive. Hence make sure you are going to provide the case sensitive password while testing the connection.

If you don’t have previous experiences with creating net service names, please follow the instructions below.

clip_image009

Start “Net Configuration Assistant” from the Oracle Home and follow the images below.

clip_image010

Read the help texts available, especially if you are new to Oracle

clip_image011

Provide the service name, which is your database name usually.

clip_image012

 

clip_image013

Provide the FQDN (fully qualified domain name), ie, your computer name like “rajesh-pc.abc.com” in case if you are connected to a domain, else just the name of your computer, like “rajesh-pc”

clip_image014

I would suggest doing a test, if you are sure that all the details provided are correct & no need to test, you can skip this step

clip_image015

clip_image016

By default the configuration tool would suggest you the Oracle service name as new net service name, which you can change to any name. Just make sure that you will remember it.

clip_image017

clip_image018

Now let us test the new service name we have just created.

clip_image019

Once the Net Service Name is created, we will see both the scenarios using 2 different asp files, both using different connection approaches

(ASP sample was copied from here)

1. Connecting to 12c using DSN

[code language=”vb” gutter=”false”]

<html>
<head>
<title>Connecting to an Oracle database using ODBC and DSN connection</title>
</head>
<body>
<%
SET myConn=SERVER.createobject("adodb.connection")
myConn.Open "DSN=BAC;" & _
"Uid=APPS;" & "Pwd=APPS"
SQLStr="SELECT BANK_ID, BANK_NAME, BANK_TYPE FROM BAC_BANKS"
SET result=adoCon.execute(SQLStr)
IF NOT result.EOF thEN
response.write("<h2>Oracle ASP Example</h2>")
response.write("<p>Connecting to the Oracle11g database using ODBC & without a DSN</p>")
response.write("<table BORDER=3 BGCOLOR=#0099CC><tr><th>BANK ID</th>" & _
"<th>Name</th><th>TYPE</th>")
WHILE NOT result.EOF
response.write("<tr><td>" & result("BANK_ID") & "</td>")
response.write("<td>" & result("BANK_NAME") & "</td>")
response.write("<td>" & result("BANK_TYPE") & "</td></tr>")
result.movenext()
WEND
response.write("</table>")
ELSE
response.write("<p>Error retrieving bank data!!</p>")
END IF
adoCon.Close()
%>
</body>
</html>

[/code]

2. Connecting to 12c without DSN

[code language=”vb” gutter=”false”]

<%
Dim adoCon ‘Holds Connection
Dim rsViewRecords ‘Holds Record Set

‘ Initiate connection

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "provider=oraoledb.oracle;data source=SCT;user id=APPS;password=APPS"

if Err.Number <> 0 then
Response.Clear()
response.Write "<hr>ORASESSION Error<br>" & err.number & " — " & err.Description & "<hr>"
response.End
end if

SQLStr="SELECT BANK_ID, BANK_NAME, BANK_TYPE FROM BAC_BANKS"
SET result=adoCon.execute(SQLStr)
IF NOT result.EOF thEN
response.write("<h2>Oracle ASP Example</h2>")
response.write("<p>Connecting to the Oracle11g database using ODBC & without a DSN</p>")
response.write("<table BORDER=3 BGCOLOR=#0099CC><tr><th>BANK ID</th>" & _
"<th>Name</th><th>TYPE</th>")
WHILE NOT result.EOF
response.write("<tr><td>" & result("BANK_ID") & "</td>")
response.write("<td>" & result("BANK_NAME") & "</td>")
response.write("<td>" & result("BANK_TYPE") & "</td></tr>")
result.movenext()
WEND
response.write("</table>")
ELSE
response.write("<p>Error retrieving bank data!!</p>")
END IF
adoCon.Close()
%>

[/code]

Hope this helps few “Classic ASP” guys out there ;)

regards,

rajesh

Oracle Forms Developer 10g crashes while trying to open forms

Hi guys

I had couple of posts addressing the same issue

  1. https://simpleoracle.com/2010/03/20/windows-7-oracle-developer-10g-crash-work-around/
  2. https://simpleoracle.com/2011/05/21/oracle-developer-10g-10-1-2-0-2-crash-on-windows-7-while-opening-forms-modules/

and one of my observations was strictly around the connection to database. I found forms those make calls to database level packages/procedures/functions crashing the developer when they are opened while no established connection with database exists.

Recently I was contacted by one of the visitors asking for help to resolve his issues with the crashes experienced while opening forms those were developed by someone else. We had a short TeamViewer session I realized that the issues were more than mere database connection specific. Then I remembered fixing few of such issues which were caused by missing table columns, packages/procedures/functions at the database level.

I located one of the backups and tried to open a form which I was extremely sure of crashing the developer due to missing objects at database level. All I remembered that I have added additional columns to a table that was in the form and a modified database level procedure, which were missing from freshly cloned database.

Unless I open this particular form, I will not able to re-create the objects and continue with the development as I don’t have any logging for the changes I have made to the form/tables/other objects during the development.

Then I remembered that Developer 6i hardly ever crashed due to missing objects, instead it did warn. As I had a classic domain setup using Weblogic 12c 12.2.1.3 recently with forms and reports developers, I decided to open this problematic forms module with the 12c forms developer.

That was the solution.

(The best thing about Weblogic 12c Forms is, you can opt for a standalone installation of the developer, avoiding all complexities around setting up the entire Weblogic infrastructure. Oracle have so much crap in their decision making team, having weird ideas like forcing the developers to setup an entire Weblogic infrastructure for mere development? duh!)

 

Now, if you are having such a situation, follow the below instructions:

  1. MAKE many copies OF YOUR TROUBLESOME FORM, KEEP THEM IN DIFFERENT PLACES
  2. Open Forms developer 12c, establish connection to database
  3. Open a copy of the form that crashes 10g developer
  4. If the form open (it should in case if it was crashing due to missing objects!), compile all objects (CTRL+SHIFT+T). This should start throwing errors about missing columns and other objects.
  5. Alter your tables, create missing objects and open your form using Developer 10g

That’s it. You must be glad to have it fixed.

Hope this helps few Oracle developers who are mainly developing for Oracle Applications R12 using Developer 10g Suite.

 

regards,

rajesh

 

 

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

Hi guys

I’m back to blogging after couple of busy weeks and quite bit of traveling. I’m currently playing around with Oracle 12c database Release 2 & Developer 6i with Patch 18, again hacked using Patch 3 DLL files in order to execute forms/reports on Windows7-10

Obvious that, starting from Oracle 11g, Oracle has introduced stricter password policies by implementing case sensitive logons, number of attempts and password age etc. I hardly believe small industries are really ever going to implement these policies as Oracle intended to in real life scenarios as it would require a full time DBA, a lot of tracking and auditing (which in my experience never happens)

Anyway, for testing, I have always kept the commands ready to disable said all three security elements. Once a new test database is made, prior attempting any other, I change password complexity, expiry & reuse times using the below given alter commands

  • alter system set sec_case_sensitive_logon=false scope=both;
  • alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;
  • alter profile DEFAULT limit PASSWORD_LIFE_TIME  unlimited;

With 12c Oracle has made many changes to the security, SQL Net connections etc. If you are truly interested, please refer this document to understand what has been deprecated Upgrade Guide 12c Release 1 (12.1) E41397-11

According to the documentation, SEC_CASE_SENSITIVE_LOGON is maintained only for backward compatibility & most probably will be dropped from future builds. I’ve checked altering sec_case_sensitive_logon on a 12c R2 database it works. However, during each startup, I receive a notification that says “ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance”

image

and executing the following command returns a number of parameters those are not supposed to be used against 12c release Winking smile

[code language=”sql” gutter=”false”]

SELECT name from v$parameter WHERE isdeprecated = ‘TRUE’ ORDER BY name;
[/code]

and the following will be listed

NAME
——————————————————————————–
O7_DICTIONARY_ACCESSIBILITY
active_instance_count
asm_preferred_read_failure_groups
background_dump_dest
buffer_pool_keep
buffer_pool_recycle
commit_write
cursor_space_for_time
db_block_buffers
fast_start_io_target
instance_groups

NAME
——————————————————————————–
lock_name_space
log_archive_start
parallel_adaptive_multi_user
plsql_debug
plsql_v2_compatibility
rdbms_server_dn
remote_os_authent
resource_manager_cpu_allocation

sec_case_sensitive_logon

serial_reuse
sql_trace

NAME
——————————————————————————–
standby_archive_dest
unified_audit_sga_queue_size
user_dump_dest
utl_file_dir

26 rows selected.

SQL>

So, we could see that sec_case_sensitive_logon has been listed as deprecated. Now, how exactly we are going to work around this little annoyance totally depends upon us, developers & DBAs. While I prefer a test environment with no password related hassles, a production environment should be designed to accommodate case sensitive logons & other recommended password policies as Oracle may not re-introduce the parameter in future builds.

While the notification/warning we receive about deprecated parameters are generic to all deprecated parameters, in this post, I have only mentioned about case sensitive passwords.

regards,

rajesh