Oracle SYSAUX tablespace recovery

Oracle SYSAUX tablespace recovery SYSAUX tablespace is a new tablespace introduced after 10 Gb. It is mainly used to reduce the pressure on the SYSTEM tablespace and serve as the auxiliary tablespace of the system tablespace. Many components originally stored in the SYSTEM tablespace and some database metadata are transplanted to the SYSAUX tablespace in 10 Gb. The SYSAUX tablespace cannot be deleted or renamed in normal database operations. It does not support the function of movable tablespace, but can be taken offline. If the SYSAUX tablespace is invalid, for example, some database functions will become invalid after a media fault occurs. The first part restores 1. SQL> select * from t1 through UMAN; 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> 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.dbfls:/u01/oradata/orcl/sysaux01.dbf: no such file or directory SQL> shutdown immediate; Database closed. database dismounted. ORACLE instance shut down. SQL> startupORACLE instance started. total System Global Area 209715200 bytesFixed Size 1218556 bytesVariable Size 62916612 bytesDatabase Buffers 142606336 bytesRedo Buffers 2973696 bytesDatabase mounted. ORA-01157: cannot identify/lock data file 3-see DBWR trace fileORA-01110: data file 3: '/u01/oradata/orcl/sysaux01.dbf' SQL> host cp/u01/rmanbak/sysaux01.dbf/u01/oradata/orcl/SQL> recover datafile 3; Media recovery complete. SQL> alter database open; Database altered. SQL> select * from t1; ID ---------- 1 2 3 9 4 5 6 rows selected. part 2: Restore with rman 1. backup RMAN> backup tablespace sysaux format '/u01/rmanbak/% u _ % d _ % T'; Starting backup at 02-APR-13allocated channel: ORA_DISK_1channel ORA_DISK_1: sid = 154 devtype = DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile (s) in backupsetinput datafile fno = 00003 name =/u01/oradata/orcl/ORA_DISK_1: starting piece 1 at ORA_DISK_1: finished piece 1 at 02-APR-13piece handle =/u01/rmanbak/tags tag = TAG20130402T155028 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 00: 26 Finished backup at 02-APR-13 RMAN> 2. prepareSQL> 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> 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. fault Simulation [oracle @ ora10g orcl] $ dateTue Apr 2 15:56:12 CST 2013 [oracle @ ora10g orcl] $ [oracle @ ora10g orcl] $ mv sysaux01.dbf sysaux01.dbf. bak [oracle @ ora10g orcl] $ dateTue Apr 2 15:56:44 CST 2013 Tue Apr 2 15:56:44 CST 2013 [oracle @ ora10g orcl] $ exitexit 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> startupORACLE instance started. total System Global Area 209715200 bytesFixed Size 1218556 bytesVariable Size 62916612 bytesDatabase Buffers 142606336 bytesRedo Buffers 2973696 bytesDatabase mounted. database opened. SQL> col name format a50SQL> l 1 * select name, STATUS from v $ datafileSQL>/name status certificate -------/u01/oradata/orcl/system01.dbf SYSTEM/u01/oradata/orcl/offline ONLINE/u01/oradata/orcl/sysaux01.dbf RECOVER/u01 /oradata/orcl/offline 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/Jun ----- 1 oracle dba 156180480 Apr 2/u01/rmanbak/04o632e4_ORCL_20130402 RMAN> restore datafile 3; starting restore at least target database control file instead of recovery existing channel: ORA_DISK_1channel ORA_DISK_1: sid = 143 devtype = DISK channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile (s) to restore from backup setrestoring datafile 00003 to/u01/oradata/orcl/sysaux01.dbfchannel ORA_DISK_1: reading from backup piece/u01/rmanbak/logs ORA_DISK_1: restored backup piece 1 piece handle =/u01/rmanbak/shard tag = maid 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-13using channel ORA_DISK_1 starting media recoverymedia recovery complete, elapsed time: 00:00:03 Finished recover at 02-APR-13 SQL> select file #, name, STATUS from v $ datafile; FILE # name status ---------- detail ------- 1/u01/oradata/orcl/system01.dbf SYSTEM 2/u01/oradata/orcl/offline online 3/u01/oradata/orcl/sysaux01.dbf OFFLINE 4/u01 /oradata/orcl/offline 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 ---------- certificate ------- 1/u01/oradata/orcl/system01.dbf SYSTEM 2/u01/oradata/orcl/offline ONLINE 3/u01/oradata/orcl/sysaux01.dbf ONLINE 4/u01 /oradata/orcl/offline 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.