创建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编程】Linux创建虚拟环境并配置与notebook相连接

【Python编程】Linux创建虚拟环境并配置与notebook相连接

1.创建 使用 venv 创建虚拟环境。例如，在当前目录下创建一个名为 myenv 的虚拟环境： python3 -m venv myenv 2.激活 激活虚拟环境使其成为当前终端会话的活动环境。运行： source myenv/bin/activate 3.与notebook连接 在虚拟环境中，使用 pip 安装 Jupyter 和 ipykernel： pip instal
阅读更多...
在cscode中通过maven创建java项目

在cscode中通过maven创建java项目

在cscode中创建java项目 可以通过博客完成maven的导入 建立maven项目 使用快捷键 Ctrl + Shift + P 建立一个 Maven 项目 1 Ctrl + Shift + P 打开输入框2 输入 "> java create"3 选择 maven4 选择 No Archetype5 输入 域名6 输入项目名称7 建立一个文件目录存放项目，文件名一般为项目名8 确定
阅读更多...
Java 创建图形用户界面（GUI）入门指南（Swing库 JFrame 类）概述

Java 创建图形用户界面（GUI）入门指南（Swing库 JFrame 类）概述

概述 基本概念 Java Swing 的架构 Java Swing 是一个为 Java 设计的 GUI 工具包，是 JAVA 基础类的一部分，基于 Java AWT 构建，提供了一系列轻量级、可定制的图形用户界面（GUI）组件。 与 AWT 相比，Swing 提供了许多比 AWT 更好的屏幕显示元素，更加灵活和可定制，具有更好的跨平台性能。 组件和容器 Java Swing 提供了许多
阅读更多...
顺序表之创建，判满，插入，输出

顺序表之创建，判满，插入，输出

文章目录 🍊自我介绍🍊创建一个空的顺序表，为结构体在堆区分配空间🍊插入数据🍊输出数据🍊判断顺序表是否满了，满了返回值1，否则返回0🍊main函数 你的点赞评论就是对博主最大的鼓励 当然喜欢的小伙伴可以：点赞+关注+评论+收藏（一键四连）哦~ 🍊自我介绍   Hello,大家好，我是小珑也要变强（也是小珑），我是易编程·终身成长社群的一名“创始团队·嘉宾”
阅读更多...
Maven创建项目中的groupId, artifactId, 和 version的意思

Maven创建项目中的groupId, artifactId, 和 version的意思

文章目录 groupIdartifactIdversionname groupId 定义：groupId 是 Maven 项目坐标的第一个部分，它通常表示项目的组织或公司的域名反转写法。例如，如果你为公司 example.com 开发软件，groupId 可能是 com.example。作用：groupId 被用来组织和分组相关的 Maven artifacts，这样可以避免
阅读更多...
批处理以当前时间为文件名创建文件

批处理以当前时间为文件名创建文件

批处理以当前时间为文件名创建文件 批处理创建空文件 有时候，需要创建以当前时间命名的文件，手动输入当然可以，但是有更省心的方法吗？ 假设我是 windows 操作系统，打开命令行。 输入以下命令试试： echo %date:~0,4%_%date:~5,2%_%date:~8,2%_%time:~0,2%_%time:~3,2%_%time:~6,2% 输出类似： 2019_06
阅读更多...
ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

在win7 64位系统下安装oracle11g，在使用Database configuration Assistant创建数据库时，在创建到85%的时候报错，错误如下： 解决办法： 在listener.ora中增加对BlueAeri-PC或ip地址的侦听，具体步骤如下： 1.启动Net Manager，在“监听程序”--Listener下添加一个地址，主机名写计
阅读更多...
PHP7扩展开发之类的创建

PHP7扩展开发之类的创建

本篇文章主要将如何在扩展中创建一个对象。创建的对象的过程，其实和一个小孩出生，成长的过程有些类似。 第一步，办准生证 生孩子第一步，先办准生证。声明我要生孩子了。对象创建的时候，如何办准生证呢？只要定义一个zend_class_entry变量即可。代码如下： zend_class_entry ce; zend_class_entry 是啥？可以认为它使一个原型，定义了一些对象应该有哪些东西
阅读更多...
创建表时添加约束

创建表时添加约束

查询表中的约束信息： SHOW KEYS FROM 表名; 示例： 创建depts表包含department_id该列为主键自动增长，department_name列不允许重复，location_id列不允许有空值。 create table depts(department_id int primary key auto_increment,department_name varcha
阅读更多...
UML- 统一建模语言（Unified Modeling Language）创建项目的序列图及类图

UML- 统一建模语言（Unified Modeling Language）创建项目的序列图及类图

陈科肇 ============= 1.主要模型 在UML系统开发中有三个主要的模型： 功能模型：从用户的角度展示系统的功能，包括用例图。 对象模型：采用对象、属性、操作、关联等概念展示系统的结构和基础，包括类图、对象图、包图。 动态模型：展现系统的内部行为。 包括序列图、活动图、状态图。 因为要创建个人空间项目并不是一个很大的项目，我这里只须关注两种图的创建就可以了，而在开始创建UML图
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2