搭建dataguard主要是为了防止主库宕机后，备库能够切换为主库，但是当备库切换为主库后，那么客户端tns也需要面临着修改，如果客户端特别多，或者说有的应用已经嵌入了连接方式，这样去改特别繁琐。
因此，能不能在DG备库切换为主库后，所有的客户端都不用更改任何配置而实现正常访问呢？ 答案是可以的。只需要修改备库的ip为主库ip。
只是，这个要注意一些参数的设置：
db_unique_name 一定不能一致
service_names 必须一致。 客户端tns连接一般是通过service_names连接,如果service_names主备库不一致，那备库切换为主库后，除了修改备库上ip还需要修改客户端tns，或者再在新主库上去修改数据库名字，那样的话太麻烦了
先看一下，如果db_unique_name设置为一致时，会产生什么效果：
----在主库上切换日志，然后主库直接宕机了，看告警日志：
Mon Jul 18 01:01:02 2016
Thread 1 cannot allocate new log, sequence 10
Private strand flush not complete
Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Archival destination is a Primary RAC instance: 'dg_22'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_4048.trc (incident=6089):
ORA-00600: internal error code, arguments: [krsu_upi_atc.7], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6089/orcl_lgwr_4048_i6089.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_4048.trc:
ORA-00600: internal error code, arguments: [krsu_upi_atc.7], [], [], [], [], [], [], [], [], [], [], []
LGWR (ospid: 4048): terminating the instance due to error 470
Mon Jul 18 01:01:05 2016
System state dump requested by (instance=1, osid=4048 (LGWR)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_4038_20160718010105.trc
Dumping diagnostic data in directory=[cdmp_20160718010105], requested by (instance=1, osid=4048 (LGWR)), summary=[abnormal instance termination].
Instance terminated by LGWR, pid = 4048
Mon Jul 18 01:01:23 2016
Adjusting the default value of parameter parallel_max_servers
from 160 to 120 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 4
Number of processor cores in the system is 4
Number of processor sockets in the system is 2
CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
NUMA status: non-NUMA system
cellaffinity.ora status: N/A
CELL communication will use 1 IP group(s):
Grp 0:
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
ARCH: Warning; less destinations available than specified
by LOG_ARCHIVE_MIN_SUCCEED_DEST init.ora parameter
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
----处理方法：
----主库上：
alter system set log_archive_dest_2='service=dg_22 sync affirm net_timeout=10 valid_for=(online_logfile,primary_role) db_unique_name=dg_22';
----备库上：
alter system set db_unique_name=dg_22 scope=spfile;
shut immediate
startup mount
alter system set service_names=orcl; ----服务名和主库一致
最后再开启日志应用，这时主库切换日志后，可以看到备库能正常应用日志，并且主库没有出现任何异常了。
下面是我搭建DG的整个过程：
DG搭建（一主一备）
| 节点 | 网络ip地址 | 数据库名 | unique name | 数据库实例名 | service name | 数据文件位置 |
| xuan1（主库） | 192.168.11.111 | orcl | orcl | orcl | orcl | /u01/app/oracle/oradata/orcl/ |
| xuanDG2（备库） | 192.168.11.22 | orcl | dg_22 | orcl | orcl | /u01/app/oracle/oradata/orcl/ |
1.主库设置
1.1 开归档
sys@ORCL> shutdown immediate
sys@ORCL> startup mount
sys@ORCL> alter database archivelog;
sys@ORCL> alter database open;
sys@ORCL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
sys@ORCL> alter database force logging;
Database altered.
1.2 参数设置
sys@ORCL> alter system set log_archive_config='dg_config=(orcl,dg_22)';
sys@ORCL> alter system set log_archive_dest_2='service=dg_22 async valid_for=(online_logfile,primary_role) db_unique_name=dg_22';
sys@ORCL> alter system set fal_server=dg_22;
sys@ORCL> alter system set fal_client=orcl;
sys@ORCL> alter system set standby_file_management=auto;
sys@ORCL> create pfile='/home/oracle/pfile.ora' from spfile;
1.3 配置TNS
----看见了吗？主备库连接的tns设置，只有ip地址不一样，也就是说，备库切换为主库后，只需要把备库ip地址改为原主库ip地址，业务就能正常咯，不用在修改客户端的tns配置。
[oracle@xuan1 ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
dg_22 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
2.备库dg_22配置
----数据库软件安装好，数据库不用创建
2.1 环境变量
[oracle@xuanDG2 ~]$ vim .bash_profile
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_UNQNAME=dg_22
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=american_america.ZHS16GBK
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
export EDITOR=vi
export LANG=en_US.UTF-8
umask 022
[oracle@xuanDG2 ~]$ . .bash_profile
2.2 创建必要目录
mkdir -p $ORACLE_BASE/fast_recovery_area/orcl
mkdir -p $ORACLE_BASE/admin/orcl/adump
mkdir -p $ORACLE_BASE/admin/orcl/dpdump
mkdir -p $ORACLE_BASE/admin/orcl/pfile
mkdir -p $ORACLE_BASE/oradata/orcl
2.3 静态监听
[oracle@xuanDG2 ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orcl)
(SID_NAME=orcl)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)))
[oracle@xuanDG2 ~]$ lsnrctl start
2.4 配置TNS
[oracle@xuanDG2 ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
dg_22 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
2.5 参数文件设置
[oracle@xuan1 ~]$ scp pfile.ora 192.168.11.22:/home/oracle
----修改参数
idle>ho vim /home/oracle/pfile.ora
*.fal_client='DG_22'
*.fal_server='ORCL'
*.log_archive_dest_2='service=orcl async valid_for=(online_logfile,primary_role) db_unique_name=orcl'
----添加一个参数
*.service_names='orcl'
*.db_unique_name='dg_22'
----创建spfile，启动到nomount
idle>create spfile from pfile='/home/oracle/pfile.ora';
idle>startup nomount
2.6 密钥文件创建
[oracle@xuanDG2 ~]$ cd $ORACLE_HOME/dbs
[oracle@xuanDG2 dbs]$ orapwd file=orapworcl password=sys
3.duplicate到备库
3.1 登录测试
[oracle@dg2 ~]$ tnsping dg_22
[oracle@dg2 ~]$ tnsping orcl
[oracle@dg2 ~]$ sqlplus sys/sys@dg_22 as sysdba
[oracle@dg2 dbs]$ sqlplus sys/sys@orcl as sysdba
3.2 duplicate复制数据库到dg_22
[oracle@xuanDG2 dbs]$ rman target sys/sys@orcl auxiliary sys/sys@dg_22
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 18 00:29:26 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1445261955)
connected to auxiliary database: ORCL (not mounted)
RMAN>
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
--------如果复制数据库时，备库的路径和原库一致，就需要加nofilenamecheck，不然会报错
3.3 备库dg_22配置
----查看当前状态
idle>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl MOUNTED
----如果需要手动启动备用数据库：
---- startup nomount
---- alter database mount standby database;
----创建srl日志（比主库redo多一组，大小一样）
idle>alter database add standby logfile group 11 ('/u01/app/oracle/oradata/orcl/srl01.log') size 50m;
idle>alter database add standby logfile group 12 ('/u01/app/oracle/oradata/orcl/srl02.log') size 50m;
idle>alter database add standby logfile group 13 ('/u01/app/oracle/oradata/orcl/srl03.log') size 50m;
idle>alter database add standby logfile group 14 ('/u01/app/oracle/oradata/orcl/srl04.log') size 50m;
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/
o1_mf_3_crqds4hc_.log
2 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/
o1_mf_2_crqds2kx_.log
1 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/
o1_mf_1_crqds0pf_.log
11 STANDBY /u01/app/oracle/oradata/orcl/srl01.log
12 STANDBY /u01/app/oracle/oradata/orcl/srl02.log
13 STANDBY /u01/app/oracle/oradata/orcl/srl03.log
14 STANDBY /u01/app/oracle/oradata/orcl/srl04.log
7 rows selected.
----应用日志，开启redoapply
idle> alter database recover managed standby database using current logfile disconnect;
---------取消日志应用 recover managed standby database cancel;
3.5 主库添加srl日志
sys@ORCL> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/orcl/srl01.log') size 50m;
sys@ORCL> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/orcl/srl02.log') size 50m;
sys@ORCL> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/orcl/srl03.log') size 50m;
sys@ORCL> alter database add standby logfile group 14 ('/u01/app/oracle/oradata/orcl/srl04.log') size 50m;
4.查看主备应用日志情况
4.1主库切换日志
sys@ORCL>alter system switch logfile;
4.2备库出现新归档
----备库dg_22出现新的归档
idle> select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
7 YES
8 YES
9 IN-MEMORY
----备库dg_22传输模式
idle> select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
----主库上最大性能改为最大可用
sys@ORCL>alter database set standby database to maximize availability;
sys@ORCL> alter system set log_archive_dest_2='service=dg_22 sync affirm net_timeout=10 valid_for=(online_logfile,primary_role) db_unique_name=dg_22';
sys@ORCL>select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
4.3主库上查看备库应用情况（可以把name字段也加上）
sys@ORCL>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
6 NO
7 NO
8 NO
8 YES
9 YES
9 NO
10 YES
10 NO
11 YES
11 NO
12 YES
12 NO
13 YES
13 NO
14 NO
14 NO
16 rows selected.
----OK! 这样配置好后，可以自己做一下failover，然后把备库ip地址改为原主库的ip地址，然后客户端不改任何配置，直接就可以连接现在的新主库咯。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2122794/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2122794/
707
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
