[20200115]重新建立awr report.txt

[20200115]重新建立awr report.txt

--//測試環境，表空間sysaux資料檔案佔用磁碟空間太大達到3G。我想回收空間我發現主要是awr相關表佔用在後面，導致無法回收。
--//測試看看，刪除awr全部資訊，重新建立看看。
--//測試過程參考連結：

1.環境：
SYS@book> @ ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.清除awr相關資訊：

SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup restrict
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

--//刪除AWR物件，執行指令碼
@ ?/rdbms/admin/catnoawr.sql

--//檢查awr相關物件是否還存在。
SYS@book> column table_name format a30 ;
SYS@book> select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';
TABLE_NAME
------------------------------
WRM$_SNAPSHOT_DETAILS
WRM$_WR_USAGE

--//刪除以上物件：
spool drop_awr_objs.sql
SELECT 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS;'
FROM dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';
spool off

SYS@book> host cat drop_awr_objs.sql
SYS@book> SELECT 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS;'
  2  FROM dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';
'DROPTABLE'||TABLE_NAME||'CASCADECONSTRAINTS;'
--------------------------------------------------------------
DROP TABLE WRM$_SNAPSHOT_DETAILS CASCADE CONSTRAINTS;
DROP TABLE WRM$_WR_USAGE CASCADE CONSTRAINTS;
SYS@book> spool off

--//整理刪除指令碼。
$ cat drop_awr_objs.sql
DROP TABLE WRM$_SNAPSHOT_DETAILS CASCADE CONSTRAINTS;
DROP TABLE WRM$_WR_USAGE CASCADE CONSTRAINTS;

SYS@book> @ drop_awr_objs.sql
Table dropped.
Table dropped.

SYS@book> PURGE RECYCLEBIN;
Recyclebin purged.

3.重新安裝awr：
--//執行如下：
@?/rdbms/admin/catawrtb.sql
@?/rdbms/admin/utlrp.sql

--//On 11g and above
@?/rdbms/admin/execsvrm.sql

--//If you got error
--//注：我的測試沒有遇到相關錯誤。
@?/rdbms/admin/execsvrm.sql
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYS.DBMS_SWRF_INTERNAL" has been invalidated

--- //手工執行如下在遇到錯誤的情況下：
alter package dbms_swrf_internal compile;
alter package dbms_swrf_internal compile body;

4.測試是否可以建立awr報表：

Shutdown immediate
Startup

--// Create snapshot
exec dbms_workload_repository.create_snapshot;
--//wait for 1 min, create snapshot
exec dbms_workload_repository.create_snapshot;

--//Fetch the reports
@?/rdbms/admin/awrrpt.sql;

5.回收磁碟空間看看：
SYS@book> select max(block_id) from dba_extents where tablespace_name ='SYSAUX' ;
MAX(BLOCK_ID)
-------------
       108152

--//108152*8192/1024/1024 = 844.9375,回收到900M應該沒有問題。
SYS@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sysaux01.dbf' RESIZE 900M;
Database altered.