1、概述
用户有一套Oracle数据库环境存在单节点运行隐患，虽然有全库备份，但是听用户说恢复数据极慢，且数据量有5T了。了解到该系统承载的业务比较重要，为消除隐患，给用户提了部署ADG环境的建议，用户也采纳了，申请了一台相同配置的服务器，目前已经部署完成，运行正常。
网上部署ADG环境的文档很多，但是Windows环境的却极少，且Windows环境与Linux环境部署DG还是有部分差异的，好记性不如烂笔头，下面是根据生产环境的操作记录下来的。
2、环境介绍
源端操作系统：Windows Server 2008 R2
源端数据库：Oracle 11.2.0.4.0
目标端操作系统：Windows Server 2008 R2
目标端数据库：Oracle 11.2.0.4.0
3、主库计算数据文件大小
select DF.TOTAL / 1048576 "DataFile Size Mb",
LOG.TOTAL / 1048576 "Redo Log Size Mb",
CONTROL.TOTAL / 1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL) / 1048576 "Total Size Mb"
from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz + 1) * cfbsz) TOTAL from x$kcccf c) CONTROL;
4、主库开启归档
archive log list;
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_dest_1='LOCATION=E:\arch' scope=spfile sid='*';
shutdown immediate;
startup mount
alter database archivelog;
alter database open;
select log_mode from v$database;
5、主库开启强制写日志
alter database force logging;
6、主库与备库配置网络
将tnsnames.ora文件拷贝给备库。
tnsorcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
tnsprod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.21)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = prod)
)
)
7、备库配置静态监听
# listener.ora Network Configuration File: E:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = WIN-T6052JOO0GG)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.21)(PORT = 1522))
)
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = prod)
(ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
)
)
ADR_BASE_LISTENER = E:\app\Administrator
8、启动静态监听
lsnrctl start listener1
9、主库修改参数
alter system set log_archive_config='DG_CONFIG=(orcl,prod)' scope=both;
alter system set log_archive_dest_1='LOCATION=E:\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;
alter system set log_archive_dest_2='SERVICE=tnsprod LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod' scope=both;
alter system set standby_file_management=auto scope=both sid='*';
10、备库创建实例
oradim -new -sid prod
set ORACLE_SID=pro
sqlplus / as sysdba
11、主备测试网络连通性
sqlplus sys/oracle@tnsorcl as sysdba
sqlplus sys/oracle@tnsprod as sysdba
12、主库创建口令文件并传输给备库
orapwd file=E:\app\Administrator\product\11.2.0\dbhome_1\database\pwdorcl.ora password=oracle entries=5
13、备库创建参数文件
db_name='orcl'
db_unique_name='prod'
memory_target=2G
compatible='11.2.0.1.0'
control_files='E:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL','E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL'
log_archive_config='DG_CONFIG=(orcl,prod)'
log_archive_dest_1='LOCATION=E:\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
log_archive_dest_2='SERVICE=tnsorcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
db_file_name_convert='E:\app\Administrator\oradata\orcl','E:\app\Administrator\oradata\orcl',
log_file_name_convert='E:\app\Administrator\oradata\orcl','E:\app\Administrator\oradata\orcl'
fal_client='tnsracdb'
fal_server='tnsprod'
standby_file_management='AUTO'
14、备库启动到NOMOUNT
SQL> create spfile from pfile;
SQL> startup nomount
15、主库duplicate开始复制
[oracle@rac2 ~]$ rman target sys/oracle@tnsorcl
RMAN> connect auxiliary sys/oracle@tnsprod
RMAN> duplicate target database for standby from active database nofilenamecheck;
16、创建standby logfile
alter database add standby logfile group 4 'E:\app\Administrator\oradata\orcl\standby01.log' size 50m;
alter database add standby logfile group 5 'E:\app\Administrator\oradata\orcl\standby02.log' size 50m;
alter database add standby logfile group 6 'E:\app\Administrator\oradata\orcl\standby03.log' size 50m;
alter database add standby logfile group 7 'E:\app\Administrator\oradata\orcl\standby04.log' size 50m;
Standby Redo Log多少组合适
在单实例情况下
所有redo log组数+1即可。
在RAC环境下
所有redo log组数+实例数
正常情况下，一般每个实例的redo log组数目是一样的，比如为n，则standbby redo log组数为(n+1)*thread
假如RAC有三个实例，每个实例都是3个Redo LOG组，那么如果要做DG的standby log要增加12个standby loggroup
(3+1)*3=12
假如有个rac共三个实例，实例1有3个log组，实例2有4个log组，实例3有5个log组，总共有12个log组，那么如果要做dg的standby log要增加15个standby loggroup
所有redo log组数+实例数=(3+4+5)+3=15
17、备库只读状态启动
alter database open read only;
18、备库启动实时日志应用
alter database recover managed standby database using current logfile disconnect from session;
