dataguard搭建-rac到单机-创新互联
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>archive log list; #如果未打开，关闭数据库 Shutdown immediate; #启动至mount状态 Startup mount #开启归档 SQL> alter database archivelog ; Database altered. #打开强制日志，不打开，使nologging选项时，备库的表会报错 SQL> alter database force logging; Database altered. SQL> alter database open; Database altered. #检查 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /data/arch Oldest online log sequence 12 Next log sequence to archive 14 Current log sequence 14 SQL> select force_logging from v$database; FORCE --- YES二 更改参数
alter system set log_archive_config='DG_CONFIG=(db,standby)' scope=both; alter system set log_archive_dest='' scope=both; alter system set log_archive_dest_1='location=/data/arch' scope=spfile; --用归档传输 alter system set log_archive_dest_2='service=standby arch valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=spfile; --选在线日志传输，实时应用 alter system set log_archive_dest_2='service=standby lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=both; SQL> alter system set log_archive_max_processes=4 scope=both; SQL> alter system set fal_server=standby scope=both; SQL> alter system set fal_client=db scope=both; SQL> alter system set standby_file_management=auto scope=both; SQL> alter system set db_file_name_convert='+data','/data' scope=spfile; SQL> alter system set log_file_name_convert='+data','/data' scope=spfile; Sql>alter system set db_unique_name=db scope=spfile; #先不启用 SQL>alter system set log_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{ configure default device type to disk; configure device type disk parallelism 1; backup as compressed backupset database format '/home/oracle/full_%s_%p_%T.bak' TAG fullbak_fordg; }五 创建standby controlfile：
创建standby controlfile：
SQL> alter database create standby controlfile as '/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/arch valid_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七 创建密钥文件
orapwd file=orapw$ORACLE_SID password=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启动及恢复
cp standby.ctl /data/standby/ctl01.ctl SQL> startup nomount; SQL> alter database mount standby database; rman target / catalog start with '/home/oracle/backup'; Restore database; SQL>recover managed standby database disconnect from session;11 备库添加standby log file;
alter database recover managed standby database cancel alter database add standby logfile thread 1 group 10 '+data(flashfile)' size 50m; alter database add standby logfile thread 1 group 11 '+data(flashfile)' size 50m; alter database add standby logfile thread 2 group 12 '+data(flashfile)' size 50m; alter database add standby logfile thread 2 group 13 '+data(flashfile)' size 50m; alter database add standby logfile group 14 '/u01/data/standbyredo09.log' size 50m;#主库启用日志传送
SQL>alter system set log_archive_dest_state_2=enabled;#模式为大可用
SQL>alter database set standby database to maximize availability; alter database recover managed standby database cancel; alter database open; alter database recover managed standby database using current logfile disconnect from session;12 检查
select process,client_process,sequence#,status from v$managed_standby; col current_scn for 999999999999; select protection_mode,database_role,open_mode,current_scn from v$database col name for a50 ; col sequence# for 9999999; select name,sequence#,to_char(completion_time,'yyyy-mm-dd hh34:mi:ss') from v$archived_log order by 2; SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; --主库查询 select sequence#,status from v$Log; col value for a30; col name for a13; col unit for a30; select name,value,unit,time_computed from v$dataguard_stats; SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0; col checkpoint_change# for 99999999999999; select name,checkpoint_change# from v$datafile; select name,checkpoint_change# from v$datafile_header; SELECT DBID, NAME, TO_CHAR(CREATED, 'DD-MON-YYYY HH24:MI:SS') CREATED, OPEN_MODE, LOG_MODE, TO_CHAR(CHECKPOINT_CHANGE#, '999999999999999') AS CHECKPOINT_CHANGE#, CONTROLFILE_TYPE, TO_CHAR(CONTROLFILE_CHANGE#, '999999999999999') AS CONTROLFILE_CHANGE#, TO_CHAR(CONTROLFILE_TIME, 'DD-MON-YYYY HH24:MI:SS') CONTROLFILE_TIME, TO_CHAR(RESETLOGS_CHANGE#, '999999999999999') AS RESETLOGS_CHANGE#, TO_CHAR(RESETLOGS_TIME, 'DD-MON-YYYY HH24:MI:SS') RESETLOGS_TIME FROM V$DATABASE; --sofar 单位为KBselect * from v$recovery_progress; --检查归档应用情况 col name for a50 ; col sequence# for 9999999; select name,sequence#,to_char(completion_time,'yyyy-mm-dd hh34:mi:ss') from v$archived_log order by 2; SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; select thread#,max(sequence#) maxseq from gv$archived_log group by thread# order by thread#; select * from v$archive_gap; --检查net_timeout的适当值 select frequency,duration from v$redo_dest_resp_histogram where dest_id=2 and frequency>1; select recovery_mode from v$archive_dest_status where dest_id=2;另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
网页名称：dataguard搭建-rac到单机-创新互联
URL链接：http://azwzsj.com/article/cdhhcp.html
