标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (103)
- 数据库 (1,716)
- DB2 (22)
- MySQL (74)
- Oracle (1,576)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (160)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (68)
- Oracle Bug (8)
- Oracle RAC (54)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (575)
- Oracle安装升级 (94)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (81)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (28)
- SQL Server恢复 (9)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 不当使用_allow_resetlogs_corruption参数引起ORA-600 2662错误
- CSSD signal 11 in thread clssnmRcfgMgrThread故障处理
- 使用sid方式直接访问pdb(USE_SID_AS_SERVICE_LISTENER)
- ORA-00069: cannot acquire lock — table locks disabled for xxxx
- ORA-600 [4000] [a]相关bug
- sql server数据库“正在恢复”故障处理
- 如何判断数据文件是否处于begin backup状态
- CDM备份缺少归档打开数据库报ORA-600 kcbzib_kcrsds_1故障处理
- ORA-07445: exception encountered: core dump [expgod()+43] [IN_PAGE_ERROR]
- 2025年第一起ORA-600 16703故障恢复
- _gc_undo_affinity=FALSE触发ORA-01558
- public授权语句
- 中文环境显示AR8MSWIN1256(阿拉伯语字符集)
- 处理 Oracle 块损坏
- Oracle各种类型坏块说明和处理
- fio测试io,导致磁盘文件系统损坏故障恢复
- ORA-742 写丢失常见bug记录
- Oracle 19c 202501补丁(RUs+OJVM)-19.26
- 避免 19c 数据库性能问题需要考虑的事项 (Doc ID 3050476.1)
- Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium – ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2]
标签归档:重建awr
How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository—重建awr
由于某种原因,比如数据异常断电,导致awr数据严重不一致,awr部分表损坏等情况,需要重建awr,可以参考如下步骤进行重建,本文主要针对目前主流的10g和11g版本数据库,12c未进行测试
停止awr自动收集信息
方法1:参数调整
sqlplus /nolog connect / as sysdba create pfile='/tmp/pfile.xifenfei' from spfile; alter system set shared_pool_size = 200m scope = spfile; alter system set db_cache_size = 300m scope = spfile; alter system set java_pool_size = 100m scope = spfile; alter system set large_pool_size = 50m scope = spfile; --内存值可以根据实际情况调整 alter system reset sga_target scope = spfile sid='*'; alter system set statistics_level=basic scope=spfile; --11G alter system reset memory_target scope= spfile sid='*'; alter system reset memory_max_target scope=spfile sid='*'; alter system set sga_target=0 scope= spfile; alter system set memory_target=0 scope= spfile; --RAC alter system set cluster_database = false scope = spfile;
方法2:使用包/参数
For 10g, you need to download the package DBMS_AWR.DISABLE_AWR available at Note 436386.1 Package for disabling AWR without a Diagnostic Pack license in Oracle To install, run the package as SYS from SQL*Plus: @dbmsnoawr.plb To execute the package, use the command: begin dbms_awr.disable_awr(); end; For 11g, use the parameter control_management_pack_access to disable it alter system set control_management_pack_access = NONE scope = both;
方法1:需要重启数据库
如果选择方法2,忽略此步骤
sqlplus /nolog connect / as sysdba shutdown immediate startup restrict
删除AWR
start ?/rdbms/admin/catnoawr.sql --由于Bug 5376177在10.2.0.1/2中可能没有catnoawr.sql文件,可以从10.2.0.3/4中拷贝过来 alter system flush shared_pool; --验证awr数据数据删除情况 select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%'; 如果有记录存在,使用drop table 语句删除
创建AWR
start ?/rdbms/admin/catawrtb.sql start ?/rdbms/admin/utlrp.sql --11G start ?/rdbms/admin/execsvrm.sql alter package dbms_swrf_internal compile; alter package dbms_swrf_internal compile body; start ?/rdbms/admin/execsvrm.sql
重启数据库
方法1对应处理
create spfile from pfile='/tmp/pfile.xifenfei'; shutdown immediate startup
方法2对应处理
--11g alter system set control_management_pack_access = 'DIAGNOSTIC+TUNING' scope = both; shutdown immediate startup --10g @dbmsnoawr.plb begin dbms_awr.enable_awr();end;
处理无效对象
spool objects.lst set pagesize500 set linesize 100 select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name; select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type; select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ; spool off alter package <schema name>.<package_name> compile; alter package <schema name>.<package_name> compile body; alter view <schema name>.<view_name> compile; alter trigger <schema).<trigger_name> compile;
测试AWR
--收集快照 exec dbms_workload_repository.create_snapshot; --wait for 5 min exec dbms_workload_repository.create_snapshot; --生成awr报告 start $ORACLE_HOME/rdbms/admin/awrrpt.sql
注意BUG
Bug:17063159 CATNOAWR.SQL NOT DROPPING ALL AWR TABLES
Bug:10211252 ‘DROP TABLE WRM$_WR_USAGE MISSING IN CATNOAWR.SQL
Bug:9150463 CANNOT RECREATE THE AWR ON R11.1
参考文档
How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? (Doc ID 782974.1)
How to Recreate Tables in the SYSAUX Tablespace (Doc ID 333665.1)