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;