w indows2008r2配置oracle 11g DataGuard数据同步.doc

windows2008 r2配置oracle 11g r2 DataGuard数据同步一、首要条件：1两台相同的window 2008 server,都安装好oracle11g r2。2选其中一台作为主库（primary），另一台作为备库（standby），主库配置备库配置备注：主库备库中的tnsnames.ora配置范例如下：standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.124.133)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = standby) ) )primary = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.124.132)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = primary) ) )3确保在主、备库可以通过CMD命令sqlplus scott/adminprimary ,sqlplus scott/adminstandby相互程登录 二、主库操作1设置主库归档模式，查看是否为归档：SQL archive log list;备注：在安装oracle的时候，可以直接选择为存档模式。如果自动存档未开启，执行SQL alter database archivelog;然后设置强制归档SQL alter database force logging;查看SQL select force_logging from v$database;主库归档配置完成。2. 添加主库standby联机日志(路径不存在需要手动建立)SQL alter database add standby logfile group 4C:appAdministratororadataORCLONLINELOGredo04.log size 50m;SQL alter database add standby logfile group 5C:appAdministratororadataORCLONLINELOGredo05.log size 50m;SQL alter database add standby logfile group 6C:appAdministratororadataORCLONLINELOGredo06.log size 50m;3. 编辑主库参数文件创建主库pfileSQL create pfile=c:primary.ora from spfile;打开创建好的primary.ora 修改或添加以下参数*.db_name=orcl*.log_archive_format=ARC_%T%S%r.ARC*.DB_UNIQUE_NAME=primary*.log_archive_config=DG_CONFIG=(primary,standby)*.log_archive_dest_1=location=E:appadminoradataarchive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary*.log_archive_dest_2=SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.FAL_SERVER=standby*.FAL_CLIENT=primary*.STANDBY_FILE_MANAGEMENT=AUTO（注意：有些参数与primary.ora现存的参数重复，建议相同的只保留一条，标红为特别注意需按实际情况修改的）4. 使用新编辑的primary.ora启动数据库SQL shutdown immediate;SQL startup pfile=c:primary.ora mount;SQL create spfile from pfile=c:primary.ora; - 写入spfile 再关闭重启实例这个错误可以不管，也可以在关闭之前alter database open，然后关闭SQL startup重建一次pfileSQL create pfile=c:std.ora from spfile;修改创建好的std.ora *.db_name=orcl*.log_archive_format=ARC_%T%S%r.ARC*.DB_UNIQUE_NAME=standby*.log_archive_config=DG_CONFIG=(primary,standby)*.log_archive_dest_1=location=E:appadminoradataarchive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby*.log_archive_dest_2=SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.FAL_SERVER=primary*.FAL_CLIENT=standby*.STANDBY_FILE_MANAGEMENT=AUTO （注意：有些参数与std.ora现存的参数重复，建议相同的只保留一条，标红为特别注意需按实际情况修改的）5. 创建standby controlfile SQL alter database create standby controlfile as c:std.ctl; 改名并复制2份，control01.ctl control02.ctl SQL shutdown immediate;关闭主库数据库。三、备库操作1.在主备库都关闭的状态下，拷贝主库standby控制文件（即改名后的control01.ctl control02.ctl）、std.ora参数文件、密码文件（ORACLE_HOMEdatabasePWDsid.ora）、所有数据文件、联机日志文件（C:appAdministratororadata下所有文件）到备库相对应目录。2. 使用std.ora启动备库SQL startup pfile=c:std.ora nomount;SQL create spfile from pfile=c:std.ora;SQL shutdown immediate;（会报错，不用管）SQL startup nomount;SQL alter database mount standby database;查看日志路径：SQL select * from v$log;设置应用归档SQL alter database recover managed standby database disconnect from session;（取消应用归档SQL alter database recover managed standby database cancel;）备库配置完成。主库备库执行SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;主库显示备库显示3.测试Active DataGuard备库执行，取消应用归档SQLalter database recover managed standby database cancel;打开数据库Read only模式SQL shutdown immediate;SQL startup nomount;SQLalter database mount standby database;SQL alter database open read only;修改备库模式为收到日志后完成恢复SQLalter database recover managed standby database using current logfile disconnect;SQLselect open_mode from v$database;查看数据库打开状态在主库验证归档目录是否有效：SQL SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;状态为valid4主库上查看角色切换是否能够满足SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;(当主库的SWITCHOVER_STATUS状态为FAILED DESTINATION时，是因为备库不在mount状态下，在备库中：startup mount；当主库的SWITCHOVER_STATUS状态为RESOLVABLE GAP时，可以shutdown和startup备库，问题可解决。)注意：备库在主库做切换之前SWITCHOVER_STATUS表现为not_allowed5完成此时可在主库建立表空间、表、插入数据等，在备库查询。四、主库、备库切换1.原主库执行物理备库切换SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;SQL SHUTDOWN ABORT;SQL STARTUP MOUNT;2.查看原主库状态SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;3.取消原备库应用归档SQL alter database recover managed standby database cancel;设置交换为主库SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;SQL alter database open;SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;注意：当主库的SWITCHOVER_STATUS状态为RESOLVABLE GAP时，可以shutdown和startup备库4.设置原主库模式为收到日志后自动完成备份恢复（现备库模式）SQL alter database open read only;修改备库模式为收到日志后完成恢复SQLalter database recover managed standby database using current logfile disconnect;查看数据库打开状态SQLselect open_mode from v$database;主库备库切换成功。五、常用命令select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database; -查看当前的保护模式select dest_name,archiver from v$archive_dest; -查看日志的传送方式select open_mode from v$database; -查看数据库打开状态SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST; -查看归档目录SELECT SWITCHOVER_STATUS FROM V$DATABASE; -查看切换状态1，查看当前的保护模式select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;2，查看日志的传送方式；select dest_name,archiver from v$archive_dest;3，停止standby的自动恢复状态alter database recover manager standby database finish;4，添加standby logfilealter database add standby logfile group 4(/oracle/product/10.2.0/db_1/oradata/sjh10g/redo04.log) size 50m;5，更改保护模式alter database set standby database to maximize protextion;alter database set standby database to maximize availability;alter database set standby database to maximize performancen;6，更改传输方式alter system setlog_archive_dest_2=SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UN