- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

如何利用DG环境的备库来异机还原一个新主库

小麦苗DB宝发表于 2022/02/21 12:52:04 2022/02/21

【摘要】 DG环境介绍一、备库执行备份二、在新主机执行恢复操作 2.1、恢复spfile 2.2、恢复控制文件 2.3、注册备份信息 2.4、还原数据文件 2.5、恢复数据库 2.6、激活备库为主库，并启动数据库在部署完ADG（Active Data Guard）后，不但可以将只读的查询交给备库执行，还可以把日常的数据库备份工作放在备库上执行，从而减轻主库的压力，充分的发挥服务器资源。本文演示的...

在部署完ADG（Active Data Guard）后，不但可以将只读的查询交给备库执行，还可以把日常的数据库备份工作放在备库上执行，从而减轻主库的压力，充分的发挥服务器资源。

本文演示的是利用Oracle DG环境的备库执行备份，然后异机还原恢复成一个新的主库的过程。

DG环境介绍

项目	primary db	physical standby db	新库
数据库类型（rac或单实例）	单实例	单实例	单实例
数据库版本	11.2.0.3.0	11.2.0.3.0	11.2.0.3.0
platform_name	Linux x86 64-bit	Linux x86 64-bit	Linux x86 64-bit
ORACLE_SID	oradg11g	oradgphy	LHRDB
db_name/GLOBAL_DBNAME	oradg11g	oradg11g	oradg11g
db_unique_name	oradg11g	oradgphy	LHRDB
ORACLE_HOME	/u01/app/oracle/product/11.2.0/dbhome_1	/u01/app/oracle/product/11.2.0/dbhome_1	/u01/app/oracle/product/11.2.0/dbhome_1

一、备库执行备份

cat > /rman/rman_backup_oradgphy_full.sh <<"EOF0"
#!/bin/ksh

export ORACLE_SID=oradgphy
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

MYDATE=`date +'%Y%m%d%H%M%S'`

rman target /  log /rman/backup_oradgphy_full_$MYDATE.log append <<EOF  
run {
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
sql 'alter session set NLS_LANGUAGE="AMERICAN"';
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
allocate channel c9 type disk;
allocate channel c10 type disk;
allocate channel c11 type disk;
allocate channel c12 type disk;

backup as compressed backupset FILESPERSET 10 database format '/rman/FULL_%d_%U.full' section size 100G;

backup as compressed backupset archivelog from time 'sysdate-1' format '/rman/ARC_%d_%U.arc' section size 100G;

backup current controlfile  format '/rman/standby_%U.ctl';

backup spfile format '/rman/spfile_%d_%U.ora';

release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
release channel c11;
release channel c12;
 }
EOF
EOF0

赋予可执行权限：

chmod +x /rman/rman_backup_oradgphy_full.sh

执行备份，或添加定时任务：

nohup sh /rman/rman_backup_oradgphy_full.sh &

备份结果：数据库790G（非分配大小），压缩备份后140G，用时30分钟。

接下来就是把/rman备份目录下的内容都拷贝到新主机上，方法很多，例如scp、磁盘卸载重新挂载等。

二、在新主机执行恢复操作

2.1、恢复spfile

export ORACLE_SID=LHRDB
rman target /
startup nomount;
restore spfile to pfile '?/dbs/initLHRDB.ora' from '/rman/spfile_ORADG11G_3lv6cd9m_1_1.ora';

-- 修改pfile，去除dg相关参数
vi $ORACLE_HOME/dbs/initLHRDB.ora

需要根据情况对pfile做相关的修改，最终的参数文件内容：

*.audit_file_dest='/u01/app/oracle/admin/LHRDB/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/LHRDB/crontal01.ctl','/u01/app/oracle/oradata/LHRDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oradg11g'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='LHRDB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oradg11gXDB)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.memory_max_target=209715200
*.memory_target=209715200
*.open_cursors=300
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

根据pfile文件内容创建相关目录

mkdir -p /u01/app/oracle/admin/LHRDB/adump
mkdir -p /u01/app/oracle/oradata/LHRDB/

根据pfile创建spfile，并启动到nomout

create spfile from pfile;
startup force nomount

2.2、恢复控制文件

-- 因为要恢复为主库，所以需要加上primary关键字
restore primary controlfile from '/rman/standby_3kv6cd9k_1_1.ctl';

2.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 '/rman/';

2.4、还原数据文件

# 创建日志路径
mkdir -p /home/oracle/rman_log/
chown oracle.dba /home/oracle/rman_log/


cat > /home/oracle/rman_restore_LHRDB.sh <<"EOF0"
#!/bin/ksh

export ORACLE_SID=LHRDB
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

MYDATE=`date +'%Y%m%d%H%M%S'`
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

rman target /  log /home/oracle/rman_log/rman_restore_LHRDB_$MYDATE.log append <<EOF  
run {
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
sql 'alter session set NLS_LANGUAGE="AMERICAN"';
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
allocate channel c9 type disk;
allocate channel c10 type disk;
allocate channel c11 type disk;
allocate channel c12 type disk;

SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/LHRDB/%b';
restore database;
switch datafile all;

release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
release channel c11;
release channel c12;
}
EOF
EOF0

开始后台执行还原：

chmod +x /home/oracle/rman_restore_LHRDB.sh 
nohup sh /home/oracle/rman_restore_LHRDB.sh &

还原时间大概2小时。

2.5、恢复数据库

首先使用如下命令找到最大的日志序列号：

list backupset of archivelog from time "sysdate - 1";

准备恢复数据库的脚本：

cat > /home/oracle/rman_recover_LHRDB.sh <<"EOF0"
#!/bin/ksh

export ORACLE_SID=LHRDB
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

MYDATE=`date +'%Y%m%d%H%M%S'`
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

rman target /  log /home/oracle/rman_log/rman_recover_LHRDB_$MYDATE.log append <<EOF
run{
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
sql 'alter session set NLS_LANGUAGE="AMERICAN"';
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
allocate channel c9 type disk;
allocate channel c10 type disk;
allocate channel c11 type disk;
allocate channel c12 type disk;

set until sequence 230 thread 1;
recover database;

release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
release channel c11;
release channel c12;
}
EOF
EOF0

开始后台执行恢复：

chmod +x /home/oracle/rman_recover_LHRDB.sh 
nohup sh /home/oracle/rman_recover_LHRDB.sh &

恢复时间大概30分钟。

2.6、激活备库为主库，并启动数据库

-- 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;

结果：

SYS@LHRDB > alter database open resetlogs;

Database altered.

SYS@LHRDB > 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

SYS@LHRDB > select open_mode , database_role, flashback_on from v$database;

OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
-------------------- ---------------- ------------------
READ WRITE           PRIMARY          NO

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入