一步一步学会dataguard部署单机版（一）（My Cat Home）

本文档介绍redhat linux 6.5 Oracle 18C 单机环境下的基于dataguard搭建步骤。

Oracle企业版内建的工具，oracle8开始支持，名称为standby，oracle9i后改为dataguard（数据卫士），是数据库级别的灾备工具，11g以后性能增强为ADG(active dataguard)，支持备用站点只读和并行恢复操作。DataGuard基于Rman的备份和恢复技术，增加了主备站点间的归档自动传输和同步功能，配置了同步监控的管理接口。配置dataguard后系统自动同步，不需要人工操作。利用DataGuard做迁移，需要做的就是就备用站点切换为主站点。

优点：物理迁移、速度快、增量签约、自动同步，性能稳定，切换时间短。

缺点：需要较高的数据库技能、源和目标库相同的平台和数据库版本、需要在源库增加一些dataguard配置。

迁移流程：

①安装软件 ②配置dataguard ③自动归档同步 ④切换日切换系统 ⑤质量检查⑥业务系统复核。其中④--⑥为停机时间。

使用条件：大型数据库迁移、对停机时间有很较高要求。

IP规划

角色规划

18C为单机模式，CDB部署，文件系统 1
主库到备库的日志传输链路通常采用log_archive_dest_2参数。
备库到主库的日志传输链路log_archive_dest_2参数。

软件安装

主机操作系统安装、集群/数据库软件安装不在本文档讨论之列。以下详细描述在软件环境就绪的情况下，Active Dataguard备库的部署过程及SwitchOver主备库角色互换切换测试。

更新hosts文件

更新hosts文件如下： 192.168.100.120 db01 192.168.100.121 db02

主库启动归档

-----启动归档- 停止数据库 Shutdown immediate 启动一个节点到mount startup mount select log_mode from v$database; #####是否为归档模式 ALTER database force logging; #####强制归档 alter database archivelog; 启动两节点实例 select force_logging from v$database; YES

主库添加standby日志组

容量大小和现有redo保持一致，由于主备库需要进行SwitchOver角色切换，因此主备库均需创建standby redo（standby redo每个Thread的比主库redo数量多一组，大小保持一致）。查看当前redo select * from v$log; ALTER DATABASE ADD STANDBY LOGFILE group 5 '/oradata/ORCL/redostd05.log' size 50M; ALTER DATABASE ADD STANDBY LOGFILE group 6 '/oradata/ORCL/redostd06.log' size 50M; ALTER DATABASE ADD STANDBY LOGFILE group 7 '/oradata/ORCL/redostd07.log' size 50M; ALTER DATABASE ADD STANDBY LOGFILE group 8 '/oradata/ORCL/redostd08.log' size 50M; 查看standby redo是否创建成功 SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; GROUP# THREAD# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 5 0 0 YES UNASSIGNED 6 0 0 YES UNASSIGNED 7 0 0 YES UNASSIGNED 8 0 0 YES UNASSIGNED 6 rows selected.

拷贝主库密码件到从库

拷贝主库密码文件到备库主库： oracle@db01:/u02/app/oracle/product/18.1.0/dbhome_1/dbs>scp orapworcl oracle@db02:/u01/app/oracle/product/18.1.0/dbhome_1/dbs/orapworclstd

修改主库参数

su - oracle sqlplus / as sysdba 如下主库参数，当角色为主库的时候生效。 alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclstd)'; alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oradata/orcl_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'; alter system set LOG_ARCHIVE_DEST_2='SERVICE=orclstd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd'; 如下参数为备库角色参数--当转换为备库角色时候生效 alter system set FAL_SERVER=orclstd; alter system set FAL_CLIENT=orcl; alter system set DB_FILE_NAME_convert='/ORCLSTD/','/ORCL/' scope=spfile; #可以写绝对路径，路径不标准的，可以写相对路径，路径比较相似。如果有多个转换可以以逗号间隔相邻的两个是一组转换。注意斜线如果没有则前后都不需要。 alter system set LOG_FILE_NAME_CONVERT='/ORCLSTD/','/ORCL/' scope=spfile; alter system set standby_file_management=auto scope=both;

参数说明

从库参数文件创建

主库： Sqlplus / as sysdba Create pfile=’/home/oracle/pfile.ora’ from spfile; 修改pfile文件修改如下参数： oracle@db02:/u01/app/oracle/product/18.1.0/dbhome_1/dbs>cat initorclstd.ora *.audit_file_dest='/u01/app/oracle/admin/orclstd/adump' #修改 *.audit_trail='db' *.compatible='18.0.0' *.control_files='/oradata/ORCLSTD/control01.ctl','/oradata/ORCLSTD/control02.ctl' #修改 *.db_block_size=8192 *.db_file_name_convert='/ORCL/','/ORCLSTD/' #修改 *.db_name='orcl' *.db_unique_name='orclstd' #修改 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclstdXDB)' #修改 *.enable_pluggable_database=true *.fal_client='ORCLSTD' #修改 *.fal_server='ORCL' #修改 *.log_archive_config='DG_CONFIG=(orcl,orclstd)' *.log_archive_dest_1='LOCATION=/oradata/orcl_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclstd' #修改 *.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' #修改 *.log_file_name_convert='/ORCL/','/ORCLSTD/' #修改 *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=800m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2400m *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' 如上参数文件发送到备库$ORACLE_HOME/dbs目录下

mkdir -p /u01/app/oracle/admin/orclstd/adump

配置tns文件

su - oracle vi $ORACLE_HOME/network/admin/tnsnames.ora 主库从库连接串配置 orcl= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.100.120)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =orcl) ) ) orclstd = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.121)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =orclstd) ) )

配置静态监听

主库创建静态监听 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.120)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_listener= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=orcl) (SID_NAME=orcl) (ORACLE_HOME=/u02/app/oracle/product/18.1.0/dbhome_1) ) ) 从库创建静态监听 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.121)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_listener= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=orclstd) (SID_NAME=orclstd) (ORACLE_HOME=/u01/app/oracle/product/18.1.0/dbhome_1) ) ) 登陆测试： sqlplus sys/oracle@orcl as sysdba sqlplus sys/oracle@orclstd as sysdba

duplicate from active

在备库节点，使用Duplicate方式在线创建备库，Duplicate脚本如下： oracle@db02:/home/oracle>rman target sys/oracle@orcl auxiliary sys/oracle@orclstd Recovery Manager: Release 18.0.0.0.0 - Production on Fri May 13 08:47:03 2022 Version 18.3.0.0.0 copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1632375323) connected to auxiliary database: ORCL (not mounted) RMAN> duplicate target database for standby from active database dorecover nofilenamecheck; Starting Duplicate Db at 13-MAY-22 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=245 device type=DISK current log archived contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/u01/app/oracle/product/18.1.0/dbhome_1/dbs/orapworclstd' ; } executing Memory Script Starting backup at 13-MAY-22 deleted archived log archived log file name=/oradata/orcl_arch/1_7_1104594590.dbf RECID=1 STAMP=1104569389 deleted archived log archived log file name=/oradata/orcl_arch/1_8_1104594590.dbf RECID=2 STAMP=1104569390 Deleted 2 objects Finished Duplicate Db at 13-MAY-22

启动同步

oracle@db02:/home/oracle>sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Fri May 13 08:52:44 2022 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> alter database recover managed standby database disconnect from session; Database altered.

备库创建spfile

注意pfile路径，如果不在默认的dbs目录下，创建spfile需指定pfile的真实路径。 SQL> create spfile from pfile;

备库启动实时同步

在备库节点，开启实时日志应用 sqlplus / as sysdba alter database open; recover managed standby database using current logfile disconnect from session; 启动实时应用在备库节点，如要取消日志应用如下 - recover managed standby database cancel; 检查,当前同步状态 select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

查看全文