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備

[[email protected] 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'

[[email protected] 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，備份是一件很重要的事，一定要做！