Oracle 18c使用dbca创建级联DG
-------------------- dbca搭建级联备库 --------配置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)))';
配置完结果:
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>