oracle sysaux表空间管理

1、简介
sysaux —–system auxiliary(辅助表空间10G引进)，用户分离sysaux工具等，避免因工具报错、异常、损坏，到时system表空间无法使用

sysaux 不能更改名字，不能drop、read only；
可以offline，但sysaux工具程序功能不能使用

查询sysaux表空间中的工具：

SQL> desc v$sysaux_occupants; Name
Null? Type
—————————————– ——– —————————- OCCUPANT_NAME VARCHAR2(64) OCCUPANT_DESC
VARCHAR2(64) SCHEMA_NAME
VARCHAR2(64) MOVE_PROCEDURE
VARCHAR2(64) MOVE_PROCEDURE_DESC
VARCHAR2(64) SPACE_USAGE_KBYTES NUMBER

    SQL> select  OCCUPANT_NAME,SCHEMA_NAME from v$sysaux_occupants;

OCCUPANT_NAME        SCHEMA_NAME
-------------------- --------------------
LOGMNR               SYSTEM
LOGSTDBY             SYSTEM
SMON_SCN_TIME        SYS
PL/SCOPE             SYS
STREAMS              SYS
AUDIT_TABLES         SYS
XDB                  XDB
AO                   SYS
XSOQHIST             SYS
XSAMD                OLAPSYS
SM/AWR               SYS

OCCUPANT_NAME        SCHEMA_NAME
-------------------- --------------------
SM/ADVISOR           SYS
SM/OPTSTAT           SYS
SM/OTHER             SYS
STATSPACK            PERFSTAT
SDO                  MDSYS
WM                   WMSYS
ORDIM                ORDSYS
ORDIM/ORDDATA        ORDDATA
ORDIM/ORDPLUGINS     ORDPLUGINS
ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA
HEMA

OCCUPANT_NAME        SCHEMA_NAME
-------------------- --------------------

EM                   SYSMAN
TEXT                 CTXSYS
ULTRASEARCH          WKSYS
ULTRASEARCH_DEMO_USE WK_TEST
R

EXPRESSION_FILTER    EXFSYS
EM_MONITORING_USER   DBSNMP
TSM                  TSMSYS
SQL_MANAGEMENT_BASE  SYS

OCCUPANT_NAME        SCHEMA_NAME
-------------------- --------------------
AUTO_TASK            SYS
JOB_SCHEDULER        SYS

31 rows selected.

2、备份

归档模式
热备 RMAN备
热备操作：

alter tablespace sysaux begin backup;
ho cp …… …… alter
tablespace system end backup;

RMAN备操作：

rman target /
backup tablespace sysaux;

演示：
热备

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL>ho cp /u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/bak/

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL>

RMAN备

[oracle@oracle11g bak]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 24
15:11:48 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights
reserved.

connected to target database: ORCL (DBID=1445346880)
RMAN> backup tablespace sysaux;

Starting backup at 24-APR-17 using channel ORA_DISK_1 channel
ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1:
specifying datafile(s) in backup set input datafile file number=00002
name=/u01/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_1:
starting piece 1 at 24-APR-17 channel ORA_DISK_1: finished piece 1 at
24-APR-17 piece
handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_24/o1_mf_nnndf_TAG20170424T151514_dhv9j2v1_.bkp
tag=TAG20170424T151514 comment=NONE channel ORA_DISK_1: backup set
complete, elapsed time: 00:00:46 Finished backup at 24-APR-17
RMAN>

3、损坏后如何恢复sysaux;
有备份—->还原 恢复
没有备份—->offline
将内容迁移到新库中去
或
使用特殊手段drop sysaux表空间，之后再新建（暂不涉及该内容）

演示：
热备恢复

SQL> startup;
ORACLE instance started.

Total System Global Area 835104768 bytes Fixed Size
2217952 bytes Variable Size 624953376 bytes Database
Buffers 205520896 bytes Redo Buffers 2412544
bytes Database mounted. ORA-01157: cannot identify/lock data file 2 -
see DBWR trace file ORA-01110: data file 2:
‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’

SQL> ho cp /u01/app/oracle/bak/sysaux01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf

SQL> alter database open;
alter database open
* ERROR at line 1: ORA-01113: file 2 needs media recovery ORA-01110: data file 2: ‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’

SQL> recover datafile 2;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

RMAN备

SQL> startup
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             624953376 bytes
Database Buffers          205520896 bytes
Redo Buffers                2412544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'

[oracle@oracle11g bak]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 24 15:26:52 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1445346880, not open)

RMAN> restore tablespace sysaux;

Starting restore at 24-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_24/o1_mf_nnndf_TAG20170424T151514_dhv9j2v1_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_04_24/o1_mf_nnndf_TAG20170424T151514_dhv9j2v1_.bkp tag=TAG20170424T151514
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 24-APR-17

RMAN> recover tablespace sysaux;

Starting recover at 24-APR-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 24-APR-17

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN> 

没有备份

SQL> startup;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             624953376 bytes
Database Buffers          205520896 bytes
Redo Buffers                2412544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'


SQL> alter database datafile 2 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> 

之后手动将数据表导出至新库即可

身为DBA，备份是一件很重要的事，一定要做！