专栏首页乐沙弥的世界SYSAUX表空间管理及恢复

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 归档日志

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle 参数文件

    主要用来记录数据库的配置文件,在数据库启动时,Oracle读取参数文件,并根据参数文件中的参数设置来配置数据库。

    Leshami
  • Oracle 冷备份

    首先在运行的库中得到数据库运行的所有的物理文件位置,然后在计划内关闭数据库(shutdown)

    Leshami
  • Oralce OMF 功能详解

    OMF,全称是Oracle_Managed Files,即Oracle文件管理,使用OMF可以简化管理员的管理工作,不用指定文件的名字、大小、路径,其名字,大...

    Leshami
  • Github|基于 Jittor 的 GAN 模型库

    今天介绍一个 Github 项目---集成了 27 种 GAN 模型的 Jittor-GAN,项目地址:

    材ccc
  • Ajax第一节

    异步: 不受当前任务的影响,两件事情同时进行,做一件事情时,不影响另一件事情的进行。

    用户3461357
  • 新冠病毒Logistic增长模型:中国+钻石公主号游轮

    Rimmer 博士是一位退休的心脏病专家,自1988年以来一直使用Mathematica。他对数学统计,金融市场,全球定位系统,信息知识和医学感兴趣;他在 Ma...

    WolframChina
  • Centos7 下部署使用 nmon2influxdb

    This application take a nmon file and upload it in a InfluxDB database. It gener...

    Devops海洋的渔夫
  • Spring MVC的参数解析器---HandlerMethodArgumentResolver

    最近我们的项目中的有一个获取客户ID的方法多次调用(数据库分离,每查一次客户ID,就要调用该方法一次),为了统一管理和减少代码的冗余,使用了Spring MVC...

    haoming1100
  • “移花接木”偷换广告:HTTPS劫匪木马每天打劫200万次网络访问

    近年来,国内各大网站逐渐升级为HTTPS加密连接,以防止网站内容被篡改、用户数据被监听。但是一向被认为“安全可靠”的HTTPS加密传输,其实也可以被木马轻易劫持...

    FB客服
  • 生死平衡,谁来制衡“不作恶”的“谷歌”?

    镁客网

扫码关注云+社区

领取腾讯云代金券