暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / oracle11gAdg_搭建和切换法

oracle11gAdg_搭建和切换法

原创 John2020 2021-06-08
671

导读

作者：杨漆
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/archivedest> 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 vmanagedstandby;selectdatabaserole,protectionmode,protectionlevel,openmodefromvmanaged_standby; select database_role,protection_mode,protection_level,open_mode from vdatabase;
查看DG的日志信息
select * from vdataguard_status alter database set standby database to maximize availability; ----切换为最大可用 alter database set standby database to maximize protection; ----切换为最大保护 (切换为最大保护模式报错，需要将standby端启动到mount状态切换) ----------------------故障切换 手工------------ --primary 做如下操作 alter database commit to switchover to physical standby; shutdown immediate startup alter database recover managed standby database disconnect from session; select database_role,switchover_status from vdatabase;
–standby 端做如下操作
alter database commit to switchover to primary;
shutdown immediate
startup
alter system switch logfile;
select database_role,switchover_status from vdatabase; ----------ADG做(fail over)切换测试------------- --standby 端检查状态 select open_mode from vdatabase;
–我们通过shutdown abort方式人工模拟primary奔溃，直接关闭:
–primary端
shutdown abort ;
–在standby端执行如下操作 :
startup mount;
alter system flush redo to ‘carthage’;
select thread#, low_sequence#, high_sequence# from varchive_gap; --如果没有发现明显的gap现象，说明此次的failover不会有数据损失情况。在standby端，要进行关闭apply和结束应用动作 alter database recover managed standby database cancel; alter database recover managed standby database finish; select open_mode, switchover_status from vdatabase;
123凡科快图.gif

oraclemysqlpostgresqldb2mongodb
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

Oracle DataGuard高可用性解决方案详解
孙莹
583次阅读
2025-03-26 23:27:33
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
539次阅读
2025-04-15 17:24:06
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
445次阅读
2025-04-18 14:18:38
XTTS跨版本迁移升级方案（11g to 19c RAC for Linux）
zwtian
435次阅读
2025-04-08 09:12:48
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
433次阅读
2025-04-22 00:20:37
墨天轮个人数说知识点合集
JiekeXu
433次阅读
2025-04-01 15:56:03
Oracle SQL 执行计划分析与优化指南
Digital Observer
433次阅读
2025-04-01 11:08:44
MySQL数据库当前和历史事务分析
听见风的声音
423次阅读
2025-04-01 08:47:17
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
410次阅读
2025-04-22 00:13:51
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
399次阅读
2025-04-20 10:07:02
John2020
关注
73
文章
16
粉丝
76K+
浏览量
暂无图片
获得了20次点赞
暂无图片
内容获得10次评论
暂无图片
获得了51次收藏
热门文章
mysql8.0.22 GTID模式主从复制13114错误的解决方案
2021-04-28 7552浏览
Tidb4.0中Tiup 常用运维操作命令干货
2020-11-23 3354浏览
goldengate12C安装调试手顺from_oracle12c到Kafka数据实时同步
2021-06-08 3290浏览
怎样将数据从Oracle迁移到TiDB
2021-02-25 3006浏览
TiDB4.0生态里的TiCDC是何方神物？揭秘！
2020-10-15 2899浏览
在线实训环境入口
PostgreSQL在线实训环境
查看详情
最新文章
mysql查询结果输出跳出平台限制小妙招
2021-10-13 1122浏览
提高mysql插入速度的方法
2021-09-08 1267浏览
oracle 中快速批量获取Meta data
2021-09-01 1234浏览
bbed批量修改asm的datafile脚本
2021-08-23 780浏览
BBED修复Oracle 12C ASM文件方法
2021-08-12 1073浏览
目录
  • cd /John/app/oracle/oradata/carthage/
  • rm -rf redo01.log
  • rm -rf redo02.log
  • rm -rf redo03.log
  • listener.ora Network Configuration File: /John/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
  • Generated by Oracle configuration tools.
  • tnsnames.ora Network Configuration File: /John/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora
  • Generated by Oracle configuration tools.
  • listener.ora Network Configuration File: /John/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
  • Generated by Oracle configuration tools.