DG常见故障处理汇总
https://blog.51cto.com/u_13482808/6649862
1. 场景一 Primary DB的归档日志存在,但出现GAP场景
Oracle 11G & 19C Standby DB 可通过配置 Fetch Archive lo(FAL) 参数
【即:FAL_SERVER:用来指定出现GAP的时候重哪里取归档日志。】
Standby DB 会通过Fal_server参数,主动从Priary DB取归档日志,并自动解决归档GAP问题。
主库查看DG同步是否报错
set linesize 200;
col destination for a40;
select dest_id, destination, status, error, fail_date from v$archive_dest;
1.1针对Primary archivelog 存在场景
(日常需在Primary Parameter配置以下参数)
– Primary DB
DB Parameter:
1、LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(PRIMARY,STANDBY)’ --(必需)
2、LOG_ARCHIVE_DEST_2=‘SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY’
3、LOG_ARCHIVE_DEST_STATE_2=ENABLE --(必需)
alter system set fal_server= STANDBY sid = '*';
alter system set fal_client= PRIMARY sid = '*';
RMAN Parameter:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; -- 备库应用才删除归档
– Standby DB
DB Parameter:
1、LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(PRIMARY,STANDBY)’ --(必需)
2、FAL_SERVER=‘PRIMARY’ --(必需)
*.db_unique_name
*.standby_file_management='AUTO'
alter system set db_file_name_convert='/oracle/app/oradata/zydb','/oracle/app/oradata/zydb' scope=spfile sid='*';
alter system set log_file_name_convert='/oracle/app/oradata/zydb','/oracle/app/oradata/zydb' scope=spfile sid='*';
RMAN Parameter:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
1.2 针对Primary archivelog 存在场景(手工恢复)
– 手工解决日志GAP(日志间隙)
1)、确定DATAGUARD ARCHIVELOG GAP 信息
在主库通过查询V$ARCHIVE_GAP视图,确定GAP的归档日志
SELECT * FROM V$ARCHIVE_GAP;
2)、取消备库的归档日志恢复应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3)、确定归档日志,传输备库
scp xxx_1_2_1087819724.arc oracle@standbydb:/dbfile/archive/xxxx/.
4)、手工注册ARCHIVELOG
alter database register logfile ‘/dbfile/archive/XXXX/xxx_1_2_1087819724.arc’;
5)、启动备库归档日志应用
–备端启动恢复进程mrp0
alter database recover managed standby database using current logfile disconnect from session;
6)、查看Standby DB的警告日志
2.场景二 Primary DB主库归档日志丢失,出现GAP场景
2.1 若主库归档日志丢失
2.1.1 Oracle 11G版本,GAP处理步骤
场景介绍:
1)主库不小心删除归档,而这时归档还没传递到备库,因此出现GAP场景。
2)主库由于数据变更生成大量的归档,而备库不能及时应用,可能会导致延迟异常严重场景。
3)主库的归档文件遭到破坏,导致备库不能应用归档,因而出GAP场景。
步骤如下:
请严格按照以下步骤来实施:
1)停止备库的日志传输进程:
alter database recover managed standby database cancel;
2)查出备库的最小SCN号:
select CURRENT_SCN from V$database;
3)在主库做基于SCN物理增量备份:
BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT ‘/tmp/RMANBAK/ForStandby_%U’ tag ‘FORSTANDBY’;
4)将主库的增量备份文件传输到备库
scp -r /tmp/RMANBAK/ oracle@standby:/tmp/.
5)在备库注册增量备份集
RMAN> CATALOG START WITH ‘/tmp/RMANBAK/’;
6)在备库恢复增量备份集
RMAN> RECOVER DATABASE NOREDO;
7)主库创建standby控制文件
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT ‘/tmp/RMANBAK/ForStandbyCTRL.bck’;
8)从主库库拷贝standby控制文件到备库
scp /tmp/RMANBAK/ForStandbyCTRL.bck oracle@standby:/tmp/RMANBAK/.
9)检查主备库数据文件信息
#获取备库的数据文件信息,检查主备库的数据文件是否比配(包括路径和名称)
set lines 200 pagesi 1000
col name format a60
select file#, name from v$datafile order by file# ;
10)在备库恢复从主库拷贝过来的standby控制文件
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM ‘/tmp/ForStandbyCTRL.bck’;
11)备库关闭后启动到mount状态
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
--12)配置备库flashback
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
--13)#在备库清除所有的standby redo log
select 'ALTER DATABASE CLEAR LOGFILE GROUP ’ || group# || ‘;’ from V$logfile;
14)修改数据文件路径
catalog start with '/zktadata/ktadb/'
switch database to copy;
15)追加完成,启用日志恢复
RECOVER MANAGED STANDBY DATABASE using CURRENT LOGFILE DISCONNECT;
2.1.2 Oracle 19C版本,GAP处理步骤 -- 12C以上推荐
1)取消备库的归档日志恢复应用
alter database recover managed standby database cancel;
2)将备库启动到MOUNT状态
startup mount force;
3)启动基于网络直接恢复
(RECOVER STANDBY DATABASE FROM SERVICE)
recover database from service primary_db noredo section SIZE 1G USING COMPRESSED BACKUPSET;
或者:
run {
allocate channel c1 type disk connect ‘/@standbydb’;
allocate channel c2 type disk connect ‘/@standbydb’;
allocate channel c3 type disk connect ‘/@standbydb’;
allocate channel c4 type disk connect ‘/@standbydb’;
allocate channel c5 type disk connect ‘/@standbydb’;
recover standby database from service ‘standbydb’;
}
4)启动备库,恢复数据同步
alter database recover managed standby database disconnect from session;