Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle Bug caused by the collection of AWR Snapshot fault     - Oracle LONG RAW BLOB CLOB type of presentation (Database)

- Red Hat Linux mount U disk (Linux)

- ORA-00020: No more process state objects available (Database)

- Let Ubuntu 14.04 Save screen luminance value (Linux)

- The method to mount the CD under Linux (Linux)

- Efficient running Linux virtual machine Six Tips (Linux)

- Ubuntu amend resolv.conf restart failure problem (Linux)

- Thinking in Java study notes - initialization and cleanup (Programming)

- Gnu Linux - Ubuntu System Clean-term consolidation (Linux)

- Linux commands to access the cheat sheet (Linux)

- Install Ubuntu open source drawing program MyPaint 1.2.0 (Linux)

- Zabbix monitoring platform to extract a single server graphical Python implementation of concurrent (Server)

- gzip, bzip2, xz, tar, zip compression, archive Detailed (Linux)

- MySQL innodb_table_stats table does not exist solution (Database)

- Summary of Docker mounted directory (Server)

- Linux system boot process detail (Linux)

- How to implement Linux host Telnet SSH password Free (Server)

- How do I switch from NetworkManager to systemd-network on Linux (Linux)

- Eclipse-ee Tomcat browser can not start Tomcat, and Web project service deployment (Server)

- How to find on Linux and delete duplicate files: FSlint (Linux)

 
         
  Oracle Bug caused by the collection of AWR Snapshot fault
     
  Add Date : 2018-11-21      
         
         
         
  AWR report is an important performance features of Oracle 10g from the start introduced. In 9i optional component Statspack basis with automatic cycle work, we can obtain a series of Oracle performance segments, resulting in performance problems when more calm.

A result of the introduction of AWR, is the need to configure the system performance data content stored in the database. From then 10g, sysaux table space from the system table space open to departing from, provides this possibility. We actual operation and maintenance work, it is possible to experience failures caused problems AWR metadata. This part describes the case, left colleagues need to be examined.

1. Problem Description

Operation and maintenance personnel have a "festive holiday phobia", more to the holiday, leave and go on a business trip, the more system problems may occur. The author during a routine check of the system, there is a problem.

Database version 11gR2, the specific version number 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

Problems found by the head is generated when AWR reports, found in recent days are not normally generate AWR Snapshot. Because it is rarely used in the system, so I only once a month tracking. This situation is certainly not normal, after entering 10g, AWR background every hour by default, a Snapshot will automatically generate the mirror data.

This situation, I instinctively want to locate alert log, most unusual circumstances, Oracle will be recorded in the database. Sure enough, in which a problem is found.

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

From the content point of view, is sysaux table space is full. By default, Oracle system table space is not the nature of the support files automatically expand. If the original case is full, and does not support automatic expansion, there is indeed an error exception.

In this case, AWR content is the default configuration.

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL RETENTION TOPNSQL

---------- --------------------------------------- - ---------- --------------------------------------

1778314713 +00000 01: 00: 00.0 +00008 00: 00: 00.0 DEFAULT

The default AWR hour to save a mirror, the mirror data is saved eight days. In this case, AWR has no corresponding data mirroring.

SQL> select snap_id, dbid, startup_time from dba_hist_snapshot;

  SNAP_ID DBID STARTUP_TIME

---------- ---------- ------------------------------ --------------------------------------------------

2, to ease the problem

General database failure, is usually not a separate policy can be solved. I believe that: in order of priority problem, the solution can be divided drastic warming. The key trade-offs depending on the scene priority. In this case, AWR restoration work, increase sysaux table space is the primary storage needs.

This operation is relatively simple, as long as the individual location and automatically expand to allow file.

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 information is recorded.

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

Manual test generation AWR mirror to determine whether the problem is resolved.

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

No new error message appears in the log. We can consider that the initial problem is solved. The next step is to locate the question: Why would the situation sysaux bursting occurs.

3, deep analysis

AWR and other performance collection, will indeed continue to collect data to be recorded inside sysaux table space. I always thought: Any correct data architecture model, a necessary condition is that "there is a carry out there." Data continue to accumulate, there must be a mechanism (within the system or outside the system) allows data from the system. From the microscopic point of view, the data table to maintain a stable volumetric capacity.

AWR system is indeed the case. At the time of the continuous collection of data will be based on Retention rules (the default is eight days) Purge out the data continuously. Regardless of the setting, under normal circumstances should be sysaux a stable fixed size specification. The author of the current database has been running for a year or two, the problem would have to be a problem. Help system, there are some data in constant "quietly grown", the problem continues to slowly accumulate.

Try checking the current segment structure sysaux ranking table space to see if there is no related information.

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 < 5;

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

Questions appeared, some of the biggest objects, snapshot appears on them. Snapshot currently there is no data, whether by natural purge out, or that deleted data should not exist. See the underlying data table:

SQL> select count (*) from WRM $ _SNAPSHOT_DETAILS;

  COUNT (*)

----------

  1723102

SQL> select * from WRM $ _SNAPSHOT_DETAILS where rownum < 5;

  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 ????

Wherein the amount of data is still very large, with emphasis snap_id.

SQL> select max (snap_id), min (snap_id) from WRM $ _SNAPSHOT_DETAILS;

MAX (SNAP_ID) MIN (SNAP_ID)

------------ ------------

      233 831

"Gradually become larger," the data found, as AWR data base, has never been removed. Manually delete it? Is not it can be deleted?

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)

------------ ------------

      233 831

SQL> select max (snap_id), min (snap_id) from WRM $ _SNAPSHOT;

MAX (SNAP_ID) MIN (SNAP_ID)

------------ ------------

Deleted data, not related to the underlying data table WRM $ _SNAPSHOT_DETAILS content removal. Strange question!

After verification MOS, we found that the data table can not delete an Oracle Bug, specifically described as follows:

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.

Wherein the content, if you want to solve this problem, we need to patch operation, or under the guidance of Oracle Support manually deleted.

Because it is a production environment, coordinated, that the current policy is open to expand sysaux acceptable solution. So I intend to stop, without affecting normal system operation.

4, restore the automatic AWR collected

Finally, the need to restore AWR collected. Note: In some of the information, it is recommended beginning a snapshot, created manually, after Oracle can be automatically generated.

For quick verification, adjustments collection period is 10 minutes.

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;

      DBID SNAP_INTERVAL RETENTION TOPNSQL

---------- --------------------------------------- - ---------- --------------------------------------

1778314713 +00000 00: 10: 00.0 +00008 00: 00: 00.0 DEFAULT

Over time, you can see the data changes.

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

---------- ------------------------------

    23384 2015-09-30 16:07:51

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

---------- ------------------------------

    23384 2015-09-30 16:07:51

    23385 2015-09-30 16:39:32

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

---------- ------------------------------

    23384 2015-09-30 16:07:51

    23385 2015-09-30 16:39:32

    23386 2015-09-30 16:50:17

Recovery 60 minutes were collected.

SQL> exec dbms_workload_repository.modify_snapshot_settings (interval => 60);

PL / SQL procedure successfully completed

problem solved.

5 Conclusion

After this case, I feel there are two. First, regularly check alert log, check the system performance data situation is a very important means of operation and maintenance. Or rely on automatic monitoring tools, or rely on the daily work of operation and maintenance personnel. The so-called "rest and recuperation such as raising the tiger, the tiger will hurt big" in the operation and maintenance of the field also exists. The second is "there may be, there may not be a" problem found when analyzed according to the situation, to find a balance point technology, management and business.
     
         
         
         
  More:      
 
- Grub2 Boots the openSUSE installation image (Linux)
- Linux character device - a simple character device model (Linux)
- Oracle 10046 Event (Database)
- MySQL database to open a remote connection method (Database)
- Swift used in the application to add a local push at the specified time (Programming)
- Linux command line to put on your coat GUI (Linux)
- Perl said method B if A judge (Programming)
- The new task parallel library feature in .NET 4.6 (Programming)
- Analysis: Little Notebook facing a major security threat secure online (Linux)
- MySQL appears data too long for column 'name' at row 1 to solve (Database)
- Java string intern constant pool resolution Introduction (Programming)
- Hibernate4 The Hello World (basic environmental structures) (Programming)
- C language Bubble Sort - integer sort (Programming)
- How to install Hadoop on CentOS7 (Server)
- CentOS static IP network configuration (Linux)
- Ubuntu 14.10 / 14.04 / 12.04 installation GNOME Pie 0.5.6 (Linux)
- Protection Docker container Notice (Server)
- How to modify the Sublime in Tab four spaces (Linux)
- Transfer MySQL database to MariaDB (Database)
- Depth understanding of DB2 table space (Tablespace) (Database)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.