RMAN备份与恢复（新旧控制文件及归档日志）测试（叙）

2022-01-26 17:22:19

（我的学习过程）

继上一次做的试验，过后我又思考了下，当然其中的4点结论没有问题，但是却忽略了一点，上次的试验证明了在不使用恢复目录的情况下，做恢复时使用的归档重做日志文件只能是在控制文件中有记录的，
backup format "${BACKUP_DB_TARGET}/db_${ORACLE_SID}_${DATE_TAG}_%s_%p_%t"
database plus archivelog delete all input
format "${BACKUP_DB_TARGET}/arch_${ORACLE_SID}_${DATE_TAG}_%s_%p_%t";
就这样的一个语句，虽然不错，但是有一个缺点，他的备份顺序是
1、切换日志
2、备份归档
3、备份数据文件
4、备份SPFILE，CONTROLFILE
5、再切换
6、在备份切换出来的归档日志文件
好像有一个明显的缺点，也就是最后归档的备份信息是不会记录在控制文件中的，可以考虑一下这样有何不拓的地方，如果我只取一次备份周期的全备包括归档日志文件，是不能够进行恢复，他会报找不到日志组**，而这个日志组相当重要，
没有他数据文件就不能同步（应为进行热备的时候各个数据文件的SCN是不一致的，恢复的时候需要滚动归档日志文件来进行同步），所以如果我们要恢复我们只能取的是最新的控制文件，而数据文件只能取上次备份的数据文件，这样才能进行不完全恢复。我觉得最好的调整是把顺序调整为：
1、切换日志
2、备份归档
3、备份数据文件
4、再切换
5、在备份切换出来的归档日志文件
6、备份SPFILE，CONTROLFILE
这样就非常合理了，下面用试验进行证明（同样红色标记为试验过程，粉色标记为重点）


使用语句
backup format "/oradatatest/rman/datafile/db_%s_%p_%T"
database plus archivelog delete all input
format "/oradatatest/rman/archive/arch_%s_%p_%T";
进行两次备份中间同样用SWITCH LOGFILE模拟日志切换。存档
贴出备份全过程：
RMAN> backup format "/oradatatest/rman/datafile/db_%s_%p_%T"
2> database plus archivelog delete all input
3> format "/oradatatest/rman/archive/arch_%s_%p_%T";


Starting backup at 02-JUN-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=12 recid=30 stamp=688467805
input archive log thread=1 sequence=13 recid=31 stamp=688467967
channel ORA_DISK_1: starting piece 1 at 02-JUN-09
channel ORA_DISK_1: finished piece 1 at 02-JUN-09
piece handle=/oradatatest/rman/archive/arch_5_1_20090602 tag=TAG20090602T090607 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/logmin/archive/1_12_677591436.dbf recid=30 stamp=688467805
archive log filename=/logmin/archive/1_13_677591436.dbf recid=31 stamp=688467967
Finished backup at 02-JUN-09

Starting backup at 02-JUN-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/pp/pp/system01.dbf
input datafile fno=00004 name=/oradata/pp/pp/users01.dbf
input datafile fno=00003 name=/oradata/pp/pp/sysaux01.dbf
input datafile fno=00002 name=/oradata/pp/pp/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 02-JUN-09
channel ORA_DISK_1: finished piece 1 at 02-JUN-09
piece handle=/oradatatest/rman/datafile/db_6_1_20090602 tag=TAG20090602T090612 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 02-JUN-09
channel ORA_DISK_1: finished piece 1 at 02-JUN-09
piece handle=/oradatatest/rman/datafile/db_7_1_20090602 tag=TAG20090602T090612 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 02-JUN-09

Starting backup at 02-JUN-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=14 recid=32 stamp=688468070
channel ORA_DISK_1: starting piece 1 at 02-JUN-09
channel ORA_DISK_1: finished piece 1 at 02-JUN-09
piece handle=/oradatatest/rman/archive/arch_8_1_20090602 tag=TAG20090602T090750 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/logmin/archive/1_14_677591436.dbf recid=32 stamp=688468070
Finished backup at 02-JUN-09

这里做了切换
RMAN> backup format "/oradatatest/rman/datafile/db_%s_%p_%T"
2> database plus archivelog delete all input
3> format "/oradatatest/rman/archive/arch_%s_%p_%T";


Starting backup at 02-JUN-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=15 recid=33 stamp=688468104
input archive log thread=1 sequence=16 recid=34 stamp=688468112
input archive log thread=1 sequence=17 recid=35 stamp=688468115
input archive log thread=1 sequence=18 recid=36 stamp=688468230
channel ORA_DISK_1: starting piece 1 at 02-JUN-09
channel ORA_DISK_1: finished piece 1 at 02-JUN-09
piece handle=/oradatatest/rman/archive/arch_9_1_20090602 tag=TAG20090602T091030 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/logmin/archive/1_15_677591436.dbf recid=33 stamp=688468104
archive log filename=/logmin/archive/1_16_677591436.dbf recid=34 stamp=688468112
archive log filename=/logmin/archive/1_17_677591436.dbf recid=35 stamp=688468115
archive log filename=/logmin/archive/1_18_677591436.dbf recid=36 stamp=688468230
Finished backup at 02-JUN-09

Starting backup at 02-JUN-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/pp/pp/system01.dbf
input datafile fno=00004 name=/oradata/pp/pp/users01.dbf
input datafile fno=00003 name=/oradata/pp/pp/sysaux01.dbf
input datafile fno=00002 name=/oradata/pp/pp/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 02-JUN-09
channel ORA_DISK_1: finished piece 1 at 02-JUN-09
piece handle=/oradatatest/rman/datafile/db_10_1_20090602 tag=TAG20090602T091032 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 02-JUN-09
channel ORA_DISK_1: finished piece 1 at 02-JUN-09
piece handle=/oradatatest/rman/datafile/db_11_1_20090602 tag=TAG20090602T091032 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 02-JUN-09

Starting backup at 02-JUN-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=19 recid=37 stamp=688468321
channel ORA_DISK_1: starting piece 1 at 02-JUN-09
channel ORA_DISK_1: finished piece 1 at 02-JUN-09
piece handle=/oradatatest/rman/archive/arch_12_1_20090602 tag=TAG20090602T091201 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/logmin/archive/1_19_677591436.dbf recid=37 stamp=688468321
Finished backup at 02-JUN-09

RMAN>
重顺序很容易看出刚才所说的，然后进行恢复测试，先取第一个备份的控制文件和数据文件，肯定不能进行不完全恢复我猜测。
RMAN> restore controlfile from '/oradatatest/rman/datafile/db_7_1_20090602';

Starting restore at 02-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/oradata/pp/pp/control01.ctl
output filename=/oradata/pp/pp/control02.ctl
output filename=/oradata/pp/pp/control03.ctl
Finished restore at 02-JUN-09
RMAN> sql'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> list backup;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3       36.89M     DISK        00:00:03     02-JUN-09     
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20090602T090607
        Piece Name: /oradatatest/rman/archive/arch_5_1_20090602

  List of Archived Logs in backup set 3
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    12      503557     01-JUN-09 532840     02-JUN-09
  1    13      532840     02-JUN-09 532945     02-JUN-09

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    932.13M    DISK        00:01:30     02-JUN-09     
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20090602T090612
        Piece Name: /oradatatest/rman/datafile/db_6_1_20090602
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 532950     02-JUN-09 /oradata/pp/pp/system01.dbf
  2       Full 532950     02-JUN-09 /oradata/pp/pp/undotbs01.dbf
  3       Full 532950     02-JUN-09 /oradata/pp/pp/sysaux01.dbf
  4       Full 532950     02-JUN-09 /oradata/pp/pp/users01.dbf
可以看到14的那个归档文件根本不在这里，
RMAN> recover database until sequence 14;

Starting recover at 02-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/02/2009 09:20:37
RMAN-06556: datafile 1 must be restored from backup older than scn 532840
这里可以看到其实我要RESTORE的数据文件已经在13之后了，比他更新，所以我们要使用14来进行恢复
RMAN> recover database until sequence 15;

Starting recover at 02-JUN-09
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=14
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oradata/pp/pp/system01.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/02/2009 09:21:58
RMAN-06054: media recovery requesting unknown log: thread 1 seq 14 lowscn 532992
可以看出我的猜测是正确的，不能使用14日志，因为这个备份集没有记录在控制文件中，
然后使用第2次的控制文件第一次的数据文件进行恢复，也只有这样了，

贴出过程：
[oracle@localhost datafile]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 2 09:24:00 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: pp (not mounted)

RMAN> restore controlfile from '/oradatatest/rman/datafile/db_11_1_20090602';

Starting restore at 02-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/oradata/pp/pp/control01.ctl
output filename=/oradata/pp/pp/control02.ctl
output filename=/oradata/pp/pp/control03.ctl
Finished restore at 02-JUN-09

RMAN> sql'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> list backup;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3       36.89M     DISK        00:00:03     02-JUN-09     
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20090602T090607
        Piece Name: /oradatatest/rman/archive/arch_5_1_20090602

  List of Archived Logs in backup set 3
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    12      503557     01-JUN-09 532840     02-JUN-09
  1    13      532840     02-JUN-09 532945     02-JUN-09

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    932.13M    DISK        00:01:30     02-JUN-09     
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20090602T090612
        Piece Name: /oradatatest/rman/datafile/db_6_1_20090602
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 532950     02-JUN-09 /oradata/pp/pp/system01.dbf
  2       Full 532950     02-JUN-09 /oradata/pp/pp/undotbs01.dbf
  3       Full 532950     02-JUN-09 /oradata/pp/pp/sysaux01.dbf
  4       Full 532950     02-JUN-09 /oradata/pp/pp/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    6.80M      DISK        00:00:02     02-JUN-09     
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20090602T090612
        Piece Name: /oradatatest/rman/datafile/db_7_1_20090602
  Control File Included: Ckp SCN: 532992       Ckp time: 02-JUN-09
  SPFILE Included: Modification time: 02-JUN-09

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6       25.50K     DISK        00:00:01     02-JUN-09     
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20090602T090750
        Piece Name: /oradatatest/rman/archive/arch_8_1_20090602

  List of Archived Logs in backup set 6
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    14      532945     02-JUN-09 532996     02-JUN-09

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       12.00K     DISK        00:00:01     02-JUN-09     
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20090602T091030
        Piece Name: /oradatatest/rman/archive/arch_9_1_20090602

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    15      532996     02-JUN-09 533012     02-JUN-09
  1    16      533012     02-JUN-09 533016     02-JUN-09
  1    17      533016     02-JUN-09 533018     02-JUN-09
  1    18      533018     02-JUN-09 533072     02-JUN-09

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    932.13M    DISK        00:01:19     02-JUN-09     
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20090602T091032
        Piece Name: /oradatatest/rman/datafile/db_10_1_20090602
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 533076     02-JUN-09 /oradata/pp/pp/system01.dbf
  2       Full 533076     02-JUN-09 /oradata/pp/pp/undotbs01.dbf
  3       Full 533076     02-JUN-09 /oradata/pp/pp/sysaux01.dbf
  4       Full 533076     02-JUN-09 /oradata/pp/pp/users01.dbf
  可以看到这次的控制文件包含了14日志组，所以这个文件可以恢复到日志18，但是归档日志组19是没有的，这也是这个方法的弊端
  必须使用上一次备份的数据文件来进行全恢复，但是起码可以恢复。
  RMAN> restore database from tag=TAG20090602T090612;

Starting restore at 02-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/pp/pp/system01.dbf
restoring datafile 00002 to /oradata/pp/pp/undotbs01.dbf
restoring datafile 00003 to /oradata/pp/pp/sysaux01.dbf
restoring datafile 00004 to /oradata/pp/pp/users01.dbf
channel ORA_DISK_1: reading from backup piece /oradatatest/rman/datafile/db_6_1_20090602
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradatatest/rman/datafile/db_6_1_20090602 tag=TAG20090602T090612
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 02-JUN-09

RMAN> recover database until sequence 19;

Starting recover at 02-JUN-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 18 is already on disk as file /oradata/pp/pp/redo02.log
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=14
channel ORA_DISK_1: reading from backup piece /oradatatest/rman/archive/arch_8_1_20090602
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradatatest/rman/archive/arch_8_1_20090602 tag=TAG20090602T090750
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/logmin/archive/1_14_677591436.dbf thread=1 sequence=14
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=15
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=16
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=17
channel ORA_DISK_1: reading from backup piece /oradatatest/rman/archive/arch_9_1_20090602
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradatatest/rman/archive/arch_9_1_20090602 tag=TAG20090602T091030
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/logmin/archive/1_15_677591436.dbf thread=1 sequence=15
archive log filename=/logmin/archive/1_16_677591436.dbf thread=1 sequence=16
archive log filename=/logmin/archive/1_17_677591436.dbf thread=1 sequence=17
archive log filename=/oradata/pp/pp/redo02.log thread=1 sequence=18
media recovery complete, elapsed time: 00:00:03
Finished recover at 02-JUN-09
RMAN> sql'alter database open resetlogs';

sql statement: alter database open resetlogs


对吗？最后的试验结果确实如此。所以改进的办法就是如下顺序进行备份
1、切换日志
2、备份归档
3、备份数据文件（其实这里全备会自动备份控制文件）
4、再切换
5、在备份切换出来的归档日志文件
6、备份SPFILE，CONTROLFILE（手动的进行）
这样任何一个备份周期的备份集都能够恢复，但是每一次的全备数据文件都会自动备份CONTROLFILE，所以我们所能做的是额外的在手工备份一次控制文件，当然沿用以前的办法只要取出两个周期的全备一样的可以进行不完全恢复，这里还涉及到在保存备份信息的冗余上，最好使用天数而不使用份数，不然ORACLE会觉得你的控制文件份数太多，会删除我们手动进行的备份。
另外说下作为DBA要多多考虑下备份，毕竟备份是最重要的，性能调优等其实都相对而言次要一些，如果有一天你的数据库挂掉了，你对老板说我先前考虑的备份方案有误，现在不能恢复了，我想后果是很严重的。所以尽量把事情往坏处想，做好准备，在突发事件面前才不会发呆，呵呵，乱说了一下^_^。

我考虑不对的地方希望看过的朋友可以告诉我谢谢。。。我也是新手上路

上一篇：linux C++ 多线程使用pthread_cond 条件变量


下一篇：《构建高可用Linux服务器 第3版》—— 2.7　系统维护时应注意的非技术因素