步驟1 主备 hosts配置
[oracle@DB196 database]$ cat /etc/hosts
#add
192.168.1.196 DB196 DB196.COM
192.168.1.197 DB197 DB197.COM
[oracle@DB197 ~]$ cat /etc/hosts
#add
192.168.1.196 DB196 DB196.COM
192.168.1.197 DB197 DB197.COM
步驟2 主備库启动FORCE LOGGING
SQL> alter database force logging;
SQL> select force_logging from v$database;
YES
步驟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 网络配置(主)
#安装网络带宽测试工具（两台都要安装此工具）
[root@DB196 src]# wget https://iperf.fr/download/fedora/iperf3-3.1.3-1.fc24.x86_64.rpm
[root@DB196 src]# rpm -i iperf3-3.1.3-1.fc24.x86_64.rpm
#暂停防火墙（两台）
[root@DB196 ~]# systemctl stop iptables.service
#TCP模式服务端方式启动iperf3
[root@DB196 src]# iperf3 -s
#在tcp模式下，客户端到服务器192.168.40.196上传带宽测试，测试时间为60秒
[root@DB197 src]# iperf3 -c 192.168.40.196 -t 60
Connecting to host 192.168.40.196, port 5201
[ 4] local 192.168.40.197 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@DB196 src]# sysctl -a
...
net.ipv4.tcp_rmem = 4096 87380 6291456
...
net.ipv4.tcp_wmem = 4096 16384 4194304
...
#测试网路延时（数据包大小=32767）
[root@DB197 src]# ping -c 2 -s 32767 DB196
PING DB196 (192.168.40.196) 32767(32795) bytes of data.
32775 bytes from DB196 (192.168.40.196): icmp_seq=1 ttl=64 time=0.983 ms
32775 bytes from DB196 (192.168.40.196): icmp_seq=2 ttl=64 time=3.95 ms
--- DB196 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字节 大的多）
[oracle@DB196 ~]$ cd $ORACLE_HOME/network/admin
[oracle@DB196 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SDU=32767)
(SID_NAME = MPCDB)
(GLOBAL_DBNAME = MPCDB196_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(SEND_BUF_SIZE=1406250)
(RECV_BUF_SIZE=1406250)
(ADDRESS = (PROTOCOL = TCP)(HOST = DB196)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
步驟5 网络配置(备)
[oracle@12c ~]$ cd $ORACLE_HOME/network/admin
[oracle@DB197 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SDU=32767)
(SID_NAME = MPCDB)
(GLOBAL_DBNAME = MPCDB197_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(SEND_BUF_SIZE=1406250)
(RECV_BUF_SIZE=1406250)
(ADDRESS = (PROTOCOL = TCP)(HOST = DB197)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
主機
[oracle@DB196 admin]$ cat tnsnames.ora
MPCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB196)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MPCDB)
)
)
LISTENER_MPCDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB196)(PORT = 1521))
PL =
(DESCRIPTION =
(SDU=32767)
(SEND_BUF_SIZE=1406250)
(RECV_BUF_SIZE=1406250)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB196)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MPCDB)
)
)
SL =
(DESCRIPTION =
(SDU=32767)
(SEND_BUF_SIZE=1406250)
(RECV_BUF_SIZE=1406250)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB197)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MPCDB)
)
)
備庫
[oracle@DB197 admin]$ cat tnsnames.ora
MPCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB197)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MPCDB)
)
)
LISTENER_MPCDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB197)(PORT = 1521))
PL =
(DESCRIPTION =
(SDU=32767)
(SEND_BUF_SIZE=1406250)
(RECV_BUF_SIZE=1406250)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB196)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MPCDB)
)
)
SL =
(DESCRIPTION =
(SDU=32767)
(SEND_BUF_SIZE=1406250)
(RECV_BUF_SIZE=1406250)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB197)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MPCDB)
)
)
步驟6 拷贝密码文件
[oracle@DB196 admin]$ cd $ORACLE_HOME/dbs
[oracle@DB196 dbs]$ scp orapwMPCDB oracle@192.168.1.197:$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='MPCDB196' 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 MPCDB;
--db_file_name_convert ：主库数据文件路径，备库数据文件路径
SQL> alter system set db_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' scope=spfile;
--log_file_name_convert：主库的日志文件路径，备库的日志文件路径
SQL> alter system set log_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' 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=(MPCDB196,MPCDB197)' scope=spfile;
#SQL> alter system set log_archive_dest_1='location=/u03/fast_recovery_area/MPCDB/MPCDB/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=MPCDB196' 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=MPCDB196' scope=both;
SQL> alter system set log_archive_dest_2='service=SL LGWR SYNC AFFIRM valid_for=(all_logfiles,primary_role) db_unique_name=MPCDB197' 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='MPCDB' scope=spfile;
SQL> alter system set log_archive_max_processes=4 scope=spfile;
步驟8 Set ADG Parameter（备库配置）
#连接自身的tnsname里面的服务名
SQL> alter system set job_queue_processes=10 scope=spfile;
SQL> alter system set db_unique_name='MPCDB197' scope=spfile;
SQL> alter system set global_names=true scope=both;
SQL> alter system set local_listener='SL' scope=spfile;
SQL> ALTER DATABASE RENAME GLOBAL_NAME TO MPCDB;
--db_file_name_convert ：主库数据文件路径，备库数据文件路径
SQL> alter system set db_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' scope=spfile;
-- log_file_name_convert：主库的日志文件路径，备库的日志文件路径
SQL> alter system set log_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' 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=(MPCDB197,MPCDB196)' scope=spfile;
#SQL> alter system set log_archive_dest_1='location=/u03/fast_recovery_area/MPCDB/MPCDB/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=MPCDB197' 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=MPCDB197' scope=both;
SQL> alter system set log_archive_dest_2='service=PL LGWR SYNC AFFIRM valid_for=(all_logfiles,primary_role) db_unique_name=MPCDB196' 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='MPCDB' scope=spfile;
SQL> alter system set log_archive_max_processes=4 scope=spfile;
步驟9 重启主备DB及监听（如非首次配置此DG环境则无需重启）
步驟10 主备测试连接(配置后重啟主備兩台機器的數據庫)
--主
[oracle@DB196 MPCDB]$ sqlplus sys/MPCDB@PL as sysdba
[oracle@DB196 MPCDB]$ sqlplus sys/MPCDB@SL as sysdba
--備
[oracle@DB197 admin]$ sqlplus sys/MPCDB@PL as sysdba
[oracle@DB197 admin]$ sqlplus sys/MPCDB@SL as sysdba
步驟11 備機
[oracle@DB197 backupset]$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount;
SQL> exit
步驟12 在主庫通过Rman Duplicate创建备库,在主庫上执行如下命令
[oracle@DB196 ~]$ rman target sys/123456@PL auxiliary sys/123456@SL nocatalog
connected to target database: MPCDB(DBID=4221729487)
using target database control file instead of recovery catalog
connected to auxiliary database: MPCDB (not mounted)
RMAN>
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/MPCDB/redo03.log
/u02/oradata/MPCDB/redo02.log
/u02/oradata/MPCDB/redo01.log
SQL> alter database add standby logfile '/u02/oradata/MPCDB/stdredo01.log' size 200M;
SQL> alter database add standby logfile '/u02/oradata/MPCDB/stdredo02.log' size 200M;
SQL> alter database add standby logfile '/u02/oradata/MPCDB/stdredo03.log' size 200M;
SQL> alter database add standby logfile '/u02/oradata/MPCDB/stdredo04.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/MPCDB/redo03.log
/u02/oradata/MPCDB/redo02.log
/u02/oradata/MPCDB/redo01.log
/u02/oradata/MPCDB/stdredo01.log
/u02/oradata/MPCDB/stdredo02.log
/u02/oradata/MPCDB/stdredo03.log
/u02/oradata/MPCDB/stdredo04.log
7 rows selected.
SQL> col member format a50;
SQL> set linesize 100;
SQL> select member,group#,status from v$logfile;
MEMBER GROUP# STATUS
-------------------------------------------------- ---------- ---------------------
/u02/oradata/MPCDB/redo03.log 3
/u02/oradata/MPCDB/redo02.log 2
/u02/oradata/MPCDB/redo01.log 1
/u02/oradata/MPCDB/stdredo01.log 4
/u02/oradata/MPCDB/stdredo02.log 5
/u02/oradata/MPCDB/stdredo03.log 6
/u02/oradata/MPCDB/stdredo04.log 7
7 rows selected
步驟14 在備庫端开启实时日志应用
#备库操作
[oracle@DB197 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
步驟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;
