Oracle SYSAUX表空间的恢复

         SYSAUX表空间是在10g之后引入的一个新的表空间，主要用于减轻对SYSTEM表空间的压力而作为SYSTEM表空间的辅助表空间。原来存放于SYSTEM表空间的很多组件以及一些数据库元数据在10g中被移植到SYSAUX表空间。
    SYSAUX表空间在正常的数据库操作中不能被删除，或重命名，也不支持可移动表空间功能，但可以脱机。如果SYSAUX表空间失效，比如发生介质故障后有些数据库的功能会随之失效。

第一部分 通过UMAN恢复

1.
SQL>  select * from t1;

        ID
----------
         1
         2
         3

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> host cp /u01/oradata/orcl/sysaux01.dbf /u01/rmanbak/

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL>
SQL> select * from t1;

        ID
----------
         1
         2
         3
         4
         5

SQL> insert into t1 values(9);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID
----------
         1
         2
         3
         9
         4
         5

6 rows selected.

SQL> host rm /u01/oradata/orcl/sysaux01.dbf

SQL> host ls -l /u01/oradata/orcl/sysaux01.dbf
ls: /u01/oradata/orcl/sysaux01.dbf: No such file or directory

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1218556 bytes
Variable Size              62916612 bytes
Database Buffers          142606336 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/oradata/orcl/sysaux01.dbf'



SQL>
SQL> host cp /u01/rmanbak/sysaux01.dbf /u01/oradata/orcl/

SQL> recover datafile 3;
Media recovery complete.
SQL>
SQL> alter database  open;

Database altered.

SQL> select * from t1;

        ID
----------
         1
         2
         3
         9
         4
         5

6 rows selected.




第二部分：使用rman恢复

1.备份

RMAN> backup tablespace sysaux format '/u01/rmanbak/%u_%d_%T';

Starting backup at 02-APR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 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/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 02-APR-13
channel ORA_DISK_1: finished piece 1 at 02-APR-13
piece handle=/u01/rmanbak/04o632e4_ORCL_20130402 tag=TAG20130402T155028 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 02-APR-13

RMAN>

2.prepare
SQL> create table t1(id int) tablespace dt;

Table created.

SQL> insert into t1 values(1);  

1 row created.

SQL> commit;

Commit complete.
SQL> insert into t1 values(2);

1 row created.

SQL> commit;

Commit complete.


SQL> insert into t1 values(3);

1 row created.

SQL> commit；

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL>
SQL> insert into t1 values(4);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t1 values(5);

1 row created.

SQL> commit;

Commit complete.


3.故障模拟
[oracle@ora10g orcl]$ date
Tue Apr  2 15:56:12 CST 2013
[oracle@ora10g orcl]$
[oracle@ora10g orcl]$ mv sysaux01.dbf sysaux01.dbf.bak
[oracle@ora10g orcl]$ date
Tue Apr  2 15:56:44 CST 2013

Tue Apr  2 15:56:44 CST 2013
[oracle@ora10g orcl]$ exit
exit

SQL> insert into t1 values(8);  

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t1 values(9);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t1 values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>

SQL> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1218556 bytes
Variable Size              62916612 bytes
Database Buffers          142606336 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col name format a50
SQL> l
  1* select name,STATUS from v$datafile
SQL> /

NAME                                               STATUS
-------------------------------------------------- -------
/u01/oradata/orcl/system01.dbf                     SYSTEM
/u01/oradata/orcl/undotbs01.dbf                    ONLINE
/u01/oradata/orcl/sysaux01.dbf                     RECOVER
/u01/oradata/orcl/users01.dbf                      ONLINE
/u01/oradata/orcl/dt01.dbf                         ONLINE
/u01/oradata/orcl/dt02.dbf                         ONLINE
/u01/oradata/orcl/dt03.dbf                         ONLINE

7 rows selected.

restore tablespace sysaux;
restore datafile 3;

[oracle@ora10g orcl]$ ls -l /u01/rmanbak/04o632e4_ORCL_20130402
-rw-r----- 1 oracle dba 156180480 Apr  2 15:50 /u01/rmanbak/04o632e4_ORCL_20130402


RMAN> restore datafile 3;

Starting restore at 02-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 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/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbak/04o632e4_ORCL_20130402
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rmanbak/04o632e4_ORCL_20130402 tag=TAG20130402T155028
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 02-APR-13

RMAN> recover tablespace sysaux;

Starting recover at 02-APR-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 02-APR-13

SQL> select file#,name,STATUS from v$datafile;

     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/oradata/orcl/system01.dbf                     SYSTEM
         2 /u01/oradata/orcl/undotbs01.dbf                    ONLINE
         3 /u01/oradata/orcl/sysaux01.dbf                     OFFLINE
         4 /u01/oradata/orcl/users01.dbf                      ONLINE
         5 /u01/oradata/orcl/dt01.dbf                         ONLINE
         6 /u01/oradata/orcl/dt02.dbf                         ONLINE
         7 /u01/oradata/orcl/dt03.dbf                         ONLINE

7 rows selected.

SQL> alter tablespace sysaux online;

Tablespace altered.

SQL> select file#,name,STATUS from v$datafile;

     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/oradata/orcl/system01.dbf                     SYSTEM
         2 /u01/oradata/orcl/undotbs01.dbf                    ONLINE
         3 /u01/oradata/orcl/sysaux01.dbf                     ONLINE
         4 /u01/oradata/orcl/users01.dbf                      ONLINE
         5 /u01/oradata/orcl/dt01.dbf                         ONLINE
         6 /u01/oradata/orcl/dt02.dbf                         ONLINE
         7 /u01/oradata/orcl/dt03.dbf                         ONLINE

7 rows selected.


SQL> select * from t1;      

        ID
----------
         1
         2
         3
         4
         5
         8
         9
        10

8 rows selected.