文档课题：oracle运用备库增量备份恢复另一个备库的gap—主库rac，备库文件系统.
环境介绍：主库两节点rac + 11.2.0.4，配置中的两个备库均为单实例dg.
操作系统：主备rhel 7.9
问题描述：rac+dg架构中一个备库出现异常，且在实时应用断开后，主库添加过12个数据文件，而且备库查出的最小scn在系统中无法查出具体时间等信息.由于生产环境不允许在主库进行任何操作，所以需要在另一个正常的备库进行增量备份恢复.
实例名：主库orcl1、orcl2，备库1 orcldg，备库2 sh_orcl
说明：此处模拟sh_orcl备库异常，然后用orcldg备库对其进行恢复.
1、备库sh_orcl参数
SYS@sh_orcl> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(orcl,orcldg,sh_orcl
)
log_archive_dest string
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
DEST valid_for=(all_logfiles,a
ll_roles) db_unique_name=orcld
g
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string service=orcl lgwr async valid_
for=(online_logfiles,primary_r
ole) db_unique_name=orcl
log_archive_dest_20 string
log_archive_dest_21 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_4 string SERVICE=sh_orcl COMPRESSION=EN
ABLE LGWR ASYNC VALID_FOR=(ONL
INE_LOGFILES,PRIMARY_ROLE) DB_
UNIQUE_NAME=sh_orcl
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
2、异常模拟
2.1、删新增归档文件
--shutdown sh_orcl备库.
SYS@sh_orcl> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--主库当前归档日志情况.
ASMCMD> ls
ASMCMD> ls
thread_1_seq_124.321.1133858621
thread_1_seq_125.322.1133858621
thread_1_seq_126.323.1133858627
thread_2_seq_24.320.1133858617
thread_2_seq_25.324.1133858627
--主库产生新归档日志文件.
--节点1操作.
SYS@orcl1> delete from test where rownum<100000;
99999 rows deleted.
SYS@orcl1> delete from test where rownum<100000;
99999 rows deleted.
SYS@orcl1> commit;
Commit complete.
--节点2操作.
SYS@orcl2> delete from test where rownum<10000;
9999 rows deleted.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> delete from test where rownum<100000;
99999 rows deleted.
SYS@orcl2> delete from test where rownum<100000;
99999 rows deleted.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select count(*) from test;
COUNT(*)
----------
1375965
--目前归档文件情况.
ASMCMD> ls
thread_1_seq_124.321.1133858621
thread_1_seq_125.322.1133858621
thread_1_seq_126.323.1133858627
thread_1_seq_127.325.1133861745
thread_1_seq_128.328.1133862097
thread_2_seq_24.320.1133858617
thread_2_seq_25.324.1133858627
thread_2_seq_26.326.1133861953
thread_2_seq_27.327.1133862025
说明：红颜色标出为新增归档日志文件.
--现删除新增归档日志文件.
ASMCMD> rm -rf thread_1_seq_127.325.1133861745
ASMCMD> rm -rf thread_1_seq_128.328.1133862097
ASMCMD> rm -rf thread_2_seq_26.326.1133861953
ASMCMD> rm -rf thread_2_seq_27.327.1133862025
说明：以上归档日志文件未被sh_orcl备库应用.
2.2、新增数据文件
--主库节点1添加12个数据文件.
SYS@orcl1> select file_name,file_id,tablespace_name,bytes/1024/1024,autoextensible from dba_data_files order by 4;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024 AUT
--------------------------------------------- ---------- --------------- --------------- ---
+DATA/orcl/datafile/users.264.1132745567 5 USERS 5 YES
+DATA/orcl/datafile/undotbs2.263.1132745567 4 UNDOTBS2 200 YES
+DATA/orcl/datafile/sysaux.260.1132745551 2 SYSAUX 600 YES
+DATA/orcl/datafile/undotbs1.261.1132745553 3 UNDOTBS1 840 YES
+DATA/orcl/datafile/system.259.1132745549 1 SYSTEM 1060 YES
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> alter tablespace users add datafile '+data' size 50m autoextend on;
Tablespace altered.
SYS@orcl1> select file_name,file_id,tablespace_name,bytes/1024/1024,autoextensible from dba_data_files order by 4;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024 AUT
--------------------------------------------- ---------- --------------- --------------- ---
+DATA/orcl/datafile/users.264.1132745567 5 USERS 5 YES
+DATA/orcl/datafile/users.282.1133862489 9 USERS 50 YES
+DATA/orcl/datafile/users.275.1133862625 16 USERS 50 YES
+DATA/orcl/datafile/users.276.1133862603 15 USERS 50 YES
+DATA/orcl/datafile/users.277.1133862585 14 USERS 50 YES
+DATA/orcl/datafile/users.285.1133862437 6 USERS 50 YES
+DATA/orcl/datafile/users.284.1133862457 7 USERS 50 YES
+DATA/orcl/datafile/users.283.1133862473 8 USERS 50 YES
+DATA/orcl/datafile/users.274.1133862645 17 USERS 50 YES
+DATA/orcl/datafile/users.281.1133862509 10 USERS 50 YES
+DATA/orcl/datafile/users.280.1133862525 11 USERS 50 YES
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024 AUT
--------------------------------------------- ---------- --------------- --------------- ---
+DATA/orcl/datafile/users.279.1133862553 12 USERS 50 YES
+DATA/orcl/datafile/users.278.1133862571 13 USERS 50 YES
+DATA/orcl/datafile/undotbs2.263.1132745567 4 UNDOTBS2 200 YES
+DATA/orcl/datafile/sysaux.260.1132745551 2 SYSAUX 600 YES
+DATA/orcl/datafile/undotbs1.261.1132745553 3 UNDOTBS1 840 YES
+DATA/orcl/datafile/system.259.1132745549 1 SYSTEM 1060 YES
17 rows selected.
说明：红颜色标出为新增数据文件，到此成功模拟出生产环境存在的故障.
2、备库恢复
--现对备库sh_orcl进行恢复，当前备库sh_orcl数据文件信息如下.
SYS@sh_orcl> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/system.259.1132745549
/u01/app/oracle/oradata/sysaux.260.1132745551
/u01/app/oracle/oradata/undotbs1.261.1132745553
/u01/app/oracle/oradata/undotbs2.263.1132745567
/u01/app/oracle/oradata/users.264.1132745567
2.1、停止MRP
--备库停止mrp进程.
SYS@sh_orcl> alter database recover managed standby database cancel;
Database altered.
2.2、确认最小SCN
--备库sh_orcl确认最小scn，以便在备库orcldg进行备份.
SYS@sh_orcl> select current_scn from v$database;
CURRENT_SCN
-----------
1069642
SYS@sh_orcl> select min(checkpoint_change#) from v$datafile_header;
MIN(CHECKPOINT_CHANGE#)
-----------------------
1069643
SYS@sh_orcl> select to_char(scn_to_timestamp(1029642),'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SCN_TO_TIME
-------------------
2023-04-10 23:51:07
SYS@sh_orcl> select to_char(scn_to_timestamp(15930),'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SCN_TO_TIME
-------------------
2023-03-29 11:32:46
SYS@sh_orcl> select to_char(scn_to_timestamp(15910),'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(scn_to_timestamp(15910),'yyyy-mm-dd hh24:mi:ss') from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
说明：In ideal situation the above 2 queries will return the almost same SCN. However if there is huge difference its better to take backup using the SCN from second query (lesser SCN), as one of the datafile may be behind.
说明：如上所示，scn 15910无法确认出具体时间信息，生产环境会遇到此种场景，以下便以scn 15910作为依据进行增量备份恢复.
2.3、确认新增数据文件
--根据备库sh_orcl查得的最小scn确认新增数据文件.
SYS@orcldg> col name for a50
SYS@orcldg> set line 200
SYS@orcldg> select file#,name from v$datafile where creation_change#>1069642;
FILE# NAME
---------- --------------------------------------------------
6 /u01/app/oracle/oradata/users.285.1133862437
7 /u01/app/oracle/oradata/users.284.1133862457
8 /u01/app/oracle/oradata/users.283.1133862473
9 /u01/app/oracle/oradata/users.282.1133862489
10 /u01/app/oracle/oradata/users.281.1133862509
11 /u01/app/oracle/oradata/users.280.1133862525
12 /u01/app/oracle/oradata/users.279.1133862553
13 /u01/app/oracle/oradata/users.278.1133862571
14 /u01/app/oracle/oradata/users.277.1133862585
15 /u01/app/oracle/oradata/users.276.1133862603
16 /u01/app/oracle/oradata/users.275.1133862625
FILE# NAME
---------- --------------------------------------------------
17 /u01/app/oracle/oradata/users.274.1133862645
12 rows selected.
SYS@orcldg> select file#,name from v$datafile where creation_change#>15910;
FILE# NAME
---------- --------------------------------------------------
4 /u01/app/oracle/oradata/undotbs2.263.1132745567
5 /u01/app/oracle/oradata/users.264.1132745567
6 /u01/app/oracle/oradata/users.285.1133862437
7 /u01/app/oracle/oradata/users.284.1133862457
8 /u01/app/oracle/oradata/users.283.1133862473
9 /u01/app/oracle/oradata/users.282.1133862489
10 /u01/app/oracle/oradata/users.281.1133862509
11 /u01/app/oracle/oradata/users.280.1133862525
12 /u01/app/oracle/oradata/users.279.1133862553
13 /u01/app/oracle/oradata/users.278.1133862571
14 /u01/app/oracle/oradata/users.277.1133862585
FILE# NAME
---------- --------------------------------------------------
15 /u01/app/oracle/oradata/users.276.1133862603
16 /u01/app/oracle/oradata/users.275.1133862625
17 /u01/app/oracle/oradata/users.274.1133862645
14 rows selected.
说明：如上所示，根据scn 1069642和15910查询出来的新增数据文件相同.
2.4、orcldg备库修改valid_for参数
备库orcldg修改valid_for参数，将ONLINE_LOGFILES,PRIMARY_ROLE修改为ALL_LOGFILES,ALL_ROLES
SYS@orcldg> alter system set log_archive_dest_4='service=sh_orcl COMPRESSION=ENABLE LGWR ASYNC VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sh_orcl';
System altered.
SYS@orcldg> alter system set log_archive_dest_state_4='enable';
System altered.
2.5、备份数据文件和增量备份
2.5.1、备份数据文件
--创建目录.
[root@hisdbdg u01]# mkdir -p /u01/backup
[root@hisdbdg u01]# chown oracle:oinstall /u01/backup
--备库orcldg备份新增的数据文件.
RMAN> backup as compressed backupset datafile 6,7,8,9,10,11,12,13,14,15,16,17 format '/u01/backup/ForStandby_%U' tag 'FORSTANDBY';
Starting backup at 11-APR-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/users.285.1133862437
input datafile file number=00007 name=/u01/app/oracle/oradata/users.284.1133862457
input datafile file number=00008 name=/u01/app/oracle/oradata/users.283.1133862473
input datafile file number=00009 name=/u01/app/oracle/oradata/users.282.1133862489
input datafile file number=00010 name=/u01/app/oracle/oradata/users.281.1133862509
input datafile file number=00011 name=/u01/app/oracle/oradata/users.280.1133862525
input datafile file number=00012 name=/u01/app/oracle/oradata/users.279.1133862553
input datafile file number=00013 name=/u01/app/oracle/oradata/users.278.1133862571
input datafile file number=00014 name=/u01/app/oracle/oradata/users.277.1133862585
input datafile file number=00015 name=/u01/app/oracle/oradata/users.276.1133862603
input datafile file number=00016 name=/u01/app/oracle/oradata/users.275.1133862625
input datafile file number=00017 name=/u01/app/oracle/oradata/users.274.1133862645
channel ORA_DISK_1: starting piece 1 at 11-APR-23
channel ORA_DISK_1: finished piece 1 at 11-APR-23
piece handle=/u01/backup/ForStandby_0g1pasl4_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-APR-23
2.5.2、增量备份
--主库根据最小scn进行增量备份.
RMAN> backup as compressed backupset incremental from scn 15910 database format '/u01/backup/ForStandby_%U' tag 'FORSTANDBY';
Starting backup at 11-APR-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/system.259.1132745549
input datafile file number=00003 name=/u01/app/oracle/oradata/undotbs1.261.1132745553
input datafile file number=00002 name=/u01/app/oracle/oradata/sysaux.260.1132745551
input datafile file number=00004 name=/u01/app/oracle/oradata/undotbs2.263.1132745567
input datafile file number=00006 name=/u01/app/oracle/oradata/users.285.1133862437
input datafile file number=00007 name=/u01/app/oracle/oradata/users.284.1133862457
input datafile file number=00008 name=/u01/app/oracle/oradata/users.283.1133862473
input datafile file number=00009 name=/u01/app/oracle/oradata/users.282.1133862489
input datafile file number=00010 name=/u01/app/oracle/oradata/users.281.1133862509
input datafile file number=00011 name=/u01/app/oracle/oradata/users.280.1133862525
input datafile file number=00012 name=/u01/app/oracle/oradata/users.279.1133862553
input datafile file number=00013 name=/u01/app/oracle/oradata/users.278.1133862571
input datafile file number=00014 name=/u01/app/oracle/oradata/users.277.1133862585
input datafile file number=00015 name=/u01/app/oracle/oradata/users.276.1133862603
input datafile file number=00016 name=/u01/app/oracle/oradata/users.275.1133862625
input datafile file number=00017 name=/u01/app/oracle/oradata/users.274.1133862645
input datafile file number=00005 name=/u01/app/oracle/oradata/users.264.1132745567
channel ORA_DISK_1: starting piece 1 at 11-APR-23
channel ORA_DISK_1: finished piece 1 at 11-APR-23
piece handle=/u01/backup/ForStandby_0h1paso7_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed 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 11-APR-23
channel ORA_DISK_1: finished piece 1 at 11-APR-23
piece handle=/u01/backup/ForStandby_0i1paspu_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-APR-23
2.5.3、备份控制文件
--备份控制文件.
RMAN> backup current controlfile for standby format '/u01/backup/ForStandbyCTRL.bck';
Starting backup at 11-APR-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 11-APR-23
channel ORA_DISK_1: finished piece 1 at 11-APR-23
piece handle=/u01/backup/ForStandbyCTRL.bck tag=TAG20230411T111801 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-APR-23
2.6、传送备份文件
--将备库orcldg备份的文件传送到备库sh_orcl.
2.6.1、备库sh_orcl创建目录
[root@leo-shhisdbdg ~]# mkdir -p /u01/backup
[root@leo-shhisdbdg ~]# chown oracle:oinstall /u01/backup
2.6.2、备库orcldg传送文件
[root@hisdbdg backup]# ls -ltr
total 495664
-rw-r----- 1 oracle oinstall 1097728 Apr 11 11:14 ForStandby_0g1pasl4_1_1
-rw-r----- 1 oracle oinstall 486506496 Apr 11 11:17 ForStandby_0h1paso7_1_1
-rw-r----- 1 oracle oinstall 1114112 Apr 11 11:17 ForStandby_0i1paspu_1_1
-rw-r----- 1 oracle oinstall 18841600 Apr 11 11:18 ForStandbyCTRL.bck
[oracle@hisdbdg backup]$ scp ForStandby* oracle@192.168.133.216:/u01/backup/
2.7、备库恢复
2.7.1、恢复控制文件
--备库sh_orcl进行恢复.
[oracle@leo-shhisdbdg admin]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 11 11:22:04 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1660526279)
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 2455228416 bytes
Fixed Size 2255712 bytes
Variable Size 620758176 bytes
Database Buffers 1811939328 bytes
Redo Buffers 20275200 bytes
RMAN> restore standby controlfile from '/u01/backup/ForStandbyCTRL.bck';
Starting restore at 11-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/control01.ctl
Finished restore at 11-APR-23
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
2.7.2、注册备份集
RMAN> catalog start with '/u01/backup';
Starting implicit crosscheck backup at 11-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 11-APR-23
Starting implicit crosscheck copy at 11-APR-23
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 11-APR-23
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_113_l38cxx9m_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_112_l38cxxfk_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_2_18_l38cxxv7_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_2_19_l38cxxvk_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_114_l38cxxy3_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_2_20_l38cy05b_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_115_l38d7qsn_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_116_l38d7x7b_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_117_l38d84hh_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_2_21_l38d84v9_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_118_l38d8bgd_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_119_l38d8jgk_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_120_l38d8q0o_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_2_22_l38d8wx6_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_121_l38d8x5x_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_122_l38d93fo_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_1_123_l38d99qs_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_10/o1_mf_2_23_l38d9cxs_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_11/o1_mf_1_124_l39c9tx1_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_11/o1_mf_2_24_l39c9v7k_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_11/o1_mf_1_125_l39c9vkt_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_11/o1_mf_1_126_l39cb1rn_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_11/o1_mf_2_25_l39cb1so_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_11/o1_mf_2_28_l39hd2nk_.arc
File Name: /u01/app/oracle/fast_recovery_area/SH_ORCL/archivelog/2023_04_11/o1_mf_1_129_l39hd3b4_.arc
searching for all files that match the pattern /u01/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/backup/ForStandby_0h1paso7_1_1
File Name: /u01/backup/ForStandby_0i1paspu_1_1
File Name: /u01/backup/ForStandbyCTRL.bck
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/backup/ForStandby_0h1paso7_1_1
File Name: /u01/backup/ForStandby_0i1paspu_1_1
File Name: /u01/backup/ForStandbyCTRL.bck
2.7.3、恢复丢失的数据文件
--备库恢复主库新增的数据文件.
RMAN> run
2> {
3> set newname for datafile 6 to '/u01/app/oracle/oradata/users.285.1133862437';
4> set newname for datafile 7 to '/u01/app/oracle/oradata/users.284.1133862457';
5> set newname for datafile 8 to '/u01/app/oracle/oradata/users.283.1133862473';
6> set newname for datafile 9 to '/u01/app/oracle/oradata/users.282.1133862489';
7> set newname for datafile 10 to '/u01/app/oracle/oradata/users.281.1133862509';
8> set newname for datafile 11 to '/u01/app/oracle/oradata/users.280.1133862525';
9> set newname for datafile 12 to '/u01/app/oracle/oradata/users.279.1133862553';
10> set newname for datafile 13 to '/u01/app/oracle/oradata/users.278.1133862571';
11> set newname for datafile 14 to '/u01/app/oracle/oradata/users.277.1133862585';
12> set newname for datafile 15 to '/u01/app/oracle/oradata/users.276.1133862603';
13> set newname for datafile 16 to '/u01/app/oracle/oradata/users.275.1133862625';
14> set newname for datafile 17 to '/u01/app/oracle/oradata/users.274.1133862645';
15> restore datafile 6,7,8,9,10,11,12,13,14,15,16,17;
16> switch datafile all;
17> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 11-APR-23
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 00006 to /u01/app/oracle/oradata/users.285.1133862437
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/users.284.1133862457
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/users.283.1133862473
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/users.282.1133862489
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/users.281.1133862509
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/users.280.1133862525
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/users.279.1133862553
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/users.278.1133862571
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/users.277.1133862585
channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/users.276.1133862603
channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/users.275.1133862625
channel ORA_DISK_1: restoring datafile 00017 to /u01/app/oracle/oradata/users.274.1133862645
channel ORA_DISK_1: reading from backup piece /u01/backup/ForStandby_0h1paso7_1_1
channel ORA_DISK_1: piece handle=/u01/backup/ForStandby_0h1paso7_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 11-APR-23
datafile 6 switched to datafile copy
input datafile copy RECID=19 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.285.1133862437
datafile 7 switched to datafile copy
input datafile copy RECID=20 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.284.1133862457
datafile 8 switched to datafile copy
input datafile copy RECID=21 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.283.1133862473
datafile 9 switched to datafile copy
input datafile copy RECID=22 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.282.1133862489
datafile 10 switched to datafile copy
input datafile copy RECID=23 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.281.1133862509
datafile 11 switched to datafile copy
input datafile copy RECID=24 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.280.1133862525
datafile 12 switched to datafile copy
input datafile copy RECID=25 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.279.1133862553
datafile 13 switched to datafile copy
input datafile copy RECID=26 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.278.1133862571
datafile 14 switched to datafile copy
input datafile copy RECID=27 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.277.1133862585
datafile 15 switched to datafile copy
input datafile copy RECID=28 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.276.1133862603
datafile 16 switched to datafile copy
input datafile copy RECID=29 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.275.1133862625
datafile 17 switched to datafile copy
input datafile copy RECID=30 STAMP=1133868578 file name=/u01/app/oracle/oradata/users.274.1133862645
2.7.4、恢复增量数据
--运用新注册的增量备份恢复备库sh_orcl的数据.
RMAN> recover database noredo;
Starting recover at 11-APR-23
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: /u01/app/oracle/oradata/system.259.1132745549
destination for restore of datafile 00002: /u01/app/oracle/oradata/sysaux.260.1132745551
destination for restore of datafile 00003: /u01/app/oracle/oradata/undotbs1.261.1132745553
destination for restore of datafile 00004: /u01/app/oracle/oradata/undotbs2.263.1132745567
destination for restore of datafile 00005: /u01/app/oracle/oradata/users.264.1132745567
channel ORA_DISK_1: reading from backup piece /u01/backup/ForStandby_0h1paso7_1_1
channel ORA_DISK_1: piece handle=/u01/backup/ForStandby_0h1paso7_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished recover at 11-APR-23
2.7.5、开启mrp进程
SYS@sh_orcl> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SYS@sh_orcl> alter database open;
Database altered.
SYS@sh_orcl> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SYS@sh_orcl> select count(*) from test;
COUNT(*)
----------
1375965
3、同步验证
--主库从test表中删除10000行数据.
SYS@orcl1> delete from test where rownum<10000;
9999 rows deleted.
SYS@orcl1> commit;
Commit complete.
SYS@orcl1> select count(*) from test;
COUNT(*)
----------
1365966
--备库sh_orcl查询.
SYS@sh_orcl> select count(*) from test;
COUNT(*)
----------
1365966
说明：如上所示，备库sh_orcl恢复实时同步.
4、修改回原参数
--备库orcldg将valid_for参数还原.
SYS@orcldg> alter system set log_archive_dest_4='SERVICE=sh_orcl COMPRESSION=ENABLE LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sh_orcl';
System altered.
SYS@orcldg> alter system set log_archive_dest_state_4='enable';
System altered.活动
天翼云最新优惠活动，涵盖免费试用,产品折扣等，助您降本增效！
oracle运用备库增量备份恢复另一个备库的gap—主库rac，备库文件系统
oracle运用备库增量备份恢复另一个备库的gap—主库rac，备库文件系统
2024-12-23 09:16:28 阅读次数：1
版权声明：本文内容来自第三方投稿或授权转载，原文地址：https://blog.51cto.com/u_12991611/6183081，作者：Liujun_Deng，版权归原作者所有。本网站转在其作品的目的在于传递更多信息，不拥有版权，亦不承担相应法律责任。如因作品内容、版权等问题需要同本网站联系，请发邮件至ctyunbbs@chinatelecom.cn沟通。
作者介绍
天翼云用户
查看更多
最新文章
运用增量备份恢复oracle adg gap—主库rac，备库文件系统
2024-12-20 07:55:52
oracle学习82-VMware出现配置文件 .vmx 是由VMware产品创建，但该产品与此版 VMware workstation 不兼容，因此无法使用（VMware版本不兼容问题）
2024-06-26 06:21:11
oracle学习3-配置监听
2024-06-26 06:21:11
oracle学习32-plsql版本低需到配置文件中添加配置
2024-06-13 08:59:42
重操旧业障碍多
2024-06-13 08:07:37
配置防火墙允许从外网访问内网oracle
2024-06-12 09:30:16
查看更多
热门文章
Oracle安装时的系统内核参数如何设定（标准的设定方法，绝无出错）
2023-04-21 03:14:39
Oracle Data Guard详细配置过程
2023-06-14 09:12:19
oracle archivelog mode
2023-07-20 06:08:22
oracle学习1-oracle安装
2023-06-19 06:54:51
Oracle19c安装，基本配置教程（超详细）
2023-07-03 08:07:04
12c向19c迁移：OGG基本配置
2024-03-22 08:24:29
售前咨询热线
400-810-9889转1
关注天翼云
- APP
©2024
天翼云科技有限公司版权所有 增值电信业务经营许可证A2.B1.B2-20090001
公司地址：北京市东城区青龙胡同甲1号、3号2幢2层205-32室
