Oracle11g ADG部署 duplicate方式（超级详细）

学无止境的小一

已于 2022-09-15 10:12:08 修改

阅读量3.6k

点赞数 2

文章标签：数据库服务器 oracle dba 数据库架构

于 2022-09-14 17:08:58 首次发布

本文链接：https://blog.csdn.net/weixin_52444902/article/details/126852044

版权

前言

在我们的工作中经常会遇到给现有的生产环境搭建ADG容灾系统的需求，此篇文章详细的教大家Oracle11g版本的ADG部署（duplicate方式）。内容都是工作中总结出来的，还算比较详细。

环境准备

准备两台虚拟机前期基础配置步骤省略
两台都安装好oracle11g软件配置好监听
其中一台作为主库，需安装好数据库并启动实例。

主库
主机名：dg1      
SID：orcl   
hosts文件：
192.168.1.217     dg1
备库
主机名：dg2      
SID：orcl    
hosts文件
192.168.1.218     dg2

主库配置

–主库设置强制日志，保证所有的操作都记录到日志文件
–查看当前force_logging的设置

SYS@orcl>select force_logging from v$database;

FORCE_LOG
---------
NO

–修改为强制日志

SYS@orcl>alter database force logging;

Database altered.

–检查主库的密码文件
同一个Data Guard 配置中所有数据库必须都拥有独立的密码文件，
并且必须保证同一个Data Guard 配置中所有数据库服务器的SYS 用户拥有相同密码以保证redo 数据的顺利传输，
因为redo传输服务通过认证的网络会话来传输redo 数据，而会话使用包含在密码文件中的SYS 用户密码来认证。
将主库的密码文件拷贝到备库的$ORACLE_HOME/dbs下

[oracle@dg1 dbs]$ cd $ORACLE_HOME/dbs
[oracle@dg1 dbs]$ scp orapworcl 192.168.1.218:$ORACLE_HOME/dbs
oracle@192.168.1.218's password:
orapworcl                                                                                                                   100% 1536     1.5KB/s   00:00

–给主库增加standby redo logfile
–查看日志组信息、大小

SYS@orcl>select GROUP#,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

    GROUP#     SIZE_M STATUS                                           ARCHIVED
---------- ---------- ------------------------------------------------ ---------
         1         50 INACTIVE                                         NO
         2         50 INACTIVE                                         NO
         3         50 CURRENT                                          NO

–查看redo日志的文件路径

col member for a60
set line 300
set pages 2000
SYS@orcl>select group#,status,member from v$logfile;

    GROUP# STATUS                MEMBER
---------- --------------------- ------------------------------------------------------------
         3 (null)                /u01/app/oracle/oradata/orcl/redo03.log
         2 (null)                /u01/app/oracle/oradata/orcl/redo02.log
         1 (null)                /u01/app/oracle/oradata/orcl/redo01.log

–添加standby redo logfile
主库不需要这个文件，当主库变成备库的时候才使用，个数一般是比现有日志组的数量+1 并且增加的日志组大小要和之前保持一致

SYS@orcl>alter database add standby logfile group 4 '/u01/app/oracle/oradata/orcl/redo04.log' size 50M;

Database altered.

SYS@orcl>alter database add standby logfile group 5 '/u01/app/oracle/oradata/orcl/redo05.log' size 50M;

Database altered.

SYS@orcl>alter database add standby logfile group 6 '/u01/app/oracle/oradata/orcl/redo06.log' size 50M;

Database altered.

SYS@orcl>alter database add standby logfile group 7 '/u01/app/oracle/oradata/orcl/redo07.log' size 50M;

Database altered.

–生产环境中主库不能停机，在线修改主库参数这里几个关键的容易弄混的name已经做好了标注

SYS@orcl>alter system set log_archive_config='DG_CONFIG=(orcl,stddb)';

System altered.
#这里的orcl和stddb是主备库的unique_name唯一名称 用来区分数据库
SYS@orcl>alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/archive valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=both;

System altered.
#这里填写主库本地归档日志路径  主库唯一名
SYS@orcl>alter system set log_archive_dest_2='SERVICE=stddb lgwr ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=stddb' scope=both;

System altered.
#这里的service是备库的tns文件中的名称  后面的db_unique_name是唯一名称
SYS@orcl>alter system set log_archive_dest_state_1 = enable scope=both;

System altered.

SYS@orcl>alter system set log_archive_dest_state_2 = enable scope=both;

System altered.

SYS@orcl>alter system set fal_server=stddb scope=both;

System altered.
#这里的指定切换对象 也是备库的tns文件中的名称
SYS@orcl>alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;

System altered.
#dg管理模式 自动

–创建pfile 并将pfile传到备库的相对应路径下

SYS@orcl>create pfile from spfile;

File created.

[oracle@dg1 dbs]$ scp initorcl.ora 192.168.1.218:$ORACLE_HOME/dbs
oracle@192.168.1.218's password:
initorcl.ora                                                                                                                100% 1090     1.1KB/s   00:00

备库配置

–编辑主库传过来的参数文件

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name=stddb
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_server=orcl
#指定切换对象  写主库tns文件中的名称
*.log_archive_config='DG_CONFIG=(orcl,stddb)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/archive valid_for=(all_logfiles,all_roles) db_unique_name=stddb'
#这里写备库的归档日志存放路径和备库的唯一名
*.log_archive_dest_2='SERVICE=orcl lgwr ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
#这里写主库的tns文件中的名称和唯一名
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#归档日志文件格式
*.LOG_ARCHIVE_MAX_PROCESSES=4
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
#数据文件路径转换 前面是主库的路径  后面是备库的路径
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
#数据文件路径转换 前面是主库的路径  后面是备库的路径
*.STANDBY_FILE_MANAGEMENT=AUTO
*.open_cursors=300
*.pga_aggregate_target=413138944
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1239416832
*.undo_tablespace='UNDOTBS1'

–备库创建参数文件中想对应的目录

[oracle@dg2 ~]$ mkdir /u01/app/oracle/oradata/orcl/ -pv
[oracle@dg2 ~]$ mkdir /u01/app/oracle/oradata/archive
[oracle@dg2 ~]$ mkdir /u01/app/oracle/admin/orcl/adump -pv

–备库通过pfile创建spfile并启动实例到nomount状态

[oracle@dg2 ~]$ export ORACLE_SID=orcl
[oracle@dg2 ~]$ sqlplus / as sysdba

SYS@orcl>create spfile from pfile;

File created.

SYS@orcl>startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             385875984 bytes
Database Buffers          838860800 bytes
Redo Buffers                8970240 bytes
SYS@orcl>

监听配置

--配置备库静态监听
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME =  /u01/app/oracle/product/11.2.0/dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.218)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
--配置主备库tnsname文件
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.217)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

STDDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.218)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

--tnsping测试互通性  主备库都测试一下
[oracle@dg1 admin]$ tnsping stddb

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-SEP-2022 16:48:15

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.218)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@dg1 admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-SEP-2022 16:48:16

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.217)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

–sqlplus连接测试

主库连接备库
[oracle@dg1 admin]$ sqlplus sys/oracle@stddb as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 14 16:49:44 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@stddb>

备库连接主库
[oracle@dg2 admin]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 14 16:50:13 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@orcl>

恢复备库 duplicate方式

--rman同时连接主库备库  进行恢复
[oracle@dg1 admin]$ rman target sys/oracle@orcl auxiliary sys/oracle@stddb

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Sep 14 16:51:39 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1641340200)
connected to auxiliary database: ORCL (not mounted)

RMAN>
RMAN> duplicate target database for standby from active database nofilenamecheck;
#nofilenamecheck 文件名不检测

–恢复完成后备库已经自动启动到mount状态

启动日志应用
SYS@orcl>alter database recover managed standby database disconnect from session;

Database altered.
查看日志应用情况
SYS@orcl>select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------------------------
        34 YES
        35 YES
        36 YES

停止日志应用并启动数据库到open状态
SYS@orcl>alter database recover managed standby database cancel;

Database altered.

SYS@orcl>alter database open;

Database altered.
启动实时日志应用
SYS@orcl>alter database recover managed standby database using current logfile disconnect from session;

Database altered.
查看同步时效
SYS@orcl>select name,value,TIME_COMPUTED,DATUM_TIME from v$dataguard_stats;

NAME                    VALUE         TIME_COMPUTED        DATUM_TIME
----------------------- ------------- -------------------- --------------------
transport lag           +00 00:00:00  09/14/2022 17:00:43  09/14/2022 17:00:42
apply lag               +00 00:00:00  09/14/2022 17:00:43  09/14/2022 17:00:42
apply finish time       (null)        09/14/2022 17:00:43  (null)
estimated startup time  7             09/14/2022 17:00:43  (null)
查看MRPO进程状态  ADG状态为APPLYING_LOG
SYS@orcl>select process,status from v$managed_standby;

PROCESS                     STATUS
--------------------------- ------------------------------------
ARCH                        CLOSING
ARCH                        CONNECTED
ARCH                        CLOSING
ARCH                        CLOSING
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
MRP0                        APPLYING_LOG