创建ADG

2024-02-10 13:48
文章标签 创建 adg

本文主要是介绍创建ADG，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、主库单实例，备库单实例

主库（silentdg1）：10.107.173.11
备库（silentdg2）：10.107.173.12（只有oracle软件）
主备实例名：orcl
os：centos7.5minimal
oracle版本：11.2.0.4

1、pri端和sty端配置静态监听和tns， 主备启动监听 并且都测试监听
主库编写listener.ora，然后lsnrctl start启动监听

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.107.173.11)(PORT=1521))(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=orcl)(ORACLE_HOME=/u01/app/oracle/product/11.2.0/)(SID_NAME=orcl))(SID_DESC=(SID_NAME=plsextproc)(ORACLE_HOME=/u01/app/oracle/product/11.2.0/)(PROGRAM=extproc)))

主备库准备tnsnames.ora内容是一样的

pritns =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.107.173.11)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))stytns =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.107.173.12)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))

主备库使用tnsping互相测试监听（略）
2.修改primary端初始化参数文件

mkdir /u01/app/archivelog
sqlplus  / as sysdba
shu immediate
startup mount;  
alter database archivelog;  
alter database force logging;  
alter database open;
alter system set db_unique_name = priuni scope=spfile;
alter system set log_archive_config = 'DG_CONFIG=(priuni,styuni)' scope=spfile;
alter system set log_archive_dest_1 = 'LOCATION=/u01/app/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=priuni' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=stytns LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=styuni' scope=spfile;
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set fal_server=stytns scope=spfile;
alter system set fal_client=pritns scope=spfile;
alter system set standby_file_management=AUTO scope=spfile;
shu immediate
startup(重启为了使上面的参数生效)

3、在primary端生成pfile参数文件和密码文件，并且拷贝到standby段相应位置
主库：

orapwd file=orapworcl password=oracle force=y

create pfile from spfile;  

cd $ORACLE_HOME/dbs

把主库的orapworcl和initorcl.ora传到备库

cd $ORACLE_BASE
scp -r diag/ 10.107.173.12:/u01/app/oracle

备库创建pfile中涉及到的目录（略）
备库操作：

mkdir /u01/app/archivelog
sqlplus / as sysdba  
create spfile from pfile;  
startup nomount;
alter system set db_unique_name = styuni scope = spfile;
alter system set log_archive_config = 'DG_CONFIG=(priuni,styuni)' scope = spfile;
alter system set log_archive_dest_1 = 'LOCATION=/u01/app/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=styuni' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=pritns LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=priuni' scope=spfile;
alter system set fal_server=pritns scope=spfile;
alter system set fal_client=stytns scope=spfile;
shu abort
startup nomount

5.在primary端通过Rman Duplicate创建备库,在主库上执行如下命令

[oracle@silentdg1 ~]$ rman target sys/oracle@pritns auxiliary sys/oracle@stytns nocatalogRecovery Manager: Release 11.2.0.4.0 - Production on Mon May 17 14:45:38 2021Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (DBID=1600326864)
using target database control file instead of recovery catalog
connected to auxiliary database: ORCL (not mounted)RMAN> duplicate target database for standby from active database nofilenamecheck;Starting Duplicate Db at 17-MAY-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=189 device type=DISKcontents of Memory Script:
{backup as copy reusetargetfile  '/u01/app/oracle/product/11.2.0/dbs/orapworcl' auxiliary format '/u01/app/oracle/product/11.2.0/dbs/orapworcl'   ;
}
executing Memory ScriptStarting backup at 17-MAY-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
Finished backup at 17-MAY-21contents of Memory Script:
{backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/orcl/control01.ctl';restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' from '/u01/app/oracle/oradata/orcl/control01.ctl';
}
executing Memory ScriptStarting backup at 17-MAY-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbs/snapcf_orcl.f tag=TAG20210517T144636 RECID=1 STAMP=1072795596
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-MAY-21Starting restore at 17-MAY-21
using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 17-MAY-21contents of Memory Script:
{sql clone 'alter database mount standby database';
}
executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script:
{set newname for tempfile  1 to "/u01/app/oracle/oradata/orcl/temp01.dbf";switch clone tempfile all;set newname for datafile  1 to "/u01/app/oracle/oradata/orcl/system01.dbf";set newname for datafile  2 to "/u01/app/oracle/oradata/orcl/sysaux01.dbf";set newname for datafile  3 to "/u01/app/oracle/oradata/orcl/undotbs01.dbf";set newname for datafile  4 to "/u01/app/oracle/oradata/orcl/users01.dbf";backup as copy reusedatafile  1 auxiliary format "/u01/app/oracle/oradata/orcl/system01.dbf"   datafile 2 auxiliary format "/u01/app/oracle/oradata/orcl/sysaux01.dbf"   datafile 3 auxiliary format "/u01/app/oracle/oradata/orcl/undotbs01.dbf"   datafile 4 auxiliary format "/u01/app/oracle/oradata/orcl/users01.dbf"   ;sql 'alter system archive log current';
}
executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 17-MAY-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u01/app/oracle/oradata/orcl/system01.dbf tag=TAG20210517T144646
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf tag=TAG20210517T144646
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf tag=TAG20210517T144646
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/orcl/users01.dbf tag=TAG20210517T144646
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-MAY-21sql statement: alter system archive log currentcontents of Memory Script:
{switch clone datafile all;
}
executing Memory Scriptdatafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1072795630 file name=/u01/app/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1072795630 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1072795630 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1072795630 file name=/u01/app/oracle/oradata/orcl/users01.dbf
Finished Duplicate Db at 17-MAY-21

6.在primary 和standby端添加standby日志
主库：

select status from v$instance;
STATUS
------------
OPENSELECT a.member,bytes/1024/1024FROM v$logfile a,v$log bWHERE a.group# = b.group#;MEMBER						   				  BYTES/1024/1024
--------------------------------------------- ---------------
/u01/app/oracle/oradata/orcl/redo03.log 			50
/u01/app/oracle/oradata/orcl/redo02.log 			50
/u01/app/oracle/oradata/orcl/redo01.log 			50alter database add standby logfilegroup 4 ('/u01/app/oracle/oradata/orcl/styredo04.log') size 300m,group 5 ('/u01/app/oracle/oradata/orcl/styredo05.log') size 300m,group 6 ('/u01/app/oracle/oradata/orcl/styredo06.log') size 300m,group 7 ('/u01/app/oracle/oradata/orcl/styredo07.log') size 300m;SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------4	    0	       0 YES UNASSIGNED5	    0	       0 YES UNASSIGNED6	    0	       0 YES UNASSIGNED7	    0	       0 YES UNASSIGNED

备库操作：

select status from v$instance;STATUS
------------
MOUNTEDalter database add standby logfilegroup 4 ('/u01/app/oracle/oradata/orcl/styredo04.log') size 300m,group 5 ('/u01/app/oracle/oradata/orcl/styredo05.log') size 300m,group 6 ('/u01/app/oracle/oradata/orcl/styredo06.log') size 300m,group 7 ('/u01/app/oracle/oradata/orcl/styredo07.log') size 300m;Database altered.SQL> select member from v$logfile;MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/flash_recovery_area/STYUNI/onlinelog/o1_mf_3_jb44cgs0_.log
/u01/app/oracle/flash_recovery_area/STYUNI/onlinelog/o1_mf_2_jb44cgno_.log
/u01/app/oracle/flash_recovery_area/STYUNI/onlinelog/o1_mf_1_jb44cgjg_.log
/u01/app/oracle/oradata/orcl/styredo04.log
/u01/app/oracle/oradata/orcl/styredo05.log
/u01/app/oracle/oradata/orcl/styredo06.log
/u01/app/oracle/oradata/orcl/styredo07.log

7.在standby端开启实时日志应用

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

开始测试ADG
8、执行日志切换测试（在pri端切换归档，在节点二上检查是否也发生了切换）
–primary执行日志切换

archive log list;
Current log sequence	       21alter system switch logfile;archive log list;
Current log sequence	       22

–standby查看日志的sequence号也跟着变了

archive log list;
Current log sequence	       22

9、查看standby启动的DG进程

select process,client_process,sequence#,status from v$managed_standby;PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH	  ARCH		    0 CONNECTED		--归档进程  
ARCH	  ARCH		    0 CONNECTED
ARCH	  ARCH		    0 CONNECTED
ARCH	  ARCH		    0 CONNECTED
RFS	  ARCH		    0 IDLE				--归档传输进程
RFS	  UNKNOWN	    0 IDLE
RFS	  LGWR		   22 IDLE
RFS	  UNKNOWN	    0 IDLE
MRP0	  N/A		   22 APPLYING_LOG	--日志应用进程9 rows selected.

10、查看数据库的保护模式
#primary 端查看，我们可以看到数据库的保护模式为最大性能

select database_role,protection_mode,protection_level,open_mode from v$database;DATABASE_ROLE	 PROTECTION_MODE      PROTECTION_LEVEL	   OPEN_MODE
---------------- -------------------- -------------------- --------------------
PRIMARY 	 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  READ WRITE

#standby端查看

select database_role,protection_mode,protection_level,open_mode from v$database;DATABASE_ROLE	 PROTECTION_MODE      PROTECTION_LEVEL	   OPEN_MODE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  MOUNTED

11.查看DG的日志信息

set line 200
col MESSAGE for a80
select * from v$dataguard_status;
--查看应用延迟情况
select * from v$dataguard_stats;

12.Open Read Only standby数据库并且开启实时日志应用
备库

shutdown immediate  
startup  
select OPEN_MODE from v$database ;OPEN_MODE
--------------------
READ ONLYalter database recover managed standby database using current logfile disconnect from session;select open_mode from v$database;OPEN_MODE
--------------------
READ ONLY WITH APPLYselect process,client_process,sequence#,status from v$managed_standby;PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH	  ARCH		   23 CLOSING
ARCH	  ARCH		    0 CONNECTED
ARCH	  ARCH		    0 CONNECTED
ARCH	  ARCH		   22 CLOSING
RFS	  ARCH		    0 IDLE
RFS	  UNKNOWN	    0 IDLE
RFS	  LGWR		   24 IDLE
MRP0	  N/A		   24 APPLYING_LOG

#至此Oracle 11g ADG配置完成

这篇关于创建ADG的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/697266。

相关文章

python如何创建等差数列

python如何创建等差数列

《python如何创建等差数列》：本文主要介绍python如何创建等差数列的问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录python创建等差数列例题运行代码回车输出结果总结python创建等差数列import numpy as np x=int(in
阅读更多...
怎么用idea创建一个SpringBoot项目

怎么用idea创建一个SpringBoot项目

《怎么用idea创建一个SpringBoot项目》本文介绍了在IDEA中创建SpringBoot项目的步骤,包括环境准备（JDK1.8+、Maven3.2.5+）、使用SpringInitializr... 目录如何在idea中创建一个SpringBoot项目环境准备1.1打开IDEA，点击New新建一个项
阅读更多...
如何使用Maven创建web目录结构

如何使用Maven创建web目录结构

《如何使用Maven创建web目录结构》：本文主要介绍如何使用Maven创建web目录结构的问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录创建web工程第一步第二步第三步第四步第五步第六步第七步总结创建web工程第一步js通过Maven骨架创pytho
阅读更多...
MySQL 用户创建与授权最佳实践

MySQL 用户创建与授权最佳实践

《MySQL用户创建与授权最佳实践》在MySQL中,用户管理和权限控制是数据库安全的重要组成部分,下面详细介绍如何在MySQL中创建用户并授予适当的权限,感兴趣的朋友跟随小编一起看看吧... 目录mysql 用户创建与授权详解一、MySQL用户管理基础1. 用户账户组成2. 查看现有用户二、创建用户1. 基
阅读更多...
Python中使用uv创建环境及原理举例详解

Python中使用uv创建环境及原理举例详解

《Python中使用uv创建环境及原理举例详解》uv是Astral团队开发的高性能Python工具,整合包管理、虚拟环境、Python版本控制等功能,：本文主要介绍Python中使用uv创建环境及... 目录一、uv工具简介核心特点：二、安装uv1. 通过pip安装2. 通过脚本安装验证安装：配置镜像源（可
阅读更多...
Java中实现线程的创建和启动的方法

Java中实现线程的创建和启动的方法

《Java中实现线程的创建和启动的方法》在Java中,实现线程的创建和启动是两个不同但紧密相关的概念,理解为什么要启动线程（调用start()方法）而非直接调用run()方法,是掌握多线程编程的关键,... 目录1. 线程的生命周期2. start() vs run() 的本质区别3. 为什么必须通过 st
阅读更多...
Macos创建python虚拟环境的详细步骤教学

Macos创建python虚拟环境的详细步骤教学

《Macos创建python虚拟环境的详细步骤教学》在macOS上创建Python虚拟环境主要通过Python内置的venv模块实现,也可使用第三方工具如virtualenv,下面小编来和大家简单聊聊... 目录一、使用 python 内置 venv 模块（推荐）二、使用 virtualenv（兼容旧版 P
阅读更多...
Linux lvm实例之如何创建一个专用于MySQL数据存储的LVM卷组

Linux lvm实例之如何创建一个专用于MySQL数据存储的LVM卷组

《Linuxlvm实例之如何创建一个专用于MySQL数据存储的LVM卷组》：本文主要介绍使用Linux创建一个专用于MySQL数据存储的LVM卷组的实例,具有很好的参考价值,希望对大家有所帮助,... 目录在Centos 7上创建卷China编程组并配置mysql数据目录1. 检查现有磁盘2. 创建物理卷3. 创
阅读更多...
Java 如何创建和使用ExecutorService

Java 如何创建和使用ExecutorService

《Java如何创建和使用ExecutorService》ExecutorService是Java中用来管理和执行多线程任务的一种高级工具,可以有效地管理线程的生命周期和任务的执行过程,特别是在需要处... 目录一、什么是ExecutorService？二、ExecutorService的核心功能三、如何创建
阅读更多...
使用Python创建一个功能完整的Windows风格计算器程序

使用Python创建一个功能完整的Windows风格计算器程序

《使用Python创建一个功能完整的Windows风格计算器程序》：本文主要介绍如何使用Python和Tkinter创建一个功能完整的Windows风格计算器程序,包括基本运算、高级科学计算（如三... 目录python实现Windows系统计算器程序（含高级功能）1. 使用Tkinter实现基础计算器2.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2