【undo資料表空間的丟失-恢複-1】，undo表丟失-1

【undo資料表空間的丟失-恢複-1】，undo表丟失-1
使用rman進行恢複--undo丟失
restore 把檔案還原回去；
recover 利用記錄檔重做；
關鍵性的檔案丟失和非關鍵性的檔案丟失(system/undo之外的丟失)
1>
刪除undo檔案：
[oracle@oracle ~]$ rm /u01/oracle/oradata/jadl10g/undotbs01.dbf
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 14:41:40 2014
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> conn scott/tiger ---此時串連沒有報錯，是由於undo已經緩衝了，清除緩衝；
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter system flush shared_pool;
System altered.

SQL> alter system flush buffer_cache;
System altered.

SQL> alter system flush global context;
System altered.

SQL> conn scott/tiger ----串連scott使用者出錯，提示undo丟失
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/oracle/oradata/jadl10g/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Nov 6 14:45:54 2014
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: JADL10G (DBID=2011530396)

RMAN> shutdown abort
using target database control file instead of recovery catalog
Oracle instance shut down

RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area     599785472 bytes
Fixed Size                     2098112 bytes
Variable Size                163580992 bytes
Database Buffers             427819008 bytes
Redo Buffers                   6287360 bytes

RMAN> restore datafile 2;
Starting restore at 06-NOV-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/oracle/oradata/jadl10g/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T135107_b5p32dhf_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T135107_b5p32dhf_.bkp tag=TAG20141106T135107
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 06-NOV-14

RMAN> recover database;
Starting recover at 06-NOV-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-NOV-14

RMAN> alter database open;
database opened

*****採用此種恢複方法之後不需要重新備份資料庫，該方法可用於undo丟失或者系統資料表空間丟失。****

2>建立新的undo資料表空間，更改為建立的資料表空間，前提是能正確執行建立undo資料表空間的命令；
[oracle@oracle ~]$ rm /u01/oracle/oradata/jadl10g/undotbs01.dbf
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 14:52:24 2014
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/jadl10g/system01.dbf
/u01/oracle/oradata/jadl10g/undotbs01.dbf
/u01/oracle/oradata/jadl10g/sysaux01.dbf
/u01/oracle/oradata/jadl10g/users01.dbf
/u01/oracle/oradata/jadl10g/example01.dbf

SQL> ho ls /u01/oracle/oradata/jadl10g/undotbs01.dbf
ls: cannot access /u01/oracle/oradata/jadl10g/undotbs01.dbf: No such file or directory

SQL> create undo tablespace undotbs2 datafile '/u01/oracle/oradata/jadl10g/undotbs02.dbf' size 10m autoextend on;
create undo tablespace undotbs2 datafile '/u01/oracle/oradata/jadl10g/undotbs02.dbf' size 10m autoextend on
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/oracle/oradata/jadl10g/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

create undo tablespace 命令不能正常執行的話，只能採用上面的方法恢複；如果能建立成功，可以不關閉資料庫；

alter system set undo_tablespace=undotbs2;

執行以下命令會錯：
drop tablespace undotbs01;
alter tablespace undotbs01 offline;

可以執行以下命令離線不在使用的檔案：
alter database datafile 2 offline;
也可以將原來的檔案還原回來：
進入rman：
restore datafile 2;
recover datafile 2;
進入sqlplus：
alter database datafile 2 online;
alter system set undo_tablespace=undotbs1;

****此時需要重新備份資料庫；需要注意正在使用的undo是不能離線的額。
****不清空緩衝的話是可以建立的。還有就是建立兩個undo，以防止丟失一個，可以切換到另外一個上面。
****system資料表空間是必須關閉資料庫的噢噢