Data Guard环境搭建
1.设置归档模式
#su - oracle
$sqlplus / as sysdba
SQL>archive log list;
先关闭数据库
SQL>shutdown immediate;
启动数据库到mount状态下
SQL>startup mount;
SQL>select open_mode from v$database;
把数据库修改为归档模式并打开数据库：
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
数据库打开后，我们需要把数据库设为force logging：
SQL>alter database force logging;
SQL>select name,log_mode,force_logging from v$database;
2.添加standby日志：
standby logfile的数量和大小均要与redo logfile相同
查询主库当前redo logfile的数量
SQL>select thread#,group#,members,bytes/1024/1024 from v$log;
SQL>alter database add standby logfile group 11 (‘/app/oracle/oradata/dxjcdb/redo11_STB_A.log’,’/app/oracle/oradata/dxjcdb/redo11_STB_B.log’) size 512M;
SQL>alter database add standby logfile group 12 (‘/app/oracle/oradata/dxjcdb/redo12_STB_A.log’,’/app/oracle/oradata/dxjcdb/redo12_STB_B.log’) size 512M;
SQL>alter database add standby logfile group 13 (‘/app/oracle/oradata/dxjcdb/redo13_STB_A.log’,’/app/oracle/oradata/dxjcdb/redo13_STB_B.log’) size 512M;
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;
3.设置数据库口令文件的使用模式
执行以下命令查看remote_login_passwordfile的值是否EXCLUSIVE
SQL>show parameter remote_login_passwordfile
如果不是，执行以下命令进行设置，并且重启数据库，使其生效：
SQL>alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
SQL>shutdown immediate;
SQL>startup;
4.参数设置
a)主库的参数配置
SQL>show parameter db_unique_name
SQL>alter system set log_archive_config=’dg_config=(dxjcdb,dxjcdbs)’ scope=spfile;
其中dg_config填写的是主备库的db_unique_name。
设置归档日志的存放位置：
SQL>alter system set log_archive_dest_1=’LOCATION=/app/oracle/oradata/dxjcdb/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=dxjcdb’ scope=spfile;
SQL>alter system set log_archive_dest_2=’SERVICE=dxjcdbs ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dxjcdbs’ scope=spfile;
注：第一个dxjcdbs是tnsname.ora的连接名，第二个dxjcdbs是DB_UNIQUE_NAME
启用设置的日志路径：
SQL>alter system set log_archive_dest_state_1=enable scope=spfile;
SQL>alter system set log_archive_dest_state_2=enable scope=spfile;
设置归档日志进程的最大数量（视实际情况调整）：
SQL>alter system set log_archive_max_processes=30 scope=both;
设置standby库从哪个数据库获取归档日志（只对standby库有效，在主库上设置是为了在故障切换后，主库可以成为备库使用）：
SQL>alter system set fal_server=dxjcdbs scope=both;
设置文件管理模式，此项设置为自动，不然在主库创建数据文件后，备库不会自动创建：
SQL>alter system set standby_file_management=auto scope=spfile;
启用OMF功能：
SQL>alter system set db_create_file_dest=’/app/oracle/oradata/dxjcdb’ scope=spfile;
如果主备库文件的存放路径不同，还需要设置以下两个参数（需要重启数据库生效）：
SQL> alter system set db_file_name_convert=’/app/oracle/oradata/dxjcdb’,’/app/oracle/oradata/dxjcdb’ scope=spfile;
SQL> alter system set log_file_name_convert=’/app/oracle/oradata/dxjcdb’,’/app/oracle/oradata/dxjcdb’ scope=spfile;
这步路径的先后顺序在主备库上的设置是不一样的，大家要注意！
b)备库参数设置
完成了以上步骤后，通过以下命令生成一个pfile文件给备库使用：
SQL>create pfile from spfile;
create pfile=’/home/oracle/dxjcdb_dg_pfile.ora’ from spfile;
[oracle@hdty-dxjc-db1 dbs]$ ll initdxjcdb.ora
-rw-r—r— 1 oracle oinstall 1389 Sep 30 13:55 initdxjcdb.ora
[oracle@hdty-dxjc-db1 dbs]$
c)密码文件配置
密码文件是创建DG不可缺少的一部分，主库的密码文件一般在$ORACLE_HOME/dbs，命名格式是：orapw+db_unique_name
如果不存在此文件，我们可以通过以下命令生成一个：
#su - oracle
$cd $ORACLE_HOME/dbs
$orapwd file=orapwocrl password=oracle
我们将密码文件和刚才修改好的pfile一起拷贝到备库的$ORACLE_HOME/dbs目录下，并重命名密码文件的名字：
-rw-r—r— 1 oracle oinstall 1415 Sep 30 14:17 initdxjcdbs.ora
-rw-r—r— 1 oracle oinstall 1536 Aug 27 17:53 orapwdxjcdbs
5.listener.ora与tnsnames.ora配置
a)备库配置
listener.ora内容如下：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.60.71)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dxjcdb)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = dxjcdb)
)
)
ADR_BASE_LISTENER = /app/oracle
tnsname.ora内容如下：
DXJCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.60.70)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dxjcdb)
)
)
DXJCDBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.60.71)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dxjcdb)
)
)
重启一下监听：
$lsnrctl stop
$lsnrctl start
a)主库配置
listener.ora内容如下：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.60.70)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dxjcdb)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = dxjcdb)
)
)
ADR_BASE_LISTENER = /app/oracle
tnsname.ora内容如下：
DXJCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.60.70)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dxjcdb)
)
)
DXJCDBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.60.71)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dxjcdb)
)
)
重启一下监听：
$lsnrctl stop
$lsnrctl start
做完以上配置后，在主备库上执行以下命令，确保两个主机之间网络相通：
$tnsping dxjcdb
$tnsping dxjcdbs
7.RMAN复制创建 standby库
准备工作都完成了，那我们可以开始standby库的创建了。
注：以下操作在备库完成
a)文件复制
首先，我们使用之前修改的pfile把备库启动到nomount状态，生成spfile：
$echo $ORACLE_SID （确认SID是否我们设置的）
$sqlplus / as sysdba
SQL>startup nomount pfile=’/app/oracle/product/11.2.0/db_1/dbs/initdxjcdbs.ora’;
SQL>create spfile from pfile=’/app/oracle/product/11.2.0/db_1/dbs/initdxjcdbs.ora’;
SQL>shutdown immediate;
SQL>startup nomount;
确认db_unique_name
SQL>show parameter db_unique_name;
复制数据文件，在备库上操作
[oracle@hdty-dxjc-db2 ~]$ rman target sys/hdty2019@dxjcdb auxiliary sys/hdty2019@dxjcdbs
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 30 16:50:56 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DXJCDB (DBID=3999615226)
connected to auxiliary database: DXJCDB (DBID=3999681020)
RMAN>
确认我们已经连接上主库和备库后，执行以下命令：
RMAN>duplicate target database for standby from active database;
命令执行完后，可以看到主库在开始复制文件到备库中
注：duplicate target database for standby from active database dorecover nofilenamecheck;如果复制数据库时，备库的路径和原库一致，就需要加nofilenamecheck，不然会报错。
复制完成后，打开数据库开启实时同步：
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;
查看主数据库状态
$sqlplus / as sysdba
SQL>select database_role from v$database;
登录到备库上查看：
$sqlplus / as sysdba
SQL>select database_role from v$database;
检查归档日志是否能正常传输（日志的序号必须是一样的）：
主库
SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
备库
SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
b)切换日志测试
主库
SQL> alter system switch logfile;
SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
备库
SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
这样，我们的DG已经配置成功了！
8.故障切换：
我们配置DG的目的就是为了在主库出现故障时，备库能够提供服务，保证业务的正常运行。DG的故障切换分为switchover和failover两种:
a)SWITCHOVER
switchover是用户有计划的进行停机切换，能够保证不丢失数据，下面我们来看下switchover是怎样操作的：
登录主库
$sqlplus / as sysdba
SQL>select switchover_status from v$database;（查看主库状态）
查询结果是TO STANDBY 或 SESSIONS ACTIVE表明可以进行切换；
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
SQL>shutdown immediate;
SQL> startup mount;
SQL> select database_role from v$database;
登录备库：
$sqlplus / as sysdba
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
TO PRIMARY 或 SESSIONS ACTIVE表明可以切换成主库；
现在可以把备库切换成主库了
SQL>alter database commit to switchover to primary with session shutdown;
SQL> ALTER DATABASE OPEN;
SQL>select switchover_status,database_role,open_mode from v$database;
记住：这时候要在现在的备库上开启同步：
SQL> alter database recover managed standby database using current logfile disconnect from session;
b)FAILOVER
为了能够在failover后能够恢复DG,需要在主库上开启flashback
关于开启flashback可以参考http://blog.csdn.net/shiyu1157758655/article/details/55095760
如果不开启flashback的话，DG只能重新构建相对比较麻烦。
开启flashback：
1.使用Flashback Database的前提条件
1）启用了flashback database
2）必须打开flash recovery area,若为RAC，flash recovery area必须位于共享存储中。
3）必须处于archivelog模式，开启FORCE LOGGING
2.确认上面的前提条件是否满足
1）验证是否启用了flashback database并确认FORCE LOGGIN是否开启
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON FOR
————————— —-
NO YES
SQL>
若flashback_on为“NO”，请开启flashback database,具体步骤如下：
设置闪回区大小：
SQL>ALTER SYSTEM SET db_recovery_file_dest_size=30g scope=spfile;
System altered.
设置闪回区位置：
SQL>alter system set db_recovery_file_dest=’/app/oracle/oradata/dxjcdb/flashback_area’ scope=spfile;
System altered.
操作系统创建闪回区文件夹：
SQL>host mkdir -p /app/oracle/oradata/dxjcdb/flashback_area
设置闪回目标为5天，以分钟为单位，oracle默认1440分钟，即一天
SQL>alter system set db_flashback_retention_target=7200 scope=spfile;
查看归档及闪回状态
SQL>archive log list
SQL>select open_mode,flashback_on from v$database;
关闭数据库
SQL>shutdown immediate;
启动数据库到mount状态
SQL> startup mount;
开启闪回功能
SQL> alter database flashback on;
打开数据库
SQL> alter database open;
查看闪回区配置：
SQL>show parameter db_recovery
检查flash recovery area的使用情况,查看archivelog
SQL>set linesize 200
SQL>set pagesize 80
SQL>select * from V$FLASH_RECOVERY_AREA_USAGE;
计算flash recovery area已经占用的空间
SQL>select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
查看状态
SQL>select flashback_on from v$database;
若force_logging为“NO”，请使如下SQL语句开启
SQL>alter database force logging;
2）数据库是否处于archivelog模式
SQL> archive log list;
由于主库已经不可访问，我们所有的操作都在备库完成：
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database finish force;
SQL> select database_role from v$database;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database commit to switchover to primary;
SQL> alter database open; 或者 shutdown immediate+startup
现在利用flashback重建DG
在新的主库上执行下面操作：
SQL> select to_char(standby_became_primary_scn) from v$database;
在之前的主库上，也就是现在的备库上执行下面的操作:
SQL> startup mount
SQL> flashback database to scn 103134807; //这个值为在新主库上查询到的SCN值
SQL> alter database convert to physical standby;
SQL> shutdown immediate
SQL> startup
SQL> alter database recover managed standby database using current logfile disconnect from session;
至此failover切换和切换过如何恢复就已经完成
DG_broker配置
a)配置broker
在主库上进行配置，
（也可以在备库上或者另找一台机器上配置都可以）
修改dg_broker_config_file参数。如果是在RAC环境中，这个把这个文件把到共享的存储上面，如果有ASM可以放到ASM中。
修改一下监听
主库：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.60.70)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dxjcdb)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = dxjcdb)
)
(SID_DESC =
(GLOBAL_DBNAME = dxjcdb_DGMGRL)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = dxjcdb)
)
)
ADR_BASE_LISTENER = /app/oracle
配置后重启监听：
$ lsnrctl stop
$ lsnrctl start
备库监听配置：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.60.71)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dxjcdb)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = dxjcdb)
)
(SID_DESC =
(GLOBAL_DBNAME = dxjcdbs_DGMGRL)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = dxjcdb)
)
)
ADR_BASE_LISTENER = /app/oracle
配置后重启监听：
$ lsnrctl stop
$ lsnrctl start
主库和备库上都要执行
SQL> show parameter dg_broker_config_file
这里就用默认的路径，也可以自己指定。
启用BROKER
在两个数据库上都执行：
SQL> alter system set dg_broker_start=true;
在其中任意一台做下面的操作就可以了，另它安装了client的客户端也是可以的.
这里选择在主库上执行
$ dgmgrl
DGMGRL> connect sys/hdty2019@dxjcdb //连接数据库
Connected.
DGMGRL> help create//help是帮助命令
语法:
CREATE CONFIGURATION
PRIMARY DATABASE IS
CONNECT IDENTIFIER IS
ADD DATABASE
[AS CONNECT IDENTIFIER IS
[MAINTAINED AS {PHYSICAL|LOGICAL}];
DGMGRL> create configuration dg as primary database is dxjcdb connect identifier is dxjcdb;
如果报ORA-16698的错误：
这个错误可以通过在Primary 和 Standby上取消log_archive_dest_n参数来解决，实际这一块的参数应当是交给DG broker 来管理了，不再需要人为介入设置。
在主备库上执行下面的命令
SQL> alter system set log_archive_dest_2=’’;
再次执行创建命令
启用配置文件
DGMGRL> enable configuration
增加备库到配置文件中
DGMGRL> help add
DGMGRL> add database dxjcdbs as connect identifier is dxjcdbs;
查看配置文件
DGMGRL> show configuration
查看某个数据库的配置消息
DGMGRL> show database verbose dxjcdb
DGMGRL> show database verbose dxjcdbs
这里要注意了。broker里面的连接的service_name是_DGMGRL,因为我们静态监听中的GLOBAL_NAME是DB_UNIQUE_NAME，所以这里我们要修改StaticConnectIdentifier
#不然在切换的时候会报错的。
所以上面要修改监听文件，如果不修改监听文件的话，那也可以执行下面的命令来修改StaticConnectIdentifier//这也是一种方法
DGMGRL> edit database dxjcdb set property StaticConnectIdentifier= ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.60.70)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dxjcdb_DGMGRL)(INSTANCE_NAME=dxjcdb)(SERVER=DEDICATED)))’;
DGMGRL> edit database dxjcdbs set property StaticConnectIdentifier= ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.60.71)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dxjcdbs_DGMGRL)(INSTANCE_NAME=dxjcdb)(SERVER=DEDICATED)))’;
#不然在切换的时候会报错的。
所以上面要修改监听文件，如果不修改监听文件的话，那也可以执行下面的命令来修改StaticConnectIdentifier//这也是一种方法
DGMGRL> edit database dxjcdb set property StaticConnectIdentifier= ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.60.70)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dxjcdb_DGMGRL)(INSTANCE_NAME=dxjcdb)(SERVER=DEDICATED)))’;
DGMGRL> edit database dxjcdbs set property StaticConnectIdentifier= ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.60.71)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dxjcdbs_DGMGRL)(INSTANCE_NAME=dxjcdb)(SERVER=DEDICATED)))’;
启动数据库配置：
DGMGRL> enable database dxjcdb
DGMGRL> enable database dxjcdbs
启动数据库配置时遇到的问题，启动备库的时候特别卡，启动成功后查看数据库配置发现报错。
DGMGRL> show database verbose dxjcdbs
Database - dxjcdbs
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: 1.20 MByte/s
Real Time Query: ON
Instance(s):
dxjcdb
Properties:
DGConnectIdentifier = ‘dxjcdbs’
ObserverConnectIdentifier = ‘’
LogXptMode = ‘ASYNC’
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 = ‘30’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ‘/app/oracle/oradata/dxjcdb, /app/oracle/oradata/dxjcdb’
LogFileNameConvert = ‘/app/oracle/oradata/dxjcdb, /app/oracle/oradata/dxjcdb’
FastStartFailoverTarget = ‘’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
ApplyLagThreshold = ‘0’
TransportLagThreshold = ‘0’
TransportDisconnectedThreshold = ‘30’
SidName = ‘dxjcdb’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.60.71)(PORT=1521))(CONNECTDATA=(SERVICE_NAME=dxjcdb_DGMGRL)(INSTANCE_NAME=dxjcdb)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘/app/oracle/oradata/dxjcdb/archivelog’
AlternateLocation = ‘’
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t%s_%r.dbf’
TopWaitEvents = ‘(monitor)’
Database Status:
DGM-17016: failed to retrieve status for database “dxjcdbs”
ORA-16664: unable to receive the result from a database
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxPerformance
Databases:
dxjcdb - Primary database
dxjcdbs - Physical standby database
Error: ORA-16664: unable to receive the result from a database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
查看备库告警日志发现如下错误：
Fatal NI connect error 12541, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.60.70)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dxjcdb_DGB)(INSTANCE_NAME=dxjcdb)(CID=(PROGRAM=oracle)(HOST=hdty-dxjc-db2)(USER=oracle))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 12-OCT-2019 17:15:42
Tracing not turned on.
Tns error struct:
ns main err code: 12541
TNS-12541: TNS:no listener
ns secondary err code: 12560
nt main err code: 511
TNS-00511: No listener
nt secondary err code: 111
nt OS err code: 0
可以发现SERVICE_NAME与监听配置的不一致，默认是DBG。
解决办法：
修改主备库监听配置，并重启：
主库配置如下：
# listener.ora Network Configuration File: /app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.60.70)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dxjcdb)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = dxjcdb)
)
(SID_DESC =
(GLOBAL_DBNAME = dxjcdb_DGB)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = dxjcdb)
)
)
ADR_BASE_LISTENER = /app/oracle
备库配置如下：
# listener.ora Network Configuration File: /app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.60.71)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dxjcdb)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = dxjcdb)
)
(SID_DESC =
(GLOBAL_DBNAME = dxjcdbs_DGB)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = dxjcdb)
)
)
ADR_BASE_LISTENER = /app/oracle
$ lsnrctl stop
$ lsnrctl start
修改dg数据库配置与监听一致：
DGMGRL> edit database dxjcdb set property StaticConnectIdentifier= ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.60.70)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dxjcdb_DGB)(INSTANCE_NAME=dxjcdb)(SERVER=DEDICATED)))’;
DGMGRL> edit database dxjcdbs set property StaticConnectIdentifier= ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.60.71)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dxjcdbs_DGB)(INSTANCE_NAME=dxjcdb)(SERVER=DEDICATED)))’;
DGMGRL> show database verbose dxjcdbs
Database - dxjcdbs
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: 1.21 MByte/s
Real Time Query: ON
Instance(s):
dxjcdb
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting
Properties:
DGConnectIdentifier = ‘dxjcdbs’
ObserverConnectIdentifier = ‘’
LogXptMode = ‘ASYNC’
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 = ‘30’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ‘/app/oracle/oradata/dxjcdb, /app/oracle/oradata/dxjcdb’
LogFileNameConvert = ‘/app/oracle/oradata/dxjcdb, /app/oracle/oradata/dxjcdb’
FastStartFailoverTarget = ‘’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
ApplyLagThreshold = ‘0’
TransportLagThreshold = ‘0’
TransportDisconnectedThreshold = ‘30’
SidName = ‘dxjcdb’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.60.71)(PORT=1521))(CONNECTDATA=(SERVICE_NAME=dxjcdbs_DGB)(INSTANCE_NAME=dxjcdb)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘/app/oracle/oradata/dxjcdb/archivelog’
AlternateLocation = ‘’
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t%s_%r.dbf’
TopWaitEvents = ‘(monitor)’
Database Status:
WARNING
警告处理方法：
查看不一致参数，broker里的值为0，参数文件里为空
DGMGRL> show database dxjcdbs inconsistentProperties;
INCONSISTENT PROPERTIES
INSTANCENAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
dxjcdb ArchiveLagTarget 0 0
dxjcdb LogArchiveMinSucceedDest 1 1
dxjcdb LogArchiveTrace 0 (missing) 0
dxjcdb LogArchiveFormat %t%s%r.dbf (missing) %t%s_%r.dbf
DGMGRL> edit database dxjcdbs SET PROPERTY ArchiveLagTarget=0;
DGMGRL> edit database dxjcdbs SET PROPERTY LogArchiveMinSucceedDest=1;
DGMGRL> edit database dxjcdbs SET PROPERTY LogArchiveTrace=0;
DGMGRL> edit database dxjcdbs SET PROPERTY LogArchiveFormat=’%t%s%r.dbf’;
DGMGRL> edit database dxjcdbs SET PROPERTY LogArchiveMinSucceedDest=1;
Property “logarchiveminsucceeddest” updated
DGMGRL> edit database dxjcdbs SET PROPERTY LogArchiveTrace=0;
Property “logarchivetrace” updated
DGMGRL> edit database dxjcdbs SET PROPERTY LogArchiveFormat=’%t%s%r.dbf’;
Property “logarchiveformat” updated
DGMGRL> show database verbose dxjcdbs
Database - dxjcdbs
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: 1.20 MByte/s
Real Time Query: ON
Instance(s):
dxjcdb
Properties:
DGConnectIdentifier = ‘dxjcdbs’
ObserverConnectIdentifier = ‘’
LogXptMode = ‘ASYNC’
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 = ‘30’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ‘/app/oracle/oradata/dxjcdb, /app/oracle/oradata/dxjcdb’
LogFileNameConvert = ‘/app/oracle/oradata/dxjcdb, /app/oracle/oradata/dxjcdb’
FastStartFailoverTarget = ‘’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
ApplyLagThreshold = ‘0’
TransportLagThreshold = ‘0’
TransportDisconnectedThreshold = ‘30’
SidName = ‘dxjcdb’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.60.71)(PORT=1521))(CONNECTDATA=(SERVICE_NAME=dxjcdbs_DGB)(INSTANCE_NAME=dxjcdb)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘/app/oracle/oradata/dxjcdb/archivelog’
AlternateLocation = ‘’
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t%s_%r.dbf’
TopWaitEvents = ‘(monitor)’
Database Status:
SUCCESS
b)开始切换
DGMGRL> switchover to dxjcdbs;
DGMGRL> switchover to dxjcdbs;
Performing switchover NOW, please wait…
Operation requires a connection to instance “dxjcdb” on database “dxjcdbs”
Connecting to instance “dxjcdb”…
Connected.
New primary database “dxjcdbs” is opening…
Operation requires startup of instance “dxjcdb” on database “dxjcdb”
Starting instance “dxjcdb”…
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is “dxjcdbs”
DGMGRL>
再次查看配置文件
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxPerformance
Databases:
dxjcdbs - Primary database
dxjcdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
发现主备已经成功完成切换了。
FSFO（fast start failover 快速启动故障）
a)启用闪回
上面的操作我们已经启用了闪回。
b)启用FSFO
启用了主备库的闪回功能后，我们就可以启动FSFO了，登录dgmgrl连接主库：
$ dgmgrl
DGMGRL> connect sys/hdty2019@dxjcdb
DGMGRL> show configuration
DGMGRL> enable fast_start failover;
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxPerformance
Databases:
dxjcdb - Primary database
Warning: ORA-16819: fast-start failover observer not started
dxjcdbs - (*) Physical standby database
Warning: ORA-16819: fast-start failover observer not started
Fast-Start Failover: ENABLED
Configuration Status:
WARNING
发现DGMGRL告警了，那是因为我们没有启动观察器（observer）的原因，那我们接下来就启动观察器吧！（由于observer的启动会一直占用session 窗口的，所以建议写成脚本挂后台）
这里我们在备库上执行
登录dgmgrl主机执行以下命令：
nohup dgmgrl sys/hdty2019@dxjcdb “start observer file=FSFO.dat”>>fsfo.log 2>&1 &
查看进程：
$ ps -ef|grep obser
启动observer后，我们再看一下配置状态
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxPerformance
Databases:
dxjcdb - Primary database
dxjcdbs - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
这样我们的FSFO就配置完成了，下面我们模拟主库宕机后，FSFO的切换：
1)直接把主库的关掉
SQL> shutdown abort
2)查看我们observer的日志//在备库上
$ tail -f fsfo.log
[oracle@hdty-dxjc-db2 admin]$ tail -f fsfo.log
nohup: ignoring input
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.
Observer started
12:01:36.51 Monday, October 14, 2019
Initiating Fast-Start Failover to database “dxjcdbs”…
Performing failover NOW, please wait…
Failover succeeded, new primary is “dxjcdbs”
12:01:43.68 Monday, October 14, 2019
从日志中可以发现FSFO已经切换备库的角色了！
查看failover
DGMGRL> show fast_start failover;
停用observer
DGMGRL> stop observer
停用FSFO
DGMGRL> disable fast_start failover;
Oracle DG 数据保护模式：
DataGuard允许定义3钟数据保护模式，分别是最大保护（Maximum Protection），最高可用（Maximum Availability）和最大性能（Maximum Performance）。
1.最大保护（Maximum Protection）
这种模式能够确保绝无数据丢失。要实现这一步当然是有代价的，它要求所有的事务在提交前其REDO不仅被写入到本地的Online Redologs，还要同时写入到Standby数据库的StandbyRedologs，并确认REDO数据至少在一个Standby数据库中可用（如果有多个的话），然后才会在Primary数据库上提交。如果出现了什么故障导致Standby数据库不可用的话（比如网络中断），Primary数据库会被Shutdown，以防止数据丢失。
使用这种方式要求Standby Database必须配置Standby RedoLog，而Primary Database必须使用LGWR，SYNC，AFFIRM方式归档到Standby Database.
2.最高可用性（Maximum availability）
这种模式在不影响Primary数据库可用前提下，提供最高级别的数据保护策略。其实现方式与最大保护模式类似，也是要求本地事务在提交前必须至少写入一台Standby数据库的Standby Redologs中，不过与最大保护模式不同的是，如果出现故障导致Standby数据库无法访问，Primary数据库并不会被Shutdown，而是自动转为最高性能模式，等Standby数据库恢复正常之后，Primary数据库又会自动转换成最高可用性模式。
这种方式虽然会尽量避免数据丢失，但不能绝对保证数据完全一致。这种方式要求Standby Database必须配置Standby RedoLog，而Primary Database必须使用LGWR，SYNC，AFFIRM方式归档到Standby Database.
3.最高性能（Maximum performance）
缺省模式。这种模式在不影响Primary数据库性能前提下，提供最高级别的数据保护策略。事务可以随时提交，当前Primary数据库的REDO数据至少需要写入一个Standby数据库，不过这种写入可以是不同步的。如果网络条件理想的话，这种模式能够提供类似最高可用性的数据保护，而仅对Primary数据库的性能有轻微影响。这也是创建Standby数据库时，系统的默认保护模式。
这种方式可以使用LGWR ASYNC或者ARCH进程实现，Standby Database也不要求使用Standby RedoLog。
DG三大模式切换
一：最高性能转最高可用（默认DG是最高性能模式）
1.确认主备数据库模式
1）主库：
SQL> set linesize 100
SQL> set pagesize 100
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
————- —————————— —————————— ———————— ——————————
DXJCDB READ WRITE MAXIMUM PERFORMANCE PRIMARY SESSIONS ACTIVE
2）备库：
SQL> set linesize 100
SQL> set pagesize 100
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
————- —————————— —————————— ———————— ——————————
DXJCDB READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL>
2.主库重启到mount
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2455228416 bytes
Fixed Size 2255712 bytes
Variable Size 620758176 bytes
Database Buffers 1811939328 bytes
Redo Buffers 20275200 bytes
Database mounted.
SQL>
3.修改参数
SQL> alter system set log_archive_dest_2=’SERVICE=dxjcdbs LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dxjcdbs’ scope=spfile;
System altered.
SQL> alter database set standby database to maximize availability;
Database altered.
4.打开主库验证：
SQL> alter database open;
Database altered.
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
————- —————————— —————————— ———————— ——————————
DXJCDB READ WRITE MAXIMUM AVAILABILITY PRIMARY RESOLVABLE GAP
SQL>
5.备库确认：
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
————- —————————— —————————— ———————— ——————————
DXJCDB READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL>
——最好把备库也改一下，以便在主备切换时，依旧是最高可用：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2455228416 bytes
Fixed Size 2255712 bytes
Variable Size 620758176 bytes
Database Buffers 1811939328 bytes
Redo Buffers 20275200 bytes
Database mounted.
SQL>
修改参数
SQL> alter system set log_archive_dest_2=’SERVICE=dxjcdbs LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dxjcdbs’ scope=spfile;
System altered.
SQL> alter database set standby database to maximize availability;
SQL> alter database open;
Database altered.
SQL>
再次确认备库：
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
————- —————————— —————————— ———————— ——————————
DXJCDB READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> alter database set standby database to maximize availability;
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
————- —————————— —————————— ———————— ——————————
DXJCDB READ ONLY WITH APPLY MAXIMUM AVAILABILITY PHYSICAL STANDBY NOT ALLOWED
问题：重启备库后dg模式又变回MAXIMUM PERFORMANCE了。
解决办法在DGMGRL中进行设置。
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxPerformance
Databases:
dxjcdb - Primary database
Error: ORA-16628: broker protection mode inconsistent with the database setting
dxjcdbs - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxPerformance
Databases:
dxjcdb - Primary database
Error: ORA-16628: broker protection mode inconsistent with the database setting
dxjcdbs - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL> edit database dxjcdb set property logxptmode=sync;
DGMGRL> edit database dxjcdbs set property logxptmode=sync;
DGMGRL> edit configuration set protection mode as maxavailability;
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxAvailability
Databases:
dxjcdb - Primary database
dxjcdbs - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
再次确认主备库dg模式：
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
发现都已经变成高可用模式，重启数据库后也保持修改后可用模式。
二：最高可用模式切换为最大保护模式
（因为最高可用模式和最大保护模式对应的参数都是LGWR和SYNC,所以不需要再改参数了）
DGMGRL> edit configuration set protection mode as maxprotection;
Succeeded.
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxProtection
Databases:
dxjcdb - Primary database
dxjcdbs - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
再次确认主备库dg模式：
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
三：最大保护模式转最高性能模式
（最高性能参数LGWR、SYNC或者ASYNC或者ARCH、SYNC，因为上面已经改为LGWR、SYNC，所以不需要再设置了，直接转换）
DGMGRL> edit configuration set protection mode as maxperformance;
Succeeded.
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxPerformance
Databases:
dxjcdb - Primary database
dxjcdbs - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
再次确认主备库dg模式：
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
总结：
a.三种模式在可以在DGbroker中自由切换，正常情况下，数据库能迅速反应过来（查看保护模式的时候）。
b.使用Maximum performance的时候，参数LogXptMode设置为async就好
c.使用Maximum protection或者Maximum availability的时候，参数LogXptMode要设置为sync。
d.（经测试）使用Maximum protection时，当备库出现异常（如网络问题，数据库宕机），主库会挂起（告警日志会报如下错误），当备库恢复正常之后，主库也会恢复。
LGWR: Error 1034 attaching to RFS for reconnect
e.使用Maximum protection时，在当备库出现异常的情况下，当主库挂起时，(主库)可以强制切换到 Maximum performance，但当重新切回Maximum protection（在备库恢复时），主库需重启。
数据库的启动和关闭
由于我们部署的是Oracle ADG，所以在对数据库进行启动和关闭时，是需要注意顺序的。
启动
先启动备库，再启动主库。启动命令如下：
SQL>STARTUP;
关闭
先关闭主库，再关闭备库。关闭命令如下：
SQL>SHUTDOWN IMMEDIATE;
若有收获，就点个赞吧
0 人点赞
