Contents

使用RMAN增量備份前滾物理備用數據庫

使用RMAN增量備份前滾物理備用數據庫

Roll Forward Physical Standby Database using RMAN incremental backup

Problem background

在某些情況下，備用數據庫會比主數據庫落後很多，從而導致Archive Gap。可能是由於以下原因之一

1.可能是由於主數據庫和備用數據庫之間的網絡中斷導致了存檔缺口。重新建立連接後，Data Guard將能夠自動檢測存檔間隔，並可以獲取丟失的日誌。

2.也可能是由於歸檔日誌在主數據庫上丟失或歸檔被破壞，並且沒有有效的備份。

在備用數據庫遠遠落後於主數據庫的情況下，增量備份可以用作前滾物理備用數據庫以使其與主數據庫同步的方法之一。

Oracle Database version : 11.2.0.1.0 My Oracle Database is using ASM.

Primary database : sspm Standby database : sssb

Primary Host : dev Standby Host : uat

在主數據庫上生成的最大存檔日誌序列為1005。

SQL> select status,instance_name,database_role from v$database,v$instance;
 
STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         sspm             PRIMARY
 
SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
 
THREAD# MAX(SEQUENCE#)
------- ----------------
1       1005

在備用數據庫上，應用的最大歸檔日誌序列為序列865。

SQL> select status,instance_name,database_role from v$database,v$instance;
 
STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED      sssb             PHYSICAL STANDBY
 
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
 
THREAD# MAX(SEQUENCE#)
------- ----------------
1       865

備用數據庫落後於主數據庫約140個存檔（1005 – 865）。

當我調查主數據庫的警報日誌文件以找出未在備用數據庫上應用日誌的原因時，我看到了以下錯誤消息。

FAL[client]: Failed to request gap sequence
GAP - thead 1 sequence 866
...

所以問題出在這裡。歸檔日誌序列866丟失，並且在FRA站點上不可用。 FRA上幾乎沒有其他存檔，我也沒有備份將其還原到備用數據庫上。我的選擇是使用增量備份前滾備用數據庫。以下是有關如何前滾物理備用數據庫的步驟。

步驟1：記錄物理備用數據庫的當前SCN

Standby Database:

SQL> select current_scn from v$database;
 
CURRENT_SCN
-----------
991247

記下備用數據庫的CURRENT_SCN值（991247）以繼續進行。

步驟2：取消Standby數據庫上的Managed Recovery Process

Standby Database:

SQL> alter database recover managed standby database cancel;

記下備用數據庫的CURRENT_SCN值（991247）以繼續進行。

步驟3：在主數據庫上，從當前記錄在備用數據庫上的SCN進行增量SCN備份991247

連接到主數據庫並進行增量SCN備份。

Primary Database:

[oracle@dev ~]$ rman target sys/oracle@sspm
 
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 2515:44:45 2012
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: SSPM (DBID=1624493265)
 
RMAN> backup incremental from scn 991247 database format '/u02/bkp/stnd_backp_%U.bak';

步驟4：備份主數據庫控製文件的備用控製文件

連接到主數據庫並創建備用控製文件備份。 Primary Database :

RMAN> backup current controlfile for standby format '/u02/stnd_%U.ctl';

步驟5：將備份從主服務器轉移到備用服務器

Primary Database :

[oracle@dev u02]$ scp stnd*.ctl bkp/stnd*.bak uat:/u02/bkp

步驟6：在備用服務器上，通過RMAN連接Standby Database並編錄複製的增量備份，以便Standby Database的Controlfile可以識別這些增量備份

我將增量備份複製到備用服務器上的位置"/u02/bkp"。 Standby Database:

[oracle@uat ~]$ rman target sys/mydbpwd@sssb
 
connected to target database: SSPM (DBID=1624493265, not open)
 
RMAN> catalog start with '/u02/bkp';

步驟7：使用分類的增量備份片恢復備用數據庫

Standby Database:

RMAN> recover database noredo;

步驟8：關閉物理備用數據庫，在nomount階段啟動它，並還原我們從主數據庫中獲取的備用控製文件備份

Standby Database:

RMAN> shutdown immediate
 
database dismounted
Oracle instance shut down
 
RMAN> startup nomount
 
connected to target database (not started)
Oracle instance started
 
Total System Global Area     659730432 bytes
 
Fixed Size                     2216264 bytes
Variable Size                398462648 bytes
Database Buffers             255852544 bytes
Redo Buffers                   3198976 bytes
 
RMAN> restore standby controlfile from '/u02/bkp/stnd_12n6p3qt_1_1.ctl';

步驟9：關閉備用數據庫並掛載備用數據庫，以便使用上一步中還原的新控製文件掛載備用數據庫

Standby Database:

RMAN> <strong>shutdown immediate
 
Oracle instance shut down
 
RMAN> startup mount
 
connected to target database (not started)
Oracle instance started
database mounted
 
Total System Global Area     659730432 bytes
 
Fixed Size                     2216264 bytes
Variable Size                398462648 bytes
Database Buffers             255852544 bytes
Redo Buffers                   3198976 bytes

步驟10：如果主數據庫和備用數據庫的數據文件位置不同，則需要執行此步驟。如果不是，則繼續執行步驟11

NOTE 如果主备数据文件位置相同，请跳过此步骤！

我的主數據庫的數據文件駐留在主服務器上的磁盤組+ DATA_NEW上，而備用數據庫的數據文件駐留在備用服務器上的磁盤組+ DATA上，數據文件的位置不同。

由於我已經在備份數據庫上還原了主數據庫的備份控製文件備份塊（步驟7）並掛載了備份數據庫，因此備份數據庫控製文件現在將記錄有數據文件的位置，在主數據庫中可用。因此，我們需要使備用控製文件了解備用數據庫的數據文件位置與主數據庫的數據文件位置不同。為此，您需要將備用數據庫的數據文件位置編入其控製文件，如下所示。

通過RMAN連接備用數據庫，並編目其數據文件的位置，然後切換它們。 Standby Database:

RMAN> catalog start with '+DATA/SSSB/DATAFILE';
searching for all files that match the pattern +DATA/SSSB/DATAFILE

List of Files Unknown to the Database
=====================================
File Name: +data/SSSB/DATAFILE/SYSTEM.274.778865099
File Name: +data/SSSB/DATAFILE/SYSAUX.275.778865193
File Name: +data/SSSB/DATAFILE/UNDOTBS1.276.778865259
File Name: +data/SSSB/DATAFILE/USERS.277.778865273

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

List of Cataloged Files
=======================
File Name: +data/SSSB/DATAFILE/SYSTEM.274.778865099
File Name: +data/SSSB/DATAFILE/SYSAUX.275.778865193
File Name: +data/SSSB/DATAFILE/UNDOTBS1.276.778865259
File Name: +data/SSSB/DATAFILE/USERS.277.778865273

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/sssb/datafile/system.274.778865099"
datafile 2 switched to datafile copy "+DATA/sssb/datafile/sysaux.275.778865193"
datafile 3 switched to datafile copy "+DATA/sssb/datafile/undotbs1.276.778865259"
datafile 4 switched to datafile copy "+DATA/sssb/datafile/users.277.77886527

步驟11：如果主數據庫和備用數據庫的數據文件位置相同，則無需執行上一步中的編目操作

在備用數據庫上，啟動託管恢復過程。

SQL> alter database recover managed standby database disconnectfrom session;
 
Database altered.
 
SQL> select process,status,sequence# from v$managed_standby;
 
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE               1010
RFS       IDLE                  0
MRP0      WAIT_FOR_LOG          0
 
9 rows selected.

步驟12：在主數據庫上，檢查生成的最大存檔日誌序列

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
 
THREAD# MAX(SEQUENCE#)
------- ----------------
1       1009

步驟13：檢查在物理備用數據庫上應用的最大歸檔日誌序列

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
 
THREAD# MAX(SEQUENCE#)
------- ----------------
1       1009

因此，在這裡我們可以從步驟12和13中看到，在主數據庫上生成的最大存檔日誌序列是序列號1009，在物理備用數據庫上應用的最大歸檔日誌序列也是1009，這意味著備用數據庫與主數據庫同步。您可以通過在主數據庫上生成一個歸檔序列來檢出它，並檢查它是否已交付並應用於備用數據庫。

Primary Database:

SQL> alter system switch logfile;

System altered.

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- ----------------
1       1010

Standby Database:

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- ----------------
1       1010

現在備用數據庫與主數據庫已同步。

Here you go !!

Troubeshooting

physical database:

select group#,member,status from v$logfile;

standby database:

alter system set standby_file_management=manual;
alter database recover managed standby database cancel;
select * from v$log;

select group# from v$standby_log;
alter database drop logfile group 10;

alter database add standby logfile group 10 '<>' size 2G;




        21 /data/MCHDPOS/redofile/standby_redolog_001.log
        22 /data/MCHDPOS/redofile/standby_redolog_002.log
        23 /data/MCHDPOS/redofile/standby_redolog_003.log
        24 /data/MCHDPOS/redofile/standby_redolog_004.log
        25 /data/MCHDPOS/redofile/standby_redolog_005.log
        26 /data/MCHDPOS/redofile/standby_redolog_006.log
        27 /data/MCHDPOS/redofile/standby_redolog_007.log

alter database add standby logfile group 21 '/data/MCHDPOS/redofile/standby_redolog_001.log' size 2048M;
alter database add standby logfile group 22 '/data/MCHDPOS/redofile/standby_redolog_002.log' size 2048M;
alter database add standby logfile group 23 '/data/MCHDPOS/redofile/standby_redolog_003.log' size 2048M;
alter database add standby logfile group 24 '/data/MCHDPOS/redofile/standby_redolog_004.log' size 2048M;
alter database add standby logfile group 25 '/data/MCHDPOS/redofile/standby_redolog_005.log' size 2048M;
alter database add standby logfile group 26 '/data/MCHDPOS/redofile/standby_redolog_006.log' size 2048M;
alter database add standby logfile group 27 '/data/MCHDPOS/redofile/standby_redolog_007.log' size 2048M;
alter database add standby logfile group 28 '/data/MCHDPOS/redofile/standby_redolog_008.log' size 2048M;

alter system set standby_file_management=AUTO;

oracle@hdmpdb02:/home/oracle$rman target / nocatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 31 12:56:02 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MCHDPOS (DBID=1372236014, not open)
using target database control file instead of recovery catalog

RMAN> catalog start with '/fra/arch01/MCHDPOS/';

searching for all files that match the pattern /fra/arch01/MCHDPOS/

List of Files Unknown to the Database
=====================================
File Name: /fra/arch01/MCHDPOS/MCHDPOS_ADG_1_65013_1000552238.dbf
File Name: /fra/arch01/MCHDPOS/MCHDPOS_ADG_1_64990_1000552238.dbf
File Name: /fra/arch01/MCHDPOS/MCHDPOS_ADG_1_65040_1000552238.dbf
File Name: /fra/arch01/MCHDPOS/MCHDPOS_ADG_1_65050_1000552238.dbf
File Name: /fra/arch01/MCHDPOS/MCHDPOS_ADG_1_64880_1000552238.dbf
File Name: /fra/arch01/MCHDPOS/MCHDPOS_ADG_1_64648_1000552238.dbf
File Name: /fra/arch01/MCHDPOS/MCHDPOS_ADG_1_64570_1000552238.dbf

Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Media Recovery Waiting for thread 1 sequence 64952
Fetching gap sequence in thread 1, gap sequence 64952-65051
Tue Mar 31 12:41:11 2020
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 64952-65051
 DBID 1372236014 branch 1000552238
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
archivelog gaps.
------------------------------------------------------------
Tue Mar 31 12:45:20 2020
RFS[1]: Selected log 22 for thread 1 sequence 65141 dbid 1372236014 branch 1000552238
Tue Mar 31 12:45:34 2020
Archived Log entry 68 added for thread 1 sequence 65140 ID 0x51cb5bee dest 1:
Tue Mar 31 12:45:52 2020
RFS[1]: Selected log 21 for thread 1 sequence 65142 dbid 1372236014 branch 1000552238
Tue Mar 31 12:45:52 2020
Archived Log entry 69 added for thread 1 sequence 65141 ID 0x51cb5bee dest 1:
Tue Mar 31 12:57:37 2020
Fetching gap sequence in thread 1, gap sequence 64952-64989
Tue Mar 31 12:57:50 2020
Fetching gap sequence in thread 1, gap sequence 64952-64966
Tue Mar 31 12:58:04 2020
Fetching gap sequence in thread 1, gap sequence 64952-64954
Tue Mar 31 12:58:44 2020
Fetching gap sequence in thread 1, gap sequence 64952-64952
Tue Mar 31 13:00:05 2020
Fetching gap sequence in thread 1, gap sequence 64952-64952
Tue Mar 31 13:01:38 2020
Media Recovery Log /fra/arch01/MCHDPOS/MCHDPOS_ADG_1_64952_1000552238.dbf
Tue Mar 31 13:03:22 2020
Media Recovery Log /fra/arch01/MCHDPOS/MCHDPOS_ADG_1_64953_1000552238.dbf

SELECT sequence#,Name,first_time, next_time, applied FROM   v$archived_log  ORDER BY sequence#;

References

RollForwardPhysicalStandby

désert/Oracle/DataGuard/RollForwardPhysicalStandby (last edited 2020-03-31 05:09:06 by merlin)