首页 > 代码库 > oracle不停库搭建dg

oracle不停库搭建dg


通过rman备份不关库做dg 

假设主库db_unique_name=orcl
  备库db_unique_name=orcl2

1.主库归档目录,以oracle用户建立
[root@orcl ~]# su - oracle
[oracle@orcl ~]$ cd /u01/oracle/oradata/orcl/arclog/
[oracle@orcl ~]$ mkdir prmlog  --主库时归档路径
[oracle@orcl ~]$ mkdir stdlog  --主库转换成备库时接收redo的路径

2.主库改为强制日志模式:
[oracle@orcl ~]$ sqlplus / as sysdba
alter database force logging;

3.tnsnames中添加orcl、orcl2,并相互ping通
tnsping orcl
tnsping orcl2

4.rman备份主库,利用当晚的rman备份即可,我的rman备份脚本如下:
#!/bin/bash
rman target / <<EOF
run {
allocate channel c1 type disk maxpiecesize=20g;
allocate channel c2 type disk maxpiecesize=20g;
backup database format ‘/u01/rman/db_%d_%s_%p_%u_%T.dbf‘;
sql "alter system archive log current";
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
backup archivelog all format ‘/u01/rman/arc_%d_%u_%T.arc‘ delete all input;
backup current controlfile format ‘/u01/rman/ctf_%d_%u_%T.ctl‘;
release channel c1;
release channel c2;
}
EOF

5.主库上为备库建立控制文件
alter database create standby controlfile as ‘/u01/standby.ctl‘ reuse;

6.主库建立pfile
create pfile from spfile;

7.并拷贝主库的文本参数、口令文件、tnsnames文件、rman备份、standby控制文件到备库
scp /u01/oracle/orcl/dbs/initorcl.ora 192.168.1.69:/u01/oracle/orcl/dbs/
scp /u01/oracle/orcl/dbs/orapworcl 192.168.1.69:/u01/oracle/orcl/dbs/
scp -r /u01/rman/  192.168.1.69:/u01/
scp /u01/standby.ctl 192.168.1.69:/u01/oracle/oradata/orcl/control01.ctl
scp /u01/standby.ctl 192.168.1.69:/u01/oracle/flash_recovery_area/orcl/control02.ctl

8.在备库文本参数文件修改或添加以下内容
db_unique_name=orcl2
log_archive_config=‘dg_config=(orcl,orcl2)‘
log_archive_dest_1=‘location=/u01/oracle/oradata/orcl/arclog/prmlog/  valid_for=(online_logfiles,all_roles)  db_unique_name=orcl2‘
log_archive_dest_2=‘service=orcl lgwr async valid_for=(online_logfiles,primary_role)  db_unique_name=orcl‘
log_archive_dest_3=‘location=/u01/oracle/oradata/orcl/arclog/stdlog/ valid_for=(standby_logfiles,standby_role) db_unique_name=orcl2‘
log_archive_max_processes=6
fal_server=orcl
fal_client=orcl2
standby_file_management=auto
log_file_name_convert=‘/u01/oracle/oradata/orcl/‘,‘/u01/oracle/oradata/orcl/‘   --避免新备库的redo放到闪回区中

9.备库建立spfile
create spfile from pfile;

10.备库建立跟主库一样的数据文件、归档文件路径
cd /u01/oracle/oradata/orcl
mkdir arclog
cd arclog
mkdir prmlog stdlog

11.rman恢复
rman target /
RMAN> startup mount
RMAN> restore database;
RMAN> recover database;
RMAN> exit
此时检查一下备库的redo文件是否存在

12.主备库添加redo文件
alter database add standby logfile ‘/u01/oracle/oradata/orcl/std_redo01.log‘ size 50m;
alter database add standby logfile ‘/u01/oracle/oradata/orcl/std_redo02.log‘ size 50m;
alter database add standby logfile ‘/u01/oracle/oradata/orcl/std_redo03.log‘ size 50m;
alter database add standby logfile ‘/u01/oracle/oradata/orcl/std_redo04.log‘ size 50m;

13.修改现主库参数
alter system set db_unique_name=orcl scope=spfile;
alter system set log_archive_config=‘dg_config=(orcl,orcl2)‘;
alter system set log_archive_dest_1=‘location=/u01/oracle/oradata/orcl/arclog/prmlog/  valid_for=(online_logfiles,all_roles)  db_unique_name=orcl‘;
alter system set log_archive_dest_2=‘service=orcl2 lgwr async valid_for=(online_logfiles,primary_role)  db_unique_name=orcl2‘;
alter system set log_archive_dest_3=‘location=/u01/oracle/oradata/orcl/arclog/stdlog/ valid_for=(standby_logfiles,standby_role) db_unique_name=orcl‘;
alter system set log_archive_max_processes=6;
alter system set fal_server=orcl2;
alter system set fal_client=orcl;
alter system set standby_file_management=auto;

14.查看主备库归档是否异常
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为inactive,修改如下:
alter system set log_archive_dest_2=‘service=orcl lgwr async valid_for=(online_logfiles,primary_role)  db_unique_name=orcl‘;

15.打开备库
sqlplus / as sysdba
alter database open;
--如果此时报以下错误,可跳过本步,先执行12、13步后,再执行本步,然后执行12步。
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/u01/oracle/oradata/orcl/system01.dbf‘


16.备库启动redo应用,并验证是否已开始应用
alter database recover managed standby database using current logfile disconnect from session;
select sequence#,applied from v$archived_log;

17.停止sql应用命令(此处不执行)
alter database stop logical standbyapply;
------------------------------------------------------------------------------------

利用观察者实现自动failover

一).准备工作
1.主备库监听分别添加数据库服务:orcl_DGMGRL或orcl2_DGMGRL:
cd /u01/oracle/orcl/network/admin
在主库监听listener.ora中添加
(SID_DESC =
      (GLOBAL_DBNAME = orcl_DGMGRL)
      (ORACLE_HOME = /u01/oracle/orcl)
      (SID_NAME = orcl)
    )
GLOBAL_DBNAME参数格式:<db_unique_name>_DGMGRL.<db_domain> 的连接,并且db_unique_name 大小写敏感。
在备库库监听listener.ora中添加
(SID_DESC =
      (GLOBAL_DBNAME = orcl2_DGMGRL)
      (ORACLE_HOME = /u01/oracle/orcl)
      (SID_NAME = orcl)
    )

2.主备库分别重新加载监听:
lsnrctl reload

3.测试监听,分别在主备库上都执行:
tnsping orcl
tnsping orcl2

4.检查主备库运行模式:
主备数据库都要运行在最大性能模式:
sqlplus / as sysdba
SQL> select protection_mode,database_role,protection_level from v$database;

5.检查主备数据库都要有standby联机日志:
SQL> select group# from v$standby_log;

6.备库停止应用日志:
alter database recover managed standby database cancel;

7.主备数据库都要启动数据库的闪回功能:
SQL> select flashback_on from v$database;
SQL> alter database flashback on;
如果该sql执行失败,检查是否用的spfile启库,如果是备库已用spfile启库,需停止应用redo后再执行该sql。

8.加大闪回区:
SQL> alter system set db_recovery_file_dest_size=50g;
SQL> show parameter db_recovery_file_dest_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 50G

9.主备数据库修改参数local_listener:
主库SQL> alter system set local_listener=‘orcl‘;   --orcl为监听服务名
备库SQL> alter system set local_listener=‘orcl2‘;   --orcl2为监听服务名

10.主备数据库都要启用broker:
SQL> alter system set dg_broker_start=true;

11.查看主备库归档终目的地是否有异常:
set lines 400
col dest_name for a30
col error for a20
select dest_name,status,target,archiver,error,process from v$archive_dest where rownum<=3;

官方建议在第三台机上设置observer,用来检测主备库状态。并在observer machine上配置相关的tnsnames参数,使第三台机器能访问主备库的实例。
然后通过这个observer来判断主备库的状态。如果主库出现问了,那么observer就会把备库切换成主库。
放在第三台机器上的原因也很明显,如果放在主库上,如果主库系统坏掉了,那么Observer也就失效了。
如果放在备库上,备库网络或系统故障会导致主库库关闭,同时备库自动切为主库。

二).配置观察者:
1.在观察者上登录主库:
[oracle@dg03 ~]$ dgmgrl
DGMGRL> connect sys/******@orcl

2.创建中介配置:
DGMGRL> create configuration ‘orclBroker‘ as primary database is ‘orcl‘ connect identifier is orcl;

3.在中介配置中添加备用数据库:
DGMGRL> add database ‘orcl2‘ as connect identifier is orcl2 maintained as physical;

4.查看verbose和快速切换状态:
DGMGRL> show configuration verbose;
DGMGRL> show fast_start failover


5.查看主备数据库的配置:
DGMGRL> show database verbose orcl;  -----orcl为主库唯一名
DGMGRL> show database verbose orcl2;  -----orcl2为备库唯一名
确认主备数据库LogXptMode=‘async‘(最大性能为async,最大可用为sync),不是则执行 :
DGMGRL> edit database orcl2 set property LogXptMode = ‘async‘;   -----这里假设orcl的LogXptMode=‘async‘

6.启动broker配置:
DGMGRL> enable configuration

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

7.启动快速故障转移:
DGMGRL> enable fast_start failover

8.启动Observer监视器:
DGMGRL> start observer
启动之后,该前台进程不会退出,会一直挂在这。 直到从其他窗口关闭!

9.在备库新建窗口再启动一个dgmgrl,查看快速转移配置是否成功:
DGMGRL> connect sys/******@orcl2
DGMGRL> show configuration verbose;
DGMGRL> show fast_start failover
DGMGRL> show database verbose orcl;
DGMGRL> show database verbose orcl2;

10.在主库上检查是否可以切换到备库:
SQL> select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;

本文出自 “健康快乐就是幸福” 博客,请务必保留此出处http://yiqiuyu.blog.51cto.com/3228402/1580915

oracle不停库搭建dg