Oracle 19C Data Guard 单实例1+1部署（Duplicate方式）

环境描述

项目	主库	备库
操作系统	CentOS 7.9	CentOS 7.9
数据库版本	Oracle 19.3.0.0	Oracle 19.3.0.0
ORACLE_UNQNAME	his	hisdg
IP地址	10.172.1.101	10.172.1.102
Hostname	hisdb01	hisdb02
SID	his	his
db_name	his	his
db_unique_name	his	hisdg

说明

• 主库和备库建议采用相同服务器配置。
• 主库和备库建议采用相同操作系统版本。
• 主库和备库需要采用相同数据库版本（含 PSU）。

注：本次环境中主库和备库文件路径是保持一致的，如果不一致请注意修改。

主库配置

1. 查看是否归档模式

[oracle@hisdb01 dbs]$ sqlplus / as sysdba
SQL> archive log list;

如果没有归档，则执行以下步骤切换为归档模式：

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

2. 启动 FORCE_LOGGING 模式

SQL> alter database FORCE LOGGING;
SQL> select FORCE_LOGGING from v$database;
FOR
---
YES

3. 创建参数文件

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

4. 拷贝口令文件到备库

scp -P2222 orapwhis 10.172.1.102:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/

5. 修改 /etc/hosts

主备库都进行配置：

cat >> /etc/hosts <<EOF
10.172.1.101 hisdb01
10.172.1.102 hisdb02
EOF

6. 修改主库参数文件

修改生成的参数文件 /home/oracle/pfile.ora 文件，参考添加以下内容：

*.db_name='his'
*.db_unique_name='his'
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_config='dg_config=(his,hisdg)' 
*.log_archive_DEST_1='location=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=his'
*.LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hisdg'
*.db_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his'
*.log_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his'
*.LOG_ARCHIVE_DEST_STATE_1='enable'
*.LOG_ARCHIVE_DEST_STATE_2='enable'
*.standby_file_management='AUTO' 

如果不停机在线修改主库参数，参考如下：

alter system set db_unique_name='his' scope=spfile;
alter system set FAL_SERVER='standby' scope=both;
alter system set FAL_CLIENT='primary' scope=both;
alter system set db_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(his,hisdg)' scope=both;
alter system set log_archive_dest_1='LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=his' scope=both;
alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hisdg' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1='enable' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2='enable' scope=both;
alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=both;

7. 修改监听文件

主库编辑 listener.ora 文件，添加以下内容：

vi /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora

SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = his)(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)(SID_NAME = his)))

8. 修改 TNS文件

主库修改tnsnames.ora 追加以下内容：

primary =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.1.101)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = his)))standby =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.1.102)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = his)))

测试TNS:

tnsping primary
tnsping standby
sqlplus sys/oracle@primary as sysdba
sqlplus sys/oracle@standby as sysdba

备库配置

1. 创建必要的文件目录

手动建立以下目录，保持与主库目录一致：

$ cd $ORACLE_BASE
$ mkdir -p /u01/app/oracle/oradata/his
$ mkdir -p /u01/app/oracle/admin/his/adump
$ mkdir -p /u01/app/oracle/fast_recovery_area

2. 创建密码文件

主库和备库的 SYS 密码一致(前面已经从主库拷贝了)：

# 方式 1：使用 orapwd 设置相同密码：
orapwd file=orapwhis password=<password># 方式 2：将主库密码文件复制到备库 $ORACLE_HOME/dbs 目录下，并修改密码文件名为 orapwhis：
scp <主库密码文件> <备库用户>@<备库IP>:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwhis

注：参考主库配置中 4.拷贝口令文件到备库

3. 修改监听文件

编辑 /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora 文件，添加以下内容：

SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = his)(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)(SID_NAME = his)))

5. 修改 tnsnames.ora 文件

追加以下内容：

primary =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.1.101)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = his)))standby =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.1.102)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = his)))

测试TNS:

tnsping primary
tnsping standby
sqlplus sys/oracle@primary as sysdba
sqlplus sys/oracle@standby as sysdba

6. 修改备库参数文件并生成 spfile文件

通过从主库拷贝过来的参数文件，修改成备库参数文件，参考如下：

*.audit_file_dest='/u01/app/oracle/admin/his/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/his/control01.ctl','/u01/app/oracle/oradata/his/control02.ctl'
*.db_block_size=8192
*.db_name='his'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=7851m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hisXDB)'
*.log_archive_dest_1='LOCATION=/u01/archivelog'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=9639m
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=28917m
*.undo_tablespace='UNDOTBS1'# 添加如下内容
*.db_unique_name='hisdg'
*.service_names='his'
*.log_archive_config='dg_config=(his,hisdg)' 
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
*.log_archive_DEST_1='location=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hisdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=his'
*.db_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his'
*.log_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his'
*.LOG_ARCHIVE_DEST_STATE_1='enable'
*.LOG_ARCHIVE_DEST_STATE_2='enable'
*.standby_file_management='AUTO' 

通过新创建修改的pfile文件生成spfile文件

SQL> startup pfile='/home/oracle/pfile.ora' nomount;
SQL> create spfile from pfile='/home/oracle/pfile.ora';
SQL> shutdown immediate
-- 最后再重新启动到nomount
SQL> startup nomount;

7. 使用 Duplicate 创建物理 Standby

-- 接 RMAN 并连接辅助（auxiliary）实例，备库目前是 nomount 状态：
rman target sys/His#2021@primary auxiliary sys/His#2021@standby-- 执行复制：
duplicate target database for standby from active database nofilenamecheck DORECOVER;

8. 查询备库数据库所有文件

SQL> select status from v$instance;
SQL> select member from v$logfile;
SQL> select name from v$datafile;
SQL> select name from v$tempfile;
SQL> show parameter control

添加standby日志文件

1. 添加主库standby日志文件

新建4个日志组作为standby redolog 日志组，大小与原来的日志组一致：

alter database add standby logfile group 11 '/u01/app/oracle/oradata/his/standby_redo11.log' size 500m;
alter database add standby logfile group 12 '/u01/app/oracle/oradata/his/standby_redo12.log' size 500m;
alter database add standby logfile group 13 '/u01/app/oracle/oradata/his/standby_redo13.log' size 500m;
alter database add standby logfile group 14 '/u01/app/oracle/oradata/his/standby_redo14.log' size 500m;

2. 添加备库standby日志文件

alter database add standby logfile group 11 '/u01/app/oracle/oradata/his/standby_redo11.log' size 500m;
alter database add standby logfile group 12 '/u01/app/oracle/oradata/his/standby_redo12.log' size 500m;
alter database add standby logfile group 13 '/u01/app/oracle/oradata/his/standby_redo13.log' size 500m;
alter database add standby logfile group 14 '/u01/app/oracle/oradata/his/standby_redo14.log' size 500m;

在备库执行

备库打开数据库并实时应用

alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect; -- 简写

查看未应用的归档日志

-- 查询未应用的归档日志的最小和最大序列号，以及每个线程未应用的日志数量
select thread#, min(sequence#) no_applied_min, max(sequence#) no_applied_max, count(1) no_applied_all from v$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;-- 查询所有未应用的归档日志的详细信息
select thread#, sequence#, applied from v$archived_log where applied='NO' order by thread#, sequence#;

查看进程状态

select process, pid, status, sequence# from v$managed_standby;select name, open_mode, PROTECTION_MODE, DATABASE_ROLE from v$database;

验证主备同步

在主库执行生成一张表：

create table test(a varchar2(2), b date);
insert into test values('1', sysdate);
commit;

在备库查询：

select * from test;

切回主库删除测试表：

drop table test;

然后在主备库执行：

select * from test; -- 应报错则OK.

自动启动DataGuard脚本

创建 /etc/init.d/dataguard 启动脚本

脚本内容如下：

#!/bin/bash
# chkconfig: 2345 98 01
# description: Oracle database dataguard server
# Starts the oracle database dataguard server
# If more than four archive no application will be under the mout synchronization
# make SHELL: MUXINQNG
case $1 in
'start')if [ ! -f /var/lock/subsys/oracle ]; thenprog="listener"echo -n $"Starting $prog: "su - oracle -c "lsnrctl start" >> /var/log/oracle.logRETVAL=$?[ $RETVAL -eq 0 ] && echo "success" || echo "failed"prog="oracle dataguard"echo -n $"Starting $prog: "su - oracle -c "sqlplus /nolog" << EOF >> /var/log/oracle.logconnect / as sysdbastartup mount! sleep 3select name, open_mode, PROTECTION_MODE, DATABASE_ROLE from v\$database;select thread#, min(sequence#) no_applied_min, max(sequence#) no_applied_max, count(1) no_applied_all from v\$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;declare wyyn number;beginselect count(1) into wyyn from v\$archived_log where applied='NO' and standby_dest='NO';if wyyn < 5 thenEXECUTE IMMEDIATE 'alter database open';EXECUTE IMMEDIATE 'alter database recover managed standby database using current logfile disconnect';elseEXECUTE IMMEDIATE 'alter database recover managed standby database using current logfile disconnect';end if;end;/select name, open_mode, PROTECTION_MODE, DATABASE_ROLE from v\$database;select applied, count(1) from v\$archived_log where applied='NO' group by applied;exit
EOFRETVAL=$?[ $RETVAL -eq 0 ] && echo "success" || echo "failed"echo[ $RETVAL -eq 0 ] && touch /var/lock/subsys/oracle || RETVAL=1fi;;
'stop')prog="listener"echo -n $"Stopping $prog: "su - oracle -c "lsnrctl stop" >> /var/log/oracle.logRETVAL=$?[ $RETVAL -eq 0 ] && echo "success" || echo "failed"echoprog="oracle dataguard"echo -n $"Stopping $prog: "su - oracle -c "sqlplus /nolog" << EOF >> /var/log/oracle.logconnect / as sysdbaalter database recover managed standby database cancel;shutdown immediateexit
EOFRETVAL=$?[ $RETVAL -eq 0 ] && echo "success" || echo "failed"echo[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/oracle;;
'restart')prog="oracle dataguard"echo -n $"Stopping $prog: "$0 stopprog="oracle dataguard"echo -n $"Starting $prog: "$0 start;;
'status')su - oracle -c "lsnrctl status"su - oracle -c "sqlplus /nolog" << EOFconnect / as sysdbaselect name, open_mode, PROTECTION_MODE, DATABASE_ROLE from v\$database;select process, status from v\$managed_standby;exit
EOF;;
*)echo $"Usage: $0 {start|stop|restart|status}";;
esac

设置脚本权限：

chmod 775 /etc/init.d/dataguard

将脚本转换为 systemd 服务单元文件

创建一个 systemd 服务单元文件 /etc/systemd/system/dataguard.service，内容如下：

[Unit]
Description=Oracle database dataguard server
After=network.target[Service]
Type=forking
ExecStart=/etc/init.d/dataguard start
ExecStop=/etc/init.d/dataguard stop
User=root
Group=root
Restart=on-failure[Install]
WantedBy=multi-user.target

重新加载 systemd 配置：

systemctl daemon-reload

启用服务（开机自启）：

systemctl enable dataguard

手动启停dataguard服务：

systemctl start dataguard
systemctl stop dataguard
systemctl status dataguard

删除应用归档日志

创建定时任务和脚本：

crontab -l
00 01 * * * /u01/script/del_dg_arch.sh 

del_dg_arch.sh脚本内容：

#!/bin/bash
# delete dataguard applied archive log
# use database v$archived_log get applied archive log in os rmarchive_name=$(su - oracle -c "sqlplus -silent / as sysdba" <<EOF
set pagesize 0 feedback off verify off heading off echo off numwidth 4
select NAME from V\$ARCHIVED_LOG WHERE STATUS='A' AND APPLIED='YES' and registrar='RFS' and name is not null;
exit
EOF
)
result=$?
if [ $result -eq 0 ]; thenrecord=`echo $archive_name | wc -w`if [  $record -gt 0 ]; thenfor arch_list in $archive_namedoecho $arch_listrm -f $arch_list && echo 'delete archive succeed' || echo 'delete archive Failure'donefi
fi
su - oracle -c "rman target /" <<EOF
crosscheck archivelog all; 
delete noprompt expired archivelog all;
exit;
EOF