Oracle 19c搭建物理adg

select force_logging from v$database;


alter database force logging;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=/data/cms/archlog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cms' scope=both;


alter system set LOG_ARCHIVE_DEST_2='SERVICE=cms_stb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cms_stb';


alter system set log_archive_config='dg_config=(cms,cms_stb)';


alter system set fal_server=cms_stb;


alter system set fal_client=cms;


alter system set standby_file_management=auto;


alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;


alter system set DB_FILE_NAME_CONVERT='/data/cms/cms_stb','/data/cms/datafile/CMS' scope=spfile;


alter system set LOG_FILE_NAME_CONVERT='/data/cms/cms_stb','/data/cms/datafile/CMS' scope=spfile;

从Oracle Database 18c开始，引入了以下两个新的nologging子句，它们可以执行非日志记录操作，同时可以使Active Data Guard备用数据库接收到所有数据，


从而防止FORCE 方式生成大量重做日志导致性能下降。






STANDBY NOLOGGING FOR DATA AVAILABILITY模式使批量加载操作通过其自身与备用数据库的连接将加载的数据发送到每个备用数据库。


提交会延迟，直到所有Active Data Guard备用数据库通过 recover 方式将数据应用完成。






SQL> alter database set standby nologging for data availability;






Database altered.






SQL> select log_mode,force_logging from v$database;






LOG_MODE                FORCE_LOGGING






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






NOARCHIVELOG    STANDBY NOLOGGING FOR DATA AVAILABILITY










STANDBY NOLOGGING FOR LOAD PERFORMANCE模式与先前的模式类似，不同之处在于，如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。


在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。






SQL> alter database set standby nologging for load performance;






Database altered.






SQL> select log_mode,force_logging from v$database;






LOG_MODE              FORCE_LOGGING






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






NOARCHIVELOG    STANDBY NOLOGGING FOR LOAD PERFORMANCE

cms_stb =


  (DESCRIPTION =


    (ADDRESS_LIST =


      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.16.19)(PORT = 1521)))


      (CONNECT_DATA =


        (SERVICE_NAME = cms)(UR = A)))






cms =


  (DESCRIPTION =


    (ADDRESS_LIST =


      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.16.18)(PORT = 1521)))


      (CONNECT_DATA =


        (SERVICE_NAME = cms)(UR = A)))


        

静态监听创建     


LISTENER =


  (DESCRIPTION_LIST =


    (DESCRIPTION =


      (ADDRESS = (PROTOCOL = TCP)(HOST = cms-stb)(PORT = 1521))


      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))


    )


  )






ADR_BASE_LISTENER = u01/app/oracle






SID_LIST_listener=


  (SID_LIST=


    (SID_DESC=


      (GLOBAL_DBNAME=cms)


      (SID_NAME=cms)


        (ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1)))

alter database add standby logfile group 11 '/data/cms/datafile/CMS/redo11.log' size 50M;


alter database add standby logfile group 12 '/data/cms/datafile/CMS/redo12.log' size 50M;


alter database add standby logfile group 13 '/data/cms/datafile/CMS/redo13.log' size 50M;


alter database add standby logfile group 14 '/data/cms/datafile/CMS/redo14.log' size 50M;

*.audit_file_dest='/u01/app/oracle/admin/cms/adump'


*.audit_trail='db'


*.compatible='19.0.0'


*.control_files='/data/cms/cms_stb/control01.ctl','/data/cms/cms_stb/control02.ctl'


*.db_block_size=8192


*.db_domain=''


*.db_file_name_convert='/data/cms/datafile/CMS','/data/cms/cms_stb'


*.db_name='cms'


*.db_unique_name='cms_stb'


*.service_names='cms','cms_stb'


*.diagnostic_dest='/u01/app/oracle'


*.dispatchers='(PROTOCOL=TCP) (SERVICE=cmsXDB)'


*.fal_client='cms_stb'


*.fal_server='cms'


*.log_archive_config='dg_config=(cms,cms_stb)'


*.log_archive_dest_1='LOCATION=/data/cms/archlog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cms_stb'


*.log_archive_dest_2='SERVICE=cms LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cms'


*.log_archive_dest_state_1='ENABLE'


*.log_file_name_convert='/data/cms/datafile/CMS','/data/cms/cms_stb'


*.open_cursors=300


*.pga_aggregate_target=6424m


*.processes=1280


*.remote_login_passwordfile='EXCLUSIVE'


*.sessions=1105


*.sga_target=19272m


*.standby_file_management='AUTO'


*.undo_tablespace='UNDOTBS1'

mkdir -p /u01/app/oracle/admin/cms/adump
mkdir -p data/cms/cms_stb/

rman target sys/1234qwer@cms auxiliary sys/1234qwer@cms_stb










run


{


allocate channel prmy1 type disk;


allocate channel prmy2 type disk;


allocate channel prmy3 type disk;


allocate channel prmy4 type disk;


allocate auxiliary channel stby1 type disk;


duplicate target database for standby from active database;


}














启动日志应用


alter database open
alter database recover managed standby database using current logfile disconnect from session;


检查主备库是有延迟
SQL> col name for a50
SQL> set linesize 200
SQL> select name,value from v$dataguard_stats;


NAME                                               VALUE
-------------------------------------------------- ----------------------------------------------------------------
transport lag                                      +00 00:00:00
apply lag                                          +00 00:00:00
apply finish time                                  +00 00:00:00.000
estimated startup time                             24


SQL>