Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people are at a loss about the impact of full SYSAUX tablespaces on the database and what the solutions are. Therefore, this paper summarizes the causes and solutions of the problems. Through this article, I hope you can solve this problem.

1. Summary

Will the login be affected when the SYSAUX table space is full? Will it affect the normal operation of the database? What should I do with it? It is easy to think that to increase the space and delete the consumed space, the priority is to resume the production business first. For troubleshooting objects that consume a large amount of SYSAUX space, no matter which version, check to see if you hold a larger business table, or a personal intermediate table. For 12C, one of the more concerned is whether the audit_trail audit parameter of the database is on, and if the parameter value is DB, then focus on the size of the audsys component in ausdsys mode, that is, the size of the lob segment SYS_LOB0000091784C00014 $$of the audsys.CLI_SWP$459d3b9 $1 $1 table. If NONE, use the same idea as 10G and 11G to troubleshoot large objects in SYSAUX.

two。 Case analysis

1. Backup check

In the daily backup maintenance, the results collected from the backup check set found that the archive backup of multiple libraries failed.

2. Check the log output of archived backups or full database backups

3. Check the audit_trail parameter values='DB'. of the catalog library

4. View the alert log of the library:

5. View the objects (segments) that consume a large amount of SYSAUX table space:

Among them, the lob segment of the audsys component accounts for 31.5g.

6. Try to use the sys user to truncate the table corresponding to the lob segment:

It is found that the sys user also does not have permissions.

7. Use stored procedures to perform cleanup:

Begin

Dbms_audit_mgmt.clean_audit_trail (

Audit_trail_type = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED

Use_last_arch_timestamp = > FALSE)

End

/

Attached: for the cleaning methods provided on the official website of oracle, see:

Https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_audit_mgmt.htm#ARPLS65414

1 > .exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (-

AUDIT_TRAIL_TYPE = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,-

LAST_ARCHIVE_TIME = > sysdate)

2 > .exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (-

AUDIT_TRAIL_TYPE = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,-

USE_LAST_ARCH_TIMESTAMP = > TRUE)

8. Check the release of sysaux table space and adjust the audit parameter audit_trail parameter values='NONE'.

9. Restart the database, and the archived backups of multiple libraries return to normal.

Description of 3.SYSAUX tablespace description of 3.1SYSAUX tablespace official website

The SYSAUX tablespace is always created at database creation. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEMtablespace. Because it is the default tablespace for many Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace.

You can specify only datafile attributes for the SYSAUX tablespace, using the SYSAUX DATAFILE clause in the CREATE DATABASE statement. Mandatory attributes of the SYSAUX tablespace are set by Oracle Database and include:

PERMANENT

READ WRITE

EXTENT MANAGMENT LOCAL

SEGMENT SPACE MANAGMENT AUTO

You cannot alter these attributes with an ALTER TABLESPACE statement, and any attempt to do so will result in an error. You cannot drop or rename the SYSAUX tablespace.

The size of the SYSAUX tablespace is determined by the size of the database components that occupy SYSAUX. See a list of allSYSAUX occupants. Based on the initial sizes of these components, the SYSAUX tablespace needs to be at least 240 MB at the time of database creation. The space requirements of the SYSAUX tablespace will increase after the database is fully deployed, depending on the nature of its use and workload. For more information on how to manage the space consumption of the SYSAUX tablespace on an ongoing basis, please refer to the "Managing the SYSAUX Tablespace"

Brief introduction of 3.2SYSAUX tablespace in Chinese

Oracle was introduced in 10G, and some database components that used independent or system tablespaces are now created in SYSAUX tablespaces. By separating these components and functions, the load on SYSTEM tablespaces is reduced. The fragmentation of SYSTEM tablespaces caused by repeated creation of related objects and components can be avoided. If the SYSAUX tablespace is not available, the core functions of the database will remain valid; using the features of the SYSAUX tablespace will fail or the function will be limited.

Major components of 3.3SYSAUX tablespaces

View the information statement for the SYSAUX tablespace component:

Col Item For a30

Col "Space Used (GB)" For A10

Col Schema For a20

Col "MoveProcedure" For A40

SELECT occupant_name "Item"

Round (space_usage_kbytes/1024,3) "Space Used (MB)"

Schema_name "Schema"

Move_procedure "MoveProcedure"

FROM v$sysaux_occupants

ORDER BY 2 Desc

The results of this script include the name of the component, the amount of space consumed by the component, the object pattern, and the stored procedures used by the component movement.

3.3.1 components of 11G

3.3.2 components of 12C

4. Measures to prevent SYSAUX table space from being full

According to the use of sysaux table space, in 10G and 11G, if you add a data file to sysaux after building the database, and there is no production business table or personal intermediate table, the growth of the table space is relatively slow and relatively stable.

4.1 authority prevention

Permissions to prevent production users or individuals from putting tables into SYSAUX tablespaces.

1. When creating an account, it is forbidden to configure the user's DEFAUL TABLESPACE as SYSAUX.

2. No matter the production account or personal account, try not to authorize UNLIMITED TABLESPACE to these users, or quota unlimited on SYSAUX, of course, according to the actual production business.

4.2 Parameter Prevention

For 12C, if there are no special requirements, the adjustment parameter audit_trail is NONE and the database restart takes effect.

4.3 Storage Prevention

Increase the SYSAUX tablespace appropriately.

4.4 Snapshot and statistics retention policy 4.4.1 adjust the preservation policy for data statistics and snapshots

1. Adjust the retention time of historical statistics:

The retention time for modifying statistics is 31 days by default. Here, it is changed to 7 days, and expired statistics will be deleted automatically.

Sys@PROD > select dbms_stats.get_stats_history_retention from dual

GET_STATS_HISTORY_RETENTION

-

thirty-one

2. Modify the retention time of statistical information:

Sys@PROD > exec dbms_stats.alter_stats_history_retention (10)

PL/SQL procedure successfully completed.

3. Check the number of days that historical statistics are retained again:

Sys@PROD > select dbms_stats.get_stats_history_retention from dual

GET_STATS_HISTORY_RETENTION

-

ten

4.4.2 adjust the save time of AWR snapshots

For example, it is changed to 7 days (7: 24: 60) and collected every hour. By default, the retention time of the current system is 8 days, and samples are taken once an hour.

1. View the current snapshot retention time and collection cycle:

Sys@PROD > col RETENTION for A20

Sys@PROD > col SNAP_INTERVAL for A25

Sys@PROD > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

338469376 + 00000 01 DEFAULT 00lv 00.0 + 00008 00lv 00lv 00.0

2. View the current MOVING_WINDOW_SIZE of the system:

Sys@PROD > select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline

DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE

338469376 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8

3. Try to adjust the collection time of AWR snapshots every 60 minutes and the retention time for 7 days:

Sys@PROD > begin

2 dbms_workload_repository.modify_snapshot_settings (

3 interval = > 60

4 retention = > 10080

5 topnsql = > 50

6)

7 end

8 /

Begin

*

ERROR at line 1:

ORA-13541 system moving window baseline size (691200) greater than retention (604800)

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line

ORA-06512: at line 2

4. First adjust the time window that is smaller than the current reservation:

Sys@PROD > exec dbms_workload_repository.modify_baseline_window_size (7)

PL/SQL procedure successfully completed.

5. Adjust the collection time of AWR snapshots once every 60 minutes and the retention time for 7 days:

Sys@PROD > begin

2 dbms_workload_repository.modify_snapshot_settings (

3 interval = > 60

4 retention = > 10080

5 topnsql = > 50

6)

7 end

8 /

PL/SQL procedure successfully completed.

6. After adjustment, confirm the retention time of the snapshot of AWR:

Sys@PROD > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

338469376 + 00000 01VOUR 00.0 + 00007 00VOLOR 00.0 50

5. Clean up SYSAUX space-consuming component content 5.1 clean up space by deleting AWR snapshots

1. View the SNAP_ID of the currently reserved snapshot:

Sys@PROD > select min (snap_id), max (snap_id) from dba_hist_snapshot

MIN (SNAP_ID) MAX (SNAP_ID)

--

2561 2570

2. Try to delete several earlier snapshots, such as the first 5 snapshots:

Sys@PROD > exec dbms_workload_repository.drop_snapshot_range (low_snap_id = > 2561 Magneol snapshots id = > 2561 snapshots 4)

Or:

Exec dbms_workload_repository.drop_snapshot_range (2561, 2565, 338469376)

3. View the SNAP_ID of the currently reserved snapshot:

Sys@PROD > select min (snap_id), max (snap_id) from dba_hist_snapshot

MIN (SNAP_ID) MAX (SNAP_ID)

--

2566 2570

If a large number of snapshots are retained, the process of deleting snapshots consumes CPU resources and is relatively slow, in which delete operations are performed on partition tables starting with WRH$_. So when we look at the SNAP_ID, the old snapshot has been deleted, but the component size in the sysaux has not changed, the original size is still the same, and the sysaux tablespace has not been released. The next step is to collect the high water level of the WRH$_ related tables, and then collect the statistical information of the related tables.

4. Reclaim the high water level after deleting the old snapshot, for example:

11:40:03 sys@PROD > ALTER TABLE SYS.WRH$_LATCH MODIFY PARTITION WRH$_LATCH_338469376_0 SHRINK SPACE

Table altered.

Elapsed: 00:00:13.11

11:42:36 sys@PROD > ALTER TABLE SYS.WRH$_SQLSTAT MODIFY PARTITION WRH$_SQLSTA_338469376_0 SHRINK SPACE

Table altered.

Elapsed: 00:00:12.55

5. Or recycle more meters with high water level:

Alter table WRH$_SQLSTAT shrink space

Alter table WRH$_SYSSTAT shrink space

Alter table WRH$_SEG_STAT shrink space

Alter table WRH$_LATCH shrink space

......

6. Collect the statistical information of the table after recycling the high water level:

Execute dbms_stats.gather_table_stats (ownname = > 'sys',tabname = >' WRH$_LATCH MODIFY',partname = > 'WRH$_LATCH_338469376_0',DEGREE= > 4)

Execute dbms_stats.gather_table_stats (ownname = > 'sys',tabname = >' WRH$_SQLSTAT',partname = > 'WRH$_SQLSTA_338469376_0',DEGREE= > 4)

5.2 clear the space by deleting old statistics

23:46:54 sys@PROD > exec dbms_stats.purge_stats (sysdate-6)

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.70

Alter table WRH$_SQLSTAT shrink space

Alter table WRH$_SYSSTAT shrink space

Alter table WRH$_SEG_STAT shrink space

Alter table WRH$_LATCH shrink space

......

5.3 delete part of the history table to clean up the space

Delete and shrink some historical statistics tables, such as deleting the invalid ASH historical session record table wrh$_active_session_history

1. View invalid historical session records:

SELECT COUNT (1) Orphaned_ASH_Rows

FROM wrh$_active_session_history a

WHERE NOT EXISTS

(SELECT 1

FROM wrm$_snapshot

WHERE snap_id = a.snap_id

AND dbid = a.dbid

AND instance_number = a.instance_number

);

2. Delete invalid historical session records:

DELETE FROM wrh$_active_session_history a

WHERE NOT EXISTS (SELECT 1

FROM wrm$_snapshot

WHERE snap_id = a.snap_id

AND dbid = a.dbid

AND instance_number = a.instance_number)

23392228 rows deleted.

SQL > commit

3. Recovery of high water level:

Alter table wrh$_active_session_history shrink space

After collection, the statistics of the table are re-collected.

5.4 Clean up spaces by moving some component content to other tablespaces

From the results of looking at the components of the SYSAUX tablespace above, you can see that the contents of the MoveProcedure field in the results provide a stored procedure to move the contents of the component, which can be moved followed by the parameters of the target tablespace, and of course, it can also be moved back to the SYSAUX tablespace, provided that the target tablespace is large enough. For example, for Logminer, migrate from SYSAUX tablespace to users tablespace, and restore it back:

1. View the tablespace before moving:

2. Move Logminer to users tablespace:

21:39:40 sys@PROD > exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE ('USERS')

PL/SQL procedure successfully completed.

3. Migrate Logminer components to SYSAUX tablespace:

22:07:55 sys@PROD > exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE ('SYSAUX')

PL/SQL procedure successfully completed.

Add: part 5: sysaux consumes a lot of space, which is also a case often encountered in the past:

1. To understand the purpose of the components of the sysaux tablespace, the three components that are most likely to encounter space growth are the following:

SM/ADVISOR: refers to the data generated by sql tuning advisor, sql access advisor, ADDM and other automatic maintenance tasks in the database

Snapshot information of SM/AWR:AWR. The space size depends on the frequency and retention time of the snapshot.

SM/OPTSTAT: historical version data used to store optimizer statistics.

two。 Why are there regular clean-up tasks, or is there abnormal space growth?

In case 1, there is a data retention period. For example, the snapshot retention time is 31 days by default. Expired data will be automatically deleted by the database's MMON process on a regular basis. However, this process only runs for 5 minutes at a time, and the cleanup task will be interrupted after 5 minutes, so data cleanup may not be successful or complete. This problem oracle provides a patch in the new version, changing the large table to a partitioned table by day, so that the cleaning method will be changed to truncate.

In case 2, the default retention period is too long. For example, the default retention period of the running log for task scheduling in dba_scheduler_job_classes is 1000000 days.

3. How to deal with it?

Generally, instead of using delete and then shrink space to clean up the space, call the cleanup functions implemented by oracle itself, such as using DBMS_SCHEDULER.PURGE_LOG () to clean up the logs run by the cleanup task, and dbms_stats.purge_stats (dbms_stats.PURGE_ALL) to clean up all statistics.

Summary

As for the impact on the database when the SYSAUX is full, from the current cases encountered, that is, when the audit parameter audit_trail of the 12C database is DB, the table space is full, which directly affects the user logging in to the database, and 10G and 11G are not affected. After some simulation tests, in 12C and 11G versions, when audit_trail is NONE, SYSAUX is full, which does not affect the availability of the database. For the other components of the SYSAUX tablespace, more test areas are needed to explore, and the content of each component is cleaned up and moved, roughly the same.

The above is a personal experience of a case, test results and some views, if there are any mistakes or mistakes, I hope the gods point out and give advice, in the future time, to supplement, improve and learn.

Attached:

New features of 12C security audit:

Oracle Database 12c Security: New Unified Auditing

Connection 1: https://blogs.oracle.com/imc/oracle-database-12c-security:-new-unified-auditing

Connection 2: https://www.cnblogs.com/shenfeng/p/oracle_12c_sysaux_clean.html

# # the new feature of 12C here means that two audits (standard audit and fine-grained audit are merged into one audit) are placed in the same table

After reading the above, have you mastered the impact of full SYSAUX tablespaces on the database and what are the solutions? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.