模拟归档丢失
备库操作，备库取消归档应用，让备库处于只读模式：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
复制主库配置归档2的状态为defer，目的是为了不把归档自动传递到备库,实际情况下往往是由于网络故障，备库挂掉等等情况导致，我们多次切换主库日志：
SQL> ALTER system SET log_archive_dest_state_2 = 'defer';
20:52:31 SQL> alter system switch logfile;
20:54:54 SQL> alter system switch logfile;
20:54:56 SQL> alter system switch logfile;
20:54:57 SQL> alter system switch logfile;
20:55:05 SQL> alter system switch logfile;
20:55:45 SQL> create table TA401.testdg as select * from dual;
20:55:49 SQL> insert into TA401.testdg select * from dual;
20:56:10 SQL> commit;
20:56:43 SQL> alter system switch logfile;
20:56:52 SQL> alter system switch logfile;
20:56:56 SQL> insert into ta401.testdg select * from dual;
20:57:07 SQL> commit;
20:57:11 SQL> alter system switch logfile;
20:57:15 SQL> select * from ta401.testdg;
复制查看主库归档情况：
col name for a100 set linesize 9999 pagesize 9999 SELECT dest_id,THREAD#,NAME,sequence#,archived,applied,a.NEXT_CHANGE# FROM v$archived_log a WHERE a.sequence# >= 75 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d) and a.dest_id=1 ORDER BY a.THREAD#,a.sequence#,a.dest_id;复制
查看备库归档情况：
col name for a100 set linesize 9999 pagesize 9999 SELECT dest_id,THREAD#,NAME,sequence#,archived,applied,a.NEXT_CHANGE# FROM v$archived_log a WHERE a.sequence# >= 75 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d) and a.dest_id=1 ORDER BY a.THREAD#,a.sequence#,a.dest_id;复制
可以看到，备库已经断档了，83到86都没有接收，接下来我们删除主库的归档日志，我们只删除83到86的归档日志：（其实就是我把归档移动到了新建的bak目录下）
cd /oracle/app/oracle/fast_recovery_area/singledb/SINGLEDB_PD/archivelog/2021_04_07 [oracle@dg1 2021_04_07]$ mkdir bak [oracle@dg1 2021_04_07]$ ll total 38792 drwxr-xr-x 2 oracle oinstall 6 Apr 7 11:42 bak -rw-r----- 1 oracle oinstall 10478592 Apr 7 09:52 o1_mf_1_64_j6t42yo8_.arc -rw-r----- 1 oracle oinstall 1734144 Apr 7 09:55 o1_mf_1_65_j6t484sv_.arc -rw-r----- 1 oracle oinstall 16033792 Apr 7 10:15 o1_mf_1_66_j6t5f69x_.arc -rw-r----- 1 oracle oinstall 2048 Apr 7 10:15 o1_mf_1_67_j6t5f80x_.arc -rw-r----- 1 oracle oinstall 2560 Apr 7 10:15 o1_mf_1_68_j6t5f9sl_.arc -rw-r----- 1 oracle oinstall 1024 Apr 7 10:15 o1_mf_1_69_j6t5f9t4_.arc -rw-r----- 1 oracle oinstall 2560 Apr 7 10:15 o1_mf_1_70_j6t5ff0t_.arc -rw-r----- 1 oracle oinstall 6188544 Apr 7 10:55 o1_mf_1_71_j6t7rq03_.arc -rw-r----- 1 oracle oinstall 1536 Apr 7 10:55 o1_mf_1_72_j6t7rrfw_.arc -rw-r----- 1 oracle oinstall 8704 Apr 7 10:55 o1_mf_1_73_j6t7rw2x_.arc -rw-r----- 1 oracle oinstall 4990464 Apr 7 11:12 o1_mf_1_74_j6t8slly_.arc -rw-r----- 1 oracle oinstall 13824 Apr 7 11:13 o1_mf_1_75_j6t8tbw9_.arc -rw-r----- 1 oracle oinstall 18432 Apr 7 11:13 o1_mf_1_76_j6t8vd75_.arc -rw-r----- 1 oracle oinstall 54784 Apr 7 11:15 o1_mf_1_77_j6t8ykyk_.arc -rw-r----- 1 oracle oinstall 84480 Apr 7 11:18 o1_mf_1_78_j6t93cxk_.arc -rw-r----- 1 oracle oinstall 2048 Apr 7 11:18 o1_mf_1_79_j6t93fyc_.arc -rw-r----- 1 oracle oinstall 3072 Apr 7 11:18 o1_mf_1_80_j6t93k7w_.arc -rw-r----- 1 oracle oinstall 1024 Apr 7 11:18 o1_mf_1_81_j6t93l7s_.arc -rw-r----- 1 oracle oinstall 26112 Apr 7 11:18 o1_mf_1_82_j6t95112_.arc -rw-r----- 1 oracle oinstall 1536 Apr 7 11:18 o1_mf_1_83_j6t95230_.arc -rw-r----- 1 oracle oinstall 3072 Apr 7 11:19 o1_mf_1_84_j6t955yw_.arc -rw-r----- 1 oracle oinstall 1024 Apr 7 11:19 o1_mf_1_85_j6t95601_.arc -rw-r----- 1 oracle oinstall 20480 Apr 7 11:19 o1_mf_1_86_j6t96c67_.arc [oracle@dg1 2021_04_07]$ mv o1_mf_1_83_j6t95230_.arc bak [oracle@dg1 2021_04_07]$ mv o1_mf_1_84_j6t955yw_.arc bak [oracle@dg1 2021_04_07]$ mv o1_mf_1_85_j6t95601_.arc bak [oracle@dg1 2021_04_07]$ mv o1_mf_1_86_j6t96c67_.arc bak复制
主库开启备库的归档： SQL> ALTER system SET log_archive_dest_state_2 = enable; 备库开启实时应用： SQL> alter database recover managed standby database using current logfile disconnect from session;复制
(1)关闭同步
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
(2)查找主库中是否存在产生GAP的归档文件
---备库gap
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 83 86
---主库
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 83 AND 86;
NAME
------------------------------------------------------------------
/oracle/app/oracle/fast_recovery_area/singledb/SINGLEDB_PD/archive
log/2021_04_07/o1_mf_1_83_j6t95230_.arc
/oracle/app/oracle/fast_recovery_area/singledb/SINGLEDB_PD/archive
log/2021_04_07/o1_mf_1_84_j6t955yw_.arc
/oracle/app/oracle/fast_recovery_area/singledb/SINGLEDB_PD/archive
log/2021_04_07/o1_mf_1_85_j6t95601_.arc
/oracle/app/oracle/fast_recovery_area/singledb/SINGLEDB_PD/archive
log/2021_04_07/o1_mf_1_86_j6t96c67_.arc
---发现主备部分归档文件已经自动删除，无法直接恢复 （我移动到bak目录下了）
/oracle/app/oracle/fast_recovery_area/singledb/SINGLEDB_PD/archivelog/2021_04_07
[oracle@dg1 2021_04_07]$ ll
total 39740
drwxr-xr-x 2 oracle oinstall 134 Apr 7 11:43 bak
-rw-r----- 1 oracle oinstall 10478592 Apr 7 09:52 o1_mf_1_64_j6t42yo8_.arc
-rw-r----- 1 oracle oinstall 1734144 Apr 7 09:55 o1_mf_1_65_j6t484sv_.arc
-rw-r----- 1 oracle oinstall 16033792 Apr 7 10:15 o1_mf_1_66_j6t5f69x_.arc
-rw-r----- 1 oracle oinstall 2048 Apr 7 10:15 o1_mf_1_67_j6t5f80x_.arc
-rw-r----- 1 oracle oinstall 2560 Apr 7 10:15 o1_mf_1_68_j6t5f9sl_.arc
-rw-r----- 1 oracle oinstall 1024 Apr 7 10:15 o1_mf_1_69_j6t5f9t4_.arc
-rw-r----- 1 oracle oinstall 2560 Apr 7 10:15 o1_mf_1_70_j6t5ff0t_.arc
-rw-r----- 1 oracle oinstall 6188544 Apr 7 10:55 o1_mf_1_71_j6t7rq03_.arc
-rw-r----- 1 oracle oinstall 1536 Apr 7 10:55 o1_mf_1_72_j6t7rrfw_.arc
-rw-r----- 1 oracle oinstall 8704 Apr 7 10:55 o1_mf_1_73_j6t7rw2x_.arc
-rw-r----- 1 oracle oinstall 4990464 Apr 7 11:12 o1_mf_1_74_j6t8slly_.arc
-rw-r----- 1 oracle oinstall 13824 Apr 7 11:13 o1_mf_1_75_j6t8tbw9_.arc
-rw-r----- 1 oracle oinstall 18432 Apr 7 11:13 o1_mf_1_76_j6t8vd75_.arc
-rw-r----- 1 oracle oinstall 54784 Apr 7 11:15 o1_mf_1_77_j6t8ykyk_.arc
-rw-r----- 1 oracle oinstall 84480 Apr 7 11:18 o1_mf_1_78_j6t93cxk_.arc
-rw-r----- 1 oracle oinstall 2048 Apr 7 11:18 o1_mf_1_79_j6t93fyc_.arc
-rw-r----- 1 oracle oinstall 3072 Apr 7 11:18 o1_mf_1_80_j6t93k7w_.arc
-rw-r----- 1 oracle oinstall 1024 Apr 7 11:18 o1_mf_1_81_j6t93l7s_.arc
-rw-r----- 1 oracle oinstall 26112 Apr 7 11:18 o1_mf_1_82_j6t95112_.arc
-rw-r----- 1 oracle oinstall 999936 Apr 7 11:46 o1_mf_1_87_j6tbrp3v_.arc
(3)确定增量恢复的起始SCN号
---备库
SQL> select file#,checkpoint_change# from v$datafile order by checkpoint_change#;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
8 1450307
5 1450307
6 1450307
10 2981271
9 2981271
13 2981271
12 2981271
11 2981271
19 2981272
29 2981272
18 2981272
17 2981272
22 3096270
21 3096270
20 3096270
30 3112833
25 3112833
24 3112833
23 3112833
7 3112833
4 3112833
31 3112833
1 3112833
3 3112833
24 rows selected.
-- 主库：
SQL> select file#,checkpoint_change# from v$datafile order by checkpoint_change#;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
8 1450307
5 1450307
6 1450307
10 2981271
9 2981271
13 2981271
12 2981271
11 2981271
19 2981272
29 2981272
18 2981272
FILE# CHECKPOINT_CHANGE#
---------- ------------------
17 2981272
22 3096270
21 3096270
20 3096270
30 3116453
25 3116453
24 3116453
23 3116453
7 3116453
4 3116453
31 3116453
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3116453
3 3116453
24 rows selected.
(4)主库:使用Rman基于SCN的增量备份
[oracle@dg1 2021_04_07]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Apr 7 11:55:41 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: SINGLEDB (DBID=2273128553)
RMAN>
run
{
allocate channel c1 device type disk;
backup as compressed backupset incremental from scn 3112833 database format '/home/oracle/rman0407/%U';
release channel c1;
}
---将备份传到备库 （通过scp）
(5)备库:恢复
---1 nomount备库
SQL> shutdown immediate.
SQL> startup nomount
---2 通过备份恢复控制文件
RMAN> restore standby controlfile from '/home/oracle/rman0407/12sjnabm_1_1';
---3 mount备库
SQL> alter database mount standby database;
Database altered.
---4 恢复备库
RMAN> catalog start with '/home/oracle/rman0407';
RMAN> recover database;
......
archived log file name=/u01/ora_arch/1_51211_910299442.dbf thread=1 sequence=51211
media recovery complete, elapsed time: 00:00:05
Finished recover at 16-NOV-17
(6)验证
---1 open备库
SQL> alter database open read only;
---3 启动应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
---4 查看归档GAP
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
---5 查看当前序列号
SQL> select file#,checkpoint_change# from v$datafile order by checkpoint_change#;
---主库:创建测试数据
SQL> create table test0407 as select level as id from dual connect by level <=10;
---备库:查询测试数据
SQL> select * from test0407;
复制「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
751次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
645次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
567次阅读
2025-04-20 10:07:02
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
518次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
511次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
497次阅读
2025-04-22 00:20:37
一页概览：Oracle GoldenGate
甲骨文云技术
479次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
437次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
381次阅读
2025-04-15 14:48:05
OR+DBLINK的关联SQL优化思路
布衣
371次阅读
2025-05-05 19:28:36
