搭建简易19c RAC ADG

-- 前提条件

已搭建好主库用RAC环境和备库RAC环境

主库RAC的节点为p1,p2。主库的db_unique_name为orcl。主库的service_name也为orcl。

备库RAC的节点为s1,s2。备库的db_unique_name为orcls。备库的service_name也为orcls。

把主备库的$ORACLE_HOME/network/admin/tnsnames.ora的连接字符串信息在主备库上相互复制一下。

-- 在主库的任意节点上操作

SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH;
SQL> ALTER SYSTEM SET log_archive_config='dg_config=(orcl,orcls)' sid='*' SCOPE=BOTH ;
SQL> select NAME, PROTECTION_MODE, DATABASE_ROLE, FLASHBACK_ON, DB_UNIQUE_NAME, FORCE_LOGGING, OPEN_MODE from v$database;

-- 在备库的任意节点上操作

SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH;
SQL> ALTER SYSTEM SET log_archive_config='dg_config=(orcls,orcl)' sid='*' SCOPE=BOTH ;
SQL> select NAME, PROTECTION_MODE, DATABASE_ROLE, FLASHBACK_ON, DB_UNIQUE_NAME, FORCE_LOGGING, OPEN_MODE from v$database;

-- 在备库的任意节点上操作

SQL> 
set heading off linesize 999 pagesize 0 feedback off trimspool on
spool /tmp/files.lst
select 'asmcmd rm '||name from v$datafile
union all
select 'asmcmd rm '||name from v$tempfile
union all
select 'asmcmd rm '||member from v$logfile;
spool off

$ vim /tmp/files.lst --把多余的行去掉
$ chmod 777 /tmp/files.lst
$ srvctl stop database -db orcls -o immediate
$ su - grid
$ sh /tmp/files.lst

-- 在主库的任意节点上操作

$ srvctl config database -d orcl | grep "Password file" --记录下主库密码文件的路径和名称
$ su - grid
$ asmcmd
ASMCMD> cp <+DATA/ORCL/PASSWORD/pwdorcl.258.1172532597> /tmp --把斜线的部分替换为确认到的主库的密码，要绝对路径
$ chmod 777 </tmp/pwdorcl.258.1172532597>
$ scp </tmp/pwdorcl.258.1172532597> s1:/tmp

-- 在备库的任意节点上操作

$ su - grid
$ asmcmd
ASMCMD> pwcopy --dbuniquename orcls /tmp/pwdorcl.258.1172532597 +data/orcls/password/pwdorcl -f

如果报出以下错误，可以忽略
ASMCMD-9453: failed to register password file as a CRS resource

$ srvctl modify database -db orcls -pwfile '+data/orcls/password/pwdorcl'
$ srvctl config database -d orcls | grep "Password file" --确认主库的密码文件被正确保存
$ su - oracle
$ sqlplus / as sysdba
SQL> startup nomount
SQL> alter system set db_name=orcl scope=spfile;  --必须先变更db_name后，再变更db_unique_name
SQL> alter system set db_unique_name=orcls scope=spfile;
SQL> shutdown abort
$ rman target /
RMAN> startup nomount
RMAN> restore standby controlfile from service 'orcl'; --12.2以后支持此方式创建备库
RMAN> alter database mount;
RMAN> restore database from service 'orcl' section size 64G;
RMAN> shutdown immediate
$ srvctl start database -db orcls -o mount
$ srvctl status database -db orcls
$ sqlplus / as sysdba

SQL>
set pagesize 0 feedback off linesize 120 trimspool on
spool /tmp/clearlogs.sql
select distinct 'alter database clear logfile group '||group#||';' from v$logfile; 
spool off

SQL> !vim /tmp/clearlogs.sql
$ sqlplus / as sysdba
SQL> set line 1000 pages 999
SQL> @/tmp/clearlogs.sql
SQL>
col MEMBER format a60
SELECT b.THREAD#, b.MEMBERS, a.GROUP#, b.SEQUENCE#, a.MEMBER, b.bytes/1024/1024 as "Bytes(MB)", a.TYPE, b.STATUS, b.ARCHIVED 
FROM v$logfile a, v$log b
WHERE a.GROUP#=b.GROUP# ORDER BY GROUP#;

SQL>
-- thread 1
alter database add standby logfile thread 1 group 11 '+DATA/ORCLS/ONLINELOG/standby_group_11' SIZE 200M;
alter database add standby logfile thread 1 group 12 '+DATA/ORCLS/ONLINELOG/standby_group_12' SIZE 200M;
alter database add standby logfile thread 1 group 13 '+DATA/ORCLS/ONLINELOG/standby_group_13' SIZE 200M;
alter database add standby logfile thread 1 group 14 '+DATA/ORCLS/ONLINELOG/standby_group_14' SIZE 200M;
alter database add standby logfile thread 1 group 15 '+DATA/ORCLS/ONLINELOG/standby_group_15' SIZE 200M;

-- thread 2
alter database add standby logfile thread 2 group 21 '+DATA/ORCLS/ONLINELOG/standby_group_21' SIZE 200M;
alter database add standby logfile thread 2 group 22 '+DATA/ORCLS/ONLINELOG/standby_group_22' SIZE 200M;
alter database add standby logfile thread 2 group 23 '+DATA/ORCLS/ONLINELOG/standby_group_23' SIZE 200M;
alter database add standby logfile thread 2 group 24 '+DATA/ORCLS/ONLINELOG/standby_group_24' SIZE 200M;
alter database add standby logfile thread 2 group 25 '+DATA/ORCLS/ONLINELOG/standby_group_25' SIZE 200M;

SQL> 
col MEMBER format a60
SELECT b.THREAD#, a.GROUP#, a.MEMBER, b.bytes/1024/1024 as "Bytes(MB)", a.TYPE, b.STATUS 
FROM v$logfile a, V$STANDBY_LOG b WHERE a.GROUP# = b.GROUP#
union
SELECT b.THREAD#, a.GROUP#, a.MEMBER, b.bytes/1024/1024 as "Bytes(MB)", a.TYPE, b.STATUS 
FROM v$logfile a, v$log b WHERE a.GROUP# = b.GROUP# ORDER BY GROUP#;

-- 在主库任意节点上操作

$ su - oracle
$ sqlplus / as sysdba
SQL> set line 1000 pages 999

SQL>
-- thread 1
alter database add standby logfile thread 1 group 11 '+DATA/ORCL/ONLINELOG/standby_group_11' SIZE 200M;
alter database add standby logfile thread 1 group 12 '+DATA/ORCL/ONLINELOG/standby_group_12' SIZE 200M;
alter database add standby logfile thread 1 group 13 '+DATA/ORCL/ONLINELOG/standby_group_13' SIZE 200M;
alter database add standby logfile thread 1 group 14 '+DATA/ORCL/ONLINELOG/standby_group_14' SIZE 200M;
alter database add standby logfile thread 1 group 15 '+DATA/ORCL/ONLINELOG/standby_group_15' SIZE 200M;

-- thread 2
alter database add standby logfile thread 2 group 21 '+DATA/ORCL/ONLINELOG/standby_group_21' SIZE 200M;
alter database add standby logfile thread 2 group 22 '+DATA/ORCL/ONLINELOG/standby_group_22' SIZE 200M;
alter database add standby logfile thread 2 group 23 '+DATA/ORCL/ONLINELOG/standby_group_23' SIZE 200M;
alter database add standby logfile thread 2 group 24 '+DATA/ORCL/ONLINELOG/standby_group_24' SIZE 200M;
alter database add standby logfile thread 2 group 25 '+DATA/ORCL/ONLINELOG/standby_group_25' SIZE 200M;

SQL>
col MEMBER format a60
SELECT b.THREAD#, a.GROUP#, a.MEMBER, b.bytes/1024/1024 as "Bytes(MB)", a.TYPE, b.STATUS 
FROM v$logfile a, V$STANDBY_LOG b WHERE a.GROUP# = b.GROUP#
union
SELECT b.THREAD#, a.GROUP#, a.MEMBER, b.bytes/1024/1024 as "Bytes(MB)", a.TYPE, b.STATUS 
FROM v$logfile a, v$log b WHERE a.GROUP# = b.GROUP# ORDER BY GROUP#;

-- 在主库任意节点上操作

SQL>
alter system set log_archive_config='DG_CONFIG=(ORCL, ORCLS)' scope=both;
alter system set log_archive_dest_2='SERVICE=ORCLS LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLS' scope=both;
alter system set fal_server='ORCLS' scope=both ;
alter system set fal_client='ORCL' scope=both;

-- 在备库的任意节点上操作

SQL>
alter system set log_archive_config='DG_CONFIG=(ORCLS, ORCL)' scope=both;
alter system set log_archive_dest_2='SERVICE=ORCL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL' scope=both;
alter system set fal_server='ORCL' scope=both ;
alter system set fal_client='ORCLS' scope=both;

-- 在备库的任意节点上启动，停止或确认MRP

--启动MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

--停止MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

--确认MRP的状态
select PROCESS,PID,STATUS,THREAD#,SEQUENCE# from V$MANAGED_STANDBY where PROCESS='MRP0';

-- 在主备库上确认是否同步了最新的归档日志

select   max(sequence#), thread# 
from     v$archived_log 
where    resetlogs_change# = (select max(resetlogs_change#) from v$archived_log) 
group by thread#;

以上就是简易的RAC ADG的搭建方法