Well, Oracle hardly fixes many errors for Windows OS. They “successfully” inherit bugs in latest versions, mostly because not many businesses are hosting Oracle products on Windows? A poll conducted by https://oracle-base.com reveals just 8% Windows OS share for hosting Oracle products! Interested? read details here
We recently migrated to 19c (19.14) & one of our 3rd party solutions needed to create a Linked Server connection to this instance from MS SQL database. Although 11g client could successfully connect to 19c, I decided to go with the 19c client, that looked less challenging to configure. Installed the client, and tried to install Oracle Services for Microsoft Transaction Server component as additional feature and nightmare started. Software was getting installed, however failing when configuring it.
As the GUI didn’t produce much details about what went wrong, I decided to try configuring it from command line after referring Oracle Services for Microsoft Transaction Server installation documents on Oracle site.
C:\Windows\system32>C:\app\oracle\product\19.0.0\client_1\oui\bin\runConfig.bat ORACLE_HOME=C:\app\oracle\product\19.0.0\client_1 MODE=perform ACTION=configure RERUN=true
C:\Windows\system32>REM Copyright (c) 2003, 2018, Oracle and/or its affiliates.
C:\Windows\system32>REM All rights reserved.
C:\Windows\system32>SET PATH=C:\app\oracle\product\19.0.0\client_1\bin;C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\Oracle\product\11.2.0\dbhome_1\bin;C:\oracle\product\11.2.0\client_1\bin;C:\orant\bin;C:\Oracle\19c\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\OpenSSH\;C:\orant\jdk\bin;C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\;C:\Program Files\Azure Data Studio\bin;C:\Users\rajesh.RTHAMPI\AppData\Local\Microsoft\WindowsApps;C:\app\oracle\product\19.0.0\client_1\oui\lib
C:\Windows\system32>"C:\app\oracle\product\19.0.0\client_1\jdk\jre\bin\java" -classpath "C:\app\oracle\product\19.0.0\client_1\oui\jlib\OraInstaller.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\OraInstallerNet.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\xmlparserv2.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\srvm.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\emCfg.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\share.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\ojmisc.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\xml.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\OraCheckPoint.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\OraPrereq.jar" oracle.sysman.oii.oiic.OiicRunConfig C:\app\oracle\product\19.0.0\client_1\oui ORACLE_HOME=C:\app\oracle\product\19.0.0\client_1 MODE=perform ACTION=configure RERUN=true
perform - mode is starting for action: configure
Apr 15, 2022 7:20:46 PM oracle.install.config.common.MTSInternalPlugIn invoke
INFO: MTSInternalPlugIn: ... adding </ouiinternal>
Apr 15, 2022 7:20:46 PM oracle.install.commons.util.FileUtils changeUnixFilePermissions
INFO: Not changing the permissions of file \configTools_2022-04-15_07-20-46PM.log, because current platform is not unix.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn invoke
INFO: Executing MTS
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn invoke
INFO: Command C:\Windows\system32\cmd.exe /c call C:\app\oracle\product\19.0.0\client_1\bin\oramtsctl.exe -new -internal
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: ... GenericInternalPlugIn.handleProcess() entered.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: ... GenericInternalPlugIn: getting configAssistantParmas.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: ... GenericInternalPlugIn: checking secretArguments.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: No arguments to pass to stdin
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: ... GenericInternalPlugIn: starting read loop.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line: Service is being created.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: End of argument passing to stdin
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line: Service creation failed.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
Then came the message that said what went wrong
INFO: Skipping line: WSE-00008: Unsupported Oracle home type.
I found a solution immediately there after on stackoverflow. This answer discusses about the registry values that could cause the installation issues for Oracle client version 12.x & quickly I tried the same, and the configuration completed successfully.
So here is how to address the problem. Please make sure that you will take a backup of Windows Registry prior attempting to manually modify the values. A corrupt registry might force you to re-install Windows.
As we could see here, ORACLE_HOME_TYPE after a default install doesn’t have a value, failing MTS in my case. As I was trying to configure the Oracle client, expected value was “2” and for the Database, the value should be “1”
Modify key & exit registry editor.
Try to configure MTS once again from the command prompt. This time configurations must complete successfully!
Few weeks back one DBA told me there is no need to setup HugePages as we are not using 40+GB SGA & we had it enabled from the initial days of 11g migration from 10g (10.2.0.3). Well, here is how you can setup HugePages for your Oracle database instance.
A brief explanation that might help you to decide whether you should enable HugePages or not is quoted from Tim Hall’s article below.
“For large SGA sizes, HugePages can give substantial benefits in virtual memory management. Without HugePages, the memory of the SGA is divided into 4K pages, which have to be managed by the Linux kernel. Using HugePages, the page size is increased to 2MB (configurable to 1G if supported by the hardware), thereby reducing the total number of pages to be managed by the kernel and therefore reducing the amount of memory required to hold the page table in memory. In addition to these changes, the memory associated with HugePages can not be swapped out, which forces the SGA to stay memory resident. The savings in memory and the effort of page management make HugePages pretty much mandatory for Oracle 11g systems running on x86-64 architectures.“
Recently I had a short Twitter exchange with Tim Hall about the subject & He mentioned that he always enables it.
Please refer the article from below link, it is by Tim Hall, a geek in this field and the owner of https://oracle-base.com
Exercises explained in this article is applicable to 19c also (verified as working). Please make sure that your version of Oracle database supports these settings before attempting. Please do not copy this to PRODUCTION environments without enough testing.
We’ve our Oracle EBS instance hosted from Linux environment. However I post much for the Windows OS as I see there are not many articles targeting Oracle on Windows. I think for better view counts, I should consider posting the articles for both OS.
In my last post about restoring 1g RMAN online backup to 19c non CDB database, I said my ultimate goal was to convert the upgraded database from non CDB to PDB, that is plugged on to an existing CDB. Let us see how to achieve this goal this time.
Before start, there are few mandatory checks you must do to insure a successful migration from Non CDB to PDB. I will list few of them those came as hurdles for me.
Character Set. Make sure that your CDB and the Non CDB database have the same character set. There are few possible conversions. Please check 12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1) for more details. My CDB was using Arabic character set and the upgraded non CDB database was using a noncompatible set. So I dropped the CDB and created a new CDB without PDB(s). Dropping a CDB will not be always possible because other PDBs are attached to it.
Available storage space. This could be a real concern incase if you are planning to create a PDB, cross verify & then delete the non CDB database. You will need approximately the same size of your non CDB datafiles size storage available to do this activity. My database was less than 5 GB in size, hence it never occurred to me. If your database has a bigger size, plan accordingly.
Let’s do it now.
From and elevated command prompt (Windows Terminal is not yet matured enough to handle such “heavy” tasks) source the non CDB. Shutdown and startup mount exclusive.
C:\Windows\system32>set ORACLE_SID=KAZEMA
C:\Windows\system32>sqlplus / as sysdba
SQL> shu immediate
SQL> startup mount exclusive
SQL> alter database open read only;
Create manifest file for the non CDB now.
SQL> exec dbms_pdb.describe (pdb_descr_file=>'D:\Oracle19c\kazema_manifest_file.xml');
SQL> shu immediate
Source the CDB now.
C:\Windows\system32>set ORACLE_SID=KAZEMACDB
C:\Windows\system32>sqlplus / as sysdba
SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
Now, we need to see whether non CDB meets the compatibility requirements. There could be multiple elements those you may have to fix before, the non CDB is ready for a successful migration as PDB. You will have to refer PDB_PLUG_IN_VIOLATIONS view for these details. Go ahead, execute the below code block.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
Compatible CONSTANT VARCHAR2(3) :=CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY
(pdb_descr_file => 'D:\Oracle19c\kazema_manifest_file.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
YES
My case I didn’t have incompatibilities blocking possible migration. Your case could be different. If the output is “NO”, run the following query (use SQL Developer for better view)
select * from PDB_PLUG_IN_VIOLATIONS where name='<your non CDB name>';
Fix each and every other ERROR & you can safely ignore the “WARNINGS” for now.
Proceed with creating a new PDB from the manifest file that we have created in the 1st step.
SQL> CREATE PLUGGABLE DATABASE KAZEMA USING 'D:\Oracle19c\kazema_manifest_file.xml' COPY FILE_NAME_CONVERT=('D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE','D:\ORACLE19C\DB\ORADATA\KAZEMA');
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 KAZEMA MOUNTED
Once the PDB has been created, confirm the same & proceed with balance activities. Run “noncdb_to_pdb.sql”
SQL> alter session set container=KAZEMA;
Session altered.
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
Based on the resource available, this might take a while for the script to complete execution. Once completed you can try to open the PDB. Check for Errors and Warnings in the PDB_PLUG_IN_VIOLATIONS view. If there are new ERRORS & WARNINGS fix them.
A typical scenario could list you many things like this (reference image only)
After fix attempts, I am still left with one Warning about sys lock issues. This is mainly due to the password file format 12.2. I hope to find a solution soon for it. Status column states whether the Error/Warning was taken care off.
SQL> alter pluggable database open;
Pluggable database altered.
SQL> SELECT name, open_mode FROM v$pdbs;
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
KAZEMA
READ WRITE
1 row selected.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 KAZEMA READ WRITE NO
SQL>
Now you have a non CDB and PDB with the same SID. You can proceed with dropping the non CDB database after cross verifying the concerns.
Foot Note: If you are plugging a database that was hosted in another server/computer after converting, make sure to verify the “local_listener” parameter for the PDB exist.
D:\Oracle19c>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 10 11:18:33 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_KAZEMACDB
SQL> alter session set container="KAZEMA";
Session altered.
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_KAZEMACDB
SQL>
If the local listener is not reachable, PDB level services will fail to register with listener and the applications those depend on such services will fail to establish connections. This applies mostly to Oracle E-Business Suite environments and could be mapped to sophisticated applications that use specific service names to establish connections to the database.
Is it possible to restore previous version RMAN database backup to a later version? Depending upon the supported upgrade paths, the lowest version I managed to restore to 19c (19.14) was 11g R2 (11.2.0.4)
Upgrade Path for Oracle database 19c
Please note, my final goal is to convert this NON CDB database to PDB and attach to an existing CDB.
What you need?
Intact RMAN online backup
Enough memory and storage space available on your computer.
Time and patience
Oracle support access, for many of the documents mentioned later in the article.
Scenario (fictitious): I’m given a full RMAN backup without any other details. I don’t know the source server details other than that it was windows & the version of the database release was 11.2.0.4.
Step#1
Using DBCA created a new non CDB database with the target database name, eg “KAZEMA”. After shutting down database, stopped listener keeping Windows Service for Oracle database running. Physically deleted all data files including control, redo logs.
(You can create an empty instance using ORADIM. That means ORADIM will create Windows services necessary for Oracle. You will have to create the necessary folders. Not just that, you need create a parameter file (initDATABASENAME.ora) to start the instance. Refer the below example)
D:\Oracle19c>oradim -new -sid KAZEMA -syspwd Oracle123 -startmode auto
Enter password for Oracle service user:
OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.
Instance created.
Considering the fact that my target database name is “KAZEMA”, I have modified a copy of parameter file that I had. One of the most important factor you should pay attention here is to create all the physical paths mentioned in the parameter file. Example D:\Oracle19c\db\admin\KAZEMA\adump, D:\Oracle19c\db\oradata\KAZEMA\CONTROLFILE. Failing to find these physical paths might cause errors or your database will not start at all.
D:\Oracle19c\db\admin\KAZEMA has few more folders, like shown in the image. Please create them manually before starting the database.
Add *.max_string_size=’STANDARD’ to the parameter file, necessary for the upgrade. You will have to change this parameter once again after upgrade completes. We will discuss about it later in the article.
SQL> startup nomount (pfile=D:\Oracle19c\database\initKAZEMA.ora # if instance was created using ORADIM)
ORACLE instance started.
Total System Global Area 2147482424 bytes
Fixed Size 9030456 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7745536 bytes
From another elevated command prompt started RMAN & restored control file from backup that I had in “D:\BAHRMAN” folder.
D:\BAHRMAN>set ORACLE_SID=KAZEMA
D:\BAHRMAN>rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 23 09:52:14 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: KAZEMA (DBID=964058362, not open)
RMAN>restore controlfile from 'D:\BAHRMAN\CTL_C-964058362-20210927-02';
RMAN>alter database mount;
RMAN>report schema; #This will populate the data file details
Shutdown the database now. Restart the database in “nomount” mode using parameter file.
Once database shutdown and restarted, exit RMAN and connect again. We’ll duplicate database using the RMAN backup now using “NOOPEN” switch that is supported from versions 12.
D:\BAHRMAN>rman auxiliary /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 23 09:56:42 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: KAZEMA (not mounted)
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 "KAZEMA" NOOPEN backup location 'D:\BAHRMAN' nofilenamecheck
db_file_name_convert=('D:\ORACLE\ORADATA\KAZEMA\','D:\Oracle19c\db\oradata\KAZEMA\DATAFILE\')
LOGFILE
GROUP 1 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo01a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo01b.log'
) SIZE 1200M ,
GROUP 2 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo02a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo02b.log'
) SIZE 1200M ,
GROUP 3 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo03a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo03b.log'
) SIZE 1200M ,
GROUP 4 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo04a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo04b.log'
) SIZE 1200M ;
# 19c & later, no need to exclusively release channels. If you restoring to other versions, make sure to release the channels.
# https://www.thegeekdiary.com/oracle-database-19c-rman-06012-channel-d1-not-allocated/
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}
allocated channel: c1
channel c1: SID=498 device type=DISK
allocated channel: c2
channel c2: SID=619 device type=DISK
allocated channel: c3
channel c3: SID=742 device type=DISK
allocated channel: c4
channel c4: SID=862 device type=DISK
Starting Duplicate Db at 23-MAR-22
searching for database ID
found backup of database ID 964058362
contents of Memory Script:
.....Removed........
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=1100081253 file name=D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE\LC01.DBF
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=1100081253 file name=D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE\BAHAR01.DBF
Leaving database unopened, as requested
Finished Duplicate Db at 23-MAR-22
#You will see the below errors for 19c and later while trying to release the channels exclusively. Remove the release channel instructions from the RMAN scripts to avoid seeing these messages.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of release command at 03/23/2022 10:07:32
RMAN-06012: channel: c1 not allocated
RMAN>
Ignore errors by the end of RMAN process, if they are related to releasing channels. If there are errors of other nature, you must resolve them. Now we will open the database resetting logs and ready to upgrade.
D:\Oracle19c>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 23 10:09:51 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
SQL> alter database open resetlogs upgrade;
Database altered.
As we have restored a RMAN backup, it is obvious that the source database had archive logs enabled. Let us disable archive logging from our new database. Shutdown the database and open it in mount mode.
SQL> startup mount pfile=D:\Oracle19c\database\initKAZEMA.ora
ORACLE instance started.
Total System Global Area 2147482424 bytes
Fixed Size 9030456 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7745536 bytes
SQL> alter database noarchivelog;
This is a long process for many reasons. First of all, no prerequisites were executed and recommended actions were performed against the source 11G database for 19c upgrade. This will cause upgrade process to fix prerequisites during upgrade & brace for few errors. Without MAX_STRING_SIZE set as ‘STANDARD’ for 11g, upgrade will fail. I have started the database instance using parameter file ONLY for this reason. I’m not sure whether RMAN restore crated SPFILE copies this value. So, I didn’t take chances.
Argument list for [D:\Oracle19c\rdbms\admin\catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = D:\Oracle19c\
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 4
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.14.0.0.0DBRU_WINDOWS.X64_211224.3]
D:\Oracle19c\rdbms\admin\orahome.exe = [D:\Oracle19c]
D:\Oracle19c\bin\orabasehome.exe = [D:\Oracle19c]
catctlGetOraBaseLogDir = [D:\Oracle19c]
Analyzing file D:\Oracle19c\rdbms\admin\catupgrd.sql
Log file directory = [D:\Oracle19c]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [D:\Oracle19c\catupgrd_catcon_8056.lst]
catcon::set_log_file_base_path: catcon: See [D:\Oracle19c\catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [D:\Oracle19c\catupgrd_*.lst] files for spool files, if any
Number of Cpus = 8
Database Name = KAZEMA
DataBase Version = 11.2.0.4.0
Parallel SQL Process Count = 4
Components in [KAZEMA]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
DataBase Version = 11.2.0.4.0
------------------------------------------------------
Phases [0-107] Start Time:[2022_03_23 10:18:27]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [KAZEMA] Files:1 Time: 273s
*************** Catalog Core SQL ***************
Serial Phase #:1 [KAZEMA] Files:5 Time: 31s
Restart Phase #:2 [KAZEMA] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [KAZEMA] Files:19 Time: 11s
Restart Phase #:4 [KAZEMA] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [KAZEMA] Files:7 Time: 14s
***************** Catproc Start ****************
Serial Phase #:6 [KAZEMA] Files:1 Time: 11s
***************** Catproc Types ****************
Serial Phase #:7 [KAZEMA] Files:2 Time: 6s
Restart Phase #:8 [KAZEMA] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [KAZEMA] Files:70 Time: 11s
Restart Phase #:10 [KAZEMA] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [KAZEMA] Files:1 Time: 42s
Restart Phase #:12 [KAZEMA] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [KAZEMA] Files:95 Time: 4s
Restart Phase #:14 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:15 [KAZEMA] Files:122 Time: 6s
Restart Phase #:16 [KAZEMA] Files:1 Time: 0s
Serial Phase #:17 [KAZEMA] Files:25 Time: 2s
Restart Phase #:18 [KAZEMA] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [KAZEMA] Files:32 Time: 8s
Restart Phase #:20 [KAZEMA] Files:1 Time: 0s
Serial Phase #:21 [KAZEMA] Files:3 Time: 8s
Restart Phase #:22 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:23 [KAZEMA] Files:25 Time: 157s
Restart Phase #:24 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:25 [KAZEMA] Files:12 Time: 96s
Restart Phase #:26 [KAZEMA] Files:1 Time: 0s
Serial Phase #:27 [KAZEMA] Files:1 Time: 0s
Serial Phase #:28 [KAZEMA] Files:4 Time: 2s
Serial Phase #:29 [KAZEMA] Files:1 Time: 0s
Restart Phase #:30 [KAZEMA] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [KAZEMA] Files:1 Time: 0s
Restart Phase #:32 [KAZEMA] Files:1 Time: 0s
Serial Phase #:34 [KAZEMA] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [KAZEMA] Files:297 Time: 19s
Serial Phase #:36 [KAZEMA] Files:1 Time: 0s
Restart Phase #:37 [KAZEMA] Files:1 Time: 1s
Serial Phase #:38 [KAZEMA] Files:10 Time: 3s
Restart Phase #:39 [KAZEMA] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [KAZEMA] Files:3 Time: 40s
Restart Phase #:41 [KAZEMA] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [KAZEMA] Files:13 Time: 91s
Restart Phase #:43 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:44 [KAZEMA] Files:11 Time: 7s
Restart Phase #:45 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:46 [KAZEMA] Files:3 Time: 1s
Restart Phase #:47 [KAZEMA] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [KAZEMA] Files:1 Time: 9s
Restart Phase #:49 [KAZEMA] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [KAZEMA] Files:1 Time: 15s
************ Upgrade Component Start ***********
Serial Phase #:51 [KAZEMA] Files:1 Time: 0s
Restart Phase #:52 [KAZEMA] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [KAZEMA] Files:2 Time: 363s
***************** Upgrading XDB ****************
Restart Phase #:54 [KAZEMA] Files:1 Time: 0s
Serial Phase #:56 [KAZEMA] Files:3 Time: 22s
Serial Phase #:57 [KAZEMA] Files:3 Time: 1s
Parallel Phase #:58 [KAZEMA] Files:10 Time: 2s
Parallel Phase #:59 [KAZEMA] Files:25 Time: 3s
Serial Phase #:60 [KAZEMA] Files:4 Time: 4s
Serial Phase #:61 [KAZEMA] Files:1 Time: 0s
Serial Phase #:62 [KAZEMA] Files:32 Time: 2s
Serial Phase #:63 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:64 [KAZEMA] Files:6 Time: 5s
Serial Phase #:65 [KAZEMA] Files:2 Time: 14s
Serial Phase #:66 [KAZEMA] Files:3 Time: 59s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [KAZEMA] Files:1 Time: 0s
Serial Phase #:69 [KAZEMA] Files:1 Time: 1s
Parallel Phase #:70 [KAZEMA] Files:2 Time: 25s
Restart Phase #:71 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:72 [KAZEMA] Files:2 Time: 1s
Serial Phase #:73 [KAZEMA] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:74 [KAZEMA] Files:1 Time: 0s
Serial Phase #:76 [KAZEMA] Files:1 Time: 29s
Serial Phase #:77 [KAZEMA] Files:2 Time: 1s
Restart Phase #:78 [KAZEMA] Files:1 Time: 1s
Serial Phase #:79 [KAZEMA] Files:1 Time: 14s
Restart Phase #:80 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:81 [KAZEMA] Files:3 Time: 28s
Restart Phase #:82 [KAZEMA] Files:1 Time: 0s
Serial Phase #:83 [KAZEMA] Files:1 Time: 4s
Restart Phase #:84 [KAZEMA] Files:1 Time: 0s
Serial Phase #:85 [KAZEMA] Files:1 Time: 8s
Restart Phase #:86 [KAZEMA] Files:1 Time: 1s
Parallel Phase #:87 [KAZEMA] Files:4 Time: 56s
Restart Phase #:88 [KAZEMA] Files:1 Time: 0s
Serial Phase #:89 [KAZEMA] Files:1 Time: 1s
Restart Phase #:90 [KAZEMA] Files:1 Time: 0s
Serial Phase #:91 [KAZEMA] Files:2 Time: 7s
Restart Phase #:92 [KAZEMA] Files:1 Time: 0s
Serial Phase #:93 [KAZEMA] Files:1 Time: 0s
Restart Phase #:94 [KAZEMA] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [KAZEMA] Files:1 Time: 18s
Restart Phase #:96 [KAZEMA] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [KAZEMA] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [KAZEMA] Files:1 Time: 61s
******************* Migration ******************
Serial Phase #:99 [KAZEMA] Files:1 Time: 30s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [KAZEMA] Files:1 Time: 1s
Serial Phase #:101 [KAZEMA] Files:1 Time: 0s
Serial Phase #:102 [KAZEMA] Files:1 Time: 80s
***************** Post Upgrade *****************
Serial Phase #:103 [KAZEMA] Files:1 Time: 49s
**************** Summary report ****************
Serial Phase #:104 [KAZEMA] Files:1 Time: 0s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [KAZEMA] Files:1 Time: 1s
Serial Phase #:106 [KAZEMA] Files:1 Time: 0s
Serial Phase #:107 [KAZEMA] Files:1 Time: 41s
------------------------------------------------------
Phases [0-107] End Time:[2022_03_23 10:48:42]
------------------------------------------------------
Grand Total Time: 1847s
*** WARNING: ERRORS FOUND DURING UPGRADE ***
1. Evaluate the errors found in the upgrade logs
and determine the proper action.
2. Rerun the upgrade when the problem is resolved
REASON:
ERRORS FOUND: During Upgrade
FILENAME: D:\Oracle19c\catupgrd0.log AT LINE NUMBER: 1124126
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-13509: error encountered during updates to a AWR table ORA-12899: value too large for column ORA-12899: value too large for column "SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30)
(actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [BEGIN
/* Perform the required AWR catalog operations for this release */
dbms_swrf_internal.refresh_catalog;
END;]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-12899: value too large for column ORA-12899: value too large for column "SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30)
(actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = ["SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30) (actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [(actual: , maximum: ) ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524 ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-06512: at line 3]
STATEMENT = [as above]
------------------------------------------------------
LOG FILES: (D:\Oracle19c\catupgrd*.log)
Upgrade Summary Report Located in:
D:\Oracle19c\upg_summary.log
Grand Total Upgrade Time: [0d:0h:30m:47s]
I didn’t remove lines from the output to give you an overview of what happened during the initial “catupgrd.sql” call without MAX_STRING_SIZE set as “STANDARD“. As you can see, it has generated few errors & we need to fix them.
ERROR = [ORA-12899: value too large for column ORA-12899: value too large for column “SYS”.”OBJ$”.”SUBNAME” (actual: 31, maximum: 30) is pretty easy to fix. This is mainly due to strings lengths & follow the below steps to fix it.
Move to %ORACLE_HOME%\rdbms\admin folder.
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
alter system set MAX_STRING_SIZE=EXTENDED;
@utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP;
Once the script runs successfully, shutdown the database once again and start in upgrade mode. Rerun the “catupgrd.sql” as instructed above. Let the upgrade finish. Insure there are no errors. A successfully finished upgrade process will look like below. I mentioned earlier, this process could be pretty time taking based on the size of the database you are upgrading. My entire database was less than 5GB in size & I’ve NVMe+SSD based work laptop running i7 10th generation processor & 32GB memory. The waiting was frustrating.
Serial Phase #:101 [KAZEMA] Files:1 Time: 0s
Serial Phase #:102 [KAZEMA] Files:1 Time: 78s
***************** Post Upgrade *****************
Serial Phase #:103 [KAZEMA] Files:1 Time: 23s
**************** Summary report ****************
Serial Phase #:104 [KAZEMA] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [KAZEMA] Files:1 Time: 1s
Serial Phase #:106 [KAZEMA] Files:1 Time: 0s
Serial Phase #:107 [KAZEMA] Files:1 Time: 36s
------------------------------------------------------
Phases [0-107] End Time:[2022_03_23 15:15:34]
------------------------------------------------------
Grand Total Time: 1209s
LOG FILES: (D:\Oracle19c\catupgrd0\catupgrd*.log)
Upgrade Summary Report Located in:
D:\Oracle19c\catupgrd0\upg_summary.log
Grand Total Upgrade Time: [0d:0h:20m:9s]
I came across the errors while I called the upgrade and went ahead and fixed them. Kept the same in the article so that the search engine could pick up the errors for someone else out there. Now it is time for to recompile all invalid objects after upgrade.
SQL>@utlrp.sql
Unless you have a huge number of PL/SQL libraries and other, this should complete in few minutes. A count for invalid objects will be provided to you & you can proceed to fix them once after a database restart. Keep a tight watch on the alert log file and address additional errors, if any.
Finally create SPFILE from current parameter file, shutdown and restart the database. You should be good to go now. Check for the Temporary files, Redo Log files and other vitals.
These invalid objects are from custom schema & could be fixed/dropped if not needed. Make sure there are no system level invalid objects.
Another area of interest could be OLAP.
Check and confirm OLAP objects are not invalid. OLAP catalog was deprecated after 11g. You may proceed removing it if needed.
One of the major annoyances with Oracle EBS CDB/PDB environment settings I found that, I cannot connect to the PDB (for EBS) from the CDB environment (default). I always have to source the PDB environment in order to connect the PDB and do my stuffs.
As usual, I am doing this for my LAB & you may not copy this to your PRODUCTION environment.
So this what I get when I try to connect to PDB from CDB environment.
oracle@erp-devp.xyz.com:/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin>tnsping DEVP
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-MAR-2022 14:59:03
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin/DEVP_erp-devp/sqlnet.ora
TNS-03505: Failed to resolve name
The easiest solution was to add an additional entry for the PDB within the $TNS_ADMIN tnsnames.ora file. Let us see how to. Please note these might get overwritten when you run autoconfig.
Save the file & you are ready to go. Notice carefully the service name and instance name values for “DEVP” and “DEVPCDB”. The PDB will have own name for the service, however the instance name will point towards the CDB name & this is the way it should be.
oracle@erp-devp.xyz.com:/home/oracle>tnsping DEVP
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-MAR-2022 15:06:20
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin/DEVP_erp-devp/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL=tcp)(HOST=erp-devp.xyz.com)(PORT=1526)) (CONNECT_DATA = (SERVICE_NAME=DEVP)(INSTANCE_NAME=DEVPCDB)))
OK (0 msec)
oracle@erp-devp.xyz.com:/home/oracle>
That’s all. Regardless whether it is EBS or not, you can keep adding the PDB details into the tnsnames.ora file & if the service is registered with the listener, will able to connect without sourcing the specific environment variables.
Few “years” back, while it was almost impossible to install Oracle 10g on Windows 7/8, I made some attempts & managed to install 32Bit versions of 10g 10.2.0.3.x successfully. A particular release 10.2.0.4 had helluva of issues, mainly TNS related & I can see many visits to that thread even today! I believe, those visitors are not coming over there for 10g, instead for TNS.
On Windows, the below is a typical situation & we’ll see the primary reasons for the same.
Microsoft Windows [Version 10.0.22000.556]
(c) Microsoft Corporation. All rights reserved.
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 21 07:59:29 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
On Windows, everything is another service. So we’ll start with checking whether the “Oracle” service for the database instance has started.
I’m using Oracle database on my laptop mostly for learning purposes, hence the service startup is set as “Manual”. This gives me control over the resources consumed by the database. Not just the service, I have setup the instance to start manual to insure Oracle service doesn’t open the database automatically. Latest Windows OS that use quick boot will save the state of the service, hence you might find your database readily available once after a reboot, although you set everything as manual :)
Let’s see what happens after starting the “Oracle Service”.
Well, that fixes one of the many reasons for the dreaded ORA-12560: TNS:protocol adapter error! As soon I am hit with another, will amend this post.
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.
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 :)
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.
Now go to the advanced settings for the application pool that you have created & switch “Enable 32-Bit Applications” from “false” to “True”
Connection possibilities
There are two ways to connect to Oracle database from Classic ASP
Using DSN (Data Source Name)
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)
Please note, my box has multiple Oracle products, hence don’t get confused by the names in next few screens.
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
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.
Start “Net Configuration Assistant” from the Oracle Home and follow the images below.
Read the help texts available, especially if you are new to Oracle
Provide the service name, which is your database name usually.
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”
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
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.
Now let us test the new service name we have just created.
Once the Net Service Name is created, we will see both the scenarios using 2 different asp files, both using different connection approaches
<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>
2. Connecting to 12c without DSN
<% Dim adoCon 'Holds Connection Dim rsViewRecords 'Holds Record Set
'Initiate connection
Set adoCon = Server.CreateObject("ADODB.Connection")
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() %>
Hope this helps few “Classic ASP” guys out there ;)
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:
MAKE many copies OF YOUR TROUBLESOME FORM, KEEP THEM IN DIFFERENT PLACES
Open Forms developer 12c, establish connection to database
Open a copy of the form that crashes 10g developer
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.
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.
