Oracle PatchSet 11.2.0.4 upgrade issues ORA- PLS-

May 15, 2014 by 4 Comments 

Below is a list of issues (ORA- and PLS- errors) I encountered after upgrading Oracle 11gR2 Database Enterprise Servers to the latest Patchset 11.2.0.4.
The upgrade mostly done:
– from 11.2.0.3.6-8
– to 11.2.0.4.1 (including Patchset Update PSU 1)
– on platforms: HP-UX Itanium, Linux

ORA-01979: missing or invalid password for role

Problem: Roles with passwords are failing after Oracle database Patchset upgrade

Solution: Recreate a role with a new password or without it.
Example:
alter role REPORTING_RW not identified;

ORA-01791: not a SELECTed expression

Problem: SQL having different number of columns in DISTINCT and ORDER BY clauses failing after Oracle database upgrade but it was working on previous release before last Patchset 11.2.0.4 since developers happily used an Oracle Bug that was fixed in 11.2.0.4. So the correct behavior is on 11.2.0.4 and not on older versions.
SQL> select distinct sal, empno from scott.emp order by deptno;
select distinct sal, empno from scott.emp order by deptno
*
ERROR at line 1:
ORA-01791: not a SELECTed expression

Solution: include all ORDER BY columns into DISTINCT clause

PLS-00306: wrong number or types of arguments in call to ‘SYNCRN’

Another bug after upgrade, it’s happening for some tables when an application updates certain columns with CLOB type.
Example is below:
-- DEV_COMMENTS is CLOB type
UPDATE BUG  SET DEV_COMMENTS = 'ABC' where bug_id=1460 ;
Commit ;

ORA-00604: error on recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SYNCRN'
ORA-06550: line 1, column 7:
PL / SQL: Statement ignored

Solution: There are 2 solutions at the moment:

a) Apply Oracle patch 17501296
OR
b) Recreate ctxsys.syncrn procedure as below:

connect / as sysdba
alter session set current_schema=CTXSYS;
create or replace procedure syncrn (
ownid IN binary_integer,
oname IN varchar2,
idxid IN binary_integer,
ixpid IN binary_integer,
rtabnm IN varchar2,
srcflg IN binary_integer,
smallr IN binary_integer
)
authid definer
as external
name "comt_cb"
library dr$lib
with context
parameters(
context,
ownid ub4,
oname OCISTRING,
idxid ub4,
ixpid ub4,
rtabnm OCISTRING,
srcflg ub1,
smallr ub1
);
/

ORA-00600 [rwoirw: check ret val] on HP-UX

Problem: Microstrategy application failing with:
ORA-00600: internal error code, arguments: [rwoirw: check ret val], [], [], [], [], [], [], [], [], [], [], []

Solution:
a) Install Patch 14275161: ORA-600 [RWOIRW: CHECK RET VAL] ON CTAS – this patch is available for HP-UX Itanium
OR
b) Upgrade Oracle database to first release containing the fix : 12.1.0.1

So that was a list of issues (ORA- and PLS- errors) I encountered after Oracle Database Enterprise Servers upgrade to the latest Patchset 11.2.0.4.
I’ll probably add more issues if they will come up on this Oracle Patchset.

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

4 Responses to “Oracle PatchSet 11.2.0.4 upgrade issues ORA- PLS-”
  1. Hello Kirill, Oracle Real Application Testing helps you to analyse and validate the differences between your current DB version and the version with the latest patchset. It increases DBA productivity and lowers the risks with DB changes. It is also a huge benefit when upgrading to Oracle 12c, for O/S patching or hardware consolidation:
    More info can be found on: http://ow.ly/wRVAa

    Let me know if you need more info.
    Regards
    Gerald

  2. Joel says:

    After running the 11.2.0.4 DBUA in -silent mode we’ve encountered some databases have passwords expired and some accounts locked but passwords not expired.

  3. Kelly Cox says:

    Kirill,

    Thanks for your post – it was the only clue we could find to a similar problem we had with roles.

    Our upgrade (on only one grid) caused the CONNECT and RESOURCE roles to fail, even though they were NOT password-protected. (And really, CONNECT and RESOURCE!) We had the same error, and it resolved when we issued ALTER ROLE NOT IDENTIFIED.

    Even more troublesome was that Oracle Support did not have any answers and in fact filed a new bug for it.

  4. Adel says:

    Hi Kelly,

    This happened to us as well in 2 instances. We have a list of roles that are password protected, and one UNPROTECTED role. This unprotected role becomes password protected after upgrade to 11.2.0.4. The weird thing is that I was able to enable this role with the password of other protected roles. My question was: Does Oracle grabs the password from other roles, and use it to protect the unprotected role? I thought this would be almost impossible, then I had an assumption that if I used any password, this role gets enabled. Unfortunately it was too late to test this.

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email