使用DG環境的物理備庫進行備份還原的備份一致性問題

※使用物理備庫備份數據庫的一致性說明

※DG環境介紹

一、備庫執行備份

1.1、備庫取消恢復

1.2、備庫執行備份

1.3、備庫啟用日志應用進程

※使用物理備庫備份數據庫的一致性說明

※DG環境介紹

一、備庫執行備份

1.1、備庫取消恢復

1.2、備庫執行備份

1.3、備庫啟用日志應用進程

二、將備份文件傳遞到新主庫

三、在新主機執行恢復操作

3.1、恢復spfile

3.2、恢復控制文件

3.3、注冊備份信息

3.4、還原數據文件

3.5、激活備庫為主庫，并啟動數據庫

3.6、刪除不用的日志組

在部署完ADG（Active Data Guard）后，不但可以將只讀的查詢交給備庫執行，還可以把日常的數據庫備份工作放在備庫上執行，從而減輕主庫的壓力，充分的發揮服務器資源。

本文演示的是利用Oracle DG環境的備庫執行備份，然后異機還原恢復成一個新的主庫的過程。

※使用物理備庫備份數據庫的一致性說明

之前發過一篇類似的文章，請參考：https://mp.weixin.qq.com/s/ptB9D3sDzwNyHyHujTwKbQ

這幾天還原了一個將近2T的庫，碰到了一個坑。情況是這樣的，我在物理備庫進行的備份，但是當我把備份拿到新主機進行還原后，死活就是不能正常啟動數據庫，報錯“數據庫不一致，需要相關的歸檔日志進行恢復”，即使加上隱含參數“_allow_resetlogs_corruption”強制啟庫也不行。無奈之下，找了找相關資料，發現在物理備庫進行的備份其實是存在備份一致性的問題的。

請看下圖，來自于Benefits and Usage of RMAN with Standby Databases (文檔 ID 602299.1)：

在備庫進行備份，主要關注備份的一致性問題，分為2種情況：

1、備庫可以連接到主庫：

方法a、對數據文件備份完成后，需要在主庫切換歸檔后，等最新的歸檔傳遞到備庫后，再在備庫備份歸檔，最后備份控制文件。需要注意的是，如果是11.2.0.4以上，可以不用單獨在主庫執行切換歸檔操作，因為在備庫執行備份的過程中，會自動去連接主庫執行一個切換歸檔的操作，但是若主庫本身拒絕備庫的連接，會報RMAN警告（RMAN-06820: WARNING: failed to archive current log at primary database），那么依然需要手工在主庫進行切換。

方法b、災備端可以在mount狀態或read only狀態進行備份，但是不能啟用實時應用功能（mrp進程）。因為，即使數據庫處于read only或mount狀態，mrp進程也會持續更新數據塊。

2、備庫不能連接到主庫：災備端可以在mount狀態或read only狀態進行備份，但是不能啟用實時應用功能（mrp進程）。因為，即使數據庫處于read only或mount狀態，mrp進程也會持續更新數據塊。

如果備庫還原完成后，還需要歸檔才能打開數據庫，那么我們也可以在原主庫上拷貝相關的歸檔日志到我們的新主庫進行恢復（recover），即可打開數據庫，只是有的情況下，環境不允許我們連接主庫，主庫一般都是生產庫。

其它更多內容請參考：http://blog.itpub.net/26736162/viewspace-2723840/

參考文檔：

( Doc ID 1616074.1) RMAN-06820 ORA-17629 During Backup at Standby Site

How to take consistent backups at standby site (文檔 ID 1292126.1)

( Doc ID 1419923.1) Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode

Step by Step method to create Primary/Standby Database from Standby Backup (文檔 ID 1604251.1)

How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (文檔 ID 1354256.1)

Benefits and Usage of RMAN with Standby Databases (文檔 ID 602299.1)

本環境中，我們選擇第2種辦法在災備端進行備份數據庫，并拿到新環境進行還原。

※DG環境介紹

一、備庫執行備份

1.1、備庫取消恢復

為了能在備庫達到一致性備份，需要取消實時應用功能：

alter database recover managed standby database cancel;

檢查是否處于一致性狀態：

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; set line 1000 set numwidth 16 col name format a55 select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ;

如果fuzzy為NO，且checkpoint_change#列的值都一致，那么就代表數據庫處于一致性狀態，可以進行rman備份。

SYS@oradgphy > alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; Session altered. SYS@oradgphy > set line 1000 SYS@oradgphy > set numwidth 16 SYS@oradgphy > col name format a55 SYS@oradgphy > select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ; FUZ STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME FILE# NAME --- ------- ------------------ ------------------- ---------------- ------------------------------------------------------- NO ONLINE 3500675 2020-09-25 14:44:17 1 /u01/app/oracle/oradata/oradgphy/system01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 2 /u01/app/oracle/oradata/oradgphy/sysaux01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 3 /u01/app/oracle/oradata/oradgphy/undotbs01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 4 /u01/app/oracle/oradata/oradgphy/users01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 5 /u01/app/oracle/oradata/oradgphy/example01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 6 /u01/app/oracle/oradata/oradgphy/logmnrtbs1.dbf SYS@oradgphy > select open_mode , database_role, flashback_on from v$database; OPEN_MODE DATABASE_ROLE FLASHBACK_ON -------------------- ---------------- ------------------ READ ONLY PHYSICAL STANDBY NO

1.2、備庫執行備份

export NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss' ORACLE_SID=oradgphy rman target / run{ backup as compressed backupset database format '/home/oracle/oracle_bk/FULL_%d_%U.full' section size 100G; backup as compressed backupset archivelog from time 'sysdate-2/24' format '/home/oracle/oracle_bk/ARC_%d_%U.arc' section size 100G; backup current controlfile format '/home/oracle/oracle_bk/standby_1.ctl'; backup spfile format '/home/oracle/oracle_bk/spfile_%d_%U.ora'; }

1.3、備庫啟用日志應用進程

alter database recover managed standby database using current logfile disconnect from session; ------------物理dg日志應用情況(主備庫都可以) alter session set NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss'; COL NAME FOR A100 SET LINESIZE 9999 PAGESIZE 9999 SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME FROM V$ARCHIVED_LOG A, (SELECT NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE# FROM V$ARCHIVED_LOG NB WHERE NB.APPLIED = 'YES' and NB.DEST_ID in (SELECT NB.DEST_ID FROM V$ARCHIVE_DEST_STATUS NB where STATUS <>'INACTIVE') GROUP BY NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID) B WHERE B.THREAD# = A.THREAD# AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE# AND A.DEST_ID=B.DEST_ID AND A.SEQUENCE# >= MAX_SEQUENCE# AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB) ORDER BY A.THREAD#, A.SEQUENCE#;

執行過程：

SYS@oradgphy > alter session set NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss'; Session altered. SYS@oradgphy > COL NAME FOR A100 SYS@oradgphy > SET LINESIZE 9999 PAGESIZE 9999 SYS@oradgphy > SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME 2 FROM V$ARCHIVED_LOG A, 3 (SELECT NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE# 4 FROM V$ARCHIVED_LOG NB 5 WHERE NB.APPLIED = 'YES' 6 and NB.DEST_ID in (SELECT NB.DEST_ID FROM V$ARCHIVE_DEST_STATUS NB where STATUS <>'INACTIVE') 7 GROUP BY NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID) B 8 WHERE B.THREAD# = A.THREAD# 9 AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE# 10 AND A.DEST_ID=B.DEST_ID 11 AND A.SEQUENCE# >= MAX_SEQUENCE# 12 AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB) 13 ORDER BY A.THREAD#, A.SEQUENCE#; THREAD# NAME SEQUENCE# APPLIED FIRST_TIME ---------- ---------------------------------------------------------------------------------------------------- ---------- --------- ------------------- 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_242_hpv5042k_.arc 242 YES 2020-09-25 14:53:53 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_243_hpv5050f_.arc 243 YES 2020-09-25 14:53:56 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_244_hpv505oh_.arc 244 IN-MEMORY 2020-09-25 14:53:57

確保DG環境恢復正常運行。

二、將備份文件傳遞到新主庫

接下來就是把/home/oracle/oracle_bk/備份目錄下的內容都拷貝到新主機上，方法很多，例如scp、磁盤卸載重新掛載等。

[oracle@rhel6lhr oracle_bk]$ scp /home/oracle/oracle_bk/* oracle@192.168.1.35:/home/oracle/oracle_bk/ oracle@192.168.1.35's password: FULL_ORADG11G_36vbaa2n_1_1.full 100% 333MB 19.6MB/s 00:17 FULL_ORADG11G_37vbaa3q_1_1.full 100% 1120KB 1.1MB/s 00:00 spfile_ORADG11G_39vbaa3v_1_1.ora 100% 96KB 96.0KB/s 00:00 standby_1.ctl 100% 10MB 9.8MB/s 00:00 [oracle@rhel6lhr oracle_bk]$

三、在新主機執行恢復操作

3.1、恢復spfile

export ORACLE_SID=TEST rman target / startup nomount; restore spfile to pfile '?/dbs/initTEST.ora' from '/home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora'; -- 修改pfile，去除dg相關參數 vi $ORACLE_HOME/dbs/initTEST.ora

需要根據情況對pfile做相關的修改，最終的參數文件內容：

*.audit_file_dest='/home/oracle/oradata/TEST/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/home/oracle/oradata/TEST/control01.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='oradg11g' *.db_recovery_file_dest='/home/oracle/oradata/flash_recovery_area' *.db_recovery_file_dest_size=4322230272 *.db_unique_name='TEST' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradg11gXDB)' *.memory_max_target=229715200 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' *.log_file_name_convert='/u01/app/oracle/oradata/oradg11g/','/home/oracle/oradata/' *.db_recovery_file_dest='/home/oracle/oradata/'

建議加上log_file_name_convert和db_recovery_file_dest參數，可以避免很多后續的日志轉換操作。

根據pfile文件內容創建相關目錄

mkdir -p /home/oracle/oradata/TEST/adump mkdir -p /home/oracle/oradata/flash_recovery_area

根據pfile創建spfile，并啟動到nomout

create spfile from pfile='/home/oracle/a.txt'; startup force nomount

3.2、恢復控制文件

-- 因為要恢復為主庫，所以需要加上primary關鍵字 restore primary controlfile from '/home/oracle/oracle_bk/standby_1.ctl';

3.3、注冊備份信息

-- 啟動到mout階段 alter database mount; -- 清除之前的備份信息 EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(11); /** CLEAR V$ARCHIVED_LOG */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(12); /** CLEAR V$BACKUP_SET */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13); /** CLEAR V$BACKUP_PIECE */ --重新注冊,注意路徑最后一定需要加上/ catalog start with '/home/oracle/oracle_bk/';

3.4、還原數據文件

run{ SET NEWNAME FOR DATABASE TO '/home/oracle/oradata/%b'; restore database; switch datafile all; }

執行過程：

[oracle@lhrora11203 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:31:44 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORADG11G (DBID=1403587593, not open) RMAN> run{ 2> SET NEWNAME FOR DATABASE TO '/home/oracle/oradata/%b'; 3> restore database; 4> switch datafile all; 5> } executing command: SET NEWNAME Starting restore at 2020-09-25 15:32:54 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 00001 to /home/oracle/oradata/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oradata/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/oradata/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oradata/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oradata/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/oradata/logmnrtbs1.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full channel ORA_DISK_1: piece handle=/home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full tag=TAG20200925T144855 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:26 Finished restore at 2020-09-25 15:34:20 datafile 1 switched to datafile copy input datafile copy RECID=36 STAMP=1052062460 file name=/home/oracle/oradata/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=37 STAMP=1052062460 file name=/home/oracle/oradata/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=38 STAMP=1052062460 file name=/home/oracle/oradata/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=39 STAMP=1052062460 file name=/home/oracle/oradata/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=40 STAMP=1052062460 file name=/home/oracle/oradata/example01.dbf datafile 6 switched to datafile copy input datafile copy RECID=41 STAMP=1052062460 file name=/home/oracle/oradata/logmnrtbs1.dbf RMAN> exit Recovery Manager complete.

3.5、激活備庫為主庫，并啟動數據庫

-- 1、查詢數據庫狀態 select open_mode , database_role, flashback_on from v$database; -- 2、激活備庫為主庫，如果已經是主庫了，那就跳過該步驟 alter database activate standby database; -- ALTER DATABASE CLEAR LOGFILE GROUP 4; -- alter database drop logfile group 4; -- 3、啟動數據庫 alter database open resetlogs;

結果：

[oracle@lhrora11203 ~]$ sas SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 25 15:34:58 2020 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@TEST> select open_mode , database_role, flashback_on from v$database; OPEN_MODE DATABASE_ROLE FLASHBACK_ON -------------------- ---------------- ------------------ MOUNTED PRIMARY NO SYS@TEST> set line 1000 SYS@TEST> set numwidth 16 SYS@TEST> col name format a55 SYS@TEST> select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ; FUZ STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME FILE# NAME --- ------- ------------------ ------------------- ---------------- ------------------------------------------------------- NO ONLINE 3500675 2020-09-25 14:44:17 1 /home/oracle/oradata/system01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 2 /home/oracle/oradata/sysaux01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 3 /home/oracle/oradata/undotbs01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 4 /home/oracle/oradata/users01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 5 /home/oracle/oradata/example01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 6 /home/oracle/oradata/logmnrtbs1.dbf -- 此處查詢出來fuzzy列都為NO，所以應該可以直接啟動數據庫，而不用執行recover操作。 SYS@TEST> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00392: log 2 of thread 1 is being cleared, operation not allowed ORA-00312: online log 2 thread 1: '/home/oracle/oradata/redo02.log' SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 2; Database altered. SYS@TEST> alter database open resetlogs; Database altered. SYS@TEST> select open_mode , database_role, flashback_on from v$database; OPEN_MODE DATABASE_ROLE FLASHBACK_ON -------------------- ---------------- ------------------ READ WRITE PRIMARY NO SYS@TEST> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1

3.6、刪除不用的日志組

col member format a100 select * from v$logfile; select 'alter database drop logfile group '|| GROUP# ||';' from v$standby_log; alter database drop logfile group 4; ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;

執行過程：

SYS@TEST> col member format a100 SYS@TEST> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------------- ------- ------- ------------------------------------------------------- --- 3 ONLINE /home/oracle/oradata/redo03.log NO 2 ONLINE /home/oracle/oradata/redo02.log NO 1 ONLINE /home/oracle/oradata/redo01.log NO 4 STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo04.log NO 5 STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo05.log NO 6 STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo06.log NO 7 STANDBY /home/oracle/oradata/standby_redo07.log NO 7 rows selected. SYS@TEST> select 'alter database drop logfile group '|| GROUP# ||';' from v$standby_log; 'ALTERDATABASEDROPLOGFILEGROUP'||GROUP#||';' --------------------------------------------------------------------------- alter database drop logfile group 4; alter database drop logfile group 5; alter database drop logfile group 6; alter database drop logfile group 7; SYS@TEST> alter database drop logfile group 4; Database altered. SYS@TEST> alter database drop logfile group 5; alter database drop logfile group 5 * ERROR at line 1: ORA-00313: open failed for members of log group 5 of thread 1 ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SYS@TEST> alter database drop logfile group 6; Database altered. SYS@TEST> alter database drop logfile group 7; Database altered. SYS@TEST> alter database drop logfile group 5; alter database drop logfile group 5 * ERROR at line 1: ORA-00313: open failed for members of log group 5 of thread 1 ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 5; ALTER DATABASE CLEAR LOGFILE GROUP 5 * ERROR at line 1: ORA-00350: log 5 of instance TEST (thread 1) needs to be archived ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log' SYS@TEST> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5; ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5 * ERROR at line 1: ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/oradgphy/standby_redo05.log' ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 5; ALTER DATABASE CLEAR LOGFILE GROUP 5 * ERROR at line 1: ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/oradgphy/standby_redo05.log' ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 SYS@TEST> alter database drop logfile group 5; Database altered. SYS@TEST> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------------- ------- ------- --------------------------------- --- 3 ONLINE /home/oracle/oradata/redo03.log NO 2 ONLINE /home/oracle/oradata/redo02.log NO 1 ONLINE /home/oracle/oradata/redo01.log NO

本文結束，感興趣的朋友可以自行測試不同情況下的還原。

任務調度 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。