ORACLE10G通过RMAN创建DATAGUARD步骤.doc

一、安装环境Windows 2008 SP2 64位企业版Oracle 64位企业版主库：HOSTNAME: YM-MES-SRVIP: SID：MESDB_UNIQUE_NAME：MESPRI备库：HOSTNAME: YM-MES-SRV02IP: SID：MESDB_UNIQUE_NAME：MESSTB主机SQL ALTER DATABASE FORCE LOGGING;SQL create pfile from spfile;修改initmes.ora，添加如下信息*.DB_UNIQUE_NAME=mespri*.LOG_ARCHIVE_CONFIG=DG_CONFIG=(mespri,messtb) *.log_archive_dest_1=location=d:oraclearchive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mespri*.log_archive_dest_2=SERVICE=messtb arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=messtb*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.FAL_SERVER=mespri*.FAL_CLIENT=messtb*.STANDBY_FILE_MANAGEMENT=AUTO SQL shutdown immediate删除spfilemes.oraSQL create spfile from pfile;SQL startupSQL ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (D:MESDATABASEMESstandbyREDO04.LOG) SIZE 50M;SQL ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (D:MESDATABASEMESstandbyREDO05.LOG) SIZE 50M;SQL ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 (D:MESDATABASEMESstandbyREDO06.LOG) SIZE 50M;SQL ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 (D:MESDATABASEMESstandbyREDO07.LOG) SIZE 50M; 修改tnsnames.ora,添加如下信息mespri = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SID = MES) ) ) messtb = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SID = MES) ) ) $ rman target / nocatalogRMAN backup device type disk format d:mesrmanback%U database plus archivelog;RMAN backup device type disk format d:mesrmanback%U current controlfile for standby;copy文件到从机同名目录下 从机从主机获得init文件和pwd文件mes._db_cache_size=1124073472mes._java_pool_size=16777216mes._large_pool_size=16777216mes._shared_pool_size=436207616mes._streams_pool_size=0*.audit_file_dest=D:oracleproduct10.2.0/admin/MES/adump*.background_dump_dest=D:oracleproduct10.2.0/admin/MES/bdump*.compatible=.0*.control_files=D:MESDATABASEMEScontrol01.ctl,D:MESDATABASEMEScontrol02.ctl,D:MESDATABASEMEScontrol03.ctl*.core_dump_dest=D:oracleproduct10.2.0/admin/MES/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=MES*.db_recovery_file_dest=D:oracleproduct10.2.0/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.dispatchers=(PROTOCOL=TCP) (SERVICE=MESXDB)*.job_queue_processes=10*.open_cursors=300*.pga_aggregate_target=847249408*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=1610612736*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=D:oracleproduct10.2.0/admin/MES/udump*.DB_UNIQUE_NAME=messtb*.LOG_ARCHIVE_CONFIG=DG_CONFIG=(mespri,messtb) *.log_archive_dest_1=location=d:oraclearchive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=messtb*.log_archive_dest_2=SERVICE=mespri arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mespri*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.FAL_SERVER=mespri*.FAL_CLIENT=messtb*.STANDBY_FILE_MANAGEMENT=AUTO 修改tnsnames.ora,添加如下信息mespri = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SID = MES) ) ) messtb = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SID = MES) ) ) 启动ORACLE服务（创建实例）D:/oradim -NEW -SID MESSQL startup nomount; 数据库没有Mount连接数据库,发现:ORA-12528: TNS:listener: all appropriate instances are blocking new connections修改listener.ora的参数,把动态的参数设置为静态的参数,然后从新启动监听SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:oracleproduct10.2.0db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = MES) (ORACLE_HOME = D:oracleproduct10.2.0db_1) (SID_NAME = MES) ) ) 主机上 $ rmanRMAN connect target / RMAN connect auxiliary sys/aa1234messtb RMAN duplicate target database for standby nofilenamecheck ;（不同机器上路径一致的话必须指定NOFILENAMECHECK） 从机上SQL shutdown immediateSQL startup mountSQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;改为最大可用性方式主机上SQL alter system set log_archive_dest_2=SERVICE=messtb LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=messtb scope=both;SQL alter database set standby database to maximize availability; SQL alter system set archive_lag_target=1800 scope=both; 从机上*.log_archive_dest_2=SERVICE=mespri LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mespri 在window服务启动时并不启动实例通过Oracle Administration Assistant for Windows NT工具进行设置 a.开始-程序-Oracle-Configuration and Migration Tools-Oracle Administration Assistant for Windows NT b.数据库-orcl-启动关闭选项-服务启动时启动例程,把该项取消就可以了从机日常启动SQL startup mountSQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;备机数据库启动脚本sqlplus /nolog d:messtart_standby.sqlpausenulstart_standby.sql内容conn / as sysdbashutdown immediate;startup nomount;alter database mount standby database;alter database recover managed standby database disconnect from session;select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from (select * FROM V$ARCHIVED_LOG where applied=YES ORDER BY SEQUENCE# desc) where rownumtrunc(sysdate-2) and completion_time STARTUP;备库:SQL STARTUP MOUNT;SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;2、正确关闭顺序备库:SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;SQL SHUTDOWN IMMEDIATE;主库SQL SHUTDOWN IMMEDIATE;3、备库Read-Only 模式打开当前主库正常OPEN 状态、备库处于日志传送状态。1）在备库停止日志传送SQL alter database recover managed standby database cancel;2）备库Read-only 模式打开SQL alter database open read only;3）备库回到日志传送模式SQL alter database recover managed standby database disconnect from session;SQL select status from v$instance;4、日志传送状态监控1）主库察看当前日志状况SQL select sequence#,status from v$log;2）备库察看RFS(Remote File Service)接收日志情况和MRP 应用日志同步主库情况SQL SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;3）察看备库是否和主库同步SQL SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;4）察看备库已经归档的redoSQL SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;5）察看备库已经应用的redoSQL SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;6）察看备库接收,应用redo 数据过程.SQL SELECT MESSAGE FROM V$DATAGUARD_STATUS;三、主库正常切换1、在主库端检验数据库可切换状态SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS:TO STANDBY 表示可以正常切换。如果SWITCHOVER_STATUS 的值为SESSIONS ACTIVE，表示当前有会话处于ACTIVE状态。2、开始主库正常切换如果SWITCHOVER_STATUS 的值为TO STANDBY 则：SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;如果SWITCHOVER_STATUS 的值为SESSIONS ACTIVE 则：SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;成功运行这个命令后，主库被修改为备库。3、重启先前的主库SQL SHUTDOWN IMMEDIATE;SQL STARTUP MOUNT;4、在备库验证可切换状态SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS-TO_PRIMARY1 row selecte