Sokrates on Oracle

Archive for the ‘bugfighting’ Category

ORA-54012 oddity and a workaround

Posted by Matthias Rogel on 3. September 2012

sokrates@11.2 > create package p is
2 function func return int deterministic;
3 end p;
4 /

Package created.

sokrates@11.2 > create table t(
2 i int,
3 func as ( p.func() )
4 );
func as ( p.func() )
*
ERROR at line 3:
ORA-54012: virtual column is referenced in a column expression

oops ? what’s wrong ?

http://docs.oracle.com/cd/E11882_01/server.112/e17766/e53000.htm#sthref13803
says

ORA-54012: virtual column is referenced in a column expression
Cause: This virtual column was referenced in an expression of another virtual column

which is not true: there is no “another virtual column”, there is only one virtual column.
It seems, I cannot name a virtual column the same as its generating function – at least in a “create table”, we have to split this “create table” in a “create table” and an “alter table”.

So

sokrates@11.2 > create table t(
2 i int,
3 fun as ( p.func() )
4 );

Table created.

sokrates@11.2 > alter table t rename column fun to func;

Table altered.

So we end up in a table for which the SQL generated by dbms_metadata cannot be replayed, see:

sokrates@11.2 > select dbms_metadata.get_ddl('TABLE', 'T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "SOKRATES"."T"
   (    "I" NUMBER(*,0),
        "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

sokrates@11.2 > drop table t purge;

Table dropped.

sokrates@11.2 > REM copy & and paste the above DBMS_METADATA - output
sokrates@11.2 > CREATE TABLE "SOKRATES"."T"
  2   (    "I" NUMBER(*,0),
  3        "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
  4   ) SEGMENT CREATION DEFERRED
  5  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  6  TABLESPACE "USERS";
      "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
      *
ERROR at line 3:
ORA-54012: virtual column is referenced in a column expression

Posted in 11.2, Allgemein, Bug, bugfighting | Leave a Comment »

If you want to sleep well …

Posted by Matthias Rogel on 25. January 2012

… and you are a DBA and you use ASM and you happen to run 11.2 ( but not yet  11.2.0.3 ) …

then, you probably want to apply patch 10422126  ( see http://sve.to/2011/12/22/database-11-2-bug-causes-huge-number-of-alert-log-entries/ and MOS Doc ID 1274852.1 )

I ( and our storage admin ) didn’t sleep well last night 😦

The huge number of logging entries Svetoslav is talking about started for us at 6 pm yesterday, generating about 500 MB / minute.

....
WARNING: Read Failed. group:2 disk:1 AU:98945 offset:1474560 size:8192
path:/dev/oracleasm/disks/RAID5_SPB
 incarnation:0xe9508f84 synchronous result:'I/O error'
 subsys:System iop:0x2b54bc0f8390 bufp:0x2f41b4000 osderr:0x434c5344 osderr1:0x0
....

At midnight, around 180 GB was written, so our partition holding the alert log was full. New Connections received ORA-03113.

No signs of any error in the alert of ASM, no signs of any error from the storage, no signs of weird behaviour of the database (beside extremely logging the same error over and over again)

We cleaned all log- and alert-directories, restarted the database and everything looks ok again (like the last 5 months)

Tomorrow, we will apply patch 10422126.

Posted in 11.2, bugfighting, dba | Leave a Comment »

ORA-8103s

Posted by Matthias Rogel on 9. August 2011

looking forward to Tanel Poder’s webinar this evening.
Especially, the announcement to learn something about “ORA-8103s” called my attention.
The reason is, that I was hitting some bug or bugs in 11.2 (sporadically and not reproducible ORA-8103s) last November and working with support on this since then (issue has status “Development working” since some weeks now)
They used to occur several times per day in complex queries (selects in batch jobs).
Usually, the following workaround was helpful: wait for a minute, then issue the same select again.
So, we “patched” the batch jobs they occurred in, set parameter event to
‘8103 trace name errorstack forever, level 10’
and uploaded tons (dozens of GBs) of tracefiles to support.
In the meantime, we applied some patches concerning other issues and monitored the number of ORA-08103’s per day via
with days as
(
select date'2010-11-19' + level as day
from dual
connect by date'2010-11-19' + level <= sysdate
)
select
days.day,
count(distinct x.message_text) as count_08103s_per_day
from x$dbgalertext x, days
where x.message_text(+) like '%ORA-08103%'
and trunc(x.originating_timestamp(+)) = days.day
group by days.day
order by days.day

After ugrading to 11.2.0.2.2 via Patch 11724916, this number went strongly down, but we still had issues from time to time with a false ORA-08103.

Today I noticed that since applying Patch 9285259 we did hit no ORA-08103 anymore !

Coincidence ? I don’t know

Update August 28th:
I was wrong, false ORA-08103s have not disappeared yet, we encountered some of them last week, re-activated the SR (which is open unsolved now since last November …).
The reason that I thought it had disappeared was that I didn’t see it mentioned in the alert.log anymore, however, since I had unset event, it was not logged there.

Posted in 11.2, Bug, bugfighting | Leave a Comment »

My Constraints are checked again, now – how to validate them ?

Posted by Matthias Rogel on 12. June 2011

I upgraded to 11.2 in production some days after it was released in September 2010.

Since them I did hit a number of bugs, got some of them fixed with Oracle Support and are still working with them on a number of others.

The most serious for me ( because my data was not validated anymore in a number of SQLs – and I care about my data ) I wrote about on

https://marogel.wordpress.com/2011/03/27/prevent-validation-of-check-constraints-in-11-2-0-2/

Thanks t

Timo Raitalaakso I was informed about an available patch 9285259 for “Bug 9285259: MERGE IGNORES CHECK CONSTRAINT”

It did install without issues on test-db and I verified successfully that the software was really patched, I could not cheat again with merge.

So today (Sunday) I patched production in 5 minutes. Good news: my constraints are checked again !

Bad news: I now have to validate each of them manually – did my data run into inconsistencies ?
How to achive that ? No word on Oracle Support about that. Seems to me, they don’t really care much about the consistency of the data of their clients.

My first idea was something like this (pseudo-code)


for c in (
select owner, table_name, search_condition, constraint_name
from dba_constraints
where constraint_type='C'
) loop
execute immediate
'select count(*) from ' ||
c.owner || '."' || c.table_name ||
'" where not (' || c.search_condition || ') '
into n;
if n > 0 then
dbms_output.put_line(
c.owner || ', ' || c.table_name || ', ' || c.constraint_name || ': ' ||
n || 'exceptionally rows - you may want to have a deeper look onto these');
end if;
end loop;

However, the optimizer of course takes a shortcut – using the constraint:


11.2.0.2 > create table t ( i int check( i != 3 ));
Table created.
11.2.0.2 > insert into t values(2);
1 row created.
11.2.0.2 > merge into t
2 using ( select rowid r from t) s
3 on (t.rowid = s.r)
4 when matched then
5 update set i = 3;
1 row merged.
11.2.0.2 > set autotr on explain
11.2.0.2 > select i from t where not (i != 3);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1322348184
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T | /1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("I"=3)
Note
-----
- dynamic sampling used for this statement (level=2)

See the
”  1 – filter(NULL IS NOT NULL)”
using the check constraint.

So my second idea was to use lnnvl :

11.2.0.2 > select i from t where lnnvl(i != 3);
I
----------
3
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LNNVL("I"<>3))
Note
-----
- dynamic sampling used for this statement (level=2)

well, this one seems to work – for this case

But it doesn’t work for all cases – see:

11.2.0.2 > create table s( i int check ( i in (3, 4) ));
Table created.
11.2.0.2 > insert into s values(3);
1 row created.
11.2.0.2 > merge into s
2 using (select rowid r from s) p
3 on (p.r = s.rowid)
4 when matched then update
5 set i = 2;
1 row merged.
11.2.0.2 > commit;
Commit complete.
11.2.0.2 > select i from s;
I
----------
2
11.2.0.2 > select search_condition, validated, status from user_constraints where table_name='S';
SEARCH_CONDITION VALIDATED
STATUS
-------------------------------------------------------------------------------- -------------
--------
i in (3, 4) VALIDATED
ENABLED
11.2.0.2 > select * from s where lnnvl(i in (3, 4));
select * from s where lnnvl(i in (3, 4))
*
ERROR at line 1:
ORA-13207: incorrect use of the [LNNVL] operator

so now, my question is still holds:

how can I validate all my check constraints manually without using clever optimizer ?

Seems to me, I have to disable / enable each of them.

Enabling with validate of course.

And not in production of course, but on a clone DB.

Update on Monday:
ok, tests show there is no need to disable them before call enable validate.
So, the following script to validate all in-doubt check-constraints of a list of owners suffices:

create table exceptions(row_id urowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
declare
check_cons_violated exception;
pragma exception_init(
check_cons_violated,
-2293 -- "cannot validate (%s.%s) - check constraint violated"
);
vers_obj_cant_be_altered exception;
pragma exception_init(
vers_obj_cant_be_altered,
-20171 -- "WM error: Versioned objects cannot be altered."
);
begin
for c in (
select *
from dba_constraints
where constraint_type = 'C'
and owner in (....)
) loop
begin
execute immediate
'alter table "' || c.owner ||
'"."' || c.table_name ||
'" enable validate constraint "' ||
c.constraint_name ||
'" exceptions into exceptions';
exception when check_cons_violated or vers_obj_cant_be_altered then null;
end;
end loop;
end;
/
select * from exceptions;

Posted in 11.2, Allgemein, bugfighting | Leave a Comment »