SYSAUX表空间重建测试 (http://www.itpub.net/forum.php?mod=viewthread&tid=1477610&highlight=sysaux)
一位网友问我，SYSAUX表空间坏了，文件无法读取，而且没有备份，怎么办？我冒出了一个想法：重建。于是有了这个试验

看看官方文档中对sysaux表空间的描述：
The SYSAUX tablespace is always created at database creation. The SYSAUX tablespace
serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default
tablespace for many Oracle Database features and products that previously required
their own tablespaces, it reduces the number of tablespaces required by the database.
It also reduces the load on the SYSTEM tablespace.

可以看到，SYSAUX表空间是SYSTEM的辅助表空间，从名称上就能看出来--SYStem AUXililary。它在oracle10g中引入，用以分担SYSTEM表空间的负载以及对一些组件的集中管理。它不是必需的，但如果没有SYSAUX，很多功能将无法使用。以下是我的测试库中SYSAUX中存放的组件:

SQL> col occupant_name format a30
SQL> col schema_name format a30
SQL> select occupant_name,schema_name from v$sysaux_occupants;

OCCUPANT_NAME                  SCHEMA_NAME
------------------------------ ------------------------------
LOGMNR                         SYSTEM
LOGSTDBY                       SYSTEM
STREAMS                        SYS
XDB                            XDB
AO                             SYS
XSOQHIST                       SYS
XSAMD                          OLAPSYS
SM/AWR                         SYS
SM/ADVISOR                     SYS
SM/OPTSTAT                     SYS
SM/OTHER                       SYS
STATSPACK                      PERFSTAT
ODM                            DMSYS
SDO                            MDSYS
WM                             WMSYS
ORDIM                          ORDSYS
ORDIM/PLUGINS                  ORDPLUGINS
ORDIM/SQLMM                    SI_INFORMTN_SCHEMA
EM                             SYSMAN
TEXT                           CTXSYS
ULTRASEARCH                    WKSYS
ULTRASEARCH_DEMO_USER          WK_TEST
EXPRESSION_FILTER              EXFSYS
EM_MONITORING_USER             DBSNMP
TSM                            TSMSYS
JOB_SCHEDULER                  SYS

26 rows selected.

下面我们做个试验，删除sysaux表空间数据文件，并重建sysaux表空间

环境
RHEL 4.7 x86 + ORACLE 10.2.0.5 单节点RAC +ASM
安装的可选组件有：
Oracle JVM
Oracle XML DB
Oracle Intermedia
Oracle Data Mining
Oracle Text
Oracle OLAP
Oracle Spatial
EnterPrise Manager Repository
删除SYSAUX表空间对应的数据文件
删除SYSAUX数据文件
[oracle@rac tmp]$ export ORACLE_SID=+ASM1
[oracle@rac tmp]$ asmcmd
ASMCMD> cd ORADATA_DG/oradb/datafile
ASMCMD> ls
SYSAUX.257.707141009
SYSTEM.256.707141009
UNDOTBS1.258.707141009
UNDOTBS1.267.759631847
USERS.259.707141011
W.266.756293967
ASMCMD> rm -f sysaux*
ASMCMD> ls
SYSTEM.256.707141009
UNDOTBS1.258.707141009
UNDOTBS1.267.759631847
USERS.259.707141011
W.266.756293967
ASMCMD>

 

启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1275104 bytes
Variable Size             209718048 bytes
Database Buffers          390070272 bytes
Redo Buffers                7110656 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '+ORADATA_DG/oradb/datafile/sysaux.257.707141009'

由于找不到数据文件，数据库停在了mounted状态

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL>

 

SQL> select file#,name from v$datafile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------         1
+ORADATA_DG/oradb/datafile/system.256.707141009

         2
+ORADATA_DG/oradb/datafile/undotbs1.258.707141009

         3
+ORADATA_DG/oradb/datafile/sysaux.257.707141009

     FILE#
----------
NAME
--------------------------------------------------------------------------------         4
+ORADATA_DG/oradb/datafile/users.259.707141011

         5
+ORADATA_DG/oradb/datafile/w.266.756293967

         6
+ORADATA_DG/oradb/datafile/undotbs1.267.759631847

6 rows selected.

将该数据文件offline后启动实例到open状态

SQL> alter database datafile 3 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL>

SQL> select status from dba_tablespaces where tablespace_name='SYSAUX';

STATUS
---------
ONLINE

SQL> select file_name,status,online_status from dba_data_files where tablespace_name='SYSAUX';

FILE_NAME
-------------------------------------------------------  STATUS    ONLINE_--------- -------
+ORADATA_DG/oradb/datafile/sysaux.257.707141009         AVAILABLE  OFFLINE
SQL>

备份数据库
由于SYSAUX表空间不可用，RMAN和EXP均无法正常使用：

[oracle@rac ~]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Aug 23 17:00:54 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORADB (DBID=2460405854)

RMAN> backup database;

Starting backup at 23-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=513 instance=oradb1 devtype=DISK
could not read file header for datafile 3 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/23/2011 17:01:09
RMAN-06056: could not access datafile 3

RMAN> exit

Recovery Manager complete.
[oracle@rac ~]$ exp system/aaa file=/bak/expdata/exp.dmp owner=a rows=y

Export: Release 10.2.0.5.0 - Production on Tue Aug 23 17:02:06 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user A
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user A
About to export A's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '+ORADATA_DG/oradb/datafile/sysaux.257.707141009'
EXP-00000: Export terminated unsuccessfully
[oracle@rac ~]$

 

但可以按表空间导出

[oracle@rac ~]$ exp system/aaa file=/bak/expdata/exp.dmp tablespaces=users statistics=none compress=n

Export: Release 10.2.0.5.0 - Production on Tue Aug 23 17:07:26 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export selected tablespaces ...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
. . exporting table                              A      49630 rows exported
. . exporting table                              B      49630 rows exported
. . exporting table                              A      49630 rows exported
. . exporting table                              C      49630 rows exported
. . exporting table                              E      49630 rows exported
. . exporting table                              F      49630 rows exported
. . exporting table                              G      49630 rows exported
. . exporting table                           TEST         99 rows exported
. . exporting table                              A          1 rows exported
. . exporting table                           TEST          0 rows exported
. . exporting table                           TEST      50097 rows exported
. . exporting table                       TEST_TMP
. . exporting partition                             T1          0 rows exported
. . exporting table                              T          0 rows exported
. . exporting table                              T     150303 rows exported
. . exporting table                          T_TMP
. . exporting partition                             T1          0 rows exported
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
[oracle@rac ~]$

[oracle@rac ~]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Aug 23 17:08:20 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORADB (DBID=2460405854)

RMAN> backup tablespace users;

Starting backup at 23-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=519 instance=oradb1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=+ORADATA_DG/oradb/datafile/users.259.707141011
channel ORA_DISK_1: starting piece 1 at 23-AUG-11
channel ORA_DISK_1: finished piece 1 at 23-AUG-11
piece handle=+RECOVERY_DG/oradb/backupset/2011_08_23/nnndf0_tag20110823t170835_0.291.759949715 tag=TAG20110823T170835 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 23-AUG-11

Starting Control File and SPFILE Autobackup at 23-AUG-11
piece handle=/bak/orabak/c-2460405854-20110823-00 comment=NONE
Finished Control File and SPFILE Autobackup at 23-AUG-11

RMAN>

删除使用SYSAUX的一些组件
先将部分组件先删除，后续再重建。

先以migrate模式打开数据库
删除DB CONTROL
SQL> alter session set events'10851 trace name context forever,level 1';

Session altered.

SQL> drop table sysman.mgmt_notify_qtable cascade constraints;

Table dropped.

SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL> DECLARE
    CURSOR c1 IS
    SELECT owner, synonym_name name
    FROM dba_synonyms
    WHERE table_owner = 'SYSMAN';
    BEGIN
    FOR r1 IN c1 LOOP
    IF r1.owner = 'PUBLIC' THEN
    EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
   ELSE
   EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
   END IF;
   END LOOP;
   END;
   /

PL/SQL procedure successfully completed.

SQL>  DROP USER mgmt_view CASCADE;

User dropped.

SQL> DROP ROLE mgmt_user;
DROP ROLE mgmt_user
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '+ORADATA_DG/oradb/datafile/sysaux.257.707141009'

SQL>

SYSMAN用户删不干净，后续再做处理

删除ORACLE SPATIAL
首先确定没有空间索引和SDO_GEOMETRY类型的列
SQL> select owner,index_name from dba_indexes
  2  where ityp_name = 'SPATIAL_INDEX';

no rows selected

SQL> select owner, table_name, column_name
  2  from dba_tab_columns
  3  where data_type = 'SDO_GEOMETRY'
  4  and owner != 'MDSYS'
  5  order by 1,2,3;

no rows selected

SQL>

没有的话，继续

SQL> drop user MDSYS cascade;

User dropped.

SQL> set pagesize 0
SQL> set feed off
SQL> spool dropsyn.sql
SQL> select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
SQL> spool off;
SQL> @dropsyn.sql
SQL> drop user mddata cascade;

删除intermedia
[oracle@rac admin]$ cd /opt/app/oracle/product/10.2.0/db_1/ord/im/admin/
[oracle@rac admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Aug 27 01:49:43 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> @imdinst     

SQL> drop user SI_INFORMTN_SCHEMA cascade;

User dropped.

SQL> drop user ORDPLUGINS cascade;

User dropped.

SQL> drop user ORDSYS cascade;

User dropped.

SQL>

删除XDB
SQL> @?/rdbms/admin/catnoqm.sql

删除ORACLE TEXT
SQL> @?/ctx/admin/catnoctx.sql

SQL> drop procedure sys.validate_context;

Procedure dropped.

SQL>

删除DBSNMP

SQL> @?/rdbms/admin/catnsnmp.sql

删除EXPRESSION_FILTER

SQL> @?/rdbms/admin/catnoexf

SQL> declare
    cursor cur1 is select synonym_name from all_synonyms where owner = 'PUBLIC' and table_owner = 'EXFSYS';
    begin
    for c1 in cur1 loop
    EXECUTE IMMEDIATE 'drop public synonym '||dbms_assert.enquote_name(c1.synonym_name, false);
    end loop;
    end;
    /

PL/SQL procedure successfully completed.

SQL>

删除WORKSPACE MANAGER

SQL> @?/rdbms/admin/owmuinst.plb

WMSYS用户这里删不掉，留到后面处理

重建SYSAUX表空间中剩余的表到SYSAUX1表空间
虽然SYSAUX表空间已经沦为一个空架子，但表结构还在数据字典中。不能直接把这些表全部删掉，否则后面的重建会出现问题。这里使用临时改建到别的表空间的方法来变通。
建表空间SYSAUX1
SQL> CREATE TABLESPACE sysaux1 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL>

将用户的默认表空间由SYSAUX改为SYSAUX1
begin
  for x in (select username from dba_users where default_tablespace='SYSAUX') loop
execute immediate 'alter user '||x.username||' default tablespace SYSAUX1';
  end loop;
end;
/

将表从SYSAUX重建到SYSAUX1中

set serverout on
declare
v_sql varchar2(32767);
begin
  for x in (select owner,table_name,iot_name
from dba_tables
where tablespace_name='SYSAUX') loop
  begin
  select dbms_metadata.get_ddl('TABLE',nvl(x.iot_name,x.table_name),x.owner) into v_sql from dual;
  v_sql:=replace(v_sql,'"SYSAUX"','"SYSAUX1"');

  execute immediate 'drop table '|| x.owner||'.'|| nvl(x.iot_name,x.table_name)||' cascade constraints';
  execute immediate v_sql;
  exception
    when others then
      dbms_output.put_line(x.owner||'.'|| nvl(x.iot_name,x.table_name)||'  '||sqlerrm);
  end;
end loop;
end;
/

这个过程会花点时间。另开一个会话来查看转移情况
select count(case tablespace_name when 'SYSAUX' then 1 end) a,count(case tablespace_name when 'SYSAUX1' then 1 end) b from dba_tables;

重编译无效的数据库对象
SQL> @?/rdbms/admin/utlrp 

删除SYSAUX表空间无法转移的表
alter session set events'10851 trace name context forever,level 1';
begin
  for x in(
    select owner,table_name,iot_name from dba_tables where tablespace_name='SYSAUX')loop
    begin
      execute immediate 'drop table '||x.owner||'.'||nvl(x.iot_name,x.table_name)||' cascade constraints';
    exception
      when others then
     dbms_output.put_line(x.owner||'.'||nvl(x.iot_name,x.table_name));
    end;
  end loop;
end;
/

 

删除SYSAUX表空间

SQL> drop tablespace sysaux including contents and datafiles;

Tablespace dropped.

SQL>

重建SYSAUX表空间

SQL> CREATE TABLESPACE sysaux
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
  2    3

Tablespace created.

SQL> SQL>

 

执行脚本重建相关用户和组件
此时还是连exp/imp都无法正常使用，还需要做一些处理

重建相关数据字典和包
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc

重编译
@?/rdbms/admin/utlrp

修复DATA MINING组件
SQL> @?/rdbms/admin/dminst.sql  SYSAUX TEMP;

修复WORKSPACE MANAGER
先接着上面删掉wmsys用户

SQL> drop user wmsys cascade;
drop user wmsys cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24170: WMSYS.WM$EVENT_QUEUE_R is created by AQ, cannot be dropped directly
ORA-06512: at "SYS.DBMS_RULE_ADM", line 158
ORA-06512: at line 1

SQL> alter session set events '25475 trace name context forever, level 2';

Session altered.

SQL> drop user wmsys cascade;

User dropped.

SQL> alter session set events '25475 trace name context off';

Session altered.

SQL>

然后再安装该组件

SQL> @?/rdbms/admin/owminst.plb

此时进行exp导出已经完全没有问题了

重建DB CONTROL
上面的SYSMAN用户没有删掉，还留下一堆无效的对象，看着不爽，先拿它开刀。

SQL> alter session set events '25475 trace name context forever, level 2';

Session altered.

SQL> drop user sysman cascade;

User dropped.

SQL>  alter session set events '25475 trace name context off';

Session altered.

SQL> @?/sysman/admin/emdrep/sql/emreposcre ?/ SYSMAN aaa TEMP ON;

重建OLAP
[oracle@rac ~]$ cd $ORACLE_HOME/olap/admin
SQL> @catnoamd.sql
SQL> @olapidrp.plb
SQL> @catnoaps.sql
SQL> @catnoxoq.sql
SQL> @cwm2drop.sql
SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

安装ORACLE TEXT
SQL> @?/ctx/admin/catctx aaa SYSAUX TEMP NOLOCK;
SQL> conn CTXSYS/aaa
SQL> @?/ctx/admin/defaults/dr0defin.sql "AMERICAN"

安装Oracle XML DB
SQL> conn / as sysdba
SQL> @?/rdbms/admin/catqm.sql aaa SYSAUX TEMP;
SQL> @?/rdbms/admin/catxdbj.sql;
SQL> @?/rdbms/admin/catrul.sql;

安装Oracle Intermedia
SQL> @?/ord/admin/ordinst.sql SYSAUX SYSAUX;
SQL> @?/ord/im/admin/iminst.sql;

安装Oracle Spatial
SQL> @?/md/admin/mdinst.sql;

 
搞定！！！！！

可惜还是有几个系统对象处于INVALID状态，暂时无法解决。此时可以做个expdp/exp，找个合适的时间重建数据库再导入
如果不注意备份的话，可有够折腾的。呵呵