本篇内容介绍了“怎么搭建oracle DataGuard”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！1.备库环境变量复制主库环境变量一致即可，注意sid，$ORACLE_BASE,$ORACLE_HOME2.软件及补丁主库查看补丁版本select COMMENTS from dba_registry_history;或opatch lsinvmkdir /u01/oracle/softwarecd /u01/oracle/software注意：所有7u4的系统都要下载这个补丁包get software/oracle/db/PSU171017/p19692824_112040_Linux-x86-64.zipsftpsftp> cd software/oracle/dbget p13390677_112040_Linux-x86-64_1of7.zip get p13390677_112040_Linux-x86-64_2of7.zip get p6880880_112000_Linux-x86-64.zip get byeunzip p13390677_112040_Linux-x86-64_1of7.zipunzip p13390677_112040_Linux-x86-64_2of7.zipunzip p6880880_112000_Linux-x86-64.zipunzip p19692824_112040_Linux-x86-64.zipunzip cd databasexclock./runInstaller跑脚本Finishcd ..mv $ORACLE_HOME/OPatch/ $ORACLE_HOME/OPatch.bakmv /u01/oracle/software/OPatch/ $ORACLE_HOME/打补丁必须要有环境变量cd 19692824opatch applycd ..cd opatch apply （如果不能打补丁，查看监听是否为关闭状态）opatch lsinv3.主库参数检查检查主备库cpu，内存和文件系统备库尽量和主库一致free -glscpudf -h主库参数检查（保证归档和force logging开启）set line 200col DATABASE_ROLE for a30col DB_UNIQUE_NAME for a20col OPEN_MODE for a30col LOG_MODE for a30select database_role,db_unique_name,open_mode,log_mode,force_logging from v$database;主库参数检查（datafile是否都是online状态或system状态）select distinct status from v$datafile;主库参数检查 （表空间是否都是online状态）select distinct STATUS from dba_tablespaces;主库参数检查（都是FULL）show parameter DB_BLOCK_CHECKING;show parameter DB_LOST_WRITE_PROTECT;show parameter DB_BLOCK_CHECKSUM;如果不是的话查看是修改是否需要重启，不需要就修改alter system set DB_BLOCK_CHECKING=’FULL’;alter system set DB_LOST_WRITE_PROTECT =’FULL’;alter system set DB_BLOCK_CHECKSUM =’FULL’;主库通道检查set linesize 200 pages 999col dest_id for 9999col dest_name for a30col status for a10col error for a50select dest_id,dest_name,status,error from v$archive_dest;show parameter destexit如果主库已经有了一个dg库的话，就要注意_sec变量名字要改成不冲突的名字log_archive_dest_2参数要改成不冲突的通道二.操作步骤1.主库(备库相同)配置tnsnames.oravi $ORACLE_HOME/network/admin/tnsnames.ora = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = ) ) )_sec = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = ) ) )2.备库的listener.ora和sqlnet.oravi $ORACLE_HOME/network/admin/listener.oraSID_LIST_ = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ) (ORACLE_HOME = /u01/oracle/product/db11gr2) (SID_NAME = ) ) ) = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) )ADR_BASE_ = /u01/oracleINBOUND_CONNECT_TIMEOUT_=60SECURE_REGISTER_ = (TCP)ADMIN_RESTRICTIONS_ =ONDIAG_ADR_ENABLED_ = ONvi $ORACLE_HOME/network/admin/sqlnet.oraSQLNET.INBOUND_CONNECT_TIMEOUT =60sqlnet.expire_time =10sqlnet.allow_logon_version =8DIAG_ADR_ENABLED_ =ONlsnrctl start 主备库tnsping tnsping _sec3.主库传输必要文件（密码文件，参数文件，控制文件）export ORACLE_SID=sqlplus / as sysdbacreate pfile=’/tmp/init.ora’ from spfile;exitscp $ORACLE_HOME/dbs/orapw:/home/oraclescp /tmp/init.ora :/tmpscp /usr/openv/scripts/_controlfile.ctl :/tmp备库执行mv /home/oracle/orapw $ORACLE_HOME/dbs4.修改参数，启动到mount修改参数文件：如果是迁移用的dg库则不要修改参数文件如果是单纯的dg库则按照备库的实际内存等修改一般来说修改归档路径，fal_server和fal_client，创建归档文件和审计文件路径xmata.__oracle_base=’/u01/oracle’#ORACLE_BASE set from environment #修改目录*.audit_file_dest=’/db_audit_file_dest/xmata’ #修改目录*.control_files=’/xmata/xmatasys_u01/xmata/control01.ctl’,’/xmata/xmatardo_u01/xmata/control02.ctl’,’/xmata/xmataudo_u01/xmata/control03.ctl’ #修改目录*.db_cache_size=4294967296 #修改大小*.diagnostic_dest=’/u01/oracle’ #修改目录*.fal_client=’xmata_new’ #修改名字client端是自己*.fal_server=’xmata’ #修改名字*.java_pool_size=134217728 #修改大小*.large_pool_size=268435456 #修改大小*.log_archive_dest_1=’LOCATION=/oraarchivelog/xmata’ #修改目录*.log_archive_dest_2=’service=zdhdb_sec LGWR ASYNC NOAFFIRM NET_TIMEOUT=30′ #删除本行*.log_archive_format=’%t_%s_%r.arc’ #添加本行(使归档文件以.arc结尾)*.pga_aggregate_target=2147483648 #修改大小*.sga_max_size=12884901888 #修改大小*.shared_pool_size=1073741824 #修改大小*.utl_file_dir=’/oraarchivelog/xmata’ #修改目录启动到nomount,修改参数export ORACLE_SID=sqlplus / as sysdbacreate spfile from pfile=’/tmp/init.ora’;startup nomount;alter system set standby_file_management=auto; #主库添加数据文件时，dg库自动添加alter system set filesystemio_options=seta免费云主机域名ll scope=spfile; #设置异步IOalter system set parallel_execution_message_size=32768 scope=spfile; #并行执行消息大小,迁移用的不要设置此参数exit启动到mountrman target /restore controlfile from ‘/tmp/_controlfile.ctl’;exitsqlplus / as sysdbaalter database mount;alter database create standby controlfile as ‘/tmp/control01.ctl’;shutdown immediate;startup nomount;exitrman target /restore controlfile from ‘/tmp/control01.ctl’;exitsqlplus / as sysdbaalter database mount;exit5.主库配置日志传输通道，备库部署自删除脚本主库配置日志传输通道COMPRESSION=enable压缩参数alter system set log_archive_dest_2=’service=_sec LGWR ASYNC NOAFFIRM NET_TIMEOUT=30′;alter system set log_archive_dest_state_2=’enable’;col dest_name format a30select DEST_ID,DEST_NAME,STATUS,error from v$archive_dest_status;备库部署自删除脚本mkdir -p /home/oracle/scripts_/cd /home/oracle/scripts_/vi dg_rm_applied_arclog.sh （修改脚本sid，$ORACLE_HOME，$ORACLE_BASE）##########DG搭建的部署自动删除归档日志的定时任务##########export ORACLE_BASE=/u01/oracleexport ORACLE_HOME=/u01/oracle/product/db11gr2export ORACLE_SID=export ORACLE_TERM=xterm export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 export ORA_NLS10=$ORACLE_HOME/nls/data export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/RDBMS/lib:/lib:/usr/libexport JAVA_HOME=$ORACLE_HOME/jdk export ORACLE_DOC=$ORACLE_HOME/doc export SQLPATH=$ORACLE_HOME/dbs export PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$JAVA_HOME/bin:$PATH:/usr/vacpp/bin:/usr/ccs/bin export CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib export TMP=/tmp export TMPDIR=$TMP export NLS_DATE_FORMAT=’yyyy-mm-dd hh34:mi:ss’ export NLS_TIMESTAMP_FORMAT=’yyyy-mm-dd hh34:mi:ss.ff’ export NLS_TIMESTAMP_TZ_FORMAT=’yyyy-mm-dd hh34:mi:ss.ff’ export EDITOR=vi stty erase ^H ######delete archivelog tread 1 SEQ1=`sqlplus -s “/ as sysdba”
set heading off feedback off pagesize 0 verify off echo off numwidth 9select max(SEQUENCE#) from v$archived_log al where al.first_time=sysdate-7 and al.first_time
and al.APPLIED=’YES’ and al.deleted=’NO’ and al.THREAD#=1;exit;EOF`echo ‘DELETE ARC BEFORE’ $SEQ1 `date` > rm_thread1.logrman target /
delete noprompt force archivelog until sequence ${SEQ1} thread 1;exit;EOF######if PRIMARY is RAC, delete archivelog tread 2 SEQ2=`sqlplus -s “/ as sysdba”
set heading off feedback off pagesize 0 verify off echo off numwidth 9select max(SEQUENCE#) from v$archived_log al where al.first_time=sysdate-7 and al.first_time
and al.APPLIED=’YES’ and al.deleted=’NO’ and al.THREAD#=2;exit;EOF`echo ‘DELETE ARC BEFORE’ $SEQ2 `date` > rm_thread2.logif [ ! -n “$SEQ2” ];thenexit 0;elserman target /
delete noprompt force archivelog until sequence ${SEQ2} thread 2;exit;EOFficrontab -e0 * * * * sh /home/oracle/scripts_/dg_rm_applied_arclog.sh >/dev/null 2>&1crontab -l6.磁带库方式恢复配置恢复脚本（17:00之后才能发起）***********************************************************************************************************如果主备库文件系统一致，则忽略此操作如果主备库文件系统不一致，在主库执行以下语句后在备库修改成备库路径，粘贴到res.rcv脚本中restore前select ‘set newname for datafile ‘||file#||’ to ‘||””||” || ‘/’ || ‘datafiletihuanlujing’ ||’/’ || substr(name,instr(name,’/’,1,3)+1)||””||’;’ from v$datafileunion allselect ‘set newname for tempfile ‘||file#||’ to ‘||””||” || ‘/’ || ‘temptihuanlujing’ ||’/’ || substr(name,instr(name,’/’,1,3)+1)||””||’;’ from v$tempfile;或者select ‘alter database rename file ‘||file_name||’ to ‘||””||’目标端data路径’ || ‘/’ || substr(file_name,instr(file_name,’/’,1,3)+1)||””||’;’ from dba_data_files;如下语句粘贴到res.rcv脚本中restore后recover前switch datafile all;***********************************************************************************************************grep CLIENT_NAME /usr/openv/netbackup/bp.conf #主库主机名查询备库恢复脚本：mkdir /home/oracle/rman_cd /home/oracle/rman_vi res.shDATE=`date +%Y%m%d%H`export ORACLE_SID=rman nocatalog target / cmdfile=res.rcv msglog=res.log.$DATEvi res.rcvrun{ALLOCATE CHANNEL ch00 TYPE ‘SBT_TAPE’ parms ‘ENV=(NB_ORA_CLIENT=)’;ALLOCATE CHANNEL ch01 TYPE ‘SBT_TAPE’ parms ‘ENV=(NB_ORA_CLIENT=)’;ALLOCATE CHANNEL ch02 TYPE ‘SBT_TAPE’ parms ‘ENV=(NB_ORA_CLIENT=)’;ALLOCATE CHANNEL ch03 TYPE ‘SBT_TAPE’ parms ‘ENV=(NB_ORA_CLIENT=)’;restore database;recover database delete archivelog;release channel ch00;release channel ch01;release channel ch02;release channel ch03;}nohup sh res.sh &*************************************************************************************************************************如果是数据量很小的新库 可以用此方式恢复6.主库duplicate发起恢复（17:00之后才能发起）DG启动到nomount模式发起（主库或dg库做都可以）rman target sys/@ auxiliary sys/@_sec nocatalogduplicate target database for standby from active database dorecover nofilenamecheck;**************************************************************************************************************************************************************************************************************************************************6.主库压缩全备方式恢复进行全库压缩备份mkdir //db_dg_arc//rmancd !$vi backup.sqlrun{allocate channel c1 type disk maxpiecesize 10G;allocate channel c2 type disk maxpiecesize 10G;allocate channel c3 type disk maxpiecesize 10G;allocate channel c4 type disk maxpiecesize 10G;backup as compressed backupset database format ‘//db_dg_arc//rman/bk_%d_%T_%U’;release channel c1;release channel c2;release channel c3;release channel c4;}vi rman_backup.shexport ORACLE_SID=rman nocatalog target / cmdfile=backup.sql msglog=backup.lognohup sh rman_backup.sh &后台执行开始全库压缩，注意目录空间是否足够存放全备份集备份完毕后把备份片传输到成都对应的目录下DGmkdir -p //db_dg_arc//rman主库scp bk* ://db_dg_arc//rman/备库进行全库恢复cd //db_dg_arc//rman/rman target /CONFIGURE DEFAULT DEVICE TYPE TO DISK;catalog start with ‘//db_dg_arc//rman/’;exitvi recover.sqlrun{allocate channel c1 type disk maxpiecesize 10G;allocate channel c2 type disk maxpiecesize 10G;allocate channel c3 type disk maxpiecesize 10G;allocate channel c4 type disk maxpiecesize 10G;restore database;recover database;release channel c1;release channel c2;release channel c3;release channel c4;}vi rman_recover.shexport ORACLE_SID=rman nocatalog target / cmdfile=recover.sql msglog=recover.lognohup sh rman_recover.sh &cd //db_dg_arc/rm -rf rman*************************************************************************************************************************7.恢复后配置*************************************************************************************************************************文件系统不一致的需要重命名redolog（dg库需要为mount状态，取消日志应用）select member from v$logfile; #主库查询出目前redo日志的名字rename文件系统：show parameter standby_file_managementalter system set standby_file_management=manual;alter database rename file ‘+DATA/zdhdb/onlinelog/group_1.257.876509499’ to ‘/zdhdb/zdhdbrdo_u01/zdhdb/redo01′;……alter system set standby_file_management=auto;show parameter convertalter system set log_file_name_convert=’+DATA/zdhdb/onlinelog’,’/zdhdb/zdhdbrdo_u01/zdhdb’ scope=spfile;alter system set db_file_name_convert=’+DATA/zdhdb/datafile’,’/zdhdb/zdhdbdata_u01/zdhdb’,’+DATA01/zdhdb/datafile’,’/zdhdb/zdhdbdata_u01/zdhdb’ scope=spfile;#添加路径转换参数:（主库新增数据文件，备库可以通过该参数自动转换文件名）**************************************************************************************************************************在备机上创建standby logfile(迁移用的dg库不用创建这步)set pagesize 2000set linesize 150col member for a50col status for a20select g.group#, g.thread#,g.status,f.member, g.members, g.bytes/1024/1024 MB,g.sequence# from v$log g, v$logfile f where g.group#=f.group# order by 1;#主库查看redo日志路径及大小standby logfile需要比redologfile多一组,每组只能创建一个成员,group编号不能重复，group编号要比主库上最大group#大于20ALTER DATABASE ADD STANDBY LOGFILE group 30 (‘//stdredo30.log’) SIZE ;……主库重启通道（不重启查询同步时间报错）sqlplus / as sysdbaalter system set log_archive_dest_state_2=defer;alter system set log_archive_dest_state_2=enable;备库show parameter fal #没有的话设置一下alter system set fal_server=’‘;alter system set fal_client=’_sec’;show parameter falshu immediatestartup nomountalter database mount;show parameter convert8.应用日志alter database open read only;（不报错直接执行，如果没有standby redo就不用加using current logfile）alter database recover managed standby database using current logfile disconnect from session;（如果报错执行以下四步，如果没有standby redo就不用加using current logfile）alter database recover managed standby database disconnect from session; #应用到最新日志，可能会很长时间，可能有gapalter database recover managed standby database cancel;alter database open read only ;alter database recover managed standby database using current logfile disconnect from session;“怎么搭建oracle DataGuard”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注百云网站，小编将为大家输出更多高质量的实用文章！
相关推荐: Oracle里面的外连中where和on之后and有啥区别
1、表做关联的免费云主机域名时候有些条件写在where中，有些写在on之后关联条件的and中。那么有什么区别呢。 2、我们指导，表关联一般分为两种：内联和外联。 3、我们先来看内联。 4、准备两个表做连接。表A：test_tb_stid；表B：test_tb_…
免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。
微信扫一扫 
