1漾抬、文檔介紹
本文檔基于在Centos7下安裝Oracle11g數(shù)據(jù)庫，組建完成DG高可用架構(gòu)的規(guī)劃部署工作岂座。
2 陶因、Oracle Data Guard 介紹
2.1 Data Guard環(huán)境拓展
2.2 Data Guard特點(diǎn)
數(shù)據(jù)庫服務(wù)器采用DATAGUARD災(zāi)備模式，可以滿足對(duì)可用性有特殊需求的應(yīng)用巷嚣，具備以下特點(diǎn)：
1喘先、 需要冗余的服務(wù)器設(shè)備。該模式需要有冗余的服務(wù)器硬件廷粒。硬件成本較高窘拯。
2红且、 需要冗余的存儲(chǔ)設(shè)備。主機(jī)和備機(jī)都需要同樣的存儲(chǔ)空間涤姊，成本較高暇番。
3、 安裝配置比較復(fù)雜思喊。該模式比單節(jié)點(diǎn)壁酬、單實(shí)例的模式配置復(fù)雜一些，需要更多的配置步驟恨课。
4舆乔、 管理維護(hù)成本高。該模式對(duì)維護(hù)人員的要求較高剂公，維護(hù)成本高希俩。
5、 具備一定的容災(zāi)特性纲辽。當(dāng)主機(jī)整個(gè)數(shù)據(jù)庫系統(tǒng)不可用并短期內(nèi)無法恢復(fù)時(shí)颜武，可以把數(shù)據(jù)庫系統(tǒng)切換到備機(jī)上，具備容災(zāi)的功能文兑。
6盒刚、 備機(jī)可以用作只讀查詢。備機(jī)可以切換到只讀狀態(tài)供報(bào)表之類的查詢操作绿贞，減輕主機(jī)的壓力因块。
3、Oracle DataGurad單實(shí)例部署
3.1安裝環(huán)境
在主機(jī)1上安裝數(shù)據(jù)庫軟件籍铁，并建監(jiān)聽和實(shí)例涡上，在主機(jī)2上安裝數(shù)據(jù)庫軟件，并建監(jiān)聽拒名，但不建實(shí)例吩愧。
| 主機(jī)1 | 主機(jī)2 | |
|---|---|---|
| 操作系統(tǒng) | Centos7.6 64位 | Centos7.6 64位 |
| 主機(jī)名 | oracle1 | oracle2 |
| IP | 192.168.99.237 | 192.168.99.238 |
| 主機(jī)名 | oracle1 | oracle2 |
| 數(shù)據(jù)庫軟件版本 | oracle 11.2.0.4 | oracle 11.2.0.4 |
| ORACLE_BASE | /oracle | /oracle |
| ORACLE_HOME | /oracle/product/11.2.0/db_1 | /oracle/product/11.2.0/db_1 |
| ORACLE_SID | orcl | orcl2 |
| DB_UNIQUE_NAME | orcl | orcl2 |
| 告警文件目錄 | /oracle/admin/orcl/adump | /oracle/admin/orcl2/adump |
| 控制文件目錄 | /ora_data/orcl,/ora_flash/orcl | /data/orcl2/controfile/ |
| 數(shù)據(jù)文件目錄 | /ora_data/orcl | /data/orcl2/datafile |
| 歸檔路徑 | /ora_flash/archive | /data/orcl2/archivelog |
| 閃回區(qū)路徑 | /ora_flash/flashback | /data/orcl2/ |
| STANDBY日志路徑 | /ora_data/orcl | /data/orcl2/onlinelog |
| 閃回區(qū) | 開啟 | |
| 歸檔 | 開啟 |
3.2主數(shù)據(jù)庫配置
3.2.1 設(shè)置數(shù)據(jù)庫歸檔
查看數(shù)據(jù)庫是否運(yùn)行在歸檔模式：
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Current log sequence 41
備注：如果數(shù)據(jù)庫已經(jīng)開啟歸檔，下面的操縱可以忽略增显。
如上所示未開啟歸檔雁佳，可按下面方法開啟數(shù)據(jù)庫歸檔
1、數(shù)據(jù)庫啟動(dòng)到mount狀態(tài)：
SQL> shutdown immediate `/關(guān)閉數(shù)據(jù)庫/`
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount `/啟動(dòng)到mount狀態(tài)/`
ORACLE instance started.
Total System Global Area 4092055552 bytes
Fixed Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
Database mounted.
2同云、開啟歸檔模式糖权，并設(shè)置歸檔路徑：
SQL> alter database archivelog;
Database altered.
SQL> alter database open; #open數(shù)據(jù)庫
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
SQL> alter system set log_archive_dest_1='location=/ora_flash/archive'; /設(shè)置歸檔路徑/
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ora_flash/archive
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
3.2.2 設(shè)置數(shù)據(jù)庫閃回
1、驗(yàn)證是否開啟閃回：
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------
NO
備注：如果數(shù)據(jù)庫已經(jīng)開啟flashback炸站，那么下面步驟可忽略星澳。
如上顯示，該數(shù)據(jù)庫未開啟flashback旱易，可按下面方法開啟禁偎。
2腿堤、設(shè)置閃回區(qū)路徑、大腥缗：
SQL> alter system set db_recovery_file_dest='/ora_flash/flashback'; #設(shè)置閃回去路徑
System altered.
SQL> alter system set db_recovery_file_dest_size='5G'; #設(shè)置閃回區(qū)大小
System altered.
3笆檀、開啟閃回區(qū)：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 4092055552 bytes
Fixed Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
Database mounted.
SQL> alter database flashback on; #開啟閃回
Database altered.
SQL> alter database open; #open數(shù)據(jù)庫
Database altered.
SQL> select flashback_on from v$database; #驗(yàn)證是否開啟
FLASHBACK_ON
------------------------------------
YES
3.2.3 設(shè)置數(shù)據(jù)庫強(qiáng)制歸檔
1、驗(yàn)證是否開啟focelogging
SQL> select force_logging from v$database;
FORCE_
------
NO
如果數(shù)據(jù)庫已經(jīng)開啟force logging盒至，那么下面步驟可忽略误债。
如上可以看出數(shù)據(jù)庫未開啟，則按下面步驟執(zhí)行：
2妄迁、開啟強(qiáng)制日志模式：
SQL> alter database force logging; #開啟force logging
Database altered.
SQL> select force_logging from v$database; #驗(yàn)證
FORCE_
------
YES
3.2.4 添加STANDBY 日志文件
在備庫，當(dāng)RFS進(jìn)程接受到日志后李命，就將其寫入Standby日志文件里登淘，備庫的Standby日志文件可以看做是主庫在線日志文件的一個(gè)鏡像，當(dāng)主庫做日志切換時(shí)封字，備庫的Standby日志也做相應(yīng)的切換黔州，切換后的Standby日志由備庫的ARCH進(jìn)程歸檔。
Oracle規(guī)定備庫的Standby日志文件大小不能小于主庫在線日志文件最大的一個(gè)阔籽，一般情況下流妻，為了管理方便，最好把所有的在線日志和Standby日志大小設(shè)為一樣笆制。
1绅这、通過下面語句可以查詢主庫在線日志的大小和組數(shù)：
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
2 50
3 50
2、通過下面的語句可以查詢備庫Standby日志的大小和組數(shù)：
SQL> select group#,bytes/1024/1024 from v$standby_log;
no rows selected
3在辆、創(chuàng)建standby logfile：
SQL> select * from v$logfile; #先查看了重做日志路徑证薇，計(jì)劃把standby logfile路徑也定義在/ora_data/orcl/
GROUP# STATUS TYPE
---------- -------------- --------------
MEMBER
--------------------------------------------------------------------------------
IS_REC
------
3 ONLINE
/ora_data/orcl/redo03.log
NO
2 ONLINE
/ora_data/orcl/redo02.log
NO
GROUP# STATUS TYPE
---------- -------------- --------------
MEMBER
--------------------------------------------------------------------------------
IS_REC
------
1 ONLINE
/ora_data/orcl/redo01.log
NO
SQL> alter database add standby logfile group 11 '/ora_data/orcl/redo11_stb01.log' size 50M;
Database altered.
SQL> alter database add standby logfile group 12 '/ora_data/orcl/redo12_stb01.log' size 50M;
Database altered.
SQL> alter database add standby logfile group 13 '/ora_data/orcl/redo13_stb01.log' size 50M;
Database altered.
SQL> alter database add standby logfile group 14 '/ora_data/orcl/redo14_stb01.log' size 50M;
Database altered.
SQL> select group#,bytes/1024/1024 from v$standby_log; #驗(yàn)證
GROUP# BYTES/1024/1024
---------- ---------------
11 50
12 50
13 50
14 50
3.2.5 修改參數(shù)文件
1、修改對(duì)應(yīng)的參數(shù)文件：
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,orcl2)';
#修改log_archive_config配置
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=orcl2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2' scope=spfile; #修改log_archive_dest_2配置
System altered.
SQL> alter system set log_archive_dest_state_1='enable'; #打開log_archive_dest_state_1
System altered.
SQL> alter system set log_archive_dest_state_2='enable'; #打開log_archive_dest_state_2
System altered.
SQL> alter system set db_file_name_convert='/data/orcl2/datafile','/ora_data/orcl' scope=spfile; #打開log_archive_dest_state_2
System altered.
SQL> alter system set log_file_name_convert='/data/orcl2/onlinelog','/ora_data/orcl' scope=spfile; #修改STANDBY日志路徑
System altered.
SQL> alter system set fal_server='orcl2';
System altered.
SQL> alter system set fal_client='orcl';
System altered.
SQL> alter system set standby_file_management='AUTO';
System altered.
SQL>alter system set db_unique_name='orcl' scope=spfile;
System altered.
注意：上面修改的參數(shù)有的需要重啟數(shù)據(jù)庫才能生效匆篓，下面為重啟數(shù)據(jù)庫步驟
2浑度、重啟數(shù)據(jù)庫：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4092055552 bytes
Fixed Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
Database mounted.
Database opened.
3.2.6 修改監(jiān)聽配置文件
備注：如果是grid建庫或者rac環(huán)境，listener需要在grid用戶下創(chuàng)建鸦概，并且下文ORACLE_HOME的配置為ORACLE用戶的$ORACLE_HOME路徑箩张。
[oracle@oracle1 /oracle/product/11.2.0/db_1/network/admin]$vim listener.ora
# listener.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.237)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
ADR_BASE_LISTENER = /oracle
3.2.7 修改TNS配置文件。
備注：如果是grid建庫或者rac環(huán)境窗市，tns需要在oracle用戶下創(chuàng)建先慷。
[oracle@oracle1 /oracle/product/11.2.0/db_1/network/admin]$vim tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.237)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.238)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl2)
)
)
3.2.8 重啟監(jiān)聽服務(wù)
[oracle@oracle1 ~]$lsnrctl stop
[oracle@oracle1 ~]$lsnrctl start
[oracle@oracle1 ~]$lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-JAN-2021 13:58:12
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.237)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 25-JAN-2021 08:22:24
Uptime 1 days 5 hr. 35 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/oracle1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.237)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
3.2.9 拷貝參數(shù)文件
1、利用spfile創(chuàng)建pfile
SQL> create pfile='/tmp/initorcl2' from spfile;
File created.
[root@oracle1 /tmp]#ll
total 4
drwxr-xr-x 2 oracle dba 6 Jan 21 09:27 hsperfdata_oracle
-rw-r--r-- 1 oracle dba 1350 Jan 22 16:02 initorcl2
2谨设、將pfile拷貝到備庫
[root@oracle1 /tmp]#scp initorcl2 192.168.99.238:/oracle/product/11.2.0/db_1/dbs
3.2.10 拷貝密碼文件
[root@oracle1 ]#scp /oracle/product/11.2.0/db_1/dbs/orapworcl 192.168.99.238:/oracle/product/11.2.0/db_1/dbs
3.3備數(shù)據(jù)庫配置
3.3.1 修改參數(shù)文件
修改之前從主庫拷貝過來的那個(gè)參數(shù)文件熟掂，具體如下：
[root@oracle2 /tmp]#vim initorcl2
*.audit_file_dest='/oracle/admin/orcl2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/orcl2/controlfile/control01.ctl','/data/orcl2/controlfile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/ora_data/orcl','/data/orcl2/datafile'
*.db_name='orcl'
*.db_recovery_file_dest='/data/orcl2'
*.db_recovery_file_dest_size=5368709120
*.db_unique_name='orcl2'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl2XDB)'
*.fal_client='orcl2'
*.fal_server='orcl'
*.log_archive_config='DG_CONFIG=(orcl,orcl2)'
*.log_archive_dest_1='location=/data/orcl2/archivelog'
*.log_archive_dest_2='SERVICE=orcl VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_file_name_convert='/ora_data/orcl','/data/orcl2/onlinelog'
*.open_cursors=300
*.pga_aggregate_target=2456813568
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_target=4099932160
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
注意:尤其注意紅色標(biāo)注的地方
3.3.2 修改密碼文件
修改之前從主庫拷貝過來的密碼文件，具體如下：
[oracle@oracle2 /oracle/product/11.2.0/db_1/dbs]$mv orapworcl orapworcl2
3.3.3 創(chuàng)建相應(yīng)的文件目錄
根據(jù)上面修改的參數(shù)文件扎拣，為備庫創(chuàng)建相應(yīng)的文件目錄
[root@oracle2 /]#mkdir -p /oracle/admin/orcl2/adump
[root@oracle2 /]#chown oracle.dba /oracle/admin/orcl2/adump/
[root@oracle2 /]#mkdir -p /data/orcl2/controlfile
[root@oracle2 /]#chown oracle.dba /data/orcl2/controlfile
[root@oracle2 /]#mkdir -p /data/orcl2/datafile
[root@oracle2 /]#chown oracle.oinstall /data/orcl2/datafile
[root@oracle2 /]#mkdir -p /data/orcl2
[root@oracle2 /]#chown oracle.oinstall /data/orcl2
[root@oracle2 /]#mkdir -p /data/orcl2/archivelog
[root@oracle2 /]#chown oracle.oinstall /data/orcl2/archivelog
[root@oracle2 /]#mkdir -p /data/orcl2/onlinelog
[root@oracle2 /]#chown oracle.oinstall /data/orcl2/onlinelog
3.3.4 修改監(jiān)聽配置文件
備注：如果是grid建庫或者rac環(huán)境赴肚，listener需要在grid用戶下創(chuàng)建素跺，并且下文ORACLE_HOME的配置為ORACLE用戶的$ORACLE_HOME路徑。
[oracle@oracle2 /oracle/product/11.2.0/db_1/network/admin]$vim listener.ora
# listener.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.238)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl2)
(ORACLE_HOME = /oracle/product/11.2.0/db_1)
(SID_NAME = orcl2)
)
)
ADR_BASE_LISTENER = /oracle
3.3.5 修改TNS配置文件
備注：如果是grid建庫或者rac環(huán)境誉券，tns需要在oracle用戶下創(chuàng)建指厌。
[oracle@oracle2 /oracle/product/11.2.0/db_1/network/admin]$vim tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.237)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.238)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl2)
)
)
3.3.6 重啟監(jiān)聽服務(wù)
[oracle@oracle2 ~]$lsnrctl stop
[oracle@oracle2 ~]$lsnrctl start
[oracle@oracle2 ~]$lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-JAN-2021 14:56:14
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.238)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 25-JAN-2021 08:22:48
Uptime 1 days 6 hr. 33 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/oracle2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.238)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl2" has 2 instance(s).
Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orcl2XDB" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully
3.3.7 啟動(dòng)數(shù)據(jù)庫到nomount狀態(tài)
SQL> startup nomount pfile='/oracle/product/11.2.0/db_1/dbs/initorcl2.ora';
ORACLE instance started.
Total System Global Area 4092055552 bytes
Fixed Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
SQL> create spfile from pfile; #創(chuàng)建spfile
File created.
3.3.8 驗(yàn)證監(jiān)聽和TNS配置
主庫上驗(yàn)證:
[oracle@oracle1 ~]$sqlplus sys/123456@orcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:00:29 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle1 ~]$sqlplus sys/123456@orcl2 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:00:40 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
備庫上驗(yàn)證：
[oracle@oracle2 ~]$sqlplus sys/123456@orcl2 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:01:47 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle2 ~]$sqlplus sys/123456@orcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:01:53 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
注意：該步驟一定要在主備庫上都能通過才能執(zhí)行下面步驟
3.3.9 恢復(fù)數(shù)據(jù)庫
在備庫上執(zhí)行rman命令，恢復(fù)數(shù)據(jù)：
[oracle@oracle2 /oracle/product/11.2.0/db_1/dbs]$rman target sys/123456@orcl auxiliary sys/123456@orcl2
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 24 15:39:29 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1588813678)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck; #具體執(zhí)行過程顯示如下：
Starting Duplicate Db at 24-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1141 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/product/11.2.0/db_1/dbs/orapworcl' auxiliary format
'/oracle/product/11.2.0/db_1/dbs/orapworcl2' ;
}
executing Memory Script
Starting backup at 24-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1146 device type=DISK
Finished backup at 24-JAN-21
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/data/orcl2/controlfile/control01.ctl';
restore clone controlfile to '/data/orcl2/controlfile/control02.ctl' from
'/data/orcl2/controlfile/control01.ctl';
}
executing Memory Script
Starting backup at 24-JAN-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f tag=TAG20210124T154010 RECID=3 STAMP=1062690010
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-JAN-21
Starting restore at 24-JAN-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 24-JAN-21
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/data/orcl2/datafile/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/data/orcl2/datafile/system01.dbf";
set newname for datafile 2 to
"/data/orcl2/datafile/sysaux01.dbf";
set newname for datafile 3 to
"/data/orcl2/datafile/undotbs01.dbf";
set newname for datafile 4 to
"/data/orcl2/datafile/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/data/orcl2/datafile/system01.dbf" datafile
2 auxiliary format
"/data/orcl2/datafile/sysaux01.dbf" datafile
3 auxiliary format
"/data/orcl2/datafile/undotbs01.dbf" datafile
4 auxiliary format
"/data/orcl2/datafile/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /data/orcl2/datafile/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 24-JAN-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/ora_data/orcl/system01.dbf
output file name=/data/orcl2/datafile/system01.dbf tag=TAG20210124T154018
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/ora_data/orcl/sysaux01.dbf
output file name=/data/orcl2/datafile/sysaux01.dbf tag=TAG20210124T154018
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/ora_data/orcl/undotbs01.dbf
output file name=/data/orcl2/datafile/undotbs01.dbf tag=TAG20210124T154018
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/ora_data/orcl/users01.dbf
output file name=/data/orcl2/datafile/users01.dbf tag=TAG20210124T154018
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-JAN-21
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=1062690058 file name=/data/orcl2/datafile/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=1062690058 file name=/data/orcl2/datafile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1062690058 file name=/data/orcl2/datafile/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1062690058 file name=/data/orcl2/datafile/users01.dbf
Finished Duplicate Db at 24-JAN-21
RMAN> exit
Recovery Manager complete.
3.3.10 開啟實(shí)時(shí)同步
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
3.3.11 開啟flashback
上面DataGuard搭建好之后踊跟，千萬不要忘了把備庫的flashback打開踩验，具體如下：
SQL> alter database recover managed standby database cancel; #取消實(shí)時(shí)同步
Database altered.
SQL> shutdown immediate #關(guān)閉數(shù)據(jù)庫
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount #打開到mount狀態(tài)
ORACLE instance started.
Total System Global Area 4092055552 bytes
Fixed Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
Database mounted.
SQL> alter database flashback on; #開始flashback
Database altered.
SQL> alter database open; #open數(shù)據(jù)庫
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session; #開啟實(shí)時(shí)同步
Database altered.
3.4驗(yàn)證DG同步
上面已經(jīng)把DataGurad搭建完，下面介紹如何驗(yàn)證DataGuard是否能實(shí)時(shí)同步
- 通過查看主備數(shù)據(jù)庫上archive_log_dest_2列是否有error報(bào)錯(cuò)商玫，如果有報(bào)錯(cuò)箕憾，則必須先根據(jù)報(bào)錯(cuò)內(nèi)容解決問題：
主庫：
SQL> select dest_name,error from v$archive_dest;
DEST_NAME ERROR
------------------------------ --------------------
LOG_ARCHIVE_DEST_1
LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_3
LOG_ARCHIVE_DEST_4
LOG_ARCHIVE_DEST_5
LOG_ARCHIVE_DEST_6
LOG_ARCHIVE_DEST_7
DEST_NAME ERROR
------------------------------ --------------------
LOG_ARCHIVE_DEST_8
LOG_ARCHIVE_DEST_9
LOG_ARCHIVE_DEST_10
LOG_ARCHIVE_DEST_11
LOG_ARCHIVE_DEST_12
LOG_ARCHIVE_DEST_13
LOG_ARCHIVE_DEST_14
LOG_ARCHIVE_DEST_15
LOG_ARCHIVE_DEST_16
LOG_ARCHIVE_DEST_17
LOG_ARCHIVE_DEST_18
DEST_NAME ERROR
------------------------------ --------------------
LOG_ARCHIVE_DEST_19
LOG_ARCHIVE_DEST_20
LOG_ARCHIVE_DEST_21
LOG_ARCHIVE_DEST_22
LOG_ARCHIVE_DEST_23
LOG_ARCHIVE_DEST_24
LOG_ARCHIVE_DEST_25
LOG_ARCHIVE_DEST_26
LOG_ARCHIVE_DEST_27
LOG_ARCHIVE_DEST_28
LOG_ARCHIVE_DEST_29
DEST_NAME ERROR
------------------------------ --------------------
LOG_ARCHIVE_DEST_30
LOG_ARCHIVE_DEST_31
備庫：
SQL> select dest_name,error from v$archive_dest;
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_1
LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_3
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_4
LOG_ARCHIVE_DEST_5
LOG_ARCHIVE_DEST_6
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_7
LOG_ARCHIVE_DEST_8
LOG_ARCHIVE_DEST_9
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_10
LOG_ARCHIVE_DEST_11
LOG_ARCHIVE_DEST_12
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_13
LOG_ARCHIVE_DEST_14
LOG_ARCHIVE_DEST_15
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_16
LOG_ARCHIVE_DEST_17
LOG_ARCHIVE_DEST_18
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_19
LOG_ARCHIVE_DEST_20
LOG_ARCHIVE_DEST_21
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_22
LOG_ARCHIVE_DEST_23
LOG_ARCHIVE_DEST_24
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_25
LOG_ARCHIVE_DEST_26
LOG_ARCHIVE_DEST_27
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_28
LOG_ARCHIVE_DEST_29
LOG_ARCHIVE_DEST_30
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_31
STANDBY_ARCHIVE_DEST
32 rows selected.
注意：上面顯示沒有報(bào)錯(cuò)
- 查詢主庫最大歸檔序號(hào)，一致即歸檔同步成功拳昌。
主庫上執(zhí)行：
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
132
備庫上操作：
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
132
主庫上執(zhí)行日志切換:
SQL>alter system archive log current;
Systemaltered.
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
133
備庫上再次驗(yàn)證：
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
133
- 查看主備庫狀態(tài):
主庫上執(zhí)行：
SQL>select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
------------------------------------
TOSTANDBY PRIMARY
備庫上執(zhí)行：
SQL>select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
------------------------------------
NOTALLOWED PHYSICAL STANDBY
- 可以創(chuàng)建一個(gè)table進(jìn)行測試:
主庫上操作：
SQL>create table dg(id number);
Tablecreated.
SQL>insert into dg values(1);
1row created.
SQL>commit;
Commitcomplete.
SQL>select * from dg;
ID
----------
1
備庫上操作：
SQL>select * from dg;
ID
----------
1
上面說明DG已同步袭异，如果沒有同步成功，可以在備庫上執(zhí)行一次同步語句:alter database recover managed standby database using current logfile disconnect from session炬藤。
3.5DG切換與恢復(fù)
我們配置DG的目的就是為了在主庫出現(xiàn)故障時(shí)御铃，備庫能夠提供服務(wù)，保證業(yè)務(wù)的正常運(yùn)行沈矿。DG的故障切換分為switchover和failover兩種:
3.5.1 switchover
switchover是用戶有計(jì)劃的進(jìn)行停機(jī)切換上真，能夠保證不丟失數(shù)據(jù)，下面我們來看下switchover是怎樣操作的：
主庫上操作：
SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
TO STANDBY PRIMARY
#`注意：上面查詢結(jié)果為TO STANDBY 或 SESSIONS ACTIVE表明可以進(jìn)行切換`
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> startup mount
Total System Global Area 4092055552 bytes
Fixed Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
Database mounted.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
備庫上操作：
SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
------------------------------------
TO PRIMARY PHYSICAL STANDBY
注意：上面查詢結(jié)果顯示為TO PRIMARY 或 SESSIONS ACTIVE表明可以切換成主庫
現(xiàn)在可以把備庫切換成主庫：
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> alter database open;
Database altered.
SQL> select switchover_status,database_role,open_mode from v$database;
SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
-------------------- ------------------------------------
SESSIONS ACTIVE PRIMARY READ WRITE
記赘拧：這時(shí)候需要在現(xiàn)在的備庫（原先的主庫）開啟實(shí)時(shí)同步
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
到此DG switover切換完成睡互，驗(yàn)證方法同上。
3.5.2 failover
failover是當(dāng)主庫真正出現(xiàn)嚴(yán)重系統(tǒng)故障溜徙，如數(shù)據(jù)庫宕機(jī)湃缎，軟硬件故障導(dǎo)致主庫不能支持服務(wù)，從而進(jìn)行的切換動(dòng)作蠢壹。
注意：為了能夠在failover后能夠恢復(fù)DG,需要在主庫上開啟flashback嗓违，如果不開啟flashback的話，DG就可能需要重新搭建
由于主庫已經(jīng)不可訪問图贸，下面所有的操作都在備庫完成：
SQL> alter database recover managed standby database cancel; #停止實(shí)時(shí)同步
Database altered.
SQL> alter database recover managed standby database finish force;
Database altered.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> alter database open;
Database altered.
SQL> select switchover_status,database_role,open_mode from v$database;
SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
-------------------- ------------------------------------
RESOLVABLE GAP PRIMARY READ WRITE
至此failover操作完成蹂季，原來的備庫已經(jīng)切換為主庫，可以給業(yè)務(wù)提供服務(wù)了疏日。
3.5.2 failover恢復(fù)
上面提到了failover偿洁，這種情形是當(dāng)主庫真正出現(xiàn)異常之后，才會(huì)執(zhí)行的操作沟优，那么我們執(zhí)行過failover 之后涕滋，如何在重新構(gòu)建DG，這里我們利用flashback database來重構(gòu)挠阁，具體方法如下：
在新的主庫上執(zhí)行：
SQL> select to_char(standby_became_primary_scn) from v$database;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
977458
在之前的主庫上宾肺，也就是現(xiàn)在的備庫上執(zhí)行下面的操作:
SQL> startup mount
ORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 688959488 bytes
Fixed Size 2256432 bytes
Variable Size 566231504 bytes
Database Buffers 117440512 bytes
Redo Buffers 3031040 bytes
Database mounted.
SQL> flashback database to scn 977458; #這個(gè)值為在新主庫上查詢到的SCN值
Flashback complete.
SQL> alter database convert to physical standby;
Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 688959488 bytes
Fixed Size 2256432 bytes
Variable Size 566231504 bytes
Database Buffers 117440512 bytes
Redo Buffers 3031040 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
到此failover 恢復(fù)已經(jīng)完成溯饵，關(guān)于如何驗(yàn)證DG方法如上，這里不在介紹锨用。
