首页 > 数据库 > Oracle > DG主备日志缺失,数据无法同步修复
1、数据库版本:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
2、检查主备库同步状态
主库:
SYS@orcl>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Next log sequence to archive 8
Current log sequence 8
备库:
SYS@orcl>select process,sequence#,status from v$managed_standby;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 0 CONNECTED
DGRD 0 ALLOCATED
DGRD 0 ALLOCATED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
RFS 0 IDLE
RFS 8 IDLE
RFS 0 IDLE
MRP0 8 APPLYING_LOG
10 rows selected.
3、主库 scott/oracle@pdb1 增加 it表记录
SCOTT@pdb1>select count(*) from it;
COUNT(*)
----------
16
SCOTT@pdb1>commit;
Commit complete
备库显示已经同步:
SCOTT@pdb1>select count(*) from it;
COUNT(*)
----------
12
4、删除主库所有在线 redo日志,重启库:
[oracle@db1 ORCL]$ rm -rf redo0*
[oracle@db1 ORCL]$ ll
total 3015912
-rw-r----- 1 oracle oinstall 20103168 Jul 31 10:10 control01.ctl
-rw-r----- 1 oracle oinstall 104865792 Jul 31 09:33 data01.dbf
drwxr-xr-x 2 oracle oinstall 103 Jul 31 09:30 pdb1
drwxr-xr-x 2 oracle oinstall 6 Jul 31 09:20 pdb2
drwxr-xr-x 2 oracle oinstall 6 Jul 31 09:20 pdb3
drwxr-xr-x 2 oracle oinstall 6 Jul 31 09:20 pdb4
drwxr-x--- 2 oracle oinstall 111 Jul 31 09:30 pdbseed
-rw-r----- 1 oracle oinstall 209715712 Jul 31 09:33 standby01.log
-rw-r----- 1 oracle oinstall 209715712 Jul 31 09:33 standby02.log
-rw-r----- 1 oracle oinstall 209715712 Jul 31 09:33 standby03.log
-rw-r----- 1 oracle oinstall 209715712 Jul 31 09:33 standby04.log
-rw-r----- 1 oracle oinstall 796925952 Jul 31 10:08 sysaux01.dbf
-rw-r----- 1 oracle oinstall 964698112 Jul 31 10:08 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Jul 31 09:33 temp.dbf
-rw-r----- 1 oracle oinstall 356524032 Jul 31 10:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Jul 31 09:33 users01.dbf
主库 alert.log报错:
2022-07-31T10:15:47.167108+08:00
ARC1 (PID:24715): Master archival failure: error=313
2022-07-31T10:15:47.168645+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc2_24717.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/ORCL/redo02.log'
4、清空在线 redo日志
主库:
RMAN> list failure;
using target database control file instead of recovery catalog
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
8303 CRITICAL OPEN 31-JUL-22 Online log group 3 is unavailable
8297 CRITICAL OPEN 31-JUL-22 Online log group 2 is unavailable
8291 CRITICAL OPEN 31-JUL-22 Online log group 1 is unavailable
8306 HIGH OPEN 31-JUL-22 Online log member /oradata/ORCL/redo03.log is missing
8300 HIGH OPEN 31-JUL-22 Online log member /oradata/ORCL/redo02.log is missing
8294 HIGH OPEN 31-JUL-22 Online log member /oradata/ORCL/redo01.log is missing
清空 redo日志:
SYS@orcl>set linesize 200;
SYS@orcl>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ----------
1 1 15 209715200 512 1 YES INACTIVE 4349770 31-JUL-22 4352046 31-JUL-22 0
3 1 16 209715200 512 1 YES INACTIVE 4352046 31-JUL-22 4352054 31-JUL-22 0
2 1 17 209715200 512 1 NO CURRENT 4352054 31-JUL-22 1.8447E+19 0
SYS@orcl>alter database clear logfile group 1;
Database altered.
SYS@orcl>alter database clear logfile group 2;
alter database clear logfile group 2
ERROR at line 1:
ORA-00350: log 2 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/oradata/ORCL/redo02.log'
SYS@orcl>alter database clear logfile group 3;
Database altered.
尝试使用 clear unarchived logfile方式清空;
SYS@orcl>alter database clear unarchived logfile group 2;
Database altered.
启动主库:
SYS@orcl>alter database open;
Database altered.
SYS@orcl>alter pluggable database all open;
Pluggable database altered.
SYS@orcl>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SYS@orcl>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Next log sequence to archive 19
Current log sequence 19
SCOTT@pdb1>select * from it;
ID
----------
1
2
100
1
2
100
1
2
100
1
2
ID
----------
100
999
222
888
1111
16 rows selected.
5、检查备库数据同步状态
SYS@orcl>select process,sequence#,status from v$managed_standby;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 0 CONNECTED
DGRD 0 ALLOCATED
DGRD 0 ALLOCATED
ARCH 18 CLOSING
ARCH 0 CONNECTED
ARCH 0 CONNECTED
RFS 0 IDLE
RFS 19 IDLE
RFS 0 IDLE
MRP0 17 APPLYING_LOG
10 rows selected.
SYS@orcl>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 0
Current log sequence 17
SCOTT@pdb1>select * from it;
ID
1
2
100
1
2
100
1
2
100
1
2
ID
----------
100
999
222
888
15 rows selected.
从现象来看,主库数据没有同步过来!!! 主库日志日志断档 -缺少 17号日志,造成数据无法同步;
[oracle@db1 2022_07_31]$ ll -lrth
-rw-r----- 1 oracle oinstall 153K Jul 31 06:50 o1_mf_1_21_kgcfgfxs_.arc
-rw-r----- 1 oracle oinstall 1.0K Jul 31 06:50 o1_mf_1_22_kgcfgfz0_.arc
-rw-r----- 1 oracle oinstall 4.4M Jul 31 08:29 o1_mf_1_23_kgcm728w_.arc
-rw-r----- 1 oracle oinstall 1.0K Jul 31 08:29 o1_mf_1_1_kgcm72fl_.arc
-rw-r----- 1 oracle oinstall 11M Jul 31 09:09 o1_mf_1_2_kgcokkpm_.arc
-rw-r----- 1 oracle oinstall 1.5K Jul 31 09:09 o1_mf_1_3_kgcokl7z_.arc
-rw-r----- 1 oracle oinstall 2.0M Jul 31 09:26 o1_mf_1_4_kgcpkwqk_.arc
-rw-r----- 1 oracle oinstall 1.0K Jul 31 09:26 o1_mf_1_5_kgcpkws2_.arc
-rw-r----- 1 oracle oinstall 241K Jul 31 09:32 o1_mf_1_6_kgcpy08y_.arc
-rw-r----- 1 oracle oinstall 1.0K Jul 31 09:33 o1_mf_1_7_kgcpz1ot_.arc
-rw-r----- 1 oracle oinstall 19K Jul 31 10:17 o1_mf_1_9_kgcsl61c_.arc
-rw-r----- 1 oracle oinstall 31K Jul 31 10:21 o1_mf_1_10_kgcsrqq2_.arc
-rw-r----- 1 oracle oinstall 1.9M Jul 31 10:21 o1_mf_1_11_kgcssn3l_.arc
-rw-r----- 1 oracle oinstall 249K Jul 31 10:30 o1_mf_1_12_kgct980r_.arc
-rw-r----- 1 oracle oinstall 179K Jul 31 10:34 o1_mf_1_13_kgctlkfr_.arc
-rw-r----- 1 oracle oinstall 12K Jul 31 10:35 o1_mf_1_14_kgctm6hq_.arc
-rw-r----- 1 oracle oinstall 730K Jul 31 10:45 o1_mf_1_15_kgcv6x9d_.arc
-rw-r----- 1 oracle oinstall 1.0K Jul 31 10:45 o1_mf_1_16_kgcv6xbo_.arc
-rw-r----- 1 oracle oinstall 1.0K Jul 31 11:31 o1_mf_1_18_kgcxx9lr_.arc
[oracle@db2 2022_07_31]$ ls -lrth
total 5.3M
-rw-r----- 1 oracle oinstall 1.5K Jul 31 09:09 o1_mf_1_3_kgcokl49_.arc
-rw-r----- 1 oracle oinstall 2.0M Jul 31 09:26 o1_mf_1_4_kgcpkwlv_.arc
-rw-r----- 1 oracle oinstall 1.0K Jul 31 09:26 o1_mf_1_5_kgcpkwo9_.arc
-rw-r----- 1 oracle oinstall 241K Jul 31 09:34 o1_mf_1_6_kgcq18bj_.arc
-rw-r----- 1 oracle oinstall 1.0K Jul 31 09:34 o1_mf_1_7_kgcq1bwz_.arc
-rw-r----- 1 oracle oinstall 19K Jul 31 10:17 o1_mf_1_9_kgcsl883_.arc
-rw-r----- 1 oracle oinstall 31K Jul 31 10:21 o1_mf_1_10_kgcsrt2x_.arc
-rw-r----- 1 oracle oinstall 1.9M Jul 31 10:21 o1_mf_1_11_kgcssn72_.arc
-rw-r----- 1 oracle oinstall 249K Jul 31 10:30 o1_mf_1_12_kgct983p_.arc
-rw-r----- 1 oracle oinstall 179K Jul 31 10:34 o1_mf_1_13_kgctlkjw_.arc
-rw-r----- 1 oracle oinstall 12K Jul 31 10:35 o1_mf_1_14_kgctm6ml_.arc
-rw-r----- 1 oracle oinstall 1.0K Jul 31 10:45 o1_mf_1_16_kgcv6xco_.arc
-rw-r----- 1 oracle oinstall 730K Jul 31 10:45 o1_mf_1_15_kgcv6xdh_.arc
-rw-r----- 1 oracle oinstall 1.0K Jul 31 11:31 o1_mf_1_18_kgcxxcx9_.arc
7、恢复备库日志断档,使主备数据同步!
方法有多种:
第一种:使用 duplicate 复制在线库;
第二种:采用主库创建备库控制文件,使用全备份主库,二者一起恢复备库;(数据量大)
第三种:采用主库创建备库控制文件,使用增量备份主库,二者一起恢复备库。(数据量较少)
下面第三种方式(主备同步):
开始增量备份主库:
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup as compressed backupset incremental level 1 cumulative database format '/home/oracle/backup3/full_db_level1_%U_%T.bak';
sql 'alter system archive log current';
backup archivelog all not backed up 1 times format '/home/oracle/backup3/arh_level1_%U_%T.bak';
backup current controlfile format '/home/oracle/backup3/ctl_level1_%U_%T.bak';
backup spfile format '/home/oracle/backup3/spfile_level1_%U_%T.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
为备库备份控制文件:
[oracle@db1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 31 15:04:27 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@orcl>alter database create standby controlfile as '/home/oracle/standby01.ctl';
Database altered.
增量备份 +控制文件一起复制到备库:
[oracle@db1 ~]$ scp -r backup3 oracle@db2:/home/oracle
启动备库到 nomount状态:
SYS@orcl>startup nomount;
ORACLE instance started.
Total System Global Area 2466250400 bytes
Fixed Size 9137824 bytes
Variable Size 553648128 bytes
Database Buffers 1895825408 bytes
Redo Buffers 7639040 bytes
恢复控制文件:
RMAN> restore controlfile from '/home/oracle/backup3/standby01.ctl';
Starting restore at 31-JUL-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=379 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/oradata/ORCLDG/control01.ctl
output file name=/flash_recovery_area/ORCLDG/control02.ctl
Finished restore at 31-JUL-22
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
注册恢复目录:
RMAN> catalog start with '/home/oracle/backup3/';
恢复备库:
RMAN> recover database;
Starting recover at 31-JUL-22
using channel ORA_DISK_1
RMAN-00571: =======================================
RMAN-00569: ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/31/2022 15:10:40
RMAN-06094: datafile 86 must be restored
RMAN> restore datafile 86;
Starting restore at 31-JUL-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00086 to /oradata/ORCL/data111.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup3/full_db_level1_h4140ct6_1_1_20220731.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup3/full_db_level1_h4140ct6_1_1_20220731.bak tag=TAG20220731T150302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 31-JUL-22
RMAN> recover database;
Starting recover at 31-JUL-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /oradata/ORCL/undotbs01.dbf
destination for restore of datafile 00085: /oradata/ORCL/data01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup3/full_db_level1_h5140ct6_1_1_20220731.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup3/full_db_level1_h5140ct6_1_1_20220731.bak tag=TAG20220731T150302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00075: /oradata/ORCL/pdb1/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup3/full_db_level1_h6140ct6_1_1_20220731.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup3/full_db_level1_h6140ct6_1_1_20220731.bak tag=TAG20220731T150302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oradata/ORCL/system01.dbf
destination for restore of datafile 00007: /oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup3/full_db_level1_h3140ct6_1_1_20220731.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup3/full_db_level1_h3140ct6_1_1_20220731.bak tag=TAG20220731T150302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup3/full_db_level1_h4140ct6_1_1_20220731.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup3/full_db_level1_h4140ct6_1_1_20220731.bak tag=TAG20220731T150302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00074: /oradata/ORCL/pdb1/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup3/full_db_level1_h8140ctb_1_1_20220731.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup3/full_db_level1_h8140ctb_1_1_20220731.bak tag=TAG20220731T150302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00076: /oradata/ORCL/pdb1/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup3/full_db_level1_hb140cte_1_1_20220731.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup3/full_db_level1_hb140cte_1_1_20220731.bak tag=TAG20220731T150302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00081: /oradata/ORCL/pdb1/data11.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup3/full_db_level1_hc140ctf_1_1_20220731.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup3/full_db_level1_hc140ctf_1_1_20220731.bak tag=TAG20220731T150302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /flash_recovery_area/ORCLDG/archivelog/2022_07_31/o1_mf_1_19_kgdb9m0n_.arc
archived log for thread 1 with sequence 20 is already on disk as file /flash_recovery_area/ORCLDG/archivelog/2022_07_31/o1_mf_1_20_kgdb9mbm_.arc
archived log file name=/flash_recovery_area/ORCLDG/archivelog/2022_07_31/o1_mf_1_19_kgdb9m0n_.arc thread=1 sequence=19
archived log file name=/flash_recovery_area/ORCLDG/archivelog/2022_07_31/o1_mf_1_20_kgdb9mbm_.arc thread=1 sequence=20
media recovery complete, elapsed time: 00:00:00
Finished recover at 31-JUL-22
启动备库:
RMAN> alter database open;
Statement processed
备库同步主库数据:
[oracle@db2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 31 15:13:14 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@orcl>@1
Database altered.
检查备库日志:(日志序列 21已经同步到备库了,主库下一个日志序列是 21)
2022-07-31T15:13:20.717299+08:00
TT02 (PID:48672): Waiting for all non-current ORLs to be archived
2022-07-31T15:13:20.717411+08:00
TT02 (PID:48672): All non-current ORLs have been archived
2022-07-31T15:13:20.867609+08:00
PR00 (PID:48662): Media Recovery Waiting for T-1.S-21 (in transit)
2022-07-31T15:13:20.868152+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 21 Reading mem 0
Mem# 0: /oradata/ORCL/standby01.log
2022-07-31T15:13:21.492542+08:00
Completed: alter database recover managed standby database using current logfile disconnect from session
主库:
[oracle@db1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 31 15:14:49 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@orcl>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 19
Next log sequence to archive 21
Current log sequence 21
开启 pdb1库;
[oracle@db2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 31 15:13:14 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@orcl>@1
Database altered.
( ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;)
SYS@orcl>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SYS@orcl>alter pluggable database pdb1 open;
Pluggable database altered.
SYS@orcl>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
检查主备库数据一致性:
主库:
SCOTT@pdb1>select count(*) from it;
COUNT(*)
----------
16
备库:
SCOTT@pdb1>select count(*) from it;
COUNT(*)
----------
16
总结:主备数据不同步可能有多种原因,一般过程如下: 1、数据同步语句是否开启 2、主备应用日志是否相同,是否存在日志缺失 3、备库 alter文件是否报错,具体问题再来解决。
Yicheng16
22.07.31
-- The End --
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/69963467/viewspace-2908333/ ,如需转载,请注明出处,否则将追究法律责任。