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@xxxxxxxxxxx> Dimensional.dba@xxxxxxxxxxx
<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@xxxxxxxxx] ;
Sent: Wednesday, October 18, 2017 8:00 AM
To: Matthew Parker <dimensional.dba@xxxxxxxxxxx>
Cc: Ryan January <rjanuary@xxxxxxxxx>; Stefan Knecht <knecht.stefan@xxxxxxxxx>;
rob@xxxxxxxxxxxxxxxx; oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>; Rumpi
Gravenstein <rgravens@xxxxxxxxx>
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@xxxxxxxxxxx
<mailto:dimensional.dba@xxxxxxxxxxx> > 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@xxxxxxxxxxx> Dimensional.dba@xxxxxxxxxxx
<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@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx ;<mailto:oracle-l-bounce@xxxxxxxxxxxxx> ]
On Behalf Of Bill Ferguson
Sent: Wednesday, October 18, 2017 5:30 AM
To: rjanuary@xxxxxxxxx <mailto:rjanuary@xxxxxxxxx>
Cc: knecht.stefan@xxxxxxxxx <mailto:knecht.stefan@xxxxxxxxx> ;
rob@xxxxxxxxxxxxxxxx <mailto:rob@xxxxxxxxxxxxxxxx> ; oracle-l-freelists
<oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx> >; rgravens@xxxxxxxxx
<mailto:rgravens@xxxxxxxxx>
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@xxxxxxxxx
<mailto:rjanuary@xxxxxxxxx> > 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@xxxxxxxxx
<mailto:knecht.stefan@xxxxxxxxx> > 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
