Oracle 12c Data Guard 純小白部署手冊(cè)

子公司需要搭建數(shù)據(jù)倉庫，并且新建ADG作為主從，硬件設(shè)備Dell R730機(jī)器；
192.168.1.11 dwjrdb --主庫
192.168.1.12 dwjrstdyd --備庫
--數(shù)據(jù)庫軟件：linuxx64_12201_database.zip
--數(shù)據(jù)庫補(bǔ)丁包：p27105253_122010_Linux-x86-64.zip
--Opatch升級(jí)包：p6880880_122010_Linux-x86-64.zip
--目錄規(guī)劃為/u01存放歸檔日志，數(shù)據(jù)文件等，目錄6.6TB空間

一、數(shù)據(jù)庫安裝實(shí)施步驟如下：
1、關(guān)閉防火墻：
systemctl stop firewalld
systemctl disable firewalld

systemctl stop firewalld.service
systemctl disable firewalld.service
chkconfig NetworkManager off

2、配置主機(jī)名和IP地址對(duì)應(yīng)關(guān)系
vi /etc/hosts 添加如下內(nèi)容

192.168.1.11 dwjrdb
192.168.1.12 dwjrstdydb

修改主備庫兩端主機(jī)名
/etc/hostname
dwjrdb

/etc/hostname
dwjrstdydb

3、安裝依賴包：(oracle linux 7.2依賴包如下，具體可參考官方文檔)

yum clean all
yum install -y binutils-
yum install -y compat-libcap1-
yum install -y compat-libstdc
yum install -y glibc-
yum install -y glibc-devel-
yum install -y ksh
yum install -y libaio-
yum install -y libaio-devel
yum install -y libX11-
yum install -y libXau-
yum install -y libXi-
yum install -y libXtst-
yum install -y libgcc-
yum install -y libstdc
yum install -y libstdc++-devel
yum install -y libxcb-
yum install -y make
yum install -y nfs-utils-
yum install -y net-tools-
yum install -y smartmontools-
yum install -y sysstat-
yum install -y compat-libcap1
yum install -y sysstat-
yum install -y libaio-devel-
yum install -y compat-libcap1-*

4、關(guān)閉transparent hugepages，設(shè)置hugepages
查看transparent hugepage
Red Hat Enterprise Linux kernels:

cat /sys/kernel/mm/redhat_transparent_hugepage/enabled

Other kernels:

cat /sys/kernel/mm/transparent_hugepage/enabled

oracle linux 7.2 沒有開啟transparent hugepages，忽略。

5、添加NTP時(shí)間同步，root下執(zhí)行
[root@dwjrdb ~]# crontab -l
/5 * /usr/sbin/ntpdate 1.1.1.1 >/dev/null 2>&1

6、用戶和組：
groupadd -g 501 oinstall
groupadd -g 502 dba
groupadd -g 503 oper
groupadd -g 504 backupdba
groupadd -g 505 dgdba
groupadd -g 506 kmdba
groupadd -g 507 racdba

/usr/sbin/useradd -u 500 -g oinstall -G oinstall,dba,oper,backupdba,dgdba,kmdba,racdba oracle

7、目錄：
mkdir -p /u01/app/oraInventory
chown -R oracle:oinstall /u01/app/oraInventory
chmod -R 775 /u01/app/oraInventory

mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle

mkdir -p /u01/app/oracle/product/12.2.0/db_1
chown -R oracle:oinstall /u01/app/oracle/product/12.2.0/db_1
chmod -R 775 /u01/app/oracle/product/12.2.0/db_1

mkdir -p /u01/arch
chown -R oracle:oinstall /u01/arch
chmod -R 775 /u01/arch

備庫
mkdir -p /u01/app/oracle/oradata/dwjrstdydb
chown -R oracle:oinstall /u01/app/oracle/oradata/dwjrstdydb
chmod -R 775 /u01/app/oracle/oradata/dwjrstdydb

8、環(huán)境變量

主庫：
export ORACLE_SID=dwjrdb
export ORACLE_BASE=/u01/app/oracle/
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
umask 022
stty erase ^h

備庫：
export PATH
export ORACLE_SID=dwjrstdydb
export ORACLE_BASE=/u01/app/oracle/
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
umask 022
stty erase ^h

9、內(nèi)核參數(shù)：

vi /etc/sysctl.conf
kernel.shmmax = 236223201280
kernel.shmall = 57671680
kernel.shmmni = 4096
kernel.sem = 5010 641280 5010 128
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=1048576
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048576
fs.aio-max-nr=1048576
vm.swappiness = 0
vm.nr_hugepages = 62850

--保存生效
/sbin/sysctl -p

10、限制文件：/etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240

11、vi /etc/pam.d/login文件
session required pam_limits.so

vi /etc/profile

if [ $USER = "oracle" ] || [ $USER = "grid" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi

12、啟動(dòng)圖形化窗口執(zhí)行databas建庫腳本
a》error啟動(dòng)不起來設(shè)定export DISPLAY=:11未果；

b》直接用xmanager登陸oracle用戶，執(zhí)行安裝腳本可以；

13、創(chuàng)建完成后，dbca建庫、netca創(chuàng)建監(jiān)聽

14、備庫端只裝sqlplus軟件不建庫

15、修改數(shù)據(jù)庫參數(shù)
alter system set open_cursors=500 container=all;
alter system set processes=5000 scope=spfile;
alter system set db_files=5000 scope=spfile;
alter system set undo_retention=7200 scope=spfile;
alter system set "_undo_autotune"=FALSE scope=spfile;
alter system set DEFERRED_SEGMENT_CREATION=false scope=spfile;

16、更新OPatch版本，OPatch直接壓縮替換，并且將patch打上。
1.(1) 上傳p6880880_122010_Linux-x86-64.zip到/u01/software目錄下, /u01/software給777權(quán)限。
(2) root用戶下將原OPatch目錄改名。

mv /u01/app/oracle/product/12.2.0/db_1/OPatch /u01/app/oracle/product/12.2.0/db_1/OPatchBAK

2. oracle目錄OPatch替換
   (1) root用戶

cd /u01/software/;chown oracle:oinstall p6880880_122010_Linux-x86-64.zip

(2) oracle用戶

su - oracle cd /u01/software/ ; unzip p6880880_122010_Linux-x86-64.zip

(3) root用戶

cd /u01/software/; mv OPatch /u01/app/oracle/product/12.1.0/db_1/

(4) oracle用戶測(cè)試

su - oracle /u01/app/oracle/product/12.1.0/db_1/OPatch/opatch version

打補(bǔ)丁
[oracle@dwjrdb 27105253]$ cd /software/27105253
[oracle@dwjrdb 27105253]$ /u01/app/oracle/product/12.2.0/db_1/OPatch/opatch apply

17、設(shè)定pdb隨cdb啟動(dòng)參數(shù)
alter pluggable database all save state;

18、添加數(shù)據(jù)文件參考現(xiàn)歷史庫

--CDB--
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/dwjrdb/system01.dbf' RESIZE 1024M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/dwjrdb/undotbs01.dbf' RESIZE 5120M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/dwjrdb/users01.dbf' RESIZE 1024M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/dwjrdb/sysaux01.dbf' RESIZE 8192M;
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/dwjrdb/temp01.dbf' RESIZE 1024M;

--pdb
alter database datafile '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/system01.dbf' resize 1024M;
alter database datafile '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/users01.dbf' resize 5120M;
alter database datafile '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/sysaux01.dbf' resize 20480M;
alter database datafile '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/undotbs01.dbf' resize 30720M;
alter database tempfile '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/temp01.dbf' resize 30720M;
--參考數(shù)據(jù)中心庫新增數(shù)據(jù)文件
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/undotbs02.dbf' SIZE 30720M;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/undotbs03.dbf' SIZE 30720M;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/undotbs04.dbf' SIZE 30720M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/temp02.dbf' SIZE 30720M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/temp03.dbf' SIZE 30720M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dwjrdb/dwjrdbpdb/temp04.dbf' SIZE 30720M;

--添加redo日志，初始化redo太小，并將redo組1-3全部剔除
alter database add logfile group 4 ('/u01/app/oracle/oradata/dwjrdb/redo04a.log')size 1024M;
alter database add logfile member '/u01/app/oracle/oradata/dwjrdb/redo04b.log' to group 4;
alter database add logfile group 5 ('/u01/app/oracle/oradata/dwjrdb/redo05a.log')size 1024M;
alter database add logfile member '/u01/app/oracle/oradata/dwjrdb/redo05b.log' to group 5;
--剔除redo組1-3，查看redo狀態(tài)為INACTIVE即可drop
select GROUP#,STATUS,MEMBERS from v$log;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3; 一直是current，將組1,2回切
[oracle@dwjrdb ~]$ cd /u01/app/oracle/oradata/dwjrdb/
[oracle@dwjrdb dwjrdb]$ rm -rf redo01.log redo02.log
alter database add logfile group 1 ('/u01/app/oracle/oradata/dwjrdb/redo01a.log')size 1024M;
alter database add logfile member '/u01/app/oracle/oradata/dwjrdb/redo01b.log' to group 1;
alter database add logfile group 2 ('/u01/app/oracle/oradata/dwjrdb/redo02a.log')size 1024M;
alter database add logfile member '/u01/app/oracle/oradata/dwjrdb/redo02b.log' to group 2;
alter database drop logfile group 3; --切日志后日志不活動(dòng)
[oracle@dwjrdb ~]$ cd /u01/app/oracle/oradata/dwjrdb/
[oracle@dwjrdb dwjrdb]$ rm -rf redo03.log
alter database add logfile group 3 ('/u01/app/oracle/oradata/dwjrdb/redo03a.log')size 1024M;
alter database add logfile member '/u01/app/oracle/oradata/dwjrdb/redo03b.log' to group 3;

--增加控制文件--
SQL> select name from v$controlfile;
/u01/app/oracle/oradata/dwjrdb/control01.ctl
/u01/app/oracle/oradata/dwjrdb/control02.ctl
--加控制文件
alter system set control_files='/u01/app/oracle/oradata/dwjrdb/control01.ctl','/u01/app/oracle/oradata/dwjrdb/control02.ctl','/u01/app/oracle/oradata/dwjrdb/control03.ctl' scope=spfile;

shutdown immediate;
cp control01.ctl control03.ctl
startup
SQL> select name from v$controlfile;
NAME

/u01/app/oracle/oradata/dwjrdb/control01.ctl
/u01/app/oracle/oradata/dwjrdb/control02.ctl
/u01/app/oracle/oradata/dwjrdb/control03.ctl

二、ADG的搭建步驟

192.168.1.11 dwjrdb --主庫

192.168.1.12 dwjrstdydb --備庫—安裝數(shù)據(jù)庫軟件，等配置dg后將數(shù)據(jù)庫升級(jí)opatch并打補(bǔ)丁

1、修改主備監(jiān)聽文件
主庫： listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dwjrdb)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =dwjrdb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrdb)
)
(SID_DESC =
(GLOBAL_DBNAME =dwjrstdydb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrstdydb)
)
)

如果配置broker的話，需要新增listener.ora中
(SID_DESC =
(GLOBAL_DBNAME =dwjrdb_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrdb)
)

--tnsnames.ora

dwjrdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dwjrdb)
)
)

dwjrstdydb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dwjrstdydb)
)
)

--備庫

--listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =dwjrdb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrdb)
)
(SID_DESC =
(GLOBAL_DBNAME =dwjrstdydb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrstdydb)
)
)

--tnsnames.ora

dwjrdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dwjrdb)
)
)

dwjrstdydb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1192.168.1.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dwjrstdydb)
)
)

--重新啟動(dòng)主備庫監(jiān)聽
lsnrctl reload

--測(cè)試

tnsping dwjrdb
tnsping dwjrstdydb
sqlplus sys/VgdAJ9ZeNw2B@dwjrdb as sysdba
sqlplus sys/VgdAJ9ZeNw2B@dwjrstdydb as sysdba

2、修改主庫必要參數(shù)（CDB中執(zhí)行即可）

select force_logging from v$database; --查詢是否開啟

alter database force logging;--開啟強(qiáng)制記錄日志

alter database archivelog;--開啟歸檔

alter system set standby_file_management=auto;--手動(dòng)

alter system set log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=dwjrdb' scope=spfile;--開啟本地歸檔

alter system set log_archive_dest_2='SERVICE=dwjrstdydb LGWR ASYNC valid_for=(all_logfiles,all_roles) db_unique_name=dwjrstdydb' scope=spfile;--開啟遠(yuǎn)程歸檔

alter system set log_archive_config='dg_config=(dwjrdb,dwjrstdydb)';--開啟DG功能

alter system set fal_client='dwjrdb';--指定源端

alter system set fal_server='dwjrstdydb';--指定目標(biāo)端

alter system set log_archive_dest_state_2=enable;--啟動(dòng)應(yīng)用

alter system set log_archive_dest_state_1=enable;--啟動(dòng)應(yīng)用

alter system set log_archiveformat='dwjrdb%t%s_%r.dbf' scope=spfile;--指定歸檔文件形式

--源端--先寫備端地址，在寫源端地址

alter system set db_file_name_convert='/u01/app/oracle/oradata/dwjrstdydb/','/u01/app/oracle/oradata/dwjrdb/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/dwjrstdydb/','/u01/app/oracle/oradata/dwjrdb/' scope=spfile;

select bytes/1024/1024 from v$log;--查詢?nèi)罩敬笮?/p>

select member from v$logfile;--查詢?nèi)罩疚恢?/p>

select GROUP#,BYTES/1024/1024/1024 G from v$log;--查詢組的個(gè)數(shù)，以及大小

select group#,thread#,members,archived,status from v$log;--查詢當(dāng)前使用的是那個(gè)日志組

--添加 standby logfile

alter database add standby logfile group 6 ('/u01/app/oracle/oradata/dwjrdb/standby06a.log',
'/u01/app/oracle/oradata/dwjrdb/standby06b.log') size 1G ;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/dwjrdb/standby07a.log',
'/u01/app/oracle/oradata/dwjrdb/standby07b.log') size 1G ;
alter database add standby logfile group 8 ('/u01/app/oracle/oradata/dwjrdb/standby08a.log',
'/u01/app/oracle/oradata/dwjrdb/standby08b.log') size 1G ;
alter database add standby logfile group 9 ('/u01/app/oracle/oradata/dwjrdb/standby09a.log',
'/u01/app/oracle/oradata/dwjrdb/standby09b.log') size 1G ;
alter database add standby logfile group 10 ('/u01/app/oracle/oradata/dwjrdb/standby10a.log',
'/u01/app/oracle/oradata/dwjrdb/standby10b.log') size 1G ;
alter database add standby logfile group 11 ('/u01/app/oracle/oradata/dwjrdb/standby11a.log',
'/u01/app/oracle/oradata/dwjrdb/standby11b.log') size 1G ;

---重啟主庫數(shù)據(jù)庫，使修改參數(shù)生效

shut immediate;

startup

---生成 pfile 文件

create pfile from spfile;

---傳遞密碼文件

cd $ORACLE_HOME/dbs

scp orapwdwjrdb 192.168.1.12:/u01/app/oracle/product/12.2.0/db_1/dbs/orapwdwjrstdydb

---傳遞參數(shù)文件
cd $ORACLE_HOME/dbs
scp initdwjrdb.ora 192.168.1.12:/u01/app/oracle/product/12.2.0/db_1/dbs/initdwjrstdydb.ora

3、修改備庫參數(shù)文件initteststdydb.ora相關(guān)參數(shù)

--創(chuàng)建audit_file_dest 目錄（改成備庫的）

mkdir -p /u01/app/oracle/admin/dwjrstdydb/adump

--創(chuàng)建control_files 路徑

mkdir -p /u01/app/oracle/oradata/dwjrstdydb

--修改db_file_name_convert、log_file_name_convert 路徑

.db_file_name_convert='/u01/app/oracle/oradata/dwjrdb','/u01/app/oracle/oradata/dwjrstdydb'
.log_file_name_convert='/u01/app/oracle/oradata/dwjrdb','/u01/app/oracle/oradata/dwjrstdydb'

--添加db_unique_name參數(shù)

*.db_unique_name='dwjrstdydb'

---修改下列參數(shù)

.fal_client='dwjrstdydb'
.fal_server='dwjrdb'

*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=dwjrstdydb'

*.log_archive_dest_2=''

*.log_archiveformat='dwjrstdydb%t%s%r.arc'

--修改備庫控制文件的路徑，因?yàn)槭菑?fù)制過來的，控制文件目錄還是原端的

--啟動(dòng)備庫到nomount

startup nomount;

4、主庫執(zhí)行恢復(fù)操作

rman target sys/VgdAJ9ZeNw2B@dwjrdb auxiliary sys/VgdAJ9ZeNw2B@dwjrstdydb

duplicate target database for standby nofilenamecheck from active database;
--返回結(jié)果
datafile 10 switched to datafile copy
input datafile copy RECID=10 STAMP=980117877 file name=/u01/app/oracle/oradata/dwjrstdydb/dwjrdbpdb/undotbs01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=11 STAMP=980117877 file name=/u01/app/oracle/oradata/dwjrstdydb/dwjrdbpdb/users01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=12 STAMP=980117877 file name=/u01/app/oracle/oradata/dwjrstdydb/dwjrdbpdb/undotbs02.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=13 STAMP=980117877 file name=/u01/app/oracle/oradata/dwjrstdydb/dwjrdbpdb/undotbs03.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=14 STAMP=980117877 file name=/u01/app/oracle/oradata/dwjrstdydb/dwjrdbpdb/undotbs04.dbf
Finished Duplicate Db at 29-JUN-18

---上面操作完成，備庫就處于mount狀態(tài)

alter database open;---起主庫

---備庫應(yīng)用日志

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

alter database recover managed standby database cancel;---取消應(yīng)用

--查詢備庫日志是否應(yīng)用

select sequence#,applied from v$archived_log;

---查詢備庫進(jìn)程是否正常

select process,client_process,sequence#,status from v$managed_standby;

SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

MRP0 APPLYING_LOG 1 110 86 2097152

---查詢主備庫狀態(tài)

select DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

5、驗(yàn)證

--創(chuàng)建一個(gè)測(cè)試表a；進(jìn)行增刪改操作；在備庫驗(yàn)證是否同步
create table a(a int);

三、配置dg borker
1、修改主備庫監(jiān)聽文件，tns文件。

主庫listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =dwjrdb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrdb)
)
(SID_DESC =
(GLOBAL_DBNAME =dwjrstdydb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrstdydb)
)
(SID_DESC =
(GLOBAL_DBNAME =dwjrdb_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrdb)
)
)

備庫listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =dwjrdb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrdb)
)
(SID_DESC =
(GLOBAL_DBNAME =dwjrstdydb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrstdydb)
)
(SID_DESC =
(GLOBAL_DBNAME =dwjrstdydb_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = dwjrstdydb)
)
)

主庫tnsname文件中pdb是初建時(shí)候ip為11.11
[oracle@dwjrdb ~]$ more /u01/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora
DWJRDBPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DWJRDBPDB)
)
)
備庫tnsnames。ora文件中是初建時(shí)候，所以ip為11.11，改為11.12
[oracle@dwjrstdydb admin]$ vi tnsnames.ora
DWJRDBPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DWJRDBPDB)
)
)

2、兩邊實(shí)例均 設(shè)置dg_broker_start=true;
primary :
alter system set dg_broker_start=true;

standby :
alter system set dg_broker_start=true;

dgmgrl sys/VgdAJ9ZeNw2B@dwjrdb
create CONFIGURATION dwjrdb_dg as primary database is dwjrdb CONNECT IDENTIFIER IS dwjrdb;
add database dwjrstdydb as CONNECT IDENTIFIER IS dwjrstdydb MAINTAINED AS PHYSICAL;
enable configuration;
show configuration;
switchover to dwjrstdydb;
switchover to dwjrdb;
show database verbose dwjrstdydb

--下面執(zhí)行過程發(fā)現(xiàn)報(bào)錯(cuò)，備庫用的是主庫的pfile直接修改，但是主庫是spfile，備庫是pfile
[oracle@dwjrdb ~]$ dgmgrl sys/VgdAJ9ZeNw2B@dwjrdb
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Mon Jul 2 10:48:32 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "dwjrdb"
Connected as SYSDBA.
DGMGRL> create CONFIGURATION dwjrdb_dg as primary database is dwjrdb CONNECT IDENTIFIER IS dwjrdb;
Configuration "dwjrdb_dg" created with primary database "dwjrdb"
DGMGRL> add database dwjrstdydb as CONNECT IDENTIFIER IS dwjrstdydb MAINTAINED AS PHYSICAL;
Database "dwjrstdydb" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - dwjrdb_dg

Protection Mode: MaxPerformance
Members:
dwjrdb - Primary database
dwjrstdydb - Physical standby database
Error: ORA-16797: member is not using a server parameter file

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 1 second ago)

DGMGRL>

--兩邊的參數(shù)文件不一致，主庫是spfile，備庫是pfile，所以生成備庫的spfile，然后重啟實(shí)例；

DGMGRL> remove database dwjrdb;

DGMGRL> remove database dwjrstdydb;

DGMGRL> remove configuration;

--刪掉后備庫參數(shù)dg_broker_start失效，且報(bào)錯(cuò)；
SQL> show parameter dg_broker_start

NAME TYPE VALUE

dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start=true;
--重新執(zhí)行上面的命令
create CONFIGURATION dwjrdb_dg as primary database is dwjrdb CONNECT IDENTIFIER IS dwjrdb;
add database dwjrstdydb as CONNECT IDENTIFIER IS dwjrstdydb MAINTAINED AS PHYSICAL;
enable configuration;
--具體過程
[oracle@dwjrdb trace]$ dgmgrl sys/VgdAJ9ZeNw2B@dwjrdb
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Mon Jul 2 14:55:53 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "dwjrdb"
Connected as SYSDBA.
DGMGRL> add database dwjrstdydb as CONNECT IDENTIFIER IS dwjrstdydb MAINTAINED AS PHYSICAL;
Database "dwjrstdydb" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - dwjrdb_dg

Protection Mode: MaxPerformance
Members:
dwjrdb - Primary database
dwjrstdydb - Physical standby database (disabled)
ORA-16795: the standby database needs to be re-created --新報(bào)錯(cuò)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 5 seconds ago)

DGMGRL> show database dwjrstdydb

Database - dwjrstdydb

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
dwjrstdydb

Database Status:
ORA-16795: the standby database needs to be re-created
--解決方法
DGMGRL> enable database dwjrstdydb
Enabled.
DGMGRL> show database dwjrstdydb

Database - dwjrstdydb

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 8 seconds ago)
Apply Lag: 0 seconds (computed 8 seconds ago)
Average Apply Rate: 5.00 KByte/s
Real Time Query: ON
Instance(s):
dwjrstdydb

Database Status:
SUCCESS

DGMGRL> show database verbose dwjrstdydb

Database - dwjrstdydb

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 11 seconds ago)
Apply Lag: 0 seconds (computed 11 seconds ago)
Average Apply Rate: 5.00 KByte/s
Active Apply Rate: (unknown)
Maximum Apply Rate: (unknown)
Real Time Query: ON
Instance(s):
dwjrstdydb

Properties:
DGConnectIdentifier = 'dwjrstdydb'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = '/u01/app/oracle/oradata/dwjrdb, /u01/app/oracle/oradata/dwjrstdydb'
LogFileNameConvert = '/u01/app/oracle/oradata/dwjrdb, /u01/app/oracle/oradata/dwjrstdydb'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dwjrstdydb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dwjrstdydb_DGMGRL)(INSTANCENAME=dwjrstdydb)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'dwjrstdydb%t%s%r.arc'
TopWaitEvents = '(monitor)'

Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/dwjrstdydb/dwjrstdydb/trace/alert_dwjrstdydb.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/dwjrstdydb/dwjrstdydb/trace/drcdwjrstdydb.log

Database Status:
SUCCESS

--切換將主庫切到備庫
switchover to dwjrstdydb;

DGMGRL> switchover to dwjrstdydb;
Performing switchover NOW, please wait...
Operation requires a connection to database "dwjrstdydb"
Connecting ...
Connected to "dwjrstdydb"
Connected as SYSDBA.
New primary database "dwjrstdydb" is opening...
Operation requires start up of instance "dwjrdb" on database "dwjrdb"
Starting instance "dwjrdb"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "dwjrdb"
Switchover succeeded, new primary is "dwjrstdydb"

switchover to dwjrdb;

DGMGRL> switchover to dwjrdb;
Performing switchover NOW, please wait...
New primary database "dwjrdb" is opening...
Operation requires start up of instance "dwjrstdydb" on database "dwjrstdydb"
Starting instance "dwjrstdydb"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "dwjrstdydb"
Switchover succeeded, new primary is "dwjrdb"

--測(cè)試刪除testpdb
SQL> alter pluggable database TESTPDB close;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

     2 PDB$SEED                       READ ONLY  NO
     3 DWJRDBPDB                      READ WRITE NO
     5 TESTPDB                        MOUNTED

SQL> drop pluggable database TESTPDB including datafiles; --包括數(shù)據(jù)文件全部刪除
備庫查詢
SQL> show pdbs;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

     2 PDB$SEED                       READ ONLY  NO
     3 DWJRDBPDB                      READ ONLY  NO
     5 TESTPDB                        READ ONLY  NO

SQL> show pdbs;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

     2 PDB$SEED                       READ ONLY  NO
     3 DWJRDBPDB                      READ ONLY  NO

--創(chuàng)建home下的軟連接
ln -s /u01/app/oracle/diag/rdbms/dwjrdb/dwjrdb/trace trace

生產(chǎn)環(huán)境部署完成，該12C DG環(huán)境已經(jīng)可以實(shí)現(xiàn)切換主備，數(shù)據(jù)同步一致。讓小白參考此文檔也可以搭出自己的DG~