74.ORA-16191问题修复&基于SCN增量修复DG

1.检查主库发现:

select dest_name,status,error from v$archive_dest;
LOG_ARCHIVE_DEST_2 ERROR:ORA-16191: ??????????????????

密码文件的DM5码不一致。

从库的ALERT日志

alert_ctpricedg.log 警告日志为。
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 23315-23315
 DBID 1228473367 branch 1080828633
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that''s sufficiently large
enough to maintain adequate log switch information to resolve

2.修复方法。

将主库的密码文件复制到备库。

oracle修改sys用户的密码时会修改密码文件。主库修改sys用户的密码，
必须将该密码文件复制到备库，并修改为和备库的实例名称相匹配。
从库的密码文件与主库密码文件的DM5码不同，1月20日凌晨出现无法拉去主库的日志。
导致不同步。一般需要让主库和从使用相同的密码文件,这样DM5码就相同了。

3.使用增量备份恢复的方法修复从库。

--查看备份的起点:最小的SCN;

--从库SCN查看
SQL>  select to_char(current_scn) scn from v$database;
SCN
--------------------------------------------------------------------------------
2849888456
SQL>  select min(fhscn) from x$kcvfh;
MIN(FHSCN)
------------------------------------------------
2849888457
SQL> select distinct to_char(checkpoint_change#) from v$datafile_header order by 1; 

TO_CHAR(CHECKPOINT_CHANGE#)
--------------------------------------------------------------------------------
2849888457

--以归档日志查询相关scn/实际是23315号日志没传输过来。
select THREAD#,min(sequence#) from v$archived_log where applied='NO' group by THREAD#;  
  THREAD# MIN(SEQUENCE#)
---------- --------------
	 1	    23316

col fc for a30 
SELECT thread#,SEQUENCE#,to_char(FIRST_CHANGE#) fc,to_char(NEXT_CHANGE#) nc 
FROM v$archived_log WHERE SEQUENCE# = 23316 ORDER BY 1,2; 

SQL>
   THREAD#  SEQUENCE# FC			     NC
---------- ---------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------
	 1	23316 2849888465		     2849893823

3.主库端基于scn的增量备份

(1) --SCN是前面查询得到的。 
 --取最小的SCN:2849888456
 RMAN> BACKUP INCREMENTAL FROM SCN 2849888456 DATABASE FORMAT '/u01/backup/ForStandby_%U' tag 'FORSTANDBY' ;
Starting backup at 25-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=587 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/CTPRICE/datafile/rulechtic.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/CTPRICE/datafile/autoprice01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/CTPRICE/datafile/autoprice02.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/CTPRICE/datafile/autoprice04.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/CTPRICE/datafile/autoprice03.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/CTPRICE/datafile/rulechtic01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/CTPRICE/datafile/o1_mf_undotbs1_jkpnobys_.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/CTPRICE/datafile/o1_mf_system_jkpnoby6_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/CTPRICE/datafile/o1_mf_sysaux_jkpnobyp_.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/CTPRICE/datafile/monitor.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/CTPRICE/datafile/o1_mf_users_jkpnobz5_.dbf
channel ORA_DISK_1: starting piece 1 at 25-JAN-24
channel ORA_DISK_1: finished piece 1 at 25-JAN-24
piece handle=/u01/backup/ForStandby_r42hflrj_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:10:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 25-JAN-24
channel ORA_DISK_1: finished piece 1 at 25-JAN-24
piece handle=/u01/backup/ForStandby_r52hfmfe_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JAN-24


--(2)备份控制文件。
ALTER DATABASE CREATE standby controlfile AS '/u01/backup/standby.ctl';  

 
--(3).传输文件到dg端:
控制文件传输到备库。
备份文件传输到备库:/chac/backup 目录。

--(4).恢复控制文件，恢复数据库
shu immediate; 
startup nomount; 

--(5)备份控制文件。
mv o1_mf_lqj88304_.ctl o1_mf_lqj88304_.ctl.20240125

--(6)恢复新的控制文件。
RMAN> restore controlfile from '/chac/backup/standby.ctl';

Starting restore at 25-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=576 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/chac/app/oracle/fast_recovery_area/CTPRICEDG/controlfile/o1_mf_lqj88304_.ctl
Finished restore at 25-JAN-24

--(7)挂载数据库。
SQL> alter database mount;
Database altered.
  
--(8)主从备份的文件。
rman>catalog start with '/chac/backup';

RMAN> catalog start with '/chac/backup';

Starting implicit crosscheck backup at 25-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=769 device type=DISK
Crosschecked 91 objects
Finished implicit crosscheck backup at 25-JAN-24

Starting implicit crosscheck copy at 25-JAN-24
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JAN-24

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23632_lv3pndbf_.arc
File Name: /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23633_lv3pp614_.arc
File Name: /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23634_lv3q1wyr_.arc
File Name: /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23635_lv3s30t4_.arc
File Name: /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23636_lv3tlf9k_.arc
File Name: /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23637_lv3wk5qq_.arc
File Name: /chac/app/oracle/fast_recovery_area/CTPRICEDG/controlfile/o1_mf_lqj88304_.ctl.20240125

searching for all files that match the pattern /chac/backup

List of Files Unknown to the Database
=====================================
File Name: /chac/backup/standby.ctl
File Name: /chac/backup/ForStandby_r42hflrj_1_1
File Name: /chac/backup/ForStandby_r52hfmfe_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /chac/backup/standby.ctl
File Name: /chac/backup/ForStandby_r42hflrj_1_1
File Name: /chac/backup/ForStandby_r52hfmfe_1_1

--(9)恢复数据库
RMAN> RECOVER DATABASE NOREDO;

Starting recover at 25-JAN-24
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 00001: /chac/app/oracle/oradata/ctpricedg/datafile/o1_mf_system_jkpnoby6_.dbf
destination for restore of datafile 00002: /chac/app/oracle/oradata/ctpricedg/datafile/o1_mf_sysaux_jkpnobyp_.dbf
destination for restore of datafile 00003: /chac/app/oracle/oradata/ctpricedg/datafile/o1_mf_undotbs1_jkpnobys_.dbf
destination for restore of datafile 00004: /chac/app/oracle/oradata/ctpricedg/datafile/o1_mf_users_jkpnobz5_.dbf
destination for restore of datafile 00005: /chac/app/oracle/oradata/ctpricedg/datafile/autoprice01.dbf
destination for restore of datafile 00006: /chac/app/oracle/oradata/ctpricedg/datafile/autoprice02.dbf
destination for restore of datafile 00007: /chac/app/oracle/oradata/ctpricedg/datafile/autoprice03.dbf
destination for restore of datafile 00008: /chac/app/oracle/oradata/ctpricedg/datafile/autoprice04.dbf
destination for restore of datafile 00009: /chac/app/oracle/oradata/ctpricedg/datafile/rulechtic.dbf
destination for restore of datafile 00010: /chac/app/oracle/oradata/ctpricedg/datafile/monitor.dbf
destination for restore of datafile 00011: /chac/app/oracle/oradata/ctpricedg/datafile/rulechtic01.dbf
channel ORA_DISK_1: reading from backup piece /chac/backup/ForStandby_r42hflrj_1_1
channel ORA_DISK_1: piece handle=/chac/backup/ForStandby_r42hflrj_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:35

Finished recover at 25-JAN-24

4.启动同步进程，检查同步状态等。

--(1)闪回区修改稍微大点,备库闪回区太小
SQL> alter system set db_recovery_file_dest_size=30g;
System altered.

--(2)开启同步。
SQL> alter database recover managed standby database disconnect;
Database altered.

--查看alter日志,表示同步。
Deleted Oracle managed file /chac/app/oracle/oradata/ctpricedg/onlinelog/o1_mf_3_jkpnvt11_.log
Clearing online redo logfile 3 complete
Media Recovery Log /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23637_lv3wk5qq_.arc
Media Recovery Log /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23638_lv3x1k50_.arc
Media Recovery Log /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23639_lv3y2x18_.arc
Media Recovery Log /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23640_lv3y6ymd_.arc
Media Recovery Log /chac/app/oracle/fast_recovery_area/CTPRICEDG/archivelog/2024_01_25/o1_mf_1_23641_lv3y70gd_.arc
Media Recovery Waiting for thread 1 sequence 23642 (in transit)

--(3)开启数据库/开启实时同步。
SQL> alter database recover managed standby database cancel; 
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.

--(4)从库检查 
SQL> select status,process,thread#,sequence# from v$managed_standby; 
SQL> /
STATUS				     PROCESS			    THREAD#  SEQUENCE#
------------------------------------ --------------------------- ---------- ----------
CLOSING 			     ARCH				  1	 23641
CLOSING 			     ARCH				  1	 23638
CONNECTED			     ARCH				  0	     0
CLOSING 			     ARCH				  1	 23640
IDLE				     RFS				  0	     0
IDLE				     RFS				  0	     0
IDLE				     RFS				  0	     0
IDLE				     RFS				  1	 23642
APPLYING_LOG			 MRP0				  1	 23642

5.总结

基于SCN的增量修复是快速修复DG的有效方法，其底层原理是基于块级别的复制，类似增量备份，然后量部分内容进行合并。