一、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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30590361/viewspace-2135721/
5953
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
