windows下oracle 11g搭建主备

Oracle Data Guard 主备搭建主库: 192.168.100.73 SIDorcl备库: 192.168.100.74 SIDorcldg一、主库配置 (在73服务器执行)-- 1.1 开启归档模式alter system set db_recovery_file_destC:\app\Administrator\flash_recovery_area scopeboth;alter system set db_recovery_file_dest_size20G scopeboth;alter system set log_archive_dest_1locationuse_db_recovery_file_dest valid_for(all_logfiles,all_roles) scopeboth;alter system set log_archive_formatorcl_%t_%s_%r.arc scopespfile;alter system set log_archive_configdg_config(orcl,orcldg) scopeboth;shutdown immediate;startup mount;alter database archivelog;alter database open;archive log list;exit;-- 1.2 开启强制日志alter database force logging;exit;-- 1.3 配置主库参数alter system set fal_serverorcldg scopeboth;alter system set fal_clientorcl scopeboth;alter system set standby_file_managementAUTO scopeboth;alter system set dg_broker_config_file1C:\app\Administrator\oradata\orcl\dr1_orcl.dat scopeboth;alter system set dg_broker_config_file2C:\app\Administrator\oradata\orcl\dr2_orcl.dat scopeboth;alter system set dg_broker_startTRUE scopeboth;exit;-- 1.4 创建 standby redo log-- 建议数量 主库redo log组数 1 (一般主库有3组这里创建4组)-- 大小应与主库原有redo log一致先查询: select group#, bytes from v$log;alter database add standby logfile group 4 C:\app\Administrator\oradata\orcl\standby_redo04.log size 50M;alter database add standby logfile group 5 C:\app\Administrator\oradata\orcl\standby_redo05.log size 50M;alter database add standby logfile group 6 C:\app\Administrator\oradata\orcl\standby_redo06.log size 50M;alter database add standby logfile group 7 C:\app\Administrator\oradata\orcl\standby_redo07.log size 50M;exit;二、备库准备 (在74服务器执行)-- 2.1 创建目录md C:\app\Administrator\oradata\orcldgmd C:\app\Administrator\admin\orcldg\adumpmd C:\app\Administrator\admin\orcldg\bdumpmd C:\app\Administrator\admin\orcldg\cdumpmd C:\app\Administrator\admin\orcldg\udumpmd C:\app\Administrator\admin\orcldg\pfile-- 2.2 复制密码文件 (11g需要手动复制)copy \\192.168.100.73\c$\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ora C:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcldg.ora-- 2.3 创建备库初始化参数文件 (保存为 initorcldg.ora)-- 路径: C:\app\Administrator\product\11.2.0\dbhome_1\database\initorcldg.ora*.db_nameorcl*.db_unique_nameorcldg*.instance_nameorcldg*.service_namesorcldg*.memory_target2G*.control_filesC:\app\Administrator\oradata\orcldg\control01.ctl*.db_create_file_destC:\app\Administrator\oradata\orcldg*.db_recovery_file_destC:\app\Administrator\flash_recovery_area*.db_recovery_file_dest_size20G*.log_archive_dest_1locationuse_db_recovery_file_dest valid_for(all_logfiles,all_roles)*.log_archive_formatorcl_%t_%s_%r.arc*.log_archive_configdg_config(orcl,orcldg)*.standby_file_managementAUTO*.fal_serverorcl*.fal_clientorcldg*.remote_login_passwordfileEXCLUSIVE*.dg_broker_config_file1C:\app\Administrator\oradata\orcldg\dr1_orcldg.dat*.dg_broker_config_file2C:\app\Administrator\oradata\orcldg\dr2_orcldg.dat*.dg_broker_startTRUE三、配置TNS和监听 (主备库都需要)-- 3.1 tnsnames.ora (主库73配置)orcl (description (address (protocol tcp)(host 192.168.100.73)(port 1521))(connect_data (service_name orcl)(server dedicated)))orcldg (description (address (protocol tcp)(host 192.168.100.74)(port 1521))(connect_data (service_name orcldg)(server dedicated)))-- 3.1 tnsnames.ora (备库74配置)orcl (description (address (protocol tcp)(host 192.168.100.73)(port 1521))(connect_data (service_name orcl)(server dedicated)))orcldg (description (address (protocol tcp)(host 192.168.100.74)(port 1521))(connect_data (service_name orcldg)(server dedicated)))(server dedicated)))-- 3.2 listener.ora (主库 73)SID_LIST_LISTENER (SID_LIST (SID_DESC (GLOBAL_DBNAME orcl)(ORACLE_HOME C:\app\Administrator\product\11.2.0\dbhome_1)(SID_NAME orcl))(SID_DESC (GLOBAL_DBNAME orcl_DGMGRL)(ORACLE_HOME C:\app\Administrator\product\11.2.0\dbhome_1)(SID_NAME orcl)))-- 3.2 listener.ora (备库 74)SID_LIST_LISTENER (SID_LIST (SID_DESC (GLOBAL_DBNAME orcldg)(ORACLE_HOME C:\app\Administrator\product\11.2.0\dbhome_1)(SID_NAME orcldg))(SID_DESC (GLOBAL_DBNAME orcldg_DGMGRL)(ORACLE_HOME C:\app\Administrator\product\11.2.0\dbhome_1)(SID_NAME orcldg)))-- 配置完成后重启监听lsnrctl stoplsnrctl start四、使用RMAN创建备库 (在74服务器执行)-- 4.1 设置环境变量 (每开一个CMD都要执行)set ORACLE_HOMEC:\app\Administrator\product\11.2.0\dbhome_1set ORACLE_SIDorcldgset PATH%ORACLE_HOME%\bin;%PATH%-- 4.2 创建Oracle服务 (管理员CMD执行只需执行一次)oradim -NEW -SID orcldg-- 4.3 启动监听lsnrctl start-- 4.4 启动备库到nomount状态sqlplus / as sysdbastartup nomount pfileC:\app\Administrator\product\11.2.0\dbhome_1\database\initorcldg.ora;exit;-- 4.5 使用RMAN duplicaterman target sys/123456orcl auxiliary sys/123456orcldgRMAN duplicate target database for standby from active database nofilenamecheck;-- 命令执行完成后备库已创建完成五、启动Data Guard (在备库执行)set ORACLE_SIDorcldgsqlplus / as sysdba-- 启动MRP进程alter database recover managed managed standby database disconnect from session;-- 打开备库(可选用于只读查询)alter database open;-- 创建spfile以便后续修改参数create spfile from pfile;shutdown immediate;startup;exit;六、配置Data Guard Broker (主备库都执行)-- 6.1 检查主库参数alter system set dg_broker_startTRUE scopeboth;-- 6.2 检查备库参数 (备库执行)alter system set dg_broker_startTRUE scopeboth;-- 6.3 部署Broker (在主库执行)dgmgrl /-- 创建配置create configuration dg as primary database is orcl connect identifier is orcl;-- 添加备库add database orcldg as connect identifier is orcldg;-- 启用配置enable configuration;-- 6.4 检查状态show configuration;show database verbose orcldg;七、验证 (主备库分别执行)-- 主库select status, open_mode from v$database;select database_role from v$database;-- 备库select status, open_mode from v$database;select database_role, protection_mode from v$database;select process, status from v$managed_standby;