【场景一、不完全恢复】
阶段1:模拟基础数据
1.1 新建表空间和用户
create tablespace TBS_TEST1 datafile '+DATA/orcl/datafile/test1.dbf' size 100M;
create user test1 identified by test1 default tablespace TBS_TEST1 quota unlimited on TBS_TEST1;
grant connect,resource to test1;
1.2 建表并插入2条数据
create table test1.tb1 (id number primary key,name varchar2(20),savetime date);
insert into test1.tb1 values(1,'step in 1.2',sysdate);
insert into test1.tb1 values(2,'step in 1.2',sysdate);
commit;
阶段2:0级差异备份
2.1 执行脚本 rman_backup_full.sh
2.2 新建表并插入数据
create table test1.tb2 (id number primary key,name varchar2(20),savetime date);
insert into test1.tb2 values(3,'step in 2.2',sysdate);
insert into test1.tb2 values(4,'step in 2.2',sysdate);
commit;
阶段3:1级差异备份
3.1 执行脚本 rman_backup_incr.sh
3.2 新建表并插入数据
create table test1.tb3 (id number primary key,name varchar2(20),savetime date);
insert into test1.tb3 values(5,'step in 3.2',sysdate);
insert into test1.tb3 values(6,'step in 3.2',sysdate);
commit;
阶段4:1级差异备份
4.1 执行脚本 rman_backup_incr.sh
4.2 新建表并插入数据
create table test1.tb4 (id number primary key,name varchar2(20),savetime date);
insert into test1.tb4 values(7,'step in 4.2',sysdate);
insert into test1.tb4 values(8,'step in 4.2',sysdate);
commit;
阶段5:删除表四张表
select current_scn from v$database; --scn 1450051
drop table test1.tb1;
drop table test1.tb2;
drop table test1.tb3;
drop table test1.tb4;
阶段6:通过logmnr工具
找出drop table test1.tb1的scn ,
( 详细参考链接https://blog.csdn.net/weixin_47745154/article/details/119805682 )
6.1假设scn为 1450051;
阶段7:恢复4张表数据
oracle#rman target / nocatalog
RMAN> shutdown immediate
RMAN> startup mount
RMAN> restore database until scn 1450051;
RMAN> recover database until scn 1450051;
RMAN> alter database open resetlogs;
阶段8:验证不完全恢复结果
select * from dba_tables where owner='TEST1';
select * from test1.tb1;
select * from test1.tb2;
select * from test1.tb3;
select * from test1.tb4;
附:脚本
- rman_backup_full.sh
#!/bin/sh #rman full backup script source ~/.bash_profile backuptime=`date +"%Y%m%d%H%M%S"` basepath=/home/oracle/maintain logfile=$basepath/log/rman_full_$backuptime.log cd $ORACLE_HOME/bin rman target / nocatalog log=$logfile << EOF run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; crosscheck backup; sql 'alter system archive log current'; backup INCREMENTAL LEVEL 0 FILESPERSET 2 database format '/u01/app/oracle/rmanbak/rman_full_%d_%s%p%u.bak'; sql 'alter system archive log current'; backup FILESPERSET 10 archivelog all not backed up format '/u01/app/oracle/rmanbak/archivelog/rman_full_arch_%d_%s%p%u.arc'; delete expired archivelog until time 'sysdate-1'; crosscheck archivelog all; delete noprompt expired backup; delete noprompt obsolete; release channel ch1; release channel ch2; } EOF echo 'full backup success!'; |
- rman_backup_incr.sh
#!/bin/sh #rman incr backup script source ~/.bash_profile backuptime=`date +"%Y%m%d%H%M%S"` basepath=/home/oracle/maintain logfile=$basepath/log/rman_incr_$backuptime.log cd $ORACLE_HOME/bin rman target / nocatalog log=$logfile << EOF run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; crosscheck backup; sql 'alter system archive log current'; backup INCREMENTAL LEVEL 1 FILESPERSET 2 database format '/u01/app/oracle/rmanbak/rman_incr_%d_%s%p%u.bak'; sql 'alter system archive log current'; backup FILESPERSET 10 archivelog all not backed up format '/u01/app/oracle/rmanbak/archivelog/rman_incr_arch_%d_%s%p%u.arc'; delete expired archivelog until time 'sysdate-1'; crosscheck archivelog all; delete noprompt expired backup; delete noprompt obsolete; release channel ch1; release channel ch2; } EOF echo 'incr backup success!'; |