SYSAUX表空间管理及恢复
--================================
-- 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 冷备份
SPFILE错误导致数据库无法启动
Oracle 用户、对象权限、系统权限
Oracle 角色、配置文件
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 控制文件(CONTROLFILE)
Oracle 表空间与数据文件
Oracle 归档日志