1主库ALTER DATABASE FORCE LOGGING;ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;1.1监听配置ps:主库为asm单实例[gridora2 admin]$catlistener.ora#Backup file is /u01/app/grid/crsdata/ora2/output/listener.ora.bak.ora2.grid line added by AgentSID_LIST_LISTENER(SID_LIST(SID_DESC(GLOBAL_DBNAMEorcl)(ORACLE_HOME/u01/app/oracle/product/19.0.0/db_1)(SID_NAMEorcl)))LISTENER(DESCRIPTION_LIST(DESCRIPTION(ADDRESS(PROTOCOLIPC)(KEYLISTENER)))(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.56.121)(PORT1521))))ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENERON# line added by AgentVALID_NODE_CHECKING_REGISTRATION_LISTENERON# line added by Agent[gridora2 admin]$1.2主库tns[oracleora2 admin]$cattnsnames.ora# Generated by Oracle configuration tools. PDBDGORCL STD_ORCL(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.56.120)(PORT1521))(CONNECT_DATA(SERVERDEDICATED)(SERVICE_NAMEorcl)))ORCL(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.56.121)(PORT1521))(CONNECT_DATA(SERVERDEDICATED)(SERVICE_NAMEorcl)))1.3主库参数文件[oracleora2 ~]$catpfile *.audit_file_dest/u01/app/oracle/admin/orcl/adump*.audit_traildb*.common_user_prefix*.compatible19.0.0*.control_filesB/ORCL/CONTROLFILE/current.260.1139638345*.db_create_file_destB*.db_nameorcl*.db_recovery_file_dest_size5368709120*.db_recovery_file_dest/u01/flash*.dg_broker_startTRUE *.diagnostic_dest/u01/app/oracle*.enable_pluggable_databasetrue *.fal_serverSTD_ORCL*.local_listener*.log_archive_configDG_CONFIG(orcl,std_orcl)*.log_archive_dest_1location/home/oracle/arch VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAMEorc l*.log_archive_dest_2servicestd_orcl async VALID_FOR(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAMEst d_orcl*.log_archive_dest_state_1enable*.log_archive_dest_state_2enable*.log_file_name_convert/u01/app/oracle/oradata/,B/ORCL/ONLINELOG/*.db_file_name_convert/u01/app/oracle/oradata,B*.standby_file_managementAUTO*.temp_undo_enabledTRUE *.timed_statisticsTRUE2.备库2.1备库监听[oracleora1 admin]$catlistener.ora SID_LIST_LISTENER(SID_LIST(SID_DESC(GLOBAL_DBNAMEorcl)(ORACLE_HOME/u01/app/oracle/product/19.0.0/db_1)(SID_NAMEorcl)))LISTENER(DESCRIPTION_LIST(DESCRIPTION(ADDRESS(PROTOCOLIPC)(KEYLISTENER)))(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.56.120)(PORT1521))))2.2备库tns从主库复制过来[oracleora1 admin]$cattnsnames.ora STD_ORCL(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.56.120)(PORT1521))(CONNECT_DATA(SERVERDEDICATED)(SERVICE_NAMEorcl)))ORCL(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.56.121)(PORT1521))(CONNECT_DATA(SERVERDEDICATED)(SERVICE_NAMEorcl)))2.3备库pfile*.control_files/u01/app/oracle/oradata/control01.ctl#Restore Controlfile*.db_block_size8192*.db_create_file_dest/u01/app/oracle/oradata*.db_nameorcl*.db_recovery_file_dest_size5368709120*.db_recovery_file_dest/u01/flash*.db_unique_namestd_orcl*.dg_broker_startTRUE *.diagnostic_dest/u01/app/oracle*.dispatchers(PROTOCOLTCP) (SERVICEorclXDB)*.enable_pluggable_databasetrue *.fal_clientSTD_ORCL*.fal_serverorcl*.local_listener*.log_archive_configDG_CONFIG(std_orcl,orcl)*.log_archive_dest_1location/home/oracle/arch valid_for(all_logfiles,all_roles) db_unique_namestd _orcl*.log_archive_dest_2serviceorcl async VALID_FOR(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAMEorcl*.log_file_name_convertB/ORCL/ONLINELOG/,/u01/app/oracle/oradata/*.db_file_name_convertB,/u01/app/oracle/oradata*.standby_file_managementAUTO*.temp_undo_enabledTRUE *.timed_statisticsTRUE3.参数说明nohup rman target sys/system2025WLORA_PRO auxiliary sys/system2025WLORA_DR EOF /tmp/duplicate_standby.log 21 RUN {DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;}EXIT;EOF1.DB_NAME 只需注意DataGuard的主备各节点instance使用相同的db_name即可。推荐与service_name一致。我们这里未作重定义2.DB_UNIQUE_NAME1Primary与Standby端数据库的唯一名字设定后不可再更改。2如果主备db_unique_name不一样需要与LOG_ARCHIVE_CONFIG配合使用3db_unique_name并未规定需要与数据库service_name一致可以自定义任意名称。3.LOG_ARCHIVE_CONFIG 列出主备库上的DB_UNIQUE_NAME 参数。默认情况下定义该参数保证数据库能够发送或接收redo log。4.LOG_ARCHIVE_DEST_1 本地归档路径。Primary与Standby需要定义各自的online redo log的归档地址以系统实际的存放路径为准。 本例LOG_ARCHIVE_DEST_1LOCATION/u01/arch/ VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAMEbj可以这样理解对于bj主库而言不管它是主库角色还是备库角色ALL_ROLES它都会自己完成归档动作并将它们归档在本地的/u01/arch下。5.LOG_ARCHIVE_DEST_2 该参数仅当数据库角色为primary时生效指定primary传输redo log到该参数定义的standby database上。 log_archive_dest_2可以说是dataguard上最重要的参数之一它定义了redo log的传输方式(sync or async)以及传输目标(即standby applynode)直接决定了dataguard的数据保护级别。 注意SERVICE值比如bj是tnsnames.ora文件里定义的Oracle Net名称。6.LOG_ARCHIVE_DEST_STATE_n 设置为ENABLE激活log_archive_dest_n定义的属性。7.FAL_SERVER and FAL_CLIENT FAL是Fetch Archive Log的简写它是dataguard主备之间GAP间隙的处理机制。 Primary上不会有GAP所以fal_server和fal_client也是只在standby上生效的参数当然为了switch over的需要同样会在primary端进行预设置。 FAL参数定义的数据库名同样取自本地tnsnames.ora里配置的Oracle Net Service Name. 11g以后 FAL_CLIENT可以忽略。 本例FAL_SERVERtj的意思可以理解为:tj备库产生的归档日志间隙要从主库bj那里获得。(fal_serverprimary database db_unique_name)8.DB_FILE_NAME_CONVERT primary与standby上diskgroup的名称或是数据文件的存放路径不一致的时候需要定义该参数进行转换否则standby apply后无法创建与primary一致的数据文件并报错。1primary上的该参数仅在主备switch over后生效2注意格式应保持一致比如*.db_file_name_convertDG1/db/datafile,DATAGRP/db/datafile/ ”路径少了一个/”将导致standby apply失败。 本例DB_FILE_NAME_CONVERT/u01/oradata/prodstd,/u01/oradata/prod使用的是物理文件路径所以可以省略”/”3primary上执行create tablespace等add datafile操作时无须自定义datafile的全路径名称由数据库自动创建datafile即可。9.LOG_FILE_NAME_CONVERT 同DB_FILE_NAME_CONVERT类似定义主备log文件的存放路径转换。10STANDBY_FILE_MANAGEMENT 设置为auto时主库执行增删数据文件的动作在备库也会增删数据文件缺省是manual 需要在备库做手动增删。4.开始搭建4.1 主库 添加standby log1.查看redo log大小 SQLselectbytes/1024/1024 M fromv$log;M ----------2002002002.standby log要和 redolog 大小一致否则后面mrp0进程状态一致waitapply状态 alter databaseaddstandby logfile group4/u01/app/oracle/oradata/std1.logsize 200m;alter databaseaddstandby logfile group5/u01/app/oracle/oradata/std2.logsize 200m;alter databaseaddstandby logfile group6/u01/app/oracle/oradata/std3.logsize 200m;alter databaseaddstandby logfile group7/u01/app/oracle/oradata/std4.logsize 200m;3.若添加失败删除重新添加 ALTER DATABASE DROP STANDBY LOGFILE GROUP4;ALTER DATABASE DROP STANDBY LOGFILE GROUP5;ALTER DATABASE DROP STANDBY LOGFILE GROUP6;ALTER DATABASE DROP STANDBY LOGFILE GROUP7;4.若添加standby log成功查询 standby log大小及状态 SELECTv$logfile.member,v$logfile.group#,v$standby_log.status,v$standby_log.ARCHIVED,v$standby_log.bytes /1024/1024M,v$standby_log.thread#FROMv$standby_log,v$logfileWHEREv$standby_log.group# v$logfile.group#ORDER BYv$standby_log.thread#, v$logfile.group#;4.2启动备库到nomount状态开始复制[oracleora1 ~]$ rman target sys/oracleorcl auxiliary sys/oraclestd_orcl Recovery Manager: Release19.0.0.0.0 - Production on Sat Jul2215:40:302023Version19.3.0.0.0 Copyright(c)1982,2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL(DBID1667419081)connected to auxiliary database: ORCL(started)RMANduplicate target databaseforstandby from active database;5.查看备库状态5.1复制 进程控制1.复制完成备库处于mount状态 可启动至open2启动mrp0进程 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROMSESSION;ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;5.2查看复制状态SQLselectprocess,status fromv$managed_standby;PROCESS STATUS --------- ------------ DGRD ALLOCATED ARCH CLOSING DGRD ALLOCATED ARCH CLOSING ARCH CLOSING ARCH CLOSING MRP0 APPLYING_LOG RFS IDLE RFS IDLE RFS IDLE RFS IDLE SQLSELECT ROLE, THREAD#, SEQUENCE#, ACTION FROM V$DATAGUARD_PROCESS;ROLE THREAD# SEQUENCE# ACTION------------------------ ---------- ---------- ------------ log writer00IDLE redo transport monitor00IDLE gap manager00IDLE redo transport timer00IDLE archivelocal00IDLE archive redo00IDLE archive redo00IDLE archive redo00IDLE broker instance slave00IDLE broker monitor00IDLE managed recovery00IDLE recovery logmerger1192APPLYING_LOG recovery apply slave00IDLE recovery apply slave00IDLE RFS archive00IDLE RFS async1192IDLE RFSping1192IDLE RFS archive00IDLE RFS archive00IDLE19rows selected.创建同步pdbcreate pluggable database pdbprod3 admin user pdbadmin identified by oracle;6.排错如状态异常可直接用以下 命令在sqlplus中修改配置 alter systemsetLOG_ARCHIVE_CONFIGDG_CONFIG(std_orcl,orcl);alter systemsetlog_archive_dest_2serviceorcl async VALID_FOR(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAMEorcl;6.1查看备库归档日志gap状态select* fromv$archive_gap;select* from gv$archive_gap;--查看备库归档应用情况setlinesize600pagesize100selectthread#,max(sequence#) from v$log_history group by thread#;---备库上查看同步过来的归档日志的应用情况 col namefora80selectname,SEQUENCE#,APPLIED from v$archived_log order by sequence#;