--================================
-- SYSAUX 表空间管理及恢复
--================================
SYSAUX 表空间是在 10g 之后引入的一个新的表空间，主要用于减轻对 SYSTEM 表空间的压力而作为 SYSTEM 表空间的辅助表空间。
原来存放于 SYSTEM 表空间的很多组件以及一些数据库元数据在 10g 中被移植到 SYSAUX 表空间。
SYSAUX 表空间在正常的数据库操作中不能被删除，或重命名，也不支持可移动表空间功能，但可以脱机。如果 SYSAUX 表空间
失效，比如发生介质故障后有些数据库的功能会随之失效。
本文先描述一下 SYSAUX 表空间的管理特性，最后演示 SYSAUX 表空间丢失后的恢复过程
关于表空间及数据文件请参考： Oracle 表空间与数据文件
关于 Oracle 体系结构请参考： Oracle 实例和Oracle 数据库(Oracle 体系结构)
一、 SYSAUX 表空间的内容
可以从视图 V$SYSAUX_OCCUPANTS 中获得 SYSAUX 的相关信息
SQL > col occupant_name format a30
SQL > col occupant_desc format a40
SQL > col schema_name format a15
SQL > set linesize 200
SQL > select occupant_name , occupant_desc , schema_name , space_usage_kbytes / 1024
2 from v$sysaux_occupants ;
OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME SPACE_USAGE_KBYTES / 1024
-------------------- ---------------------------------------- --------------- -----------------------
LOGMNR LogMiner SYSTEM 5.9375
LOGSTDBY Logical Standby SYSTEM .875
STREAMS Oracle Streams SYS .5
XDB XDB XDB 48.5625
AO Analytical Workspace Object Table SYS 19.6875
XSOQHIST OLAP API History Tables SYS 19.6875
XSAMD OLAP Catalog OLAPSYS 15.5625
SM / AWR Server Manageability - Automatic Workloa SYS 34.6875
d Repository
---------- 部分结果省略 ---------------
二、 SYSAUX 的特性
1. 不能被删除
SQL > drop tablespace sysaux ;
drop tablespace sysaux
*
ERROR at line 1 :
ORA - 13501 : Cannot drop SYSAUX tablespace
SQL > drop tablespace sysaux including contents and datafiles ;
drop tablespace sysaux including contents and datafiles
*
ERROR at line 1 :
ORA - 13501 : Cannot drop SYSAUX tablespace
2. 不能被重命名
SQL > alter tablespace sysaux rename to sysaux_2 ;
alter tablespace sysaux rename to sysaux_2
*
ERROR at line 1 :
ORA - 13502 : Cannot rename SYSAUX tablespace
3. 不能置为只读
SQL > alter tablespace sysaux read only ;
alter tablespace sysaux read only
*
ERROR at line 1 :
ORA - 13505 : SYSAUX tablespace can not be made read only
4. 可以被脱机
SQL > alter tablespace sysaux offline ;
Tablespace altered .
SQL > alter tablespace sysaux online ;
Tablespace altered .
三、冷备模式下恢复 SYSAUX 表空间 ( 系统已经被冷备份且处于非归档模式下 )
1. 冷备以来控制文件没有被重建 , 也没有执行 resetlogs , 则可以使用备份还原，然后使用忽略一致性验证参数来恢复
2. 否则只能脱机 sysaux 数据文件，然后以表形式导出数据，再新建的数据库中 , 把导出的数据导回。
3. 全备数据库
SQL > select log_mode from v$database ; -- 查看数据的归档状态为非归档模式
LOG_MODE
------------
NOARCHIVELOG
SQL > ho ls / u01 / app / oracle / coolbak -- 查看冷备路径下备份的文件
control01 . ctl orapworcl redo2b . rdo system01 . dbf users01 . dbf
control02 . ctl redo1a . rdo redo3a . rdo tbs1_1 . dbf
example01 . dbf redo1b . rdo redo3b . rdo tbs1_2 . dbf
initorcl . ora redo2a . rdo sysaux01 . dbf undotbs01 . dbf
SQL > ho rm / u01 / app / oracle / coolbak /* -- 将冷备路径下先前的备份文件删除 */
SQL > ho cat / tmp / tmpbak2 . sql -- 查看冷备脚本
set feedback off
set heading off
set verify off
set trimspool off
set pagesize 0
set linesize 200
define dir = '/u01/app/oracle/coolbak'
define script = '/tmp/coolbak.sql'
spool & script
select 'ho cp ' || name || ' &dir' from v$controlfile
union all
select 'ho cp ' || name || ' &dir' from v$datafile
union all
select 'ho cp ' || member || ' &dir' from v$logfile
union all
select 'ho cp ' || name || ' &dir' from v$tempfile
/
create pfile = '&dir/initorcl.ora' from spfile ;
ho cp / u01 / app / oracle / 10g / dbs / orapworcl & dir
spool off
shutdown immediate
start & script
ho rm & script
startup
SQL > start / tmp / tmpbak2 . sql ; -- 执行冷备脚本，完毕后将自动完成冷备份并启动实例
SQL > col file_name format a60
SQL > select tablespace_name , file_name from dba_data_files ;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------
UNDOTBS1 / u01 / app / oracle / oradata / orcl / undotbs01 . dbf
SYSTEM / u01 / app / oracle / oradata / orcl / system01 . dbf
SYSAUX / u01 / app / oracle / oradata / orcl / sysaux01 . dbf
USERS / u01 / app / oracle / oradata / orcl / users01 . dbf
EXAMPLE / u01 / app / oracle / oradata / orcl / example01 . dbf
TBS1 / u01 / app / oracle / oradata / orcl / tbs1_1 . dbf
TBS1 / u01 / app / oracle / oradata / orcl / tbs1_2 . dbf
SQL > ho rm / u01 / app / oracle / oradata / orcl / sysaux01 . dbf -- 删除 sysaux 表空间的数据文件
SQL > startup -- 启动时收到了关于数据文件 sysaux01 的错误提示
ORACLE instance started .
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 79693588 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
Database mounted .
ORA - 01157 : cannot identify / lock data file 3 - see DBWR trace file
ORA - 01110 : data file 3 : '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
-- 查看告警日志信息
SQL > ho tail - n 10 $ORACLE_BASE / admin / orcl / bdump / alert_orcl . log
Mon Aug 9 13 : 14 : 22 2010
ALTER DATABASE OPEN
Mon Aug 9 13 : 14 : 22 2010
Errors in file / u01 / app / oracle / admin / orcl / bdump / orcl_dbw0_4056 . trc :
ORA - 01157 : cannot identify / lock data file 3 - see DBWR trace file
ORA - 01110 : data file 3 : '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
ORA - 27037 : unable to obtain file status
Linux Error : 2 : No such file or directory
Additional information : 3
ORA - 1157 signalled during : ALTER DATABASE OPEN ...
-- 根据告警日志信息查看跟踪文件 orcl_dbw0_4056.trc
SQL > ho cat / u01 / app / oracle / admin / orcl / bdump / orcl_dbw0_4056 . trc | more
/ u01 / app / oracle / admin / orcl / bdump / orcl_dbw0_4056 . trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning , OLAP and Data Mining options
ORACLE_HOME = / u01 / app / oracle / 10g
System name : Linux
Node name : robinson . com
Release : 2.6.18 - 164.el5xen
Version : #1 SMP Tue Aug 18 16 : 06 : 30 EDT 2009
Machine : i686
Instance name : orcl
Redo thread mounted by this instance : 1
Oracle process number : 5
Unix process pid : 4056 , image : oracle@robinson . com ( DBW0 )
*** SERVICE NAME :() 2010 - 08 - 09 13 : 14 : 22.046
*** SESSION ID :( 167.1 ) 2010 - 08 - 09 13 : 14 : 22.046
ORA - 01157 : cannot identify / lock data file 3 - see DBWR trace file
ORA - 01110 : data file 3 : '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
ORA - 27037 : unable to obtain file status
Linux Error : 2 : No such file or directory
Additional information : 3
SQL > ho ls / u01 / app / oracle / oradata / orcl / sysaux01 . dbf --sysaux01.dbf 在系统中不存在，即丢失
ls : / u01 / app / oracle / oradata / orcl / sysaux01 . dbf : No such file or directory
SQL > ho ls - l / u01 / app / oracle / coolbak / sysau *
- rw ------- 1 oracle oinstall 304095232 Aug 9 13:05 /u01/app/oracle/coolbak/sysaux01.dbf
-- 从备份中还原 sysaux 表空间的数据文件
SQL > ho cp / u01 / app / oracle / coolbak / sysaux01 . dbf / u01 / app / oracle / oradata / orcl /
SQL > recover database ; -- 进行介质恢复
Media recovery complete .
SQL > alter database open ; -- 将数据库切换到 open 状态
SQL > select * from dual ; -- 数据库已正常使用
X
四、使用 RMAN 备份及还原 sysaux 表空间
-- 在会话 session1 中查看归档信息
SQL > archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4 -- 当前 log sequence 为 4
-- 打开另外一个会话 session2 并使用 rman 备份 sysaux 表空间
RMAN > backup tablespace sysaux ;
Starting backup at 13 - AUG - 10
allocated channel : ORA_DISK_1
channel ORA_DISK_1 : sid = 147 devtype = DISK
channel ORA_DISK_1 : starting full datafile backupset
channel ORA_DISK_1 : specifying datafile ( s ) in backupset
input datafile fno = 00003 name =/ u01 / app / oracle / oradata / orcl / sysaux01 . dbf
channel ORA_DISK_1 : starting piece 1 at 13 - AUG - 10
channel ORA_DISK_1 : finished piece 1 at 13 - AUG - 10
piece handle =/ u01 / app / oracle / flash_recovery_area / ORCL / backupset
/ 2010_08_13 / o1_mf_nnndf_TAG20100813T102959_669ck93v_ . bkp tag = TAG20100813T102959 comment = NONE
channel ORA_DISK_1 : backup set complete , elapsed time : 00 : 01 : 11
Finished backup at 13 - AUG - 10
-- 在 session1 中删除 sysaux01.dbf
SQL > ho rm $ORACLE_BASE / oradata / orcl / sysaux01 . dbf ;
-- 对 tb_test 表插入一些记录并提交
SQL > select * from tb_test ;
no rows selected
SQL > insert into tb_test select * from all_objects ;
49835 rows created .
SQL > commit ;
Commit complete .
-- 关闭实例并重新启动后出现错误提示
SQL > startup
ORACLE instance started .
Total System Global Area 469762048 bytes
Fixed Size 1220048 bytes
Variable Size 109052464 bytes
Database Buffers 356515840 bytes
Redo Buffers 2973696 bytes
Database mounted .
ORA - 01157 : cannot identify / lock data file 3 - see DBWR trace file
ORA - 01110 : data file 3 : '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
-- 在 session2 中使用 rman 来进行恢复 sysaux 表空间，需要使用 rman 重新连接数据库
RMAN > restore tablespace sysaux ;
Starting restore at 13 - AUG - 10
using target database control file instead of recovery catalog
allocated channel : ORA_DISK_1
channel ORA_DISK_1 : sid = 155 devtype = DISK
channel ORA_DISK_1 : starting datafile backupset restore
channel ORA_DISK_1 : specifying datafile ( s ) to restore from backup set
restoring datafile 00003 to / u01 / app / oracle / oradata / orcl / sysaux01 . dbf
channel ORA_DISK_1 : reading from backup piece / u01 / app / oracle / flash_recovery_area / ORCL /
backupset / 2010_08_13 / o1_mf_nnndf_TAG20100813T102959_669ck93v_ . bkp
channel ORA_DISK_1 : restored backup piece 1
piece handle =/ u01 / app / oracle / flash_recovery_area / ORCL / backupset / 2010_08_13 /
o1_mf_nnndf_TAG20100813T102959_669ck93v_ . bkp tag = TAG20100813T102959
channel ORA_DISK_1 : restore complete , elapsed time : 00 : 00 : 25
Finished restore at 13 - AUG - 10
-- 在会话 session1 中将 database open , 提示需要执行介质恢复
SQL > alter database open ;
alter database open
*
ERROR at line 1 :
ORA - 01113 : file 3 needs media recovery
ORA - 01110 : data file 3 : '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
-- 执行介质恢复并将数据库 open
SQL > recover datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf' ;
Media recovery complete .
SQL > alter database open ;
Database altered .
-- 已提交的事务保持一致
SQL > select count ( 1 ) from tb_test ;
COUNT ( 1 )
----------
49835
五、热备模式下还原 sysaux 表空间
1. 未手动实现归档、且未发生日志切换时的处理
-- 将 sysaux 表空间置于热备模式
SQL > alter tablespace sysaux begin backup ;
Tablespace altered .
-- 执行 DML 操作，从 tb_test 删除记录，热备模式不影响正常操作
SQL > delete from tb_test ;
2 rows deleted .
SQL > commit ;
Commit complete .
-- 对 sysaux 表空间进行热备
SQL > ho cp $ORACLE_BASE / oradata / orcl / sysaux01 . dbf / u01 / app / oracle / hotbak
-- 热备后再次执行 DML 操作，即查询新的记录到 tb_test
SQL > insert into tb_test select * from dba_objects where rownum < 3 ;
2 rows created .
SQL > commit ;
Commit complete .
-- 关闭 sysaux 表空间的备份模式
SQL > alter tablespace sysaux end backup ;
Tablespace altered .
-- 再次执行 DML 插入两条记录到 tb_test
SQL > insert into tb_test select * from dba_objects where rownum < 3 ;
2 rows created .
SQL > commit ;
Commit complete .
-- 此时删除 sysaux01.dbf 文件
SQL > ho rm $ORACLE_BASE / oradata / orcl / sysaux01 . dbf
-- 关闭并重新启动实例
SQL > shutdown immediate ;
Database closed .
Database dismounted .
ORACLE instance shut down .
SQL > startup
ORACLE instance started .
Total System Global Area 469762048 bytes
Fixed Size 1220048 bytes
Variable Size 117441072 bytes
Database Buffers 348127232 bytes
Redo Buffers 2973696 bytes
Database mounted .
ORA - 01157 : cannot identify / lock data file 3 - see DBWR trace file
ORA - 01110 : data file 3 : '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
-- 还原 sysaux01.dbf
SQL > ho cp $ORACLE_BASE / hotbak / sysaux01 . dbf $ORACLE_BASE / oradata / orcl /
-- 恢复 sysaux01.dbf 并将数据库置于 open 状态
SQL > recover datafile 3 ;
Media recovery complete .
SQL > alter database open ;
Database altered .
-- 已提交的数据保持了一致性
SQL > select count ( 1 ) from tb_test ;
COUNT ( 1 )
----------
4
--SYSAUX 表空间已为可用状态
SQL > select file_name , tablespace_name , status from dba_data_files ;
FILE_NAME TABLESPACE_NAME STATUS
-------------------------------------------------- ------------------------------ ---------
/ u01 / app / oracle / oradata / orcl / users01 . dbf USERS AVAILABLE
/ u01 / app / oracle / oradata / orcl / sysaux01 . dbf SYSAUX AVAILABLE
/ u01 / app / oracle / oradata / orcl / undotbs01 . dbf UNDOTBS1 AVAILABLE
/ u01 / app / oracle / oradata / orcl / system01 . dbf SYSTEM AVAILABLE
/ u01 / app / oracle / oradata / orcl / example01 . dbf EXAMPLE AVAILABLE
2. 手动实现日志归档后的处理
-- 查看是否处于归档模式及当前归档的详细信息
SQL > archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
-- 以下处理步骤与前面类似，省略描述
SQL > alter tablespace sysaux begin backup ;
Tablespace altered .
SQL > ho cp $ORACLE_BASE / oradata / orcl / sysaux01 . dbf $ORACLE_BASE / hotbak ;
SQL > alter tablespace sysaux end backup ;
Tablespace altered .
SQL > insert into tb_test select * from dba_objects ;
50318 rows created .
SQL > commit ;
Commit complete .
SQL > ho rm $ORACLE_BASE / oradata / orcl / sysaux01 . dbf ;
SQL > delete from tb_test ;
50322 rows deleted .
SQL > commit ;
Commit complete .
-- 备份 sysaux01.dbf 以后再执行了一些 DML 操作后，对日志进行归档
SQL > alter system archive log current ;
System altered .
-- 关闭实例并重新启动实例后未错误任何错误提示
SQL > shutdown immediate ;
Database closed .
Database dismounted .
ORACLE instance shut down .
SQL > startup
ORACLE instance started .
Total System Global Area 469762048 bytes
Fixed Size 1220048 bytes
Variable Size 117441072 bytes
Database Buffers 348127232 bytes
Redo Buffers 2973696 bytes
Database mounted .
Database opened .
-- 查看告警日志提示 replication_dependency_tracking 功能被关闭及 XDB$SCHEMA 不可访问
SQL > ho tail - n 30 $ORACLE_BASE / admin / orcl / bdump / alert_orcl . log
Database Characterset is AL32UTF8
replication_dependency_tracking turned off ( no async multimaster replication found )
Starting background process QMNC
Fri Aug 13 12 : 56 : 24 2010
ARC2 : Archival started
ARC1 : STARTING ARCH PROCESSES COMPLETE
ARC1 : Becoming the heartbeat ARCH
XDB UNINITIALIZED : XDB$SCHEMA not accessible
QMNC started with pid = 19 , OS id = 4308
Fri Aug 13 12 : 56 : 25 2010
db_recovery_file_dest_size of 2048 MB is 17.52 % used . This is a
user - specified limit on the amount of space that will be used by this
database for recovery - related files , and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup .
ORA - 376 encountered when generating server alert SMG - 3600
Fri Aug 13 12 : 56 : 26 2010
Completed : ALTER DATABASE OPEN
--dba_tablespaces 视图中依然显示的是 online
SQL > select tablespace_name , status from dba_tablespaces ;
TABLESPACE_NAME STATUS
--------------- ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
--v$datafile 视图中显示为 recover 状态
SQL > select name , file# , status from v$datafile ;
NAME FILE# STATUS
-------------------------------------------------- ---------- -------
/ u01 / app / oracle / oradata / orcl / system01 . dbf 1 SYSTEM
/ u01 / app / oracle / oradata / orcl / undotbs01 . dbf 2 ONLINE
/ u01 / app / oracle / oradata / orcl / sysaux01 . dbf 3 RECOVER
/ u01 / app / oracle / oradata / orcl / users01 . dbf 4 ONLINE
/ u01 / app / oracle / oradata / orcl / example01 . dbf 5 ONLINE
-- 还原 sysaux01.dbf 并将数据库启动到 mount 状态
SQL > ho cp $ORACLE_BASE / hotbak / sysaux01 . dbf $ORACLE_BASE / oradata / orcl /
SQL > shutdown immediate ;
Database closed .
Database dismounted .
ORACLE instance shut down .
SQL > startup mount ;
ORACLE instance started .
Total System Global Area 469762048 bytes
Fixed Size 1220048 bytes
Variable Size 117441072 bytes
Database Buffers 348127232 bytes
Redo Buffers 2973696 bytes
Database mounted .
-- 还原 sysaux 表空间
SQL > recover tablespace sysaux ;
Media recovery complete .
SQL > alter database open ;
Database altered .
--sysaux01.dbf 变为 offline 状态
SQL > col name format a50
SQL > select name , file# , status from v$datafile ;
NAME FILE# STATUS
-------------------------------------------------- ---------- -------
/ u01 / app / oracle / oradata / orcl / system01 . dbf 1 SYSTEM
/ u01 / app / oracle / oradata / orcl / undotbs01 . dbf 2 ONLINE
/ u01 / app / oracle / oradata / orcl / sysaux01 . dbf 3 OFFLINE
/ u01 / app / oracle / oradata / orcl / users01 . dbf 4 ONLINE
/ u01 / app / oracle / oradata / orcl / example01 . dbf 5 ONLINE
-- 将 sysaux 表空间联机
SQL > alter tablespace sysaux online ;
Tablespace altered .
六、总结
1. 在系统启动时出现的相关提示建议先查看告警日志及跟踪日志以便进一步确认问题所在。
2. 对于 SYSAUX 表空间的丢失，先还原，再执行介质恢复，有可能需要将其联机。前提是需要先备份。
3. 在备份期间或 SYSAUX 表空间丢失以后，不影响事务处理，且能恢复已提交的事务，当且仅当归档日志或联机日志存在时。
4. 若 SYSAUX 表空间丢失后 , 表空间迁移 , 基于 SCHEMA 导入导出 , OEM 等功能不可使用 , 但不影响未涉及到 SYSAUX 表空间功能的正常使用。
5. 若 SYSAUX 表空间丢失后 , 发生了日志切换，或手动日志归档，或系统自动归档，下次重新启动数据库将不会收到错误提示。
可以参见第五点、第 2 小点中的：手动实现日志归档后的处理
在冷备模式下，当处于归档模式的情况下实现日志切换，手动或自动归档也发生类似的情况。这个未给出演示。
6. 对于上述小点中丢失 SYSAUX 可以查看 dba_data_files , dba_tablespaces , v$datafile 中数据文件的状态信息
其中 dba_data_files , dba_tablespaces 属于数据字典，可能与实际情况有些偏差
v$datafile 为实时的数据信息，可以据此对数据库实现相关操作
7. 对于不可恢复的情况 , 可以将隐藏参数 _allow_resetlogs_corruption_ 置为 true, 并使用 alter database open resetlogs 打开。
8. 使用 alter database open resetlogs 打开数据库有应当关闭 _allow_resetlogs_corruption_ 参数。
9. 对于使用 alter database open resetlogs 打开的数据库应当立即进行全备数据库。
10. 如果在未备份的情况下丢失了 SYSAUX 表空间，则可以将其脱机，然后将数据导出，并导入到新的数据库。
七、更多参考
Oracle 联机重做日志文件(ONLINE LOG FILE)
