RE: Oracle IP and Unwrapping PL/SQL Code

From: Matthew Parker <dimensional.dba_at_comcast.net>
Date: Wed, 18 Oct 2017 08:16:59 -0700
Message-ID: <016301d34824$258d8310$70a88930$_at_comcast.net>



What’s the bug number and what was the last specific ORA-00600 you got?    

Yes always good to have a real backup before some major operation.

More specifically anytime there is corruption of some sort it is always good to take a backup of the corrupted database before you restart restore operations as sometimes the corrupted database is helpful if there are problems with the actual backup.    

Matthew Parker

Chief Technologist

Dimensional DBA

425-891-7934 (cell)

D&B 047931344

CAGE 7J5S7
<mailto:Dimensional.dba_at_comcast.net> Dimensional.dba_at_comcast.net

<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew Parker's profile on LinkedIn

<http://www.dimensionaldba.com/> www.dimensionaldba.com
   

From: Bill Ferguson [mailto:wbfergus_at_gmail.com] Sent: Wednesday, October 18, 2017 8:00 AM To: Matthew Parker <dimensional.dba_at_comcast.net> Cc: Ryan January <rjanuary_at_gmail.com>; Stefan Knecht <knecht.stefan_at_gmail.com>; rob_at_oraclewizard.com; oracle-l-freelists <oracle-l_at_freelists.org>; Rumpi Gravenstein <rgravens_at_gmail.com> Subject: Re: Oracle IP and Unwrapping PL/SQL Code  

For a little background, let me start with the environment. (and the entire story is long, but not as long as the 16 page SR when printed out).

This was a 12.1 database running the multi-tenant option (only one pluggable database), on a Windows server 2012 R2 machine. I was also running Apex 5.0.3.

On May 2nd, I started out attempting to upgrade Apex to 5.1 (the newest one in May). That failed when somehow attempting to upgrade Spatial. So I filed my initial SR, and was finally told several days later that they (the Apex support team) were unable to fix the 'simple' problem and to upgrade to either the latest version of 12.1, or even better, to 12.2 and the problem would be fixed. So, since my RMAN backups ahd already cycled out (only keep for three days), and the SysAdmin's cold backup of the system had been replaced with a backup of the corrupted database, I had no choice except to proceed with the database upgrade as well.

I have never had a successful 'upgrade', but in this case I had no option since I couldn't export the data (that nasty TYPE datatype problem appears all over the place), so I went with the 12.2 upgrade option. It started out okay, then died with the dreaded ORA-0600 error, so I had to file my 4th SR (by this time I'd already gone through 3 SR's, all closed by Support as being dependent on another group of Support, and then this was finally confirmed as a bug).

After lots of back and forth with Support, they finally accepted a Webex to view the problem themselves, and their technician confirmed that what I had been saying for two months was correct, and that their instructions would not work on upgrading the root database, the PDB$SEED database, and my pluggable database (just the standard, legally allowed one pluggable configuration).

Then the bug report was updated with confirmation that was a verified bug by one of their experienced technicians. About a month later, a patch was finally available, so I installed the patch per their instructions, and then then tried to continue the upgrade process, which finished upgrading the root container and PDB$SEED, but died with an ORA-0600 again on my pluggable database. After another month, the process was repeated once again with another patch (appropriately named the exact same as the original patch), and wound up with similar results, but at least in a different place.

I should note here that before I applied the second patch, I specifically asked if I should stop the database and delete all log and alert files, so tracking down any error messages would be easier to find, and I was told emphatically to keep them, so if needed, development could backtrack back through everything.

So after I reported back that the update failed once again, and supplied only the newest log files from that day along with the upgrade log files, they said that wasn't enough and that they needed all files that contained the patch number anywhere inside them. I was finally able to run a Windows commend similar to grep and it found over 11,000 files, so rather than upload all of the individual files into zip, I simply zipped the entire directory and gave that to Support. They complained about to many files, as maybe 1,000 -2,000 didn't contain the patch number, but they finally accepted it the next day after I gave them the explanation. Several times I have offered to let development conduct a Webex to see exactly what I see when trying the update, but I always get told no.

So that was about two weeks ago, and the last two entries in my SR are:


Hi Bill,

I've updated the bug with the details of your update, that is a questions Development would have to answer- that being said I'm pretty sure they will need to get through their internal processes/testing to give you a clear answer. Your SR is already set at the highest severity and so is your bug so I"m hopeful that Dev will get back to us as soon as they have something for you. Please continue to be patient and I will update the SR as soon as I get an update from Dev.

Regards

  <https://support.oracle.com/epmos/adf/images/t.gif>                     

   <https://support.oracle.com/epmos/adf/images/t.gif> -----

Realistically, what are the chances of actually getting this fixed? It seems like the only problem has to do with the permissions of all objects that use (or create) a TYPE object in the pluggable database. The revokes, grants, describe's, etc. on any object that has an object of TYPE anywhere within the object definition all seem to have problems. I can see and acces the code I have in the pluggable database, but I cannot access tables or views, as I always get the "SYS.DBA_TAB_COLS has errors" message, followed by "SYS.DBA_IND_COLUMNS has errors" message. The SYS.DBA_TAB_COLS error seems to revert to SYS.DBA_TAB_COLS_V$ having an error, which is finally reported to be ORA-00904: "C"."COLLINTCOL#" being an invalid identifier.

I can't even perform an export of the data, or move it from one database to another, without getting similar errors. But at least through TOAD (I think SQL Developer behaves the same way), I can see and describe all of my functions, procedures and packages, though they won't compile because they are dependent upon tables that can't be accessed because SYS.DBA_TAB_COLS and other data dictionary objects have problems.

This problem has been going on for over 5 months now, which is basically the exact same thing as a dead database. I have been completely unable to perform any work on this database, as all of my code, tables, Apex applications, etc. are all in this database, and all I can access is the code, which is useless without everything else.

Thanks,


So, as a warning to everybody else, always take a good backup before starting anything major (sometimes even minor, as I have never had a Apex upgrade fail before). Place that backup in a completely different location so if there are problems, it doesn't wind up getting overwritten later. And coordinate with the SysAdmin to ensure the system itself is backed up in case it really gets bad.

And finally, always expect worse support responses on Windows machines. I haven't used *nix in around 14 years, but that support was always fairly quick, with a few new scripts to run, or a few files to recompile with a make file to generate a new Oracle binary. Windows though is a different horse, where development has to test, verify and then create whole new set of Oracle binaries for you, which really slows things down.  

Bill Ferguson  

On Wed, Oct 18, 2017 at 7:57 AM, Matthew Parker <dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> > wrote:

Can you give us some more details about your problem?    

Matthew Parker

Chief Technologist

Dimensional DBA

425-891-7934 <tel:(425)%20891-7934> (cell)

D&B 047931344

CAGE 7J5S7
<mailto:Dimensional.dba_at_comcast.net> Dimensional.dba_at_comcast.net

<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew Parker's profile on LinkedIn

<http://www.dimensionaldba.com/> www.dimensionaldba.com
   

From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> ] On Behalf Of Bill Ferguson Sent: Wednesday, October 18, 2017 5:30 AM To: rjanuary_at_gmail.com <mailto:rjanuary_at_gmail.com> Cc: knecht.stefan_at_gmail.com <mailto:knecht.stefan_at_gmail.com> ; rob_at_oraclewizard.com <mailto:rob_at_oraclewizard.com> ; oracle-l-freelists <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> >; rgravens_at_gmail.com <mailto:rgravens_at_gmail.com> Subject: Re: Oracle IP and Unwrapping PL/SQL Code  

I would have to fully and emphatically agree with Oracle's lack of responsiveness. I've been working with Oracle for close to 30 years now, and this year has hands down been the absolute worst dealing with Oracle Support - EVER!.

My development database has been basically "dead" for over 5 months now, leaving me completely hanging in the wind. A real long story about several distinct instances of miscommunications, the 5 different SR's being opened and closed while the problem was shifted to other groups, etc., and this is for a 24x7 Severity 1 problem. I don't even want to imagine how bad it would be if this wasn't escalated. On the plus side though, I have received two different patches (the original and an update to the original) for the problem, but neither fixed all of the problems. At least it seems now that the only remaining problem is I can view and access the code (packages, functions, etc.) in the application schemas, but I cannot access tables or views (in any schema) without getting a "SYS.DBA_TAB_COLS has errors", followed by "SYS.DBA_IND_COLUMNS has errors", which in turn seems to point to a "C.COLLINTCOL#" being an invalid identifier.

I haven't gone through the process of unwrapping the code since these are part of the data dictionary, but it seems like every data dictionary table or view that uses a "TYPE" object has this problem (I see it on numerous SYS tables). Revokes, grants, creations, etc. all seem to fail on any and all objects using a TYPE, I've reported this to Oracle several times during the course of the 5 SR's, and I am still without a usable database for development and am now facing my first ever annual Performance Plan failure.

It is no wonder many sites in my organization are dropping Oracle and migrating to Postgres instead. I personally can't, since I have so many apps in Apex, but I think the other groups are mainly using the database simply for storage and extremely basic retrieval. At least I only have a few years left before retirement, then it can be somebody elses headache. It's a shame, because I always had such great experiences with Oracle over the decades, even got Uncle Larry involved in one problem way, way back in the early 90's and he had development get me a fix within the week.

Other than insane growth, trying to have too many different products all under the same 'umbrella', the over-abundance of java code within the database, and something just plain wrong with Support this year (management, training, lack of talented staff, or something), Oracle has seem to taken a nose-dive this year compared to previous years. I was really hoping to leave behind some extremely usable app for my agency to continue using in the years following my retirement, but if this is any indication at all, I have no hope.

Bill Ferguson  

On Fri, Oct 13, 2017 at 1:20 PM, Ryan January <rjanuary_at_gmail.com <mailto:rjanuary_at_gmail.com> > wrote:

I disagree, however within reason. I've been on the receiving end of profoundly unresponsive Oracle service reps, leading to SR's that languish with no updates. The ability of tracking oracle internal PL/SQL to the origin (and subsequently a work around on the client end) has been a god-send.  

If Oracle were more responsive, I agree. When we're forced to troubleshoot Oracle code, on their behalf, I'll use almost any tool is at my disposal to gather additional data.  

On Oct 13, 2017, at 11:07 AM, Stefan Knecht <knecht.stefan_at_gmail.com <mailto:knecht.stefan_at_gmail.com> > wrote:  

I think those websites - if for ethical reasons only - shouldn't exist.  

It's fine to be able to purchase this as a service, if there is a legitimate need. But in such a public and free manner, I don't agree with it.  

-- 

-- Bill Ferguson




-- 

-- Bill Ferguson




--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 18 2017 - 17:16:59 CEST

Original text of this message