因为需要一份DG的搭建文档，Linux端的详细文档很多，但是网上的Windows端的文档却总是写的不是特别详细，所以参考多位博主，实验多次总结一份搭建成功的配置文档。
DG搭建说明：本文档为Oracle数据库在windows环境下的DG搭建。
一、物理规划
数据库要求：
Oracle database 11gR2(数据库版本相同)
备库数据库仅安装软件，不建库
安装路径相同，实例名相同（便于安装维护管理）
具体安装路径为E:\app\Administrato
作系统要求：
Windows server 2008 R2（操作系统版本相同）
说明：服务器环境为VM搭建的两台虚拟机，操作系统为Windows Server 2008 R2，数据库为Oracle11gR2，安装目录实例名全部相同，archivelog下的arch文件夹为手动创建。
二、配置过程
1.主库配置- 主库启动到mount状态下开启归档和force logging
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database force logging;- 查看当前日志组，并为备库创建日志文件
SQL> select * from v$logfile order by group#;
SQL> alter database add standby logfile group 4 ('E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04.LOG') size 50m;
SQL> alter database add standby logfile group 5 ('E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO05.LOG') size 50m;
SQL> alter database add standby logfile group 6 ('E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO06.LOG') size 50m;- 创建备库standby的控制文件
SQL> alter database create standby controlfile as ‘E:\APP\ADMINISTRATOR\flash_recovery_area\orcl\arch\standby.ctl’;
- 创建参数文件
SQL> create pfile from spfile;
默认参数文件位置为：
E:\app\Administrator\product\11.2.0\dbhome_1\database
- 编辑参数文件initorcl.ora
orcl.__db_cache_size=343932928
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='E:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=343932928
orcl.__sga_target=515899392
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=155189248
orcl.__streams_pool_size=0
*.audit_file_dest='E:\app\Administrator\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='E:\app\Administrator\oradata\orcl\control01.ctl','E:\app\Administrator\flash_recovery_area\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='E:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='E:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=858783744
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#以下为新加部分
*.db_unique_name='primary'
*.archive_lag_target=1800
*.fal_client='standby'
*.fal_server='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='LOCATION=E:\app\Administrator\flash_recovery_area\orcl\arch VALID_FOR=(all_logfiles,all_roles) db_unique_name=primary'
*.log_archive_dest_2='service=standby arch async valid_for=(online_logfiles,primary_role) db_unique_name=standby'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.DB_FILE_NAME_CONVERT='E:\app\Administrator\oradata\orcl','E:\app\Administrator\oradata\orcl'
*.LOG_FILE_NAME_CONVERT='E:\app\Administrator\flash_recovery_area\orcl\arch','E:\app\Administrator\flash_recovery_area\orcl\arch'
*.standby_file_management='auto'
- 使用修改过后的参数文件启动数据库
SQL> startup pfile=’ E:\app\Administrator\product\11.2.0\dbhome_1\database\initorcl.ora’;
SQL> create spfile from pfile=’ E:\app\Administrator\product\11.2.0\dbhome_1\database\initorcl.ora’;
SQL> shutdown immediate;
SQL> startup;
- 创建密码文件
密码文件存放于
E:\app\Administrator\product\11.2.0\dbhome_1\database目录,文件名为PWDorcl.ora(orcl为实例名)
若以下目录找不到，则手动创建，命令为：
SQL> orapwd file=E:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ora password=123 entries=10- 配置网络监听
文件位置为E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora修改listener.ora文件：
# 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")
)
#原有基础添加一个SID_DESC
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
#------------------------------------------------------------------------
)
#原有基础添加一个LISTENER
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.128)(PORT = 1521))
)
)
ADR_BASE_LISTENER = E:\app\Administrator
##修改tnsname.ora文件
#tnsnames.ora Network Configuration File:E:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
#Generated by Oracle configuration tools.
#添加一个PRIMARY
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.3.128) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
#添加一个STANDBY
STANDBY=
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.3.131) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORACLR_CONNECTION_DATA=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.128)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
- 测试监听
SQL> alter database open;
C:\Users\Administrator> lsnrctl stop
C:\Users\Administrator> lsnrctl start
C:\Users\Administrator> sqlplus username/pwd@primary as sysdba
- 关闭主库进行数据库冷拷贝
SQL> shutdown immediate
1. 将E:\app\Administrator目录下的admin,cfgtollogs,diag,flash_recover_area目录以及密码文件拷贝到备用库的相同路径（已存在的覆盖）
2. 将主库的listener.ora和tnsname.ora拷贝到备库相同路径
3. 将standby.ctl文件拷贝到备库E:\app\Administrator\oradata\orcl\和E:\app\Administrator\flash_recovery_area\orcl\修改为control01.ctl和control02.ctl
4. 主库备库在E:\app\Administrator\flash_recovery_area\orcl\下创建文件夹arch2.备库配置
- 修改拷贝到备库的监听文件listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.131)(PORT = 1521))
)
)- 为备库创建实例
C:\Users\Administrator> oradim –new –sid orcl
注：如若备库中已经创建数据库，实例名为orcl，此过程可以跳过（会报错实例已存在）- 启动备库监听
C:\Users\Administrator> lsnrctl start
- 修改拷贝到备库的参数文件initorcl.ora
orcl.__db_cache_size=343932928
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='E:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=343932928
orcl.__sga_target=515899392
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=155189248
orcl.__streams_pool_size=0
*.audit_file_dest='E:\app\Administrator\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='E:\app\Administrator\oradata\orcl\control01.ctl','E:\app\Administrator\flash_recovery_area\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='E:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='E:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=858783744
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#以下为新加部分
*.db_unique_name='standby'
*.archive_lag_target=1800
*.fal_client='primary'
*.fal_server='standby '
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='LOCATION=E:\app\Administrator\flash_recovery_area\orcl\arch VALID_FOR=(all_logfiles,all_roles) db_unique_name= standby '
*.log_archive_dest_2='service= primary arch async valid_for=(online_logfiles,primary_role) db_unique_name= primary '
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.DB_FILE_NAME_CONVERT='E:\app\Administrator\oradata\orcl','E:\app\Administrator\oradata\orcl'
*.LOG_FILE_NAME_CONVERT='E:\app\Administrator\flash_recovery_area\orcl\arch','E:\app\Administrator\flash_recovery_area\orcl\arch'
*.standby_file_management='auto'
- 使用修改的参数文件建立备库实例
SQL> startup nomount pfile=’E:\app\Administrator\product\11.2.0\dbhome_1\database\initorcl.ora’;
SQL> create spfile from pfile=’E:\app\Administrator\product\11.2.0\dbhome_1\database\initorcl.ora’;
SQL> shutdown immediate;
SQL> startup nomount;3.使用RMAN复制数据库
- 对主库使用RMAN备份
C:\Users\Administrator> RMAN target /
RMAN> backup full database format=’E:\app\Administrator\flash_recovery_area\orcl\arch\FOR_STANDBY_%u_%p_%s.RMN’ include current controlfile for standby;
RMAN> sql ‘alter system archive log current’;
将产生的备份拷贝到备库相同的路径下RMAN备份集拷贝到备库相同的路径后，继续在主库的RMAN中执行以下命令
RMAN > connect auxiliary sys/oracle@standby;
RMAN > duplicate target database for standby nofilenamecheck;
- 启动备库standby
C:\Users\Administrator>sqlplus / as sysdba
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
三、验证DG搭建是否成功
备库：
SQL> select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- --------------------
PROD READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
主库：
SQL> select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- --------------------
PROD READ WRITE PRIMARY MAXIMUM PERFORMANCE TO STANDBY
主库和备库分别执行：
SQL> select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- --------
10 YES
11 YES
12 YES
13 YES
14 YES
15 YES
16 YES
17 YES
18 YES
19 YES
如果查看从库可以看到归档日志,表示归档日志同步成功,如果APPLIED列的值为yes,表示重做应用成功。
然后可以手动切换日志
SQL> alter system switch logfile;
实验：在主库scott用户创建表，提交手动切换日志，查看备库是否创建新表。四、开库过程中遇到的问题
SQL > alter database open read only;
……
报错ORA-10456:cannot open standby database; media recovery session may be in progress- 这个的原因如下 ： 恢复模式不可打开数据库 所以先要cancel日志应用，在开启日志应用
1) alter database recover managed standby database cancel;
2) alter database open read only;
3) alter database recover managed standby database using current logfile disconnect from session;- 测试情况如下：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 38
Next log sequence to archive 0
Current log sequence 39
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
------------------ ----------------------------------------
orcl MOUNTED
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
------------------ ----------------------------------------
RAC READ ONLY WITH APPLY
数据库开关顺序：
开库顺序：先备库、后主库（备库就像一个仆人，没事儿先候着）
关库顺序：先主库、后备库（主库就像一个主人，有事儿先撤了）
搭建完成
