oracle 12c adg 部署

server/2024/10/22 15:18:00/

oracle 12C 搭建Active Data Gurad(主从实时同步)
Oracle版本: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
操作前须知:

 基于两个单 机oracle 进行。

需要先自行安装完成oracle12c 

​修改host ，oem 为主，oracle5 为从。

192.168.2.231 oracle5

192.168.2.230 oem

主库操作:
1.关闭数据库
SQL> shutdown immediate;
1
2.启动数据库至mount状态
SQL>startup mount
startup mount
ORACLE instance started.

Total System Global Area  767557632 bytes
Fixed Size            8625416 bytes
Variable Size          608174840 bytes
Database Buffers      142606336 bytes
Redo Buffers            8151040 bytes
Database mounted.
 
3.修改归档位置, 目录不存在，请先创建。
SQL> alter system set log_archive_dest_1='location=/u01/oracle/archive';
 
4.启用归档模式
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
 
5. 验证归档是否正常
# 切换在线日志，验证归档是否正常
SQL> alter system switch logfile;
 
6.开启强制日志
SQL> alter database force logging;
Database altered.

SQL> select force_logging from v$database;
FORCE_LOGGING
--------------------------------------------------------------------------------
YES
 
7. 查询redo log信息
select thread#,groups from v$thread;

   THREAD#     GROUPS
---------- ----------
      1              3

# 查询redo日志大小
SQL> select group#,thread#,sequence#,bytes/1024/1024 mb from v$Log;

    GROUP#    THREAD#  SEQUENCE#           MB
---------- ---------- ---------- ----------
     1                1       31                200
     2                1       29                200
     3                1       30                200

# 查询redo日志存放路径
SQL> select member from v$Logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/redo03.log

/u01/app/oracle/oradata/orcl/redo02.log

/u01/app/oracle/oradata/orcl/redo01.log

 
8. 添加备用日志
# 根据redo日志数量,添加N+1个备用日志(3+1),大小与原redo日志大小一样,否则会报错alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo01.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo02.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo03.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo04.log' size 200M;


9. 确认备用日志添加成功

SQL> SELECT THREAD#,GROUP#,bytes/1024/1024 MB,ARCHIVED,STATUS FROM V$STANDBY_LOG;

THREAD# GROUP# MB ARC STATUS

---------- ---------- ---------- --- ----------

0 4 200 YES UNASSIGNED

0 5 200 YES UNASSIGNED

0 6 200 YES UNASSIGNED

0 7 200 YES UNASSIGNED


10. 修改监听文件(主从都要配置)
cat $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oem)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
# 以下为新增 （主从都要添加）
SID_LIST_LISTENER =
  (SID_DESC =
    (GLOBAL_DBNAME = orcl)
    (ORACLE_HOME= /u01/app/oracle/product/12.2.0/dbhome_1)
    (SID_NAME = orcl)
  )
 
11. 重启监听文件
[oracle@centos-4 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-JUL-2021 20:18:26

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=centos-4)(PORT=1521)))
The command completed successfully
 
12. 创建服务
cat $ORACLE_HOME/network/admin/tnsnames.ora

LISTENER_ORCL =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracle5)(PORT = 1521))

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracle5)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

# 以下为新增服务(必须与db_unique_name一致)

 

primary =
 

(DESCRIPTION =
 

(ADDRESS = (PROTOCOL = TCP)(HOST = oem)(PORT = 1521))
 

(CONNECT_DATA =
 

(SERVER = DEDICATED)
 

(SERVICE_NAME = orcl)
 

)
 

)
 

standby =
 

(DESCRIPTION =
 

(ADDRESS = (PROTOCOL = TCP)(HOST = oracle5)(PORT = 1521))
 

(CONNECT_DATA =
 

(SERVER = DEDICATED)
 

(SERVICE_NAME = orcl)
 

)
 

)
 


 13. 检查服务
 [oracle@centos-4 admin]$ tnsping primary
 

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 24-JUL-2021 20:23:15
 

Copyright (c) 1997, 2016, Oracle.  All rights reserved.
 

Used parameter files:
 /u01/oracle/product/12c/network/admin/sqlnet.ora
 

 
 

 
 14. 创建pfile文件
 SQL> create pfile='$ORACLE_HOME/dbs/initcrmdb.ora' from spfile
 1
 15. 修改文件
 

 

orcl.__data_transfer_cache_size=0
 

orcl.__db_cache_size=620756992
 

orcl.__inmemory_ext_roarea=0
 

orcl.__inmemory_ext_rwarea=0
 

orcl.__java_pool_size=16777216
 

orcl.__large_pool_size=33554432
 

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
 

orcl.__pga_aggregate_target=637534208
 

orcl.__sga_target=956301312
 

orcl.__shared_io_pool_size=50331648
 

orcl.__shared_pool_size=218103808
 

orcl.__streams_pool_size=0
 *.audit_file_dest='/u01/oracle/admin/crmdb/adump'
 *.audit_trail='db'
 *.compatible='12.2.0'
 *.control_files='/u01/oracle/oradata/crmdb/control01.ctl','/u01/oracle/oradata/crmdb/control02.ctl'
 *.db_block_size=8192
 *.db_name='crmdb'
 *.diagnostic_dest='/u01/oracle'
 *.dispatchers='(PROTOCOL=TCP) (SERVICE=crmdbXDB)'
 *.local_listener='LISTENER_CRMDB'
 *.memory_target=732m
 *.nls_language='AMERICAN'
 *.nls_territory='AMERICA'
 *.open_cursors=300
 *.processes=300
 *.remote_login_passwordfile='EXCLUSIVE'
 *.undo_tablespace='UNDOTBS1'
 # 以下为新增
 *.db_unique_name='primary' # 名称必须唯一,切要与上面12所见服务要一样
 *.log_archive_config='dg_config=(primary,standby)'
 *.log_archive_dest_1='location=/u01/oracle/archive valid_for=(all_logfiles,all_roles) db_unique_name=primary' # 设置本地归档位置
 # 设置远程归档日志位置及同步方式为lgwr affirm sync
 *.log_archive_dest_2='service=standby valid_for=(online_logfiles,primary_role)  lgwr affirm sync db_unique_name=standby'
 *.log_archive_dest_state_1=enable #启用
 *.log_archive_dest_state_2=enable
 *.standby_file_management='auto' #允许数据文件自动同步添加
 *.fal_server='standby' #获取归档日志的服务为从库standby
 *.fal_client='primary' #获取归档日志的客户端为主库primary
 *.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
 *.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
  
 16. 应用pfile文件
 SQL>shutdown immediate;
 SQL>create spfile from  pfile='$ORACLE_HOME/dbs/initorcl.ora';
 SQL>startup
  
 17. 传输文件到从库
 [oracle@centos-4 dbs]$ scp orapworcl oracle@centos-5:$ORACLE_HOME/dbs
 [oracle@centos-4 dbs]$ scp initorcl.ora oracle@centos-5:$ORACLE_HOME/dbs
  
 18. 将主库模式改为 MAXIMUM AVAILABILITY
 # 不需要关闭数据库,默认data guard模式为 Maximize Performance
 SQL>alter database set standby database to maximize availability;
 

# 查看模式
 SQL> select database_role,protection_mode,protection_level from v$database;
 

DATABASE_ROLE                       PROTECTION_MODE                        PROTECTION_LEVEL
 -------------------- ---------------------------  ---------------------------
 PRIMARY                           MAXIMUM AVAILABILITY                    MAXIMUM AVAILABILITY
 

 
 从库操作
 19. 修改pfile文件
 

 

orcl.__data_transfer_cache_size=0
 

orcl.__db_cache_size=620756992
 

orcl.__inmemory_ext_roarea=0
 

orcl.__inmemory_ext_rwarea=0
 

orcl.__java_pool_size=16777216
 

orcl.__large_pool_size=33554432
 

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
 

orcl.__pga_aggregate_target=637534208
 

orcl.__sga_target=956301312
 

orcl.__shared_io_pool_size=50331648
 

orcl.__shared_pool_size=218103808
 

orcl.__streams_pool_size=0
 


 *.audit_file_dest='/u01/oracle/admin/orcl/adump'
 *.audit_trail='db'
 *.compatible='12.2.0'
 *.control_files='/u01/oracle/oradata/orcl/control01.ctl','/u01/oracle/oradata/orcl/control02.ctl'
 *.db_block_size=8192
 *.db_name='orcl'
 *.diagnostic_dest='/u01/oracle'
 *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
 *.local_listener='LISTENER_ORCL'
 *.memory_target=732m
 *.nls_language='AMERICAN'
 *.nls_territory='AMERICA'
 *.open_cursors=300
 *.processes=300
 *.remote_login_passwordfile='EXCLUSIVE'
 *.undo_tablespace='UNDOTBS1'
 

#修改以下内容 
 

*.db_unique_name='standby'
 *.log_archive_config='dg_config=(primary,standby)'
 *.log_archive_dest_1='location=/u01/oracle/archive valid_for=(all_logfiles,all_roles) db_unique_name=standby'
 *.log_archive_dest_2='service=primary valid_for=(online_logfiles,primary_role)  lgwr affirm sync db_unique_name=primary'
 *.log_archive_dest_state_1=enable
 *.log_archive_dest_state_2=enable
 *.standby_file_management='auto'
 *.fal_server='primary'
 *.fal_client='standby'
 *.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
 *.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
  
 20. 应用pfile文件
 SQL>shutdown immediate;
 SQL>create spfile from  pfile='$ORACLE_HOME/dbs/initorcl.ora';
 # 启动为nomout模式
 SQL>startup nomount
  
  
 21. 在备库上同步数据
 [oracle@centos-5 ~]$ rman target sys/oracle@primary auxiliary sys/oracle12C@standby
 

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jul 24 20:44:15 2021
 

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 

connected to target database: ORCL (DBID=4187303499)
 connected to auxiliary database: ORCL(DBID=4187303499)
  # 执行同步
 RMAN>duplicate target database for standby from active database nofilenamecheck;
 

 
 22. 开启从库
 # 执行完同步后,数据库状态会有 nomount--> mount
 SQL>alter database open;
  
 23. 开启实时同步
 # 开启实时同步
 alter database recover managed standby database using current logfile disconnect from session;
 # 关闭实时同步
 alter database recover managed standby database cancel;
  
 24.检查
 SQL> select open_mode,log_mode,open_mode ,database_role from v$database;
 

OPEN_MODE                             LOG_MODE                          OPEN_MODE           DATABASE_ROLE
 --------------------    --------------   -------------------------- -------------------
 READ ONLY WITH APPLY     ARCHIVELOG               READ ONLY WITH APPLY         PHYSICAL STANDBY
 # open_mode 为READ ONLY WITH APPLY,没有开启实时同步状态为 READ ONLY
  
 25. 测试
 在主库测试创建表及插入数据,查看从库是否同步
 自行创建一个模式，执行以下sql ，查看同步情况 
  
  CREATE TABLE employees (
     employee_id NUMBER(6) PRIMARY KEY,
     first_name VARCHAR2(50) NOT NULL,
     last_name VARCHAR2(50) NOT NULL,
     email VARCHAR2(100) UNIQUE,
     hire_date DATE DEFAULT SYSDATE,
     salary NUMBER(8, 2),
     department_id NUMBER(6)
   
 );
 

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES (1, 'John', 'Doe', 'john.doe@example.com', SYSDATE, 60000.00, 1);
 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', SYSDATE, 65000.00, 2);
 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES (3, 'Michael', 'Johnson', 'michael.johnson@example.com', SYSDATE, 70000.00, 1);
 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES (4, 'Emily', 'Davis', 'emily.davis@example.com', SYSDATE, 72000.00, 3);
 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES (5, 'David', 'Brown', 'david.brown@example.com', SYSDATE, 58000.00, 2);
 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES (6, 'Sarah', 'Wilson', 'sarah.wilson@example.com', SYSDATE, 62000.00, 3);
 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES (7, 'James', 'Taylor', 'james.taylor@example.com', SYSDATE, 64000.00, 1);
 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES (8, 'Laura', 'Anderson', 'laura.anderson@example.com', SYSDATE, 58000.00, 2);
 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES (9, 'Robert', 'Thomas', 'robert.thomas@example.com', SYSDATE, 69000.00, 3);
 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES (10, 'Linda', 'Jackson', 'linda.jackson@example.com', SYSDATE, 61000.00, 1);
  











http://www.ppmy.cn/server/133934.html












相关文章










FreeRTOS的中断管理









FreeRTOS的中断管理




事件 
嵌入式实时系统必须对源自环境的事件采取行动。例如，到达以太网外围设备的数据包（事件）可能需要传递到TCP/IP堆栈进行处理（动作）。非普通系统必须为来自多个来源的事件提供服务，所有这些事件都有不同…




阅读更多...

















React 进阶阶段学习计划









React 进阶阶段学习计划




React 进阶阶段学习计划 
目标 
掌握自定义Hooks的创建和使用。深入理解上下文（Context）和Redux的高级用法。学会服务端渲染（SSR）。深入探讨性能优化技巧。 
学习内容 
自定义Hooks 
创建和使用自定义Hooks 自定义Hooks&#xff1…




阅读更多...

















全天候风险平价策略下载 | Quantlab AI v0.2：OpenAI的Swarm适配国内大模型（附python代码下载）









全天候风险平价策略下载 | Quantlab AI v0.2：OpenAI的Swarm适配国内大模型（附python代码下载）




原创内容第679篇，专注量化投资、个人成长与财富自由。 
今天我们来实现服务端策略下载，下载后支持在本地调试运作，及查看源代码。 
通过服务器下载策略的代码： 
login_required
def down_strategy(request, task_id: str):task  m…




阅读更多...

















基于深度学习的设备异常检测与预测性维护









基于深度学习的设备异常检测与预测性维护




基于深度学习的设备异常检测与预测性维护是一项利用深度学习技术分析设备运行数据，实时检测设备运行过程中的异常情况，并预测未来可能的故障，以便提前进行维护，防止意外停机和生产中断。它在工业领域应用广泛，特别是在…




阅读更多...

















SpringCloud-持久层框架MyBatis Plus的使用与原理详解









SpringCloud-持久层框架MyBatis Plus的使用与原理详解




在现代微服务架构中，SpringCloud 是一个非常流行的解决方案。而在数据库操作层面，MyBatis Plus 作为 MyBatis 的增强工具，能够简化开发，提升效率，特别是在开发企业级应用和分布式系统时尤为有用。本文将详细介绍 MyBat…




阅读更多...

















c语言基础程序——经典100道实例。









c语言基础程序——经典100道实例。




c语言基础程序——经典100道实例 001， 组无重复数字的数002，企业发放的奖金根据利润提成003，完全平方数004，判断当天是这一年的第几天005，三个数由小到大输出006，输出字母C图案007，特殊图案008&…




阅读更多...

















使用ollama本地部署qwen2并api调用









使用ollama本地部署qwen2并api调用




目录 
一、下载ollama 
二、安装qwen大模型  
三、Api调用 
四、尝试apifox调用 
一、下载ollama 
Ollama 是一个开源的、本地运行的 AI 聊天模型，允许在自己的设备上运行 LLM，无需依赖云服务。它支持多种 LLM。目前Ollama library已经支持Qwen2&#xf…




阅读更多...

















ASP.NET.Web应用程序（.NET Framework）添加Swagger本地Debuge成功打开接口展示界面，发布服务器无法打开接口展示界面









ASP.NET.Web应用程序（.NET Framework）添加Swagger本地Debuge成功打开接口展示界面，发布服务器无法打开接口展示界面




前言 
提示：项目使用ASP.NET.Web应用程序（.NET Framework4.6.1）创建WEB API接口供外部系统调用。本地Debug运行可支持https://localhost:44374/swagger/打开界面展示操作，发布使用Release部署服务器时打开界面展示失败。 一、Swag…




阅读更多...















 
 
 
 










最新文章







 





















Copyright @ 2022~2023