Author: mac

如何解释大量redo的产生？

原文为Riyaj Shamsudeen https://orainternals.wordpress.com/2013/06/12/dude-where-is-my-redo/ 本博文旨在讨论一个识别对象，包括较大量redo的方法。首先，我们设置重做大小急剧扩大，然后确定生成更多redo的对象。不幸的是，redo大小不在一段级别被跟踪。不过你可以使用‘db block changes’统计进行推测。但你必须使用LogMiner工具来科学识别生成更多redo的对象。检测redo大小增加可以访问AWR表（需要诊断许可证）来确定redo大小增加。下面的查询spool redo大小增加。你只要打开Excel电子表格中的输出文件redosize.lst并图形化数据观察redo大小的变化。在Excel表格中打开文件时，使用管道符号作为分隔符。 spool redosize.lst REM You need Diagnostic Pack licence to execute this query! REM Author: Riyaj Shamsudeen col begin_interval_time format a30 set lines 160 pages 1000 col end_interval_time format a30 set colsep ‘|’ alter session set nls_date_format=’DD-MON-YYYY’; with redo_sz as ( SELECT sysst.snap_id, sysst.instance_number,…

March 16, 2012
Richard Niemiec又一力作《Oracle Database 11g Release 2 Performance Tuning Tips & Techniques》

对Oracle数据库调优性能浓厚的朋友肯定没有错过TUSC著名的Richard Niemiec的<Oracle Database 9i/10g Performance Tuning & Tips & Techniques> ，这2本书目前有中文译版；这2本书包含了 Richard Niemiec 丰富的Oracle Database 调优经验、详细的案例研究。 Richard Niemiec是一名世界顶尖的Oracle优化专家，同时他也是一位Oracle认证的管理大师OCM，他的书为优化人员提供了大量的提示和技巧：随着10g 版本正式进入Sustaining Support， 12c R1版本可能在12年末、13年初release ，现在正是 11g Release 2开始在国内大范围推广使用的时段。如果你现在对 11g 的Tuning 新特性还不甚了解，包括如：adaptive Cursor Sharing、Result Cache、SQL Performance Analayze …. 。你完全需要一本 11g 调优的完全指南，而 Richard Niemiec 的 <Oracle Database…

March 14, 2012
ASM丢失disk header导致ORA-15032、ORA-15040、ORA-15042 Diskgroup无法mount

ASM丢失disk header导致ORA-15032、ORA-15040、ORA-15042 Diskgroup无法mount的案例不少，这里我们介绍下如何解决。如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复! 诗檀软件专业数据库修复团队服务热线： 13764045638 QQ号:47079569 邮箱：[email protected] SQL> select * from v$version; BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production PL/SQL Release 11.2.0.3.0 – Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 – Production NLSRTL Version 11.2.0.3.0 – Production SQL> alter diskgroup…

March 13, 2012
dbms_space_admin.drop_empty_segments

create or replace package dbms_space_admin is ———— — OVERVIEW — — This package provides tablespace/segment space administration — not available through the standard sql. — SECURITY — — run with SYS privileges. thus any user who has privilege to execute the — the package gets to manipulate the bitmapes. — CONSTANTS to be used as…

March 11, 2012
ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT

有幸遇到这个错误了， 12c的PGA_AGGREGATE_LIMIT 限制PGA功能还是有用的： Exception in thread “main” java.sql.SQLException: ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) oracle@localhost:/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace$ oerr ora 4036 04036, 00000, “PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT” // *Cause: Private memory across the instance exceeded the limit specified // in…

March 9, 2012
【Oracle CBO优化器】视图合并View Merging技术 _complex_view_merging & _simple_view_merging

Oracle CBo优化器中各优化模块如下：相关参数了 _complex_view_merging 和 _simple_view_merging kkqvm contains functions for merging simple SPJ, outer-joined SPJ, and complex (distinct or group by) views. 其中视图合并View Merging 主要分成2种： SPJ Merge simple views that contains Select , Project and Join only (referred as SPJ views) CVM views containing grouping, aggregation, distinct and outer join •Recursively…

March 7, 2012
收到了TUSC寄来的V$ View For Oracle Database 11g

V$ view For oracle 10g版本的总览图相信大家都有看过，而这个11g版本的我专门问了一下TUSC ，目前只有hard copy 版本的，一起欣赏下吧：

March 6, 2012
Unix/Linux下删除Oracle控制文件Controlfile为什么实例Instance没有立即奔溃？

绝大多数教科书或网络教材都告诉初学者， Oracle中丢失Controlfile控制文件会造成不可挽回的损失， Oracle Instance实例会立即崩溃crash。但是实际很多同学在实际操作时发现，在Unix/Linux上删除所有的Controlfile控制文件后实例并不会在第一时间crash，相反居然还可以顺利完成FULL checkpoint 。这是为什么呢？注意这个问题仅适用于Unix/Linux，在Windows平台上不允许删除已经被其他进程打开的文件。究其根本原因是在Linux/Unix上Read 或 Write一个文件，进程都会打开一个与此文件相关联的打开文件描述符 Open File Descriptors (a file descriptor (FD) is an abstract indicator for accessing a file. The term is generally used in POSIX operating systems.In POSIX, a file descriptor is an integer, specifically of the…

March 3, 2012
Oracle ORA-00600 [4000] ORA-600 [4000] “trying to get dba of undo segment header block from usn”

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help. Parnassusdata Software Database Recovery Team Service Hotline: +86 13764045638 E-mail: [email protected] Format: ORA-600 [4000] [a] VERSIONS: version 6.0 to 9.2 DESCRIPTION: This has the potential to be a very serious error. It means that Oracle has tried to…

March 2, 2012
Oracle ORA-600 [4097] ORA-00600 [4097] “Corruption”

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help. Parnassusdata Software Database Recovery Team Service Hotline: +86 13764045638 E-mail: [email protected] ERROR: Format: ORA-600 [4097] VERSIONS: versions 7.3 to DESCRIPTION: We are accessing a rollback segment header to see if a transaction has been committed. However, the xid given…

March 2, 2012