Category Archives: Code

Did your APEX upgrade just fail with ORA-04043: object WWV_DBMS_SQL does not exist?

FailedAPEXInstall

Many folks have done upgrades of APEX to later versions of 5 or 18 without any issues at all, but there is a situation in which things can go wrong. I’m not exactly sure how much of an edge case it is, but I’ve now seen it ‘more than once’. I know that it requires a 12.2 or higher database and I believe it might require an APEX that has been upgraded over the years from ‘previous releases’, somewhere between 3 and 5.0, to a 5.1 or higher version of APEX such as the current 18.1 version of APEX.

In Oracle Database 12.2 and higher, the following three APEX package was officially deprecated or dropped: WWV_DBMS_SQL, WWV_FLOW_VAL, and WWV_FLOW_KEY. Note that I tried to find something in the official Oracle Documentation on this, but I couldn’t. (I’m sure someone with better Google-foo than I could, but it would seem that Oracle’s search capabilities for its documentation should really be a lot better than they are…)  Here’s a link to Morgan’s Library where he lays out what’s different in 12.2 and you can see these three objects are in the deprecated or dropped list. Update: Only WWV_DBMS_SQL was deprecated. The issue that we’re going to run in to is that, most definitely, WWV_DBMS_SQL has been dropped deprecated in Oracle Database 12.2. Update: WWV_FLOW_VAL and WWV_FLOW_KEY are not deprecated or dropped, they are just no longer installed by default as APEX is no longer installed by default. WWV_FLOW_SQL is deprecated.

During the APEX 18.1 install, it will look for a previous version of APEX and when it creates the new APEX_180100 schema, it will migrate all of your information from the previous APEX schema (let’s say APEX_050100) into the new schema. One VERY cool thing about this is that the install will leave all data in the previous schema so that if the installation fails, you can use these instructions to revert back to your previous installation. If you read the documentation that I linked you’ll see that for reverting to a lot of the older versions of APEX after the 18.1 install fails, you’ll actually add back the WWV_DBMS_SQL package by running the source which is in the $ORACLE_HOME/apex/core folder.

After the migration is complete, the installer will attempt to ‘clean up’ some artifacts from the previous versions of APEX that might otherwise get left behind so might run into the following situation if you have a 12.2 or higher database:

SYS@prodcon AS SYSDBA> @apexins apex apex_files temp /i/
[SNIP of vast amounts of output]
PL/SQL procedure successfully completed.

...Remove objects created from previous APEX installations
declare
*
ERROR at line 1:
ORA-04043: object WWV_DBMS_SQL does not exist
ORA-06512: at line 9
ORA-06512: at line 23

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

If I used the version of APEX that ships with the 12.2 database to add back in the WWV_DBMS_SQL (from the $ORACLE_HOME/apex/core folder run wwv_dbms_sql.sql and wwv_dbms_sql.plb files as sys) I’d get the ‘new updated versions’ (see below) that actually create a specific version of the packages with the APEX schema name in it, instead of the old ‘generic’ WWV_DBMS_SQL. I had to go back to an older version of APEX (I grabbed one from our previous 12.1 version of the database, but of course you could just download an older version of APEX from the previous versions download page on Oracle.com). I’d suggest anything in the 5.0 timeframe.

Here’s what I finally ended up with in my database after the now successful 18.1 upgrade from a 5.1 previous version after running the correct WWV_DBMS_SQL code. Rather hilariously, the error that ended my previous upgrade attempt where it appeared that the install failed because it couldn’t drop the WWV_DBMS_SQL package appears to not actually be the case because I still have the WWV_DBMS_SQL package in my database:

select owner, status, object_name, object_type
  from dba_objects
 where object_name like 'WWV_DBMS_%'
   and owner='SYS';
OWNER   STATUS    OBJECT_NAME                OBJECT_TYPE    
SYS     VALID     WWV_DBMS_SQL               PACKAGE        
SYS     INVALID   WWV_DBMS_SQL               PACKAGE BODY   
SYS     VALID     WWV_DBMS_SQL_APEX_050100   PACKAGE        
SYS     VALID     WWV_DBMS_SQL_APEX_050100   PACKAGE BODY   
SYS     VALID     WWV_DBMS_SQL_APEX_180100   PACKAGE        
SYS     VALID     WWV_DBMS_SQL_APEX_180100   PACKAGE BODY   

6 rows selected.

Rows 1 and 2 came from the 12.1 version of APEX that shipped with the database, rows 3 and 4 were from the shipped version of 12.2 APEX and rows 5 and 6 are from the 18.1 version of APEX.

At this point, I can now clean up the database by dropping the WWV_DBMS_SQL and WWV_DBMS_SQL_APEX_050100 packages from the database.

Of course, I’ve asked Oracle Support to log a bug against the APEX install script. I’ll update this post with the bug number once Oracle Support is back online (they are doing upgrades this weekend, so Support has been offline a bit… Hmmm… Maybe they should be using #ThickDB with Edition-Based Redefinition to do live updates!)

Happy APEXing!

Rich

Advertisements

Beware: Oracle RMAN Password Only Encrypted Compressed Backups to Tape Might Not Decrypt

So, a few weekends ago I had a BIG scare. I was moving an Oracle Database Appliance from one data center to another. Oracle said the only supported method to do so was to wipe the machine back to bare metal and rebuild it in the new data center with all the new IP addresses and network settings. To prepare for this I did a cold backup of a database to tape (really Oracle Cloud Backup) and used the following RMAN commands:

RMAN> set encryption on identified by ******************* only;
RMAN> backup device type sbt incremental level 0
      tag '2018_06_29_SERVER_MOVE' force as compressed backupset database;

Unfortunately, when it came time to read that backup from Oracle Cloud Backup (our configured sbt device), I got the following error:

ORA-19913: unable to decrypt backup

As I worked through the issue with Oracle Support for a very long time I eventually started working with some support folks who said the following:

“I’ve seen issues in the past when you do a password only encrypted compressed backup to tape where it can’t be decrypted. You shouldn’t do that.”

I think this is potentially one of the biggest bugs in the history of Oracle (if you can’t trust a backup then something is very very wrong!) if the issue is really there… I’ve yet to be able to do any additional testing of this, but figured I’d give folks a warning.

As an aside, I did take a disk backup before the tape backup and then copied the disk backup to a mounted NFS drive. I was able to move that disk backup over to the server and restore from it, so no customer data was harmed during the server move.


I’ll be speaking at KScope18!

ODTUG
Today I got an email that began like this:

Dear Richard,

Congratulations on being selected to speak at the premier Oracle user group conference—ODTUG Kscope18. We had more than 1,000 abstracts submitted this year, making this selection a very challenging process.

The following abstract has been accepted for presentation at ODTUG Kscope18, June 10-14 in Orlando, Florida. If you submitted multiple abstracts, you will receive multiple emails with the status of each abstract.

Texas Racing Commission: Lessons Learned from Migrating to Oracle Cloud

——–

I also had another presentation that got accepted as a possible alternate:  Entity Relationship Modeling in the Age of Agile Development.

Hopefully I’ll see some of you at KScope in Orlando!

Remember to use code “insum” at checkout to get a discount off your registration.


Today C2 Consulting and Insum Solutions Merged

Capture

Today is my first day as Director of Consulting Services at Insum Solutions! I’m really excited to be part of the Insum team. Insum has been a (the?)  leader in the APEX consulting services space for many years. C2 and Insum have had very similar philosophies and execution styles. I’m really looking forward to working with the new (much bigger) team.

https://www.insum.ca/insum-and-c2-consulting-unite-to-better-serve-the-american-market/


I’ll be speaking at AOUG on Friday December 15th

IMP_web001

I’ll be speaking, but if you come, you’ll be building!

You’ve heard about the Internet of Things (IoT). C2 Consulting has put together a hands on lab where you’ll get to build an IoT thermostat from electronic components and hook it up to to REST components in an Oracle Database and then control everything from an APEX application.

You can register for the event at this link or at this url if that link doesn’t work for some reason:
https://docs.google.com/forms/d/e/1FAIpQLSdKP0TZ8KraEuAoSM1Ad0FSqkzBIP5QMCuc4uItKneeG7nBNA/viewform?c=0&w=1

This is the lab we’ll be working through and it’s pretty awesome (and award winning!): https://concept2completion.com/iotemp

Here’s some details about the when and where:

Event Timing: Friday, December 15th from 11 am to 1:30 PM
Event Address: National Instruments at 11500 North Mopac Expressway, Building C, Rooms 1S13-1S15.
Parking available in the garage for building C.

If you are in the Austin area on Friday December 15th hopefully I’ll see you there.


Using Oracle VirtualBox (or the Oracle Cloud) to Build a DBA Practice Environment

When I get some time I’ll try to change this into a series of blog posts, but for right now here’s a 141 page pdf file that covers the following in a step by step manner:

  • Install and Oracle VirtualBox
  • Create an Oracle Linux Server
  • Install and Configure Oracle Grid Infrastructure for a Standalone Server (with special ‘Rich Soule’ start and stop scripts for using ACFS file systems)
  • Install Oracle 12cR2
  • Create a Multitenant Database
  • Install and Configure Apache/Tomcat/ORDS
  • Install and Configure Oracle Application Express

You can find the pdf here: Using the Oracle Cloud (or Oracle VirtualBox) to build a DBA practice environment.


Oracle Database 12cR2 is here!

oracledatabase12cr2

It’s 15 days early (Oracle Support had said that it would be here on Feb 15th) which means that I’ll be able to start on my ODTUG presentation 15 days earlier than I thought.

Go to http://eDelivery.oracle.com to get the latest and greatest Oracle Database.

Happy DBAing!