使用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小时内删除侵权内容。