首页 > 代码库 > 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