Oracle11g-搭建单实例DataGuard

环境：主备库都为单实例并且数据库SID相同OS：redhat6.5Oracle:11.2.0.4.3主库操作1.开启归档模式创建归档目录[root@enn~]#mkdir-p/u01/archivelog[root@enn~]#chown-Roracle:oinstall/u01/archivelog[root@enn~]#chmod777/u01/archivelog数据库到mount状态开启归档模式[root@enn~]#su-oracle[oracle@enn~]$sqlplus/assysdbaSQL*Plus:Release11.2.0.4.0ProductiononSunJul2023:46:462014Copyright(c)1982,2013,Oracle.Allrightsreserved.Connectedtoanidleinstance.SQLstartupmountORACLEinstancestarted.TotalSystemGlobalArea835104768bytesFixedSize2257840bytesVariableSize541068368bytesDatabaseBuffers289406976bytesRedoBuffers2371584bytesDatabasemounted.设置主库归档目录SQLaltersystemsetlog_archive_dest='/u01/archivelog';altersystemsetlog_archive_dest_1='location=/data/oracle/log1/archive_log';最后的目录名称需要为archive_log开启归档模式SQLalterdatabasearchivelog;Databasealtered.查看归档设置SQLarchiveloglist;DatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestination/u01/archivelogOldestonlinelogsequence2Nextlogsequencetoarchive4Currentlogsequence42.启动force_logging模式查看是否force_logging模式SQLselectlog_mode,force_loggingfromv$database;LOG_MODEFOR---------------ARCHIVELOGNO开启force_logging模式SQLalterdatabaseforcelogging;Databasealtered.3.创建备库日志文件路径查看数据库的日志组个数与大小，因为我们创建standby日志组的个数是原日志组个数+1再与thread的积((2+1)*3)，size不能小于原日志文件的大小。SQLselectgroup#,thread#,bytes/1024/1024M,STATUSfromv$log;GROUP#THREAD#MSTATUS----------------------------------------------1150CURRENT3150INACTIVE2150INACTIVESQLselectmemberfromv$logfile;MEMBER--------------------------------------------------------------------------------/u01/app/oracle/oradata/ENN/redo03.log/u01/app/oracle/oradata/ENN/redo02.log/u01/app/oracle/oradata/ENN/redo01.log创建备库日志组路径SQLalterdatabaseaddstandbylogfilethread1group4('/u01/app/oracle/oradata/ENN/redo04.log')size50M;Databasealtered.SQLalterdatabaseaddstandbylogfilethread1group5('/u01/app/oracle/oradata/ENN/redo05.log')size50M;Databasealtered.SQLalterdatabaseaddstandbylogfilethread1group6('/u01/app/oracle/oradata/ENN/redo06.log')size50M;Databasealtered.SQLalterdatabaseaddstandbylogfilethread1group7('/u01/app/oracle/oradata/ENN/redo07.log')size50M;Databasealtered.查看状态SQLselectgroup#,status,type,memberfromv$logfile;GROUP#STATUSTYPEMEMBER----------------------------------------------------------------3ONLINE/u01/app/oracle/oradata/ENN/redo03.log2ONLINE/u01/app/oracle/oradata/ENN/redo02.log1ONLINE/u01/app/oracle/oradata/ENN/redo01.log4STANDBY/u01/app/oracle/oradata/ENN/redo04.log5STANDBY/u01/app/oracle/oradata/ENN/redo05.log6STANDBY/u01/app/oracle/oradata/ENN/redo06.log7STANDBY/u01/app/oracle/oradata/ENN/redo07.log4.创建监听执行netca创建监听器[oracle@ennoracle]$netca修改tnsname文件[oracle@ennoracle]$cd$ORACLE_HOME/network/admin[oracle@ennadmin]$vimtnsname.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复制到备库中[oracle@ennadmin]$scptnsname.oraoracle@192.168.80.16:$ORACLE_HOME/network/admin/tnsname.oraoracle@192.168.80.16'spassword:tnsname.ora100%3610.4KB/s00:00注：可以使用图形配置tnsname[oracle@ennadmin]$netmgr附：如果备库tnsping不通,关闭防火墙[oracle@enn_dg~]$tnspingennTNSPingUtilityforLinux:Version11.2.0.4.0-Productionon21-JUL-201409:26:09Copyright(c)1997,2013,Oracle.Allrightsreserved.Usedparameterfiles:UsedTNSNAMESadaptertoresolvethealiasAttemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.80.15)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ENN)))TNS-12543:TNS:destinationhostunreachable[root@enn~]#chkconfigiptablesoff[root@enn~]#serviceiptablesstop5.设置主库和备库归档路径设置主库归档路径SQLaltersystemsetlog_archive_dest='';Systemaltered.SQLaltersystemsetlog_archive_dest_1='LOCATION=/u01/archivelogVALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=ENN';Systemaltered.设置备库归档路径SQLaltersystemsetlog_archive_dest_2='SERVICE=enn_dgasyncVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=ENN';Systemaltered.配置归档最大进程数SQLshowparameterlog_archive_maxNAMETYPEVALUE-----------------------------------------------------------------------------log_archive_max_processesinteger4SQLaltersystemsetlog_archive_max_processes=30;Systemaltered.6.配置参数文件生成参数文件SQLcreatepfile='/u01/app/oracle/initENN.ora'fromspfile;Filecreated.修改参数文件[oracle@ennoracle]$viminitENN.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库参数文件和密码文件[oracle@ennpfile]$cd$ORACLE_HOME/dbs[oracle@enndbs]$cporapwENNorapwENN.back[oracle@enndbs]$cpspfileENN.oraspfileENN.ora.bak将参数文件和密码文件传到备库[oracle@ennoracle]$scpinitENN.oraoracle@192.168.80.16:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora[oracle@ennoracle]$scp/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENNoracle@192.168.80.16:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN备库操作关闭防火墙[root@enn_dg~]#chkconfigiptablesoff[root@enn_dg~]#serviceiptablesstop图形方式创建监听