>>读者群| 网站统计数据 | 热点排行 回复 | egoSurf | gqgai | eygle | 百度eygle |11g| ACE en cn

置顶:《深入解析Oracle》及个人图书信息汇总

《深入解析Oracle》终于出版了，虽然到达各大书店仍然需要一段时间，但是我还是先将关于这本书以及我的所有图书的相关信息整理出来，供读者们参考。

我的淘宝店地址: http://shop36913374.taobao.com/
如果大家不方便买书，可以在我的淘宝店订购，我可以负责给大家邮递。
我的个人信息讨论新闻组 Eygle Group,欢迎加入：
https://groups.google.com/group/eygle

谢谢大家的支持。

样章试读：
第一章连载 - 之一 | 第一章及第四章下载

Posted by eygle at 11:53 AM | Permalink | Comments (9) | Books (140)

July 2, 2009

Granule 与 Redo Log Buffer (log_buffer) 的关系

不少朋友一直记得以前的优化推荐：Redo Log Buffer不要高于3M。

而实际上大家也发现，从Oracle10g开始，Redo Log Buffer缺省的已经是大大超过了原来的想象。
从Oracle 9i引入了Granule的概念后，在Oracle10g中，Oracle的内存分配会为'Fixed SGA Size'和'Redo Buffers'共享整数倍个Granule。

看看不同SGA设置下的内存分配情况：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select * from v$sgainfo where name in ('Fixed SGA Size','Redo Buffers','Granule Size');

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1267212 No
Redo Buffers                       15507456 No
Granule Size                       16777216 No

SQL> select sum(bytes)/1024/1024 from v$sgainfo where name in ('Fixed SGA Size','Redo Buffers');

SUM(BYTES)/1024/1024
--------------------
            15.99757

在另外的系统中，供分配了2个Granule，每个4M：

SQL> select * from v$sgainfo where name in ('Fixed SGA Size','Redo Buffers','Granule Size');

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1263320 No
Redo Buffers                        7122944 No
Granule Size                        4194304 No

SQL> select sum(bytes)/1024/1024 from v$sgainfo where name in ('Fixed SGA Size','Redo Buffers');

SUM(BYTES)/1024/1024
--------------------
          7.99776459

摘录一部分Oracle文档中关于Granule的描述供参考：

With dynamic SGA, the unit of allocation is called a granule. Components, such as the buffer cache, the shared pool, the java pool, and the large pool, allocate and free SGA space in units of granules. Oracle tracks SGA memory use in integral numbers of granules, by SGA component. All information about a granule is stored in a corresponding granule entry. Oracle maintains the state of each granule in the granule entry and the granule type.
Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 128 MB, and it is 16 MB for larger SGAs. There may be some platform dependency, for example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 128 MB.
The granule size that is currently being used for SGA can be viewed in the view V$SGA_DYNAMIC_COMPONENTS. The same granule size is used for all dynamic components in the SGA.

Note:
If you specify a size for a component that is not a multiple of granule size, then Oracle rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_SIZE as 10 MB, you will actually be allocated 12 MB.

Oracle keeps information about the components and their granules in a scoreboard. For each component that owns granules, the scoreboard contains the number of granules allocated to the component, any pending operations against this component, the target size in granules, and the progress made toward the target size. The start time of the operation is also logged. Oracle maintains the initial number of granules and the maximum number of granules for each component.
For operations that modify the number of granules, Oracle logs the operation, the target size, and the start time to the appropriate SGA component in the scoreboard. Oracle updates the progress field until the operation is complete. When the operation is complete, Oracle replaces the current size with the target size and clears the target size field and the progress field. At the end of the operation, a database administrator can see how the number of granules was changed. Oracle updates the initialization parameter values to reflect the updated amount of SGA in use.
Oracle maintains a circular buffer of the last 100 operations made to the scoreboard. Fixed views show the state of the scoreboard and the current contents of last 100 operations to the scoreboard.
Allocating Granules at Startup
At startup, Oracle reads the values in the initialization parameter file, queries the operating system memory limits, and allocates virtual address space for the SGA. The initialization parameter SGA_MAX_SIZE specifies the maximum size of the SGA for the life of the instance in bytes. Its value is rounded up to the next granule size.
Adding Granules to Components
A database administrator grows a component's SGA use with ALTER SYSTEM statements to modify the initialization parameter values. Oracle takes the new size, rounds it up to the nearest multiple of 16MB, and adds or takes away granules to meet the target size. Oracle must have enough free granules to satisfy the request. If the current amount of SGA memory is less than SGA_MAX_SIZE, then Oracle can allocate more granules until the SGA size reaches SGA_MAX_SIZE.

Posted by eygle at 11:49 AM | Permalink | Comments (0) | FAQ (135)

July 1, 2009

Oracle 10g LOGMNR挖掘日志很方便

Oracle 10g可以使用LOGMNR在线分析和挖掘日志，使用当前在线的数据字典，非常方便。

首先执行一些DDL或DML操作：

SQL> connect eygle/eygle
Connected.

SQL> alter system switch logfile;

System altered.

SQL> create table eygle as select * from dba_users;

Table created.

SQL> set autotrace on
SQL> select count(*) from eygle;

COUNT(*)
----------
        19

Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261

--------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EYGLE |    19 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5 recursive calls
          0 db block gets
          7 consistent gets
          5 physical reads
          0 redo size
        411 bytes sent via SQL*Net to client
        400 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

然后可以执行LOGMNR解析工作：

SQL> connect / as sysdba
Connected.
SQL> select * from v$log where status='CURRENT';

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         2          1        100   52428800          1 NO CURRENT               12729697 01-JUL-09

SQL> SELECT MEMBER from v$logfile where group#=2;

MEMBER
------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/mmstest/redo02.log

SQL> exec dbms_logmnr.add_logfile('/opt/oracle/oradata/mmstest/redo02.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
       136
SQL> select sql_redo from v$logmnr_contents;

SQL_REDO
------------------------------------------------------------------------------------------------------------------------
set transaction read write;
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS
","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('25847','25847'
,'31','EYGLE','1',NULL,'2',TO_DATE('01-JUL-09', 'DD-MON-RR'),TO_DATE('01-JUL-09', 'DD-MON-RR'),TO_DATE('01-JUL-09', 'DD-
MON-RR'),'1',NULL,NULL,'0',NULL,'6','1',NULL,NULL,NULL,NULL);

set transaction read write;
update "SYS"."CON$" set "CON#" = '10823' where "CON#" = '10822' and ROWID = 'AAAAAcAABAAAACqAAM';

commit;
set transaction read write;

SQL_REDO
------------------------------------------------------------------------------------------------------------------------
update "SYS"."CON$" set "CON#" = '10824' where "CON#" = '10823' and ROWID = 'AAAAAcAABAAAACqAAM';

commit;
set transaction read write;
update "SYS"."CON$" set "CON#" = '10825' where "CON#" = '10824' and ROWID = 'AAAAAcAABAAAACqAAM';

commit;
set transaction read write;
update "SYS"."CON$" set "CON#" = '10826' where "CON#" = '10825' and ROWID = 'AAAAAcAABAAAACqAAM';

commit;

set transaction read write;
update "SYS"."CON$" set "CON#" = '10827' where "CON#" = '10826' and ROWID = 'AAAAAcAABAAAACqAAM';

commit;
set transaction read write;
update "SYS"."CON$" set "CON#" = '10828' where "CON#" = '10827' and ROWID = 'AAAAAcAABAAAACqAAM';

commit;
set transaction read write;
update "SYS"."CON$" set "CON#" = '10829' where "CON#" = '10828' and ROWID = 'AAAAAcAABAAAACqAAM';

commit;
create table eygle as select * from dba_users;
set transaction read write;

Unsupported
update "SYS"."TSQ$" set "TS#" = '0', "GRANTOR#" = '43080', "BLOCKS" = '0', "MAXBLOCKS" = '0', "PRIV1" = '0', "PRIV2" = '
0' where "TS#" = '0' and "GRANTOR#" = '43072' and "BLOCKS" = '0' and "MAXBLOCKS" = '0' and "PRIV1" = '0' and "PRIV2" = '
0' and ROWID = 'AAAAAKAABAAAABbAAF';

commit;
set transaction read write;
SQL> exec dbms_logmnr.end_logmnr

PL/SQL procedure successfully completed.

很多时候拿LOGMNR来追踪一些误操作是很有效的方式，甚至在自己定制的数据同步中，LOGMNR也大有可为。

Posted by eygle at 11:09 AM | Permalink | Comments (0) | FAQ (135)

June 30, 2009

恩墨科技为ChinaCache提供紧急救援服务

近日，恩墨科技为ChinaCahce提供了紧急数据库援助服务。

ChinaCache成立于1998年，是中国领先的专业CDN服务提供商，向客户提供全方位网络内容快速分布解决方案。作为2000年就获信产部许可的 CDN服务提供商，目前ChinaCache在全国100多个大中城市拥有近500个节点，覆盖中国电信、中国网通、中国移动、中国联通、中国铁通和中国教育科研网等各大运营商。2007年，ChinaCache北美公司的建立，使ChinaCache网络覆盖了北美及亚太地区。

ChinaCahce与恩墨科技长期以来一直保持着良好的合作与联系，最近的一次意外断电事故，使得客户的数据库系统受到了影响，在经过了数小时的排查之后，客户请求我们介入协助寻求解决方案，在经过了全面的思考和一系列的尝试之后，我们成功的帮助用户恢复了数据库服务，使得TB级数据库能够再次提供服务。

感谢用户的信赖和支持，希望我们的服务能够继续为用户创造价值！

Posted by eygle at 9:31 PM | Permalink | Comments (3) | Life (415)

June 29, 2009

恩墨科技为中电财提供顾问咨询及容灾服务

近日，恩墨科技为中国电力财务有限公司提供数据库容灾环境实施及顾问咨询工作。

近年来，国内越来越多的公司开始认识到数据安全的重要，以各种手段和方式开始实施数据库安全架构。在众多的可选方案中，Oracle的DataGuard技术以其成本低、可用性及安全性能够满足绝大多数需求成为了很多企业的首选。在下个月即将发布的Oracle Database 11gR2版本中，Oracle Active DataGuard技术将得到进一步增强，可以预计，这一新版的的卓越新特性将为Oracle带来更多的客户。

中国电力财务有限公司（以下简称中国电财）成立于1993年，是经中国银行业监督管理委员会批准的一家全国性非银行金融机构。中国电财注册资本金50亿元，由国家电网公司控股、各省（市、区）电力公司等50家电力企、事业单位共同参股组建。经银监会批准，中国电财拥有东北、西北、华中和华东等多家区域性分公司，注册资本金、资产规模、利润总额等多项指标在国内财务公司行业中均名列前茅，在电力和金融行业树立了良好的企业形象，当选为中国财务公司协会理事长单位。

客户的数据库采用Oracle Database 10g RAC架构，异地容灾以Oracle DataGuard技术为主体实现。

感谢用户及合作伙伴用友公司的信赖与支持，希望稳健架构的实施可以帮助用户实现数据安全和业务连续性的需要。

Posted by eygle at 9:46 PM | Permalink | Comments (1) | Life (415)

June 26, 2009

恩墨科技成功签约北京资和信集团

2009年6月，恩墨科技成功签约北京资和信集团，为该集团提供金牌数据库支持与咨询服务,这是恩墨科技成立以来签下的又一重要企业客户。

北京资和信集团是一家服务于金融与零售领域的大型集团公司，主要从事业务包括：卡业务、担保业务、资和信百货及其他业务。

资和信集团的主营卡业务，占据了北京购物卡市场的绝对领导地位。北京商业服务业通用积分卡（商通卡）由北京资和信担保集团旗下北京商服通网络科技有限公司管理，北京资和信通联科技有限公司提供技术服务。作为首家运营通用积分卡的公司，经过近十年的锤炼，商通卡无论是在便利性、安全性还是服务方面都得到客户与商户的广泛认可，无论从交易额、客户数量与层次、商户规模与数量上与其他同行业企业相比，都保持着较大的优势。客户方面，持卡人众多，并与许多大型知名团体客户建立了长期的服务关系；商户方面，行业涉及百货、超市、旅游、餐饮、医疗、健身、娱乐等众多领域。

资和信集团由于承载着大量消费卡的管理、消费记录、结算等重要业务，对于数据库的高可用性、稳定性及性能都有很高的要求。基于双方长期的合作、了解与信任，北京资和信集团选择了恩墨科技作为数据库服务提供商，为其提供全面的数据库技术支持与顾问咨询服务。

感谢客户长期以来的信赖与支持，我们将一如既往的加深理解与沟通，全力为客户提供高品质的数据库服务，保障和满足客户的业务需求。

Posted by eygle at 5:20 PM | Permalink | Comments (6) | Life (415)