ORACLE 19.3 (DG 配置)

步驟1 主备 hosts配置

[oracle@XAG191 ~]$ cat /etc/hosts
#add
192.168.40.191  XAG191  XAG191.COM
192.168.40.192  XAG192  XAG192.COM

[oracle@XAG192 ~]$ cat /etc/hosts
#add
192.168.40.191  XAG191  XAG191.COM
192.168.40.192  XAG192  XAG192.COM

步驟2 主備库启动FORCE LOGGING

[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 Mode
Automatic archival         Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
#开启主库附加日志，并验证开启的结果；
SQL> alter database force logging;
SQL> select log_mode,force_logging from v$database;

LOG_MODE     FORCE_LOGGING
------------ ---------------------------------------
ARCHIVELOG   YES

force_logging 补充说明（可选项）-主备库

从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 AVAILABILITY

STANDBY 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 PERFORMANCE

步驟3 主備库启动归档模式

SQL> archive log list;

SQL> shutdown immediate （如已启动归档则无需下面的设置）
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list;
SQL> show parameter recovery
NAME                                         TYPE                  VALUE
------------------------------------ ---------------------------------
db_recovery_file_dest             string     /u03/fra
db_recovery_file_dest_size  big integer                            500G
recovery_parallelism               integer                                   0
remote_recovery_file_dest    string

步驟4 网络配置(主)

步驟4.1 测试网速

#安装网络带宽测试工具（两台都要安装此工具）
[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 60
Connecting 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 ms
32775 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 1001ms
rtt 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 s
BDP=1500000000 * 0.0025 * 3 = 11250000 b
BDP=  11250000 b/8 =1406250 字节 （比默认是16k=16384字节 大的多）

步驟4.1 配置监听

[oracle@XAG191 ~]$ cd $ORACLE_HOME/network/admin
[oracle@XAG191 admin]$ cat listener.ora
SID_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

步驟5 网络配置(备)

[oracle@XAG192 ~]$ cd $ORACLE_HOME/network/admin
[oracle@XAG192 admin]$ cat listener.ora
SID_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.ora
MYCDB =
  (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.ora
MYCDB =
  (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)
    )
  )

步驟6 拷贝密码文件

[oracle@XAG191 admin]$ cd $ORACLE_HOME/dbs

[oracle@XAG191 dbs]$ scp orapwMYCDB oracle@192.168.40.192:$ORACLE_HOME/dbs

步驟7 Set ADG Parameter（主库配置）

#连接自身的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_name
SQL> 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;

步驟8 Set ADG Parameter（备库配置）

#连接自身的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_name
SQL> 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;

步驟9 重启主备DB及监听（如非首次配置此DG环境则无需重启）

步驟10 主备测试连接(配置后重啟主備兩台機器的數據庫)

--主
[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

步驟11 備機

[oracle@XAG192 backupset]$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount;
SQL> exit

步驟12 在主庫通过Rman Duplicate创建备库,在主庫上执行如下命令

[oracle@XAG191 ~]$ rman target sys/123456@PL auxiliary sys/123456@SL nocatalog

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 16 12:59:48 2019
Version 19.3.0.0.0

Copyright (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 catalog
connected to auxiliary database: MYCDB (not mounted)
  
RMAN> duplicate target database for standby from active database nofilenamecheck;

--至此已经恢复完成

步驟13 添加standby logfile (主库和备库都添加standby log)

SQL> show con_name
            CDB$ROOT

SQL> select  group#, members,  bytes  from v$log;
    GROUP#    MEMBERS     BYTES
---------- ---------- ----------
1       1  209715200
2       1  209715200
3       1  209715200

SQL> select member from v$logfile;
/u02/oradata/MYCDB/redo03.log
/u02/oradata/MYCDB/redo02.log
/u02/oradata/MYCDB/redo01.log

SQL> 
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  209715200
2       1  209715200
3       1  209715200

SQL> 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.log

SQL> 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

步驟14 在備庫端开启实时日志应用

#备库操作
[oracle@XAG192 backupset]$ sqlplus / as sysdba
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> set pagesize 100
SQL> select sequence# ,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------------------------
11 YES
12 YES
13 YES
14 IN-MEMORY

SQL> select tablespace_name,file_name from dba_temp_files;
*
ERROR at line 1:
ORA-01187: cannot read from file because it failed verification tests
ORA-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;

步驟15 测试

主库创建表后，到备库检查一致则完成

步驟16 備庫 Open Read Only standby数据库并且开启实时日志应用

SQL> shutdown immediate;
SQL> exit
[oracle@DB197 backupset]$ sqlplus / as sysdba
SQL> startup
SQL> 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的 创建

步驟17 附加說明

------------------------------------------------------------------------------------------------
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}                      主库 快照备库            建议设置成true
LOG_ARCHIVE_MAX_PROCESSES =integer                            主库 物理、逻辑、快照备库 1-30 默认4
LOG_FILE_NAME_CONVERT = '主库日志文件位置','备库日志文件位置' 物理、逻辑、快照备库
STANDBY_ARCHIVE_DEST = filespec                              物理、逻辑、快照备库    11g之后已经被弃用
STANDBY_FILE_MANAGEMENT = {AUTO | MANUAL}                    主库 逻辑、快照备库    建议设置成auto
========================================================

創建測試自動插入job

主庫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_c
as
select 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_time
from dual;

create or replace procedure pr_test_c as
begin
  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 AS
SELECT 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 AS
SELECT 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
,COMMENTS
FROM user_scheduler_jobs order by JOB_NAME;

select * from test_c
select * from V_XAG_JOB1

#DB启动时 open all pdb
--conn / as sysdba
CREATE TRIGGER open_all_pdbs
  AFTER STARTUP
  ON DATABASE
BEGIN
  EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;

备库ADG的自动DML重定向

#备库
#在系统级别启用ADG_REDIRECT_DML：
alter system set ADG_REDIRECT_DML=TRUE scope=both;

#在会话级别启用ADG_REDIRECT_DML：
alter session enable adg_redirect_dml;

最后编辑于：2020.01.10 21:18:53

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,294评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,493评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,790评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,595评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,718评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,906评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,053评论 3赞 410
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,797评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,250评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,570评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,711评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,388评论 4赞 332
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,018评论 3赞 316
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,796评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,023评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,461评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,595评论 2赞 350

ORACLE 19.3 (DG 配置)

步驟1 主备 hosts配置

步驟2 主備库启动FORCE LOGGING

force_logging 补充说明（可选项）-主备库

步驟3 主備库启动归档模式

步驟4 网络配置(主)

步驟4.1 测试网速

步驟4.1 配置监听

步驟5 网络配置(备)

主機

備庫

步驟6 拷贝密码文件

步驟7 Set ADG Parameter（主库配置）

步驟8 Set ADG Parameter（备库配置）

步驟9 重启主备DB及监听（如非首次配置此DG环境则无需重启）

步驟10 主备测试连接(配置后重啟主備兩台機器的數據庫)

步驟11 備機

步驟12 在主庫通过Rman Duplicate创建备库,在主庫上执行如下命令

步驟13 添加standby logfile (主库和备库都添加standby log)

步驟14 在備庫端开启实时日志应用

步驟15 测试

步驟16 備庫 Open Read Only standby数据库并且开启实时日志应用

步驟17 附加說明

創建測試自動插入job

备库ADG的自动DML重定向

推荐阅读更多精彩内容