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

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

环境描述

说明

主库配置

1. 查看是否归档模式

2. 启动 FORCE_LOGGING 模式

3. 创建参数文件

4. 拷贝口令文件到备库

5. 修改 `/etc/hosts`

6. 修改主库参数文件

7. 修改监听文件

8. 修改 TNS文件

备库配置

1. 创建必要的文件目录

2. 创建密码文件

3. 修改监听文件

5. 修改 tnsnames.ora 文件

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

7. 使用 Duplicate 创建物理 Standby

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

添加standby日志文件

1. 添加主库standby日志文件

2. 添加备库standby日志文件

在备库执行

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

查看未应用的归档日志

查看进程状态

验证主备同步

自动启动DataGuard脚本

删除应用归档日志

相关资讯

热文排行

最新新闻

推荐新闻

热搜词

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

环境描述

说明

主库配置

1. 查看是否归档模式

2. 启动 FORCE_LOGGING 模式

3. 创建参数文件

4. 拷贝口令文件到备库

5. 修改 /etc/hosts

6. 修改主库参数文件

7. 修改监听文件

8. 修改 TNS文件

备库配置

1. 创建必要的文件目录

2. 创建密码文件

3. 修改监听文件

5. 修改 tnsnames.ora 文件

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

7. 使用 Duplicate 创建物理 Standby

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

添加standby日志文件

1. 添加主库standby日志文件

2. 添加备库standby日志文件

在备库执行

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

查看未应用的归档日志

查看进程状态

验证主备同步

自动启动DataGuard脚本

删除应用归档日志

相关资讯

热文排行

最新新闻

推荐新闻

热搜词

5. 修改 `/etc/hosts`