Oracle Dataguard（主库为双节点集群）配置详解（3）：配置主库

🕗 发布于 2025-01-14 00:32 oracle 数据库

Oracle Dataguard（主库为双节点集群）配置详解（3）：配置主库

一、开启归档

-- 查看主库归档状态
SQL> archive log list;
Database log mode       No Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Current log sequence       5

-- 停库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 启动数据库到mount状态
SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size    2255832 bytes
Variable Size  297796648 bytes
Database Buffers  322961408 bytes
Redo Buffers    3313664 bytes
Database mounted.

-- 修改数据库为归档模式
SQL> alter database archivelog;

Database altered.

-- 打开数据库
SQL> alter database open;

Database altered.

-- 查看归档状态
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   5
Current log sequence       5

二、开启强制日志

SQL> alter database force logging;

Database altered.

三、添加 standby 日志

创建主库的standby日志文件，每个实例的组数要比logfile多一组。

1、查看主库的日志信息

-- 查看日志组
select * from v$log;

-- 查看日志文件
select member from v$logfile;


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE  MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
 1    1      11  104857600   5122 NO  CURRENT      986498 09-JAN-25 2.8147E+14
 2    1       0  104857600   5122 YES UNUSED   0  0
 3    1       0  104857600   5122 YES UNUSED   0  0
 4    2       9  104857600   5122 NO  CURRENT      986588 09-JAN-25 2.8147E+14
 5    2       0  104857600   5122 YES UNUSED   0  0
 6    2       0  104857600   5122 YES UNUSED   0  0

6 rows selected.


SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------------
+DATA/hisdb/onlinelog/group_1.266.1189965499
+BAK/hisdb/onlinelog/group_1.260.1189965507
+DATA/hisdb/onlinelog/group_2.265.1189965519
+BAK/hisdb/onlinelog/group_2.259.1189965527
+DATA/hisdb/onlinelog/group_3.262.1189965537
+BAK/hisdb/onlinelog/group_3.275.1189965547
+DATA/hisdb/onlinelog/group_4.261.1189965557
+BAK/hisdb/onlinelog/group_4.276.1189965567
+DATA/hisdb/onlinelog/group_5.272.1189965577
+BAK/hisdb/onlinelog/group_5.277.1189965587
+DATA/hisdb/onlinelog/group_6.273.1189965595
+BAK/hisdb/onlinelog/group_6.278.1189965603

12 rows selected.

2、添加 standby log

添加standby log必须和redo log大小一样，数量为：standby logfile=(1+logfile组数)*thread=(1+3)*2=8。

alter database add standby logfile thread 1 group 11 size 100m;
alter database add standby logfile thread 1 group 12 size 100m;
alter database add standby logfile thread 1 group 13 size 100m;
alter database add standby logfile thread 1 group 14 size 100m;
alter database add standby logfile thread 2 group 15 size 100m;
alter database add standby logfile thread 2 group 16 size 100m;
alter database add standby logfile thread 2 group 17 size 100m;
alter database add standby logfile thread 2 group 18 size 100m;

3、重新查看日志信息

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE  MEMBERS ARC STATUS       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
 1    1      11  104857600   5122 NO  CURRENT      986498 09-JAN-25 2.8147E+14
 2    1       0  104857600   5122 YES UNUSED   0  0
 3    1       0  104857600   5122 YES UNUSED   0  0
 4    2       9  104857600   5122 NO  CURRENT      986588 09-JAN-25 2.8147E+14
 5    2       0  104857600   5122 YES UNUSED   0  0
 6    2       0  104857600   5122 YES UNUSED   0  0

6 rows selected.


SQL> select * from v$Standby_log;

    GROUP# DBID        THREAD#SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS  FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAS
T_CHANGE# LAST_TIME---------- ---------------------------------------- ---------- ---------------
11 UNASSIGNED     10  104857600    512  0 YES UNASSIGNED
12 UNASSIGNED     10  104857600    512  0 YES UNASSIGNED
13 UNASSIGNED     10  104857600    512  0 YES UNASSIGNED
14 UNASSIGNED     10  104857600    512  0 YES UNASSIGNED
15 UNASSIGNED     20  104857600    512  0 YES UNASSIGNED
16 UNASSIGNED     20  104857600    512  0 YES UNASSIGNED
17 UNASSIGNED     20  104857600    512  0 YES UNASSIGNED
18 UNASSIGNED     20  104857600    512  0 YES UNASSIGNED

8 rows selected.


SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------
+DATA/hisdb/onlinelog/group_1.266.1189965499
+BAK/hisdb/onlinelog/group_1.260.1189965507
+DATA/hisdb/onlinelog/group_2.265.1189965519
+BAK/hisdb/onlinelog/group_2.259.1189965527
+DATA/hisdb/onlinelog/group_3.262.1189965537
+BAK/hisdb/onlinelog/group_3.275.1189965547
+DATA/hisdb/onlinelog/group_4.261.1189965557
+BAK/hisdb/onlinelog/group_4.276.1189965567
+DATA/hisdb/onlinelog/group_11.271.1189966083
+BAK/hisdb/onlinelog/group_11.266.1189966091
+DATA/hisdb/onlinelog/group_12.270.1189966101
+BAK/hisdb/onlinelog/group_12.265.1189966111
+DATA/hisdb/onlinelog/group_13.269.1189966121
+BAK/hisdb/onlinelog/group_13.263.1189966129
+DATA/hisdb/onlinelog/group_14.268.1189966139
+BAK/hisdb/onlinelog/group_14.262.1189966147
+DATA/hisdb/onlinelog/group_5.272.1189965577
+BAK/hisdb/onlinelog/group_5.277.1189965587
+DATA/hisdb/onlinelog/group_6.273.1189965595
+BAK/hisdb/onlinelog/group_6.278.1189965603
+DATA/hisdb/onlinelog/group_15.274.1189966155
+BAK/hisdb/onlinelog/group_15.281.1189966167
+DATA/hisdb/onlinelog/group_16.275.1189966175
+BAK/hisdb/onlinelog/group_16.282.1189966183
+DATA/hisdb/onlinelog/group_17.276.1189966193
+BAK/hisdb/onlinelog/group_17.283.1189966201
+DATA/hisdb/onlinelog/group_18.277.1189966209
+BAK/hisdb/onlinelog/group_18.284.1189966215

28 rows selected.

四、配置静态监听

1、配置 listener.ora 文件

切换到grid用户，在$ORACLE_HOME/network/admin/listener.ora文件中添加如下内容：

，GLOBAL_DBNAME配置为db_unique_name，SID_NAME配置为该节点ORACLE_SID

[grid@rac01 ~]$ cd $ORACLE_HOME/network/admin

[grid@rac01 admin]$ ls
endpoints_listener.ora  listener2501091PM3305.bak  listener.ora  listener.ora.bak.rac01  samples  shrept.lst  sqlnet.ora

# 节点1
[grid@rac01 admin]$ vi listener.ora


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = 
     (GLOBAL_DBNAME = hisdb)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
     (SID_NAME = his1)
    )
   )

# 节点2
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = 
     (GLOBAL_DBNAME = hisdb)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
     (SID_NAME = his2)
    )
   )

配置完成后使用如下命令重启监听：

lsnrctl stop
lsnrctl start

2、配置 tnsnames.ora 文件

切换到 oracle用户，在$ORACLE_HOME/network/admin/tnsnames.ora文件中添加如下内容。

[oracle@rac01 ~]$ cd $ORACLE_HOME/network/admin

[oracle@rac01 admin]$ ls
samples  shrept.lst  tnsnames.ora

# 节点1与节点2都需要配置，且内容相同
# 其中 hisdb、hisdb1、hisdb2 的 host 配置分别为 rac-scan、节点1与节点2的IP地址，service_name配置为listener.ora文件中的GLOBAL_DBNAME也就是主库的db_unique_name
# hisdbdg 的 service_name 为备库 db_unique_name
[grid@rac01 admin]$ vi tnsnames.ora

hisdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdb)
    )
  )

hisdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdb)
        (SID = his1)
    )
  )

hisdb2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdb)
        (SID = his2)
    )
  )


dghisdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdbdg)
    )
  )

3、测试连接

tnsping hisdb
tnsping hisdb1
tnsping hisdb2
tnsping hisdbdg

五、修改主库的初始化参数

在节点1执行如下操作：

1、根据 spfile 生成 pfile 文件

SQL> create pfile='/home/oracle/inithis.ora' from spfile;

File created.

2、修改 pfile 文件的内容如下

[oracle@rac01 ~]$ vi /home/oracle/inithis.ora

his2.__db_cache_size=385875968
his1.__db_cache_size=369098752
his1.__java_pool_size=4194304
his2.__java_pool_size=4194304
his1.__large_pool_size=8388608
his2.__large_pool_size=8388608
his1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
his2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
his1.__pga_aggregate_target=209715200
his2.__pga_aggregate_target=209715200
his1.__sga_target=629145600
his2.__sga_target=629145600
his1.__shared_io_pool_size=0
his2.__shared_io_pool_size=0
his2.__shared_pool_size=222298112
his1.__shared_pool_size=239075328
his1.__streams_pool_size=0
his2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/hisdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/hisdb/controlfile/current.260.1189953459','+BAK/hisdb/controlfile/current.256.1189953459'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='hisdb'
*.db_recovery_file_dest='+BAK'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hisXDB)'
his2.instance_number=2
his1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=629145600
his2.thread=2
his1.thread=1
his1.undo_tablespace='UNDOTBS1'
his2.undo_tablespace='UNDOTBS2'

# 在参数文件中添加以下内容：
*.db_unique_name='hisdb'                           # 主数据库的唯一名，默认和数据库名一致
*.log_archive_config='dg_config=(hisdb,hisdbdg)'   # 主库和备库在tnsname.ora中的连接名

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=hisdb'                             # 主数据库的唯一名
*.log_archive_dest_2='service=hisdbdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=hisdbdg'                           # 备用数据库的唯一名
*.db_file_name_convert='/usr/local/oradata/hisdbdg/','+DATA/hisdb/datafile/'
# 格式： 
# 主库配置格式： *.db_file_name_convert= 备用数据库数据文件目录，主数据库数据文件目录
# 备库配置格式： *.db_file_name_convert= 主数据库数据文件目录，备用数据库数据文件目录
*.log_file_name_convert='/usr/local/oradata/hisdbdg/','+DATA/hisdb/onlinelog/'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
# fal_client用于接受日志，fal_server用于发送日志。
# 也即无论是主库或备库，fal_server=对方，fal_client=自己
# 主库设置如下
*.fal_server='hisdbdg'
*.fal_client='hisdb'
# 备库设置如下
# *.fal_server='hisdb'
# *.fal_client='hisdbdg'

3、重启数据库到 nomount，根据修改后的 pfile 文件重新生成 spfile 参数文件

-- 在节点1和节点2上关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 使用修改后的参数文件启动数据库到nomount
SQL> startup nomount pfile='/home/oracle/inithis.ora'
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size    2255832 bytes
Variable Size  297796648 bytes
Database Buffers  322961408 bytes
Redo Buffers    3313664 bytes

-- 根据修改后的 pfile 文件重新生成 spfile 参数文件
SQL> create spfile='+DATA/hisdb/spfilehis.ora' from pfile='/home/oracle/inithis.ora';

File created.

4、重新启动数据库（两个节点同时操作）

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size    2255832 bytes
Variable Size  297796648 bytes
Database Buffers  322961408 bytes
Redo Buffers    3313664 bytes
Database mounted.
Database opened.

六、复制主库密码文件至备库

备库修改密码文件名为orapwhisdg，备库的实例名为hisdg。

[oracle@rac01 dbs]$ scp orapwhis1 oracle@racdg:/usr/local/oracle/product/11.2.0/db_1/dbs/orapwhisdg
oracle@racdg's password:     100% 1536     1.5KB/s   00:00 

# 在备库查看密码文件
[oracle@racdg dbs]$ pwd
/usr/local/oracle/product/11.2.0/db_1/dbs
[oracle@racdg dbs]$ ll
总用量 8
-rw-r--r--. 1 oracle oinstall 2851 5月  15 2009 init.ora
-rw-r-----  1 oracle oinstall 1536 1月  10 14:57 orapwhisdg

原文地址：https://blog.csdn.net/weixin_44377973/article/details/145077278

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：Three.js 扩展与插件：增强3D开发的利器
下一篇：数据库作业02

GB44495-2024 汽车整车信息安全技术要求 - V2X部分前置要求
GB 44495-2024《汽车整车信息安全技术要求》中关于V2X（车与外界通信）的部分，主要关注于通信安全要求，旨在确保车辆在与外部设备进行数据交互时的信息安全。其测试大致可分为消息层（数据无异常）
阅读更多2025-01-16
Python----Python高级（面向对象：对象，类，属性，方法）
Python----Python高级（面向对象：对象，类，属性，方法）
阅读更多2025-01-16
js多种循环方法（通过循环进行判断的相关方法）
可以判断数组或字符串内是否包含某个值，返回值是下标，找到true，没找到返回false。可以判断数组或字符串内是否包含某个值，返回值是下标，找到返回位置下标，没找到返回-1。先执行循环，再判断下次循环
阅读更多2025-01-16
使用 MailKit 从 Outlook.Office365.com 下载邮件附件的教程
本教程将指导您如何使用 MailKit 库从 Microsoft Outlook 的 Office 365 服务器（IMAP 服务器）下载邮件附件。我们将逐步介绍从设置开发环境到编写和运行代码的全过程
阅读更多2025-01-16
如何仅用递归函数和栈操作逆序一个栈（C）
这个算法的精髓在于它巧妙地结合了递归和栈的特性，通过递归深入到栈的最底层并逐层返回，在递归过程中逐步将栈底元素移除并保存，然后在归的过程中将这些元素逆序压入栈中，最终实现了栈内元素的逆序操作，整个过程
阅读更多2025-01-16
HAL库-第七章-lvgl交互界面开发
学习
阅读更多2025-01-16
Android Auto能够与Android设备整合的几项功能有哪些？
1.Engaging with Google:与Google签署基本保密协议等相关协议，同时提交产品的兼容性报告（Head Unit Compatibility）和产品面向市场说明文件（Go To M
阅读更多2025-01-16
【深度学习】Pytorch：调度器与学习率衰减
在深度学习中，学习率（Learning Rate）控制模型参数更新的步幅。学习率衰减（Learning Rate Decay）是一种动态调整学习率的方法，能够在训练初期提供快速的优化效果，并在后期保证
阅读更多2025-01-16
BERT与CNN结合实现糖尿病相关医学问题多分类模型
首先直接利用transformer.models.bert.BertForSequenceClassification()实现文本分类。使用HuggingFace开发的Transformers库，使用
阅读更多2025-01-16
android T 建立文件夹及文件的记录
第一：AndroidManifest.xml 中整体给予apk权限，如此加入后，在android的settings中，可以找到app.手动给予静态的权限，但是app不一定能使用，请大神指导为什么？Lo
阅读更多2025-01-16

Oracle Dataguard（主库为双节点集群）配置详解（3）：配置主库