dataguard搭建-rac到单机

2025-04-16 技术教程

DG兼容列表：

Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration (Doc ID 413484.1)

DG在IBM和sun之间的异构说明：

Using Oracle Data Guard between IBM AIX on Power systems and Oracle Solaris on SPARC systems (Doc ID 1982638.1)

简单点说就是linux可以到windows/soloaris x64，IBM可以和solaris sparc异构，hp unix只能自己玩；

这里举例linux x86_64　rac到单机之间的异构部署。

一检查源端,打开归档

SQL>archiveloglist;#如果未打开，关闭数据库Shutdownimmediate;#启动至mount状态Startupmount#开启归档SQL>alterdatabasearchivelog;Databasealtered.#打开强制日志，不打开，使nologging选项时，备库的表会报错SQL>alterdatabaseforcelogging;Databasealtered.SQL>alterdatabaseopen;Databasealtered.#检查SQL>archiveloglist;DatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestination/data/archOldestonlinelogsequence12Nextlogsequencetoarchive14Currentlogsequence14SQL>selectforce_loggingfromv$database;FORCE---YES

二更改参数

altersystemsetlog_archive_config='DG_CONFIG=(db,standby)'scope=both;altersystemsetlog_archive_dest=''scope=both;altersystemsetlog_archive_dest_1='location=/data/arch'scope=spfile;--用归档传输altersystemsetlog_archive_dest_2='service=standbyarchvalid_for=(online_logfiles,primary_role)db_unique_name=standby'scope=spfile;--首选在线日志传输，实时应用altersystemsetlog_archive_dest_2='service=standbylgwrsyncaffirmvalid_for=(online_logfiles,primary_role)db_unique_name=standby'scope=both;SQL>altersystemsetlog_archive_max_processes=4scope=both;SQL>altersystemsetfal_server=standbyscope=both;SQL>altersystemsetfal_client=dbscope=both;SQL>altersystemsetstandby_file_management=autoscope=both;SQL>altersystemsetdb_file_name_convert='+data','/data'scope=spfile;SQL>altersystemsetlog_file_name_convert='+data','/data'scope=spfile;Sql>altersystemsetdb_unique_name=dbscope=spfile;#先不启用SQL>altersystemsetlog_archive_dest_state_2=defer;

三配置tnsnames

服务端$ORACLE_HOME/network/admin/tnsnames.ora

standby=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.84.17)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standby)))

四备份主库：

rman"target/nocatalog"run{configuredefaultdevicetypetodisk;configuredevicetypediskparallelism1;backupascompressedbackupsetdatabaseformat'/home/oracle/full_%s_%p_%T.bak'TAGfullbak_fordg;}

五创建standby controlfile：

创建standby controlfile：

SQL>alterdatabasecreatestandbycontrolfileas'/home/oracle/standby.ctl';

六创建pfile

create pfile='/data/backup/pfile.ora' from spfile;

主要修改db_unique_name

Fal_server

Fal_client

参考如下：

*.audit_file_dest='/opt/db/adump'*.audit_trail='none'*.cluster_database=false*.compatible='11.2.0.4.0'*.db_file_name_convert='+DATA','/data'*.log_file_name_convert='+DATA','/data'*.control_files='/u01/data/ctl_01.ctl'*.log_archive_config='DG_CONFIG=(db,standby)'*.db_block_size=8192*.db_create_file_dest='/data'*.db_domain=''*.db_files=200*.db_name='db'*.db_unique_name='standby'*.db_recovery_file_dest_size=53477376000*.db_recovery_file_dest=''*.deferred_segment_creation=FALSE*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP)(SERVICE=TCIS30XDB)'*.log_archive_dest_1='location=/data/archvalid_for=(all_logfiles,all_roles)db_unique_name=standby'*.open_cursors=100*.pga_aggregate_target=50MB*.processes=100*.remote_login_passwordfile='exclusive'*.sessions=150*.sga_target=100MB*.undo_tablespace='UNDOTBS1'*.fal_server=db

七创建密钥文件

orapwdfile=orapw$ORACLE_SIDpassword=oracle

八创建备库 listener.ora

SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=PLSExtProc)(ORACLE_HOME=/opt/product/11.2)(PROGRAM=extproc))(SID_DESC=(GLOBAL_DBNAME=standby)(ORACLE_HOME=/opt/product/11.2)(SID_NAME=sid_name)))

9 备机创建目录

mkdir /data/arch

mkdir /data/dg1

mkdir –p /u01/app/oracle/admin/standby/adump

更改~/.bash_profile

Export ORACLE_SID=standby

10启动及恢复

cpstandby.ctl/data/standby/ctl01.ctlSQL>startupnomount;SQL>alterdatabasemountstandbydatabase;rmantarget/catalogstartwith'/home/oracle/backup';Restoredatabase;SQL>recovermanagedstandbydatabasedisconnectfromsession;

11 备库添加standby log file;

alterdatabaserecovermanagedstandbydatabasecancelalterdatabaseaddstandbylogfilethread1group10'+data(flashfile)'size50m;alterdatabaseaddstandbylogfilethread1group11'+data(flashfile)'size50m;alterdatabaseaddstandbylogfilethread2group12'+data(flashfile)'size50m;alterdatabaseaddstandbylogfilethread2group13'+data(flashfile)'size50m;alterdatabaseaddstandbylogfilegroup14'/u01/data/standbyredo09.log'size50m;

#主库启用日志传送

SQL>altersystemsetlog_archive_dest_state_2=enabled;

#模式为最大可用

SQL>alterdatabasesetstandbydatabasetomaximizeavailability;alterdatabaserecovermanagedstandbydatabasecancel;alterdatabaseopen;alterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnectfromsession;

12 检查

selectprocess,client_process,sequence#,statusfromv$managed_standby;colcurrent_scnfor999999999999;selectprotection_mode,database_role,open_mode,current_scnfromv$databasecolnamefora50;colsequence#for9999999;selectname,sequence#,to_char(completion_time,'yyyy-mm-ddhh34:mi:ss')fromv$archived_logorderby2;SELECTPROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKSFROMV$MANAGED_STANDBY;--主库查询selectsequence#,statusfromv$Log;colvaluefora30;colnamefora13;colunitfora30;selectname,value,unit,time_computedfromv$dataguard_stats;SELECT*FROMV$STANDBY_EVENT_HISTOGRAMWHERENAME='applylag'ANDCOUNT>0;colcheckpoint_change#for99999999999999;selectname,checkpoint_change#fromv$datafile;selectname,checkpoint_change#fromv$datafile_header;SELECTDBID,NAME,TO_CHAR(CREATED,'DD-MON-YYYYHH24:MI:SS')CREATED,OPEN_MODE,LOG_MODE,TO_CHAR(CHECKPOINT_CHANGE#,'999999999999999')ASCHECKPOINT_CHANGE#,CONTROLFILE_TYPE,TO_CHAR(CONTROLFILE_CHANGE#,'999999999999999')ASCONTROLFILE_CHANGE#,TO_CHAR(CONTROLFILE_TIME,'DD-MON-YYYYHH24:MI:SS')CONTROLFILE_TIME,TO_CHAR(RESETLOGS_CHANGE#,'999999999999999')ASRESETLOGS_CHANGE#,TO_CHAR(RESETLOGS_TIME,'DD-MON-YYYYHH24:MI:SS')RESETLOGS_TIMEFROMV$DATABASE;--sofar单位为KB

select*fromv$recovery_progress;--检查归档应用情况colnamefora50;colsequence#for9999999;selectname,sequence#,to_char(completion_time,'yyyy-mm-ddhh34:mi:ss')fromv$archived_logorderby2;SELECTPROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKSFROMV$MANAGED_STANDBY;selectthread#,max(sequence#)maxseqfromgv$archived_loggroupbythread#orderbythread#;select*fromv$archive_gap;--检查net_timeout的适当值selectfrequency,durationfromv$redo_dest_resp_histogramwheredest_id=2andfrequency>1;selectrecovery_modefromv$archive_dest_statuswheredest_id=2;