12C单实例DG

在测试服务器上搭建了DG，记录下。

OS：RHEL7.4

DB：ORACLE 12.2.0.1

1、归档设置

--干净的关闭数据库
SQL> shutdown immediate
--以mount模式启动
SQL> startup mount
--切换到归档模式
SQL> alter database archivelog;
--开启强制日志
SQL> alter database force logging;
--打开数据库
SQL> alter database open;
--查看归档
SQL> archive log list;
--查看是否为强制日志
SQL> select force_logging from v$database;

2、创建主库standby log
alter database add standby logfile thread 1 group 9 '/u01/app/oradata/SCPRD/SCPRD_standbyredo01.log' size 50m;
alter database add standby logfile thread 1 group 10 '/u01/app/oradata/SCPRD/SCPRD_standbyredo02.log' size 50m;
alter database add standby logfile thread 1 group 11 '/u01/app/oradata/SCPRD/SCPRD_standbyredo03.log' size 50m;
alter database add standby logfile thread 1 group 12 '/u01/app/oradata/SCPRD/SCPRD_standbyredo04.log' size 50m;
alter database add standby logfile thread 1 group 13 '/u01/app/oradata/SCPRD/SCPRD_standbyredo05.log' size 50m;
alter database add standby logfile thread 1 group 14 '/u01/app/oradata/SCPRD/SCPRD_standbyredo06.log' size 50m;
alter database add standby logfile thread 1 group 15 '/u01/app/oradata/SCPRD/SCPRD_standbyredo07.log' size 50m;
alter database add standby logfile thread 1 group 16 '/u01/app/oradata/SCPRD/SCPRD_standbyredo08.log' size 50m;
alter database add standby logfile thread 1 group 17 '/u01/app/oradata/SCPRD/SCPRD_standbyredo09.log' size 50m;

3、主库参数的设定
alter system set log_archive_config='DG_CONFIG=(SCPRDDG,SCPRD)' scope=spfile ;
alter system set log_archive_dest_1='location=/u01/app/oradata/SCPRD/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SCPRD' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=SCPRDDG SYNC lgwr REOPEN=15 NET_TIMEOUT=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SCPRDDG COMPRESSION=ENABLE';

alter system set fal_client='SCPRD' scope=both; --------> 本地service_name
alter system set fal_server='SCPRDDG' scope=both; --------> 远端service_name
alter system set standby_file_management=AUTO scope=both;

4、主库密码文件、参数文件复制到备库
scp orapwSCPRD oracle@dataguard:/u01/app/oracle/product/12.2.0/dbhome_1/dbs
scp initSCPRD.ora oracle@dataguard:/u01/app/oracle/product/12.2.0/dbhome_1/dbs

5、备库参数文件修改
*.db_unique_name=SCPRDDG
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(SCPRDDG,SCPRD)'
*.log_archive_dest_1='location=/u01/app/oradata/SCPRD/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SCPRDDG'
*.LOG_ARCHIVE_DEST_2='SERVICE=SCPRD LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SCPRD COMPRESSION=ENABLE'
*.fal_client='SCPRDDG'
*.FAL_SERVER='SCPRD'
*.standby_file_management=AUTO
*.log_archive_format='scprd_%t_%s_%r.arc'
*.remote_login_passwordfile='EXCLUSIVE'
*.PARALLEL_EXECUTION_MESSAGE_SIZE=8192

6、网络配置
监听文件（listener.ora)
主库：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SCPRD)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = SCPRD)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wmsapp11)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

重启主库监听
Services Summary...
Service "SCPRD" has 1 instance(s).
Instance "SCPRD", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
status UNKNOWN表示静态监听

备库
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dataguard)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SCPRDDG)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = SCPRDDG)
)
)
重启备库监听
Services Summary...
Service "SCPRD" has 1 instance(s).
Instance "SCPRDDG", status UNKNOWN,, has 1 handler(s) for this service...
The command completed successfully
status UNKNOWN表示静态监听

主备库tnsnames.ora
SCPRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wmsapp11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SCPRD)
)
)

SCPRDDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dataguard)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SCPRDDG)
)
)

测试上述两个网络别名是否可用
tnsping SCPRD
tnsping SCPRDDG
看上述命令是否会报错，网络连通是必要条件。

7、主库到备库的数据初始化
创建备库的数据库文件目录
mkdir /u01/app/oradata/SCPRDDG
备库启动到nomount
[oracle@dataguard admin]$ echo $ORACLE_SID
SCPRDDG
[oracle@dataguard admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 14 17:52:19 2020

Connected to an idle instance.

SYS@SCPRDDG>startup nomount;

执行以下命令连接主备库，在主库或备库执行均可，本文是在备库执行
rman target sys/sceadmin@SCPRD auxiliary sys/sceadmin@SCPRDDG

duplicate target database for standby from active database nofilenamecheck;

出现报错
RMAN-05535 warning:All redo log files were not defined properly.
ORACLE error from auxiliary database:ORA-19527: physical standby redo log must be renamed
查看在备库的日志目录没有redo文件，
ORA-19527 物理备用重做日志必须重命名。
备库pfile增加参数
*.log_file_name_convert='/u01/app/oradata/SCPRD','/u01/app/oradata/SCPRDDG'
*.db_file_name_convert='/u01/app/oradata/SCPRD','/u01/app/oradata/SCPRDDG'
主库增加参数
alter system set db_file_name_convert='/u01/app/oradata/SCPRDDG','/u01/app/oradata/SCPRD' scope=spfile;
alter system set log_file_name_convert='/u01/app/oradata/SCPRDDG','/u01/app/oradata/SCPRD' scope=spfile;

再次执行复制主库命令成功。如果备库DG参数不是在参数文件中配置的，可在run块中设置。run块格式如下：
duplicate命令后面加上了spfile,如果复制中出现错误，需要删除spfile再重新执行。
run
{
allocate channel prmy1 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database nofilenamecheck spfile
set db_unique_name='adgorcl'
set db_file_name_convert='/u01/app/oradata/SCPRD','/u01/app/oradata/SCPRDDG'
set log_file_name_convert='/u01/app/oradata/SCPRD','/u01/app/oradata/SCPRDDG'
set control_files='/u01/app/oradata/SCPRD/control01.ctl'
set log_archive_max_processes='10'
set fal_client='SCPRDDG'
set fal_server='SCPRD'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(SCPRD,SCPRDDG)'
set log_archive_dest_1='LOCATION=/u01/app/oradata/SCPRD/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=adgorcl'
set log_archive_dest_2='service=orcl LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'
set audit_file_dest='/u01/app/oradata/SCPRDDG/adump'
SET CLUSTER_DATABASE='FALSE'
set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
release channel prmy1;
release channel stby;
}

以下是复制命令日志输出：
RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 15-OCT-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=781 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwSCPRD' auxiliary format
'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwSCPRDDG' ;
}
executing Memory Script

Starting backup at 15-OCT-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
Finished backup at 15-OCT-20

contents of Memory Script:
{
restore clone from service 'SCPRD' standby controlfile;
}
executing Memory Script

Starting restore at 15-OCT-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oradata/SCPRD/ctl1SCPRD.ora
output file name=/u01/app/oradata/SCPRD/ctl2SCPRD.ora
output file name=/u01/app/oradata/SCPRD/ctl3SCPRD.ora
Finished restore at 15-OCT-20

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oradata/SCPRDDG/SCPRD_temp01.dbf";
set newname for tempfile 2 to
"/u01/app/oradata/SCPRDDG/common/sce_common_tmp01.dbf";
set newname for tempfile 3 to
"/u01/app/oradata/SCPRDDG/component/sce_component_tmp01.dbf";
set newname for tempfile 4 to
"/u01/app/oradata/SCPRDDG/wmwhse/wmwhse1_tmp01.dbf";
set newname for tempfile 5 to
"/u01/app/oradata/SCPRDDG/wmwhse/wmwhse2_tmp01.dbf";
set newname for tempfile 6 to
"/u01/app/oradata/SCPRDDG/wmwhse/wmwhse3_tmp01.dbf";
set newname for tempfile 7 to
"/u01/app/oradata/SCPRDDG/wmwhse/wmwhse4_tmp01.dbf";
set newname for tempfile 8 to
"/u01/app/oradata/SCPRDDG/wmwhse/wmwhse5_tmp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oradata/SCPRDDG/SCPRD_system01.dbf";
set newname for datafile 2 to
"/u01/app/oradata/SCPRDDG/SCPRD_sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oradata/SCPRDDG/SCPRD_undo1.dbf";
set newname for datafile 4 to
"/u01/app/oradata/SCPRDDG/SCPRD_users01.dbf";
set newname for datafile 5 to
"/u01/app/oradata/SCPRDDG/SCPRD_tools01.dbf";
set newname for datafile 6 to
"/u01/app/oradata/SCPRDDG/common/sce_common_01.dbf";
set newname for datafile 7 to
"/u01/app/oradata/SCPRDDG/component/sce_component_01.dbf";
set newname for datafile 8 to
"/u01/app/oradata/SCPRDDG/wmwhse/wmwhse1_01.dbf";
set newname for datafile 9 to
"/u01/app/oradata/SCPRDDG/wmwhse/wmwhse2_01.dbf";
set newname for datafile 10 to
"/u01/app/oradata/SCPRDDG/wmwhse/wmwhse3_01.dbf";
set newname for datafile 11 to
"/u01/app/oradata/SCPRDDG/wmwhse/wmwhse4_01.dbf";
set newname for datafile 12 to
"/u01/app/oradata/SCPRDDG/wmwhse/wmwhse5_01.dbf";
restore
from nonsparse from service
'SCPRD' clone database
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oradata/SCPRDDG/SCPRD_temp01.dbf in control file
renamed tempfile 2 to /u01/app/oradata/SCPRDDG/common/sce_common_tmp01.dbf in control file
renamed tempfile 3 to /u01/app/oradata/SCPRDDG/component/sce_component_tmp01.dbf in control file
renamed tempfile 4 to /u01/app/oradata/SCPRDDG/wmwhse/wmwhse1_tmp01.dbf in control file
renamed tempfile 5 to /u01/app/oradata/SCPRDDG/wmwhse/wmwhse2_tmp01.dbf in control file
renamed tempfile 6 to /u01/app/oradata/SCPRDDG/wmwhse/wmwhse3_tmp01.dbf in control file
renamed tempfile 7 to /u01/app/oradata/SCPRDDG/wmwhse/wmwhse4_tmp01.dbf in control file
renamed tempfile 8 to /u01/app/oradata/SCPRDDG/wmwhse/wmwhse5_tmp01.dbf in control file

executing command: SET NEWNAME

Starting restore at 15-OCT-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oradata/SCPRDDG/SCPRD_system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oradata/SCPRDDG/SCPRD_sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oradata/SCPRDDG/SCPRD_undo1.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oradata/SCPRDDG/SCPRD_users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oradata/SCPRDDG/SCPRD_tools01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oradata/SCPRDDG/common/sce_common_01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oradata/SCPRDDG/component/sce_component_01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oradata/SCPRDDG/wmwhse/wmwhse1_01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oradata/SCPRDDG/wmwhse/wmwhse2_01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oradata/SCPRDDG/wmwhse/wmwhse3_01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oradata/SCPRDDG/wmwhse/wmwhse4_01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCPRD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oradata/SCPRDDG/wmwhse/wmwhse5_01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
Finished restore at 15-OCT-20

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/SCPRD_system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/SCPRD_sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/SCPRD_undo1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/SCPRD_users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/SCPRD_tools01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/common/sce_common_01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/component/sce_component_01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/wmwhse/wmwhse1_01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=9 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/wmwhse/wmwhse2_01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=10 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/wmwhse/wmwhse3_01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=11 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/wmwhse/wmwhse4_01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=12 STAMP=1053857031 file name=/u01/app/oradata/SCPRDDG/wmwhse/wmwhse5_01.dbf
Finished Duplicate Db at 15-OCT-20

复制完成后备库是mount状态，创建spfile(如果是通过run做的复制不需要此操作直接open)，然后重启数据库。
create spfile from pfile;
startup; --启动后备库是只读状态
开启日志实时应用
SYS@SCPRDDG>alter database recover managed standby database using current logfile disconnect from session;
SYS@SCPRDDG>select dest_name,error,status,process from v$archive_dest where rownum<3;

DEST_NAME ERROR STATUS PROCESS
------------------------------ ----------------------------------------------------------------- --------- ----------
LOG_ARCHIVE_DEST_1 VALID ARCH
LOG_ARCHIVE_DEST_2 ORA-16009: invalid redo transport destination ERROR LGWR

主库也出现如下错误信息
RFS[39]: Assigned to RFS process (PID:26309)
RFS[39]: Database mount ID mismatch [0x3cab44d6:0x3cab54d1] (1017857238:1017861329)
RFS[39]: Client instance is standby database instead of primary
RFS[39]: Not using real application clusters

出现错误，ora-16009 invalid redo transport destination并没有影响dg体系结构的运行，在备库可以看到归档日志都已生成，而这个错误也是反映在备库的v$archive_dest下，log_archive_dest_2参数不规范引起的，默认不设置valid_for属性oracle认为是VALID_FOR= (ALL_LOGFILES, ALL_ROLES)。我前面将主备库的此参数设置为了(ALL_LOGFILES, ALL_ROLES)。

主备库做如下参数修改后，主备库的报错消失。
alter system set log_archive_dest_2 = 'SERVICE=SCPRD SYNC lgwr REOPEN=15 NET_TIMEOUT=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SCPRD';

alter system set log_archive_dest_2 = 'SERVICE=SCPRDDG SYNC lgwr REOPEN=15 NET_TIMEOUT=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SCPRDDG';

总结：

1、DG相关的8个主备库参数配置正确；

2、主备库listener.ora&tnsnames.ora配置正确，tnsping互通。

这两点完成了，基本DG就OK了。