oracle database 11.2.0.1 AIX 7.1的认证

oracle database 11.2.0.1 AIX 7.1的认证:11.2.0.1是认证AIX 7.1操作系统的。

但要求AIX 7.1上安装以下三个APAR:

The customers must install the follow APARs (IBM OS Patches) on AIX7.1:

  • IZ87216
  • IZ87564
  • IZ89165

 

Screen Shot 2014-11-14 at 2.30.46 PM

 

 

Oracle Database 11.2.0.1.0 with IBM AIX on POWER Systems (64-bit) 7.1

Product: For general information relating to certification for the Oracle Database product, including virtualization, interoperability, binary compatibiliy, general release and patch set information, see Core Database Certification Information (Doc ID 1306539.1).

Platform: For details about certification of all Oracle Database releases on IBM AIX on Power, click here.

ACFS requires a minimum OS level of  AIX 6.1 TL4 SP2, AIX 7.1 is not supported at this time. For more details please review Oracle documentation

Oracle Database products are tested and certified on the AIX 5,  AIX 6  and AIX 7 operating systems. The customers must install the follow APARs (IBM OS Patches) on AIX7.1:

  • IZ87216
  • IZ87564
  • IZ89165

For further details on minimum software versions and patch requirements, refer to 282036.1

Certification: For details specific to the certification of Oracle Database Release 11.2 on IBM AIX on Power, click here.

The following notes apply to release 11.2.0.1.0 of Oracle Database:

ACFS: Oracle Cloud File System (ACFS) certification details are listed under the “Oracle Cloud File System” product

 

Oracle数据库打不开的解决

造成Oracle数据库打不开,无法打开的情况大致有几种:

  • 参数设置不当
  • 控制文件损坏
  • 日志文件损坏
  • 数据文件头损坏
  • 数据字典损坏
  • UNDO损坏
  • SMON回滚事务时遇到问题

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 400-690-3643   备用电话: 18501767907    邮箱:service@parnassusdata.com

 

针对不同的报错ORA-00600/ORA-07445等,可以有不同的应对方法:

 

在ORACLE中形成 数据块损坏/坏块诊断corruption多种多样,但其症状大致为如下几种:

  • ORA-01578错误
  • ORA-600[61xx]错误
  • ORA-600[3339]或者ORA-600[3398]
  • ORA-600[2130],ORA-600[2845],ORA-600[4147]错误等等
  • SELECT 查询出讹误的数据

 

应当该类ORACLE数据块损坏/坏块诊断的问题 有这么几个三板斧的步骤:

1、如果数据库仍然是打开状态,则需要判断该块损坏/坏块所在的 数据文件号、块号 并定位到具体的对象(可能是表或者索引)。 结合ORA-1578错误或者ORA-600报出的变量信息,采取如下SQL来定位

 

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &fileid
and &blockid between block_id AND block_id + blocks - 1;

 

2、取决于上一步获得的SEGMENT_TYPE, 如果是以下的SEGMENT_TYPE是可以重建的:

  • index
  • 数据可以重新获得的表,或者可以重建的表
  • 回滚段,除了SYSTEM这个回滚段
  • 排序段 , sort segment
  • 临时表

 

 

3、 如果不属于步骤2中支出的任何一种,那么需要注意以下的信息:

  • 数据库是否是归档模式
  • 有无表的备份数据,包括export /sqlldr
  • 是否该表上有基于 NOT NULL字段的索引?
  • 如果有这样的索引,那么是否是UNIUQE的?

 

4、是否这套库从前已经有块损坏/坏块的情况? 这一点有经验的DBA可以从alert.log大致了解情况的, 如果以往有过此类问题则可以参考下文的后续建议

 

5、如果用户正使用归档模式,则应当建议保存一份归档redo和在线日志以便今后的后续诊断。如果不是,则要求用户备份所有的在线日志

 

6、在有条件的情况下做10210,10211和10212 event来捕捉错误源头。 如果现场工程师怀疑问题不是由于 ORACLE本身引起的,则建议dump 有问题的数据块并结合OS和存储、卷管理器的日志来分析。  如果怀疑是内存损坏则有必要考虑_db_block_cache_protect ,注意不是所有平台支持_db_block_cache_protect而且其损坏较多性能

 

7、在某些情况下,有必要要求用户启用归档模式来避免后续再次发生问题时无法有效恢复

 

必要收集的证据

 

1、 包括ORACLE TRACE和ALERT文件,这个是我们诊断此类问题的源头, 并分析这些报告中是否有其他数据块被报告存在损坏

2、从OS角度转储坏的数据块

Unix: dd if=badfile.dbf count=5 bs=2048 skip=75

 

 

后续建议

 

1、当我们在分析trace或redo日志转储时 有必要调整用户的预期,要表达给用户这些信息:

  • 我们在帮助判断原因,而不是判断如何修复这些坏块
  • 我们在研究这些证据,但这些证据未必能让我们下决定性的结论

 

 

2、有时候数据块是在内存中损坏了 例如ORA-600[3398],为了验证这些情况可以:

  • analyze table X validate structure cascade;
  • alter system flush buffer_cache;
  • 从OS角度转储该数据块并分析

 

 

后续措施

 

1、寻找本质, 例如:

  • 所有的损坏都只发生在某个裸设备或者设备或者控制器上
  • 每数4个块出现一个坏块
  • 数据块本身没问题,但是出现的位置不对
  • 数据块的部分是健康的,但其他地方不正确

 

2、 通过绕过存在 损坏/坏块的数据块来重建表:

使用10231 level 10事件来执行一个全表扫描的CTAS

通过构建ROWID来避免访问损坏的数据块 【数据恢复】利用构造ROWID实现无备份情况下绕过ORA-1578、ORA-8103、ORA-1410等逻辑/物理坏块问题

 

3、 启用10210、10211和10212并更新数据块来进一步定位坏块的细节,并考虑使用10231 event

 

其他工具

 

其他可选的工具包括dul、oranum、orapatch、bbed等,这些都是ORACLE内部工具。

Oracle Optimizer Hint优化器提示分类表

Oracle Optimizer Hint优化器提示分类表

 

 

 

分类 9i R1 9i R2 10g R1 10g R2 11g R1 11g R2
优化器模式 ALL_ROWS ALL_ROWS ALL_ROWS ALL_ROWS ALL_ROWS ALL_ROWS
FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n)
CHOOSE CHOOSE
RULE RULE RULE ※askmaclean.com
Hints OPTIMIZER_FEATURES_ENABLE
针对访问路径 FULL FULL FULL FULL FULL FULL
access path的HINT ROWID ROWID
CLUSTER CLUSTER CLUSTER CLUSTER CLUSTER CLUSTER
HASH HASH HASH HASH HASH HASH
INDEX INDEX INDEX INDEX INDEX INDEX
NO_INDEX NO_INDEX NO_INDEX NO_INDEX NO_INDEX NO_INDEX
INDEX_ASC INDEX_ASC INDEX_ASC INDEX_ASC INDEX_ASC INDEX_ASC
INDEX_COMBINE INDEX_COMBINE INDEX_COMBINE INDEX_COMBINE INDEX_COMBINE INDEX_COMBINE
INDEX_JOIN INDEX_JOIN INDEX_JOIN INDEX_JOIN INDEX_JOIN INDEX_JOIN
INDEX_DESC INDEX_DESC INDEX_DESC INDEX_DESC INDEX_DESC INDEX_DESC
INDEX_FFS INDEX_FFS INDEX_FFS INDEX_FFS INDEX_FFS INDEX_FFS
AND_EQUAL AND_EQUAL NO_INDEX_FFS NO_INDEX_FFS NO_INDEX_FFS NO_INDEX_FFS
INDEX_SS INDEX_SS INDEX_SS INDEX_SS
NO_INDEX_SS NO_INDEX_SS NO_INDEX_SS NO_INDEX_SS
INDEX_SS_ASC INDEX_SS_ASC INDEX_SS_ASC INDEX_SS_ASC
INDEX_SS_DESC INDEX_SS_DESC INDEX_SS_DESC INDEX_SS_DESC
关于转换的HINT NO_QUERY_TRANSFORMATION NO_QUERY_TRANSFORMATION NO_QUERY_TRANSFORMATION NO_QUERY_TRANSFORMATION
USE_CONCAT USE_CONCAT USE_CONCAT USE_CONCAT USE_CONCAT USE_CONCAT
NO_EXPAND NO_EXPAND NO_EXPAND NO_EXPAND NO_EXPAND NO_EXPAND
REWRITE REWRITE REWRITE REWRITE REWRITE REWRITE
EXPAND_GSET_TO_UNION
NOREWRITE NOREWRITE NO_REWRITE NO_REWRITE NO_REWRITE NO_REWRITE
MERGE MERGE MERGE MERGE MERGE MERGE
NO_MERGE NO_MERGE NO_MERGE NO_MERGE NO_MERGE NO_MERGE
STAR_TRANSFORMATION STAR_TRANSFORMATION STAR_TRANSFORMATION STAR_TRANSFORMATION STAR_TRANSFORMATION STAR_TRANSFORMATION
NO_STAR_TRANSFORMATION NO_STAR_TRANSFORMATION NO_STAR_TRANSFORMATION NO_STAR_TRANSFORMATION
FACT FACT FACT FACT FACT FACT
NO_FACT NO_FACT NO_FACT NO_FACT NO_FACT NO_FACT
UNNEST UNNEST UNNEST UNNEST
NO_UNNEST NO_UNNEST NO_UNNEST NO_UNNEST
JOIN order HINT LEADING LEADING LEADING LEADING
ORDERED ORDERED ORDERED ORDERED ORDERED ORDERED
STAR STAR
JOIN操作HINT USE_NL USE_NL USE_NL USE_NL USE_NL USE_NL
NO_USE_NL NO_USE_NL NO_USE_NL NO_USE_NL
USE_NL_WITH_INDEX USE_NL_WITH_INDEX USE_NL_WITH_INDEX USE_NL_WITH_INDEX
USE_MERGE USE_MERGE USE_MERGE USE_MERGE USE_MERGE USE_MERGE
NO_USE_MERGE NO_USE_MERGE NO_USE_MERGE NO_USE_MERGE
USE_HASH USE_HASH USE_HASH USE_HASH USE_HASH USE_HASH
NO_USE_HASH NO_USE_HASH NO_USE_HASH NO_USE_HASH
DRIVING_SITE DRIVING_SITE (参见其他HINT) (参见其他HINT) (参见其他HINT) (参见其他HINT)
LEADING LEADING
HASH_AJ、MERGE_AJ、NL_AJ HASH_AJ、MERGE_AJ、NL_AJ
HASH_SJ、MERGE_SJ、NL_SJ HASH_SJ、MERGE_SJ、NL_SJ
特殊 CHANGE_DUPKEY_ERROR_INDEX
IGNORE_ROW_ON_DUPKEY_INDEX
RETRY_ON_ROW_CHANGE
并行执行HINT PARALLEL PARALLEL PARALLEL PARALLEL PARALLEL PARALLEL
NOPARALLEL NOPARALLEL NO_PARALLEL NO_PARALLEL
PQ_DISTRIBUTE PQ_DISTRIBUTE PQ_DISTRIBUTE PQ_DISTRIBUTE PQ_DISTRIBUTE PQ_DISTRIBUTE
PARALLEL_INDEX PARALLEL_INDEX PARALLEL_INDEX PARALLEL_INDEX PARALLEL_INDEX PARALLEL_INDEX
NOPARALLEL_INDEX NOPARALLEL_INDEX NO_PARALLEL_INDEX NO_PARALLEL_INDEX NO_PARALLEL_INDEX NO_PARALLEL_INDEX
其他HINT APPEND APPEND APPEND APPEND APPEND APPEND
NOAPPEND NOAPPEND NOAPPEND NOAPPEND NOAPPEND NOAPPEND
APPEND_VALUES
CACHE CACHE CACHE 诗檀软件 CACHE CACHE CACHE
NOCACHE NOCACHE NOCACHE NOCACHE NOCACHE NOCACHE
UNNEST UNNEST
NO_UNNEST NO_UNNEST
PUSH_PRED PUSH_PRED PUSH_PRED PUSH_PRED PUSH_PRED PUSH_PRED
NO_PUSH_PRED NO_PUSH_PRED NO_PUSH_PRED NO_PUSH_PRED NO_PUSH_PRED NO_PUSH_PRED
PUSH_SUBQ PUSH_SUBQ PUSH_SUBQ PUSH_SUBQ PUSH_SUBQ PUSH_SUBQ
NO_PUSH_SUBQ NO_PUSH_SUBQ NO_PUSH_SUBQ NO_PUSH_SUBQ NO_PUSH_SUBQ
QB_NAME QB_NAME QB_NAME QB_NAME
ORDERED_PREDICATES ORDERED_PREDICATES
CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT
DYNAMIC_SAMPLING DYNAMIC_SAMPLING DYNAMIC_SAMPLING DYNAMIC_SAMPLING DYNAMIC_SAMPLING
SPREAD_MIN_ANALYSIS
MODEL_MIN_ANALYSIS MODEL_MIN_ANALYSIS MODEL_MIN_ANALYSIS
DRIVING_SITE DRIVING_SITE DRIVING_SITE DRIVING_SITE

Oracle数据库技术交流和知识转移

 oracle-kt

序号 名称 天数 服务对象 特点及主要内容
 1 数据库和SQL语言基础课程 2 初级开发人员 面向初级开发人员的入门级课程,讲述数据库基本原理、SQL基本操作,介绍SQL应用优化基本原理、常见性能问题和案例分析
2 PL/SQL语言基础课程 2 初、中级开发人员 介绍PL/SQL概述、PL/SQL变量声明、常用SQL语句编写、条件和循环语句编写、复合数据类型处理、游标使用、异常处理等,并介绍PL/SQL编写经验和相关案例
3 PL/SQL语言高级课程 3 高级开发人员 系统介绍PL/SQL开发高级技术。包括存储过程、函数、包的编写,Oracle主要内置包的介绍,动态SQL语句编写,大对象管理等,并介绍PL/SQL高级技术运用经验和相关案例
4 数据库逻辑设计及建模 2 数据库设计、开发人员和DBA 系统介绍数据库规范化设计理论,并通过SQL Developer Data Modeler工具的运用,结合案例讲述数据流程图设计和实体关系图(E-R)设计过程,提高客户数据库设计水平
序号 名称 天数 服务对象 特点及主要内容
 5 数据库物理设计 2 数据库设计、开发人员和DBA 全面介绍Oracle数据库物理设计过程,包括表空间、表、索引等物理设计,适合于海量数据管理的分区、ASM等技术和方案设计,10g/11g相关新特性,并介绍若干实际案例
6 DBA基础课程 3 初、中级DBA 介绍Oracle数据库体系结构,以及DBA日常运行维护操作。如数据库对象管理、备份/恢复、安全性基本管理、网络管理、性能监控和分析,以及AWR、ADDM等基本工具使用和分析
7 DBA高级课程 3 高级DBA 面向高级DBA,结合大量实战案例,在系统级和SQL应用级分别介绍性能优化高级技术,并讲述分区技术方案,故障诊断方法和实用技巧,以及Oracle最大高可用性(MAA)体系结构及技术方案
8 SQL应用优化高级课程 3 中高级DBA和应用开发人员 基于Oracle性能优化方法论,结合大量实战案例,系统介绍SQL应用优化技术,如索引策略、优化器原理,针对OLTP和OLAP不同类型系统的优化技术运用过程等

 

 

序号 名称 天数 服务对象 特点及主要内容
 9 Oracle分区技术 2 中高级DBA和应用开发人员 系统介绍适合于海量数据管理的Oracle分区技术。包括分区表和分区索引技术、分区技术运用、11g分区新特性、分区实施经验和案例分析等
10 故障诊断高级课程 2 高级DBA 系统介绍Oracle数据库故障诊断方法论,并介绍常见数据库故障诊断工具(RDA、AWR、ADDM、ASH、OSW、database dump、Hang analysis、diagcollect、CHM)、数据库坏块处理流程、数据库Hang的处理、ORA-4031内存错误的处理、RAC故障诊断方法,以及大量故障诊断实战案例
11 Oracle 11g数据库应用优化高级课程 3 中高级DBA和应用开发人员 系统介绍11g在应用优化方面的新技术。包括优化器高级操作优化、自动SQL优化功能、SQL Tuning Advisor、SQL Access Advisor、SQL PLAN 管理功能、HINT使用技巧,以及大量实战性能优化案例
12 Oracle RAC原理及实施 3 高级DBA 全面介绍Oracle RAC体系结构、RAC安装过程、RAC高可用性测试、RAC运行维护经验、RAC性能分析和优化等,并介绍ASM原理、实施经验等,以及11g在RAC和ASM方面新特性及相关实施案例

 

序号 名称 天数 服务对象 特点及主要内容
 13 Oracle OEM 3 高级DBA 系统介绍Oracle集中监控管理平台OEM的EM Repository、Grid Console、Agent三层架构设计,数据库基本管理功能,数据库在线诊断和优化包使用,报表方案设计,并介绍OEM实施案例
14 Oracle数据库安全性 3 高级DBA 全面介绍Oracle数据库安全体系结构,并介绍Oracle数据库安全加密技术,Oracle DB Vault,Oracle DB Mask,Oracle Audit Vault 等典型技术及实施案例
15 数据仓库设计和建模 3 数据仓库设计、开发和管理人员 面向数据仓库系统,介绍业务和逻辑模型设计、多维数据模型、物理模型、容量规划、典型ETL技术、汇总管理等内容,以及数据仓库实施案例
16 Oracle容灾课程 2 高级DBA 介绍Oracle容灾产品Data Guard的体系结构,物理Data Guard和逻辑Data Guard实施过程和日常运行维护管理,Data Guard优化技术和11g新特性,以及Data Guard若干实施案例

 

序号 名称 天数 服务对象 特点及主要内容
 17 11g新特性 3 数据库设计、开发人员和DBA 系统介绍11g新特性,包括SQL自动性能优化技术、SQL PLAN、Database Replay、SPA、分区新技术、SecureFiles、SQL Result Cache、RMAN新特性、安全性新技术等,以及11g实施案例
18 12c新特性 2 数据库设计、开发人员和DBA 介绍12c若干新特性:CDB和PDB概念和架构、CDB和PDB日常管理;以及信息生命周期管理新技术,如Heat Map和ADO技术、数据归档新技术等。
19 Oracle综合高级技术 3 数据库设计、开发人员和DBA 围绕11g新特性、11g升级解决方案、性能优化、分区技术、Oracle容灾解决方案等高级技术进行介绍,并介绍相关实施案例
20 Exadata 2 高级DBA 介绍Exadata原理和体系结构,Exadata 安装和配置,Exadata Database Michine和Exadata Storage Server日常运行维护、常见故障诊断,Exadata备份恢复,迁移至Exadata技术方案等,并介绍相关实施案例
21 Oracle TimesTen内存数据库 2 高级DBA 介绍Oracle TimesTen内存数据库基本原理,TimesTen内存数据库日常管理、并发控制和性能优化,TimesTen复制技术、TimesTen高可用性方案,以及相关实施案例。
22 Oracle云计算及数据整合技术 2 高级DBA Oracle云计算总体策略,数据库云计算平台(DBaaS),Oracle虚拟机技术,云计算的容灾和安全性,主要数据整合和迁移技术等,以及相关实施案例

 

 

PRM-DUL成功助力安徽某乙方恢复了用户数据库

PRM-DUL成功助理安徽某乙方恢复了用户涉及到大量图片BLOB的数据库,该数据库由于存储异常且无任何备份导致无法打开和恢复:

 

最新版PRM-DUL下载地址: http://parnassusdata.com/sites/default/files/ParnassusData_PRMForOracle_3206.zip

免费的PRM-DUL License :http://www.parnassusdata.com/zh-hans/node/122 

 

 

prm-dul win 2 prm-dul win3 prm-dul win1

Oracle 12.1.0.2新特性 Approximate Count Distinct

[oracle@PD009 ~]$ grep -i approx_count oracle.str
settings for approx_count_distinct optimizations
qkaGBPushdown:  estimated memory without GPD = groupSize (%.2f) * aclsum (%u) = %.2f; estimated memory with GPD = optHllEntry (%u) * # of approx_count_distinct (%d) * parallelDegree (%.0f) = %.2f
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT
 
 
_approx_cnt_distinct_gby_pushdown = choose
_approx_cnt_distinct_optimization = 0




 alter system flush shared_pool;
  alter session set events '10053 trace name context forever ,level 1';
 select count( distinct prod_id) from sales_history where  amount_sold>1;
 

 select approx_count_distinct(prod_id) from sales_history where  amount_sold>1;

  oradebug setmypid;
  oradebug tracefile_name;
  /s01/diag/rdbms/pdprod/PDPROD/trace/PDPROD_ora_4086.trc
  
  
  
  sql= select count( distinct prod_id) from sales_history where  amount_sold>1
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation             | Name         | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT      |              |       |       |  4912 |           |
| 1   |  SORT AGGREGATE       |              |     1 |    13 |       |           |
| 2   |   VIEW                | VW_DAG_0     |    72 |   936 |  4912 |  00:00:59 |
| 3   |    HASH GROUP BY      |              |    72 |   648 |  4912 |  00:00:59 |
| 4   |     TABLE ACCESS FULL | SALES_HISTORY| 3589K |   32M |  4820 |  00:00:58 |
---------------------------------------------+-----------------------------------+













sql= select approx_count_distinct(prod_id) from sales_history where  amount_sold>1
----- Explain Plan Dump -----
----- Plan Table -----
 
============
Plan Table
============
----------------------------------------------+-----------------------------------+
| Id  | Operation              | Name         | Rows  | Bytes | Cost  | Time      |
----------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |              |       |       |  4820 |           |
| 1   |  SORT AGGREGATE APPROX |              |     1 |     9 |       |           |
| 2   |   TABLE ACCESS FULL    | SALES_HISTORY| 3589K |   32M |  4820 |  00:00:58 |
----------------------------------------------+-----------------------------------+
Predicate Information: 


about db_lost_write_protect


SQL> create table lost_write(t1 int) tablespace users; 

Table created.

SQL> 
SQL> insert into lost_write values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.


select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from lost_write;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                                 222                                    6

								 
								 

	alter system set db_lost_write_protect=typical;
	
	
	
	
SQL> select name from v$datafile where file#=6;

NAME
--------------------------------------------------------------------------------
/s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf


update lost_write set t1=9999;

alter system flush buffer_cache;



dd if=/s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf skip=222 bs=8192 count=1  of=222_block

dd if=222_block of=/s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf  seek=222 bs=8192 count=1 conv=notrunc

11g以后的space preallocation特性和SMCO/W00N

11g以后oracle引入了智能空间预分配space preallocation的新特性,该新特性涉及3个领域:

  • 表空间的预分配和扩展
  • 数据段segment的预分配和扩展
  • LOB chunk的预分配和扩展

 

以下是一个tablespace 预分配和扩展的例子,可以看到某个表空间对应的FILE#=3的数据文件,由于在一段时间内的空间使用情况预估,所以在几个小时内扩展了不少的空间:

 

Sat Oct 04 06:07:46 2014
Resize operation completed for file# 3, old size 706560K, new size 716800K
Sat Oct 04 08:00:03 2014
www.askmaclean.com
Thread 1 advanced to log sequence 60 (LGWR switch)
  Current log# 2 seq# 60 mem# 0: /s01/oradata/PDPROD/onlinelog/o1_mf_2_b2wgc3rf_.log
  Current log# 2 seq# 60 mem# 1: /s01/fast_recovery_area/PDPROD/onlinelog/o1_mf_2_b2wgc4mf_.log
Sat Oct 04 08:00:05 2014
TT00: Standby redo logfile selected for thread 1 sequence 60 for destination LOG_ARCHIVE_DEST_2
Sat Oct 04 08:00:06 2014
Archived Log entry 79 added for thread 1 sequence 59 ID 0xe5f08f5 dest 1:
Sat Oct 04 08:08:00 2014
www.askmaclean.com
Resize operation completed for file# 3, old size 716800K, new size 727040K
Sat Oct 04 10:08:37 2014
Thread 1 advanced to log sequence 61 (LGWR switch)
  Current log# 3 seq# 61 mem# 0: /s01/oradata/PDPROD/onlinelog/o1_mf_3_b2wgc6ol_.log
  Current log# 3 seq# 61 mem# 1: /s01/fast_recovery_area/PDPROD/onlinelog/o1_mf_3_b2wgc70g_.log
Sat Oct 04 10:08:37 2014
TT00: Standby redo logfile selected for thread 1 sequence 61 for destination LOG_ARCHIVE_DEST_2
Sat Oct 04 10:08:40 2014
Archived Log entry 81 added for thread 1 sequence 60 ID 0xe5f08f5 dest 1:
Sat Oct 04 13:08:34 2014
Resize operation completed for file# 3, old size 727040K, new size 737280K
Sat Oct 04 14:04:39 2014


Resize operation completed for file# 3, old size 747520K, new size 757760K

 

以上这种扩展受到Oracle内部预定义的参数TBS_EXTENSION_MAX_STEP_SIZE(64MB)的限制,即最大一次扩展是64MB,可以从上述日志看到大多数扩展在这里是一次10MB。

 

对于表空间预分配, space preallocation特性会基于过去6个小时内的表空间使用情况,每隔10分钟分析一次,基于以下的数据分析:

  • Database id – Database identifier
  • Tablespace id – Tablespace identifier number
  • Creation SCN (wrap, base) [NEW] – SCN when the tablespace is created. This is for tablespace sanity check in case that the tablespace is dropped or recreated after the statistics is collected.
  • Allocated space – Space allocated to the tablespace
  • Used space – Space currently used in the tablespace. For permanent tablespace, it is represented by the used extents. For temporary tablespace, this should be the space used by active sorts, hash joins and other transient objects.
  • Max size – Maximum size of the tablespace
  • Flag – Tablespace flag (e.g. perm/temp/undo)
  • Stat-collection Timestamp [NEW] – The time when the statistics is collected

 

对于数据段segment预分配,数据段的统计信息会每半个小时flush到AWR中(WRH$_SEG_STAT),Segment growth trend数据段的增长趋势也会每半个小时完成一次, 基于以下的数据分析:

  • Tablespace id
  • Tablespace creation SCN (wrap, base) [NEW]
  • Segment obj#
  • Segment dataobj#
  • Number of allocated blocks
  • Number of used blocks
  • Stat-collect Timestamp [NEW] – The time when the statistics is collected
  • Last-analysis timestamp [NEW] – The time when the segment is analyzed the last time
  • Number of forecasted used blocks [NEW]

 

LOB chunk的使用情况 会每10分钟记录在内存中,也会定期flush到AWR中,基于以下的数据分析:

 

  • Instance id
  • Tablespace id
  • Tablespace creation SCN (wrap, base)
  • Segment obj#
  • Segment dataobj#
  • Number of estimated optimal allocation for each chunk size (Ne) – One occurrence of an allocation for a chunk size refers to a planned
  • allocation of the optimal chunk size that is calculated by the space layer when receiving a chunk allocation request from the data layer.
  • Number of allocations for each chunk size (Na)
  • Number of deallocation for each chunk size (Nd)
  • Number of under-allocation for each chunk size (Nu)
  • Number of split for each chunk size (Ns)
  • Number of projected allocations for each chunk size (Np)
  • Timestamp – The time when the statistics is collected

 

这些预分配和扩展任务主要由SMCO(Space Management Coordinator Process)和其小工进程W00n(slave )一起完成。 SMCO和W00n是基于任务task驱动的后台任务Framework。这个后台任务体系是基于实例的,而非基于数据库的。每一个实例instance有其自己的后台任务服务体系,运行和处理在本实例发起的任务。RAC的不同实例之间不会交互来负责此种任务的负载。

 

Task coordinator ( SMCO ) ,SMCO充当调度进程以便管理任务队列和slave进程池。其主要任务是在几个task queue之间移动任务,清理过期任务,基于任务需要来动态分配新的slave(W00n)进程,并监控slave进程。 一个实例只有一个SMCO进程,且SMCO进程不是fatal进程,kill了一般也没事。

 

部分12c新特性 Policy Driven Data Movement and ILM(Information Lifecycle Management) Project 数据生命周期管理也依赖于SMCO后台进程。

 

 

 

SMON,SMON是老牌后台进程 已经负责了一系列任务。其现在也负载动态启动SMCO进程。SMON现在会定期检查SMCO是否启动着,SMON也会当有任务提交时启动SMCO。

W00n等一组Slave Process,Slave进程总是实际干活的人。Salve process由SMCO这个调度器动态分配。一旦启动后,slave 进程将自主工作,其自动从ready-task队列中找寻任务并执行。如果空闲了过长时间,那么W00n也会自行终止。

 

其大致的工作流程如下:

 

  • The foreground system/user session submits a task through ktsjCreateTask.
  • The task is created. If the task is planned to start right away, it is inserted into one of the ready-task queue directly, otherwise, it isinserted into the not-ready-task queue.
  • If SMCO does not exist, post SMON to start SMCO .
  • If SMCO is available, post SMCO that a new task is available.
  • SMCO knows that there is a task that needs to be run in the near future. It starts a slave process.
  • SMCO moves the task from the not-ready queue to the ready queue and posts any waiting slave.
  • The slave process picks up the task from the ready queue and invokes the task’s callback function.
  • The task is done. The slave process executes the completion call back of the task.
  • The slave process post SMCO that a task is finished. askmaclean.com
  • SMCO updates task execution statistics and may choose to free the task if there is no enough memory for task cache.
  • The slave process periodically checks if there is any task in the ready-task queue.

 

与该11g以后的space preallocation特性相关的参数如下:

_enable_space_background_task/_enable_spacebg – This parameter specifies whether the background task support is enabled.

_max_ smco _slaves – This parameter specifies the maximum number of active slave process that can be spawned at a time. Ideally, the number
of slave processes should be decided by the system workload and the availability of system resources.
_max_ smco _tasks – This parameter specifies the maximum number of tasks that can be stored in memory. Ideally, the number of tasks should be
decided by the availability of SGA memory and the efficiency of task maintenance. Too many cached tasks will simply increase the burden of
task maintenance.

 

_enable_space_background_task
Parameter Name: _enable_space_background_task
Parameter Type: boolean
Allowable Values: TRUE to enable the feature, and FALSE to disable the feature
Default Value: TRUE. The space management background task support will be enabled by default.
Description: This parameter allows the user to choose whether to enable the space management background task support feature.
Dynamic: The parameter is dynamic and the scope is ALTER SYSTEM.
Example: alter system set “_enable_space_background_task ” = TRUE

 

 

_enable_space_preallocation
Parameter Name: _enable_space_preallocation
Parameter Type: integer
Allowable Values:
0x00: disable all levels of space preallocation
0x01: enable tablespace extension ahead of time
0x02: enable segment growth ahead of time
0x04: enable chunk allocation ahead of time
Combination of any of the above 3 levels of space preallocation Default Value: 0x07. The space preallocation at all levels will be enabled by default. However, whether the space preallocation will be done and how much space will be allocated relies on the system monitor analysis result.

Description: This parameter allows the user to enable different levels of space preallocation.
Dynamic: The parameter is dynamic and the scope is ALTER SYSTEM.
Example: alter system set “_enable_space_prealloaction ” = 1
_max_spacebg_slaves
the parameter that allows user to set maximum number of space BG slaves

_minmax_spacebg_slaves
the parameter that allows user to set min-max number of space BG slaves

_min_spacebg_slaves

the parameter that allows user to set minimum number of space BG slaves

_max_spacebg_tasks

the parameter that specifies the maximum number of space BG tasks
_max_spacebg_msgs_percentage

the parameter that specifies percentage of _messages (ksaxxm) before KTSJ
throttling occurs. Default level is 50%. When throttle level is reached,
interrupt messages are not sent (thus avoiding _messages queue).

_enable_space_preallocation 3 enable space pre-allocation
_enable_spacebg TRUE enable space management background task
_max_spacebg_slaves 1024 maximum space management background slaves
_minmax_spacebg_slaves 8 min-max space management background slaves
_min_spacebg_slaves 2 minimum space management background slaves
_max_spacebg_tasks 8192 maximum space management background tasks
_max_spacebg_msgs_percentage 50 maximum space management interrupt message throttling

对于由于SMCO 或W00n在完成space preallocation过程中遇到的问题时,可以考虑禁用该特性,具体方法为:

 

 

SQL> alter system set "_enable_spacebg"=false;

System altered.

SQL> alter system set "_enable_space_preallocation"=0;

System altered.




 

SMCO/Wnnn

 

Short Description: The space management coordinator (SMCO) process coordinates the execution of
various space management related tasks, such as proactive space allocation and space reclamation. It
dynamically spawns slave processes (Wnnn) to implement the task.
Detailed Description: The space management coordinator (SMCO) and slave (Wnnn) processes work
cooperatively on various background space management tasks in a database instance. The coordinator
is responsible for maintaining the tasks and dispatching tasks. It dynamically spawns new slaves based
on task needs. The slave process performs the actual space management task, including space preallocation
and space reclamation. Once started, the slave process acts as an autonomous agent. After
it finishes execution of the task, it automatically pick up another one from the task queue. It terminates
itself after being idle for a long time. SMCO and Wnnn are optional non-fatal background processes.
There is at most one SMCO process per instance. There can be several Wnnn processes at a time. The
failure of these processes does not cause instance to fail.
Properties: Optional, non-fatal, database instance only