情况1: 丢失后库关闭过(且有RMAN备份的情况)
查看丢失文件ID
set linesize 120
set pagesize 0
col name format a70
select ts#,file#,name from V$datafile;
select t.file#, t.name, s.name from v$datafile t,v$tablespace s where t.ts#=s.ts#;
set line 135
select log_mode from v$database;
col DBID for 9999999999
col name for a8
col force_logging for a30
set head on
select dbid,name,force_logging from v$database;
RMAN> run{
sql 'alter database datafile 5 offline';
restore datafile 5;
recover datafile 5;
sql 'alter database datafile 5 online';
alter database open;
}
sqlplus / as sysdba
alter database datafile 5 online;
col name for a40
select name,status from v$datafile;
情况2 丢失后未关库,可以直接用rman 恢复
RMAN> run{
sql 'alter database datafile 5 offline';
restore datafile 5;
recover datafile 5;
sql 'alter database datafile 5 online';
查看丢失文件ID
set linesize 120
set pagesize 0
col name format a70
select ts#,file#,name from V$datafile;
select t.file#, t.name, s.name from v$datafile t,v$tablespace s where t.ts#=s.ts#;
set line 135
select log_mode from v$database;
col DBID for 9999999999
col name for a8
col force_logging for a30
set head on
select dbid,name,force_logging from v$database;
RMAN> run{
sql 'alter database datafile 5 offline';
restore datafile 5;
recover datafile 5;
sql 'alter database datafile 5 online';
alter database open;
}
sqlplus / as sysdba
alter database datafile 5 online;
col name for a40
select name,status from v$datafile;
情况2 丢失后未关库,可以直接用rman 恢复
RMAN> run{
sql 'alter database datafile 5 offline';
restore datafile 5;
recover datafile 5;
sql 'alter database datafile 5 online';
}
情况3 若无RMAN备份,则只能删除该表空间
aler database datafile 5 offline;
alter databse open;
drop tablespace aaa including contents and datafiles;