I have uploaded the material of the german presentation on Oracle Data Guard 11gr2 Whats New to the “presentation” section. It also contains 6 recorded demos using Enterprise Manager Grid Control.
Today, I received a mail from Oracle to inform me that I passed Oracle Certified Master 11g Upgrade Exam. After several weeks of after-work preparations, this is a very satisfying result. If you are OCM 10g and also interested in upgrading, you can find exam schedule and list of objectives here. Good luck.
Schedule: http://education.oracle.co.uk/html/oracle/28US/SCHED_SP_OCM11.htm
I would like to draw your attention to a particularly dangerous security vulnerability, which was recently published by David Litchfield.
How dangerous is the vulnerability?
Any database user, who has “create session” privilege, which means, who can log into the database, can use the security hole to execute any OS command in the ownership of the oracle database owner. This means, that both denial of service as well as access to all data is exposed.
Which versions are affected?
Affected are database versions 10.2.0.4 (incl. 10.2.0.4.3 containing latest security patches as of January 2010) as well as 11g (incl. 11.2.0.1).
What can I do to close this security vulernability?
You can revoke privileges from PUBLIC:
revoke execute on dbms_java from PUBLIC;
revoke execute on dbms_java_test from PUBLIC;
revoke execute on “oracle/aurora/util/Wrapper” from PUBLIC;
grant execute on sys.dbms_jvm_exp_perms to IMP_FULL_DATABASE;
grant execute on sys.dbms_jvm_exp_perms to EXP_FULL_DATABASE;
revoke execute on sys.dbms_jvm_exp_perms from PUBLIC;
If you are using a third party vendor application, you should contact your vendor to check compatibility with revoked privileges or test before implementing in production.
I just came back from the DOAG Conference 2009, the german Oracle user group conference in Nürnberg where I had a speech about RAC PreProduction Testing. I have uploaded the slides and the paper to the papers section. At this time, the presentation is available in german only.
During a 11gR1 Clusterware installation for a Single Instance Failover Cluster at a customer site, I have experienced an interesting behaviour, which was caused by an Oracle Bug.
The environment was:
The ASM instances could be started with SQL*Plus without any problems, but if the ASM instances were started by means of clusterware using srvctl (either from root, crs or oracle) the ASM instances would crash at diskgroup mount with:
ORA-07445: exception encountered: core dump [sskgds_find_rtn_hdr()+1171] [SIGBUS] [ADDR:0x2AACD701342C] [PC:0x25799DF] [Non-existent physical address] []
Oracle Support identified this behaviour as Bug 6952915, for which there are patches for Linux x86, x86_64 and Solaris Sparc64.
Oracle has released the October 2009 Patch Set Update (PSU) which contains several interesting news:
More info can be found in Metalink Note 854428.1.
Yesterday, on September 1st, Oracle released the much awaited database version 11gR2 for Linux x86 for both 32bit and 64bit platforms. The software can be downloaded via OTN: http://www.oracle.com/technology/software/products/database/index.html.
It is not very known that you can run into serious problems if you run Linux x86-32bit with a big amount of RAM installed, if using RHEL below 5. The official name for the issue is called “Low Memory Starvation”. The best solution is to use x86-64bit to be able to address the whole amount of RAM efficiently.
However, if that is not feasible, then make sure that you at least run the hugemem kernel if you use RHEL < 5. In RHEL5-32bit, the hugemem kernel is default.
A quick demonstration about what can happen if you don´t use hugemem kernel is shown here:
We realized that RMAN backup is taking more than 24 hours. Querying v$session, we find out that
one session is in ACTION "STARTED", whereas the other sessions are FINISHED.
SQL> select program, module,action
from v$session
where username = 'SYS' and program like 'rman%'
/
PROGRAM MODULE ACTION
-------------------------- --------------------------- -------------------
rman@ora-vm1 (TNS V1-V3) backup full datafile 0000078 FINISHED129
rman@ora-vm1 (TNS V1-V3) backup full datafile 0000272 STARTED16
rman@ora-vm1 (TNS V1-V3) backup full datafile 0000084 FINISHED129
rman@ora-vm1 (TNS V1-V3) rman@ora-vm1 (TNS V1-V3)
rman@ora-vm1 (TNS V1-V3) rman@ora-vm1 (TNS V1-V3) 0000004 FINISHED131
rman@ora-vm1 (TNS V1-V3) backup full datafile 0000092 FINISHED129
Then we check the SID,serial# from v$session of this session and also query the UNIX PID from v$process.spid
SQL> select sid,serial# from v$session where event like 'RMAN%';
SID SERIAL#
---------- ----------
4343 5837
We activate SQL Tracing for this session to determine its activity:
SQL> select spid from v$process where addr = (select paddr from v$session where sid = 4343); SPID ------------ 1681 SQL> begin dbms_monitor.session_trace_enable(4343,5837,true,true); 2 end; 3 /
However, no trace file gets created. Then we start tracing system calls with strace:
ora-vm1:# strace -fp 1681 attach: ptrace(PTRACE_ATTACH, ...): Operation not permitted
“Not permitted”? – Although I am connected as root?
ps -ef|grep 1681 oracle 1681 1582 0 Aug24 ? 00:00:09 [oracle] <defunct>
The linux command “ps” reports the server process as “defunct”.
ora-vm1:/usr/oracle/admin/labo/udump$ ps -ef|grep 1582 oracle 1582 21578 0 Aug24 ? 00:00:02 rman oracle/product/10.2.0/bin/rman nocatalog oracle 21663 1582 0 Aug24 ? 00:00:01 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 21665 1582 0 Aug24 ? 00:00:03 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 1681 1582 0 Aug24 ? 00:00:09 [oracle] <defunct> oracle 21691 1582 0 Aug24 ? 00:01:36 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 21695 1582 0 Aug24 ? 00:01:41 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 21793 1582 0 Aug24 ? 00:01:30 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
Next, I checked logfile /var/log/messages.1 and realized that the kernel out-of-memory killer (OOM) killed this PID because of low memory starvation.
/var/log/messages.1: Aug 24 22:32:44 ora-vm1 kernel: Out of Memory: Killed process 1681 (oracle).
I have added a new book review to my bookshelf: HOWTO Secure and Audit Oracle 10g and 11g – Ron Ben Natan
During the Hotsos Symposium 2009 Training Day, Jonathan Lewis presented a problem which appears even on current 10g/11g databases. What is especially interesting is how this issue can be diagnosed. I reproduced the problem in 11.1.0.7 and will provide the steps you can use to verify. The problem can be demonstrated best when comparing response time of an update statement for 8k blocksize and 16k blocksize.
SQL> DROP TABLE t1_8k purge; SQL> CREATE TABLESPACE DEMO8K datafile SIZE 128M blocksize 8192; TABLESPACE created. Elapsed: 00:00:07.35 SQL> CREATE TABLE mdecker.t1_8k (n1 NUMBER, n2 NUMBER) TABLESPACE DEMO8K; TABLE created. Elapsed: 00:00:00.02 SQL> INSERT INTO t1_8k SELECT TRUNC(dbms_random.VALUE(10000000,100000000)) n1, TO_NUMBER(NULL) AS n2 FROM dual CONNECT BY LEVEL <= 500000 / 500000 ROWS created. Elapsed: 00:00:06.89 SQL> BEGIN dbms_stats.gather_table_stats( ownname => 'MDECKER', tabname => 'T1_8K', estimate_percent => 100); END; / PL/SQL PROCEDURE successfully completed. Elapsed: 00:00:02.13 SQL> SELECT num_rows,blocks FROM dba_tables WHERE table_name = 'T1_8K'; NUM_ROWS BLOCKS ---------- ---------- 500000 874 Elapsed: 00:00:00.15 SQL> UPDATE t1_8k SET n2 = n1; 500000 ROWS updated. Elapsed: 00:01:09.04 SQL> COMMIT;
SQL> CREATE TABLESPACE DEMO16K datafile SIZE 128M blocksize 16384; TABLESPACE created. Elapsed: 00:00:14.75 SQL> CREATE TABLE mdecker.t1_16k (n1 NUMBER, n2 NUMBER) TABLESPACE DEMO16K; TABLE created. Elapsed: 00:00:00.03 SQL> INSERT INTO t1_16k SELECT TRUNC(dbms_random.VALUE(10000000,100000000)) n1, TO_NUMBER(NULL) AS n2 FROM dual CONNECT BY LEVEL <= 500000 / 500000 ROWS created. Elapsed: 00:00:05.51 SQL> BEGIN dbms_stats.gather_table_stats( ownname => 'MDECKER', tabname => 'T1_16K', estimate_percent => 100); END; / PL/SQL PROCEDURE successfully completed. Elapsed: 00:00:01.78 SQL> SELECT num_rows,blocks FROM dba_tables WHERE table_name = 'T1_16K'; NUM_ROWS BLOCKS ---------- ---------- 500000 436 Elapsed: 00:00:00.07 SQL> UPDATE t1_16k SET n2 = n1; 500000 ROWS updated. Elapsed: 00:20:20.89
As you can see, the update statement for the 8k blocksize table took around 69 seconds whereas the same update for the table in the 16k tablespace took more than 20 minutes. When executing oradebug short_stack, you can see that for the 16k update, the stacktrace is similar for many executions. So, a lot of time is spent in the kernel functions ktspfsrch() and ktspscan_bmb().
SQL> oradebug setospid 23668
Oracle pid: 18, Unix process pid: 23668, image: oracle@ora-vm1.intra (TNS V1-V3)
SQL> oradebug short_stack
.....ktspfsrch()+559<-ktspscan_bmb()+315 .....
SQL> oradebug short_stack
.....ktspfsrch()+559<-ktspscan_bmb()+315 .....
SQL> oradebug short_stack
.....ktspfsrch()+559<-ktspscan_bmb()+315 .....
It is important to understand that the problem is not necessarily related to the blocksize, but to the PCTFREE value. More information about this topic can be found here:
http://structureddata.org/files/jl_test_case.html
http://structureddata.org/2008/09/08/understanding-performance/
http://www.oraclealchemist.com/oracle/hey-guys-does-size-matter/
