请教数据库备份校验的问题

lujiaguai · 发表于 2016-11-30 21:21:26

唐SIR：
  上课的时候提到交叉验证备份集是否存在，用crosscheck命令。
  另外还有一种校验的方法验证数据库备份集是不是有效，validate database 或者 restore validate database, resrore check logical validate database之类的validata的用法
  我在验证的时候发现，这样的办法可以预演验证数据库restore是否能成功，但是不包含recover 的过程，即归档日志是不是连续，完整的依然无法知道。
  那么有没有办法可以验证recover 可不可以成功的办法？
  通过 crosscheck验证备份文件或者归档日志是不是真实存在，validate来验证备份集是不是完整可用。这些都没办法知道归档日志是不是连续可用的。

  如果备份集之后的归档日志很多，比如有数百个，那么需要恢复的时候能走顺利recover完成，会不会到中间报缺失某个sequence，导致断掉走不到底？
  类似的问题有办法避免吗？

lujiaguai · 发表于 2016-12-1 13:42:28

本帖最后由 lujiaguai 于 2016-12-1 14:12 编辑

感谢唐老师指点，我试了一下，如果delete archivelog sequenct  1515，然后执行validate，命令没有报错，但是结果如下，确实缺失了sequence 1515

RMAN> check logical validate archivelog from sequence 1358 until sequence 1522;
略去部分显示内容
channel ORA_DISK_8: validation complete, elapsed time: 00:00:16
List of Archived Logs
=====================
Thrd Seq    Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 1500 OK    0             71022          +FRA/orcl/archivelog/2016_11_29/thread_1_seq_1500.596.929157985
1 1501 OK    0             69945          +FRA/orcl/archivelog/2016_11_29/thread_1_seq_1501.597.929169965
1 1502 OK    0             71390          +FRA/orcl/archivelog/2016_11_29/thread_1_seq_1502.598.929174549
1 1503 OK    0             70240          +FRA/orcl/archivelog/2016_11_29/thread_1_seq_1503.599.929176883
1 1504 OK    0             72429          +FRA/orcl/archivelog/2016_11_29/thread_1_seq_1504.508.929188829
1 1505 OK    0             70041          +FRA/orcl/archivelog/2016_11_29/thread_1_seq_1505.507.929201203
1 1506 OK    0             73004          +FRA/orcl/archivelog/2016_11_29/thread_1_seq_1506.506.929214057
1 1507 OK    0             70195          +FRA/orcl/archivelog/2016_11_29/thread_1_seq_1507.505.929224837
1 1508 OK    0             71067          +FRA/orcl/archivelog/2016_11_29/thread_1_seq_1508.504.929224903
1 1509 OK    0             70245          +FRA/orcl/archivelog/2016_11_29/thread_1_seq_1509.503.929226383
1 1510 OK    0             71297          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1510.502.929235631
1 1511 OK    0             70301          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1511.501.929247987
1 1512 OK    0             70498          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1512.500.929260673
1 1513 OK    0             82072          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1513.499.929261049
1 1514 OK    0             69964          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1514.498.929270097
1 1516 OK    0             69942          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1516.496.929294513
1 1517 OK    0             70272          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1517.495.929307383
1 1518 OK    0             72940          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1518.494.929311255
1 1519 OK    0             78716          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1519.493.929311317
1 1520 OK    0             97071          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1520.492.929311331
1 1521 OK    0             72384          +FRA/orcl/archivelog/2016_12_01/thread_1_seq_1521.491.929404819

这样的结果命令没有报错，我依然要用眼睛去一行一行对，是不是中间有断点，这个问题是不是只能这样，无法更加的智能了？

接下来，我尝试了直接去asmcmd下直接物理上删除了sequence 1512，再执行检查的时候直接就报告缺少了sequence 1512
RMAN>  validate archivelog from sequence 1358 until sequence 1522;
Starting validate at 2016-12-01:05:07:14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 12/01/2016 05:07:16
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1512.500.929260673
ORA-17503: ksfdopn:2 Failed to open file +FRA/orcl/archivelog/2016_11_30/thread_1_seq_1512.500.929260673
ORA-15012: ASM file '+FRA/orcl/archivelog/2016_11_30/thread_1_seq_1512.500.929260673' does not exist
由此看来，物理上的缺失，更容易被准确的识别出来，这样跟crosscheck配合使用，用于识别归档的可用性，连续性，完整性应该是足够用了。

有消息说11月底的一场M考试涉及到在线日志损坏，需要recover database until cancel，居然有很多人做不出来，讨论被定性为难题，我感到非常的震惊。
由此再一次深刻认识到唐老师的课程给自己带来的帮助是多么的巨大，虽然我还有很多东西不懂，但实际上不知不觉当中已经取得了巨大的进步。
前些日子广州某个很大服务顾问公司，跟我们公司签了数额巨大的ORALCE BIEE+INFOMATICA的BI系统建设合同。
对方某资深DBA负责远程安装测试系统，大DBA还要预约时间，此人创建表空间的语句是这样写的：
create tablespace info961 datafile '+DATA/etl/datafile/info961.DBF' ******

我问对方为什么要写表空间全路径，他告诉我不写全路径命令会报错。
并且对方在明知道有grid的情况下，用户配置文件里的TNS_ADMIN依然指向错误，由此排查近3个小时无果，最后推说系统有问题，试图全部推翻重来一次。
近期周围发生的一些事让我感觉从事oracle dba这一行当真的是水深难测。
很感谢唐老师的教导，能让我在这样一片混沌当中还能保留一次清明。
M考试一样水深难测，到处都是陷阱，近期改用KVM虚拟机，有人居然最右一题RAC安装复制文件的过程用去40分钟导致时间来不及失败。
希望轮到自己的时候能顺利吧。

botang · 发表于 2016-12-1 09:55:10

本帖最后由 botang 于 2016-12-1 09:56 编辑

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key    Thrd Seq    S Low Time
------- ---- ------- - ---------
7    1 15    A 20-OCT-13
      Name: +FRA/orcl/archivelog/2013_12_11/thread_1_seq_15.261.833878627

8    1 16    A 11-DEC-13
      Name: +FRA/orcl/archivelog/2013_12_11/thread_1_seq_16.262.833879059

9    1 17    A 11-DEC-13
      Name: +FRA/orcl/archivelog/2016_11_30/thread_1_seq_17.263.929301565

10    1 18    A 30-NOV-16
      Name: +FRA/orcl/archivelog/2016_11_30/thread_1_seq_18.264.929301621

11    1 19    A 30-NOV-16
      Name: +FRA/orcl/archivelog/2016_11_30/thread_1_seq_19.265.929303277

12    1 20    A 30-NOV-16
      Name: +FRA/orcl/archivelog/2016_11_30/thread_1_seq_20.266.929307663

13    1 21    A 30-NOV-16
      Name: +FRA/orcl/archivelog/2016_12_01/thread_1_seq_21.267.929438471

RMAN> validate archivelog from sequence 15 until sequence 21;

Starting validate at 01-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=193 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=139 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=202 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=9 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=77 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=140 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=200 device type=DISK
channel ORA_DISK_1: starting validation of archived log
channel ORA_DISK_1: specifying archived log(s) for validation
input archived log thread=1 sequence=15 RECID=7 STAMP=833878627
channel ORA_DISK_2: starting validation of archived log
channel ORA_DISK_2: specifying archived log(s) for validation
input archived log thread=1 sequence=16 RECID=8 STAMP=833879059
channel ORA_DISK_3: starting validation of archived log
channel ORA_DISK_3: specifying archived log(s) for validation
input archived log thread=1 sequence=17 RECID=9 STAMP=929301565
channel ORA_DISK_4: starting validation of archived log
channel ORA_DISK_4: specifying archived log(s) for validation
input archived log thread=1 sequence=18 RECID=10 STAMP=929301621
channel ORA_DISK_5: starting validation of archived log
channel ORA_DISK_5: specifying archived log(s) for validation
input archived log thread=1 sequence=19 RECID=11 STAMP=929303277
channel ORA_DISK_6: starting validation of archived log
channel ORA_DISK_6: specifying archived log(s) for validation
input archived log thread=1 sequence=20 RECID=12 STAMP=929307662
channel ORA_DISK_7: starting validation of archived log
channel ORA_DISK_7: specifying archived log(s) for validation
input archived log thread=1 sequence=21 RECID=13 STAMP=929438472
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
List of Archived Logs
=====================
Thrd Seq    Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 15    OK    0             32715          +FRA/orcl/archivelog/2013_12_11/thread_1_seq_15.261.833878627
channel ORA_DISK_2: validation complete, elapsed time: 00:00:00
List of Archived Logs
=====================
Thrd Seq    Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 16    OK    0             31251          +FRA/orcl/archivelog/2013_12_11/thread_1_seq_16.262.833879059
channel ORA_DISK_3: validation complete, elapsed time: 00:00:00
List of Archived Logs
=====================
Thrd Seq    Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 17    OK    0             40106          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_17.263.929301565
channel ORA_DISK_4: validation complete, elapsed time: 00:00:00
List of Archived Logs
=====================
Thrd Seq    Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 18    OK    0             38386          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_18.264.929301621
channel ORA_DISK_5: validation complete, elapsed time: 00:00:00
List of Archived Logs
=====================
Thrd Seq    Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 19    OK    0             67622          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_19.265.929303277
channel ORA_DISK_6: validation complete, elapsed time: 00:00:00
List of Archived Logs
=====================
Thrd Seq    Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 20    OK    0             32092          +FRA/orcl/archivelog/2016_11_30/thread_1_seq_20.266.929307663
channel ORA_DISK_7: validation complete, elapsed time: 00:00:01
List of Archived Logs
=====================
Thrd Seq    Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 21    OK    0             31353          +FRA/orcl/archivelog/2016_12_01/thread_1_seq_21.267.929438471
Finished validate at 01-DEC-16

RMAN>

botang · 发表于 2016-12-1 16:42:01

lujiaguai 发表于 2016-12-1 13:42
感谢唐老师指点，我试了一下，如果delete archivelog sequenct 1515，然后执行validate，命令没有报错， ...

谢谢反馈，继续反馈。我才有动力坚持我的培训风格。这个市场上充满了走江湖的骗子。

如果rman删的，的确是你的现象。

我想可以补充查v$archived_log查一下那个范围中NAME为空的，就是被rman人为删的。

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 1 16:41:01 2016
SQL> conn / as sysdba
Connected.
SQL> desc v$archived_log
Name Null? Type
----------------------------------------- -------- ----------------------------
RECID NUMBER
STAMP NUMBER
NAME VARCHAR2(513)
DEST_ID NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
RESETLOGS_ID NUMBER
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
BLOCKS NUMBER
BLOCK_SIZE NUMBER
CREATOR VARCHAR2(7)
REGISTRAR VARCHAR2(7)
STANDBY_DEST VARCHAR2(3)
ARCHIVED VARCHAR2(3)
APPLIED VARCHAR2(9)
DELETED VARCHAR2(3)
STATUS VARCHAR2(1)
COMPLETION_TIME DATE
DICTIONARY_BEGIN VARCHAR2(3)
DICTIONARY_END VARCHAR2(3)
END_OF_REDO VARCHAR2(3)
BACKUP_COUNT NUMBER
ARCHIVAL_THREAD# NUMBER
ACTIVATION# NUMBER
IS_RECOVERY_DEST_FILE VARCHAR2(3)
COMPRESSED VARCHAR2(3)
FAL VARCHAR2(3)
END_OF_REDO_TYPE VARCHAR2(10)
BACKED_BY_VSS VARCHAR2(3)
SQL>

复制代码

lujiaguai · 发表于 2016-12-2 14:01:01

select sequence# from v$archived_log where sequence#>=1358 and sequence#<=1522 and name is null;
该查询结果非常准确，能实时识别被rman删除归档日志状态，该问题至此非常圆满了。
感谢唐SIR