oracle18C (多租户模式) ADG部署流程

最新推荐文章于 2023-11-08 16:39:39 发布

想大声的死去

最新推荐文章于 2023-11-08 16:39:39 发布

阅读量802

点赞数 1

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/Natura_l/article/details/123983559

版权

系列文章目录

第一章 ADG基本知识
第二章 oracle18C ADG部署流程
第三章 oracle18c OGG级联模式（ADG模式级联 Downstream库+OGG18）

文章目录

系列文章目录
前言
一、环境说明
二、使用步骤
总结

前言

oracle18c ADG

提示：以下是本篇文章正文内容，下面案例可供参考

一、环境说明

oracle版本：Version 18.3.0.0.0

角色	IP	主机名	sid	global_names	service_names	db_unique_name
主库	192.168.103.31	pri	orcl	pri	pri	pri
备库	192.168.103.32	adg	orcl	adg	adg	pri

二、使用步骤

1.检查数据库各个参数

要保证每个实例的db_unique_name和global_name不同

SQL> show parameter db_unique_name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 pri
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
pri

2.配置网络，添加tnsnames字符串

cd $ORACLE_HOME/network/admin
vim tnsnames.ora 
##添加以下内容

PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.31)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pri)
    )
  )

ADG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.32)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = adg)
    )
  )

检查是否可达

[oracle@adg admin]$ tnsping pri

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 06-APR-2022 22:15:55

Copyright (c) 1997, 2018, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/18.3.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.31)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pri)))
OK (10 msec)
[oracle@adg admin]$ tnsping adg

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 06-APR-2022 22:15:57

Copyright (c) 1997, 2018, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/18.3.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.32)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = adg)))
OK (10 msec)

3.修改主库强制归档以及开启归档模式

开启归档模式：

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1577058176 bytes
Fixed Size		    8896384 bytes
Variable Size		  503316480 bytes
Database Buffers	 1056964608 bytes
Redo Buffers		    7880704 bytes
Database mounted.
SQL>      
SQL> alter database archivelog; 

Database altered.

SQL> alter database open;

Database altered.

开启强制归档
FORCE LOGGING模式可防止以非记录方式执行任何加载操作。这会减慢加载过程，因为加载的数据必须复制到重做日志中。

SQL> alter database force logging;

Database altered.

验证结果：

SQL> select log_mode,force_logging from v$database; 

LOG_MODE     FORCE_LOGGING
------------ ---------------------------------------
ARCHIVELOG   YES

此外，oracle18C添加的新的data gurd特性,它们可以执行非日志记录操作，同时可以使Active Data Guard备用数据库接收到所有数据，从而防止FORCE 方式生成大量重做日志导致性能下降。
STANDBY NOLOGGING FOR DATA AVAILABILITY 即 loading 操作的 commit 会被 delay，直到所有的 standby 都 apply data 为止。模式使加载操作通过自己与备用的连接将加载的数据发送到每个备用。提交会延迟，直到所有Active Data Guard备用数据库通过 recover 方式将数据应用完成。使用以下命令启用它：

SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;

STANDBY NOLOGGING FOR LOAD PERFORMANCE模式与先前的模式类似，不同之处在于，如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。（在 load 数据的时候，遇到网络瓶颈时，先不发送数据，这就保证了 loading 性能，但是丢失了数据，但是丢失的数据，会从 primary 中再次获取。）使用以下命令启用它：

SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;

此处选择第一种方式，忽略性能损失，保证数据的实时性。

SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;

Database altered.

4.配置重做传输认证

①、配置主库接收重做数据

两个原则：
每个备用重做日志文件必须至少与重做源数据库的重做日志中最大的重做日志文件一样大。为便于管理，Oracle 建议重做源数据库的重做日志和重做传输目的地的备用重做日志中的所有重做日志文件大小相同。
对于重做源数据库上的每个重做线程，备用重做日志必须至少比重做源数据库上的重做日志多一个重做日志组。
确定日志组和大小：

SQL> SELECT GROUP#, BYTES FROM V$LOG;

    GROUP#	BYTES
---------- ----------
	 1  209715200
	 2  209715200
	 3  209715200

日志组数量为3，大小为200M，则备用redo日志应该至少为4个200M的日志文件

SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;

Database altered.

SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

   GROUP#	BYTES
---------- ----------
    4  209715200
    5  209715200
    6  209715200
    7  209715200

②、设置主库初始化参数

使用文章开头说明的参数

角色	IP	主机名	sid	global_names	service_names	db_unique_name
主库	192.168.103.31	pri	orcl	pri	pri	pri
备库	192.168.103.32	adg	orcl	adg	adg	pri

查看数据库数据文件位置

SQL> select name from v$datafile;

NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_system_k3kfyoff_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_sysaux_k3kfzrkx_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_undotbs1_k3kg0kor_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_system_k3kg5tw5_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_sysaux_k3kg5tw4_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_users_k3kg0lro_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_undotbs1_k3kg5tw7_.dbf

配置主库参数

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,adg)' scope=both sid='*';         
alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=ds  ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)     DB_UNIQUE_NAME=ds' scope=both sid='*';
alter system set FAL_CLIENT='pri' scope=both sid='*';    
alter system set FAL_SERVER='ds' scope=both sid='*';  
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile' scope=spfile sid='*';  
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/ADG/onlinelog','/u01/app/oracle/oradata/orcl/ORCL/onlinelog' scope=spfile sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
alter system set PARALLEL_EXECUTION_MESSAGE_SIZE=8192 scope=spfile;
--备注：
--log_archive_dest_1这里写主库arch路径
--DB_FILE_NAME_CONVERT，LOG_FILE_NAME_CONVERT参数需要已'备库路径','主库路径'一组为准则

参数解析：

LOG_ARCHIVE_CONFIG：列出主备库上的DB_UNIQUE_NAME 参数。默认情况下，定义该参数能确保主备库数据库能够互相识别对方

LOG_ARCHIVE_DEST_1：本地归档路径。Primary与Standby需要定义各自的归档路径

LOG_ARCHIVE_DEST_2：设置redo log的传输方式(sync or async)以及传输目标(即standby apply node)，当前节点设置的均为另一端数据库的db_unique_name，并且其中的service的值需要与tnsnames.ora别名相同。

FAL_CLIENT：（Fetch Archive Log）用来解决归档裂缝，定义的客户端（获取日志的客户端），参数取自Oracle Net Service Name

FAL_SERVER：当主库转为备库的时候此参数会生效。通过网络向FAL_CLIENT发送缺失的日志，参数取自Oracle Net Service Name上述例子当rac转为备库时，会向std获取redo或者归档应用。

STANDBY_FILE_MANAGEMENT：当主库转为备库的时候此参数会生效。用来控制是否自动将Primary数据库增加表空间或数据文件的改动，传播到物理Standby数据库。AUTO：如果该参数值设置为AUTO，则Primary数据库执行的表空间创建操作也会被传播到物理Standby数据库上执行。
MANUAL：如果设置为MANUAL或未设置任何值（默认值是MANUAL），需要手工复制新创建的数据文件到物理Standby服务器。

DB_FILE_NAME_CONVERT：当主库转为备库的时候此参数会生效。主数据库和备用数据库的数据文件转换目录对映（如果两数据库的目录结构不一样），如果有多个对映，逐一指明对映关系。注意: primary上的该参数仅在主备switch over后生效,格式应保持一致

LOG_FILE_NAME_CONVERT：当主库转为备库的时候此参数会生效。定义主备log文件的存放路径转换

在主库上生成用于备库的参数文件

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

File created.

将主库的参数文件，密码文件拷贝到备库

[oracle@pri dbs]$ cd $ORACLE_HOME/dbs/
[oracle@pri dbs]$ ls
hc_orcl.dat  init.ora  lkORCL  lkPRI  orapworcl  spfileorcl.ora
[oracle@pri dbs]$ scp orapworcl 192.168.103.32:/u01/app/oracle/product/18.3.0/db_1/dbs
oracle@192.168.103.32's password: 
orapworcl                                 100% 3584     2.2MB/s   00:00

在备库上更改参数文件，修改参数以及创建目录结构

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,adg)' scope=both sid='*';         
alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=adg' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=pri  ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)     DB_UNIQUE_NAME=pri' scope=both sid='*';
alter system set FAL_CLIENT='adg' scope=both sid='*';    
alter system set FAL_SERVER='pri' scope=both sid='*';  
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile' scope=spfile sid='*';  
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/ORCL/onlinelog','/u01/app/oracle/oradata/orcl/ADG/onlinelog' scope=spfile sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
alter system set PARALLEL_EXECUTION_MESSAGE_SIZE=8192 scope=spfile;

关闭DS，启动到nomount状态

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1577058176 bytes
Fixed Size		    8896384 bytes
Variable Size		  503316480 bytes
Database Buffers	 1056964608 bytes
Redo Buffers		    7880704 bytes

使用rman传输备份文件（仅限小库）

[oracle@pri ~]$ rman target sys/oracle@pri auxiliary sys/oracle@adg

Recovery Manager: Release 18.0.0.0.0 - Production on Tue Apr 12 23:13:41 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=1628320479)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

这里需要设置备库的tnsname参数，修改备库的参数

ADG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.32)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = adg)
      (UR = A)
    )
  )

这里添加了 (UR = A)
再次尝试：

[oracle@pri admin]$ rman target sys/oracle@pri auxiliary sys/oracle@adg

Recovery Manager: Release 18.0.0.0.0 - Production on Tue Apr 12 23:16:45 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=1628320479)
connected to auxiliary database: ORCL (not mounted)

RMAN> 
执行脚本
run
{ 
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate AUXILIARY channel c7 type disk;
allocate AUXILIARY channel c8 type disk;
allocate AUXILIARY channel c9 type disk;
allocate AUXILIARY channel c10 type disk;
allocate AUXILIARY channel c11 type disk;
allocate AUXILIARY channel c12 type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
release channel c11;
release channel c12;
}

duplicate之后数据库处于mount状态
启动备库

SQL> alter database open;

Database altered.

DataGuard部署完毕之后，查询备库的状态

 SQL> select SWITCHOVER_STATUS,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM v$database;

SWITCHOVER_STATUS    DATABASE_ROLE    PROTECTION_MODE	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
NOT ALLOWED	     PHYSICAL STANDBY MAXIMUM PERFORMANCE  READ ONLY

二、DataGuard 数据库应用日志模式
Active DataGuard模式

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> SELECT NAME,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM V$DATABASE;

NAME	  DATABASE_ROLE    PROTECTION_MODE	OPEN_MODE
--------- ---------------- -------------------- --------------------
ORCL	  PHYSICAL STANDBY MAXIMUM PERFORMANCE	READ ONLY WITH APPLY

SQL> alter database recover managed standby database cancel; 
Database altered. 
SQL> select status from v$instance; 
STATUS ------------------------------------ MOUNTED 
SQL> alter database open read only; 
Database altered. 
SQL> alter database recover managed standby database using current logfile disconnect from session; 
Database altered.

总结

想大声的死去

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
oracle18C (多租户模式) ADG部署流程

系列文章目录提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加第一章 oracle18C ADG部署流程Oracle Data Guard 配置可以包含一个主数据库和多达 30 个目标。备用数据库是主数据库的事务一致副本。当主数据库是 CDB 时，代理配置中的所有备用数据库也必须是 CDB。备用数据库的类型如下：物理备用数据库提供物理上相同的主数据库副本，其磁盘上的数据库结构与主数据库逐块相同。包括索引在内的数据库模式是相同的。物理备用数据库通过重做应用与主数据库保持同步，
复制链接

扫一扫