oracle19c adg rac to rac 采用createDuplicateDB 搭建备库

-----创建普通数据

dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname p19c03 -responseFile NO_VALUE \
-characterSet ZHS16GBK \
-sysPassword oracle \
-systemPassword oracle \
-createAsContainerDatabase false \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-totalMemory 4086 \
-redoLogFileSize 50 \
-emConfiguration NONE \
-ignorePreReqs \
-nodelist node1,node2 \
-storageType ASM \
-diskGroupName +DATA \
-asmsnmpPassword oracle \
-recoveryAreaDestination NONE \

主、备库配置ADG静态监听
配置主备库静态监听（静态监听配置在grid下面$ORACLE_HOME/network/admin/listener.ora）
备节点1
echo "LISTENER_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.101.165)(PORT = 1521)))
ADR_BASE_LISTENER_DG = /u01/db/oracle
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = p19cdg)
(SID_NAME = p19cdg1)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db)
)
)">$TNS_ADMIN/listener.ora
cat $TNS_ADMIN/listener.ora


备节点2
echo "LISTENER_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.101.166)(PORT = 1521)))
ADR_BASE_LISTENER_DG = /u01/db/oracle
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = p19cdg)
(SID_NAME = p19cdg2)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db)
)
)">$TNS_ADMIN/listener.ora
cat $TNS_ADMIN/listener.ora




主节点1
echo "LISTENER_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.101.160)(PORT = 1521)))
ADR_BASE_LISTENER_DG = /u01/db/oracle
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = p19c)
(SID_NAME = p19c1)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db)
)
)">$TNS_ADMIN/listener.ora
cat $TNS_ADMIN/listener.ora

主节点2
echo "LISTENER_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.101.161)(PORT = 1521)))
ADR_BASE_LISTENER_DG = /u01/db/oracle
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = p19c)
(SID_NAME = p19c2)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db)
)
)">$TNS_ADMIN/listener.ora

cat $TNS_ADMIN/listener.ora


#启动静态监听
su - grid
lsnrctl stop LISTENER_DG
lsnrctl start LISTENER_DG
lsnrctl status LISTENER_DG

3、主、备库配置连接串tnsnames.ora
echo "p19c =
(DESCRIPTION =
(FAILOVER = ON)
(LOAD_BALANCE = YES)
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.101.160)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.101.161)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = p19c)
)
)

p19cdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.101.165)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.101.166)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = p19cdg)
)
)" >$TNS_ADMIN/tnsnames.ora

cat $TNS_ADMIN/tnsnames.ora

#主备库tnsping互相测试
su - oracle
tnsping p19c
tnsping p19cdg




scp -pr /u01/app/oracle/product/19.3.0/db/network/admin/tnsnames.ora /u01/app/19.3.0/grid/network/admin/






cat $TNS_ADMIN/listener.ora




srvctl stop listener -l LISTENER
srvctl start listener -l LISTENER

dbca -silent -createDuplicateDB \
-gdbName p19c \
-sid p19cdg \
-sysPassword oracle \
-primaryDBConnectionString 10.128.101.160:1521/p19c \
-nodelist node3,node4 \
-adminManaged \
-databaseConfigType RAC \
-createAsStandby -dbUniqueName p19cdg \
-datafileDestination '+data' \
-initParams db_create_file_dest=+data, db_create_online_log_dest_1=+data

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '+DATA/P19CDG/orapwp19cdg' ;
}
executing Memory Script
Starting backup at 02-DEC-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=302 instance=p19c1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/02/2022 09:49:01
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/02/2022 09:49:01
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server
RMAN>
echo set on

经排查测试，原因是主库中/etc/hosts中未包含备库的主机名IP信息条目（即使tnsnames.ora中都用的IP，dbca -silent命令里连接串也用的IP）。 这个错误比较有隐蔽性，在主备库的/etc/hosts包括自己主机名条目、tnsnames.ora中使用IP时也会报这错误，查看日志、使用dbca -silent ***event="17627 trace name errorstack level 3"也没有更详细信息。如下测试验证这一点： 测试1：主库未配置/etc/hosts中备库主机信息

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

srvctl stop database -d p19cdg;
srvctl start database -d p19cdg;
srvctl status database -d p19cdg;









set line 1000
set pagesize 1000
col name format a25
col VALUE format a100
SELECT a.NAME,
i.instance_name,
a.VALUE
FROM gv$parameter a, gv$instance i
WHERE a.inst_id = i.inst_id and a.name in ('dg_broker_start','db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_max_processes','remote_login_passwordfile','db_file_name_convert','log_file_name_convert','standby_file_management','fal_server','fal_client','dg_broker_config_file1','dg_broker_config_file2')
ORDER BY a.name, i.instance_name;

---- 主库

--备库

在没有设置log_archive_dest_1之前
db_recovery_file_dest是可以用的，archivelog 都被放到db_recovery_file_dest指定的目录下。当设置了log_archive_dest_1之后，archivelog 被放到log_archive_dest_1下，在switch logfile后，archivelog 不再放置到db_recovery_file_dest下了
alter system 修改 不加scope=spfile 立即生效



--主库修改参数
asmcmd
ASMCMD>
mkdir +FRA/arch1
mkdir +FRA/arch2
mkdir +FRA/flash_back
mkdir +DATA/dgshare/


alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=p19c' sid='*';
alter system set LOG_ARCHIVE_DEST_2='service=p19cdg VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=p19cdg' sid='*';
alter system set log_archive_config='dg_config=(p19c,p19cdg)' sid='*';
alter system set db_file_name_convert='+DATA/p19cdg','+DATA/p19c' scope=spfile sid='*';
alter system set log_file_name_convert='+DATA/p19cdg','+DATA/p19c' scope=spfile sid='*';
alter system set standby_file_management=auto scope=spfile sid='*';
alter system set fal_client='p19c' sid='*';
alter system set fal_server='p19cdg' sid='*';
alter system set dg_broker_start=true sid='*';
alter system set log_archive_dest_1='LOCATION=+FRA/arch1' scope=spfile sid='p19c1';
alter system set log_archive_dest_1='LOCATION=+FRA/arch2' scope=spfile sid='p19c2';




show parameter dg
alter system set dg_broker_config_file1='+DATA/dgshare/dr1p19c.dat' SID='*';
alter system set dg_broker_config_file2='+DATA/dgshare/dr2p19c.dat' SID='*';


alter system set db_recovery_file_dest='';
alter system set db_recovery_file_dest_size=2g scope=both;
alter system set db_recovery_file_dest='+FRA/flash_back';








--备库修改参数
asmcmd
ASMCMD>
mkdir +FRA/arch1
mkdir +FRA/arch2
mkdir +FRA/flash_back
mkdir +DATA/dgshare/


alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=p19cdg' scope=spfile sid='*';
alter system set log_archive_config='dg_config=(p19c,p19cdg)';
alter system set db_file_name_convert='+DATA/p19c','+DATA/p19cdg' sid='*';
alter system set log_file_name_convert='+DATA/p19c','+DATA/p19cdg' sid='*';
alter system set standby_file_management=auto scope=spfile sid='*';
alter system set fal_client='p19cdg' sid='*';
alter system set fal_server='p19c' sid='*';
alter system set log_archive_dest_1='LOCATION=+FRA/arch1' scope=spfile sid='p19cdg1';
alter system set log_archive_dest_1='LOCATION=+FRA/arch2' scope=spfile sid='p19cdg2';


--备库
--创建路径
ASMCMD> mkdir +DATA/dgshare/

show parameter dg
alter system set dg_broker_config_file1='+DATA/dgshare/dr1p19cdg.dat' SID='*';
alter system set dg_broker_config_file2='+DATA/dgshare/dr2p19cdg.dat' SID='*';
alter system set dg_broker_start=true sid='*';


alter system set log_archive_dest_1='LOCATION=+FRA/arch1' sid='p19c1';
alter system set log_archive_dest_1='LOCATION=+FRA/arch2' sid='p19c2';


alter system set db_recovery_file_dest_size=2g scope=both;
alter system set db_recovery_file_dest='+FRA/flash_back' scope=both;

alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect;
! ps -ef|grep ora_mrp




select INST_ID, dbid,name,DB_UNIQUE_NAME,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

COL NAME FOR A100
SET LINESIZE 9999 PAGESIZE 9999
COL NEXT_CHANGE# FOR 999999999999999
SELECT THREAD#, NAME, SEQUENCE#, ARCHIVED, APPLIED, A.NEXT_CHANGE#
FROM V$ARCHIVED_LOG A
WHERE A.SEQUENCE# >= (SELECT MAX(B.SEQUENCE#) - 3
FROM V$ARCHIVED_LOG B
WHERE B.THREAD# = A.THREAD#
AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
AND B.RESETLOGS_CHANGE# =
(SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)
AND B.APPLIED = 'YES'
GROUP BY B.THREAD#)
ORDER BY A.THREAD#, A.SEQUENCE#;




! ps -ef|grep ora_mrp


dgmgrl sys/oracle@p19c
show configuration




create configuration p19c as
primary database is p19c
connect identifier is p19c;


add database p19cdg as
connect identifier is p19cdg
maintained as physical;

enable configuration

show database verbose p19c;
show database verbose p19cdg;





alter database recover managed standby database cancel;

alter database flashback on;
select flashback_on,force_logging from v$database;
alter database recover managed standby database using current logfile disconnect;

edit database p19c set property 'FastStartFailoverTarget'='p19cdg';
edit database p19cdg set property 'FastStartFailoverTarget'='p19c';