使用RMAN Duplicate方式搭建Data guard Standby数据库

最新推荐文章于 2021-04-02 19:39:32 发布

leeryan522

最新推荐文章于 2021-04-02 19:39:32 发布

阅读量304

点赞数

分类专栏： Oracle Database 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/leeryan522/article/details/111354174

版权

Oracle Database 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

使用RMAN Duplicate方式搭建Data guard Standby数据库

前言
一、准备工作
二、创建Standby 数据库
三、小结

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

前言

本文主要介绍如何通过RMAN duplicate方式搭建Data guard Standby database。
使用该方式搭建备库需要考虑主库备库间网络带宽，及主库归档产生数量大小，时间等。
该方式主要优点是方便快捷，步骤较少。
缺点是依托网络，若出现网络抖动或其他错误导致复制中断需要重新进行复制。
另外，使用duplicate方式搭建备库，不能指定复制某个PDB，或跳过某个PDB。duplicate会针对主库所有数据库进行复制，包括CDB、PDB。
在12C和19C文档中均有说明：在这里插入图片描述

提示：以下是本篇文章正文内容，下面案例可供参考

一、准备工作

1.1 主库开启归档模式

以下为开启归档模式示例：
查看数据库归档状态

SQL> archive log list;
数据库日志模式             非存档模式     //目前不是归档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     27711
当前日志序列           27713

关闭数据库

SQL> shutdown immediate;

启动数据库至mount状态

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1.0737E+10 bytes
Fixed Size                  2038632 bytes
Variable Size            5486150808 bytes
Database Buffers         5234491392 bytes
Redo Buffers               14737408 bytes

启用归档模式

SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;

1.2主库启动force logging

alter database force logging;
select log_mode,force_logging from v$database;

1.3配置TNS、LISTENER_DG

TNS（需要在主备库都配置）：

###important !!! dataguard config entry do not modify without permission
PAASCDBDDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 135.10.150.236)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = paascdbddg)
    )
  )

PAASCDBDPR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 135.10.118.17)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = paascdbd)
    )
  )
###dataguard config end

LISTENER_DG：
Primary：

LISTENER_DG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 135.10.118.17 )(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

SID_LIST_LISTENER_DG =
    (SID_LIST =
       (SID_DESC =
          (ORACLE_HOME = /oracle/app/product/12.2/db )
          (GLOBAL_DBNAME = paascdbd)
          (SID_NAME = paascdbd1)
        )
      )

Standby：

LISTENER_DG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 135.10.150.236)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

SID_LIST_LISTENER_DG =
    (SID_LIST =
       (SID_DESC =
          (ORACLE_HOME = /oracle/app/product/12.2/db )
          (GLOBAL_DBNAME = paascdbddg)
          (SID_NAME = paascdbddg1)
        )
      )

1.4 准备密码文件

查看主库密码文件存放位置：

oracle@gapdb7:/home/oracle> srvctl config database -d paascdbd
Database unique name: paascdbd
Database name: 
Oracle home: /oracle/app/product/12.2/db
Oracle user: oracle
Spfile: +SDATADG1/PAASCDBD/PARAMETERFILE/spfile.1364.1050772611
Password file: +SDATADG1/PAASCDBD/PASSWORD/pwdpaascdbd.1304.1050626093
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: SDATADG1,SDATADG4A,SDATADG2,SDATADGHDS
Mount point paths: 
Services: gzyjhdb1,pdb4asj1,pdb4asjtb,pdb4ayj1,pdbboc1,pdbemos1,pdbgzbb1,pdbhdfx1,pdbjfwg1,pdbjfwgtb,pdbjsyh1,pdbjsyhtb,pdbnps1,pdbsjyzx1,pdbsmp1,pdbsmz1,pdbywtc1
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: dba
Database instances: paascdbd1,paascdbd2,paascdbd3,paascdbd4,paascdbd5,paascdbd6
Configured nodes: gapdb7,gapdb8,gapdb10,gapdb9,gapdb11,gapdb12
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed

拷贝主库密码文件至备库的ORACLE_HOME/dbs目录：

grid@gapdb7:/home/grid> asmcmd
ASMCMD> pwcopy +SDATADG1/PAASCDBD/PASSWORD/pwdpaascdbd.1304.1050626093 /tmp/orapwpaascdbddg1
copying +SDATADG1/PAASCDBD/PASSWORD/pwdpaascdbd.1304.1050626093 -> /tmp/orapwpaascdbddg1
ASMCMD> exit
grid@gapdb7:/home/grid> scp /tmp/orapwpaascdbddg1 oracle@135.10.150.236:/home/oracle/
 You have logged onto a secured server..All accesses logged 
oracle@135.10.150.236's password: 
orapwpaascdbddg1                                                                                                            100% 3584   851.5KB/s   00:00    
grid@gapdb7:/home/grid>

1.5 在主库创建Standby redo

查询主库当前redo分布情况：

查询主库当前redo大小：

创建Standby redo的原则是每个thread的redo数量比当前数量多1个，大小一致。参照上面的信息，standby redo创建命令如下：

alter database add standby logfile thread 1 group 40 '+SDATADG1' size 200m;
alter database add standby logfile thread 1 group 41 '+SDATADG1' size 200m;
alter database add standby logfile thread 1 group 42 '+SDATADG1' size 200m;
alter database add standby logfile thread 2 group 43 '+SDATADG1' size 2048m;
alter database add standby logfile thread 2 group 44 '+SDATADG1' size 2048m;
alter database add standby logfile thread 2 group 45 '+SDATADG1' size 2048m;
alter database add standby logfile thread 2 group 46 '+SDATADG1' size 2048m;
alter database add standby logfile thread 2 group 47 '+SDATADG1' size 2048m;
alter database add standby logfile thread 3 group 48 '+SDATADG1' size 2048m;
alter database add standby logfile thread 3 group 49 '+SDATADG1' size 2048m;
alter database add standby logfile thread 3 group 50 '+SDATADG1' size 2048m;
alter database add standby logfile thread 3 group 51 '+SDATADG1' size 2048m;
alter database add standby logfile thread 3 group 52 '+SDATADG1' size 2048m;
alter database add standby logfile thread 4 group 53 '+SDATADG1' size 2048m;
alter database add standby logfile thread 4 group 54 '+SDATADG1' size 2048m;
alter database add standby logfile thread 4 group 55 '+SDATADG1' size 2048m;
alter database add standby logfile thread 4 group 56 '+SDATADG1' size 2048m;
alter database add standby logfile thread 4 group 57 '+SDATADG1' size 2048m;
alter database add standby logfile thread 5 group 58 '+SDATADG1' size 2048m;
alter database add standby logfile thread 5 group 59 '+SDATADG1' size 2048m;
alter database add standby logfile thread 5 group 60 '+SDATADG1' size 2048m;
alter database add standby logfile thread 5 group 61 '+SDATADG1' size 2048m;
alter database add standby logfile thread 5 group 62 '+SDATADG1' size 2048m;
alter database add standby logfile thread 6 group 63 '+SDATADG1' size 2048m;
alter database add standby logfile thread 6 group 64 '+SDATADG1' size 2048m;
alter database add standby logfile thread 6 group 65 '+SDATADG1' size 2048m;
alter database add standby logfile thread 6 group 66 '+SDATADG1' size 2048m;
alter database add standby logfile thread 6 group 67 '+SDATADG1' size 2048m;

1.6 Primary 数据库参数修改

主库需要修改以下参数：

log_archive_config
log_file_name_convert
db_file_name_convert
standby_file_management
fal_client
fal_server
log_archive_dest_1
log_archive_dest_2

alter system set log_archive_config='dg_config=(paascdbd, paascdbddg)' scope=both;
alter system set log_file_name_convert='+DATA3','+SDATADG1','+DATA3','+SDATADG4A', '+DATA3','+SDATADGHDS', '+DATA3','+SDATADG2' scope=spfile;
alter system set db_file_name_convert='+DATA3','+SDATADG1','+DATA3','+SDATADG4A', '+DATA3','+SDATADGHDS', '+DATA3','+SDATADG2' scope=spfile;


alter system set standby_file_management='AUTO' scope=both;
alter system set fal_client=paascdbapr scope=both;
alter system set fal_server=paascdbadg scope=both;

alter system set log_archive_dest_1='location=+ARCHTEMP valid_for=(all_logfiles,all_roles)  db_unique_name=paascdba' scope=both;

alter system set log_archive_dest_2='service= paascdbadg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=paascdbadg' scope=both;
alter system set log_archive_dest_state_1=enable socpe=both;
alter system set log_archive_dest_state_2=enable socpe=both;

1.7 准备Standby 数据库Pfile

通过拷贝主库参数文件并根据备库实际情况修改的方法准备备库参数据文件

SQL>create pfile=’/tmp/forstandbypfile.ora’ from spfile;

pfile文件拷贝至备库$ORACLE_HOME/dbs目录
并参考备库SID重命名，例如备库SID为paascdbddg1

mv forstandbypfile.ora initpaascdbddg1.ora

修改备库参数文件：
db_file_name_convert、log_file_name_convert 需和主库参数对调，例如：
主库：

log_file_name_convert='+DATA3','+SDATADG1','+DATA3','+SDATADG4A', '+DATA3','+SDATADGHDS', '+DATA3','+SDATADG2'

则备库应调整为：

log_file_name_convert='+SDATADG1','+DATA3','+SDATADG4A', '+DATA3','+SDATADGHDS', '+DATA3','+SDATADG2','+DATA3'

参数log_archive_dest_1调整为当前备库归档存放位置，例如：

log_archive_dest_1='location=+ARCHDG valid_for=(all_logfiles,all_roles)  db_unique_name=paascdbddg'

参数log_archive_dest_2示例：

log_archive_dest_2='service=paascdbdpr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=paascdbd'

参数fal_server、fal_client需和主库参数对调，例如：

fal_server='PAASCDBDPR'
fal_client='PAASCDBDDG'

二、创建Standby 数据库

2.1 启动备库至nomount模式

% export ORACLE_SID=paascdbddg1
% sqlplus "/ as sysdba"
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initpaascdbdg1.ora

2.2 测试主备库连接正常

在备库执行：

rman target sys/system@paascdbdpr auxiliary sys/system@paascdbddg

2.3 开始复制

rman target sys/system@paascdbdpr auxiliary sys/system@paascdbddg <<EOF >/tmp/paascdba_duplicate.log
run
{
 allocate channel tgt1 device type disk ;
 allocate channel tgt2 device type disk ;
 allocate channel tgt3 device type disk ;
 allocate channel tgt4 device type disk ;
 allocate channel tgt5 device type disk ;
 allocate auxiliary channel aux1 device type disk ;
 allocate auxiliary channel aux2 device type disk ;
 allocate auxiliary channel aux3 device type disk ;
 allocate auxiliary channel aux4 device type disk ;
 allocate auxiliary channel aux5 device type disk ;
duplicate target database for standby from active database using compressed backupset;
release channel tgt1;
release channel tgt2;
release channel tgt3;
release channel tgt4;
release channel tgt5;
release channel aux1;
release channel aux2;
release channel aux3;
release channel aux4;
release channel aux5;
}
exit;
EOF

2.4 开启managed recovery

在备库执行：

SQL> alter database recover managed standby database disconnect from session;

比较主库last sequence，备库MRP (Managed Recovery Process) applying sequence

2.5 打开数据库为只读模式

在备库执行：

SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect;

检查主备库状态：