DG和ADG备库RMAN备份恢复

DG和ADG备库备份恢复测试

---

文档初衷：应对非银检查项，由于需要灾备机房也要有数据库测试，但是两地专线之间的带宽有限；故而领导要求备库备份在灾备本地恢复，呃(⊙﹏⊙)。

1. DG备库RMAN备份恢复(RAC和单实例都适用)

首先DG备库的状态是mount的状态，所以数据库本身的数据就是一致性的

备份脚本

rman target /
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup as compressed backupset FILESPERSET 10 database format '/oradata/rmanbak/FULL_%d_%U.full' section size 100G;
backup current controlfile  format '/oradata/rmanbak/standby_%U.ctl';
backup spfile format '/oradata/rmanbak/spfile_%d_%U.ora';
release channel c1;
release channel c2;
}

--将备份集复制到目标库中恢复

--只保留参数文件即可，数据文件、控制文件redo、undo等全删除，+ARCH的全删除

rm -rf CONTROLFILE DATAFILE(参数文件不删)

--恢复控制文件

-- 因为要恢复为主库，所以需要加上primary关键字

restore primary controlfile from '/oradata/rmanbak/rmanbak/cntrl_85_1_1130454364';

--清除备份信息并重新注册

-- 清除之前的备份信息
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 '/oradata/rmanbak/rmanbak';

--恢复数据文件并重定向

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
SET NEWNAME FOR DATABASE TO '/oradata/testdg/%b';
restore database;
switch datafile all;
release channel c1;
release channel c2;
}

--clear 磁盘组

alter database clear logfile group;

--激活备库

alter database recover managed standby database cancel;    --取消日志应用
 
shutdown immediate
 
startup mount;
 
alter database activate standby database;
 
alter database flashback off;
 
alter database noarchivelog;
 
alter database open;
 
select open mode,database role from v$database;
 
--如果激活备库报错
 
SQL alter database activate standby databasealter database activate standby database
 
ERROR at line 1:
 
ORA-00313open failed for members or log group 1of thread 1
 
ORA-00312online log 1 thread1:+DATA DG/FTPDMX01/ONLINELOG/group_1.577.1050100085
 
ORA-17503:ksfdopn:2 Failed to open file+DATA DG/MX01/ONLINELOG/group_1.577.1050100085
 
ORA-15012:ASM file
 
+DATA DG/MX01/ONLINELOG/group 1.577.1050100085' does not exist
 
----删除standby logfile再激活
 
set pagesize 9999
 
select 'alter database clear logfile group ‘||group#||'from vSstandby_log;
 
select 'alter database drop standby logfile group"||group#||;' from v$standby_log;
 
alter database flashback off;
 
alter database activate standby database;
 
alter database noarchivelog:
 
alter database open;
 
select open_mode,database role from v$database

2. ADG备库RMAN备份恢复(RAC和单实例都适用)

2.1 ADG备库的状态为open read only,备份脚本如下

----ADG备库备份脚本
rman target /
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup as compressed backupset FILESPERSET 10 database format '/oradata/rmanbak/FULL_%d_%U.full' section size 100G;
backup as compressed backupset archivelog from time 'sysdate-1' format '/oradata/rmanbak/ARC_%d_%U.arc' section size 100G;
backup current controlfile  format '/oradata/rmanbak/standby_%U.ctl';
backup spfile format '/oradata/rmanbak/spfile_%d_%U.ora';
release channel c1;
release channel c2;
}
---主库脚本切换归档时间最好在备份时间之后
主库做切换归档操作:alter system archive log current;
---备库再次备份归档，在主库切换归档之后
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
BACKUP FILESPERSET 20 FORMAT '/oradata/rmanbakarch_%s_%p_%t' ARCHIVELOG ALL;
release channel c1;
release channel c2;
}

2.2 开始异机恢复数据库

pfile文件中去掉DG参数并创建对应的文件目录并授权。目标数据库启动到nomount状态，

startup nomount pfile=’xxxx’;

恢复控制文件

-- 因为要恢复为主库，所以需要加上primary关键字
 
restore primary controlfile from '/oradata/rmanbak/rmanbak/cntrl_85_1_1130454364';

2.3 清除之前的备份信息并重新注册

-- 清除之前的备份信息
 
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 '/oradata/rmanbak/rmanbak';

2.4 恢复数文件并重定向

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
SET NEWNAME FOR DATABASE TO '/oradata/testdg/%b';
restore database;
switch datafile all;
release channel c1;
release channel c2;
}

2.5 恢复归档日志

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
RESTORE ARCHIVELOG ALL;
release channel c1;
release channel c2;
}

2.6 还原数据库

--查看最新的归档日志序列号，以此序列号为还原点还原数据库
list backupset of archivelog from time "sysdate - 1";  
 
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
set until sequence 486 thread 1;
recover database;
release channel c1;
release channel c2;
}

2.7 激活备库为主库，如果已经是主库就跳过该步骤

-- 2、激活备库为主库，如果已经是主库了，那就跳过该步骤
alter database activate standby database;
--如果激活报错清除和删除redo日志文件组并重新激活
-- ALTER DATABASE CLEAR LOGFILE GROUP 4;
-- alter database drop logfile group 4;

2.8 打开数据库

--如果recover database将所需归档日志全部应用完毕则直接打开数据库
alter database open;
--如果recover database提示还缺失归档日志时则需要不完全恢复数据库
alter database open resetlogs;

撰写不易，请转发时著名文章出处哦！感谢！##