Oracle EXPDP自動備份緩慢問題解決

Oracle EXPDP自動備份緩慢問題解決

問題現象：

資料庫每天兩次expdp備份，中午一次，晚上一次，最近連續兩天發現，中午備份正常，耗時 2 個小時，晚上備份確需要 5 小時；

環境：

DB:Oracle 11.2.0.1.0

OS:Windows Server 2012

問題分析：

懷疑晚上備份時間段，資料庫內部或資料庫作業系統存在大的作業，導致資源爭用；

生成晚上備份時間段AWR報告，通過 TOP SQL 可以看到，除了第一個 expdp 作業特別耗時以外，第二和第三也特別耗時；

Oracle EXPDP自動備份緩慢問題解決

詳細SQL如下：

SQL一：執行 3 個小時沒有執行完成；

call dbms_space.auto_space_advisor_job_proc ( )

SQL二：執行 4 個小時沒有執行完成， Oracle 通過 CTAS 方式自動備份了資料庫裡最大的一張表，佔用大量資源，併產生大量歸檔檔案；

create table "CHENJCH".DBMS_TABCOMP_TEMP_UNCMP tablespace "CHENJCH_TBS_STANDARD" nologging as select /*+ FULL("CHENJCH"."T_BAS_XXX") */ * from "CHENJCH"."T_BAS_XXX" sample block( 99) mytab

二個耗時 SQL 都是由 DBMS_SCHEDULER 中 auto_space_advisor 產生的；

可以看到最近幾天auto space advisor執行時間突然增加到 4 個小時；

select client_name , job_start_time , job_duration

from dba_autotask_job_history

where client_name = 'auto space advisor'

order by 2 desc ;

Oracle EXPDP自動備份緩慢問題解決

解決方案：禁用auto space advisor

select client_name , status from dba_autotask_client ;

Oracle EXPDP自動備份緩慢問題解決

begin

DBMS_AUTO_TASK_ADMIN.DISABLE ( client_name => 'auto space advisor' ,

operation => NULL ,

window_name => NULL );

end ;

Oracle EXPDP自動備份緩慢問題解決

禁用後備份時間縮短到兩個小時；

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=1dn99viqdd_9&_afrLoop=419884478500803

How to Stop Creating Table DBMS_TABCOMP_TEMP_UNCMP? (文件 ID 1326118.1)

How to Stop Creating Table DBMS_TABCOMP_TEMP_UNCMP? ( 文件 ID 1326118.1)

轉到底部

修改時間:2018-8-4 型別:PROBLEM

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.

SYMPTOMS

After upgrading to 11g, during running of the Automatic Segment Advisor, the table dms_tabcomp_temp_uncmp is being created and is taking up lots of space.

Note: You may see ORA-1652 error: Unable to extend temp segment.

CAUSE

The issue is related to unpublished Bug 8896202: "ENABLE COMPRESSION ADVISOR TO ESTIMATE EXADATA HCC COMPRESSION RATIOS."

SOLUTION

The following solutions are available:

1. Apply one-off Patch 8896202 for unpublished Bug 8896202, if available.
This fix will stop excessive redo log from being generated due to this table.

2. Apply 11.2.0.2 patchset where fix is included.

3. Workaround: Disable the Automatic Segment Adviser, as this is what makes the call to the Compression Advisor in 11.2. These steps can be found in the "Configuring the Automatic Segment Advisor" section of the Oracle Database Administrator's Guide 11g Release 2 (11.2) . Here is the excerpt from the guide:

Configuring the Automatic Segment Advisor
The Automatic Segment Advisor is an automated maintenance task. As such, you can use Enterprise Manager or PL/SQL package procedure calls to modify when (and if) this task runs. You can also control the resources allotted to it by modifying the appropriate resource plans.

You can call PL/SQL package procedures to make these changes, but the easier way to is to use Enterprise Manager.

To configure the Automatic Segment Advisor task with Enterprise Manager:

1. Log in to Enterprise Manager as user SYSTEM.

2. On the Database Home page, under the Space Summary heading, click the numeric link next to the label Segment Advisor Recommendations. The Segment Advisor Recommendations page appears.

3. Under the Related Links heading, click the link entitled Automated Maintenance Tasks. The Automated Maintenance Tasks page appears.

4. Click Configure. The Automated Maintenance Tasks Configuration page appears.

5. To completely disable the Automatic Segment Advisor, under Task Settings, select Disabled next to the Segment Advisor label, and then click Apply.

REFERENCES

NOTE:19047.1 - OERR: ORA-1652 "unable to extend temp segment by %s in tablespace %s" Reference Note

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2219526/，如需轉載，請註明出處，否則將追究法律責任。

Oracle EXPDP自動備份緩慢問題解決

相關文章