oracledatagurad(镜像)物理方式搭建步骤.doc

ORACLE DATAGURAD(镜像)物理方式搭建步骤演示第一步、演示环境说明:1第二步、主库配置:1第三步、备库配置:2第四步、日常维护:5第一步、演示环境说明:1、操作系统：windows server 2003以上;2、oracle数据库版本：11g;3、单机实例演示（多机创建时根据实际文件目录和IP进行调整即可）4、设置主库为：p_testSID为：ptest5、设置备库为：s_testSID为：stest6、创建主数据库：略；7、用户：sys8、登陆密码：saccerp9、“SQL”-指在SQLPLUS中的操作；10、“CMD”-指在windows命令行中操作；第二步、主库配置:一、设置强制写日志：1、 CMD Sqlplus sys/saccerpp_test as sysdba2、SQL Select force_logging from v$database;3、SQL Alter database force logging;4、SQL Select force_logging from v$database;二、修改主库的参数文件1、SQL create pfile from spfile; -创建参数文件副本2、默认在oracle的database目录下找到“INITptest.ora”进行编辑；添加下列文本配置：DB_UNIQUE_NAME=p_testLOG_ARCHIVE_CONFIG=DG_CONFIG=(p_test,s_test)LOG_ARCHIVE_DEST_1=LOCATION=C:oracle_11goradatap_test VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=p_testLOG_ARCHIVE_DEST_2=SERVICE=s_test LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=s_testLOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=DEFER#-配置standby 角色的参数用于角色转换FAL_SERVER=s_testFAL_CLIENT=p_testDB_FILE_NAME_CONVERT=oradatas_test,oradatap_testLOG_FILE_NAME_CONVERT=oradatas_test,oradatap_testSTANDBY_FILE_MANAGEMENT=AUTO3、更新主库参数文件：SQL shutdown immediate; -关闭数据库；SQL create spfile from pfile; -创建新的参数文件；SQL startup;三、将主库设置为归档模式SQL shutdown immediate;SQL startup mount;SQL alter database archivelog;SQL alter database open;四、创建RDO日志文件组standby redo log;1、在主库查看日志组的数量和每个日志文件的大小CMD Sqlplus sys/saccerpp_test as sysdbaSQL select group# , bytes from v$log;2、在主库创建日志组和redo log文件SQL Alter database add standby logfile group 4 (C:oracle_11goradatap_testrdoLog4.rdo) size 50M;SQL Alter database add standby logfile group 5 (C:oracle_11goradatap_testrodlog5.rdo) size 50M;SQL Alter database add standby logfile group 6 (C:oracle_11goradatap_testrodLog6.rdo) size 50M;第三步、备库配置:一、 创建备库服务:1、CMD oradim -new -sid stest -(配置监听时，记得配置为s_test)2、创建备库服务目录：复制adminp_test文件夹，拷贝到桌面，然后将桌面上的p_test重命名为s_test文件夹，完后将s_test拷贝到admin文件夹下即可；3、 创建数据文件目录：直接找到oradata文件夹，然后新建s_test文件夹即可；4、 创建备库登陆密码：CMD orapwd file=C:oracle_11gproduct11.1.0db_1databasePWDstest.ora password=saccerp entries=305、将database目录下的PWDptest.ora和PWDstest.ora两个文件拷贝到dbs目录下6、配置备库监听服务：（略，和主库一样配置就行）7、测试服务：(用tnsping 服务名也行)CMD Sqlplus sys/saccerps_test as sysdba如果上述操作登陆正常，即备库的结构创建成功，接着往下操作，反之则需要继续检查或咨询备库服务创建方法；二、 创建备库参数文件：1、 在database目录下新建“INITstest.ora”文件，然后新增下面文本配置保存至文件；更新内容如下：*.audit_file_dest=c:oracle_11gadmins_testadump*.audit_trail=db*.compatible=.0*.control_files=c:oracle_11goradatas_testcontrol01.ctl,c:oracle_11goradatas_testcontrol02.ctl,c:oracle_11goradatas_testcontrol03.ctl*.db_block_size=8192*.db_domain=*.db_name=p_test*.db_recovery_file_dest=c:oracle_11gflash_recovery_area*.db_recovery_file_dest_size=5242880000*.diagnostic_dest=c:oracle_11g*.dispatchers=(PROTOCOL=TCP) (SERVICE=stestXDB)*.memory_target=601882624*.open_cursors=300*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.undo_tablespace=UNDOTBS1db_unique_name=s_testLOG_ARCHIVE_CONFIG=DG_CONFIG=(p_test,s_test)DB_FILE_NAME_CONVERT=oradatap_test,oradatas_testLOG_FILE_NAME_CONVERT=oradatap_test,oradatas_testLOG_ARCHIVE_DEST_1=LOCATION=C:oracle_11goradatas_test VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=s_testLOG_ARCHIVE_DEST_STATE_1=ENABLE#-下列参数用于角色切换LOG_ARCHIVE_DEST_2=SERVICE=p_test LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=p_testLOG_ARCHIVE_DEST_STATE_2=ENABLEFAL_SERVER=p_testFAL_CLIENT=s_testSTANDBY_FILE_MANAGEMENT=AUTO三、 通过主库创建备库的控制文件1、生成备库的control01.ctl文件；CMD Sqlplus sys/saccerpp_test as sysdbaSQL alter database create standby controlfile as C:oracle_11goradatas_testcontrol01.ctl ;2、拷贝新生成的control01.ctl，生成两个副本，并分别更名为control02.ctl、control03.ctl，完后拷贝到“C:oracle_11goradatas_test”目录下（即拷贝到和control01.ctl相同目录下）；四、 将主库的数据、系统复制到备库目录下将C:oracle_11goradatap_test目录下的除TEMP01.DBF外的.DBF拷贝到C:oracle_11goradatas_test目录下；其余文件不拷贝；五、启动备库实例CMD Sqlplus sys/saccerps_test as sysdbaSQL create spfile from pfile; SQL startup nomount;SQL alter database mount standby database;六、创建RDO日志文件组,主、备都要配置standby redo log;1、在备库查看日志组的数量和每个日志文件的大小CMD Sqlplus sys/saccerps_test as sysdbaSQL select group# , bytes from v$log;2、删除原主库创建的RDO文件组SQL alter database drop standby logfile group 4;SQL alter database drop standby logfile group 5;SQL alter database drop standby logfile group 6;3、在备库创建日志组和redo log文件SQL Alter database add standby logfile group 4 (C:oracle_11goradatas_testrodLog4.rdo) size 50M;SQL Alter database add standby logfile group 5 (C:oracle_11goradatas_testrodlog5.rdo) size 50M;SQL Alter database add standby logfile group 6 (C:oracle_11goradatas_testrodLog6.rdo) size 50M;七、在主库上设置DataGuard的保护模式；CMD Sqlplus sys/saccerpp_test as sysdbaSQL alter system set log_archive_dest_state_2=ENABLE scope=both;SQL shutdown immediate;SQL startup mount;SQL alter database set standby database to maximize availability;SQL alter database open;八、在备库上启动日志传输CMD Sqlplus sys/saccerps_test as sysdbaSQL alter database recover managed standby database using current logfile disconnect from session;SQL select sequence#,applied from v$archived_log order by sequence#;九、检查镜像是否搭建完成1、检查主库日志传送是否正常CMD Sqlplus sys/saccerpp_test as sysdbaSQL Select dest_name,status,error from v$archive_dest;2、检查主、备库当前镜像状态CMD Sqlplus sys/saccerpp_test as sysdbaSQL select database_role,protection_mode,protection_level from v$database;CMD Sqlplus sys/saccerps_test as sysdbaSQL select database_role,protection_mode,protection_level from v$database;3、检查主、备库当前日志是否同步CMD Sqlplus sys/saccerpp_test as sysdbaSQL select sequence#,applied，first_time,next_time from v$archived_log order by sequence#;CMD Sqlplus sys/saccerps_test as sysdbaSQL select sequence#,applied，first_time,next_time from v$archived_log order by sequence#;4、在主库更新日志，用于检查备库是否能自动和主库同步；CMD Sqlplus sys/saccerpp_test as sysdbaSQL alter system archive log current;SQL select sequence#,applied，first_time,next_time from v$archived_log order by sequence#;CMD Sqlplus sys/saccerps_test as sysdbaSQL select sequence#,applied，first_time,next_time from v$archived_log order by sequence#;当前最后更新数值一致说明已同步；第四步、日常维护:一、主、备之间的切换-手动切换：1、主库切为备库CMD Sqlplus sys/saccerpp_test as sysdbaSQL Select switchover_status from v$database;SQL alter database commit to switchover to physical standby;SQL shutdown immediate;SQL startup mount;2、备库切为主库CMD Sqlplus sys/saccerps_test as sysdbaSQL select switchover_status from v$database;SQL alter database commit to switchover to primary;SQL alter database open;二、故障转移：CMD Sqlplus sys/saccerps_test as sysdbaSQL SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;SQL alter database recover managed standby database finish force;SQL alter database commit to switchover to primary;SQL alter database open;三、在日常维护中要注意：启动服务的时候要注意：先启动备库、然后启动主库；1、先从库监听、然后主库监听；2、先从库服务、然后主库服务；从库服务启动后CMD Sqlplus sy