oracle11gAdg_搭建和切换法

最新推荐文章于 2024-07-29 14:29:00 发布
最新推荐文章于 2024-07-29 14:29:00 发布
阅读量438 收藏
点赞数
文章标签： mysql 数据库 mongodb sql json
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_54651775/article/details/117627519
版权
本文详细介绍了如何在Oracle 11g中设置和切换Active Data Guard (ADG)。包括确认主库归档模式、增大日志组、修改配置文件、创建备用日志、配置监听和tnsnames.ora，以及故障切换和数据保护模式切换的步骤。适合数据库管理员参考。
摘要由CSDN通过智能技术生成

导读

作者：杨漆
16年关系型数据库管理，从oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB获得3个OCP、2个OCM；运维路上不平坦，跌过不少坑、熬过许多夜。把工作笔记整理出来分享给大伙儿，希望帮到大家少走弯路、少熬夜。

1.先确认primary库处于归档模式
SQL> archive log list;
2.强制归档
SQL> alter database force logging;
3.增大日志组
ALTER DATABASE ADD LOGFILE GROUP 4 (’/John/app/oracle/oradata/carthage/redo04.log’,’/John/app/oracle/oradata/carthage/redo044.log’) SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 5 (’/John/app/oracle/oradata/carthage/redo05.log’,’/John/app/oracle/oradata/carthage/redo055.log’) SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 6 (’/John/app/oracle/oradata/carthage/redo06.log’,’/John/app/oracle/oradata/carthage/redo066.log’) SIZE 200M;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;
4.删除原来过小的redo
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;

cd /John/app/oracle/oradata/carthage/

rm -rf redo01.log

rm -rf redo02.log

rm -rf redo03.log

5.将原有redo重建加大
ALTER DATABASE ADD LOGFILE GROUP 1 (’/John/app/oracle/oradata/carthage/redo01.log’,’/John/app/oracle/oradata/carthage/redo011.log’) SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 2 (’/John/app/oracle/oradata/carthage/redo02.log’,’/John/app/oracle/oradata/carthage/redo022.log’) SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 3 (’/John/app/oracle/oradata/carthage/redo03.log’,’/John/app/oracle/oradata/carthage/redo033.log’) SIZE 200M;
6.添加standby log
ALTER DATABASE ADD standby LOGFILE GROUP 7 (’/John/app/oracle/oradata/carthage/standby_redo01.log’,’/John/app/oracle/oradata/carthage/standby_redo011.log’) SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 8 (’/John/app/oracle/oradata/carthage/standby_redo02.log’,’/John/app/oracle/oradata/carthage/standby_redo022.log’) SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 9 (’/John/app/oracle/oradata/carthage/standby_redo03.log’,’/John/app/oracle/oradata/carthage/standby_redo033.log’) SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 10 (’/John/app/oracle/oradata/carthage/standby_redo04.log’,’/John/app/oracle/oradata/carthage/standby_redo044.log’) SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 11 (’/John/app/oracle/oradata/carthage/standby_redo05.log’,’/John/app/oracle/oradata/carthage/standby_redo055.log’) SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 12 (’/John/app/oracle/oradata/carthage/standby_redo06.log’,’/John/app/oracle/oradata/carthage/standby_redo066.log’) SIZE 200M;
$> mkdir -p /John/app/oracle/oradata/carthage/archive_dest
$> mkdir -p /John/app/oracle/oradata/carthage/standby_archive
alter system set log_archive_dest_1=‘location=/John/app/oracle/oradata/carthage/archive_dest’ scope=spfile;
alter system set standby_archive_dest=’/John/app/oracle/oradata/carthage/standby_archive’ scope=spfile;
alter system set standby_file_management=‘AUTO’;
---- 一致性关闭数据库起来后执行以下命令:
create pfile from spfile;
7. 修改pfile 关键信息如下:
cp $ORACLE_HOME/dbs/initcarthage.ora $ORACLE_HOME/dbs/initcarthage.ora.bak
vi $ORACLE_HOME/dbs/initcarthage.ora
*.db_unique_name=‘carthage’
*.diagnostic_dest=’/John/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=powerdesXDB)’
*.fal_client=‘carthage’
*.fal_server=‘carthage_dg’
*.standby_file_management=‘AUTO’
*.db_file_name_convert=’/John/app/oracle/oradata/carthage’,’/John/app/oracle/oradata/carthage’
*.log_file_name_convert=’/John/app/oracle/oradata/carthage’,’/John/app/oracle/oradata/carthage’
*.log_archive_config=‘DG_CONFIG=(carthage,carthage_dg)’
*.log_archive_dest_2=‘SERVICE=carthage_dg lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=carthage_dg’
*.log_archive_dest_state_2=‘ENABLE’
8.监听一定要静态注册（否则会跌坑里）
listener.ora

listener.ora Network Configuration File: /John/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/John/app/oracle/product/11.2.0/dbhome_2)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = carthage)
(ORACLE_HOME = /John/app/oracle/product/11.2.0/dbhome_2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.10.130)(PORT = 10010))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
ADR_BASE_LISTENER = /John/app/oracle
tnsnames.ora

tnsnames.ora Network Configuration File: /John/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora

Generated by Oracle configuration tools.

hammer =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.10.130)(PORT = 10010))
)
(CONNECT_DATA =
(SERVICE_NAME = carthage)
)
)
carthage =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.10.130)(PORT = 10010))
)
(CONNECT_DATA =
(SERVICE_NAME = carthage)
)
)
carthage_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.10.131)(PORT = 10010))
)
(CONNECT_DATA =
(SERVICE_NAME = carthage)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
9. 监听服务重启（一定要重启，否则又一大坑）
lsnrctl stop
lsnrctl start
startup pfile=/John/app/oracle/product/11.2.0/dbhome_2/dbs/initcarthage.ora
– 在备库上安装数据库软件
scp tnsnames.ora listener.ora 10.25.10.131:/John/app/oracle/product/11.2.0/dbhome_1/network/admin/
scp initcarthage.ora orapwcarthage 10.25.10.131:/John/app/oracle/product/11.2.0/dbhome_1/dbs/
cat listener.ora

listener.ora Network Configuration File: /John/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/John/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = carthage)
(ORACLE_HOME = /John/app/oracle/product/11.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.10.131)(PORT = 10010))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
ADR_BASE_LISTENER = /John/app/oracle
cat initcarthage.ora
carthage.__db_cache_size=63350767616
carthage.__java_pool_size=536870912
carthage.__large_pool_size=536870912
carthage.__oracle_base=’/John/app/oracle’#ORACLE_BASE set from environment
carthage.__pga_aggregate_target=53687091200
carthage.__sga_target=80530636800
carthage.__shared_io_pool_size=0
carthage.__shared_pool_size=15032385536
carthage.streams_pool_size=0
*.audit_file_dest=’/John/app/oracle/admin/carthage/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.0.0’
*.control_files=’/John/app/oracle/oradata/carthage/control01.ctl’,’/John/app/oracle/oradata/carthage/control02.ctl’
*.db_block_size=8192
*.db_domain=’’
*.db_name=‘carthage’
*.db_unique_name=‘carthage_dg’
*.db_file_name_convert=’/John/app/oracle/oradata/carthage’,’/John/app/oracle/oradata/carthage’
*.log_file_name_convert=’/John/app/oracle/oradata/carthage’,’/John/app/oracle/oradata/carthage’
*.log_archive_config=‘DG_CONFIG=(carthage,carthage_dg)’
*.fal_client=‘carthage_dg’
*.fal_server=‘carthage’
*.db_recovery_file_dest=’/John/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest=’/John/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=carthageXDB)’
*.log_archive_dest_1=‘location=/John/app/oracle/oradata/carthage/archive_dest’
*.log_archive_dest_2=‘SERVICE=carthage lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=carthage’
*.log_archive_format=’%t%s%r.dbf’
*.memory_target=134055198720
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sessions=555
*.standby_archive_dest=’/John/app/oracle/oradata/carthage/standby_archive’
*.standby_file_management=‘AUTO’
*.undo_tablespace=‘UNDOTBS1’
mkdir -p /John/app/oracle/admin/carthage/adump
mkdir -p /John/app/oracle/flash_recovery_area
mkdir -p /John/app/oracle/oradata/carthage/standby_archive
mkdir -p /John/app/oracle/oradata/carthage/archive_dest
lsnrctl start
lsnrctl status
—创建catalog库–可选(catalog必须为异地库)
CREATE TABLESPACE catalog_tbs DATAFILE ‘/John/app/oracle/oradata/carthage/catalog_tbs.dbf’ SIZE 50M REUSE AUTOEXTEND ON NEXT 50M;
CREATE USER rman IDENTIFIED BY rman DEFAULT TABLESPACE catalog_tbs;
GRANT RECOVERY_CATALOG_OWNER TO rman;
$ rman catalog rman/rman
RMAN> CREATE CATALOG;
RMAN> REGISTER DATABASE;

rman target sys/oracle@carthage auxiliary sys/oracle@carthage_dg nocatalog
duplicate target database for standby from active database nofilenamecheck;
–启动dg
recover managed standby database using current logfile disconnect from session;
–检查状态
select process,client_process,sequence#,status from v m a n a g e d s t a n d b y ; s e l e c t d a t a b a s e r o l e , p r o t e c t i o n m o d e , p r o t e c t i o n l e v e l , o p e n m o d e f r o m v managed_standby; select database_role,protection_mode,protection_level,open_mode from v manageds​tandby

最低0.47元/天 解锁文章
yangqi000
关注
Oracle 11g ADG 搭建
djwnb666的博客
11-21 1103
Oracle的ADG搭建，11g的，希望可以帮到你，很多照片传不上来不知道为什么，见谅
Oracle dataguard 搭建 oracle 11g ADG
最新发布
打工之路，一路生花
08-21 1006
目录 一、系统环境检查 二、参数调整三、搭建ADG1、主库操作1、主库开启归档模式，此步骤需要重启数据库--5主库打开force logging--6主库修改DG相关参数--7修改之后验证：--8、主库添加standby redo logfile(根据MAA最佳实践，我们建议只为备用重做日志组，每组多添加一个成员。)9、拷贝主库的密码文件到备库服务器10、在主库使用oracle用户创建pfile11、主库配置监听程序12、主库配置service别名(tnsnames.ora文件)2、备库操作1、根据主库s
oracle adg switch over,Oracle 11g ADG SWITCHOVER 切换过程
weixin_36392230的博客
04-10 153
--- 主库操作alter system archive log current;alter system checkpoint;select switchover_status from v$database;alter database commit to switchover to physical standby with session shutdown;startup mount;--...
组态ORACLE 11G ADG
weixin_34124939的博客
06-20 110
一旦载10g的，没有票据。昨天使用duplicate方法一安装11g ADG，过程艰辛，记录：   一、环境配置   主图书馆 IP地址:192.168.233.128/24 操作系统版本号：rhel5.8 64bit 数据库版本号：11.2.0.1 64bit 数据库sid名：orcl 数据库名：orcl 数据库db_unique_name:orcl1 主机名：pr...
ORACLE 11G adg切换
期待着的博客
07-29 222
SHUTDOWN ABORT # 11.2版本以下的ADG还需要执行这一步，11.2以上版本的不需要执行。如果 参数有问题，不能传到备库，状态不是to_primary ,要强制KILL 会话。# 启动主库到mount下（这样主库就变成了备库）# 主库切换备库（这一步会关闭主库的所有会话）# 查看切换前的状态，确认当前从操作的是主库。# 打开原来的主库（也就是现在的备库）# 打开备库（此时已经变成主库了）# 将备库切换为主库。
oracle同步数据adg_oracle11g ADG主备切换
weixin_39536630的博客
01-16 525
1、主库操作将主库切换成备库点击(此处)折叠或打开select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;alter system switch logfile;alter system archive log current;alter database c...
oracle备库ora4021,ORA-04021导致oracle11gADG备库宕机问题处理
weixin_32968239的博客
04-16 779
发现数据库告警，查看alert日志，发现如下报错Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lgwr_26383.trc:ORA-04021: timeout occurred while waiting to lock objectLGWR (ospid: 26383): terminating the ins...
Intel_Power_Gadget_3.7.0_blog.tlhub.cn.dmg
06-02
Intel Power Gadget 是因特尔官方出品的一款基于软件对 CPU 电源使用情况进行监控的小工具，它可以实时监控基于 Intel各平台（包括台式机或笔记本） CPU 的功耗、负载、频率、温度等情况，结果非常准确。
MPI.zip_MPI
09-20
【描述】中的文本"mfakpofja gak gojap gagjoapgjagda a gadg adga a agad"看起来像是随机字符序列，并未提供关于MPI的具体信息，因此无法作为知识点展开讨论。我们主要依据标签“mpi”来探讨MPI的相关知识点。 ...
"ZKBBM方程的解析解及应用
Journalof the Egyptian Mathematical Society（2015）23，42埃及数学学会埃及数学学会www.etms-eg.orgwww.elsevier.com/locate/joems原创文章ZKBBM方程的（G0/G）-展开法和广义（G0/G）-展开法的新方法Hasibun ...
oracle 11g ADG实施手册（亲测，已成功部署多次）
12-13
oracle 11g ADG实施手册（亲测，已成功部署多次）；根据文档步骤实施可以确保成功
（绝对干货）Oracle ADG数据库完整部署、切换、备份、运维手册
09-11
阿里云上部署Oracle ADG，并且带有节点故障自动切换功能。同时，也包括数据库异常故障的处理，比如死锁、数据误删除进行恢复
Oracle ADG 数据库完整部署、切换、备份、运维手册(绝对干货)
03-08
Oracle ADG 数据库完整部署、切换、备份、运维手册(绝对干货)。 阿里云上部署Oracle ADG，并且带有节点故障自动切换功能。同时，也包括数据库异常故障的处理，比如死锁、数据误删除进行恢复 Oracle ADG DG Broker Linux
ORACLE ADG自动化切换工具_oradgtool
07-11
测试过的应用环境： 主库：oracle11gr2 RAC 备库：oracle11gr2 单节点HAS 配置文件（config.ini）说明：
Oracle 11g 搭建ADG方式(手动自动)
andrewkao882的博客
10-12 389
1m. 登录主 2m/s. 在主备库同时创建静态监听ltnsnames 主库的tnsnames.ora 添加备库TNS , 不需要重启 lsnrctl 备库设定listener.ora（参考主）需重启LSNRCTL，tnsnames.ora 添加主和备库TNS 主庫： [oracle@cos6M5]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora ...
Oracle11g ADG部署
qq_29431123的博客
11-07 423
Oracle 11g版本 ADG部署，主库操作系统版本rh6.5,备库Centos7.9
redhat 6.4 oracle 11g dg 主备切换
hzcyhujw的博客
01-03 429
概述：dataguard可以起到数据库操作系统基本的高可用功能，有时需要在维护计划内亦或者在主库意外宕机期间，需将主库的应用全部切换至备库作为使用；此次我们就对dataguard的switchover做个测试记录；官方文档可参看：https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB00620
Oracle 11g Dataguard 配置，维护与详解 (ADG)
weixin_30631587的博客
02-23 701
一、前言： 本手册主要记录如何配置，还介绍了配置原因，以及注意要点，已经主备切换，以及故障转移等重要操作步骤，我希望这个文章可以作为进行dataguard配置的一个参考手册。 二、前提 1.主库是归档模式： 如果我们不清楚为什么是归档模式，那我们就应该也不会清楚dataguard是用来做什么的。透过很多修饰的官方语言，我们需要明确DG（dataguard简...
Oracle 11g搭建DG（ADG方式）
热门推荐
听雪楼主
10-16 1万+
Oracle 11g搭建DG（ADG方式）
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值