文章目录
Oracle 11/19C DataGuard场景 使用Rman增量恢复丢失的归档 登录备库查看Archive Gap情况，归档日志从编号5955开始就无法往下应用到备库。 登录备库，备库查询最小SCN(6种方式里选最小)，以最小SCN在主库进行增量备份。 备库停止日志应用，并关闭数据库。 主库 查询主备GAP期间是否新增数据文件。 基于异常的scn时间点，对主库进行一次手动增量备份，然后将增量备份恢复至备库。 备库恢复控制文件 备库上进行恢复，先将主库的增量备份文件注册到控制文件。 备库启动ADG mgr 同步情况检查，调整相关参数并开启数据库同步。
1 、检查主备日志差异数量及日志延迟情况。
SELECT THREAD
THREAD
1 5955 5971
2 6043 6058
2 、备库检查主从同步日志差异
SELECT ARCH. THREAD
FROM
( SELECT THREAD
( SELECT THREAD
WHERE
ARCH. THREAD
ORDER BY 1 ;
Thread Last in Sequence Last Applied Sequence Difference
1 5971 5955 16
2 6058 6043 15
3 、登录备库，检查应用的归档日志是否已传输到备库（备库）
set linesize 10000
col name for a80
select name, BACKUP_COUNT, DELETED from gv$archived_log
where thread
and name <> 'standby_db_name_' order by SEQUENCE
SELECT to_char( CURRENT_SCN) CURRENT_SCN FROM GV$DATABASE ;
CURRENT_SCN
38356313018
select min ( fhscn) from x$kcvfh;
MIN ( FHSCN)
38356313019
select min ( kc. fhscn) from x$kcvfh kc, v$datafile dd where kc. hxfil = dd. file
MIN ( KC. FHSCN)
select min ( kc. fhscn) from x$kcvfh kc, v$datafile dd where kc. hxfil = dd. file
MIN ( KC. FHSCN)
38356313019
select distinct to_char( checkpoint_change
TO_CHAR( CHECKPOINT_CHANGE
38356313019
select distinct to_char( checkpoint_change
TO_CHAR( CHECKPOINT_CHANGE
38356313019
alter database recover managed standby database cancel;
srvctl stop database - d db_name
select file# from v$datafile where creation_change# > =38356313018;
1 、主库执行增量备份
run
{
allocate channel ch00 type disk ;
allocate channel ch01 type disk ;
backup incremental from scn 38356313018 database format '/backup/rman/db_name_%U' ;
release channel ch00;
release channel ch01;
}
2 、传输备份文件到目标端
scp db_name_* 192.168 .1 .196 :/ backup / rman/ db_name_*
3 、 主库上创建standby controlfile文件并传输到备库。
alter database create standby controlfile as '/backup/rman/control_db_name_.ctl' ;
scp * root@192.168.1.196 :/ backup / rman/
4 、执行select file_id, file_name from dba_data_files语句，备份数据文件名称。
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/backup/rman/control_db_name_.ctl' ;
输出日志如下：
Starting restore at 19 - SEP- 2023 12 :53 :18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID= 71 instance= db_name_2 device type = DISK
channel ORA_DISK_1: copied control file copy
output file name= + db_name_/ db_name_/ CONTROLFILE/ current .256 .1066861809
Finished restore at 19 - SEP- 2023 12 :53 :19
RMAN> alter database mount standby database ;
RMAN> report schema ;
输出日志如下：
List of Permanent Datafiles
= = = = = = = = = = = = = = = = = = = = = = = = = = =
File Size( MB) Tablespace RB segs Datafile Name
1 0 SYSTEM * * * + db_name_/ db_name_/ datafile/ system. 261.1045757579
2 0 SYSAUX * * * + db_name_/ db_name_/ datafile/ sysaux. 262.1045757581
3 0 UNDOTBS1 * * * + db_name_/ db_name_/ datafile/ undotbs1. 263.1045757583
4 0 UNDOTBS2 * * * + db_name_/ db_name_/ datafile/ undotbs2. 265.1045757589
5 0 USERS * * * + db_name_/ db_name_/ datafile/ users. 266.1045757591
RMAN> CATALOG START WITH '/backup/rman/' ;
输出日志：
searching for all files that match the pattern / backup / rman/ db_name_
List of Files Unknown to the Database
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
File Name: / backup / rman/ db_name_/ db_name_nn26or7j_1_1
File Name: / backup / rman/ db_name_/ db_name_np26or7n_1_1
File Name: / backup / rman/ db_name_/ db_name_no26or7j_1_1
File Name: / backup / rman/ db_name_/ control_db_name_. ctl
Do you really want to catalog the above files ( enter YES or NO ) ? yes
cataloging files. . .
cataloging done
List of Cataloged Files
= = = = = = = = = = = = = = = = = = = = = = =
File Name: / backup / rman/ db_name_/ db_name_nn26or7j_1_1
File Name: / backup / rman/ db_name_/ db_name_np26or7n_1_1
File Name: / backup / rman/ db_name_/ db_name_no26or7j_1_1
File Name: / backup / rman/ db_name_/ control_db_name_. ctl
1 、由于主备数据文件名称不一样，调整参数以便后面调整数据文件名。
alter system set standby_file_management= MANUAL;
2 、查看数据文件与控制文件头SCN信息，发现数据文件头的信息无法识别出来。
SQL > select file
FILE
1 38359206434 2023 - 09 - 19 12 :47 :48
2 38359206434 2023 - 09 - 19 12 :47 :48
3 38359206429 2023 - 09 - 19 12 :47 :47
4 38359206429 2023 - 09 - 19 12 :47 :47
5 38359206434 2023 - 09 - 19 12 :47 :48
6 38359206434 2023 - 09 - 19 12 :47 :48
7 38359206429 2023 - 09 - 19 12 :47 :47
8 38359206434 2023 - 09 - 19 12 :47 :48
9 38359206429 2023 - 09 - 19 12 :47 :47
10 38359206434 2023 - 09 - 19 12 :47 :48
11 38359206429 2023 - 09 - 19 12 :47 :47
12 38359206429 2023 - 09 - 19 12 :47 :47
13 38359206434 2023 - 09 - 19 12 :47 :48
SQL > select file
FILE
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 38356313019 2023 - 09 - 16 10 :02 :24
12 0
13 0
3 、可以看到数据文件头的信息与控制文件不匹配。通过rename 将控制文件里的数据文件名调整为实际的文件名。
ALTER DATABASE RENAME FILE '+db_name_/db_name_/datafile/system.261.1045757579' to '+db_name_/db_name_/DATAFILE/system.264.1066862341' ;
ALTER DATABASE RENAME FILE '+db_name_/db_name_/datafile/sysaux.262.1045757581' to '+db_name_/db_name_/DATAFILE/sysaux.262.1066862315' ;
ALTER DATABASE RENAME FILE '+db_name_/db_name_/datafile/undotbs1.263.1045757583' to '+db_name_/db_name_/DATAFILE/undotbs1.265.1066862341' ;
ALTER DATABASE RENAME FILE '+db_name_/db_name_/datafile/users.266.1045757591' to '+db_name_/db_name_/DATAFILE/users.269.1066862371' ;
4 、alter system set standby_file_management= auto;
NAME FILE
/ backup / oradata/ PROD/ datafile/ tbs_ogg55. dbf 557
/ backup / oradata/ PROD/ datafile/ tbs_ogg56. dbf 558
/ backup / oradata/ PROD/ datafile/ a_txn_data205. dbf 559
/ backup / oradata/ PROD/ datafile/ a_txn_data206. dbf 560
/ backup / oradata/ PROD/ datafile/ a_txn_ind147. dbf 561
/ backup / oradata/ PROD/ datafile/ a_txn_ind148. dbf 562
/ backup / oradata/ PROD/ datafile/ cux_data49. dbf 563
/ backup / oradata/ PROD/ datafile/ cux_data50. dbf 564
SQL > select name, file
5 、执行恢复
run{
allocate channel c1 type disk ;
allocate channel c2 type disk ;
backup datafile 557 , 558 , 559 , 560 , 561 , 562 , 563 , 564 format '/backup/rman/db_name_datafile_%U' ;
release channel c1;
release channel c2;
}
CATALOG START WITH '/backup/rman/' ;
run{
set newname for datafile 557 to '/backup/oradata/PROD/datafile/tbs_ogg55.dbf' ;
set newname for datafile 558 to '/backup/oradata/PROD/datafile/tbs_ogg56.dbf' ;
set newname for datafile 559 to '/backup/oradata/PROD/datafile/a_txn_data205.dbf' ;
set newname for datafile 560 to '/backup/oradata/PROD/datafile/a_txn_data206.dbf' ;
set newname for datafile 561 to '/backup/oradata/PROD/datafile/a_txn_ind147.dbf' ;
set newname for datafile 562 to '/backup/oradata/PROD/datafile/a_txn_ind148.dbf' ;
set newname for datafile 563 to '/backup/oradata/PROD/datafile/cux_data49.dbf' ;
set newname for datafile 564 to '/backup/oradata/PROD/datafile/cux_data50.dbf' ;
restore datafile 557 , 558 , 559 , 560 , 561 , 562 , 563 , 564 ;
}
6 、进行增量恢复，备库重新接收并应用日志。
run
{
allocate channel ch00 type disk ;
allocate channel ch01 type disk ;
allocate channel ch02 type disk ;
RECOVER DATABASE ;
release channel ch00;
release channel ch01;
release channel ch02;
}
数据库mount状态下应用归档：
alter database recover managed standby database disconnect from session ;
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session ;
1 、同步情况检查
select t. NAME, t. SWITCHOVER_STATUS, t. OPEN_MODE, t. DATABASE_ROLE from v$database t;
select thread
from v$archived_log where resetlogs_id = ( SELECT resetlogs_id FROM v$database_incarnation
WHERE status = 'CURRENT' AND rownum = 1 ) group by thread
select * from v$archive_gap;
2 、调整相关参数
调整相关参数：alter system set standby_file_management= auto;
alter system set standby_file_management= auto;
3 、关库并开库
shutdown immediate
srvctl start database - d db_name
4 、备库重新接收并应用日志
SQL > alter database recover managed standby database using current logfile disconnect from session ;
alter database recover managed standby database disconnect from session ;
5 、同步情况检查
select t. NAME, t. SWITCHOVER_STATUS, t. OPEN_MODE, t. DATABASE_ROLE from v$database t;
NAME SWITCHOVER_STATUS OPEN_MODE DATABASE_ROLE
db_name_ NOT ALLOWED READ ONLY WITH APPLY PHYSICAL STANDBY
select thread
SYS@db_name_2 >
THREAD
1 5977 5976
2 6064 6064
6 、查看同步状态：
select inst_id, to_char( START_TIME, 'dd.mm.yyyy hh24:mi:ss' ) "Recover_start" ,
to_char( item) || ' = ' || to_char( sofar) || ' ' || to_char( units) || ' ' ||
to_char( TIMESTAMP , 'dd.mm.yyyy hh24:mi' ) "Values"
from gv$recovery_progress
where start_time = ( select max ( start_time) from gv$recovery_progress) ;
INST_ID Recover_start Values
2 14.09 .2024 11 :25 :19 Log Files = 14248 Files
2 14.09 .2024 11 :25 :19 Active Apply Rate = 1440 KB/ sec
2 14.09 .2024 11 :25 :19 Average Apply Rate = 1732 KB/ sec
2 14.09 .2024 11 :25 :19 Maximum Apply Rate = 281005 KB/ sec
2 14.09 .2024 11 :25 :19 Redo Applied = 22100580 Megabytes
2 14.09 .2024 11 :25 :19 Last Applied Redo = 0 SCN+ Time 12.02 .2025 16 :05
2 14.09 .2024 11 :25 :19 Active Time = 5105918 Seconds
2 14.09 .2024 11 :25 :19 Apply Time per Log = 357 Seconds
2 14.09 .2024 11 :25 :19 Checkpoint Time per Log = 1 Seconds
2 14.09 .2024 11 :25 :19 Elapsed Time = 13063186 Seconds
2 14.09 .2024 11 :25 :19 Standby Apply Lag = 1 Seconds
7 、查看延迟信息
set linesize 220 ;
col name for a30;
col value for a20;
select * from gv$dataguard_stats order by 1 , 2 ;
INST_ID NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
-
1 apply finish time day ( 2 ) to second ( 3 ) interval 02 / 12 / 2025 16 :06 :46
1 apply lag day ( 2 ) to second ( 0 ) interval 02 / 12 / 2025 16 :06 :46
1 estimated startup time 63 second 02 / 12 / 2025 16 :06 :46
1 transport lag + 00 00 :00 :00 day ( 2 ) to second ( 0 ) interval 02 / 12 / 2025 16 :06 :46 02 / 12 / 2025 16 :06 :46
2 apply finish time + 00 00 :00 :00.000 day ( 2 ) to second ( 3 ) interval 02 / 12 / 2025 16 :06 :46
2 apply lag + 00 00 :00 :00 day ( 2 ) to second ( 0 ) interval 02 / 12 / 2025 16 :06 :46 02 / 12 / 2025 16 :06 :45
2 estimated startup time 46 second 02 / 12 / 2025 16 :06 :46
2 transport lag + 00 00 :00 :00 day ( 2 ) to second ( 0 ) interval 02 / 12 / 2025 16 :06 :46 02 / 12 / 2025 16 :06 :45
8 、查看延迟信息
select l. thread
from ( select thread
dest_id,
max ( sequence
max ( decode( archived, 'YES' , sequence
max ( decode( applied, 'YES' , sequence
from v$archived_log
group by thread
( select thread
where t. thread
11 order by l. thread
THREAD
1 167079 167078 1 167078 167078 0
2 161949 161948 1 161948 161947 1
2 161949 159636 2 159636 159636 0
