步驟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:///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;
|
