[oracle@XAG191 ~]$ cat /etc/hosts#add192.168.40.191 XAG191 XAG191.COM192.168.40.192 XAG192 XAG192.COM[oracle@XAG192 ~]$ cat /etc/hosts#add192.168.40.191 XAG191 XAG191.COM192.168.40.192 XAG192 XAG192.COM[oracle@XAG191 ~]$ sqlplus / as sysdba#查看主库归档和附加日志配置，如下的输出显示，开启了归档模式但没有开启附加日志；SQL> select log_mode,force_logging from v$database;LOG_MODE FORCE_LOGGING------------ ---------------------------------------ARCHIVELOG NO#查看主库归档目的地，从下面结果中可以看到，归档目的地使用了快速恢复区；SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 5Next log sequence to archive 7Current log sequence 7#开启主库附加日志，并验证开启的结果；SQL> alter database force logging;SQL> select log_mode,force_logging from v$database;LOG_MODE FORCE_LOGGING------------ ---------------------------------------ARCHIVELOG YES从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;SQL> select log_mode,force_logging from v$database;LOG_MODE FORCE_LOGGING---------- ----- ----- ----- -- --------------------------------------- ----- ----- ----- ----- ----- -----NOARCHIVELOG STANDBY NOLOGGING FOR DATA AVAILABILITYSTANDBY NOLOGGING FOR LOAD PERFORMANCE模式与先前的模式类似，不同之处在于，如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。SQL> alter database set standby nologging for load performance;SQL> select log_mode,force_logging from v$database;LOG_MODE FORCE_LOGGING------- ------- ------- ----- --------------------------------------- ----- ----- ----- ----- ----- ----- -----NOARCHIVELOG STANDBY NOLOGGING FOR LOAD PERFORMANCESQL> archive log list;SQL> shutdown immediate （如已启动归档则无需下面的设置）SQL> startup mountSQL> alter database archivelog;SQL> archive log list;SQL> show parameter recoveryNAME TYPE VALUE------------------------------------ ---------------------------------db_recovery_file_dest string /u03/fradb_recovery_file_dest_size big integer 500Grecovery_parallelism integer 0remote_recovery_file_dest string#安装网络带宽测试工具（两台都要安装此工具）[root@XAG191 src]# wget https://iperf.fr/download/fedora/iperf3-3.1.3-1.fc24.x86_64.rpm[root@XAG191 src]# rpm -i iperf3-3.1.3-1.fc24.x86_64.rpm #暂停防火墙（两台）[root@XAG191 ~]# systemctl stop iptables.service#TCP模式服务端方式启动iperf3[root@XAG191 src]# iperf3 -s#在tcp模式下，客户端到服务器192.168.40.196上传带宽测试，测试时间为60秒[root@XAG192 src]# iperf3 -c 192.168.40.196 -t 60Connecting to host 192.168.40.196, port 5201[ 4] local 192.168.40.192 port 60449 connected to 192.168.40.196 port 5201[ ID] Interval Transfer Bandwidth Retr Cwnd[ 4] 0.00-1.00 sec 267 MBytes 2.24 Gbits/sec 0 1.40 MBytes [ 4] 1.00-2.00 sec 209 MBytes 1.75 Gbits/sec 5 1.29 MBytes ... [ 4] 58.01-59.01 sec 141 MBytes 1.19 Gbits/sec 0 1.45 MBytes [ 4] 59.01-60.00 sec 162 MBytes 1.37 Gbits/sec 56 1.06 MBytes - - - - - - - - - - - - - - - - - - - - - - - - -[ ID] Interval Transfer Bandwidth Retr[ 4] 0.00-60.00 sec 11.0 GBytes 1.57 Gbits/sec 343 sender[ 4] 0.00-60.00 sec 11.0 GBytes 1.57 Gbits/sec receiver#从上面测试得知网络带宽1.5G左右#调整#显示用于读写网络的最小值、默认值、最大值[root@XAG191 src]# sysctl -a...net.ipv4.tcp_rmem = 4096 87380 6291456...net.ipv4.tcp_wmem = 4096 16384 4194304...#测试网路延时（数据包大小=32767）[root@XAG192 src]# ping -c 2 -s 32767 DB196 PING XAG191 (192.168.40.191) 32767(32795) bytes of data.32775 bytes from XAG191 (192.168.40.191): icmp_seq=1 ttl=64 time=0.983 ms32775 bytes from XAG191 (192.168.40.191): icmp_seq=2 ttl=64 time=3.95 ms--- XAG191 ping statistics ---2 packets transmitted, 2 received, 0% packet loss, time 1001msrtt min/avg/max/mdev = 0.983/2.467/3.951/1.484 ms#如上测试得值 延时2.5毫秒#SDU取值 【data guard 的最佳实践 添加最大SDU为32767个字节】#确定套接字缓冲区（BDP）大小BDP=带宽 * 延时 * 3倍带宽=1.5G/s=1.5*1000000000 b/s=1500000000 b/s延时=2.5ms=0.0025 sBDP=1500000000 * 0.0025 * 3 = 11250000 bBDP= 11250000 b/8 =1406250 字节 （比默认是16k=16384字节 大的多）[oracle@XAG191 ~]$ cd $ORACLE_HOME/network/admin[oracle@XAG191 admin]$ cat listener.oraSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SDU=32767) (SID_NAME = MYCDB) (GLOBAL_DBNAME = MYCDB191_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (SEND_BUF_SIZE=1406250) (RECV_BUF_SIZE=1406250) (ADDRESS = (PROTOCOL = TCP)(HOST = XAG191)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )ADR_BASE_LISTENER = /u01/app/oracle[oracle@XAG192 ~]$ cd $ORACLE_HOME/network/admin[oracle@XAG192 admin]$ cat listener.oraSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SDU=32767) (SID_NAME = MYCDB) (GLOBAL_DBNAME = MYCDB192_DGMGRL) (ORACLE_HOME =/u01/app/oracle/product/19.3.0/db_1) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (SEND_BUF_SIZE=1406250) (RECV_BUF_SIZE=1406250) (ADDRESS = (PROTOCOL = TCP)(HOST = XAG192)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )ADR_BASE_LISTENER = /u01/app/oracle[oracle@XAG191 admin]$ cat tnsnames.oraMYCDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XAG191)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYCDB) ) )LISTENER_MYCDB = (ADDRESS = (PROTOCOL = TCP)(HOST = XAG191)(PORT = 1521))PL = (DESCRIPTION = (SDU=32767) (SEND_BUF_SIZE=1406250) (RECV_BUF_SIZE=1406250) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = XAG191)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYCDB) ) )SL = (DESCRIPTION = (SDU=32767) (SEND_BUF_SIZE=1406250) (RECV_BUF_SIZE=1406250) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = XAG192)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYCDB) ) )[oracle@XAG192 admin]$ cat tnsnames.oraMYCDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XAG192)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYCDB) ) )LISTENER_MYCDB = (ADDRESS = (PROTOCOL = TCP)(HOST = XAG192)(PORT = 1521))PL = (DESCRIPTION = (SDU=32767) (SEND_BUF_SIZE=1406250) (RECV_BUF_SIZE=1406250) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = XAG191)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYCDB) ) )SL = (DESCRIPTION = (SDU=32767) (SEND_BUF_SIZE=1406250) (RECV_BUF_SIZE=1406250) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = XAG192)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYCDB) ) )[oracle@XAG191 admin]$ cd $ORACLE_HOME/dbs[oracle@XAG191 dbs]$ scp orapwMYCDB oracle@192.168.40.192:$ORACLE_HOME/dbs#连接自身的tnsname里面的服务名SQL> alter system set job_queue_processes=10 scope=spfile;SQL> alter system set db_unique_name='MYCDB191' scope=spfile;SQL> alter system set local_listener='PL' scope=spfile;SQL> alter system set global_names=true scope=both;SQL> ALTER DATABASE RENAME GLOBAL_NAME TO MYCDB;--db_file_name_convert ：主库数据文件路径，备库数据文件路径SQL> alter system set db_file_name_convert='/u02/oradata/MYCDB/','/u02/oradata/MYCDB/' scope=spfile;--log_file_name_convert：主库的日志文件路径，备库的日志文件路径SQL> alter system set log_file_name_convert='/u02/oradata/MYCDB/','/u02/oradata/MYCDB/' scope=spfile;--配置tnsnames.ora网络服务名，fal_server拷贝丢失的归档文件到这里）SQL> alter system set fal_client='PL' scope=spfile;--（配置tnsnames.ora网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件）SQL> alter system set fal_server='SL' scope=spfile;--启动db接受或发送redo data，包括所有库的db_unique_nameSQL> alter system set log_archive_config = 'DG_CONFIG=(MYCDB191,MYCDB192)' scope=spfile;SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=MYCDB191' scope=both;SQL> alter system set log_archive_dest_2='service=SL LGWR SYNC AFFIRM valid_for=(all_logfiles,primary_role) db_unique_name=MYCDB192' scope=spfile;--SQL> alter system set log_archive_format='ARC_%t_%S_%r.arc' scope=spfile;#还原默认值（未改过则无需执行）SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;SQL> alter system set standby_file_management='AUTO' scope=spfile;SQL> alter system set service_names='MYCDB' scope=spfile;SQL> alter system set log_archive_max_processes=4 scope=spfile;#select * from global_name;#连接自身的tnsname里面的服务名SQL> alter system set job_queue_processes=10 scope=spfile;SQL> alter system set db_unique_name='MYCDB192' scope=spfile;SQL> alter system set global_names=true scope=both;SQL> ALTER DATABASE RENAME GLOBAL_NAME TO MYCDB;SQL> alter system set local_listener='SL' scope=spfile;--db_file_name_convert ：主库数据文件路径，备库数据文件路径SQL> alter system set db_file_name_convert='/u02/oradata/MYCDB/','/u02/oradata/MYCDB/' scope=spfile;-- log_file_name_convert：主库的日志文件路径，备库的日志文件路径SQL> alter system set log_file_name_convert='/u02/oradata/MYCDB/','/u02/oradata/MYCDB/' scope=spfile;--（配置tnsnames.ora网络服务名，fal_server拷贝丢失的归档文件到这里）SQL> alter system set fal_client='SL' scope=spfile;--（配置tnsnames.ora网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件）SQL> alter system set fal_server='PL' scope=spfile;--启动db接受或发送redo data，包括所有库的db_unique_nameSQL> alter system set log_archive_config = 'DG_CONFIG=(MYCDB192,MYCDB191)' scope=spfile;SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=MYCDB192' scope=both;SQL> alter system set log_archive_dest_2='service=PL LGWR SYNC AFFIRM valid_for=(all_logfiles,primary_role) db_unique_name=MYCDB191' scope=spfile;--SQL> alter system set log_archive_format='ARC_%t_%S_%r.arc' scope=spfile;#还原默认值（未改过则无需执行）SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;SQL> alter system set standby_file_management='AUTO' scope=spfile;SQL> alter system set service_names='MYCDB' scope=spfile;SQL> alter system set log_archive_max_processes=4 scope=spfile;--主[oracle@XAG191 MYCDB]$ sqlplus sys/123456@PL as sysdba[oracle@XAG191 MYCDB]$ sqlplus sys/123456@SL as sysdba--備[oracle@XAG192 admin]$ sqlplus sys/123456@PL as sysdba[oracle@XAG192 admin]$ sqlplus sys/123456@SL as sysdba[oracle@XAG192 backupset]$ sqlplus / as sysdbaSQL> shutdown immediate;SQL> startup nomount;SQL> exit[oracle@XAG191 ~]$ rman target sys/123456@PL auxiliary sys/123456@SL nocatalogRecovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 16 12:59:48 2019Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.connected to target database: MYCDB (DBID=2356306992)using target database control file instead of recovery catalogconnected to auxiliary database: MYCDB (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck;--至此已经恢复完成SQL> show con_name CDB$ROOTSQL> select group#, members, bytes from v$log; GROUP# MEMBERS BYTES---------- ---------- ----------1 1 2097152002 1 2097152003 1 209715200SQL> select member from v$logfile;/u02/oradata/MYCDB/redo03.log/u02/oradata/MYCDB/redo02.log/u02/oradata/MYCDB/redo01.logSQL> alter database add standby logfile '/u02/oradata/MYCDB/stdredo01.log' size 200M;alter database add standby logfile '/u02/oradata/MYCDB/stdredo02.log' size 200M;alter database add standby logfile '/u02/oradata/MYCDB/stdredo03.log' size 200M;alter database add standby logfile '/u02/oradata/MYCDB/stdredo04.log' size 200M;alter database add standby logfile '/u02/oradata/MYCDB/stdredo05.log' size 200M;SQL> select group#, members, bytes from v$log; GROUP# MEMBERS BYTES---------- ---------- ----------1 1 2097152002 1 2097152003 1 209715200SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/u02/oradata/MYCDB/redo03.log/u02/oradata/MYCDB/redo02.log/u02/oradata/MYCDB/redo01.log/u02/oradata/MYCDB/stdredo01.log/u02/oradata/MYCDB/stdredo02.log/u02/oradata/MYCDB/stdredo03.log/u02/oradata/MYCDB/stdredo04.log/u02/oradata/MYCDB/stdredo05.logSQL> col member format a50;SQL> set linesize 100;SQL> select member,group#,status from v$logfile;MEMBER GROUP# STATUS-------------------------------------------------- ---------- ---------------------/u02/oradata/MYCDB/redo03.log 3/u02/oradata/MYCDB/redo02.log 2/u02/oradata/MYCDB/redo01.log 1/u02/oradata/MYCDB/stdredo01.log 4/u02/oradata/MYCDB/stdredo02.log 5/u02/oradata/MYCDB/stdredo03.log 6/u02/oradata/MYCDB/stdredo04.log 7/u02/oradata/MYCDB/stdredo05.log 8#备库操作[oracle@XAG192 backupset]$ sqlplus / as sysdbaSQL> alter database open;SQL> alter database recover managed standby database using current logfile disconnect from session;SQL> set pagesize 100SQL> select sequence# ,applied from v$archived_log;SEQUENCE# APPLIED---------- ---------------------------11 YES12 YES13 YES14 IN-MEMORYSQL> select tablespace_name,file_name from dba_temp_files;*ERROR at line 1:ORA-01187: cannot read from file because it failed verification testsORA-01110: data file 201: '/u02/oradata/MYCDB/temp01.dbf'alter database tempfile '/u02/oradata/MYCDB/temp01.dbf' drop;alter tablespace temp add tempfile '/u02/oradata/MYCDB/temp01.dbf' size 32m reuse;主库创建表后，到备库检查 一致则完成
SQL> shutdown immediate;SQL> exit[oracle@DB197 backupset]$ sqlplus / as sysdbaSQL> startupSQL> select database_role,protection_mode,open_mode from v$database;DATABASE_ROLE PROTECTION_MODE OPEN_MODE---------------------------------------------------------------------------------------------PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY #如 OPEN_MODE= READ ONLY WITH APPLY 則已經開始實時應用日誌SQL> recover managed standby database using current logfile disconnect from session;SQL> select database_role,protection_mode,open_mode from v$database;DATABASE_ROLE PROTECTION_MODE OPEN_MODE-------------------------------------------------------------------------------------------PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY#如 OPEN_MODE= READ ONLY WITH APPLY 則已經開始實時應用日誌SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_ID DBID NAME OPEN_MODE------------------------------------------------------------------------------------------ 2 319070549 PDB$SEED READ ONLY #如 存在 OPEN_MODE= MOUNTED 則 alter pluggable database PDBnnn open;至此 已完成ORACLE 12C DG的 创建------------------------------------------------------------------------------------------------DB_FILE_NAME_CONVERT = '主库数据文件位置','备库数据文件位置' 物理、快照备库 DB_UNIQUE_NAME = Unique name for the database 主库 物理、逻辑、快照备库FAL_CLIENT = Oracle_Net_service_name 物理、快照备库 11g之后被弃用FAL_SERVER = Oracle_Net_service_name 物理、快照备库 （如果主库发送日志缺失）备库自动裂痕检测发现之后会从该配置的server中获取LOG_ARCHIVE_CONFIG ='DG_CONFIG ( db_unique_name, db_unique_name, ... )' 主库 物理、逻辑、快照备库 data guaid 建议LOG_ARCHIVE_DEST_n = {LOCATION=path_name | SERVICE=service_name, attribute, attribute, ...} 主库 物理、逻辑、快照备库LOG_ARCHIVE_DEST_STATE_n = {ENABLE|DEFER|ALTERNATE} 主库 物理、逻辑、快照备库LOG_ARCHIVE_FORMAT=log%d_%t_%s_%r.arc 主库 物理、逻辑、快照备库LOG_ARCHIVE_LOCAL_FIRST = {TRUE | FALSE} 主库 快照备库 建议设置成trueLOG_ARCHIVE_MAX_PROCESSES =integer 主库 物理、逻辑、快照备库 1-30 默认4LOG_FILE_NAME_CONVERT = '主库日志文件位置','备库日志文件位置' 物理、逻辑、快照备库STANDBY_ARCHIVE_DEST = filespec 物理、逻辑、快照备库 11g之后已经被弃用STANDBY_FILE_MANAGEMENT = {AUTO | MANUAL} 主库 逻辑、快照备库 建议设置成auto========================================================主庫system登錄begin dbms_network_acl_admin.create_acl ( acl => 'utl_http.xml', -- 文件名称 description => 'HTTP Access', -- 描述 principal => 'SYSTEM', -- 授权或者取消授权账号，大小写敏感 is_grant => TRUE, -- 授权还是取消授权 privilege => 'connect', -- 授权或者取消授权的权限列表 start_date => null, -- 起始日期 end_date => null -- 结束日期 );end;begin dbms_network_acl_admin.add_privilege ( -- 添加访问权限列表项 acl => 'utl_http.xml', -- 刚才创建的acl名称 principal => 'SYSTEM', -- 授权或取消授权用户 is_grant => TRUE, -- 与上同 privilege => 'resolve', -- 权限列表 start_date => null, end_date => null );end;begin dbms_network_acl_admin.assign_acl(acl => 'utl_http.xml', host =>'*');end;create table test_casselect utl_inaddr.get_host_address as host_address,utl_inaddr.get_host_name || '-' || sys_context('USERENV','CON_NAME') as get_host_name,sysdate as create_timefrom dual;create or replace procedure pr_test_c asbegin delete from test_c a where a.create_time<sysdate-0.5/24; insert into test_c select utl_inaddr.get_host_address,utl_inaddr.get_host_name || '-' || sys_context('USERENV','CON_NAME'),sysdate from dual; commit;end;Declare job1 Number;Begin dbms_job.submit(job1,'pr_test_c;',Sysdate,'sysdate + (1/(24*60))'); Commit;End;CREATE OR REPLACE VIEW V_XAG_JOB1 ASSELECT job,to_char(LAST_DATE,'yyyy-mm-dd hh24:mi:ss') as last_date ,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as next_date,to_char(total_time,9999999999) as total_time ,to_char(case when failures is null then 0 else failures end,9999999999) as failures ,broken,what,interval FROM user_jobs order by job;CREATE OR REPLACE VIEW V_XAG_JOB ASSELECT JOB_NAME,JOB_ACTION job,to_char(START_DATE,'yyyy-mm-dd hh24:mi:ss') as START_DATE,REPEAT_INTERVAL,ENABLED,to_char(LAST_START_DATE,'yyyy-mm-dd hh24:mi:ss') as LAST_START_DATE,to_char(NEXT_RUN_DATE,'yyyy-mm-dd hh24:mi:ss') as NEXT_RUN_DATE,COMMENTSFROM user_scheduler_jobs order by JOB_NAME;select * from test_cselect * from V_XAG_JOB1#DB启动时 open all pdb--conn / as sysdbaCREATE TRIGGER open_all_pdbs AFTER STARTUP ON DATABASEBEGIN EXECUTE IMMEDIATE 'alter pluggable database all open';END open_all_pdbs;#备库#在系统级别启用ADG_REDIRECT_DML：alter system set ADG_REDIRECT_DML=TRUE scope=both;#在会话级别启用ADG_REDIRECT_DML：alter session enable adg_redirect_dml;联系客服
