rman全量+增量恢复报错 ORA-01152

举报
小麦苗DB宝 发表于 2022/02/16 14:52:21 2022/02/16
【摘要】 现象rman在做完增量恢复操作后，执行“alter database open resetlogs;”，报错：ORA-01152: file 1 was not restored from a sufficiently old backup，如下：RMAN> alter database open resetlogs;using target database control file i...

现象

rman在做完增量恢复操作后，执行“alter database open resetlogs;”，报错：ORA-01152: file 1 was not restored from a sufficiently old backup，如下：

RMAN> alter database open resetlogs;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/16/2022 10:46:26
ORA-01152: file 1 was not restored from a sufficiently old backup 
ORA-01110: data file 1: '/u01/app/oracle/oradata/LHR11G/system01.dbf'

恢复场景：首先rman全备，然后过了2天，删除这2天的归档，然后做增量备份，然后在异机直接做recover操作的时候报错了。

原因

由于controlfile里所记录的scn与datafile里的scn不一致。

模拟

IP 版本 数据库名 归档 角色
172.17.0.2 11.2.0.4 LHR11G Y 源端
172.17.0.3 11.2.0.4 LHR11G Y 目标端

环境准备：

-- 源库
docker run -itd --name lhroratest -h lhroratest \
   --privileged=true   lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init
   
-- 目标库
docker run -itd --name lhroratest2 -h lhroratest2 \
   --privileged=true   lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init

源库做全备

alter database enable block change tracking using file '/home/oracle/lhr11g.bct';

rm -rf /home/oracle/bk/*

CROSSCHECK backupset ;
delete noprompt backupset ;
delete noprompt archivelog all;

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;


run
{
 backup INCREMENTAL LEVEL 0 as compressed backupset database  format '/home/oracle/bk/%d%U.full';
 sql 'alter system archive log current';
 backup as compressed backupset archivelog from time 'sysdate-10/24/60'  format '/home/oracle/bk/%d%U.arc';
 backup current controlfile  format '/home/oracle/bk/%d%U.ctl';
 backup spfile format '/home/oracle/bk/spfile_%d_%U.ora';
}

scp /home/oracle/bk/* oracle@172.17.0.3:/home/oracle/bk/

注意这里的归档日志的备份“sysdate-10/24/60”，表示只备份最近10分钟的归档，因为我这个库比较小，所以10分钟可以备份完成。若库比较大，备份需要2小时，那么建议这里的时间调大一点，即需要备份从开始全备到全备结束之间的所有归档。

目标库全量恢复

startup force mount  restrict;
drop database;


rm -rf  /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/*
mkdir -p /u01/app/oracle/flash_recovery_area/LHR11G/

rman target /
startup force nomount

restore spfile from '/home/oracle/bk/spfile_LHR11G_2m0lvkmi_1_1.ora';

startup force nomount
restore controlfile from '/home/oracle/bk/LHR11G3b0lvlj3_1_1.ctl';

alter database mount;

restore database;

recover database;

全量恢复的时候，需要注意，删除之前库已经产生的归档文件。

在做完recover database后需要注意归档号是多少。

源库做增量备份

这里为了模拟实验，多次切换归档文件后，再删除这些产生的归档文件。

-- 删除归档
alter system switch logfile;

create table t1 as select * from dual;

delete noprompt archivelog all;


-- 增量
rm -rf /home/oracle/bk/*
rman target /
run
{
 backup INCREMENTAL LEVEL 1 as compressed backupset CUMULATIVE database  format '/home/oracle/bk/%d%U_inc.full';
 sql 'alter system archive log current';
 backup as compressed backupset archivelog from time 'sysdate-2/24/60' format '/home/oracle/bk/%d%U_inc.arc' section size 100G;
 backup current controlfile  format '/home/oracle/bk/%d%U_inc.ctl';
}

scp /home/oracle/bk/* oracle@172.17.0.3:/home/oracle/bk/

注意这里的归档日志的备份“sysdate-2/24/60”，表示只备份最近2分钟的归档，因为我这个库比较小，所以2分钟可以增量备份完成。若库比较大，增量需要10分钟，那么建议这里的时间调大一点，即需要备份从开始增量备份到增量备份结束之间的所有归档。

目标库做增量恢复

startup force nomount
restore controlfile from '/home/oracle/bk/LHR11G340lvl0v_1_1_inc.ctl';

alter database mount;

catalog start with '/home/oracle/bk/';
recover database;



alter database open resetlogs;


select open_mode from v$database;

解决办法

若增量恢复的时候执行“alter database open resetlogs;”，报错：ORA-01152: file 1 was not restored from a sufficiently old backup，那么可以有以下几种办法：

方法1、增量恢复的时候，先还原最新的控制文件，然后再做recover操作。

方法2、重建控制文件。

直接在目标库操作，如下：

alter database backup controlfile to trace as '/tmp/control_rebuild.trc';

STARTUP FORCE NOMOUNT;


CREATE CONTROLFILE REUSE DATABASE "LHR11G" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/LHR11G/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/LHR11G/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/LHR11G/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/LHR11G/system01.dbf',
  '/u01/app/oracle/oradata/LHR11G/sysaux01.dbf',
  '/u01/app/oracle/oradata/LHR11G/undotbs01.dbf',
  '/u01/app/oracle/oradata/LHR11G/users01.dbf',
  '/u01/app/oracle/oradata/LHR11G/example01.dbf'
CHARACTER SET AL32UTF8
;



catalog start with '/home/oracle/bk/';
recover database;

ALTER DATABASE OPEN RESETLOGS;

总结

1、若全备之后的归档日志全部存在，那么可以不恢复最新的控制文件，直接在新环境做recover操作，若归档丢失则会报错：ORA-01152: file 1 was not restored from a sufficiently old backup。此时，需要先还原最新的控制文件，然后再做recover操作，最后做alter database open resetlogs;即可。
2、若全备之后缺失归档，那么必须先还原最新的控制文件，然后再做recover操作。
3、当数据库比较大的时候（假设2T），归档一般只会保留1-2天，而这1-2天产生的归档有的时候也很大（假设100G）。
而在迁移数据库的时候，对于归档文件的备份，只需要备份开始备份到备份结束之间产生的归档即可，例如无论是全备还是增备都只需要备份在备份期间的归档即可，这样归档文件的备份大约也就不到10G，因为备份过程大概1-2小时。
4、恢复的过程中，请注意归档号的变化。
5、**直接追加归档。若在rman全备之后，产生的归档文件并不多，那么可以不做增量备份，只需要将全备之后产生的归档文件拷贝到目标平台进行注册，然后执行recover操作即可将数据库恢复到最新的状态。**此时，是不需要0级全备份的。

6、要做1级增量备份，那么必须先做0级增量全备，否则直接做1级增量备份也会自动去做0级全备。例如，如下的全备备份集是不能作为1级增量备份的基础：

run
{
 backup as compressed backupset database format '/home/oracle/bk/%d%U.full';
 sql 'alter system archive log current';
 backup as compressed backupset archivelog all  format '/home/oracle/bk/%d%U.arc';
 backup current controlfile format '/home/oracle/bk/%d_%U.ctl';
 backup spfile format '/home/oracle/bk/spfile_%d_%U.ora';
}

备份脚本

0级全备

cat > /home/oracle/rman_restore_orcl.sh <<"EOF0"
#!/bin/ksh

export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

MYDATE=`date +'%Y%m%d%H%M%S'`

rman target /  log /rman/backup_full_$MYDATE.log append <<EOF  
run {
    sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
    sql 'alter session set NLS_LANGUAGE="AMERICAN"';
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    allocate channel c3 type disk;
    allocate channel c4 type disk;
    allocate channel c5 type disk;
    allocate channel c6 type disk;
    allocate channel c7 type disk;
    allocate channel c8 type disk;
    allocate channel c9 type disk;
    allocate channel c10 type disk;
    allocate channel c11 type disk;
    allocate channel c12 type disk;
    allocate channel c13 type disk;
    allocate channel c14 type disk;
    allocate channel c15 type disk;
    allocate channel c16 type disk;
    allocate channel c17 type disk;
    allocate channel c18 type disk;
    allocate channel c19 type disk;
    allocate channel c20 type disk;
    allocate channel c21 type disk;
    
    backup incremental level=0 as compressed backupset FILESPERSET 10 database format '/rman/FULL_%d_%U.full' section size 100G;
    
    
    sql 'alter system archive log current';
  
    backup as compressed backupset archivelog from time 'sysdate-2/24' format '/rman/ARC_%d_%U.arc' section size 100G;
    
    backup current controlfile  format '/rman/control_%U.ctl';
    
    backup spfile format '/rman/spfile_%d_%U.ora';
    
    release channel c1;
    release channel c2;
    release channel c3;
    release channel c4;
    release channel c5;
    release channel c6;
    release channel c7;
    release channel c8;
    release channel c9;
    release channel c10;
    release channel c11;
    release channel c12;
    release channel c13;
    release channel c14;
    release channel c15;
    release channel c16;
    release channel c17;
    release channel c18;
    release channel c19;
    release channel c20;
    release channel c21;
 }

EOF

EOF0



chmod +x /home/oracle/rman_restore_orcl.sh 
nohup sh /home/oracle/rman_restore_orcl.sh &

1级增量备份


-- 每次都是从0级全备开始备份增量
run
{
 sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
 sql 'alter session set NLS_LANGUAGE="AMERICAN"';
 
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 allocate channel c3 type disk;
 allocate channel c4 type disk;
 
 backup INCREMENTAL LEVEL 1 as compressed backupset CUMULATIVE database  format '/rman/%d_%U_inc.full';
 sql 'alter system archive log current';
 backup as compressed backupset archivelog from time 'sysdate-30/24/60' format '/rman/ARC_%d_%U.arc';
 backup current controlfile  format '/rman/%d%U.ctl';


 release channel c1;
 release channel c2;
 release channel c3;
 release channel c4;
 
}
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入