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;
