AWR报告是Oracle从10g开始引入的一项重要性能特性。以9i可选组件Statspack为基础,借助自动周期作业,我们可以获得一系列的Oracl
AWR报告是Oracle从10g开始引入的一项重要性能特性。以9i可选组件Statspack为基础,借助自动周期作业,我们可以获得一系列的Oracle性能片段,从而在出现性能问题的时候更加从容。
AWR引入的一个结果,就是系统需要根据配置内容将性能数据保存在数据库中。从10g之后,sysaux表空间从system表空间从脱离开来,就提供了这种可能性。我们在实际运维工作中,是可能会遇到AWR元数据引起的故障问题。本篇主要介绍这个案例,留待需要同仁待查。
1、问题说明
运维人员都有“节日休假恐怖症”,越到节日、休假和外出出差,系统越可能出现问题。笔者在进行一个系统的例行检查时,出现了问题。
数据库版本为11gR2,具体版本编号为11.2.0.3。
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
问题发现的由头是生成AWR报告的时候,发现近几天都没有正常生成AWR Snapshot。由于是很少用的系统,所以笔者只在每月进行一次跟踪。这种情况肯定不正常,进入10g之后,AWR后台默认每隔一小时,都会自动生成一个Snapshot镜像数据。
这种情况,笔者本能想去定位alert log,大部分异常情况,Oracle都会记录在数据库中。果然在其中发现了问题。
Wed Sep 30 14:24:15 2015
ORA-1653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 128 in tablespace SYSAUX
Errors in file /home/oracle/app/diag/rdbms/xxx/xxxdb/trace/xxxdb_j000_3385.trc:
ORA-01653: unable to extend table . by in tablespace
ORA-01653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 128 in tablespace SYSAUX
Wed Sep 30 15:06:58 2015
ORA-1653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 128 in tablespace SYSAUX
Errors in file /home/oracle/app/diag/rdbms/xxxdb/xxxdb/trace/xxxdb_j000_5102.trc:
ORA-01653: unable to extend table . by in tablespace
ORA-01653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 128 in tablespace SYSAUX
从内容上看,是sysaux表空间满了。默认情况下,Oracle的系统性质表空间都是不支持文件自动拓展的。如果原有大小写满了,同时不支持自动拓展,的确会有报错异常。
此时,AWR配置内容是默认方式。
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------------------------- --------------------------------------- ----------
1778314713 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
默认AWR是每小时保存一个镜像,镜像数据会保存八天。此时,AWR中已经没有对应的数据镜像了。
SQL> select snap_id, dbid, startup_time from dba_hist_snapshot;
SNAP_ID DBID STARTUP_TIME
---------- ---------- --------------------------------------------------------------------------------
2、问题缓解
一般数据库故障,通常不是一个单独策略可以解决的。笔者认为:问题分轻重缓急,解决方案也分猛药温补。关键的取舍取决于不同的场景优先级别。在这种情况下,恢复AWR工作,增加sysaux表空间存储是首要需求。
这种操作比较简单,只要单独定位和允许文件自动拓展即可。
SQL> alter database datafile '/data/xxxdb/systs/sysaux01.dbf' autoextend on;
Database altered
SQL> select bytes/1024/1024, AUTOEXTENSIBLE from dba_data_files where tablespace_name='SYSAUX';
BYTES/1024/1024 AUTOEXTENSIBLE
--------------- --------------
1032 YES
Alert log中记录信息。
YSAUX
Wed Sep 30 15:30:13 2015
alter database datafile '/data/xxxdb/systs/sysaux01.dbf' autoextend on
Completed: alter database datafile '/data/xxxdb/systs/sysaux01.dbf' autoextend on
手工测试生成AWR镜像,判断问题是否解决。
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed
SQL> select snap_id, to_char(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') from dba_hist_snapshot;
SNAP_ID TO_CHAR(BEGIN_INTERVAL_TIME,'Y
---------- ------------------------------
23383 2015-09-30 15:40:16
在日志中没有新的报错信息出现。可以认为初步问题解决。下一步就是定位问题:为什么会出现sysaux爆棚的情况。
3、深层分析过程
AWR和其他一些性能收集,的确是不断的将数据收集到sysaux表空间里面进行记录。笔者一直认为:任何正确的数据架构模式,必要条件之一就是“有进有出”。数据不断积累,一定要有机制(系统内或者系统外)让数据可以脱离系统。从微观角度看,数据表要维持一个稳定的体积容量结构。
AWR系统也的确是这样。在不断收集数据的时候,也会依据Retention规则(默认为8天)不断将数据Purge掉。无论如何设置,sysaux正常情况下应该是一个固定稳定的大小规格。笔者当前数据库已经运行一两年的时间,要出问题早就出现问题了。说明系统中,有一些数据在不断的“默默长大”,问题在不断的慢慢积累。
尝试检查当前sysaux表空间段结构排名,看有没有与此相关的信息。
SQL> select owner, segment_name, segment_type, bytes/1024/1024 from (select * from dba_segments where tablespace_name='SYSAUX' order by bytes desc) where rownum
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
---------- ------------------------------ ------------------ ---------------
SYS WRM$_SNAPSHOT_DETAILS_INDEX INDEX 102
SYS WRM$_SNAPSHOT_DETAILS TABLE 88
SYS SMON_SCN_TO_TIME_AUX CLUSTER 80
XDB SYS_LOB0000057474C00025$$ LOBSEGMENT 53.1875
疑问出现了,最大的几个对象中,snapshot赫然出现在其中。当前Snapshot里面没有数据,无论是被自然purge掉,还是认为删掉,都不应该有数据存在。查看基础数据表:
SQL> select count(*) from WRM$_SNAPSHOT_DETAILS;
COUNT(*)
----------
1723102
SQL> select * from WRM$_SNAPSHOT_DETAILS where rownum
SNAP_ID DBID INSTANCE_NUMBER TABLE_ID BEGIN_TIME
---------- ---------- --------------- ---------- -------------------------------
1 1778314713 1 4 05-12??-12 05.00.08.719 ????
1 1778314713 1 5 05-12??-12 05.00.08.771 ????
1 1778314713 1 6 05-12??-12 05.00.08.841 ????
1 1778314713 1 7 05-12??-12 05.00.08.892 ????
其中数据量还是很大的,重点在于snap_id。
SQL> select max(snap_id), min(snap_id) from WRM$_SNAPSHOT_DETAILS;
MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
23383 1
“慢慢变大”的数据找到了,作为AWR基础的数据,从来就没有被删除。手工删除呢?是不是可以删除?
SQL> select snap_id from dba_hist_snapshot;
SNAP_ID
----------
23383
SQL> exec dbms_workload_repository.drop_snapshot_range(1,23383);
PL/SQL procedure successfully completed
SQL> select snap_id from dba_hist_snapshot;
SNAP_ID
----------
SQL> select max(snap_id), min(snap_id) from WRM$_SNAPSHOT_DETAILS;
MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
23383 1
SQL> select max(snap_id), min(snap_id) from WRM$_SNAPSHOT;
MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
删除的数据,没有连带将基础数据表WRM$_SNAPSHOT_DETAILS内容删除。诡异的问题!
经过查证MOS,发现该数据表不能删除是一个Oracle Bug,具体描述如下:
Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged
The verification criteria for the bug are:
Drop a range of snap id's using dbms_workload_repository.drop_snapshot_range()
Check the corresponding snap id's in WRM$_SNAPSHOT_DETAILS.
If snap id's from the range that you chose to drop are still present, then you are hitting this bug.
The following solutions are available:
The Patch 9797851 for unpublished Bug 9797851 is available for some platforms and can be downloaded from My Oracle Support
If the patch is not available on your platform on a supported version, please contact Oracle Support.
This issue will be fixed from release Oracle 12.1
As a workaround, it is possible to manually purge the range of snap id's from the table WRM$_SNAPSHOT_DETAILS using appropriate delete statments under the guidance of Oracle Support.
其中内容所,如果要解决这个问题,需要进行补丁操作,或者在Oracle Support指导之下手工进行删除。
由于是生产环境,经过协调,认为当前开启拓展sysaux策略是可以接受的解决方案。于是笔者打算适可而止,不影响系统正常运行。
4、恢复自动AWR收集
最后需要恢复AWR收集。注意:在一些资料中,建议最开始有一个snapshot,通过手工创建,之后Oracle才能自动的进行生成。
为了快速验证,调整收集周期是10分钟。
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>10);
PL/SQL procedure successfully completed
SQL> select * from dba_hist_wr_control;

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

InnoDB is suitable for applications that require transaction support and high concurrency, while MyISAM is suitable for applications that require more reads and less writes. 1.InnoDB supports transaction and bank-level locks, suitable for e-commerce and banking systems. 2.MyISAM provides fast read and indexing, suitable for blogging and content management systems.

There are four main JOIN types in MySQL: INNERJOIN, LEFTJOIN, RIGHTJOIN and FULLOUTERJOIN. 1.INNERJOIN returns all rows in the two tables that meet the JOIN conditions. 2.LEFTJOIN returns all rows in the left table, even if there are no matching rows in the right table. 3. RIGHTJOIN is contrary to LEFTJOIN and returns all rows in the right table. 4.FULLOUTERJOIN returns all rows in the two tables that meet or do not meet JOIN conditions.

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
