windows环境DataGuard配置

精选文库1.DataGuard配置主机：192.168.0.7(primary)备机：192.168.0.8(standby)配置条件：分别在primary、standby上安装数据库，并自动创建实例；安装路径、数据库实例名（citizen）和密码都设置成一样特别注意：文件夹权限必须与administartor一致1.1.Primary主机操作1、设置主数据库为force logging 模式SQLsqlplus /as sysdbaSQLalter database force logging;2、设置主数据库为归档模式，并以mount启动数据库SQLarchive log listSQLshutdown immediateSQLstartup mountSQLalter database archivelog;SQLarchive log list3、添加备用联机日志文件SQLselect * from v$logfile;再添加：alter database add standby logfile group 4 (D:apporadataorclredo04.log) size 50m; alter database add standby logfile group 5 (D:apporadataorclredo05.log) size 50m; alter database add standby logfile group 6 (D:apporadataorclredo06.log) size 50m; alter database add standby logfile group 7 (D:apporadataorclredo07.log) size 50m;路径与原来的日志路径一样4、创建主库的初始化参数给备库用SQLCreate pfile from spfile;产生的文件名为initcitizen.ora 存放目录默认放在$ORACLE_HOME/database下5、在主库创建监听和配置tnsnams.oralistener.ora配置如下:SID_LIST_LISTENER中添加： 红色添加(SID_DESC = (GLOBAL_DBNAME = citizen) (ORACLE_HOME = D:oracleproduct10.2.0db_1) (SID_NAME = citizen)LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521) (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-RKCE8P1N4VV)(PORT = 1521) ) )此处HOST 最好使用主机名tnsnames.ora配置如下:添加：primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 19.128.249.51)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = citizen) ) )standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 19.128.249.52)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = citizen) ) )监听配置完后，重启监听后，需要时间久一点 primary 的服务才能启动起来。特别注意， 需要使用下面pfile 启动，primary的服务才正式启动6、在initcitizen.ora中添加以下内容：*.log_archive_format=%T%S%r.ARC *.DB_UNIQUE_NAME=primary *.log_archive_config=DG_CONFIG=(primary,standby) *.log_archive_dest_1=location=D:appflash_recovery_areaorclARCHIVELOG 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*.STANDBY_FILE_MANAGEMENT=AUTO*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.FAL_SERVER=standby*.FAL_CLIENT=primary关闭数据库shutdown immediate ，再用initcitizen.ora重启SQLstartup pfile=D:oracleproduct10.2.0db_1databaseinitcitizen.ora; 7、用Rman备份,不用停机$ rman target /RMANbackup full format D:/db/FULL_%d_%T_%s.bak database include current controlfile for standby;RMANsql alter system archive log current;RMANBackup ArchiveLog all format=D:/db/arch_%d_%T_%s.bak;备份完后将备份文件拷到standby上同样的目录，强调：同样的目录（D盘），在standby进行rman 恢复即可8、启动主数据库SQLstartup 1.2.Standby备机操作1、以mount启动备库，添加备用联机日志文件SQLsqlplus /as sysdbaSQLshutdown immediateSQLstartup mount先查看日志文件位置：SQLselect * from v$logfile;再添加：alter database add standby logfile group 4 ( D:apporadataorclcitizenredo04.log) size 50m;alter database add standby logfile group 5 (D:ORACLEPRODUCT10.2.0ORADATAcitizenredo05.log) size 50m;alter database add standby logfile group 6 (D:ORACLEPRODUCT10.2.0ORADATAcitizenredo06.log) size 50m;alter database add standby logfile group 7 (D:ORACLEPRODUCT10.2.0ORADATAcitizenredo07.log) size 50m;2、在备库创建监听和配置tnsnams.ora（同主库）3、测试主备之间网络连通 测试非常重要Primary:C:lsnrctl startC:tnsping standbyStandby:C:lsnrctl startC:tnsping primary再主库中测试 Tnsping primary / tnsping 19.130.218.32/primaryTnsping standby / tnsping 19.130.218.30/standby 备库 Tnsping standby / tnsping 19.130.218.30/standbyTnsping primary / tnsping 19.130.218.32/primary4、复制主库的参数文件到备库中，编辑$ORACLE_HOME/database目录下的initcitizen.ora添加以下内容*.log_archive_format=%T%S%r.ARC*.DB_UNIQUE_NAME=standby*.log_archive_config=DG_CONFIG=(primary,standby)*.log_archive_dest_1=location=D:oracleproduct10.2.0oradataarch 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*.STANDBY_FILE_MANAGEMENT=AUTO*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.FAL_SERVER=primary*.FAL_CLIENT=standby以及密码文件 D:appproduct11.2.0dbhome_1databasePWDorcl.ora 备份到相同路径5、启动备用数据库 开始把主库复制到备库中SQLsqlplus /as sysdbaSQLstartup nomount pfile=D:oracleproduct10.2.0db_1databaseinitcitizen.ora; 重启主、备库监听，用Rman还原数据库$rman targetsys/adminprimaryauxiliary /RMANduplicate target database for standby dorecoverse nofilenamecheck;duplicate target database for standby nofilenamecheck;SQLalter database mount standby database;-该语句如果报错可忽略SQLalter database recover managed standby database disconnect from session;这里插入一下： 如果服务器或者数据库需要重启， 在重启之后 主库： SQLsqlplus /as sysdbaSQL startup pfile=D:appproduct11.2.0dbhome_1databaseINITorcl.ORA; Startup 后面没有添加任何参数就直接打开到 open状态备库： SQLsqlplus /as sysdbaSQLstartup nomount pfile=D:appproduct11.2.0dbhome_1databaseINITorcl.ORA; SQLalterdatabasemountstandbydatabase;SQLalter database recover managed standby database cancel;-该语句如果报错，可忽略SQLalterdatabaserecovermanagedstandbydatabasedisconnectfromsession;备库手动应用归档日志ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEUSINGCURRENTLOGFILEDISCONNECTFROMSESSION; 备库只能开启到mont (挂载)的状态， 如果启动到open状态， 备库无法应用归档日志。 如果想查看同步后的表： SQLalter database recover managed standby database cancel;-SQLalter database open read only 查看完之后，需要切换回到session模式 才能继续同步并且应用归档日志SQLalterdatabaserecovermanagedstandbydatabasedisconnectfromsession;查看归档日志情况 SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG; 查看归档日志的同步情况D:appdiagrdbmsstandbyorcltrace 查看oracle 的操作日志1.3.测试注意DataGuard启动顺序：l启动顺序：先standby,后primary;l关闭顺序：先primary后standby;1、在备库将实例启动到mount状态：SQLstartupnomount;SQLalterdatabasemountstandbydatabase;SQLalter database recover managed standby database cancel;-该语句如果报错，可忽略SQLalterdatabaserecovermanagedstandbydatabasedisconnectfromsession;#如果有需要应用的日志并想手工应用，可以运行如下命令 SQLrecoverautomaticstandbydatabase;2、在主库启动实例：SQLstartup;3、在主库验证日志：SQLaltersystemswitchlogfile; 主库主动同步SQLselectmax(sequence#)fromv$archived_log;4、在备库检查日志是否和主库一致SQLselectmax(sequence#)fromv$archived_log;注：主备查询结果一致，DataGuard搭建结束。1.4.备库standby和read only切换1、主库创建一个表,用于备库打开时验证数据SQLcreate table test02 as select * from v$parameter;SQLcommit;SQLalter system switch logfile;2、将备库启动到read only状态1)如果standby db处于mount状态,则可以通过下面的命令启动到read-only状态SQLselect open_mode,database_role from v$database;OPEN_MODEDATABASE_ROLE-oraclestandbydb $ sqlplus / as sysdba- -MOUNTEDPHYSICAL STANDBYSQLalter database recover managed standby database cancel;SQLalter database open;2)如果standby db数据库处于关闭状态,则执行下面的命令启动到read-only状态SQLstartup3、切换回standby状态.断开所有链接，执行以下语句SQLalter database recover managed standby database disconnect from session;SQLselect open_mode,database_role from v$database;OPEN_MODEDATABASE_ROLE- -MOUNTEDPHYSICAL STANDBY注：在standby db打开的过程中，主库的日志文件还是传递过来，只不过不做日志的应用。所以standby db打开的时间越长，以后做日志应用的时间就越长，如果主库故障，则启用备库到正常状态的时间就越长，这是需要您来权衡的。注：如果切换后发现日志不能同步，重新切换一下即可1.5.主/备库切换一般SWITCHOVER切换都是计划中的切换，特点是在切换后，不会丢失任何的数据，而且这个过程是可逆的，整个DATA GUARD环境不会被破坏，原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。 在进行DATA GUARD的物理STANDBY切换前需要注意：l确认主库和从库间网络连接通畅；l确认没有活动的会话连接在数据库中；lPRIMARY数据库处于打开的状态，STANDBY数据库处于MOUNT状态；l确保STANDBY数据库处于ARCHIVELOG模式；l如果设置了REDO应用的延迟，那么将这个设置去掉；l确保配置了主库和从库的初始化参数，使得切换完成后，DATA GUARD机制可以顺利的运行。1、主库切换成备库1)查看switchover 状态SQLSELECT SWITCHOVER_STATUS FROM V$DATABASE;如果返回to standbySQLALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;否则SQLAlter database commit to switchover to physical standby with session shutdown;2)启动到mount和应用日志状态SQLshutdown immediateSQLstartup nomountSQLalter database mount standby database;SQLalter database recover managed standby database disconnect from session;查看数据库模式SQLselect dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;SQLselect status,database_mode from v$archive_dest_status;2、备库切换成主库1)查看switchover状态SQLselect switchover_status from v$database;如果返回TO PRIMARYSQLalter database commit to switchover to primary;否则SQLalter database commit to switchover to primary with session shutdown启动数据库SQLshutdown immediateSQLstartupSQLalter system switch logfile;SQLselectmax(sequence#)fromv$archived_log;2)查看数据库模式SQLselect dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;SQLselect status,database_mode from v$archive_dest_status;注：如果做了switchover,主库参数设置成以下方式，会触发ora-16009错误，按以下操作即可：Alert system set log_archive_dest_2=service=primaryDB_UNIQUE_NAME=citizen scope=spfile;1.6.切换保护模式1、在primary上操作1)首先查看当前的保护模式SQLselectprotection_mode,protection_levelfromv$database;2)修改初始化参数SQLalter system set log_archive_dest_2=SERVICE=standby OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby;3)重启数据库并设置新的数据保护模式SQLshutdown immediateSQLstartup mountSQLalter database set standby database to maximize availability;SQLalter database open;SQLselect protection_mode,pro