Oracle ADG搭建

孤木逢春

于 2023-09-22 10:35:59 发布

阅读量228

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_45420945/article/details/133160792

版权

Oracle ADG搭建

参考：Oracle DG搭建–rman.txt - 墨天轮文档 (modb.pro)

安装环境

主备库硬件最好一致。数据库版本一致

数据库版本：11g、19c

主库 192.168.236.146 数据库实例名：ORCL db_unique_name：ORCLPR

从库 192.168.236.147 数据库实例名：ORCL db_unique_name：ORCLST

主库需要安装数据库软件并且建库，备库仅需要安装数据库软件。

主备库的 .bash_profile 文件中 ORACLE_SID=ORCL

一、配置静态监听

1 配置静态监听

1.1 主库监听

cd /opt/oracle/oracle/product/11.2.0/dbhome_1/network/admin/
vim listener.ora
#添加以下内容
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLPR)
      (ORACLE_HOME = /opt/oracle/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.236.146)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /opt/oracle

1.2 备库监听

cd /opt/oracle/oracle/product/11.2.0/dbhome_1/network/admin/
vim listener.ora
#添加以下内容
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLST)
      (ORACLE_HOME = /opt/oracle/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.236.147)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /opt/oracle

2 主库备库使用同一个网络服务名

#主库
vim tnsnames.ora
#添加以下内容
ORCLPR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.236.146)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPR)
    )
  )

ORCLST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.236.147)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLST)
    )
  )
#备库 将主库的tnsnames.ora复制到备库
scp tnsnames.ora 192.168.236.147:/opt/oracle/oracle/product/11.2.0/dbhome_1/network/admin/

#测试
tnsping ORCLPR
tnsping ORCLST

二、主库DG相关参数配置

1 设置归档位置和主库角色

1.1 创建standbylog目录和archivelog目录

[oracle@adg1 ORCL]$ mkdir /opt/oracle/oradata/ORCL/standbylog
[oracle@adg1 ORCL]$ mkdir /opt/oracle/oradata/ORCL/archivelog

1.2 主库设置归档位置

SQL> startup mount
SQL> alter system set log_archive_dest_1='LOCATION=/opt/oracle/oradata/ORCL/archivelog valid_for=(all_logfiles, all_roles) db_unique_name=ORCLPR' scope=spfile;

2 设置归档位置和备库角色

SQL> alter system set log_archive_dest_2='SERVICE=ORCLST lgwr sync valid_for=(online_logfile, primary_role) db_unique_name=ORCLST' scope=spfile;

3 DG服务名配置

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCLPR, ORCLST)' scope=spfile;

4 设置归档可用

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

5 配置 FAL_SERVER

这个参数指定当日志传输出现问题时，备库到哪里去找缺少的归档日志。它用在备库接收的到的重做日志间有缺口的时候。

SQL> alter system set FAL_SERVER=ORCLST scope=spfile;
alter system set FAL_CLIENT=ORCLPR scope=spfile;

6 设置主库的 db_unique_name

SQL> alter system set db_unique_name=ORCLPR scope=spfile;

7 数据文件同步配置

如果需要在主库添加或者删除数据文件时，这些文件是否会在备库添加或删除，默认此参数是MANUAL手工方式（MANUAL不会同步到备库，AUTO自动同步）

SQL> alter system set standby_file_management='AUTO';

8关机启动创建pfile

SQL> shut immediate;
create pfile from spfile;

三、备库创建目录

1 创建目录

[oracle@adg2 admin]$ mkdir -p /opt/oracle/oradata/ORCL/standbylog
[oracle@adg2 admin]$ mkdir -p /opt/oracle/oradata/ORCL/archivelog
[oracle@adg2 admin]$ mkdir -p /opt/oracle/admin/ORCL/adump
[oracle@adg2 admin]$ mkdir -p /opt/oracle/fast_recovery_area/ORCL

四、主库创建standby日志

#创建
SQL> alter database add standby logfile group 11 '/opt/oracle/oradata/ORCL/standbylog/standby11.log' size 150M;
SQL> alter database add standby logfile group 12
'/opt/oracle/oradata/ORCL/standbylog/standby12.log' size 150M;
SQL> alter database add standby logfile group 13 '/opt/oracle/oradata/ORCL/standbylog/standby13.log' size 150M;
SQL> alter database add standby logfile group 14 '/opt/oracle/oradata/ORCL/standbylog/standby14.log' size 150M;
#查询
SQL> select * from v$standby_log;
SQL> select * from v$logfile;
#删除
SQL> alter database drop standby logfile group 11;

五、连接主库进行duplicate操作

1 主库备库开启监听

lsnrctl stop
lsnrctl start

2 把主库的pfile传到备库相应位置并修改

#主库
cd /opt/oracle/oracle/product/11.2.0/dbhome_1/dbs
#发送到备库
scp initORCL.ora 192.168.236.147:/opt/oracle/oracle/product/11.2.0/dbhome_1/dbs/
#备库修改参数
*.db_unique_name='ORCLST'
*.fal_client='ORCLST'
*.fal_server='ORCLPR'
*.log_archive_config='DG_CONFIG=(ORCLST, ORCLPR)'
*.log_archive_dest_1='LOCATION=/opt/oracle/oradata/ORCL/archivelog valid_for=(all_logfiles, all_roles) db_unique_name=ORCLST'
*.log_archive_dest_2='SERVICE=ORCLPR lgwr sync valid_for=(online_logfile, primary_role) db_unique_name=ORCLPR'

3 备库创建spfile文件

#备库
SQL> create spfile from pfile='/opt/oracle/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora';

4 备库创建密码文件（密码和主库一致）

[oracle@adg2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwORCL password=oracle entries=5
#19c 解决密码至少8位限制
orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle format=12 entries=5

5 备库启动到nomount状态

6 在primary端通过 rman duplicate 创建备库

[oracle@adg1 dbs]$ rman target sys/oracle@ORCLPR auxiliary sys/oracle@ORCLST [nocatalog]
RMAN> duplicate target database for standby from active database nofilenamecheck;

错误：

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 02-APR-23
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/02/2023 22:16:59
RMAN-05501: aborting duplication of target database
RMAN-06403: could not obtain a fully authorized session
RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

原因：备库实例名问题（大小写）

SQL> show parameter instance_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
instance_name			     string	 orcl

#指定实例名 （.bash_profile没有生效）
[oracle@adg2 dbs]$ export ORACLE_SID=ORCL

错误：

RMAN> duplicate target database for standby from active database nofilenamecheck;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/22/2023 09:44:17
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

原因：备库没有创建tns，将主库tnsnames.ora通过scp传到备库对应路径下

六、验证

#备库开启同步
SQL> alter database recover managed standby database disconnect from session;

#主库
SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /opt/oracle/oradata/ORCL/archivelog
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence	       19
#备库
SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /opt/oracle/oradata/ORCL/archivelog
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence	       19

如果主从库的当前日志序号一致，证明备库在跟着主库改变。至此，oracle adg 搭建完成。

七、在备库开启实时日志应用

#开启同步
SQL> alter database recover managed standby database disconnect from session;
#暂停同步
SQL> alter database recover managed standby database cancel;

19c 开启同步命令与11g有变化，但也兼容原先命令

#实时同步
alter database recover managed standby database disconnect;
#日志切换才同步
alter database recover managed standby database using archived logfile disconnect;
#取消同步
alter database recover managed standby database cancel;
#查看状态
select name,open_mode,database_role,protection_mode,protection_level from v$database;

DG Broker

DG Broker：实现一条命令完成DG切换

配置监听同ADG配置

#主备库都执行
SQL> alter system set dg_broker_start=true;

#在主库或者备库上执行
[oracle@adg1 ~]$ dgmgrl sys/oracle@ORCLPR
DGMGRL> help create

Creates a broker configuration

Syntax:

  CREATE CONFIGURATION <configuration name> AS
    PRIMARY DATABASE IS <database name>
    CONNECT IDENTIFIER IS <connect identifier>;

DGMGRL> create configuration dg as primary database is ORCLPR connect identifier is ORCLPR;

#增加备库到配置文件中
DGMGRL> help add
DGMGRL> add database ORCLST as connect identifier is ORCLST;
#启动配置文件
DGMGRL> enable configuration
#查看配置文件
DGMGRL> show configuration;
#查看某个数据库的配置消息
DGMGRL> show database verbose ORCLPR
DGMGRL> show database verbose ORCLST
#Broker切换
DGMGRL> switchover to ORCLST;
DGMGRL> show configuration

DG主备切换

Oracle DG主备切换_51CTO博客_oracle dg切换

ADG switch over切换步骤 - Nathan-wang - 博客园 (cnblogs.com)

主备切换的原因

（1）switch：用户主动切换；

（2）failover：主库出现故障，强行切换

switch 切换过程：先主库再备库

主库：v$database SWITCHOVER_STATUS 值的含义

NOT ALLOWED 当前的数据库不是带有备用数据库的主数据库
PREPARING DICTIONARY 该逻辑备用数据库正在向一个主数据库和其他备用数据库发送它的重做数据，以便为切换做准备
PREPARING SWITCHOVER 接受用于切换的重做数据时，逻辑备用配置会使用它
RECOVERY NEEDED 备用数据库还没有接收到切换请求
SESSIONS ACTIVE 在主数据库中存在活动的SQL会话；在继续执行之前必须断开这些会话
SWITCHOVER PENDING 适用于那些已收到主数据库切换请求但是还没有处理该请求的备用数据库
SWITCHOVER LATENT 切换没有完成并返回到主数据库
TO LOGICAL STANDBY 主数据库已经收到了来自逻辑备用数据库的完整的字典
TO PRIMARY 该备用数据库可以转换为主数据库

TO STANDBY 该主数据库可以转换为备用数据库

主库切换成备库

1 查看 SWITCHOVER_STATUS

SQL> select database_role, switchover_status from v$database;

DATABASE_ROLE	 SWITCHOVER_STATUS
---------------- --------------------
PRIMARY 	 SESSIONS ACTIVE

如果switchover_status为：to standby 则

alter database commit to switchover to physical standby;

否则用:

alter database commit to switchover to physical standby with session shutdown;

2 重启数据库

SQL> startup nomount

3 以备库mount

SQL> alter database mount standby database;

应用redo

SQL> alter database recover managed standby database using current logfile disconnect from session;

备库切换成主库

查看 SWITCHOVER_STATUS

SQL> select database_role, switchover_status from v$database;

DATABASE_ROLE	 SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY

如果switchover_status为：to primary 则

SQL> alter database commit to switchover to primary;

否则用：

alter database commit to switchover to primary with session shutdown;

孤木逢春

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle ADG搭建

如果需要在主库添加或者删除数据文件时，这些文件是否会在备库添加或删除，默认此参数是MANUAL手工方式（MANUAL不会同步到备库，AUTO自动同步）PREPARING DICTIONARY 该逻辑备用数据库正在向一个主数据库和其他备用数据库发送它的重做数据，以便为切换做准备。TO LOGICAL STANDBY 主数据库已经收到了来自逻辑备用数据库的完整的字典。NOT ALLOWED 当前的数据库不是带有备用数据库的主数据库。主库需要安装数据库软件并且建库，备库仅需要安装数据库软件。
复制链接

扫一扫