暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle 10.2.0.5 DataGuard主从搭建-呆瓜式

Oracle 10.2.0.5 DataGuard主从搭建-呆瓜式

原创 Albert Tan DBA 之路 2025-03-12
50

前言


Oracle 数据库使用 Data Guard 搭建主从，在主库发生故障时。可以切换至备库支持生产。

目的

主从目的是为了增加数据库的容错率。降低生产数据库崩溃导致业务停顿的几率。当前文档的操作可直接应用至生产环境。但其中的参数基于业务具体情况可适当修改。

系统信息

OSDatabaseMemoryDiskNameIPOS Use
Oracle RHEL 5.810.2.0.532G500GMaster192.168.136.11oracle
Oracle RHEL 5.810.2.0.532G500GSlave192.168.136.211oracle


Linux安装

1、Linux安装将不再演示

# 可参见该文档https://www.modb.pro/db/1899296432973164544
复制

2、IP 设置

# 编辑 hosts
$ vi /etc/hosts
192.168.136.11  master.localdomain.com master
192.168.136.211  slave.localdomain.com slave
复制

3、Hostname 设置

# 注意：更改主机名时，需要三台机器均执行，不同的服务器添加对应的一个名即可
$ vi /etc/hostname
master
slave
复制

4、关闭防火墙

systemctl stop firewlld.service && systemctl disable firewlld.service
复制

5、关闭Selinux策略

vi /etc/selinux/config
SELINUX=disabled
复制

6、关闭透明大页和numa


7、开启Hugesize

目前这个值的计算方式很复杂。如果需要设置请私信我!!!


数据库环境变量配置

# 主库oracle用户
ORACLE_HOME=/u01/db/10.2.0
ORACLE_SID=master
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$PATH
alias dba="sqlplus / as sysdba"
export PATH ORACLE_HOME ORACLE_SID

# 从库oracle用户
ORACLE_HOME=/u01/db/10.2.0
ORACLE_SID=slave
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$PATH
alias dba="sqlplus / as sysdba"
export PATH ORACLE_HOME ORACLE_SID
复制

主库设置

1、主库开启归档

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/db/arch/
Oldest online log sequence     98
Next log sequence to archive   0
Current log sequence           102


--如未开启归档，请打开
SQL>shutdown immediate
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
复制

2、主库强制日志模式

--开启强制日志
SQL>alter database force logging;
--查询实例的归档模式和强制日志
SQL> select log_mode,force_logging from v$database;

LOG_MODE     FOR
------------ ---
ARCHIVELOG   YES
复制

3、主库追加redo

SQL> alter database add standby logfile group 4 '/u01/db/oradata/master/standby04.log' size 50M;
SQL> alter database add standby logfile group 5 '/u01/db/oradata/master/standby05.log' size 50M;
SQL> alter database add standby logfile group 6 '/u01/db/oradata/master/standby06.log' size 50M;
复制

4、主库配置tnsnames.ora

MASTER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.136.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MASTER)
    )
  )

SLAVE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.136.211)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SLAVE)
    )
  )
复制

5、主库配置listener.ora

MASTER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.136.11)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
SID_LIST_MASTER =
   (SID_LIST =
     (SID_DESC =
        (GLOBAL_DBNAME = master)
        (ORACLE_HOME = /u01/db/10.2.0)
        (SID_NAME = master)
     )
   )
复制

6、主库参数文件配置

*.audit_file_dest='/u01/db/10.2.0/admin/master/adump'
*.background_dump_dest='/u01/db/10.2.0/admin/master/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/db/oradata/master/control01.ctl','/u01/db/oradata/master/control02.ctl','/u01/db/oradata/master/control03.ctl'
*.core_dump_dest='/u01/db/10.2.0/admin/master/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest='/u01/db/10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=masterXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=402653184
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1209008128
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/db/10.2.0/admin/master/udump'

# DG Parameter
*.undo_management='AUTO'
*.db_name='master'
*.db_unique_name='master'
*.log_archive_config='dg_config=(master,slave)'
*.log_archive_dest_1='location=/u01/db/arch valid_for=(all_logfiles,all_roles) db_unique_name=master'
*.log_archive_dest_2='service=slave valid_for=(all_logfiles,all_roles) db_unique_name=slave'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.fal_server='slave'
*.fal_client='master'
*.db_file_name_convert='/u01/db/oradata/master','/u01/db/oradata/master'
*.log_file_name_convert='/u01/db/oradata/master','/u01/db/oradata/ master '
*.standby_file_management='auto'
复制

7、主库执行Rman完全备份

RMAN> backup database;

Starting backup at 27-FEB-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u01/db/oradata/master/undotbs01.dbf
input datafile fno=00001 name=/u01/db/oradata/master/system01.dbf
input datafile fno=00003 name=/u01/db/oradata/master/sysaux01.dbf
input datafile fno=00005 name=/u01/db/oradata/master/example01.dbf
input datafile fno=00004 name=/u01/db/oradata/master/users01.dbf
channel ORA_DISK_1: starting piece 1 at 27-FEB-25
channel ORA_DISK_1: finished piece 1 at 27-FEB-25
piece handle=/u01/db/10.2.0/flash_recovery_area/MASTER/backupset/2025_02_27/o1_mf_nnndf_TAG20250227T141156_mw00kdbk_.bkp tag=TAG202502          27T141156 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 27-FEB-25
channel ORA_DISK_1: finished piece 1 at 27-FEB-25
piece handle=/u01/db/10.2.0/flash_recovery_area/MASTER/backupset/2025_02_27/o1_mf_ncnnf_TAG20250227T141156_mw00lhj6_.bkp tag=TAG202502          27T141156 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-FEB-25

# 备份文件存储路径
$ /u01/db/10.2.0/flash_recovery_area/MASTER/backupset/2025_02_27
复制


8、主库密码文件配置

$ orapwd file=orapwmaster password=admin!11
复制

9、主库发送文件到从库

$ scp ../ tnsnames.ora  listener.ora 192.168.136.211: /u01/db/10.2.0/network/admin/
$ scp initmaster.ora orapwmaster 192.168.136.211: /u01/db/10.2.0/dbs

# 注意：主库需要将控制文件发送至从库
$ scp /u01/db/oradata/master/control01.ctl control02.ctl control03.ctl 192.168.136.211: /u01/db/oradata/slave
复制


从库配置

1、从库创建备份文件保存路径

# 注意：在主库发送备份文件时，从库需要创建和主库备份文件一样的存储的路径，因为当前方式使用的是主库的控制文件，所以无法识别其它路径。同时需要创建和主库一致的数据库文件存储路径，必须完全一致

$ mkdir –p /u01/db/10.2.0/flash_recovery_area/MASTER/backupset/2025_02_27
$ mkdir –p /u01/db/oradata/master
复制

2、从库配置tnsnames.ora

SLAVE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.136.211)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SLAVE)
    )
  )

MASTER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.136.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = master)
    )
  )
复制

3、从库配置listener.ora

SLAVE =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.136.211)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
SID_LIST_SLAVE =
   (SID_LIST =
     (SID_DESC =
        (GLOBAL_DBNAME = slave)
        (ORACLE_HOME = /u01/db/10.2.0)
        (SID_NAME = slave)
     )
   )
复制

4、从库参数文件配置

*.audit_file_dest='/u01/db/10.2.0/admin/slave/adump'
*.background_dump_dest='/u01/db/10.2.0/admin/slave/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/db/oradata/slave/control01.ctl','/u01/db/oradata/slave/control02.ctl','/u01/db/oradata/slave/control03.ctl'
*.core_dump_dest='/u01/db/10.2.0/admin/slave/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest='/u01/db/10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=masterXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=402653184
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1209008128
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/db/10.2.0/admin/slave/udump'

# DG Parameter
*.undo_management='AUTO'
*.db_name='master'
*.db_unique_name='slave'
*.log_archive_config='dg_config=(master,slave)'
*.log_archive_dest_1='location=/u01/db/arch valid_for=(all_logfiles,all_roles) db_unique_name=slave'
*.log_archive_dest_state_1='enable'
*.fal_server='master'
*.fal_client='slave'
*.db_file_name_convert='/u01/db/oradata/master','/u01/db/oradata/master'
*.log_file_name_convert='/u01/db/oradata/master','/u01/db/oradata/master'
*.standby_file_management='auto'
复制

5、从库启动监听

[oracle@slave dbs]$ lsnrctl start slave

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 27-FEB-2025 16:54:52

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.136.211)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     slave
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                27-FEB-2025 14:27:28
Uptime                    0 days 2 hr. 27 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/db/10.2.0/network/admin/listener.ora
Listener Log File         /u01/db/10.2.0/network/log/slave.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.136.211)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "slave" has 1 instance(s).
  Instance "slave", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
复制

6、从库创建文件夹

$ mkdir –p /u01/db/10.2.0/admin/slave/adump
$ mkdir –p /u01/db/10.2.0/admin/slave/bdump
$ mkdir –p /u01/db/oradata/slave
$ mkdir –p /u01/db/10.2.0/admin/slave/cdump
$ mkdir –p /u01/db/10.2.0/flash_recovery_area
$ mkdir –p /u01/db/10.2.0/admin/slave/udump
复制

7、从库启动到mount

$ sqlplus sys/admin*11@slave as sysdba
SQL> startup nomount pfile='initslave.ora'
SQL>alter database mount standby database;
复制

8、从库开始还原

$ rman target /
RMAN> restore database;

Starting restore at 27-FEB-25
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/db/oradata/master/system01.dbf
restoring datafile 00002 to /u01/db/oradata/master/undotbs01.dbf
restoring datafile 00003 to /u01/db/oradata/master/sysaux01.dbf
restoring datafile 00004 to /u01/db/oradata/master/users01.dbf
restoring datafile 00005 to /u01/db/oradata/master/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/db/10.2.0/flash_recovery_area/MASTER/backupset/2025_02_27/o1_mf_nnndf_TAG20250227T141156_mw00kdbk_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/db/10.2.0/flash_recovery_area/MASTER/backupset/2025_02_27/o1_mf_nnndf_TAG20250227T141156_mw00kdbk_.bkp tag=TAG20250227T141156
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 27-FEB-25

RMAN> exit
复制

主库配置

1、主库创建从库控制文件

# 注意：此处是在主库操作
$ sqlplus sys/admin@master as sysdba
SQL>alter database create standby controlfile as '/u01/db/10.2.0/control.ctl';
复制

2、主库发送新备库控制文件

$ scp /u01/db/10.2.0/control.ctl /u01/db/oradata/slave
复制

从库配置

1、从库关机

SQL>shutdown immediate
复制

2、从库更改控制文件

$ cd /u01/db/oradata/slave
$ mv control01.ctl control01.ctl.bak
$ mv control02.ctl control02.ctl.bak
$ mv control03.ctl control03.ctl.bak

# 复制新的控制文件
$ cp control.ctl control01.ctl
$ cp control.ctl control02.ctl
$ cp control.ctl control03.ctl
复制

3、从库重新启动

SQL>startup nomount pfile='initslave.ora';
SQL>alter database mount standby database;
复制

4、从库启动日志同步

SQL>alter database recover managed standby database using current logfile disconnect from session;
复制

数据校验

注意：只要此处显示了和主库归档日志一样的sequence#编号及APPLIED的值为YES，即数据同步

SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;

 SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- --------- --------- ---
        39 27-FEB-25 27-FEB-25 YES
        40 27-FEB-25 27-FEB-25 YES
        41 27-FEB-25 27-FEB-25 YES
        42 27-FEB-25 27-FEB-25 YES
复制





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

文章被以下合辑收录

Oracle Database（共2篇）
Oracle 数据库相关技术

评论

目录
  • 前言
  • 目的
  • 系统信息
  • Linux安装
    • 1、Linux安装将不再演示
    • 2、IP 设置
    • 3、Hostname 设置
    • 4、关闭防火墙
    • 5、关闭Selinux策略
    • 6、关闭透明大页和numa
    • 7、开启Hugesize
  • 数据库环境变量配置
  • 主库设置
  • 从库配置
  • 主库配置
  • 从库配置
  • 数据校验