FreeDBA.net

Maximize Availability with Oracle Flashback Database Feature

Posted on May 19, 2012 at 2:22 am by margaret hamburger | Comments Off

The calmest IT guy in the world understands what it takes to recover quickly from human errors. Learn more about Oracle Flashback recovery features and Oracle Database 11g maximum availability solutions. 

Watch the third video in this series and please remember to remain calm!

Maximize Availability with Oracle Active Data Guard

Posted on May 19, 2012 at 2:18 am by margaret hamburger | Comments Off

The calmest IT guy in the world understands what it takes to keep business data protected from downtime and eliminate idle redundancy of standby servers. Learn more about Oracle Active Data Guard and Oracle Database 11g maximum availability.

 Watch the second video in this series and please remember to remain calm!

Oracle VM 3.1 is here!

Posted on May 19, 2012 at 2:10 am by Ronen Kofman | Comments Off

Maximize Availability with Oracle Database 11g and Real Application Clusters

Posted on May 19, 2012 at 2:01 am by margaret hamburger | Comments Off

The calmest IT guy in the world understands what it takes to keep business systems available. Learn how to increase server availability with Oracle Real Application Clusters and Oracle Database 11g  maximum availability solutions.

Watch the first video in this series and please remember to remain calm!

Stuff The Internet Says On Scalability For May 18, 2012

Posted on May 19, 2012 at 12:15 am by Todd Hoff | Comments Off

It's HighScalability Time:

• 42 Billion: Netflix API Requests/Month
• Quotable quotes:
  • @commonlisp: Ideas from the talk: In Haskell laziness + thunks + garbage collection (GC) impede multicore scalability. Parallel GC is crucial.
  • @Bulldozer0: Global state is the enemy of scalability; not only in software, but in governance.
• If you've ever, as I have, felt the terror of a misplaced "rm -rf /", you'll love this story: Did Pixar accidentally delete Toy Story 2 during production? It did, the reconstruction was heroic, and parts of Toy Story 2 were lost forever.

Don't miss all that the Internet has to say on Scalability, click below and become eventually consistent with all scalability knowledge...

New On The Oracle Certification Website

Posted on May 18, 2012 at 9:11 pm by Brandye Barrington | Comments Off

Several recent improvements have been made to the Oracle Certification website to help make things easier for you to quickly find the information you need. 

NEW TRACK PAGES

Track pages are the pages that describe the requirements to earn each Oracle certification. There is a track page for each certification path that shows you how to achieve your certification - step-by-step. Here you also find links to the courses that fulfill the training requirement (for those certification paths that require training) as well as a link to each exam required in your path, showing helpful exam information such as exam topics, cost in local currency, etc.

We have recently redesigned our track pages based on feedback from our customers to make them easier to follow and more comprehensive. At the top of each track page is a section that gives you an idea of what can be expected of a person who holds this certification, a broad idea of what is covered in the exam, and what types of skills will be covered in your exam preparation. 

For certifications that have logical next steps (such as OCA to OCP, or add-on OCE certifications), our track pages now suggest which credentials would likely be most helpful to you in your technical field. 

Our track pages now include any applicable upgrade paths directly within the track pages itself (i.e. no more hunting through the website for upgrade paths and exams). To upgrade from an earlier version of a certification to the current one, look for the path on the track page under the 'Upgrade An Existing Certification' tab.

Click on each of the images below for a larger version: 

NEW CERTIFICATION FINDER

Have you ever had difficulty knowing exactly where to look on the Oracle Certification web pages to find the certification you are looking for, or to see what certifications are available in a certain area?

With the Oracle Certification Program's significant growth over the past few years, the number of certifications has expanded exponentially - sometimes making it challenging to find the certifications you are looking for. With this in mind, we're pleased to have released our new Certification Finder.

This powerful new tool will allow you to search for certifications either by Product Area (as was available in the past), or by Job Role (new). Users can then drill down further, narrowing their search by product group and by product. Users can also start with the product area or pillar rather than job role and drill down in the same fashion - narrowing their results to only those certifications in which they are interested.

Click on the image below for a larger version:

持续学习

Posted on May 18, 2012 at 4:29 pm by DBA Notes | Comments Off

by Fenng@dbanotes.net

知乎上有人说起「科班出身」这个话题，我大致写了一个回复。其实也是前几天我和前同事们分享提到的观点。很多人认为「科班出身」更加专业，而有些野路子半路出家也能做差不多的事情来，于是大家都疑惑，真的是这些人天赋异禀？

以计算机技术来说，大学本科学习的时间，不过四年而已，如果投入工作后，不能持续学习不能持续实践不能开拓思维的话，那么他的专业背景很可能停留在大学毕业那一刻而不再增长。而有些非科班的人，尽管起步阶段的积累不如科班的多，但他可能持续数年依然在学习实践、不停的开拓智域，那么你说，学了四年的人能和学了十年的人相比么？

如果读过《异类》这本书中，应该会对其中提到的「一万小时定律」，要成为某个领域的专家，需要一万小时的训练。大意也是如此。你想尽快成为众人仰慕的牛人，那么只有每天花更多的时间，下更大的功夫。那些牛人也不是一夜之间冒出来的，都是数年积累才可厚积薄发。就拿做产品来说，国内被人津津乐道的人物中，无论是搜索时代的俞军还是移动互联网时代的张小龙，最大的特点就是都够勤奋，肯下功夫。

无他，持续学习尔。跟是否科班没什么关系。只是这个环境中有耐心有恒心的人越来越少了。

--EOF--

---

最近文章|Recent Articles

• 小心，中兴无线上网卡会让 Mac 无法启动(附解决办法)
• 创业团队成员的「挑战」以及「成长」的问题
• 操作系统的迷思
• 从 Solaris 到 illumos - 关于技术权力抗争、开源以及梦想的故事

本站赞助商：豆瓣网(Douban.com)

评论数(0)|添加评论 | 最近作者还说了什么? Follow Fenng@Twitter

DBA Notes 理念: 用简约的技术取得最大的收益...

How do I compare statistics?

Posted on May 18, 2012 at 4:01 pm by Maria Colgan | Comments Off

This question came up recently when I was helping a customer migrate a large data warehouse to Oracle Database 11g. Prior to the upgrade, they were using an ESTIMATE_PERCENT of 0.000001, the smallest possible sample size allowed, when they gathering statistics on their larger tables. When I asked why they picked such a tiny sample size they said it was because they needed statistics to be gathered extremely quickly after their daily load both at the partition and at the global level.

Since these large tables were partitioned, I thought they would be an excellent candidate for incremental statistics. However, in order to use incremental statistics gathering you have to let the ESTIMATE_PERCENT parameter default to AUTO_SAMPLE_SIZE. Although the customer saw the benefit of using incremental statistics they were not keen on changing the value of ESTIMATE_PERCENT. So I argued that with the new AUTO_SAMPLE_SIZE in Oracle Database 11g they would get statistics that were equivalent to a 100% sample but with the speed of a 10% sample.  And since we would be using incremental statistics we would only have to gather statistics on the freshly loaded partition and the global statistics (table level statistics) would be automatically aggregated correctly.

So with much skepticism, they tried incremental statistics in their test system and they were pleasantly surprised at the elapse time. However, they didn’t trust the statistics that were gathered and asked me, “how do I compare the statistics I got with AUTO_SAMPLE_SIZE  to the statistics I normally get with an ESTIMATE_PERCENT of 0.000001?”

The answer to that was easy, ‘use DBMS_STAT.DIFF_TABLE_STATS’. Although the answer was easy, it wasn’t an easy process to help them to work out how to use the DBMS_STAT.DIFF_STATS functions correctly. In this post I hope to share some of the gotchas you many encounter using DIFF_STATS that are not so obvious from the description of the functions in our documentation or our whitepaper on 'Upgrading to Oracle Database 10g: What to Expect From The Optimizer'.  Let’s take the SALES table from the SH schema as an example. The SALES table has 28 partitions and 918843 rows.

First we will gather statistics on the SALES table with their original setting for ESTIMATE_PRECENT, 0.00001.

These statistics can now be backed up into a newly created stats table.

Now that we have an export of the statistics from the manually specified ESTIMATE_PERCENT run, let’s re-gather statistics on the SALES table using the default, AUTO_SAMPLE_SIZE.

So, we are now ready to compare the two sets of the statistics for the SALES table using the DBMS_STAT.DIFF_TABLE_STATS function. There are actually three version of this function depending on where the statistics being compared are located;

• DBMS_STAT.DIFF_TABLE_STATS_IN_HISTORY
• DBMS_STAT.DIFF_TABLE_STATS_IN_PENDING
• DBMS_STAT.DIFF_TABLE_STATS_IN_STATTAB

 In this case we will be using the DBMS_STAT.DIFF_TABLE_STATS_IN_STATTAB  function. The functions also compare the statistics of the dependent objects (indexes, columns, partitions) and will only displays statistics for the object(s) from both sources if the difference between the statistics exceeds a certain threshold (%). The threshold can be specified as an argument to the function; the default value is 10%. The statistics corresponding to the first source will be used as the basis for computing the differential percentage.

The DBMS_STAT.DIFF_TABLE_STATS functions are table functions so you must use the key word table when you are selecting from them, otherwise you will receive an error saying the object does not exist. 

The table function returns a report (clob datatype) and maxdiffpct (number). In order to display the report correctly you must use the set long command to define the width of a long so the report can be displayed properly.

How that we know how to generate the report, let’s look at what it says,

The report has three sections. It begins with a comparison of the basic table statistics. In this case the table statistics (number of rows, number of blocks etc) are the same. The results so far are to be expected since we can accurately extrapolate the table statistics from a very small sample. The second section of the report examines column statistics.

Each of the columns where the statistics vary is listed (AMOUNT_SOLD, CUST_ID, TIME_ID) along with a copy of the statistics values from each source. Source A is the STATTAB, which in our case is the ESTIMATE_PERCENT of 0.000001. Source B is the statistics currently in the dictionary, which in our case is the AUTO_SAMPLE_SIZE set. You will notice quite a significant difference in the statistics, especially in the NDV (number of distinct values) and the minimum and maximum values for each of the columns. If we compare these results with the actual number of distinct values for these (below), we see that the statistics reported by source B, the AUTO_SAMPLE_SIZE are the most accurate.

The report then goes on to list the column statistics differences for each of the partitions. In this section you will see that the problems occur only in the AMOUNT_SOLD, CUST_ID columns. The SALES table is range partitioned on TIME_ID, so there is a limited number of TIME_IDs in each partition, thus the percentage difference between the results on this level is not enough to meet the threshold of 10%.

Finally the report looks at the index statistics.In this case the index statistics were different but they were not greater than 10% different so the report doesn't show them.

Along with the report the function returns the MAXDIFFPCT. This is the maximum percentage difference between the statistics. These differences can come from the table, column, or index statistics. In this case the MAXDIFFPCT was 96%!

So the statistics were significantly different with AUTO_SAMPLE_SIZE but the proof is in the pudding. We put the new statistics to the test and found that the Optimizer chose much better execution plan with the new statistics. So much so they were able to remove a ton of hints from their code that had been necessary previously due to poor statistics. You can't ask for anything better than that!

New Smart Card Features for Oracle Desktop Virtualization

Posted on May 18, 2012 at 9:12 am by Chris Kawalek | Comments Off

Sun Ray Software 5.3 and Oracle Virtual Desktop Infrastructure 3.4 now include a completely new and improved smart card software stack for Sun Ray 3 Series Clients and clients installed with Oracle Virtual Desktop Client 3.1. The new smart card software stack allows end users to quickly and easily login to their virtual desktops and applications, and offers users an even more secure virtual desktop environment by providing the capability of using two-factor authentication - what you have (your smart card) and what you know (your PIN). Some partner products can also add biometric authentication in conjunction with PIN authentication, thus providing three-factor authentication, making Sun Ray Clients one of the most secure virtual desktop thin client solutions in the industry.

Oracle安全 – SCN的可能最大值与耗尽问题

Posted on May 17, 2012 at 11:38 pm by Oracle Life | Comments Off

作者：eygle 发布在 eygle.com

在2012年第一季度的CPU补丁中，包含了一个关于SCN修正的重要变更，这个补丁提示，在异常情况下，Oracle的SCN可能出现异常增长，使得数据库的一切事务停止，由于SCN不能后退，所以数据库必须重建，才能够重用。

我曾经在以下链接中描述过这个问题：
http://www.eygle.com/archives/2012/03/oracle_scn_bug_exhaused.html

Oracle使用6 Bytes记录SCN，也就是48位，其最大值是：

SQL> col scn for 999,999,999,999,999,999
SQL> select power(2,48) scn from dual;

                     SCN
------------------------
     281,474,976,710,656

Oracle在内部控制每秒增减的SCN不超过 16K，按照这样计算，这个数值可以使用大约544年：

SQL> select power(2,48) / 16 / 1024 / 3600 / 24 / 365 from dual;

POWER(2,48)/16/1024/3600/24/365
-------------------------------
                     544.770078

然而在出现异常时，尤其是当使用DB Link跨数据库查询时，SCN会被同步，在以下链接中，我曾经描述过此问题：
http://www.eygle.com/archives/2006/11/db_link_checkpoint_scn.html

一个数据库当前最大的可能SCN被称为"最大合理SCN"，该值可以通过如下方式计算：

col scn for 999,999,999,999,999,999
select
(
    (
        (
            (
                (  
                    (
                        to_char(sysdate,'YYYY')-1988
                    )*12+
                to_char(sysdate,'mm')-1
                )*31+to_char(sysdate,'dd')-1
            )*24+to_char(sysdate,'hh24')
        )*60+to_char(sysdate,'mi')
    )*60+to_char(sysdate,'ss')
) * to_number('ffff','XXXXXXXX')/4 scn
from dual
/

这个算法即SCN算法，以1988年1月1日 00点00时00分开始，每秒计算1个点数，最大SCN为16K。
这个内容可以参考如下链接：
http://www.eygle.com/archives/2006/01/how_big_scn_can_be.html

在CPU补丁中，Oracle提供了一个脚本 scnhealthcheck.sql 用于检查数据库当前SCN的剩余情况。
该脚本的算法和以上描述相同，最终将最大合理SCN 减去当前数据库SCN，计算得出一个指标：HeadRoom。也就是SCN尚余的顶部空间，这个顶部空间最后折合成天数：
以下是这个脚本的内容：

Rem
Rem $Header: rdbms/admin/scnhealthcheck.sql st_server_tbhukya_bug-13498243/8 2012/01/17 03:37:18 tbhukya Exp $
Rem
Rem scnhealthcheck.sql
Rem
Rem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.
Rem
Rem    NAME
Rem      scnhealthcheck.sql - Scn Health check
Rem
Rem    DESCRIPTION
Rem      Checks scn health of a DB
Rem
Rem    NOTES
Rem      .
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    tbhukya     01/11/12 - Created
Rem
Rem

define LOWTHRESHOLD=10
define MIDTHRESHOLD=62
define VERBOSE=FALSE

set veri off;
set feedback off;

set serverout on
DECLARE
 verbose boolean:=&&VERBOSE;
BEGIN
 For C in (
  select
   version,
   date_time,
   dbms_flashback.get_system_change_number current_scn,
   indicator
  from
  (
   select
   version,
   to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') DATE_TIME,
   ((((
    ((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +
    ((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
    (((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +
    (to_number(to_char(sysdate,'HH24'))*60*60) +
    (to_number(to_char(sysdate,'MI'))*60) +
    (to_number(to_char(sysdate,'SS')))
    ) * (16*1024)) - dbms_flashback.get_system_change_number)
   / (16*1024*60*60*24)
   ) indicator
   from v$instance
  )
 ) LOOP
  dbms_output.put_line( '-----------------------------------------------------'
                        || '---------' );
  dbms_output.put_line( 'ScnHealthCheck' );
  dbms_output.put_line( '-----------------------------------------------------'
                        || '---------' );
  dbms_output.put_line( 'Current Date: '||C.date_time );
  dbms_output.put_line( 'Current SCN:  '||C.current_scn );
  if (verbose) then
    dbms_output.put_line( 'SCN Headroom: '||round(C.indicator,2) );
  end if;
  dbms_output.put_line( 'Version:      '||C.version );
  dbms_output.put_line( '-----------------------------------------------------'
                        || '---------' );

  IF C.version > '10.2.0.5.0' and
     C.version NOT LIKE '9.2%' THEN
    IF C.indicator>&MIDTHRESHOLD THEN
      dbms_output.put_line('Result: A - SCN Headroom is good');
      dbms_output.put_line('Apply the latest recommended patches');
      dbms_output.put_line('based on your maintenance schedule');
      IF (C.version < '11.2.0.2') THEN
        dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='
                             || '24 after apply.');
      END IF;
    ELSIF C.indicator<=&LOWTHRESHOLD THEN
      dbms_output.put_line('Result: C - SCN Headroom is low');
      dbms_output.put_line('If you have not already done so apply' );
      dbms_output.put_line('the latest recommended patches right now' );
      IF (C.version < '11.2.0.2') THEN
        dbms_output.put_line('set _external_scn_rejection_threshold_hours=24 '
                             || 'after apply');
      END IF;
      dbms_output.put_line('AND contact Oracle support immediately.' );
    ELSE
      dbms_output.put_line('Result: B - SCN Headroom is low');
      dbms_output.put_line('If you have not already done so apply' );
      dbms_output.put_line('the latest recommended patches right now');
      IF (C.version < '11.2.0.2') THEN
        dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='
                             ||'24 after apply.');
      END IF;
    END IF;
  ELSE
    IF C.indicator<=&MIDTHRESHOLD THEN
      dbms_output.put_line('Result: C - SCN Headroom is low');
      dbms_output.put_line('If you have not already done so apply' );
      dbms_output.put_line('the latest recommended patches right now' );
      IF (C.version >= '10.1.0.5.0' and
          C.version <= '10.2.0.5.0' and
          C.version NOT LIKE '9.2%') THEN
        dbms_output.put_line(', set _external_scn_rejection_threshold_hours=24'
                             || ' after apply');
      END IF;
      dbms_output.put_line('AND contact Oracle support immediately.' );
    ELSE
      dbms_output.put_line('Result: A - SCN Headroom is good');
      dbms_output.put_line('Apply the latest recommended patches');
      dbms_output.put_line('based on your maintenance schedule ');
      IF (C.version >= '10.1.0.5.0' and
          C.version <= '10.2.0.5.0' and
          C.version NOT LIKE '9.2%') THEN
       dbms_output.put_line('AND set _external_scn_rejection_threshold_hours=24'
                             || ' after apply.');
      END IF;
    END IF;
  END IF;
  dbms_output.put_line(
    'For further information review MOS document id 1393363.1');
  dbms_output.put_line( '-----------------------------------------------------'
                        || '---------' );
 END LOOP;
end;
/

在应用补丁之后，一个新的隐含参数 _external_scn_rejection_threshold_hours 引入，通常设置该参数为 24 小时：

_external_scn_rejection_threshold_hours=24

这个设置降低了SCN Headroom的顶部空间，以前缺省的设置容量至少为31天，降低为 24 小时，可以增大SCN允许增长的合理空间。

但是如果不加控制，SCN仍然可能会超过最大的合理范围，导致数据库问题。

这个问题的影响会极其严重，我们建议用户检验当前数据库的SCN使用情况，以下是检查脚本的输出范例：

--------------------------------------
ScnHealthCheck
--------------------------------------
Current Date: 2012/01/15 14:17:49
Current SCN:  13194140054241
Version:      11.2.0.2.0
--------------------------------------
Result: C - SCN Headroom is low
If you have not already done so apply
the latest recommended patches right now
AND contact Oracle support immediately.
For further information review MOS document id 1393363.
--------------------------------------

这个问题已经出现在客户环境中，需要引起大家的足够重视。

相关文章|Related Articles

• 数据安全防范 提升需从今日始 - 浅析数据安全
  
• Oracle Security Alert for CVE-2011-5035 OC4J
  
• Oracle SCN exhaustion BUG - CPU Jan 2012
  
• Oracle数据库安全 - CVE-2011-3512
  
• 数据安全 - 统计局CPI数据及央行经济数据泄露
  

评论数量(0)|Add Comments

本文网址：http://www.eygle.com/archives/2012/05/oracle_rejection_scn_threshold.html

older posts »