1. 首页
  2. IT资讯

[20200115]重新建立awr report.txt

[20200115]重新建立awr report.txt

–//测试环境,表空间sysaux数据文件占用磁盘空间太大达到3G。我想回收空间我发现主要是awr相关表占用在后面,导致无法回收。
–//测试看看,删除awr全部信息,重新建立看看。
–//测试过程参考链接:https://smarttechways.com/2020/01/03/recreate-or-reconfigure-the-awr-report-in-oracle/

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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2673515/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183088.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code