RMAN1：不完全恢复

【场景一、不完全恢复】

阶段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;

附：脚本

1. 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!';

1. 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!';