创建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。

相关文章

Android 悬浮窗开发示例((动态权限请求 | 前台服务和通知 | 悬浮窗创建 )

Android 悬浮窗开发示例((动态权限请求 | 前台服务和通知 | 悬浮窗创建 )

《Android悬浮窗开发示例((动态权限请求|前台服务和通知|悬浮窗创建)》本文介绍了Android悬浮窗的实现效果,包括动态权限请求、前台服务和通知的使用,悬浮窗权限需要动态申请并引导... 目录一、悬浮窗 动态权限请求1、动态请求权限2、悬浮窗权限说明3、检查动态权限4、申请动态权限5、权限设置完毕后
阅读更多...
Python创建Excel的4种方式小结

Python创建Excel的4种方式小结

《Python创建Excel的4种方式小结》这篇文章主要为大家详细介绍了Python中创建Excel的4种常见方式,文中的示例代码简洁易懂,具有一定的参考价值,感兴趣的小伙伴可以学习一下... 目录库的安装代码1——pandas代码2——openpyxl代码3——xlsxwriterwww.cppcns.c
阅读更多...
使用Python在Excel中创建和取消数据分组

使用Python在Excel中创建和取消数据分组

《使用Python在Excel中创建和取消数据分组》Excel中的分组是一种通过添加层级结构将相邻行或列组织在一起的功能,当分组完成后,用户可以通过折叠或展开数据组来简化数据视图,这篇博客将介绍如何使... 目录引言使用工具python在Excel中创建行和列分组Python在Excel中创建嵌套分组Pyt
阅读更多...
解决IDEA使用springBoot创建项目,lombok标注实体类后编译无报错,但是运行时报错问题

解决IDEA使用springBoot创建项目,lombok标注实体类后编译无报错,但是运行时报错问题

《解决IDEA使用springBoot创建项目,lombok标注实体类后编译无报错,但是运行时报错问题》文章详细描述了在使用lombok的@Data注解标注实体类时遇到编译无误但运行时报错的问题,分析... 目录问题分析问题解决方案步骤一步骤二步骤三总结问题使用lombok注解@Data标注实体类，编译时
阅读更多...
MySQL分表自动化创建的实现方案

MySQL分表自动化创建的实现方案

《MySQL分表自动化创建的实现方案》在数据库应用场景中,随着数据量的不断增长,单表存储数据可能会面临性能瓶颈,例如查询、插入、更新等操作的效率会逐渐降低,分表是一种有效的优化策略,它将数据分散存储在... 目录一、项目目的二、实现过程（一）mysql 事件调度器结合存储过程方式1. 开启事件调度器2. 创
阅读更多...
mysql外键创建不成功/失效如何处理

mysql外键创建不成功/失效如何处理

《mysql外键创建不成功/失效如何处理》文章介绍了在MySQL5.5.40版本中,创建带有外键约束的`stu`和`grade`表时遇到的问题,发现`grade`表的`id`字段没有随着`studen... 当前mysql版本：SELECT VERSION();结果为：5.5.40。在复习mysql外键约
阅读更多...
Window Server创建2台服务器的故障转移群集的图文教程

Window Server创建2台服务器的故障转移群集的图文教程

《WindowServer创建2台服务器的故障转移群集的图文教程》本文主要介绍了在WindowsServer系统上创建一个包含两台成员服务器的故障转移群集,文中通过图文示例介绍的非常详细,对大家的... 目录一、 准备条件二、在ServerB安装故障转移群集三、在ServerC安装故障转移群集，操作与Ser
阅读更多...
Window Server2016 AD域的创建的方法步骤

Window Server2016 AD域的创建的方法步骤

《WindowServer2016AD域的创建的方法步骤》本文主要介绍了WindowServer2016AD域的创建的方法步骤,文中通过图文介绍的非常详细,对大家的学习或者工作具有一定的参考学习价... 目录一、准备条件二、在ServerA服务器中常见AD域管理器：三、创建AD域，域地址为“test.ly”
阅读更多...
Python在固定文件夹批量创建固定后缀的文件(方法详解)

Python在固定文件夹批量创建固定后缀的文件(方法详解)

《Python在固定文件夹批量创建固定后缀的文件(方法详解)》文章讲述了如何使用Python批量创建后缀为.md的文件夹,生成100个,代码中需要修改的路径、前缀和后缀名,并提供了注意事项和代码示例,... 目录1. python需求的任务2. Python代码的实现3. 代码修改的位置4. 运行结果5.
阅读更多...
使用IntelliJ IDEA创建简单的Java Web项目完整步骤

使用IntelliJ IDEA创建简单的Java Web项目完整步骤

《使用IntelliJIDEA创建简单的JavaWeb项目完整步骤》：本文主要介绍如何使用IntelliJIDEA创建一个简单的JavaWeb项目,实现登录、注册和查看用户列表功能,使用Se... 目录前置准备项目功能实现步骤1. 创建项目2. 配置 Tomcat3. 项目文件结构4. 创建数据库和表5.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2