Oracle 18c使用dbca创建级联DG
--------配置tns
CDBLHR18cdg2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBLHR18cdg2)
)
)
--------配置监听
(SID_DESC =
(GLOBAL_DBNAME = CDBLHR18cdg2)
(ORACLE_HOME = /u08/app/oracle/product/18.0.0/dbhome_1)
(SID_NAME = CDBLHR18cdg2)
)
(SID_DESC =
(GLOBAL_DBNAME = CDBLHR18cdg2_DGMGRL)
(ORACLE_HOME = /u08/app/oracle/product/18.0.0/dbhome_1)
(SID_NAME = CDBLHR18cdg2)
)
--搭建过程是：备库和第2备库的操作
dbca -silent -createDuplicateDB \
-gdbName CDBLHR18cdg2 \
-sid CDBLHR18cdg2 \
-sysPassword lhr \
-primaryDBConnectionString 192.168.59.130:1521/CDBLHR18cdg \
-nodelist rhel6lhr \
-databaseConfigType SINGLE \
-createAsStandby -dbUniqueName CDBLHR18cdg2 \
-datafileDestination '/u01/app/oracle/oradata/CDBLHR18cdg2/' \
-initParams db_create_file_dest=/u01/app/oracle/oradata/CDBLHR18cdg2/,db_create_online_log_dest_1=/u01/app/oracle/oradata/CDBLHR18cdg2/,sga_target=800M,memory_max_target=0,memory_target=0
---全参数 rac环境
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_3','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3','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;
--主库修改参数
Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';
alter system set LOG_ARCHIVE_DEST_3= 'SERVICE=CDBLHR18cdg2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDBLHR18cdg2';
--一级备库修改参数
alter system set log_archive_config='dg_config=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';
alter system set LOG_ARCHIVE_DEST_3= 'SERVICE=CDBLHR18cdg2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDBLHR18cdg2';
-- 二级备库修改参数
alter system set log_archive_config='dg_config=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDBLHR18cdg2';
alter system set db_file_name_convert='/u01/app/oracle/oradata/CDBLHR18cdg/','/u01/app/oracle/oradata/CDBLHR18cdg2/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/CDBLHR18cdg/','/u01/app/oracle/oradata/CDBLHR18cdg2/' scope=spfile;
alter system set fal_client='CDBLHR18cdg2';
alter system set fal_server='CDBLHR18cdg';
shutdown immediate
startup
alter system register;
--备库查询实时应用
alter database recover managed standby database cancel;
ALTER DATABASE flashback on;
alter database recover managed standby database using current logfile disconnect;
! ps -ef|grep ora_mrp
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'
AND B.DEST_ID=A.DEST_ID
GROUP BY B.THREAD#)
AND A.STANDBY_DEST='NO'
ORDER BY A.THREAD#, A.SEQUENCE#;
alter system set dg_broker_start=true sid='*';
dgmgrl sys/lhr@CDBLHR18cdg
show configuration
add database CDBLHR18cdg2 as
connect identifier is CDBLHR18cdg2;
show database verbose CDBLHR18c;
show database verbose CDBLHR18cdg;
show database verbose CDBLHR18cdg2;
alter database recover managed standby database cancel;
--必须配置主库
edit database cdblhr18c set property RedoRoutes ='(cdblhr18cdg:cdblhr18cdg2 ASYNC)';
enable database CDBLHR18cdg2
edit database CDBLHR18cdg2 set property StaticConnectIdentifier ='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDBLHR18cdg2)(INSTANCE_NAME=CDBLHR18cdg2)(SERVER=DEDICATED)))';配置完结果：
1 2 3 4 5 6 7 8 9 10 11 | DGMGRL> show configuration Configuration - cdblhr18c Protection Mode: MaxPerformance Members: cdblhr18c - Primary database cdblhr18cdg - Physical standby database cdblhr18cdg2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 49 seconds ago) DGMGRL> |
