如何重建SYSAUX表空间上的对象

SQL> select file_id,file_name from dba_data_files where tablespace_name='SYSAUX'; FILE_ID ---------- FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------- 2 /s01/oradata/FIXIT/datafile/o1_mf_sysaux_85wkhmrk_.dbf SQL> alter database datafile 2 offline; Database altered. 假设这里datafile 2即SYSAUX所有的数据文件都丢失了 且没有任何备份，此时我们无法利用任何备份还原该表空间 但是SYSAUX表空间却是数据库必要的系统表空间之一，存放了 AWR等重要数据和其他一些组件的辅助数据 SQL> exec dbms_workload_repository.create_snapshot; BEGIN dbms_workload_repository.create_snapshot; END; * ERROR at line 1: ORA-13509: error encountered during updates to a AWR table ORA-00376: file ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/s01/oradata/FIXIT/datafile/o1_mf_sysaux_85wkhmrk_.dbf' cannot be read at this time ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 99 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 122 ORA-06512: at line 1   我们需要在 丢失SYSAUX所有数据文件及其备份的情况下 重建该表空间上的对象   除了以上彻底丢失 数据文件的情况， SYSAUX 数据文件存在某些逻辑或物理坏块也可能是重建该表空间上对象的理由 例如以下错误： ORA-00600: [kcbz_check_objd_typ] from MMON slave or its process ORA-00600: [kdsgrp1] while querying WR% tables from SYSAUX 对应不同的AWR负载基表， $ORACLE_HOME/rdbms/admin/目录下存有相关这些系统表的创建脚本，例如： WRI$_OPTSTAT catost.sql - Optimizer Statistics Tables WRI$_ALERT catalrt.sql - Catalog script for server ALeRT WRH$_* catawrtb.sql - Catalog script for AWR Tables catawrvw.sql - Catalog script for AWR Views 接下来我们开始着手重建SYSAUX上的对象，请注意recreate sysaux上的对象是在正常恢复之外的非常规手段，也是恢复SYSAUX的最后一步，不要在产品环境中滥用以下手段：   1. 如果是丢失数据文件导致的问题，那么 先在原SYSAUX表空间加入一个 数据 文件 SQL> alter tablespace sysaux add datafile size 500M; Tablespace altered. 2. 重建对象的第一步是禁用AWR、ASH、ADDM 对于10g执行以下脚本： -- 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; 对于11g直接使用control_management_pack_access 参数 alter system set control_management_pack_access = NONE scope = both;     3. 第二步drop并重建SYSAUX上的数据库监控统计对象： 对于10gR1 执行以下脚本： sqlplus /nolog connect / as sysdba @?/rdbms/admin/catnoawr.sql alter system flush shared_pool; @?/rdbms/admin/catawr.sql   对于 10gR2 执行以下脚本： sqlplus /nolog connect / as sysdba @?/rdbms/admin/catnoawr -- @?/rdbms/admin/catnomwn -- fails with ORA-27479: @?/rdbms/admin/catnomtr @?/rdbms/admin/catnoalr @?/rdbms/admin/catnofus -- Some objects don't have a DROP script, they will have to -- WRR$ drop SEQUENCE WRR$_CAPTURE_ID ; drop TABLE WRR$_CAPTURES ; drop TABLE WRR$_CAPTURE_STATS ; drop TABLE WRR$_FILTERS ; -- WRM$ drop TABLE WRM$_WR_USAGE ; -- WRI$ drop SEQUENCE WRI$_SQLSET_ID_SEQ ; drop SEQUENCE WRI$_SQLSET_REF_ID_SEQ ; drop SEQUENCE WRI$_SQLSET_STMT_ID_SEQ ; drop SEQUENCE WRI$_ADV_SEQ_TASK ; drop SEQUENCE WRI$_ADV_SEQ_DIR ; drop SEQUENCE WRI$_ADV_SEQ_JOURNAL ; drop SEQUENCE WRI$_ADV_SEQ_MSGGROUP ; drop SEQUENCE WRI$_ADV_SEQ_SQLW_QUERY ; drop TABLE WRI$_ADV_DEFINITIONS ; drop TABLE WRI$_ADV_TASKS ; drop TABLE WRI$_ADV_DEF_PARAMETERS ; drop TABLE WRI$_ADV_PARAMETERS ; drop TABLE WRI$_ADV_OBJECTS ; drop TABLE WRI$_ADV_FINDINGS ; drop TABLE WRI$_ADV_RECOMMENDATIONS ; drop TABLE WRI$_ADV_ACTIONS ; drop TABLE WRI$_ADV_RATIONALE ; drop TABLE WRI$_ADV_REC_ACTIONS ; drop TABLE WRI$_ADV_DIRECTIVES ; drop TABLE WRI$_ADV_JOURNAL ; drop TABLE WRI$_ADV_MESSAGE_GROUPS ; drop TABLE WRI$_ADV_USAGE ; drop TABLE WRI$_ADV_SQLW_SUM ; drop TABLE WRI$_ADV_SQLW_STMTS ; drop TABLE WRI$_ADV_SQLW_TABLES ; drop TABLE WRI$_ADV_SQLW_TABVOL ; drop TABLE WRI$_ADV_SQLW_COLVOL ; drop TABLE WRI$_ADV_SQLA_MAP ; drop TABLE WRI$_ADV_SQLA_STMTS ; drop TABLE WRI$_ADV_SQLA_TMP ; drop TABLE WRI$_ADV_SQLA_FAKE_REG ; drop TABLE WRI$_SEGADV_OBJLIST ; drop TABLE WRI$_SEGADV_CNTRLTAB ; drop TABLE WRI$_ADV_ASA_RECO_DATA ; drop TABLE WRI$_OPTSTAT_TAB_HISTORY ; drop TABLE WRI$_OPTSTAT_IND_HISTORY ; drop TABLE WRI$_OPTSTAT_HISTHEAD_HISTORY ; drop TABLE WRI$_OPTSTAT_HISTGRM_HISTORY ; drop TABLE WRI$_OPTSTAT_AUX_HISTORY ; drop TABLE WRI$_OPTSTAT_OPR ; drop TABLE WRI$_ADV_SQLT_BINDS ; drop TABLE WRI$_ADV_SQLT_STATISTICS ; drop TABLE WRI$_ADV_SQLT_PLANS ; drop TABLE WRI$_ADV_SQLT_RTN_PLAN ; drop TABLE WRI$_SQLSET_DEFINITIONS ; drop TABLE WRI$_SQLSET_REFERENCES ; drop TABLE WRI$_SQLSET_STATEMENTS ; drop TABLE WRI$_SQLSET_PLANS ; drop TABLE WRI$_SQLSET_PLANS_TOCAP ; drop TABLE WRI$_SQLSET_STATISTICS ; drop TABLE WRI$_SQLSET_MASK ; drop TABLE WRI$_SQLSET_PLAN_LINES ; drop TABLE WRI$_SQLSET_BINDS ; drop TABLE WRI$_SQLSET_WORKSPACE ; drop TABLE WRI$_ADV_OBJSPACE_CHROW_DATA ; drop TABLE WRI$_ADV_OBJSPACE_TREND_DATA ; drop TYPE WRI$_ADV_ABSTRACT_T ; drop TYPE WRI$_ADV_HDM_T ; drop TYPE WRI$_ADV_SQLACCESS_ADV ; drop TYPE WRI$_ADV_TUNEMVIEW_ADV ; drop TYPE WRI$_ADV_WORKLOAD ; drop TYPE WRI$_ADV_UNDO_ADV ; drop TYPE WRI$_ADV_SQLTUNE ; drop TYPE WRI$_ADV_OBJSPACE_TREND_T ; drop table WRM$_SNAPSHOT_DETAILS;   在10gR2中重建对象的脚本如下： alter system flush shared_pool; alter system flush shared_pool; alter system flush shared_pool; @?/rdbms/admin/catadvtb.sql @?/rdbms/admin/catsvrm.sql @?/rdbms/admin/catsqlt.sql @?/rdbms/admin/catsqltv.sql @?/rdbms/admin/catadv.sql @?/rdbms/admin/catost.sql @?/rdbms/admin/dbmsslrt.sql @?/rdbms/admin/catawrtb.sql 版本10.2.0.2 中可能缺失catnoawr.sql脚本，这不要紧 可以从版本10.2.0.3中拷贝过来用   3. 重新启用AWR、ASH、ADDM 10g执行以下脚本 -- To install, run the package as SYS from SQL*Plus: @dbmsnoawr.plb -- To execute the package use the command: begin dbms_awr.enable_awr(); end;   11g 执行以下脚本 alter system set control_management_pack_access = 'DIAGNOSTIC+TUNING' scope = both;   4. 尝试做一个AWR快照确保重建已经成功 -- Take the AWR snaps exec dbms_workload_repository.create_snapshot; -- wait for 5 min exec dbms_workload_repository.create_snapshot; -- Create the AWR report, run below script @$ORACLE_HOME/rdbms/admin/awrrpt.sql