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

 

 

 

posted @ 2010-08-13 20:36  生活不是用来挥霍的  阅读(786)  评论(0编辑  收藏  举报