RAC-RAC DataGuard配置
一、配置信息
1.1、搭建类别
1.1.1、第一种为备份恢复方式搭建dataguard，本次采用的是该方式
1.1.2、第二种为duplicate方式(主库克隆到备库)
1）：在primary端的tnsnames.ora文件中增加UR=A 参数，连接standby数据库
该方法是不需要修改监听（见标题：第二种方法）
2）：如果路径不一致必须要修改db_file_name_convert，log_file_name_conver参数，如果不修改在克隆数据库时会遇到错误
3）：非ASM方式，单对单方式，要创建数据文件存放目录
1.2、环境介绍：
主数据库安装集群软件和数据库软件，建库
备数据库只需要安装集群软件和数据库软件即可，不需要建库
| 类别 | 主库 | 备库 |
| clusterware | 11g R2 Grid Infrastructure (11.2.0.1) | 11g R2 Grid Infrastructure (11.2.0.1) |
| cluster nodes | dbrac1,dbrac2 | dbrac1,dbrac2 |
| IP Address | public:192.168.1.160/161 vip : 192.168.1.162/163 scan : 192.168.1.200 | public:192.168.1.170/171 vip : 192.168.1.172/173 scan : 192.168.1.174 |
|
| ||
| DB Version | 11.2.0.1 | 11.2.0.1 |
| instance_name | racdb1,racdb2 | racdb1,racdb2 |
| db_name | racdb | racdb |
| db_unique_name | racdb | standbydb |
| service_names | racdb | standbydb,racdb |
| ASM storage | ASM | ASM |
| File Management | OMF | OMF |
| ASM Diskgroup | +ASMDATA | +ADATA |
| OS Version | Red Hat Enterprise Linux Server release 5.5 (Tikanga) 64 | Red Hat Enterprise Linux Server release 5.5 (Tikanga) 64 |
二、网络配置
2.1、Primary端
2.1.1、修改tnsnames.ora
oracle用户
节点1、节点2：Tnsnames.ora 添加如下内容:
STANDBYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.172)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standbydb)
(INSTANCE_NAME= racdb1)
)
)
第二种方法(后加)：
增加UR=A参数，允许连接到一个为监听为blocked状态的数据库
如下：
standbydb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2.standby.com)(PORT = 1521))
)
(CONNECT_DATA = (UR = A)
(SERVICE_NAME = standbydb)
(INSTANCE_NAME = orcl)
)
)
2.2、standby端
2.2.1、静态监听配置
grid用户:
[grid@racdb1 admin]$ vi listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standbydb)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
(SID_NAME = racdb1)
)
)
oracle用户：
Tnsnames.ora添加如下内容
PRIMARYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.160)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
)
)
STANDBYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.172)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standbydb)
(INSTANCE_NAME = racdb1)
)
)
测试standby端到primary端的连通性
standbydb端：
tnsping primary
sqlplus sys/oracle@primarydb as sysdba
set line 200
select instance_name,host_name from v$instance;
2.2.1、第二种方法：
这种方法不需要添加静态监听，只需要在tnsname.ora中添加UR=A即可
注意:（UR=A)）是连接到一个监听BLOCKED的未mount的数据库实例
STANDBYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.172)(PORT = 1521))
)
(CONNECT_DATA = (UR = A)
(SERVICE_NAME = standbydb)
(INSTANCE_NAME= racdb1)
)
)
2.3、复制密码文件到standby端
主库：
拷贝密码文件到备端
SCP $ORACLE_HOME/dbs/oraracdb1 oracle@racdb1:/$ORACLE_HOME/dbs
SCP $ORACLE_HOME/dbs/oraracdb1 oracle@racdb1:/$ORACLE_HOME/dbs/oraracdb2
2.5、primary数据库运行在force logging模式下
查看数据库是否运行在force logging模式下，运行以下命令：
SQL> select FORCE_LOGGING from v$database
如果是NO那么修改一下命令修改：
SQL> alter database force logging;
2.6、配置DataGuard参数
--备份spifle文件，失败后可以重新启动数据库
SQL>create pfile='/home/oracle/initold.ora' from spfile;
--
SQL> alter system set log_archive_config='DG_CONFIG=(racdb,standbydb)' scope=both sid='*';
--本地归档文件存放目录
1、ALL_LOGFILES
为该目标位置归档联机的和备用数据库的重做日志。
2、PRIMARY_ROLE
只在是主数据库角色时归档到该目标位置
3、ALL_ROLES
无论是主数据库角色还是备用数据库角色，均归档到该目标位置
当处于主数据库角色时，我们只将联机的重做日志归档到目标位置1。
因为备用数据库的重做日志处于主数据库角色时是不被激活的。
同时会将联机重做日志归档到目标位置2。
SQL> alter system set log_archive_dest_1='location=/archivelog valid_for=(all_logfiles,primary_role) db_unique_name= racdb' scope=both sid='*';
--当本地有归档日志发生时，这个日志会被传送到standby这个service所指向的数据库所在的主机。 （service=standbydb,standbydb是在tnsnames.ora中创建的网络服务名）
SQL> alter system set log_archive_dest_2='service=standbydb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=standbydb' scope=both sid='*';
SQL> alter system set log_archive_dest_3='location=/standby_log valid_for=standby_logfile,standby_role db_unique_name=racdb' scope=both sid='*'
SQL> alter system set fal_client='racdb' scope=both sid='*'
SQL> alter system set fal_server='standbydb' scope=both sid='*'
SQL> alter system set standby_file_management=auto scope=both sid='*'
SQL>alter system set log_archive_max_processes=5 scope=both sid='*'
2.6.1、创建pfile文件
1):主库创建pfile
SQL> create pfile='/home/oracle/init.ora' from spfile;
2): 拷贝init.ora参数文件到standby端
scp init.ora oracle@racdb1:/home/oracle
2.7、创建standby日志组文件
节点1：
SQL> alter database add standby logfile group 7 size 50M;
SQL> alter database add standby logfile group 8 size 50M;
SQL> alter database add standby logfile group 9 size 50M;
SQL> alter database add standby logfile group 10 size 50M;
节点2：
SQL> alter database add standby logfile group 11 size 50M;
SQL> alter database add standby logfile group 12 size 50M;
SQL> alter database add standby logfile group 13 size 50M;
SQL> alter database add standby logfile group 14 size 50M;
2.8、创建standby控制文件
SQL>alter database create standby controlfile as '/home/oracle/standctl01.ctl
2.9、备份源端数据库
因直接duplicate报一下错误：
ORA-17627: ORA-12577: Message 12577 not found; product=RDBMS; facility=ORA
所以采取备份恢复方式
---已解决：原因是没有设置参数log_file_name_conver，db_file_name_convert
2.9.1、备份脚本(第一种方法)
备份数据库：
#!/bin/bash
bk_date=`date +%Y_m%_d%`
rman target / log='/home/oracle/rmanlog/$bk_date.log' append <
run{
allocate channel c1 type disk connect sys/oracle@racdb1;
allocate channel c2 type disk connect sys/oracle@racdb2;
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt obsolete;
sql 'alter system switch logfile';
backup database skip inaccessible format '/rman/db_$bk_date_%T_%s';
sql 'alter system switch logfile';
backup archivelog all delete input format '/rman/arch$bk_date_%T_%s';
backup current controlfile format '/rman/ctl$bk_date_%T_%s';
release channel c1;
release channel c2;
}
EOF
2.9.2、备份完成后传输到备库
两个节点都需要把备份传输到备库指定目录下
主库节点1：
scp * oracle@racdb1:/rman
主库节点2：
scp * oracle@racdb1:/rman
2.9.3、克隆数据库(第二种方法)
1):primary端:
先连接primary数据库，然后再连接standby数据库
rman target sys/oracle@primary auxiliary sys/oracle@racdb
执行以下命令：
rman>duplcate target database for standby from active database dorecover nofilenamecheck
执行完该条命令以后，如果需要可以把备库open
三、standby端配置
3.1、pfile参数修改
修改如下参数：
racdb1.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
racdb2.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/opt/app/oracle/admin/racdb/adump'
*.control_files='+ADATA/STANDBYDB/CONTROLFILE/standctl01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+ADATA'
racdb1.db_file_name_convert='+ASMDATA','+ADATA'
racdb2.db_file_name_convert='+ASMDATA','+ADATA'
*.db_name='racdb'
*.db_unique_name='standbydb'
*.diagnostic_dest='/opt/app/oracle'
racdb1.fal_server='standbydb'
racdb2.fal_server='standbydb'
*.log_archive_config='DG_CONFIG=(standbydb,racdb)'
racdb1.log_archive_dest_1='location=/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standbydb'
racdb2.log_archive_dest_1='location=/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standbydb'
racdb1.log_archive_dest_2='service=primarydb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=racdb'
racdb2.log_archive_dest_2='service=primarydb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=racdb'
racdb1.log_archive_dest_3='location=/standby_log valid_for=standby_logfile,standby_role db_unique_name=standby'
racdb2.log_archive_dest_3='location=/standby_log valid_for=standby_logfile,standby_role db_unique_name=standby'
*.remote_listener='racdb-scan:1521'
racdb1.service_names='standbydb','racdb'
racdb2.service_names='standbydb','racdb'
第二种方法需要添加以下参数
----增加log_file_name_conver和db_file_name_convert参数
格式: db_file_name_convert='主库路径','备库路径'，db_file_name_convert='主库路径','备库路径'
racdb1. log_file_name_conver='+ASMDATA','ADATA'
racdb2.log_file_name_conver='+ASMDATA','ADATA'
racdb1. db_file_name_conver='+ASMDATA','ADATA'
racdb2.db_file_name_conver='+ASMDATA','ADATA'
3.2、启动到nomount
mkdir -p $ORACLE_BASE/admin/racdb/adump
sqlplus / as sysdba
startup nomount pfile='/home/oracle/init.ora'
3.3、恢复standby数据库（第一种方法）
standby端：
oracle用户:
rman target sys/oracle@primarydb auxiliary /
duplicate target database for standby nofilenamecheck;
3.4、启动redo应用
--启动日志实时应用
sql>alter database open
sql> alter database recover managed standby database using current logfile disconnect from session;
此时数据库是READ ONLY WITH APPLY 状态
观察日志传输情况：
primary端的alert*.log：
Wed Dec 11 06:46:43 2013
LGWR: Standby redo logfile selected for thread 1 sequence 152 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 152 (LGWR switch)
Current log# 1 seq# 152 mem# 0: +ASMDATA/racdb/onlinelog/group_1.262.825044607
Wed Dec 11 06:46:46 2013
Archived Log entry 482 added for thread 1 sequence 151 ID 0x3108c1ff dest 1:
standby端的alert*.log：
Media Recovery Waiting for thread 1 sequence 152 (in transit)
Wed Dec 11 14:46:51 2013
RFS[6]: Selected log 7 for thread 1 sequence 152 dbid 800562524 branch 825044606
Recovery of Online Redo Log: Thread 1 Group 7 Seq 152 Reading mem 0
Mem# 0: +ADATA/standbydb/onlinelog/group_7.266.83381979
3.5、修改控制文件路径
alter system set control_files='+ADATA/standbydb/controlfile/standctl01.ctl' sid='*'
3.6、创建spfile参数文件
create spfile='+ADATA' from '/home/oracle/init.ora'
编辑spfile指向，如果不知道spfile的具体位置，可以用grid用户通过asmcmd命令查找
节点1:
vi $ORACLE_HOME/dbs/initracdb1.ora
spfile='+adata/standbydb/parameterfile/spfileracdb.ora'
节点2：
vi $ORACLE_HOME/dbs/initracdb2.ora
spfile='+adata/standbydb/parameterfile/spfileracdb.ora'
3.7、注册数据库到集群
oracle：用户
1):注册数据库：
srvctl add database -d standbydb -o $ORACLE_HOME
2):添加实例：
srvctl add instance -d standbydb -i racdb1 -n racdb1
srvctl add instance -d standbydb -i racdb2 -n racdb2
3.8、恢复控制文件到ASM磁盘
3.8.1、关闭数据库
sqlplus / as sysdba
1):停止日志应用
SQL> alter database recover managed standby database cancel;
2):关闭数据库
SQL>shutdown immdiate;
3.8.2、启动数据库到nomount
sqlplus / as sysdba
startup nomount
3.8.3、恢复controlfile到+ADATA盘
rman target /
rman>resotre controlfile to '+ADATA/standbydb/controlfile/standctl01.ctl' from '/home/oracle/ standctl01.ctl'
3.8.4、打开数据库
1):启动到mount
sql>alter database mount;
2):启动到open
sql>alter database open;
3.8.5、启动日志实时应用
sql>alter database recover managed standby database using current logfile disconnect from session;
3.8.6、删除静态监听
grid用户:
cd $ORACLE_HOME/network/admin
删除之前添加的:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standbydb)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
(SID_NAME = racdb1)
)
)
3.8.7、重启监听
grid
1):lsnrctl reload
2):查看监听状态
[oracle@racdb1 ~]$ lsnrctl status
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "racdb" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "standbydb" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
3.9、节点2网络配置
3.9.1、tnsnames.ora
拷贝standby节点1的tnsnames.ora到节点2的$ORACLE_HOME/network/admin目录下修改IP地址
3.9.2、启动节点2数据库
grid:用户
1):srvctl start instance -d standbydb -i racdb2
3.9.3、启动日志实时应用:
sqlplus / as sysdba
sql>alter database recover managed standby database using current logfile disconnect from session;
此时2节点不会应用日志，恢复进程在节点1上，节点2为休眠状态
select * from v$archive_gap;
select process, client_process, sequence#, status from v$managed_standby;
select sequence#, first_time, next_time, applied from v$archived_log;
select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;
select thread#, max (sequence#) from v$log_history group by thread#;
select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;
四、配置DataGuard Broker(未完成)
SQL> alter system set dg_broker_config_file1='+asmdata/racdb/DATAGUARDCONFIGURE/dr1racdb.dat' scope=both sid='*';
SQL> alter system set dg_broker_config_file2='+asmdata/racdb/DATAGUARDCONFIGURE/dr2racdb.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file1='+adata/standbydb/DATAGUARDCONFIGURE/dr1standbydb.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='+adata/standbydb/DATAGUARDCONFIGURE/dr2standbydb.dat' scope=both sid='*';
System altered.
SQL>exit
[oracle@dbrac1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@racdb
Connected.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> CREATE CONFIGURATION 'DG_Config' AS PRIMARY DATABASE IS 'racdb' CONNECT IDENTIFIER IS 'racdb';
Configuration "DG_Config" created with primary database "racdb"
DGMGRL> show configuration;
Configuration - DG_Config
Protection Mode: MaxPerformance
Databases:
racdb - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> ADD DATABASE 'standbydb' AS CONNECT IDENTIFIER IS standbydb;
Database "standbydb" added
DGMGRL> show configuration;
Configuration - DG_Config
Protection Mode: MaxPerformance
Databases:
racdb - Primary database
standbydb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13634698/viewspace-1273972/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/13634698/viewspace-1273972/
167
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
