1. 首页
  2. Oracle
  3. 正文

简单记录一次ADG备库同步故障

2021年8月13日 1391点热度 0人点赞 1条评论

这是一套11g的老库，主库3节点，备库1节点。项目上于昨天晚上做某测试扩容了表空间，在其他位置新建了9个数据文件，在备库无法创建这个非标准位置的datafile，从而导致同步中断。
第二天上午才发现数据不同步，备库的只读业务受到影响。

查看alertlog看到明显的报错

ORA-01111: name for data file 41 is unknown - rename to correct file
ORA-01110: data file 41: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00041'
ORA-01157: cannot identify/lock data file 41 - see DBWR trace file
ORA-01111: name for data file 41 is unknown - rename to correct file
ORA-01110: data file 41: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00041'

后续处理时发现，实际新建了共9个数据文件，这样手动恢复时更慢了。

查看备库未应用的最小log sequence

SELECT thread#, MIN(sequence#) FROM v$archived_log WHERE applied = 'NO' GROUP BY thread#;

        THREAD#  MIN(SEQUENCE#)
--------------- ---------------
              1          668069
              2          565913
              3          605677

可知需从主库归档或rman备份搞到的起始sequence。

查看备库此时还存在的最小sequence

col name for a100
select thread#, name, applied from v$archived_log where name like '%2021_08_12/thread_2%';
select thread#, name, applied from v$archived_log where name like '%2021_08_12/thread_1%';
select thread#, name, applied from v$archived_log where name like '%2021_08_12/thread_3%';
        THREAD# NAME                                                                                                 APPLIED
--------------- ---------------------------------------------------------------------------------------------------- ---------
              2 +FRA/jkdbadg/archivelog/2021_08_12/thread_2_seq_565922.1646.1080425497                               NO
              2 +FRA/jkdbadg/archivelog/2021_08_12/thread_2_seq_565923.603.1080426099                                NO
--------------- ---------------------------------------------------------------------------------------------------- ---------
              1 +FRA/jkdbadg/archivelog/2021_08_12/thread_1_seq_668088.702.1080425419                                NO
              1 +FRA/jkdbadg/archivelog/2021_08_12/thread_1_seq_668089.1258.1080425495                               NO
--------------- ---------------------------------------------------------------------------------------------------- ---------
              3 +FRA/jkdbadg/archivelog/2021_08_12/thread_3_seq_605696.464.1080425385                                NO
              3 +FRA/jkdbadg/archivelog/2021_08_12/thread_3_seq_605697.549.1080425565                                NO

可见备库的早期sequence日志被删掉了，但可算出需搞到的各个节点的sequence范围，其实在alertlog也能看到这个信息，或者从主库也能看到gap。

此时在主库查看这些日志是否存在

col name for a100
select name from v$archived_log where name like '%thread_2_seq_5659%' order by sequence#;
select name from v$archived_log where name like '%thread_1_seq_6680%' order by sequence#;
select name from v$archived_log where name like '%thread_3_seq_6056%' order by sequence#;

可见主库的被删了，只能恢复。

根据如上查出的sequence范围，用RMAN恢复，这个时间比较长

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
restore archivelog from logseq 565913 until logseq 565921 thread 2;
release channel c1;
release channel c2;
}
耗时：约50分钟吧

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
restore archivelog from logseq 668069 until logseq 668087 thread 1;
release channel c1;
release channel c2;
release channel c3;
}
耗时：
channel c1: restore complete, elapsed time: 01:06:36

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
restore archivelog from logseq 605677 until logseq 605695 thread 3;
release channel c1;
release channel c2;
release channel c3;
}
耗时：
channel c1: restore complete, elapsed time: 01:04:16

可见总共不到50个log，合计不到100g文件，恢复总耗时3小时左右，期间也开了并行，实际效果一般，它就是很慢。

列一下恢复后的这些归档

+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565913.473.1080476161
+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565914.2899.1080476113
+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565915.3016.1080476221
+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565916.3181.1080473795
+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565917.1065.1080476095
+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565918.3691.1080476199
+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565919.901.1080476099
+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565920.3093.1080476215
+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565921.2949.1080476203

+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668069.1175.1080477495
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668070.4519.1080477471
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668071.3017.1080476777
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668072.4492.1080476045
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668073.4420.1080474467
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668074.460.1080474465
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668075.660.1080474463
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668076.4320.1080474469
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668077.1439.1080477471
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668078.2937.1080476731
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668079.1055.1080475307
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668080.3592.1080477511
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668081.4135.1080478199
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668082.3376.1080477473
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668083.689.1080474465
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668084.844.1080476005
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668085.292.1080476049
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668086.972.1080475263
+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668087.4327.1080477467

+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605677.682.1080480277
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605678.3212.1080482429
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605679.3467.1080480273
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605680.1296.1080482427
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605681.1585.1080482445
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605682.264.1080480981
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605683.1220.1080480979
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605684.1581.1080479581
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605685.614.1080478803
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605686.3263.1080481711
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605687.984.1080480975
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605688.1080.1080479573
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605689.717.1080478801
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605690.3227.1080478801
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605691.1373.1080479579
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605692.581.1080479573
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605693.2787.1080480297
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605694.1312.1080481017
+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605695.4046.1080479629

从ASM拷贝到nfs，这个是物理拷贝，比较快

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;

copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565913.473.1080476161'  to '/nfs/bak/archivelog/2021_08_13/thread_2_seq_565913.473.1080476161';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565914.2899.1080476113' to '/nfs/bak/archivelog/2021_08_13/thread_2_seq_565914.2899.1080476113';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565915.3016.1080476221' to '/nfs/bak/archivelog/2021_08_13/thread_2_seq_565915.3016.1080476221';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565916.3181.1080473795' to '/nfs/bak/archivelog/2021_08_13/thread_2_seq_565916.3181.1080473795';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565917.1065.1080476095' to '/nfs/bak/archivelog/2021_08_13/thread_2_seq_565917.1065.1080476095';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565918.3691.1080476199' to '/nfs/bak/archivelog/2021_08_13/thread_2_seq_565918.3691.1080476199';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565919.901.1080476099'  to '/nfs/bak/archivelog/2021_08_13/thread_2_seq_565919.901.1080476099';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565920.3093.1080476215' to '/nfs/bak/archivelog/2021_08_13/thread_2_seq_565920.3093.1080476215';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_2_seq_565921.2949.1080476203' to '/nfs/bak/archivelog/2021_08_13/thread_2_seq_565921.2949.1080476203';

release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;

copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668069.1175.1080477495' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668069.1175.1080477495';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668070.4519.1080477471' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668070.4519.1080477471';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668071.3017.1080476777' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668071.3017.1080476777';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668072.4492.1080476045' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668072.4492.1080476045';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668073.4420.1080474467' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668073.4420.1080474467';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668074.460.1080474465'  to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668074.460.1080474465';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668075.660.1080474463'  to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668075.660.1080474463';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668076.4320.1080474469' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668076.4320.1080474469';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668077.1439.1080477471' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668077.1439.1080477471';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668078.2937.1080476731' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668078.2937.1080476731';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668079.1055.1080475307' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668079.1055.1080475307';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668080.3592.1080477511' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668080.3592.1080477511';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668081.4135.1080478199' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668081.4135.1080478199';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668082.3376.1080477473' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668082.3376.1080477473';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668083.689.1080474465'  to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668083.689.1080474465';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668084.844.1080476005'  to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668084.844.1080476005';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668085.292.1080476049'  to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668085.292.1080476049';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668086.972.1080475263'  to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668086.972.1080475263';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_1_seq_668087.4327.1080477467' to '/nfs/bak/archivelog/2021_08_13/thread_1_seq_668087.4327.1080477467';

release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;

copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605677.682.1080480277'  to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605677.682.1080480277';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605678.3212.1080482429' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605678.3212.1080482429';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605679.3467.1080480273' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605679.3467.1080480273';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605680.1296.1080482427' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605680.1296.1080482427';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605681.1585.1080482445' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605681.1585.1080482445';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605682.264.1080480981'  to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605682.264.1080480981';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605683.1220.1080480979' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605683.1220.1080480979';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605684.1581.1080479581' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605684.1581.1080479581';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605685.614.1080478803'  to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605685.614.1080478803';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605686.3263.1080481711' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605686.3263.1080481711';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605687.984.1080480975'  to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605687.984.1080480975';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605688.1080.1080479573' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605688.1080.1080479573';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605689.717.1080478801'  to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605689.717.1080478801';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605690.3227.1080478801' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605690.3227.1080478801';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605691.1373.1080479579' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605691.1373.1080479579';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605692.581.1080479573'  to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605692.581.1080479573';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605693.2787.1080480297' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605693.2787.1080480297';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605694.1312.1080481017' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605694.1312.1080481017';
copy archivelog '+FRA/jkdb/archivelog/2021_08_13/thread_3_seq_605695.4046.1080479629' to '/nfs/bak/archivelog/2021_08_13/thread_3_seq_605695.4046.1080479629';

release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

在备库注册归档备份

使用alter database register logfile 'XXXXXX';方式比较麻烦，尤其文件多的情况下，使用catalog start with比较轻松，只要文件在一个目录下。
catalog start with '/nfs/bak/archivelog/2021_08_13/';
备库有了所需的归档就好说了，之后就可以手动重建那9个数据文件了【后续补上】，只能一个个的建，也比较考验耐心，这次故障处理前后总共耗时5个多小时，真是累眼。

标签： 暂无
最后更新：2021年8月13日

liking

这个人很懒，什么都没留下

文章评论

  • liking

    注：实际一开始在备库查看已经同步的最大log sequence#来得更直接、靠谱。

    2021年8月13日
    回复

    • COPYRIGHT © 2023 Liking's Blog. ALL RIGHTS RESERVED.

    Theme Kratos Made By Seaton Jiang