在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"
