dataguard示例文档(包含主备切换).doc

Dataguard 配置实例准备环境：两台机器安装windowxp主库ip： 192.168.1.20从库ip: 192.168.1.21两台机器可以互相ping通。主库和从库都安装oracle10g，而且版本需要一致， oracle的服务名和实例名需要同样。目录名最好一致。我这里都是为orcl（系统默认）；Primary 数据库：主数据库上先建一个测试表空间，(目的是用于检验standby 从库的数据是否有生成)Create tablespace test datafile E:oracleproduct10.2.0oradataorcltest.dbf size 100m autoextend on;Create user test identified by test default tablespace test ;Grant connect,resource,dba to test;主库配置tns链接到从库（用ncfa配置）配置后主库的tnsname.ora的内容如下：(注： 下面 orcl1为主库，orcl2为从库)# tnsnames.ora Network Configuration File: E:oracleproduct10.2.0db_1networkadmintnsnames.ora# Generated by Oracle configuration tools.ORCL1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )ORCL2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )配置完后 需要 tnsping orcl2 能够出现下图所示（注意 是 tnsping orcl2 而不是tnsping orcl1）操作步骤：主数据库 ：从（1-11）都是主数据库操作1 Sqlplus/nolog2。 conn / as sysdba;-以sysdba身份登录3 Shutdown immediate; -先关闭数据库4 Startup mount;5 。 Alter database archivelog; -开启归档模式6 Alter database force logging;-开启强制记录日志模式7 Alter database open;-打开数据库-生成oracle 备机的数据库控制文件8 Alter database create standby controlfile as d: CONTROL01.CTL;-注： 这里生成的控制文件用于存放在备机的控制文件。 所以备机有几个控制文件，这里需要生成几个。 或者将d: CONTROL01.CTL 拷贝几份，然后重命名。-生成主库 init文件9 Create pfile=d:pfile.ora from spfile; -这样会在d盘目录下 生成pfile.ora文件10 编辑pfile.ora文件，在文件的末尾增加-下面的orcl1，orcl2 都是在tnsname.ora里面配置的注 让归档日志放置在该路径下*.LOG_ARCHIVE_DEST_1=location=E:oracleproduct10.2.0archivelog *.LOG_ARCHIVE_DEST_2=SERVICE=orcl2*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.FAL_SERVER=orcl1*.FAL_CLIENT=orcl2*.log_archive_min_succeed_dest=2- 注： 下面这个设置，只有在两台机器的安装路径不一致的时候需要设置。前面是主库的文件地址，后面是从库的文件地址*.DB_FILE_NAME_CONVERT=E:oracleproduct10.2.0oradataorcl,D:oracleproduct10.2.0oradataorcl 11 shutdown immediate;12： 从库 Sqlplus/nologConn / as sysdbaShutdown immediate;删除从库 oradata下的所有文件（数据文件，控制文件，日志文件）将主库的init的文件拷贝到从库目录下同样 为了主备切换。所以 从库的init文件的上面内容如下注 让归档日志放置在该路径下*.LOG_ARCHIVE_DEST_1=location=E:oracleproduct10.2.0archivelog *.LOG_ARCHIVE_DEST_2=SERVICE=orcl1*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.FAL_SERVER=orcl1*.FAL_CLIENT=orcl2*.log_archive_min_succeed_dest=213 ： 将主库生成的standby控制文件拷贝到从库的oradata文件夹下，14： 将主库的E:oracleproduct10.2.0oradataorcl目录下除控制文件以外的所有的文件拷贝到从库的oradata文件夹下15： 如果主库和从库的数据库安装目录不一致，那么需要进行这一步，如果目录一致，那么不执行下面这步：(我的例子是从E:盘修改到d盘)同时注意，如果有修改目录的话，主库pfile.Ora里面的*.DB_FILE_NAME_CONVERT=E:oracleproduct10.2.0oradataorcl,D:oracleproduct10.2.0oradataorcl从库执行下面的语句：Sqlplus/nologConn / as sysdbaStartup mount;alter database rename file E:oracleproduct10.2.0oradataorclUSERS01.DBF to D:oracleproduct10.2.0oradataorclUSERS01.DBF;alter database rename file E:oracleproduct10.2.0oradataorclUNDOTBS01.DBF to D:oracleproduct10.2.0oradataorclUNDOTBS01.DBF;alter database rename file E:oracleproduct10.2.0oradataorclTEST.DBF to D:oracleproduct10.2.0oradataorclTEST.DBF;alter database rename file E:oracleproduct10.2.0oradataorclTEMP01.DBF to D:oracleproduct10.2.0oradataorclTEMP01.DBF;alter database rename file E:oracleproduct10.2.0oradataorclSYSTEM01.DBF to D:oracleproduct10.2.0oradataorclSYSTEM01.DBF;alter database rename file E:oracleproduct10.2.0oradataorclSYSAUX01.DBF to D:oracleproduct10.2.0oradataorclSYSAUX01.DBF;alter database rename file E:oracleproduct10.2.0oradataorclREDO03.LOG to D:oracleproduct10.2.0oradataorclREDO03.LOG;alter database rename file E:oracleproduct10.2.0oradataorclREDO02.LOG to D:oracleproduct10.2.0oradataorclREDO02.LOG;alter database rename file E:oracleproduct10.2.0oradataorclREDO01.LOG to D:oracleproduct10.2.0oradataorclREDO01.LOG;16: 从库：shutdown immediate;17: 从库：startup mount;18: 从库：alter database recover managed standby database disconnect from session;-开启standby 备用模式这部如果没有抛错误的话，那说明从库是可以运行了19： 主库运行下面语句：Sqlplus/nologConn / as sysdbaStartup pfile=d:pfile.ora20: 测试数据库是否已经成功部署dataguard主库下Conn test/testCreate table td_t1(id number,name varchar2(20);Begin For I in 1.10000 loopInsert into td_t1 values (I,to_char(i);Commit;End loop;End;Alter system switch logfile;Alter system switch logfile;Shutdown immediate;从库下：sqlplus/nologconn / as sysdba;Shutdown immediate;Startup mount;Alter database open read only;-以只读方式打开Conn test/testSelect * from tab;Select count(*) from td_t1;-如果这里有td_t1 表，而且td_t1 的记录数问10000则说明dataguard配置成功另： 主从库切换：8.1 将主库切换到备用状态SQL alter database commit to switchover to physical standby;Database altered.SQL shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL startup nomount;ORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 67111156 bytesDatabase Buffers 96468992 bytesRedo Buffers 2973696 bytesSQL alter database mount standby database;Database altered.SQL select name,open_mode,protection_mode,database_role from v$database;NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE- - - -GUARD1 MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBYSQL alter database recover managed standby database disconnect from session;Database altered.8.2 将备库转换成主库模式SQL alter database commit to switchover to primary;Database altered.SQL shutdown immediateORA-01507: database not mountedORAC