记一次rman基于scn/时间的不完全恢复

故障模拟:
删除了用户以及表空间数据文件对象

drop user DALE cascade;
DROP TABLESPACE DALE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE DALE_TEMP INCLUDING CONTENTS AND DATAFILES;

1、环境准备

sqlplus / as sysdba
create temporary tablespace DALE_TEMP tempfile
'/oradata/cndb/DALE_TEMP.dbf'
size 20480m autoextend off
extent management local; 
create tablespace DALE datafile
'/oradata/cndb/DALE_01.dbf' size 5M
reuse autoextend ON NEXT 5m maxsize 32480M logging online permanent                     
blocksize 8192
extent management local default nocompress
segment space management auto;
create user DALE identified by "dale"       
default tablespace DALE
temporary tablespace DALE_TEMP; 
grant dba to DALE;  

create table DALE.dale01 as select * from dba_objects;
commit;
select count(*) from   DALE.dale01;
SELECT NAME FROM V$DATAFILE;
alter system checkpoint;
alter system switch logfile;

2、备份数据库以及控制文件

rman target / log=/oraback/rmanbak/0_rmanbak.log <<EOF 
run{
delete noprompt backup;
allocate channel c1 type disk maxpiecesize 1000M;
allocate channel c2 type disk maxpiecesize 1000M;
backup incremental level = 0 filesperset = 32 format '/oraback/rmanbak/rman_lev0_%d_%T_%U.bak' skip inaccessible  database include current controlfile tag '0_rmanbak_cndb' ;
sql "alter system archive log current";
backup filesperset = 32 format '/oraback/rmanbak/arch_%d_%T_%U.bak' skip inaccessible archivelog all tag 'arch_cndb';
release channel c1;
release channel c2;
}
EOF

3、删除用户(DALE)以及数据文件

sqlplus / as sysdba
select current_scn from v$database;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
drop user DALE cascade;
DROP TABLESPACE DALE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE DALE_TEMP INCLUDING CONTENTS AND DATAFILES;
select count(*) from DALE.dale01;
exit

4、恢复过程

rman target /
run{
shutdown immediate;
startup nomount;
restore controlfile from autobackup;
}

--这个时间time/scn是备份之后的
run{
alter database mount;
set until scn=1150693;
restore database;
recover database;
alter database open resetlogs;
}
/*or基于时间     
run {
alter database mount;
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time = '2024-03-29 23:08:31';                           
restore database;
recover database;
alter database open resetlogs;
release channel c1;
release channel c2;
}
*/

5、验证

exit
sqlplus / as sysdba
select count(*) from DALE.dale01;
SELECT NAME FROM V$DATAFILE;
exit

可以看到数据和数据文件都恢复了