天天看点

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,备份是一件很重要的事,一定要做!

继续阅读