Oracle Data Guard搭建——Physical Standby Database

文章说明

第一次完成了基础的物理备库搭建，便于后续回顾故写下该文章，有关DG的认知还很浅显，文章中不乏有许多的问题，若有错误或配置不完善的地方请大家指出，希望该文章能在大家搭建dg的过程中起到帮助。

环境信息说明

搭建步骤

一、确认主备物理环境信息

修改主数据库hosts文件，同时将该文件复制替换至备库

[root@orclpr ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.194   orclpr
192.168.1.195   orcldg

[root@orclpr ~]$ scp /etc/hosts root@orcldg:/etc/hosts

检查两台主机连接情况

# orclpr ping orcldg
[root@orclpr ~]# ping orcldg
PING orcldg (192.168.1.195) 56(84) bytes of data.
64 bytes from orcldg (192.168.1.195): icmp_seq=1 ttl=64 time=0.306 ms
64 bytes from orcldg (192.168.1.195): icmp_seq=2 ttl=64 time=0.397 ms
64 bytes from orcldg (192.168.1.195): icmp_seq=3 ttl=64 time=0.442 ms
64 bytes from orcldg (192.168.1.195): icmp_seq=4 ttl=64 time=0.500 ms
64 bytes from orcldg (192.168.1.195): icmp_seq=5 ttl=64 time=0.401 ms
……
--- orcldg ping statistics ---
7 packets transmitted, 7 received, 0% packet loss, time 5999ms
rtt min/avg/max/mdev = 0.306/0.406/0.500/0.056 ms

# orcldg ping orclpr
[root@orcldg ~]# ping orclpr
PING orclpr (192.168.1.194) 56(84) bytes of data.
64 bytes from orclpr (192.168.1.194): icmp_seq=1 ttl=64 time=0.317 ms
64 bytes from orclpr (192.168.1.194): icmp_seq=2 ttl=64 time=0.350 ms
64 bytes from orclpr (192.168.1.194): icmp_seq=3 ttl=64 time=0.438 ms
64 bytes from orclpr (192.168.1.194): icmp_seq=4 ttl=64 time=0.410 ms
64 bytes from orclpr (192.168.1.194): icmp_seq=5 ttl=64 time=0.319 ms

--- orclpr ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4000ms
rtt min/avg/max/mdev = 0.317/0.366/0.438/0.054 ms

检查防火墙状态，若为开启状态，建议关闭，也可放行1521端口。

# 主库防火墙状态
[root@orclpr ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Fri 2021-03-19 08:35:51 CST; 6 days ago
     Docs: man:firewalld(1)
  Process: 959 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS)
 Main PID: 959 (code=exited, status=0/SUCCESS)

Mar 17 19:31:15 orclpr systemd[1]: Starting firewalld - dynamic firewall daemon...
Mar 17 19:31:15 orclpr systemd[1]: Started firewalld - dynamic firewall daemon.
Mar 19 08:35:50 orclpr systemd[1]: Stopping firewalld - dynamic firewall daemon...
Mar 19 08:35:51 orclpr systemd[1]: Stopped firewalld - dynamic firewall daemon.

# 备库防火墙状态
[root@orcldg ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Fri 2021-03-19 08:35:38 CST; 6 days ago
     Docs: man:firewalld(1)
  Process: 975 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS)
 Main PID: 975 (code=exited, status=0/SUCCESS)

Mar 17 17:15:56 orcldg systemd[1]: Starting firewalld - dynamic firewall daemon...
Mar 17 17:15:59 orcldg systemd[1]: Started firewalld - dynamic firewall daemon.
Mar 19 08:35:37 orcldg systemd[1]: Stopping firewalld - dynamic firewall daemon...
Mar 19 08:35:38 orcldg systemd[1]: Stopped firewalld - dynamic firewall daemon.

检查主、备库SELinux，并关闭SELinux

[root@orclpr ~]# cat /etc/selinux/config 

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled  # 将该值改成disabled后重启服务器即可
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted 

二、配置主库日志模式

将主库模式修改为强制日志模式，为了防止部分直接路径插入而不记录日志的部分信息不能够传递到备库。

[oracle@orclpr dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 25 14:52:12 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0


SQL> alter database force logging;

三、配置静态监听

修改监听文件listener.ora，该文件默认位于"$ORACLE_HOME/network/admin"路径下(本文位置为/u01/app/oracle/product/12.2.3/db_1/network/admin/listener.ora)
根据自身数据库及主机信息修改相关参数即可。

# 修改主库监听文件，完成修改后重启监听服务。
[oracle@orclpr ~]$ cat /u01/app/oracle/product/12.2.3/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.3/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)  # 数据库全局名，单例数据库与实例名相同
      (SID_NAME = orcl)      # 数据库实例名
    )
  )

LISTENER = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orclpr)(PORT = 1521)) # 监听主机信息
  )

ADR_BASE_LISTENER = /u01/app/oracle

# 修改备库监听文件，完成修改后启动监听服务。
[oracle@orcldg ~]$ cat /u01/app/oracle/product/12.2.3/db_1/network/admin/listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.3/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (SID_NAME = orcl)
    )
  )

LISTENER = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

四、修改tnsname.ora文件

定义服务名，用于通过服务名连接数据库。
如下所示，修改tnsname.ora文件，同时将该文件复制到备库相同位置。

[oracle@orclpr ~]$ cat /u01/app/oracle/product/12.2.3/db_1/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.3/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCLPR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orclpr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )


[oracle@orclpr ~]$ scp tnsname.ora oracle@orcldg:/u01/app/oracle/product/12.2.3/db_1/network/admin/tnsnames.ora

五、传输密钥文件

将主库的秘钥文件传输至备库，主库与备库之间的通信需要使用秘钥文件进行认证。

[oracle@orclpr ~]$ scp /u01/app/oracle/product/12.2.3/db_1/dbs/orapworcl oracle@orcldg:/u01/app/oracle/product/12.2.3/db_1/dbs/orapworcl

六、主库创建standby log

使用standby log能够实现日志信息的实时应用，减少主备数据库之间的数据差异。
standby log的数量官方建议为：日志组数量+1*线程数，本例子中redo日志组数量为3，线程数为1，故创建standby log为4，standby大小建议与redo日志大小相同。

SQL> alter database add standby logfile group 8('/u01/app/oracle/oradata/ORCL/sredo08.log') size 200m;
SQL> alter database add standby logfile group 9('/u01/app/oracle/oradata/ORCL/sredo08.log') size 200m;
SQL> alter database add standby logfile group 10('/u01/app/oracle/oradata/ORCL/sredo08.log') size 200m;
SQL> alter database add standby logfile group 11('/u01/app/oracle/oradata/ORCL/sredo08.log') size 200m;

七、修改主库参数信息

将主库参数文件导出为文本文件，并修改相关参数信息。

[oracle@orclpr admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 25 15:59:25 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> create pfile='/tmp/prpfile.ora' from spfile;

File created.


# 如下修改参数文件信息
[oracle@orclpr admin]$ cat /tmp/prpfile.ora 
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=13824425984
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=0
orcl.__large_pool_size=67108864
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=5368709120
orcl.__sga_target=16106127360
orcl.__shared_io_pool_size=134217728
orcl.__shared_pool_size=2046820352
orcl.__streams_pool_size=0
orcl.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

#==============================begin:需要添加的配置信息===========================================
# 若备库的数据文件路径与主库不同，需要进行配置，形式为：'备库路径','主库路径'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl_stdby','/u01/app/oracle/oradata/ORCL'
# 若备库的在线日志文件路径与主库不同，需要进行配置，形式为：'备库路径','主库路径'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl_stdby','/u01/app/oracle/oradata/ORCL'
# 数据库唯一名，主备库不得相同
*.db_unique_name='orclpr'
*.FAL_CLIENT='orcldg'
*.FAL_SERVER='orclpr'
*.local_listener=''
# 主备库列表信息
*.log_archive_config='DG_CONFIG=(orclpr,orcldg)'
# 主备库传输的归档日志存放位置及相关权限设置
*.log_archive_dest_1='LOCATION=/u01/arch/
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
 DB_UNIQUE_NAME=orclpr'
*.log_archive_dest_2='SERVICE=orcldg lgwr SYNC
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 DB_UNIQUE_NAME=orcldg'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
#==============================end:需要添加的配置信息===========================================

*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=5114m
*.processes=480
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=15341m
*.STANDBY_FILE_MANAGEMENT='auto'
*.undo_tablespace='UNDOTBS1'

主要根据自身情况修改主备库的名称及数据文件、日志文件位置信息。
完成配置后，通过该文件重启数据库，并新建参数文件

# 关闭数据库
SQL> shutdown immediate;
# 使用pfile启动数据库
SQL> startup pfile=/tmp/prpfile.ora;
# 通过pfile重建spfile
SQL> create spfile from pfile='/tmp/prpfile.ora';

八、复制参数文件至备库，修改并启用

将主库参数文件传输至备库，修改相关参数信息。主要时为了在进行主备切换后，备库在转化为主库后能够再次形成主备关系。
修改的参数内容与上一节类似，只是当前的参数是以备库作为主库情况下进行的设置。

[oracle@orcldg ~]$ cat /tmp/ora_pfile 
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=13824425984
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=0
orcl.__large_pool_size=67108864
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=5368709120
orcl.__sga_target=16106127360
orcl.__shared_io_pool_size=134217728
orcl.__shared_pool_size=2046820352
orcl.__streams_pool_size=0
orcl.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.db_block_size=8192
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.db_unique_name='orcldg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

#===========================begin：修改内容说明===================================================
*.control_files='/u01/app/oracle/oradata/orcl_stdby/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl_stdby/control02.ctl'
*.FAL_CLIENT='orclpr'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/orcl_stdby'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/orcl_stdby'
*.FAL_SERVER='orcldg'
*.local_listener=''
*.STANDBY_FILE_MANAGEMENT=AUTO
*.log_archive_config='DG_CONFIG=(orcldg,orclpr)'
*.log_archive_dest_1='LOCATION=/u01/arch/
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
 DB_UNIQUE_NAME=orcldg'
*.log_archive_dest_2='SERVICE=orclpr
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 DB_UNIQUE_NAME=orclpr'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
#===========================end：修改内容说明===================================================

*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=5114m
*.processes=480
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=15341m
*.STANDBY_FILE_MANAGEMENT='auto'
*.undo_tablespace='UNDOTBS1'

完成修改后，启动数据库至nomount状态

SQL> startup nomount pfile=/tmp/prpfile.ora;

九、使用RMAN进行数据库复制

使用rman连接主、备库，复制主库相关文件至备库。

[oracle@orclpr dbs]$ rman target sys/Nhu_123456@orclpr auxiliary sys/Nhu_123456@orcldg
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 25 11:45:38 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1595238851)
connected to auxiliary database: ORCL (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

十、主备库信息检查

查询主库状态信息

SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME	  DATABASE_ROLE    PROTECTION_MODE	SWITCHOVER_STATUS
--------- ---------------- -------------------- -------------
ORCL	  PRIMARY	   MAXIMUM PERFORMANCE	TO STANDBY

查询备库状态信息

SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME	  DATABASE_ROLE    PROTECTION_MODE	SWITCHOVER_STATUS
--------- ---------------- -------------------- -------------
ORCLDG  PHYSICAL STANDBY MAXIMUM PERFORMANCE	NOT ALLOWED

开启备库，主库实施同步模式

开启备库

SQL> alter database open;

主库启用同步模式

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;  

功能验证

在主库新建数据表，并插入测试数据，查询备库是否正确同步信息。

SQL> create tablespace test datafile '/u01/app/oracle/oradata/ORCL/test01.dbf' size 5m;
SQL> create user tmpuser identified by tmpuser default tablespace test;
SQL> grant resource,connect,dba to tmpuser;
SQL> conn tmpuser/tmpuser;
SQL> create table tb_stu(
	id number(2),
	name varchar(10)
	);
SQL> insert into tb_stu values (1,'test');
SQL> commit;

完成提交后，前往备库，查看上述主库操作是否在备库已同样生效。