oracle11g單節點DGbroker搭建
一、oracle11g DG 搭建
主機 備機
Version 11.2.0.4 11.2.0.4
IP 192.168.91.131 192.168.91.132
安裝情況 已安裝好資料庫 只安裝資料庫軟體
1、關防火牆(用root使用者再主備節點都執行)
臨時關閉
$ service iptables stop
永久關閉
$ chkconfig iptables off
2、關閉Selinux(用root使用者再主備節點都執行)
$ sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
及時生效
$ setenforce 0
3、主機名能互訪(用root使用者再主備節點都執行)
$ vi /etc/hosts
192.168.91.131 primarydb
192.168.91.132 standbydb
4、主備庫建建立必要的目錄(用oracle使用者再主備節點都執行)
$ mkdir -p /u01/app/archivelog
$ mkdir -p /u01/app/oracle/admin/burton/adump
$ mkdir -p /u01/app/oracle/fast_recovery_area/burton
$ mkdir -p /u01/app/oracle/oradata/burton
$ mkdir -p /u01/app/backup
$ chmod -R 755 /u01/app
5、主機設定引數檔案
SQL> alter database force logging;
SQL> alter system set log_archive_format='burton_%t_%s_%r.arc' scope=spfile;
SQL> alter system set fal_client='burton';
SQL> alter system set fal_server='standby';
SQL> alter system set standby_file_management='AUTO';
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/burton','/u01/app/oracle/oradata/burton' scope=spfile;
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/burton','/u01/app/oracle/oradata/burton' scope=spfile;
SQL> alter system set log_archive_config='DG_CONFIG=(burton,standby)';
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=burton';
SQL> alter system set log_archive_dest_state_1='enable';
SQL> alter system set log_archive_dest_2='SERVICE=standby lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
SQL> alter system set log_archive_dest_state_2='enable';
SQL> alter system set db_unique_name='burton';
SQL> alter database flashback on;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> create pfile='/tmp/pfile.ora' from spfile;
SQL> exit
6、主機上pfile檔案複製到備庫
6.1 主庫把pfile複製到備庫
$ scp -r /tmp/pfile.ora oracle@192.168.91.132:/tmp
6.2 備庫檢視pfile檔案
$ ll /tmp/pfile.ora
7、修改備庫的pfile
$ vi /tmp/pfile.ora
burton.__db_cache_size=289406976
burton.__java_pool_size=4194304
burton.__large_pool_size=71303168
burton.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
burton.__pga_aggregate_target=335544320
burton.__sga_target=503316480
burton.__shared_io_pool_size=0
burton.__shared_pool_size=130023424
burton.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/burton/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/burton/control01.ctl','/u01/app/oracle/fast_recovery_area/burton/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/burton','/u01/app/oracle/oradata/burton'
*.db_name='burton'
*.db_unique_name='standby'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=burtonXDB)'
*.fal_client='standby'
*.fal_server='burton'
*.log_archive_config='DG_CONFIG=(burton,standby)'
*.log_archive_dest_1='LOCATION=/u01/app/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=burton lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=burton'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='burton_%t_%s_%r.arc'
*.log_file_name_convert='/u01/app/oracle/oradata/burton','/u01/app/oracle/oradata/burton'
*.memory_target=836763648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
注：
(a)db_name：資料庫名字，需要保持同一個Data Guard中所有資料庫db_name相同(靜態引數，8個字元限制，大小寫不敏感)。
(b)db_unique_name：用來區分資料庫的唯一名。
(c)db_file_name_convert：主庫和備庫的資料檔案路徑轉換。如果有多個，逐一指明對映關係（靜態引數,成對出現）。
(d)log_file_name_convert：主庫和備庫的online redo log檔案路徑轉換。如果有多個，逐一指明對映關係（靜態引數,成對出現）。
(e)log_archive_format：指定歸檔檔案格式，這裡在主備端應保持一樣的格式(靜態引數)。
(f)log_archive_config：該引數通過DG_CONFIG設定同一個Data Guard中所有db_unique_name，以逗號分隔(動態引數)。
(g)log_archive_dest_n與log_archive_dest_state_n：設定歸檔路徑。location即本地路徑；service即tnsnames.ora中配置的遠端服務，
指向standby，此時的歸檔路徑即為standby的歸檔路徑。(動態引數)。
(h)fal_server和fal_client：FAL即Fetch Archive Log，其值為Oracle Net service name，即 tnsnames.ora中的服務名。設定這兩個引數
可以用來解決Archive Gaps。一旦產生了gap，fal_client會自動向fal_server請求傳輸gap的archivelog。設定了這2個引數，就不需要在
產生gap時手動向standby註冊歸檔日誌了。所需要做的就是確認主庫有這些歸檔日誌，並且主庫的控制檔案中有這些日誌的註冊資訊。
fal_client設定為資料庫自身的service name，fal_server設定為遠端資料庫的service name。
(i)standby_file_management：如果primary 資料庫資料檔案發生修改（如新建，重新命名等）則按照本引數的設定在standby中做相應修改。
設為AUTO 表示自動管理。設為MANUAL表示需要手工管理。
(j)dispatchers="(PROTOCOL=TCP)(SERVICE=<sid>XDB)"。
8、備庫用引數檔案啟動到nomount，並建立spfile
$ sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/pfile.ora';
SQL> create spfile from pfile='/tmp/pfile.ora';
SQL> shutdown abort
SQL> startup nomount
9、主庫複製密碼檔案到備庫
$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ scp -r ./orapwburton oracle@192.168.91.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwburton
備庫檢視
$ ll /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwburton
10、修改主庫和備庫的監聽
10.1 修改主備庫tnsnames.ora(tnsnames.ora主備庫一樣)
$ cd $ORACLE_HOME/network/admin/
$ vi tnsnames.ora
BURTON =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = burton)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.132)(PORT = 1521))
)
(CONNECT_DATA =
(SID = burton)
)
)
10.2 新增備庫listener.ora
$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = burton)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = standbydb)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
注：listener.ora中的GLOBAL_DBNAME向外提供服務名，listener.ora中的SID_NAME提供註冊的例項名
10.3 重啟主備監聽
$ lsnrctl stop
$ lsnrctl start
$ lsnrctl status
11、測試聯通性
主->備
$ sqlplus sys/oracle4U@standby as sysdba
備->主
$ sqlplus sys/oracle4U@burton as sysdba
12、在主機上新增standby日誌檔案
12.1 檢視原日誌組檔案
SQL> set lines 200
SQL> col member for a50
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/burton/redo03.log
2 ONLINE /u01/app/oracle/oradata/burton/redo02.log
1 ONLINE /u01/app/oracle/oradata/burton/redo01.log
SQL> select group#,sequence#,BYTES/1024/1024 sizeM,members from v$log;
GROUP# SEQUENCE# SIZEM MEMBERS
---------- ---------- ---------- ----------
1 7 50 1
2 8 50 1
3 9 50 1
12.2 增加standy日誌組檔案（原組數+1）
SQL> alter database add standby logfile thread 1 group 4 ('/u01/app/oracle/oradata/burton/redo_dg04.log') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 ('/u01/app/oracle/oradata/burton/redo_dg05.log') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 ('/u01/app/oracle/oradata/burton/redo_dg06.log') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/oradata/burton/redo_dg07.log') size 50M;
Database altered.
13、複製資料庫
主庫:
$ rman target sys/oracle4U auxiliary sys/oracle4U@standby nocatalog
RMAN> duplicate target database for standby from active database nofilenamecheck;
注：此操作主要執行Memory Script，複製密碼檔案，控制檔案，資料檔案，線上日誌檔案和歸檔日誌檔案。在此期間，
指令碼執行了alter database mount standby database，是備庫啟動到mount狀態。
14、啟動備庫
14.1 正常啟動流程
原nomount狀態，但在duplicate時，已啟動到mount狀態。
SQL> alter database open read only;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
14.2 可以延遲應用日誌(一般略過)
方法1: 在備庫應用主庫日誌的語句中指定delay屬性
備庫延遲120分鐘應用主庫日誌
SQL> alter database recover managed standby database delay 120 disconnect from session;
方法2: log_ archive_dest_n引數中指定了delay屬性
SQL> alter system set log_archive_dest_3='service=standby2 lgwr async delay=5 valid_for=(all_logfiles,all_roles) db_unique_name=standby2';
注：delay屬性並不是說延遲從主庫傳送日誌到備庫，而是指日誌到備庫後，延遲多長時間應用主庫日誌。
select ora_rowscn,to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss') opertime,id ,name from scntest;
15、驗證主備庫同步
15.1 檢查歸檔目錄是否有誤
SQL> select error from v$archive_dest where error is not null;
正常，沒有錯誤。
15.2 在主庫手工切換歸檔
SQL> alter system switch logfile;
15.3 檢視主備庫歸檔情況（主備是一致的）
SQL> select max(sequence#) from v$archived_log;
15.4 檢視standby管理情況
備庫:
SQL> select process,pid,status from v$managed_standby;
PROCESS PID STATUS
--------- ---------- ------------
ARCH 7728 CONNECTED
ARCH 7730 CONNECTED
ARCH 7732 CONNECTED
ARCH 7734 CLOSING
RFS 7826 IDLE
RFS 7766 IDLE
RFS 7768 IDLE
RFS 7824 IDLE
MRP0 7807 APPLYING_LOG
ARCH、MRPO和RFS都有表示正常
主庫：
SQL> select process,pid,status from v$managed_standby;
PROCESS PID STATUS
--------- ---------- ------------
ARCH 33377 CLOSING
ARCH 33379 CLOSING
ARCH 33381 CONNECTED
ARCH 33383 CLOSING
LNS 33385 WRITING
沒有RFS程式和MRP程式，有LNS程式
16、主備機配置最大可用模式：
SQL> alter database set standby database to maximize availability;
Database altered.
17、Data Guard 配置
17.1 主庫 PROTECTION_MODE
SQL> select name,dbid,database_role,protection_mode from v$database;
NAME DBID DATABASE_ROLE PROTECTION_MODE
--------- ---------- ---------------- --------------------
BURTON 3893622323 PRIMARY MAXIMUM AVAILABILITY
17.2 備庫 PROTECTION_MODE
SQL> select name,dbid,database_role,protection_mode from v$database;
NAME DBID DATABASE_ROLE PROTECTION_MODE
--------- ---------- ---------------- --------------------
BURTON 3893622323 PHYSICAL STANDBY MAXIMUM AVAILABILITY
17.3 檢視主備flashback database情況開啟
SQL> select name,flashback_on from v$database;
NAME FLASHBACK_ON
--------- ------------------
BURTON YES
二、DG Broker 配置環境
主機 備機
database_name burton burton
db_unique_name burton standby
service_name burton standby
Version 11.2.0.4 11.2.0.4
hostname primarydb standbydb
1、主庫設定
1.1 修改主庫引數
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1burton.dat';
System altered.
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2burton.dat';
System altered.
SQL> alter system set DG_BROKER_START=TRUE;
System altered.
注：DB_BROKER_CONFIG_FILEn引數用於指定DataGuard配置檔案的路徑,DG_BROKER_START引數設定例項啟動的時候是否自動啟動Broken.
1.2 配置監聽
(1)修改listener.ora配置
cd $ORACLE_HOME/network/admin
vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = burton)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = burton)
)
(SID_DESC =
(GLOBAL_DBNAME = burton_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = burton)
)
)
注：新增一個靜態註冊的service_name為db_unique_name_DGMGRL,這個service_name會在DGMGRL重啟資料庫的時候用到.通過DGMGRL重啟
資料庫時DMON程式會先將資料庫關閉,然後DGMGRL在通過靜態監聽中的service_name連線到資料庫,傳送啟動的命令.如果不這麼做的
話,在做switch over的時候我們容易遇到TNS-12514錯誤。
SERVICE_NAME=<db_unique_name>,<db_domain>.
SID_NAME=echo $ORACLE_SID.
ORACLE_HOME=echo $ORACLE_HOME
(2)主備庫tnsnames 配置
vi tnsnames.ora
BURTON =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = burton)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
2、備庫設定
2.1 修改備庫引數
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1standby.dat';
System altered.
SQL> alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2standby.dat';
System altered.
SQL> alter system set DG_BROKER_START=TRUE;
System altered.
2.2 配置監聽
(1)修改listener.ora配置
cd $ORACLE_HOME/network/admin
vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = burton)
)
(SID_DESC =
(GLOBAL_DBNAME = standby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = burton)
)
)
3、建立DataGuard Broker配置
3.1 在主庫上使用dgmgrl連線到資料庫
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle4U
Connected.
3.2 建立主機的配置(主庫執行)
DGMGRL> create configuration burtoncfg as primary database is burton connect identifier is burton;
Configuration "burtoncfg" created with primary database "burton"
注：burtoncfg 是配置的名稱,這裡可以隨便填.PRIMARY DATABASE IS 'burton' ,這兒的burton是指database的db_unique_name,
而connect identifier is 'burton'這裡的bjdb是指tnsname.ora連線到主庫的net service name.
檢視配置
DGMGRL> show configuration
Configuration - burtoncfg
Protection Mode: MaxAvailability
Databases:
burton - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
3.2 建立備機的配置(主庫執行)
DGMGRL> add database standby as connect identifier is standby maintained as physical;
注：add database 'standby',這兒的standby是指database的db_unique_name,而AS CONNECT IDENTIFIER IS 'standby' 這裡的standby是指
tnsname.ora 連線到standby database的net service name。
4、開啟配置
4.1 配置DGbroker引數
DGMGRL> enable Configuration;
可能會遇到Warning: ORA-16629，解決方案見文章最後部分
DGMGRL> edit database burton set property LogXptMode='sync';
DGMGRL> edit database standby set property LogXptMode='sync';
DGMGRL> enable fast_start failover;
DGMGRL> show configuration;
DGMGRL> start observer;
4.2 新開一個視窗，檢視配置情況
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle4U
Connected.
DGMGRL> show configuration;
Configuration - burtoncfg
Protection Mode: MaxAvailability
Databases:
burton - Primary database
standby - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
4.3 驗證FSF
a.主機（primarydb）：
SQL> col FS_FAILOVER_OBSERVER_HOST for a30
SQL> select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;
FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD
------- ------------------------------ ---------------------
YES primarydb 30
b.備機（standbydb）:
SQL> col FS_FAILOVER_OBSERVER_HOST for a30
SQL> select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;
FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD
------- ------------------------------ ---------------------
YES primarydb 30
c.主機上執行檢視
$ dgmgrl sys/oracle4U@burton
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show database verbose burton;
Database - burton
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
burton
Properties:
DGConnectIdentifier = 'burton'
ObserverConnectIdentifier = ''
LogXptMode = 'sync'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/app/oracle/oradata/burton, /u01/app/oracle/oradata/burton'
LogFileNameConvert = '/u01/app/oracle/oradata/burton, /u01/app/oracle/oradata/burton'
FastStartFailoverTarget = 'standby'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'burton'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primarydb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=burton_DGMGRL)(INSTANCE_NAME=burton)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/archivelog'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'burton_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> show database verbose standby;
Database - standby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
burton
Properties:
DGConnectIdentifier = 'standby'
ObserverConnectIdentifier = ''
LogXptMode = 'sync'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/app/oracle/oradata/burton, /u01/app/oracle/oradata/burton'
LogFileNameConvert = '/u01/app/oracle/oradata/burton, /u01/app/oracle/oradata/burton'
FastStartFailoverTarget = 'burton'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'burton'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standbydb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL)(INSTANCE_NAME=burton)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/archivelog'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'burton_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
5、驗證swictover
5.1 切換資料庫角色
DGMGRL> switchover to 'standby';
Performing switchover NOW, please wait...
Operation requires a connection to instance "burton" on database "standby"
Connecting to instance "burton"...
Connected.
New primary database "standby" is opening...
Operation requires startup of instance "burton" on database "burton"
Starting instance "burton"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "standby"
5.2 檢視資料庫情況
原主機（primarydb）：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
原備機（standbydb）：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
6、failover 驗證：
6.1 主機（primarydb）,模擬主機當機
SQL> shutdown abort
6.2 備機（standbydb）檢視dg配置情況
$ dgmgrl sys/oracle4U@standby
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - burtoncfg
Protection Mode: MaxAvailability
Databases:
standby - Primary database
Warning: ORA-16817: unsynchronized fast-start failover configuration
burton - (*) Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: ENABLED
Configuration Status:
WARNING
6.3 自動切換完成後，在現有備庫更新資料
SQL> create table qwe(id number);
Table created.
SQL> insert into qwe values (34);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
6.4 原主機（primarydb），啟動到mount
SQL> startup mount
6.5 現主機（standbydb）
DGMGRL> show configuration
Configuration - burtoncfg
Protection Mode: MaxAvailability
Databases:
standby - Primary database
burton - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
6.6 原主機（primarydb）執行切換角色
DGMGRL> switchover to 'burton';
Performing switchover NOW, please wait...
Operation requires a connection to instance "burton" on database "burton"
Connecting to instance "burton"...
Connected.
New primary database "burton" is opening...
Operation requires startup of instance "burton" on database "standby"
Starting instance "burton"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "burton"
6.7 主機（primarydb），檢視資料
SQL> select * from qwe;
ID
----------
34
問題：
Warning: ORA-16629: database reports a different protection level from the protection mode
日誌內容：
Error: The actual protection level 'RESYNCHRONIZATION' is different from the configured protection mode 'MAXIMUM AVAILABILITY'.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration burtoncfg Warning ORA-16608
Primary Database burton Warning ORA-16629
Physical Standby Database standby Success ORA-00000
解決辦法：
1.確保log_archive_dest_2配置成 lgwr sync affirm
alter system set log_archive_dest_2='SERVICE=standby lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
alter system set log_archive_dest_2='SERVICE=burton lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=burton';
2.保護修改模式，（ASYNC傳輸模式僅支援maximum performance 保護模式）
DGMGRL> edit database burton set property LogXptMode='sync';
DGMGRL> edit database standby set property LogXptMode='sync';
DGMGRL> edit configuration set protection mode as MAXPERFORMANCE;
DGMGRL> show configuration;
Configuration - burtoncfg
Protection Mode: MaxAvailability
Databases:
burton - Primary database
standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
主機（primarydb）：
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
備機（standbydb）：
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
注意：protection mode是設定的dataghuard保護模式；而protection level是實際生效的保護模式。
參考文獻連線：
http://blog.csdn.net/lqx0405/article/details/44777155
http://blog.csdn.net/islandstar/article/details/38042773
http://prsync.com/oracle/reinstate-a-failed-over-data-guard-using-flashback-database-440004/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30590361/viewspace-2135721/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- consul 多節點/單節點叢集搭建
- oracle11g RAC新增節點Oracle
- mongo資料庫單節點搭建Go資料庫
- hadoop叢集搭建——單節點（偽分散式）Hadoop分散式
- 基於minikube快速搭建kubernetes單節點環境
- 單機Linux下搭建MongoDB副本集-三節點LinuxMongoDB
- MongoDB叢集搭建(包括隱藏節點，仲裁節點)MongoDB
- 搭建Solana驗證者節點（全節點）的過程
- 4.2 叢集節點初步搭建
- 使用kubeadm搭建一單節點k8s測試叢集K8S
- greenplum單節點安裝
- DM8搭建2節點DMDSC
- Geth搭建多節點私有鏈條
- HAC叢集更改IP（單節點更改、全部節點更改）
- CentOS7 單節點和多節點 HPL測試CentOS
- Networker備份oracle單節點Oracle
- ElasticSearch（單節點）環境配置Elasticsearch
- vertica單節點安裝教程
- unix cm單節點啟動
- 透過Geth搭建多節點私有鏈
- MongoDB單節點部署與基本操作MongoDB
- kubernetes環境部署單節點redisRedis
- NEO共識節點推薦搭建步驟
- C# Redis分散式鎖 - 單節點C#Redis分散式
- kafka系列二：多節點分散式叢集搭建Kafka分散式
- CentOS7 上搭建多節點 Elasticsearch叢集CentOSElasticsearch
- CAS單點登入-基礎搭建
- dataguard 搭建 oracle_sid不同 2節點 primary+standbyOracle
- dataguard 搭建 oracle_sid相同 2節點 primary+standbyOracle
- 升級kubeadm 叢集（只有master單節點）AST
- Linux下NiFi 1.9.2部署（單節點）LinuxNifi
- XML節點自動生成簡單例項XML單例
- ElasticSearch- 單節點 unassigned_shards 故障排查Elasticsearch
- 單連結串列-相鄰節點交還
- zookeeper單節點和叢集部署與配置
- Ubuntu 18.04 搭建單節點 k8s 記錄 (不需要科學上網的方案)UbuntuK8S
- JavaScript學習之DOM（節點、節點層級、節點操作）JavaScript
- linux搭建kafka叢集，多master節點叢集說明LinuxKafkaAST
- 安裝 Hadoop：設定單節點 Hadoop 叢集Hadoop