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

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

一、數據庫安裝實施步驟如下：
1、關閉防火墻：
systemctl stop firewalld
systemctl disable firewalld

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

2、配置主機名和IP地址對應關系
vi /etc/hosts 添加如下內容

192.168.1.11 dwjrdb
192.168.1.12 dwjrstdydb

修改主備庫兩端主機名
/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、關閉transparent hugepages，設置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時間同步，root下執行
[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、環境變量

主庫：
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、內核參數：

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、啟動圖形化窗口執行databas建庫腳本
a》error啟動不起來設定export DISPLAY=:11未果；

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

13、創建完成后，dbca建庫、netca創建監聽

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

15、修改數據庫參數
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權限。
(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用戶測試

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

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

17、設定pdb隨cdb啟動參數
alter pluggable database all save state;

18、添加數據文件參考現歷史庫

--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;
--參考數據中心庫新增數據文件
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狀態為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; --切日志后日志不活動
[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 --備庫—安裝數據庫軟件，等配置dg后將數據庫升級opatch并打補丁

1、修改主備監聽文件
主庫： 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)
)
)

--重新啟動主備庫監聽
lsnrctl reload

--測試

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

2、修改主庫必要參數（CDB中執行即可）

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

alter database force logging;--開啟強制記錄日志

alter database archivelog;--開啟歸檔

alter system set standby_file_management=auto;--手動

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;--開啟遠程歸檔

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

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

alter system set fal_server='dwjrstdydb';--指定目標端

alter system set log_archive_dest_state_2=enable;--啟動應用

alter system set log_archive_dest_state_1=enable;--啟動應用

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;--查詢日志大小

select member from v$logfile;--查詢日志位置

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

select group#,thread#,members,archived,status from v$log;--查詢當前使用的是那個日志組

--添加 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 ;

---重啟主庫數據庫，使修改參數生效

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

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

3、修改備庫參數文件initteststdydb.ora相關參數

--創建audit_file_dest 目錄（改成備庫的）

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

--創建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參數

*.db_unique_name='dwjrstdydb'

---修改下列參數

.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'

--修改備庫控制文件的路徑，因為是復制過來的，控制文件目錄還是原端的

--啟動備庫到nomount

startup nomount;

4、主庫執行恢復操作

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

duplicate target database for standby nofilenamecheck from active database;
--返回結果
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狀態

alter database open;---起主庫

---備庫應用日志

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

alter database recover managed standby database cancel;---取消應用

--查詢備庫日志是否應用

select sequence#,applied from v$archived_log;

---查詢備庫進程是否正常

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

---查詢主備庫狀態

select DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

5、驗證

--創建一個測試表a；進行增刪改操作；在備庫驗證是否同步
create table a(a int);

三、配置dg borker
1、修改主備庫監聽文件，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是初建時候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文件中是初建時候，所以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、兩邊實例均 設置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

--下面執行過程發現報錯，備庫用的是主庫的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>

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

DGMGRL> remove database dwjrdb;

DGMGRL> remove database dwjrstdydb;

DGMGRL> remove configuration;

--刪掉后備庫參數dg_broker_start失效，且報錯；
SQL> show parameter dg_broker_start

NAME TYPE VALUE

dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start=true;
--重新執行上面的命令
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 --新報錯

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"

--測試刪除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; --包括數據文件全部刪除
備庫查詢
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

--創建home下的軟連接
ln -s /u01/app/oracle/diag/rdbms/dwjrdb/dwjrdb/trace trace

生產環境部署完成，該12C DG環境已經可以實現切換主備，數據同步一致。讓小白參考此文檔也可以搭出自己的DG~