SQL> @pd dg_broker_service Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION ---------- ----- -------------------------------------------------------- ------------------------------ --------------------------------------------------------------------------------------------------- 775 307 __dg_broker_service_names ora12c_DGB, ora12c_CFG service names for broker use
far sync备库的好处主要在于它可以处于最大可用模式下作为主库的一个本地归档日志库，而其后的物理或者逻辑备库可以放在较远的其他地方。当之前的主备库距离较远时，可以先通过同步的方式将日志快速传到far sync备库，而后从far sync备库将日志通过异步的方式传到物理备库，这样既兼顾了效率同时也保证了安全。
DGMGRL> connect sys/oracle Connected to "ora12c" Connected as SYSDG. DGMGRL> add FAR_SYNC ora12c_fs as connect identifier is ora12c_fs; far sync instance "ora12c_fs" added
在Rman中执行duplicate database for standby from active database命令时，只会在备库创建一个单实例，你需要手动设置rac参数包括INSTANCE_NUMBER和INSTANCE_NAME等，在另一个主机上启用第二个实例，然后添加新创建的数据库到OCR中
PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- --------------------------------------------------------------------------- log_archive_dest_1 string
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH;
# 注意参数大小写敏感: ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,stbrac)' SCOPE=BOTH SID='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=stbrac ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbrac' SCOPE=BOTH SID='*';
alter system set log_archive_dest_state_1 = ENABLE SCOPE=BOTH SID='*'; alter system set log_archive_dest_state_2 = ENABLE SCOPE=BOTH SID='*';
# 应该返回EXCLUSIVE show parameter REMOTE_LOGIN_PASSWORDFILE
# FAL = fetch archive log ALTER SYSTEM SET FAL_SERVER='stbrac' SCOPE=BOTH SID='*'; ALTER SYSTEM SET FAL_CLIENT='rac' SCOPE=BOTH SID='*';
# 如果主备库文件目录一致，则不用设置 show parameter DB_FILE_NAME_CONVERT show parameter STANDBY_FILE_MANAGEMENT ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH SID='*';
# 设置ARCn进程最大值 ( default 4): ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8 SCOPE=BOTH SID='*';
主库开启数据库闪回
1 2
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET =2880 scope=BOTH; ALTER DATABASE FLASHBACK ON;
run { allocate channel c1 type disk; allocate channel c2 type disk; allocate auxiliary channel stbc1 type disk; DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE set 'db_unique_name'='stbrac' set instance_name='stbrac1' set instance_number='1' set db_create_online_log_dest_1='+FRA' set db_create_online_log_dest_2='+DATA' set db_recovery_file_dest='+FRA' set audit_file_dest='/u01/app/oracle/admin/stbrac/adump' set core_dump_dest='/u01/app/oracle/admin/stbrac/cdump' nofilenamecheck dorecover; }
set linesize 150 set pagesize 100 select 'alter database drop standby logfile member ''' || member || ''';' from v$logfile where type='STANDBY' and member like '+DATA%';
'ALTERDATABASEDROPSTANDBYLOGFILEMEMBER'''||MEMBER||''';' ------------------------------------------------------------------------------------------------------------------------------------------------------ alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_5.278.1005926909'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_6.277.1005926915'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_7.276.1005926921'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_8.275.1005926925'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_9.274.1005926931'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_10.273.1005926937'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_11.272.1005926943'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_12.271.1005926947'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_13.314.1005926953'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_14.315.1005926959';
-- 确认删除完毕 sys@STBRAC1> SELECT GROUP#, MEMBER FROM V$LOGFILE WHERE TYPE='STANDBY' ORDER BY 1,2;
ALTER SYSTEM SET FAL_SERVER=rac SCOPE=BOTH SID='*'; ALTER SYSTEM SET FAL_CLIENT=stbrac SCOPE=BOTH SID='*'; ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,stbrac)' SID='*'; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=rac ASYNC DB_UNIQUE_NAME=rac VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' SCOPE=BOTH SID='*';
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/STBRAC/PARAMETERFILE/spfilestbrac.ora
修改spfile中的INSTANCE_NAME和INSTANCE_NUMBER参数
1 2 3 4
ALTER SYSTEM SET INSTANCE_NUMBER=1 SCOPE=SPFILE SID='stbrac1'; ALTER SYSTEM SET INSTANCE_NUMBER=2 SCOPE=SPFILE SID='stbrac2'; ALTER SYSTEM SET INSTANCE_NAME='stbrac1' SCOPE=SPFILE SID='stbrac1'; ALTER SYSTEM SET INSTANCE_NAME='stbrac2' SCOPE=SPFILE SID='stbrac2';
alter database recover managed standby database using current logfile disconnect from session;
检查每个节点的dg配置情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
select inst_id, database_role, db_unique_name instance, open_mode, protection_mode, protection_level from gv$database;
INST_ID DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL ---------- ---------------- ------------------------------ -------------------- -------------------- -------------------- 1 PHYSICAL STANDBY stbrac READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 2 PHYSICAL STANDBY stbrac READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
select inst_id, database_mode, recovery_mode, protection_mode from gv$archive_dest_status where dest_name ='LOG_ARCHIVE_DEST_1';
INST_ID DATABASE_MODE RECOVERY_MODE PROTECTION_MODE ---------- --------------- ----------------------- -------------------- 2 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 1 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE
检查redo传输服务
1 2 3 4 5 6 7 8 9 10 11
alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss'; select inst_id, sequence#, applied, first_time, next_time from gv$archived_log order by 2,1,4;
-- 主库建表 create table test as select * from dba_tables where 1=2;
set linesize 300 col start_time format a20 col item format a20 select to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, item , sofar, units from v$recovery_progress where item in ('Active Apply Rate', 'Average Apply Rate', 'Redo Applied');
START_TIME ITEM SOFAR UNITS -------------------- -------------------- ---------- -------------------------------- 2019-04-18 17:26:33 Active Apply Rate 1148 KB/sec 2019-04-18 17:26:33 Average Apply Rate 58 KB/sec 2019-04-18 17:26:33 Redo Applied 22 Megabytes
col name for a13 col value for a13 col unit for a30 set lines 132 select name, value, unit, time_computed from v$dataguard_stats where name in ('transport lag', 'apply lag');
NAME VALUE UNIT TIME_COMPUTED ------------- ------------- ------------------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval 04/18/2019 17:33:57 apply lag +00 00:00:00 day(2) to second(0) interval 04/18/2019 17:33:57
优化配置
配置主库归档日志删除策略
1 2
rman target / CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
启用备库的闪回
1 2 3 4
alter system set db_flashback_retention_target =2880; alter database recover managed standby database cancel; alter database flashback on; alter database recover managed standby database using current logfile disconnect from session;
配置dg broker
查看系统现有dg broker配置
1 2 3 4 5 6 7
sys@RAC1> show parameter dg_broker
PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- ------------------------------------------------------------------------------------- dg_broker_config_file1 string /u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr1rac.dat dg_broker_config_file2 string /u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr2rac.dat dg_broker_start boolean FALSE
rac环境中dgbroker的配置文件要存放在共享目录上，在ASM上新建一个目录
1 2 3
su - grid ASMCMD>cd +data/rac/ ASMCMD>mkdir DGCONFIG
停掉备库apply
1
alter database recover managed standby database cancel;
修改主库参数
1 2 3 4
alter system set LOG_ARCHIVE_DEST_2='' scope=both; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/RAC/DGCONFIG/dr1rac.dat' SCOPE=spfile sid='*'; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/RAC/DGCONFIG/dr2rac.dat' SCOPE=spfile sid='*'; alter system set dg_broker_start=true scope=both sid='*';
同时也修改备库
1 2 3 4 5 6 7 8
su - grid ASMCMD>cd +data/stbrac/ ASMCMD>mkdir DGCONFIG
alter system set LOG_ARCHIVE_DEST_2='' scope=both; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/STBRAC/DGCONFIG/dr1stbrac.dat' SCOPE=spfile sid='*'; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/STBRAC/DGCONFIG/dr2stbrac.dat' SCOPE=spfile sid='*'; alter system set dg_broker_start=true scope=both sid='*';
DGMGRL> switchover to 'stbrac'; Performing switchover NOW, please wait... Operation requires a connection to database "stbrac" Connecting ... Connected to "stbrac" Connected as SYSDBA. New primary database "stbrac" is opening... Oracle Clusterware is restarting database "rac" ... Connected to "rac" Connected to "rac" Switchover succeeded, new primary is "stbrac" DGMGRL> show configuration;
--重新swithover回来 DGMGRL> switchover to 'rac'; Performing switchover NOW, please wait... Operation requires a connection to database "rac" Connecting ... Connected to "rac" Connected as SYSDBA. New primary database "rac" is opening... Oracle Clusterware is restarting database "stbrac" ... Switchover succeeded, new primary is "rac"
早上来检查告警邮件，一套10.2.0.5的dg主库一直在报ora-16086错误，根据错误提示大概是备库没有可用的standby log files。在11g以后，这个错误名称换成了ORA-16086 Redo data cannot be written to the standby redo log
1 2 3 4 5
16086, 0000, "standby database does not contain available standby log files" // *Cause: The primary database is in "no data loss" mode, but the standby // database does not contain any "standby log files". // *Action: Add one or more standby log files to the standby database. // This can be done while the standby database is mounted.
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL -------------------- -------------------- MAXIMUM AVAILABILITY RESYNCHRONIZATION
而主备库的standby log file均存在，且大小一致，状态正常
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------------------------------------- --- 1 ONLINE /u01/app/oracle/oradata/test/redo01.log NO 2 ONLINE /u01/app/oracle/oradata/test/redo02.log NO 3 ONLINE /u01/app/oracle/oradata/test/redo03.log NO 4 ONLINE /u01/app/oracle/oradata/test/redo04.log NO 5 ONLINE /u01/app/oracle/oradata/test/redo05.log NO 6 ONLINE /u01/app/oracle/oradata/test/redo06.log NO 7 STANDBY /u01/app/oracle/oradata/test/redo07.log NO 8 STANDBY /u01/app/oracle/oradata/test/redo08.log NO 9 STANDBY /u01/app/oracle/oradata/test/redo09.log NO 10 STANDBY /u01/app/oracle/oradata/test/redo10.log NO 11 STANDBY /u01/app/oracle/oradata/test/redo11.log NO 12 STANDBY /u01/app/oracle/oradata/test/redo12.log NO 13 STANDBY /u01/app/oracle/oradata/test/redo13.log NO
MOS上有说可能是因为fra目录满导致的bug，但检查数据库并没有启用fra
1 2 3 4 5 6 7
SQL> show parameter recovery
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 recovery_parallelism integer 0
尝试重建standby log file，主备库均删除重建
1 2 3 4 5 6 7 8 9 10 11 12 13
alter database drop standby logfile group 7; alter database drop standby logfile group 8; alter database drop standby logfile group 9; alter database drop standby logfile group 10; alter database drop standby logfile group 11; alter database drop standby logfile group 12; alter database drop standby logfile group 13;
select 'alter database add standby logfile group '||group#||' '''||member||''' size 1g reuse;' from v$logfile where type='STANDBY';
recover managed standby database cancel;
recover managed standby database using current logfile disconnect from session;
