oracle data guard DGBroker方式之DG安装（2/2）

MirrorJon

已于 2023-10-31 14:08:06 修改

阅读量364

点赞数

文章标签： oracle 数据库

于 2023-07-31 15:38:07 首次发布

一、本次安装为一主一丛

1、一主两从的话，就是在pfile里还有listener.ora与tnsname.ora多加点配置，哈哈嚯

服务器	主机名	配置
172.30.11.12	DG1	8核16G
172.30.11.13	DG2	8核16G

2、设置hosts，两台都一样哦

vim /etc/hosts

二、配置listner.ora

1、 cd $ORACLE_HOME/network/admin

vim listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listene
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = porcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = porcl_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = DG1)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

2、从的就把上面的porcl改成sorcl，DG1改为DG2。（如果是一主两从的话，第二个从的，就把porcl改成dorcl，DG1改成DG3）

三、配置tnsnames.ora

1、vim tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DG1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = porcl)
    )
  )

SORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DG2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sorcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DG1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

2、tnsnames.ora配置都一样，就是记得把HOST = DG1改为对应的主机名，主的是DG1，从的是DG2或者DG3

3、如果是一主两从的话，配置如下

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DG1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = porcl)
    )
  )

SORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DG2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sorcl)
    )
  )

DORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DG3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dorcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DG1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

4、配置完tnsnames.ora文件后，重加载下，输入指令

lsnrctl reload

5、请在每台服务器用tnsping指令去ping一下，是否能通

例：tnsping porcl、tnsping sorcl、tnsping orcl

四、修改主库修改db_unique_name

alter system set db_unique_name='porcl' scope=spfile;

五、主库修改standby_file_management

alter system set standby_file_management=auto scope=spfile;

六、主库打开强制日志和dg_broker

alter database force logging;

alter system set dg_broker_start=true scope=spfile;

七、增加备库日志

select group#,type,member from v$logfile; 查看redologs日志信息

增加你的日志多一个

alter database add standby logfile;

八、重启数据库

强制启动数据库读取spfile文件内容

startup force;

九、生成pfile文件

create pfile from spfile;

看你自己的数据库实例名是什么，就对应生成什么，我的是orcl，就是initorcl.ora，如果是hello，生成的就是initohello.ora文件

十、修改pfile文件

1、 cd $ORACLE_HOME/dbs

vim initorcl.ora，将以下内容填入

*.fal_server='sorcl'
*.log_archive_config='dg_config=(porcl,sorcl)'
*.log_archive_dest_1='location=/u01/app/oracle/arch_log/arch'
*.LOG_ARCHIVE_DEST_2='SERVICE=sorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sorcl'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

2、如果是一主两从，则填入以下

*.fal_server='sorcl'
*.log_archive_config='dg_config=(porcl,sorcl,dorcl)'
*.log_archive_dest_1='location=/u01/app/oracle/arch_log/arch'
*.LOG_ARCHIVE_DEST_2='SERVICE=sorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sorcl'
*.LOG_ARCHIVE_DEST_3='SERVICE=dorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dorcl'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_3='ENABLE'

3、将主库关闭，用当前的initorcl.ora启动

shutdown immediate;

startup pfile='$ORACLE_HOME/dbs/initorcl.ora';

4、将pfile的内容打回spfile，以后数据库正常开关，都会以新的spfile内容启动了

create spfile from pfile='$ORACLE_HOME/dbs/initorcl.ora';

startup force;

十一、将密码文件及initorcl传输到从服务器DG2

1、 scp initorcl.ora orapworcl oracle@DG2:$ORACLE_HOME/dbs/

2、然后请对应修改initorcl.ora里的

*.db_unique_name='porcl'为*.db_unique_name='sorcl'

*.fal_server='sorcl'为*.fal_server='porcl'

*.LOG_ARCHIVE_DEST_2='SERVICE=porcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=porcl'

3、将从库的数据库执行以下操作，跟主库上面一样，我列出指令就好了，不解释

shutdown immediate

startup pfile='$ORACLE_HOME/dbs/initorcl.ora';

create spfile from pfile='$ORACLE_HOME/dbs/initorcl.ora';

startup force;

十二、在备库中克隆

1、备库先把数据库打开到nomount状态

shutdown immediate

startup nomount

2、登录rman

rman target sys/oracle@porcl auxiliary sys/oracle@sorcl nocatalog;

如果是一主两从的话，在DG3上也是执行这个指令连接rman，只是把sorcl改成 dorcl，如下：

rman target sys/oracle@porcl auxiliary sys/oracle@dorcl nocatalog;

3、进行克隆，这个指令不能出现任何错误

RMAN> duplicate target database for standby nofilenamecheck from active database;

十三、在主库上进入DGBroker

1、dgmgrl / （可以用help查看对应的指令帮助）

2、创建主库

DGMGRL>CREATE CONFIGURATION dgc as PRIMARY DATABASE IS porcl CONNECT IDENTIFIER IS porcl;

3、创建从库

DGMGRL>ADD DATABASE sorcl as CONNECT IDENTIFIER IS sorcl;

一主两从，则再创建从库dorcl

DGMGRL>ADD DATABASE dorcl as CONNECT IDENTIFIER IS dorcl;

4、激活dgbroker

DGMGRL>enable configuration;

5、至此，搭建完成，可以去建表及切换库验证了，从库切为主库指令为

DGMGRL>switchover to sorcl;

十四、主库宕机切换

1、若我们主库宕机了，并且在短时间内启动不回来了，经过协商，允许丢失一部分数据的情况下，可以强制将从库切换为主库，临时接管生产，那接下来，看我的操作，哈哈嚯。

2、在从库上执行登录DGBroker

dgmgrl /

DGMGRL>connect sys

DGMGRL>failover to sorcl;

十五、主库恢复

1、夜深人静的时候，生产不活跃了，我们就该把主库恢复回来，接着看，哈哈嚯

2、将主库启动到nomount

shutdown immediate

startup nomount

3、登录rman

rman target sys/oracle@sorcl auxiliary sys/oracle@porcl

4、数据克隆

RMAN> duplicate target database for standby nofilenamecheck from active database;

5、删除和重新添加备库

DGMGRL> remove database porcl;

DGMGRL> add database porcl;

DGMGRL> disable configuration;

DGMGRL> enable configuration;

DGMGRL> show configuration; （查看数据库状况，如下）

Configuration - dgc

  Protection Mode: MaxPerformance
  Databases:
    sorcl - Primary database
    porcl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

6、又可以把主库切回来了

DGMGRL>swichover to porcl;

SQL> alter database open;

十六、keepalived配合着主从使用

1、一般情况下，主从都搭配着LVS来做高可用，当keepalived检测到主库down机，立刻切换到从库，虚拟IP不变，这样，生产就不会那么鸡肋，至少不需要去改配置里的数据库IP地址

2、先关闭linux selinux（不关，一会儿执行不了脚本）

vim /etc/selinux/config

将SELINUX=enforceing改为SELINUX=disabled

重启服务器，reboot

3、查看当前selinux状态

getenforce

4、安装keepalived

yum install -y keepalived

5、编辑keepalived.conf，使用的抢占模式

1）、cd /etc/keepalived，将以下内容插入DG1

global_defs {
  router_id DG1 #一般是主机名称，通过hostname获取
}
 
#定义脚本。固定间隔时间执行
vrrp_script chk_oracle {
 script "/script/check_oracle_status.sh" #检测oracle状态
 interval 20
 weight -30
 fall 3
 rise 2
}
 
# 定义主机信息
vrrp_instance VI_1 {
 state MASTER #主机填写MASTER
 interface eth0 #设置实例绑定的网卡
 garp_master_delay 1
 virtual_router_id 10 #虚拟路由器id号。主从必须一致
 priority 100 #定义优先级，数字越大，优先级越高
 advert_int 1 #心跳频率
 
unicast_src_ip 172.30.11.12 #本机ip
unicast_peer {
 172.30.11.13 #对端ip
}
 
authentication {
 auth_type PASS
 auth_pass cgzair
}
virtual_ipaddress {
 172.30.11.14
}
 
#执行上面定义的脚本
track_script {
 chk_oracle
}
}

2）、将以下内容插入DG2

global_defs {
  router_id DG2 #一般是主机名称，通过hostname获取
}
 
#定义脚本。固定间隔时间执行
vrrp_script chk_oracle {
 script "/script/check_oracle_status.sh" #检测Oracle状态
 interval 20
 weight -30
 fall 3
 rise 2
}
 
# 定义主机信息
vrrp_instance VI_1 {
 state BACKUP #主机填写BACKUP
 interface eth0 #设置实例绑定的网卡
 garp_master_delay 1
 virtual_router_id 10 #虚拟路由器id号。主从必须一致
 priority 90 #定义优先级，数字越大，优先级越高
 advert_int 1 #心跳频率
 
unicast_src_ip 172.30.11.13 #本机ip
unicast_peer {
 172.30.11.12 #对端ip
}
 
authentication {
 auth_type PASS
 auth_pass cgzair
}
virtual_ipaddress {
 172.30.11.14
}
 
#执行上面定义的脚本
track_script {
 chk_oracle
}
}

6、在root用户下建立Oracle状态检测脚本

1、mkdir -p /script

2、建立两个脚本

touch check_oracle_status.sh 插入以下内容

#!/bin/bash
userName=`whoami`
echo $userName
if [ $userName == "root" ]; then
        su - oracle -s /bin/bash /script/select.sh
        RETURN=$?
        echo $RETURN
        exit $RETURN
fi

touch select.sh 插入以下内容

#!/bin/bash
# Oracle数据库连接信息
DB_USER="admin"
DB_PASS="admin"
DB_HOST="172.30.11.12"
DB_PORT="1521"
DB_SID="orcl"
now_date=`date '+%Y-%m-%d %H:%M:%S'`

# 检测数据库连接
check_database() {
    sqlplus -S ${DB_USER}/${DB_PASS}@${DB_HOST}:${DB_PORT}/${DB_SID} <<EOF
    exit;
EOF
    if [ $? -eq 0 ]; then
        echo "Database connection successful."
        return 0
    else
        echo "Database connection failed."
        return 1
    fi
}

# 执行简单的查询
execute_query() {
    sqlplus -S ${DB_USER}/${DB_PASS}@${DB_HOST}:${DB_PORT}/${DB_SID} <<EOF
    set heading off;
    select sysdate from dual;
    exit;
EOF
    if [ $? -eq 0 ]; then
        echo "Query executed successfully. ${now_date}"  >> /script/oracle_status.log
        return 0
    else
        echo "Query execution failed. ${now_date}" >> /script/error.log
        return 1
    fi
}
# 调用函数检测数据库连接和执行查询
check_database
execute_query

3、授权

chmod +x check_oracle_status.sh select.sh

touch oracle_status.log error.log

chown -R oracle:oinstall oracle_status.log error.log

4、DG2的keepalived.conf自己去改select.sh里的数据库IP地址就行了

5、启动keepalived

systemctl start keepalived

查看状态

systemctl status keepalived

6、现在你可以尝试把主库down机试试，看看IP是不是会切到从库

SQL>shutdown abort