注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

RAC上搭建DG  

2017-06-27 09:15:54|  分类: 集群 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

 

 

 

 

 

 

 

RAC上搭建DG

 

 

 

 

 

v 2.0

2013530

 

 

 

   

一、基本概况... 2

二、RAC上搭建DG. 2

三、switchover步骤--手动主备切换... 7

四、failover步骤的准备工作... 8

五、failover步骤--手动主备切换... 8

六、主库崩溃时,手动将备库切成主库的脚本... 10

七、配置broker(自动主备切换)的准备工作... 11

八、配置broker(自动主备切换). 12

九、使用broker自动switchover. 13

十、使用broker自动failover. 14

十一、删除DG broker. 15

十二. 定时删除DG备库的归档文件... 15

 

 

 

 

 

 

 

 

 

 

 

 

一、基本概况

db-rac1

db-rac2

dg

public ip

192.168.100.81

192.168.100.82

192.168.100.5

virtual ip

10.10.10.81

10.10.10.82

instance name

crmdb1

crmdb2

crmdb

db_name

crmdb

crmdb

db_unique_name

crmdb

dg

storage mode

ASM

local file system

 

 

 

二、RAC上搭建DG

1. 配置 hosts文件

三台主机的hosts文件改成如下内容

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

# Public Network - (eth0)

192.168.100.81 db-rac1 db-rac1.jjlg.net

192.168.100.82 db-rac2 db-rac2.jjlg.net

192.168.100.5  dg      db-dg02.jjlg.net

# Public Virtual IP (eth1)

192.168.100.83 db-rac1-vip db-rac1-vip.jjlg.net

192.168.100.84 db-rac2-vip db-rac2-vip.jjlg.net

# Private Interconnect

10.10.10.81 db-rac1-priv db-rac1-priv.jjlg.net

10.10.10.82 db-rac2-priv db-rac2-priv.jjlg.net

#scan ip

192.168.100.80 rac rac.jjlg.net

 

2. 强制主库记录日志

避免一些主库操作不记录日志,导致备库不同步的情况,如sql使用nologging后缀,主库默认不记录日志。

archive log list;

select force_logging from v$database;

alter database force logging;

 

3. tnsnames配置并检查

①三台主机上oracle用户下的tnsnames文件,增加如下内容:

DG =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = db-dg02.jjlg.net)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg)

(INSTANCE_NAME = crmdb)

)

)

 

CRMDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac.jjlg.net)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = crmdb)

(FAILOVER_MODE =(TYPE = SESSION)(METHOD = BASIC))

)

)

 

②在主库和备库上oracle用户下测试ping

tnsping dg

tnsping crmdb

 

4. 备库添加以下参数log_archive_dest_1all_logfiles,all_roles   log_archive_dest_2all_logfiles,primary_role

db_unique_name=dg

log_archive_config='dg_config=(crmdb,dg)'

log_archive_dest_1='location=/u01/oracle/oradata/crmdb/arclog/prmlog/ valid_for=(online_logfiles,all_roles) db_unique_name=dg'

log_archive_dest_2='service=crmdb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=crmdb'

log_archive_dest_3='location=/u01/oracle/oradata/crmdb/arclog/stdlog/ valid_for=(standby_logfiles,standby_role) db_unique_name=dg'

log_archive_max_processes=8

standby_file_management=auto

fal_server='crmdb'

db_file_name_convert='+DATA/crmdb/datafile','/u01/oracle/oradata/crmdb','+DATA/crmdb/tempfile','/u01/oracle/oradata/crmdb'

log_file_name_convert='+DATA/crmdb/onlinelog','/u01/oracle/oradata/crmdb'

注意:不要使用参数standby_archive_dest,这是一个过时的参数,而且使用参数时,数据同步会很慢。

 

5. 备库建立spfile

SQL> create spfile from pfile;

 

6. 备份主库

由于RAC主库的归档是放到了ASM共享磁盘,每个实例都能看到对方的归档,所以其rman备份跟单实例没有区别。

RAC的任一实例上执行一次正常的rman备份:

[oracle@db-rac1 ~]$ rman target /

sql 'alter system archive log current';

backup database format '/home/oracle/rman/db_%d_%s_%p_%u_%T.dbf';

crosscheck backup;

delete noprompt obsolete;

delete noprompt expired backup;

sql 'alter system archive log current';

backup archivelog all format '/home/oracle/rman/arc_%d_%u_%T.arc' delete all input;

backup current controlfile format '/home/oracle/rman/ctf_%d_%u_%T.ctl';

exit

 

7. 主库建立备库的控制文件

SQL> alter database create standby controlfile as '/home/oracle/standby.ctl';

 

8. 将口令文件、备库控制文件、备份片和备份之后的归档日志scp到备库的对应位置

[oracle@dg ~]$ mkdir rman

[oracle@db-rac1 ~]$

scp $ORACLE_HOME/dbs/orapwcrmdb1 dg:/u01/oracle/crmdb/dbs/orapwcrmdb

scp /home/oracle/standby.ctl dg:/u01/oracle/oradata/crmdb/control01.ctl

scp /home/oracle/standby.ctl dg:/u01/oracle/oradata/crmdb/control02.ctl

scp /home/oracle/rman/* dg:/home/oracle/rman/

 

9. 恢复备库

[oracle@dg ~]$ rman target /

RMAN> startup mount

RMAN> list backup;

RMAN> restore database;

scp 主库归档文件到备库的stdlog目录下:

#scp /u01/oracle/oradata/crmdb/arclog/prmlog/* dg:/u01/oracle/oradata/crmdb/arclog/stdlog/

RMAN> recover database;

RMAN> exit

 

10. 查看主库redolog的组数,并在备库创建standby redolog

①查看主库redolog的组数:

SQL> select thread#,group#,bytes/1024/1024||'M' M from v$log;

    THREAD#GROUP# M

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

         1          1 1024m

         1          2 1024m

         1          3 1024m

         1          4 1024m

         1          5 1024m

         2         11 1024m

         2         12 1024m

 2         13 1024m

         2         14 1024m

         2         15 1024m

10 rows selected

 

②在备库创建standby redolog

创建原则和单实例一样,大小相等,但日志组数量要比单实例库多一组。

如单实例为5组,则每个实例要创建6组,这里RAC有两个实例,共创建12组,3实例的话要建18组。

alter database add standby logfile thread 1 group 21 '/u01/oracle/oradata/crmdb/std_redo21.log' size 1024m;

alter database add standby logfile thread 1 group 22 '/u01/oracle/oradata/crmdb/std_redo22.log' size 1024m;

alter database add standby logfile thread 1 group 23 '/u01/oracle/oradata/crmdb/std_redo23.log' size 1024m;

alter database add standby logfile thread 1 group 24 '/u01/oracle/oradata/crmdb/std_redo24.log' size 1024m;

alter database add standby logfile thread 1 group 25 '/u01/oracle/oradata/crmdb/std_redo25.log' size 1024m;

alter database add standby logfile thread 1 group 26 '/u01/oracle/oradata/crmdb/std_redo26.log' size 1024m;

alter database add standby logfile thread 1 group 27 '/u01/oracle/oradata/crmdb/std_redo27.log' size 1024m;

alter database add standby logfile thread 1 group 28 '/u01/oracle/oradata/crmdb/std_redo28.log' size 1024m;

alter database add standby logfile thread 1 group 29 '/u01/oracle/oradata/crmdb/std_redo29.log' size 1024m;

alter database add standby logfile thread 1 group 30 '/u01/oracle/oradata/crmdb/std_redo30.log' size 1024m;

 

 

alter database add standby logfile thread 2 group 31 '/u01/oracle/oradata/crmdb/std_redo31.log' size 1024m;

alter database add standby logfile thread 2 group 32 '/u01/oracle/oradata/crmdb/std_redo32.log' size 1024m;

alter database add standby logfile thread 2 group 33 '/u01/oracle/oradata/crmdb/std_redo33.log' size 1024m;

alter database add standby logfile thread 2 group 34 '/u01/oracle/oradata/crmdb/std_redo34.log' size 1024m;

alter database add standby logfile thread 2 group 35 '/u01/oracle/oradata/crmdb/std_redo35.log' size 1024m;

alter database add standby logfile thread 2 group 36 '/u01/oracle/oradata/crmdb/std_redo36.log' size 1024m;

alter database add standby logfile thread 2 group 37 '/u01/oracle/oradata/crmdb/std_redo37.log' size 1024m;

alter database add standby logfile thread 2 group 38 '/u01/oracle/oradata/crmdb/std_redo38.log' size 1024m;

alter database add standby logfile thread 2 group 39 '/u01/oracle/oradata/crmdb/std_redo39.log' size 1024m;

alter database add standby logfile thread 2 group 40 '/u01/oracle/oradata/crmdb/std_redo40.log' size 1024m;

 

 

③备库上查看新建的standby logfile

select group#,thread#,sequence#,status from v$standby_log;

 

11. 主库建立做为备库时接收归档文件的路径

db-rac1db-rac2上分别使用root用户执行:

mkdir /u01/arclog/stdlog/ -p

chown oracle:oinstall /u01/arclog -R

 

12. RAC主库添加DG参数

注意使用ASM的时候,不要改变db_unique_name参数,否则之后创建的asm文件就会放入至新的db_unique_name目录下面,导致DB_FILE_NAME_CONVERT失效。

RAC上任一节点上执行以下sql

alter system set log_archive_config='dg_config=(crmdb,dg)';

alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(online_logfiles,all_roles) db_unique_name=crmdb';

alter system set log_archive_dest_2='service=dg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dg';

alter system set log_archive_dest_3='location=/u01/arclog/stdlog/ valid_for=(standby_logfiles,standby_role) db_unique_name=crmdb';

alter system set fal_server='dg';

alter system set standby_file_management=auto;

alter system set db_file_name_convert='/u01/oracle/oradata/crmdb','+data/crmdb/datafile','/u01/oracle/oradata/crmdb','+data/crmdb/tempfile'scope=spfile;

alter system set log_file_name_convert='/u01/oracle/oradata/crmdb/arclog/prmlog','+data/crmdb/onlinelog' scope=spfile;

alter system set log_archive_max_processes=8;

 

13. 查看主备库归档是否异常

set lines 300

col dest_name for a30

col error for a20

select dest_name,status,error,target,process from v$archive_dest where rownum<=3;

 

14. 备库开始应用主库日志,并验证日志被应用

alter database recover managed standby database using current logfile disconnect from session;

主库执行:alter system archive log current;

备库查看:

set linesize 200

col name for a70

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select * from (select name,thread#,first_time,sequence#,applied from v$archived_log order by sequence# desc) where rownum<=5 order by sequence#;

 

15. 打开备库继续应用主库日志

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

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

此时可在主库的测试表上增删数据,查看备库同步情况。

 

16. 主库添加standby redolog

[oracle@db-rac1 ~]$ sqlplus / as sysdba

alter database add standby logfile thread 1 group 21 size 1024m;

alter database add standby logfile thread 1 group 22 size 1024m;

alter database add standby logfile thread 1 group 23 size 1024m;

alter database add standby logfile thread 1 group 24 size 1024m;

alter database add standby logfile thread 1 group 25 size 1024m;

alter database add standby logfile thread 1 group 26 size 1024m;

 

[oracle@db-rac2 ~]$ sqlplus / as sysdba

alter database add standby logfile thread 2 group 31 size 1024m;

alter database add standby logfile thread 2 group 32 size 1024m;

alter database add standby logfile thread 2 group 33 size 1024m;

alter database add standby logfile thread 2 group 34 size 1024m;

alter database add standby logfile thread 2 group 35 size 1024m;

alter database add standby logfile thread 2 group 36 size 1024m;

--alter database drop standby logfile group 31;

 

查看主库的standby logfile

select group#,thread#,sequence#,status from v$standby_log;

 

17. 删除主库多余的standby redo文件

在上一步建立standby redo时,默认也是一组两个redo文件,下面删除FRA中的standby redo

select 'alter database drop logfile member '''||member||''';' from v$logfile where member like '+FRA%';

 

18. 主库配置rman

rman target /

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

三、switchover步骤--手动主备切换

主备切换必须是一个主库和一个备库,所以RAC只留一个实例是open的,其他节点需要shut immediate

1. 分别查看主备库的数据库角色和数据保护模式

SQL> select protection_mode,database_role,open_mode,protection_level from v$database;

 

2. 主库切换当前redo到备库

SQL> alter system archive log current;

 

3. 主库查看是否可变为备库

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY --主可以直接切换为备

NOT ALLOWED --备库查为此状态,为没有切换标记,不能切换,此状态为正常状态,主变为备后,此状态会变。

SESSION ACTIVE --有活动会话

 

4. 主库变为备库

对于TO PRIMARY

SQL> alter database commit to switchover to physical standby;

对于SESSIONS ACTIVE

SQL> alter database commit to switchover to physical standby with session shutdown;

 

5. 主库切换成备库后重启,执行下列命令

shutdown abort

startup

 

6. 主变备之后,查看备库是否可变为主库

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

NOT ALLOWED --没有切换标记,不能切换,此状态为正常状态,主变为备后,此状态会变。

TO PRIMARY --可以直接切换

SESSION ACTIVE --有活动会话

 

7. 备库切换为主库

对于TO PRIMARY

SQL> alter database commit to switchover to primary;

对于SESSION ACTIVE

SQL> alter database commit to switchover to primary with session shutdown;

 

8. 打开新主库

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> alter database open;

 

9. 查看主备库归档是否异常

col dest_name for a30

col error for a20

select dest_name,status,error,target,process from v$archive_dest where rownum<=3;

 

10. 当前备库开始应用日志

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

 

11. 验证备库应用了日志

当前主库切换redo

SQL> alter system archive log current;

再在备库查看日志应用情况:

set linesize 200

col name for a70

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select * from (select name,thread#,first_time,sequence#,applied from v$archived_log order by sequence# desc) where rownum<=5 order by sequence#;

此时可在主库的测试表上增删数据,查看备库同步情况。

 

12. 主备切换之后,原主库如果要恢复成主库,重新执行1-11步。

 

 

 

四、failover步骤的准备工作

1.检查主备库运行模式

SQL> select protection_mode,database_role,open_mode,protection_level from v$database;

 

2.检查主备数据库都要有standby联机日志

SQL> select group# from v$standby_log;

 

3.备库停止应用日志

SQL> alter database recover managed standby database cancel;

 

4.主备数据库都要启动数据库的闪回功能

SQL> select flashback_on from v$database;

SQL> alter database flashback on;

如果该sql执行失败,检查是否用的spfile启库,如果是备库已用spfile启库,需停止应用日志后再执行该sql

 

5.备库再次开始应用日志

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

 

 

 

五、failover步骤--手动主备切换

主备切换必须是一个主库和一个备库,所以RAC只留一个实例是open的,其他节点需要关闭。

1. 查看主备库的数据库角色和数据保护模式

SQL> select protection_mode,database_role,open_mode,protection_level from v$database;

 

2. 主库模拟出现故障

SQL> shut abort

 

以下3-7步为备库切为主库的步骤:

3. 备库停止日志应用

SQL> alter database recover managed standby database cancel;

 

4. 备库结束日志应用

SQL> alter database recover managed standby database finish;

 

5. 查看备库是否可变为主库

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

NOT ALLOWED --没有切换标记,不能切换,此状态为正常状态,主变为备后,此状态会变。

TO PRIMARY --可以直接切换

SESSION ACTIVE --有活动会话

 

6. 备库切换为主库

对于TO PRIMARY

SQL> alter database commit to switchover to primary;

对于SESSION ACTIVE

SQL> alter database commit to switchover to primary with session shutdown;

 

7. 打开新主库

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> alter database open;

 

以下8-13步为原主库修好后,把它切为备库的步骤

8. 查当前主库切为主库时的SCN

SQL> select standby_became_primary_scn from v$database;

 

9. 把坏库(原主库)修好后,启动到mount,闪库到查到的SCN

SQL> startup mount

SQL> flashback database to scn xxxxx;

如果此时报ORA-38754: FLASHBACK DATABASE not started; required redo log is not available,请关掉当前报错的实例:SQL> shut immediate  然后到其他实例上执:SQL> flashback database to scn xxxxx;

 

10. 把坏库(原主库)切为备库,重启

SQL> alter database convert to physical standby;

SQL> select status from v$instance;

STATUS

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

STARTED

SQL> shut immediate

SQL> startup

 

11. 查看主备库归档是否异常

set lines 300

col dest_name for a30

col error for a20

select dest_name,status,error,target,process from v$archive_dest where rownum<=3;

当前主库的LOG_ARCHIVE_DEST_2如果报错,重新设置参数log_archive_dest_2,根据情况用下列sql之一:

dg作为主库,此时需要只留一个RAC实例启动:

alter system set log_archive_dest_2='service=crmdb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=crmdb';

crmdb作为主库:

alter system set log_archive_dest_2='service=dg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dg';

 

12. 当前备库开始应用日志

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

 

13. 验证备库应用了日志

在主库上切换redo

SQL> alter system archive log current;

在备库上查询:

SQL> select sequence#,applied,thread# from v$archived_log order by 1;

如果备库最后一行为YESNO,则证明备库应用了日志。

此时可在主库的测试表上增删数据,查看备库同步情况。

 

主备切换之后,此时可以正常switchover,原主库如果继续failover测试,重新执行1-13步。

 

 

 

六、主库崩溃时,手动将备库切成主库的脚本

[oracle@dg ~]$ vi std2prm.sh

#!/bin/bash

#用于DG主库崩溃时failover,把备库切成主库。

rm -rf log_std2prm.txt

sqlplus -s / as sysdba << EOF >> log_std2prm.txt

set head off

select 'alter database recover managed standby database cancel;' from dual;

alter database recover managed standby database cancel;

select 'alter database recover managed standby database finish;' from dual;

alter database recover managed standby database finish;

select 'alter database commit to switchover to primary with session shutdown;' from dual;

alter database commit to switchover to primary with session shutdown;

select 'alter database open;' from dual;

alter database open;

set head on

exit

EOF

echo -e "备库切成主库的sql已执行完毕\n\n" >> log_std2prm.txt

 

 

 

*****************************************截止线********************************************************

以下dg broker的配置不是很完善,需要执行少量手动操作来辅助完成原主库切成备库。

 

 

 

七、配置broker(自动主备切换)的准备工作

使用dg broker可以使用简单的命令自动执行switchoverfailover操作。

oracle建议把broker配置在非主备库上,这里在ip192.168.100.86的主机上配置broker,这里把192.168.100.86成为观察者机,观察者机需要安装一个oracle库或安装一个oracle客户端。

1. 配置观察者的hosts文件

把主备库任一主机的hosts文件主要内容追加192.168.100.86hosts文件中

 

2. 配置观察者的tnsnames文件

把主备库任一主机的tnsnames文件scp192.168.100.86

[oracle@db-rac2 ~]$ scp /u01/oracle/db/network/admin/tnsnames.ora 192.168.100.86:/u01/oracle/db/network/admin/

 

3. 测试观察者机的tnsnames配置

[oracle@cc ~]$

tnsping crmdb

tnsping dg

 

4. 主备库监听分别添加数据库服务

主库使用grid用户添加crmdb_DGMGRL,备库使用oracle用户添加dg_DGMGRL

[grid@db-rac1 ~]$ lsnrctl reload

[grid@db-rac2 ~]$ lsnrctl reload

[oracle@dg ~]$ lsnrctl reload

 

4. 主备数据库修改参数local_listener

备库:SQL> alter system set local_listener='dg';--dgdb_unique_name

RACSQL> show parameter local_listener    --rac上不需要修改

 

5. RACbroker的配置文件放到共享存储上

任一RAC节点上执行:

su - grid

[grid@db-rac2 ~]$ asmcmd

ASMCMD> cd +data/crmdb/

ASMCMD> mkdir dgbroker

 

su - oracle

SQL> show parameter dg_broker_config_file

NAME                                 TYPE        VALUE

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

dg_broker_config_file1               string      /u01/oracle/db/dbs/dr1crmdb.dat

dg_broker_config_file2               string      /u01/oracle/db/dbs/dr2crmdb.dat

 

alter system set dg_broker_config_file1='+DATA/crmdb/dgbroker/dr1crmdb.dat';

alter system set dg_broker_config_file2='+DATA/crmdb/dgbroker/dr2crmdb.dat';

 

5. 主备数据库都要启用broker

SQL> show parameter dg_broker_start

SQL> alter system set dg_broker_start=true;

 

6. 查看主备库归档终目的地是否有异常:

set lines 300

col dest_name for a30  

col error for a20

select dest_name,status,target,archiver,error,process from v$archive_dest where rownum<=3;

 

7. 备库停止应用日志:

alter database recover managed standby database cancel;

 

 

 

八、配置broker(自动主备切换)

1.在观察者机上登录主库

[oracle@dg ~]$ dgmgrl

DGMGRL> connect sys/***@crmdb

 

2.创建中介配置

DGMGRL> create configuration dbBroker as primary database is crmdb connect identifier is crmdb;

DGMGRL> add database dg as connect identifier is dg maintained as physical;

 

3.设置主备库的faststartfailovertarget

DGMGRL> edit database crmdb set property faststartfailovertarget=dg;

DGMGRL> edit database dg set property faststartfailovertarget=crmdb;

 

4.查看主备数据库的配置

DGMGRL> show database verbose crmdb -----crmdbRAC唯一名

DGMGRL> show database verbose dg  -----dg为备库唯一名

确认主备数据库LogXptMode=async(最大性能为async,最大可用为sync),不是则执行 :

DGMGRL> edit database dg set property LogXptMode = async;   --这里假设dgLogXptMode='sync'

 

5.启用broker配置

DGMGRL> enable configuration

 

可修改快速故障转移的延迟时间(默认为30秒,即主库30秒检测不到则切换主备库,但思科交换机插上网线的反应时间为35秒左右,此值应该加大,防止网线误碰时发生主备切换):

DGMGRL> edit configuration set property FastStartFailoverThreshold=60; 延迟时间改为1分钟。

 

6. 检查配置情况

DGMGRL> show database crmdb

DGMGRL> show database dg

DGMGRL> show configuration verbose

 

7.启用快速failover

DGMGRL> enable fast_start failover

 

 

8.启动Observer监视器

DGMGRL> start observer

启动之后,该前台进程不会退出,会一直挂在这。 直到从其他窗口关闭!

 

9.在备库新建窗口再启动一个dgmgrl,查看快速转移配置是否成功

DGMGRL> connect sys/******@dg

DGMGRL> show configuration verbose

DGMGRL> show fast_start failover

DGMGRL> show database verbose crmdb

DGMGRL> show database verbose dg

 

10.在主库上检查是否可以切换到备库

col fs_failover_observer_present for a10

col fs_failover_observer_host for a30

col fs_failover_threshold for 999999

select fs_failover_observer_present,fs_failover_current_target,fs_failover_threshold from v$database;

 

 

 

九、使用broker自动switchover

主备切换必须是一个主库和一个备库,所以RAC只留一个实例是open的,其他节点需要关闭,这里是crmdb1的实例已关闭。

1. 查看主备库的数据库角色和数据保护模式

SQL> select protection_mode,database_role,open_mode,protection_level from v$database;

 

2. 使用dgmgrl客户端登录

在观察者机上,新打开一个终端,使用dgmgrl命令登录当前主库。

[oracle@dg ~]$ dgmgrl

DGMGRL> connect sys/***@crmdb

 

3. dg切成主库

DGMGRL> switchover to dg;

dg切成主库后,提示需要start up instance "crmdb2" of database "crmdb"

于是手动启动实例crmdb2

[oracle@db-rac2 ~]$ sqlplus / as sysdba

SQL> startup

 

4. 查看主备库的数据库角色和数据保护模式

SQL> select protection_mode,database_role,open_mode,protection_level from v$database;

如果主库是mount,需要把库open

 

5. 查看主备库归档是否异常

col dest_name for a30

col error for a20

select dest_name,status,error,target,process from v$archive_dest where rownum<=3;

 

6. 验证备库应用了日志

在主库上切换redoSQL> alter system archive log current;

再在备库查看日志应用情况:

SQL> select sequence#,applied,thread# from v$archived_log order by 1;

此时可在主库的测试表上增删数据,查看备库同步情况。

 

 

 

十、使用broker自动failover

主库发生以下情况时,备库会切成主库:

1) Instance Failure

2) Shutdown Abort

3) Offline Datafiles due to I/O error

4) Network disconnection

注意:主库执行shutdown immediate是正常关库,并不会导致主备库切换

 

1. 验证备库应用了日志

SQL> select sequence#,applied,thread# from v$archived_log order by 1;

 

2.查看DG主备的日志 

[oracle@db-rac1 ~]$ vi /u01/oracle/diag/rdbms/crmdb/crmdb1/trace/drccrmdb1.log

[oracle@db-rac2 ~]$ vi /u01/oracle/diag/rdbms/crmdb/crmdb2/trace/drccrmdb2.log

[oracle@dg ~]$ tail -f /u01/oracle/diag/rdbms/dg/crmdb/trace/drccrmdb.log

 

如果备库dg日志的最后一行为LGWR: persisting FSFO state flags=0x44001, version=2, obid=0x3e50273c (1045440316), threshold=30, laglim=30, obslim=30 才说明备库才可以切换成主库。注意此时flags=0x44001

 

3. 查看主备库的数据库角色和数据保护模式

SQL> select protection_mode,database_role,open_mode,protection_level from v$database;

 

4. 主库上执行shut abort

SQL> shut abort

 

3. 在观察者机上查看start observer后的变化,可观察到备库被切换成了主库。

 

4. 主库关掉大概50秒后,在原备库上查看dg02的状态,已切成了主库:

SQL> select protection_mode,database_role,open_mode,protection_level from v$database;

PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE            PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE  PRIMARY          READ WRITE           MAXIMUM PERFORMANCE

 

5. 把原主库修好后,执行startup mount

 

6. 观察者机上查看observer的状态变化,observer自动检查到原主库起来了,会把原主库初始化,期间会把原主库重启,重启完后以备库身份存在,并应用新主库传来的日志,日志应用的过程会比较慢。

如果observer没有变化或有错误提示,需要修复。

把坏库启动到mount下:

SQL> startup mount

在观察者机上新建一个终端,连接现在的新主库,然后执行修复操作:

DGMGRL> DGMGRL> connect sys/***@dg

DGMGRL> reinstate database crmdb;

修复过的库状态已经是open,并已开始自动应用日志。

SQL> select status from v$instance;

注意:如果修复的库是RAC,则所有RAC节点都会成open状态,此时需要关库,只留下一台在open状态。

 

7. 查看主备库归档是否异常

col dest_name for a30

col error for a20

select dest_name,status,error,target,process from v$archive_dest where rownum<=3;

 

8. 验证备库应用了日志

在主库上切换redoSQL> alter system archive log current;

再在备库查看日志应用情况:

SQL> select sequence#,applied,thread# from v$archived_log order by 1;

此时可在主库的测试表上增删数据,查看备库同步情况。

 

 

 

十一、删除DG broker

连接当前主库:DGMGRL> connect sys/***@crmdb

停观察者:DGMGRL> stop observer

关快速自动切换:DGMGRL> disable fast_start failover

禁用配置:DGMGRL> disable configuration

删配置:DGMGRL> remove configuration

检查是否还有dgmgrl进程: ps -ef | grep dgmgrl

dgmgrl进程则杀之:killall dgmgrl

 

主备数据库都把参数dg_broker_start禁用:

SQL> show parameter dg_broker_start

SQL> alter system set dg_broker_start=false;

 

 

 

十二. 定时删除DG备库的归档文件

定时删除备库已经应用的半天前的归档文件,脚本名称为:/home/oracle/shell/del_arc.sh,内容如下:

#!/bin/bash

#本脚本用于删除备库已应用的半天前的归档文件

source /home/oracle/.bash_profile

rman target / << EOF

delete noprompt archivelog until time 'sysdate-0.5';

exit

EOF

 

在备库上添加到定时任务中:

[oracle@ ~]$ crontab -e

0 4 * * * /home/oracle/shell/del_arc.sh

每天主库2:00rman备份,所以把这个脚本执行时间放到了每天4:00执行。


  评论这张
 
阅读(64)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017