Oracle11g 搭建单实例DataGuard.doc

环境：主备库都为单实例并且数据库SID相同OS：red hat 6.5Oracle:11.2.0.4.3主库操作1.开启归档模式创建归档目录rootenn # mkdir -p /u01/archivelogrootenn # chown -R oracle:oinstall /u01/archivelogrootenn # chmod 777 /u01/archivelog数据库到mount状态开启归档模式rootenn # su - oracleoracleenn $ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 20 23:46:46 2014Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL startup mountORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2257840 bytesVariable Size 541068368 bytesDatabase Buffers 289406976 bytesRedo Buffers 2371584 bytesDatabase mounted.设置主库归档目录SQL alter system set log_archive_dest=/u01/archivelog;alter system set log_archive_dest_1=location=/data/oracle/log1/archive_log;最后的目录名称需要为archive_log开启归档模式SQL alter database archivelog;Database altered.查看归档设置SQL archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/archivelogOldest online log sequence 2Next log sequence to archive 4Current log sequence 42.启动force_logging模式查看是否force_logging模式SQL select log_mode,force_logging from v$database;LOG_MODE FOR- -ARCHIVELOG NO开启force_logging模式SQL alter database force logging;Database altered.3.创建备库日志文件路径查看数据库的日志组个数与大小，因为我们创建standby日志组的个数是原日志组个数+1再与thread的积(2+1)*3)，size不能小于原日志文件的大小。SQL select group#,thread#,bytes/1024/1024 M ,STATUS from v$log; GROUP# THREAD# M STATUS- - - - 1 1 50 CURRENT 3 1 50 INACTIVE 2 1 50 INACTIVESQL select member from v$logfile;MEMBER-/u01/app/oracle/oradata/ENN/redo03.log/u01/app/oracle/oradata/ENN/redo02.log/u01/app/oracle/oradata/ENN/redo01.log创建备库日志组路径SQL alter database add standby logfile thread 1 group 4 (/u01/app/oracle/oradata/ENN/redo04.log) size 50M;Database altered.SQL alter database add standby logfile thread 1 group 5 (/u01/app/oracle/oradata/ENN/redo05.log) size 50M;Database altered.SQL alter database add standby logfile thread 1 group 6 (/u01/app/oracle/oradata/ENN/redo06.log) size 50M;Database altered.SQL alter database add standby logfile thread 1 group 7 (/u01/app/oracle/oradata/ENN/redo07.log) size 50M;Database altered.查看状态SQL select group#,status,type,member from v$logfile; GROUP# STATUS TYPE MEMBER- - - - 3 ONLINE /u01/app/oracle/oradata/ENN/redo03.log 2 ONLINE /u01/app/oracle/oradata/ENN/redo02.log 1 ONLINE /u01/app/oracle/oradata/ENN/redo01.log 4 STANDBY /u01/app/oracle/oradata/ENN/redo04.log 5 STANDBY /u01/app/oracle/oradata/ENN/redo05.log 6 STANDBY /u01/app/oracle/oradata/ENN/redo06.log 7 STANDBY /u01/app/oracle/oradata/ENN/redo07.log4.创建监听执行netca创建监听器oracleenn oracle$ netca修改tnsname文件oracleenn oracle$ cd $ORACLE_HOME/network/adminoracleenn admin$ vim tnsname.oraENN = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.80.15 )(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = enn) ) )ENN_DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.16)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = enn_dg) ) )tnsname.ora复制到备库中oracleenn admin$ scp tnsname.ora oracle192.168.80.16:$ORACLE_HOME/network/admin/tnsname.oraoracle192.168.80.16s password:tnsname.ora 100% 361 0.4KB/s 00:00注：可以使用图形配置tnsnameoracleenn admin$ netmgr附：如果备库tnsping不通,关闭防火墙oracleenn_dg $ tnsping ennTNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2014 09:26:09Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.15)(PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = ENN)TNS-12543: TNS:destination host unreachablerootenn # chkconfig iptables offrootenn # service iptables stop5.设置主库和备库归档路径设置主库归档路径SQL alter system set log_archive_dest=;System altered.SQL alter system set log_archive_dest_1=LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ENN;System altered.设置备库归档路径SQL alter system set log_archive_dest_2=SERVICE=enn_dg async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ENN;System altered.配置归档最大进程数SQL show parameter log_archive_maxNAME TYPE VALUE- - -log_archive_max_processes integer 4SQL alter system set log_archive_max_processes=30;System altered.6.配置参数文件生成参数文件SQL create pfile =/u01/app/oracle/initENN.ora from spfile;File created.修改参数文件oracleenn oracle$ vim initENN.oraDB_UNIQUE_NAME=ENN#LOG_ARCHIVE_CONFIG=DG_CONFIG=(ENN,ENN_DG)LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLELOG_ARCHIVE_FORMAT=%t_%s_%r.arcFAL_SERVER=ENN_DGFAL_CLIENT=ENNSTANDBY_FILE_MANAGEMENT=AUTO7.配置密码文件备份standby库参数文件和密码文件oracleenn pfile$ cd $ORACLE_HOME/dbsoracleenn dbs$ cp orapwENN orapwENN.backoracleenn dbs$ cp spfileENN.ora spfileENN.ora.bak将参数文件和密码文件传到备库oracleenn oracle$ scp initENN.ora oracle192.168.80.16:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.oraoracleenn oracle$ scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN oracle192.168.80.16:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN备库操作关闭防火墙rootenn_dg # chkconfig iptables offrootenn_dg # service iptables stop图形方式创建监听器，同主库操作相同oracleenn $ netca注：可以使用图形方式配置tnsname.oraoracleenn $ netmgr启动备库到nomountSQLstartup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora;Duplicate复制主库到备库用rman连接主库和备库oracleenn $ rman target sys/oracleENN auxiliary sys/oracleENN_DGRecovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 21 11:02:39 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: ENN (DBID=4141660501)connected to auxiliary database: ENN (not mounted)执行同步复制备库操作RMAN duplicate target database for standby from active database spfile set db_unique_name ENN;出现错误：sql statement: alter database mount standby databaseRMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERTRMAN-00571: =RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =RMAN-00571: =RMAN-03002: failure of Duplicate Db command at 07/21/2014 11:15:58RMAN-05501: aborting duplication of target databaseRMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/users01.dbf conflicts with a file used by the target databaseRMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/undotbs01.dbf conflicts with a file used by the target databaseRMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/sysaux01.dbf conflicts with a file used by the target databaseRMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/system01.dbf conflicts with a file used by the target database附：a、在异机克隆时，如果auxiliary DB使用了与target DB相同的磁盘配置，目录结构以及文件名时，必须指定NOFILENAMECHECK。NOFILENAMECHECK可以阻止检查target DB的数据文件及联机日志文件是否处于正常使用的状态。而auxiliary DB与target DB的磁盘配置，目录结构以及文件名任一不同时，应避免使用NOFILENAMECHECK。b、对于没有连接到target DB或catalog的情形，应使用BACKUP LOCATION 指定备份文件所在的位置。继续执行duplicate(注：Duplicate时如果主库与备库文件目录结构与文件名都相同时需要使用nofilenamecheck参数)RMAN duplicate target database for standby from active database spfile set db_unique_name ENNnofilenamecheck;出现问题：Starting Duplicate Db at 21-JUL-14RMAN-00571: =RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =RMAN-00571: =RMAN-03002: failure of Duplicate Db command at 07/21/2014 11:35:59RMAN-05501: aborting duplication of target databaseRMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clauseMosL(备库必需用主库传过来的pfile启动到nomount状态，才能执行duplicate)CAUSE:A DUPLICATE was attempted when the auxiliary database was started with a server parameter file and the SPFILE sub-clause was specified in Duplicate syntax.RMAN cannot restore the server parameter file if the auxiliary database is already started with a server parameter file.SOLUTION:Start the auxiliary database with a client parameter file(pfile) orDo not specify SPFILE sub-clause and retry.启动备库到nomount是使用pfileSQL startup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora;重新同步复制RMAN duplicate target database for standby from active database spfile set db_unique_name ENN nofilenamecheck;报错但正常完成复制LORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 1 thread 1: /u01/app/oracle/oradata/ENN/redo01.logRMAN-05535: WARNING: All redo log files were not defined properly.ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 2 thread 1: /u01/app/oracle/oradata/ENN/redo02.logRMAN-05535: WARNING: All redo log files were not defined properly.ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 3 thread 1: /u01/app/oracle/oradata/ENN/redo03.logRMAN-05535: WARNING: All redo log files were not defined properly.ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 4 thread 1: /u01/app/oracle/oradata/ENN/redo04.logRMAN-05535: WARNING: All redo log files were not defined properly.ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 5 thread 1: /u01/app/oracle/oradata/ENN/redo05.logRMAN-05535: WARNING: All redo log files were not defined properly.ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 6 thread 1: /u01/app/oracle/oradata/ENN/redo06.logRMAN-05535: WARNING: All redo log files were not defined properly.ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 7 thread 1: /u01/app/oracle/oradata/ENN/redo07.logRMAN-05535: WARNING: All redo log files were not defined properly.Finished Duplicate Db at 21-JUL-14备库日志应用1.备库开启日志应用开启备库日志应用SQL alter database recover managed standby database using current logfile disconnect from session;Database altered.2.验证备库日志应用验证备库接收日志是否应用SQL select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;SEQUENCE# FIRST_TIME NEXT_TIME APPLIED- - - - 8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES 9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES主库切换日志SQL alter system switch logfile;System altered.SQL /System altered.SQL /System altered.备库查看日志select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;SEQUENCE# FIRST_TIME NEXT_TIME APPLIED- - - - 8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES 9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES 10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES 11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES 12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES备库数据库开启read onlySQL alter database open read only;alter database open read only*ERROR at line 1:ORA-10456: cannot open standby database; media recovery session may be inProgress关闭备库管理SQL alter database recover managed standby database cancel;Database altered.主库切换日志SQL alter system switch logfile;System altered.SQL /System altered.备库已接收但未应用SQL select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;SEQUENCE# FIRST_TIME NEXT_TIME APPLIED- - - - 8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES 9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES 10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES 11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES 12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES13 2014-07-21 12:23:27 2014-07-21 12:42:17NO14 2014-07-21 12:42:17 2014-07-21 12:42:19NO备库open到read only模式SQL alter database open read only;Database altered.备库开启日志应用SQL alter database recover managed standby database using current logfile disconnect from session;Database altered.备库已经应用主库日志SQL select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;SEQUENCE# FIRST_TIME NEXT_TIME APPLIED- - - - 8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES 9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES 10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES 11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES 12 2014-07-21 12:23:23