11gData Guard相关参考知识及配置

——Data Guard服务

RTS （Redo Transport Services）(日志传输服务）

--Oracle通过 RTS服务（及redo传输服务）控制redo数据，从Primary数据库发送到其他一个或多个归档目标；按照既定配置传输redo数据到Standby数据库；处理由于网络中断原因导致的归档中断（Gap）。控制数据库的保护模式；检查Standby端丢失或无效的归档，并自动尝试从其他Primary/Standby获取.

LAS （Log Apply Service）（日志应用进程）

--LAS服务（即redo应用服务）主要用来应用redo数据到Standby数据库，以保持Standby数据库与Primary数据库的事物一致。

对于物理Standby，Data Guard使用Redo Apply技术，即通过标准的recover方式应用redo数据。

对应逻辑Standby，Data Guard使用Sql Apply技术，即首先将接受到的redo数据转换成sql语句，然后执行sql语句的方式应用redo数据。

RTS （Redo Transport Services）(日志传输服务）相关进程
主节点上，日志传输服务主要使用如下几个进程：
LGWR

LGWR搜集事务日志，并且更新联机日志。在同步模式下，LGWR直接将redo信息直接传送到备库中的RFS进程，主库在继续进行处理前需要等待备库的确认。在非同步情况下，也是直接将日志信息传递到备库的RFS进程，但是不等待备库的确认信息主库进程可以继续运行处理。

ARCH

ARCHn或者是一个SQL session执行了一个归档操作，为了恢复的需要，创建了一个联机日志的拷贝。Archn进程可以在归档的同时，传递日志流到备库的RFS进程。该进程还用于前瞻性检测和解决备库的日志不连续问题(GAP)。

FAL Fetch archive log
只有物理备库才有该进程。FAL进程提供了一个client/server的机制，用来解决检测在主库产生的连续的归档日志，而在备库接受的归档日志不连续的问题。该进程只有在需要的时候才会启动，而当工作完成后就关闭了，因此在正常情况下，该进程是无法看见的。我们可以设置通过LGWR，ARCH进程去传递日志到备库，但是不能两个进程同时传送。

LAS （Log Apply Service）（日志应用进程）

备库节点上，日志应用进程主要使用如下的进程：

RFS Remote File Server
该进程是standby库接受来自primary库lgwr进程触发的redo信息并且写于到standby redo log中，RFS进程无疑是要和其他进程配合的，也就是传输的过程。我们知道触发同步可能由ARCH或者是LGWR进程触发的，两者是不同的。如果是LGWR进程触发，那10g前的话也是有lGWR进程负责传输redo信息，RFS进程负责接收redo信息写入到standby redo log中，10g之后则由LNSn进程完成；如果是ARCH进程触发，也就是归档日志传输的话，那就是由ARCH进程负责传输，RFS进程负责接收，然后写入指定的归档位置，然后再应用的。

LNSn
LGWR触发以后真正负责传输的，包括初始化网络I/O等一些列功能。

Arch
只对物理备库，arch进程归档备库重做日志，这些日志以后将被MPR进程应用到备库。

MRP managed recovery process

MRP进程只针对物理备库。该进程应用归档日志到备库。如果我们使用SQL语句启用该进程ALTER DATABASE RECOVER MANAGED STANDBY DATABASE，那么前台进程将会做恢复。如果加上disconnect语句，那么恢复过程将在后台进程，发出该语句的进程可以继续做其他的事情。

LSP logical standby process

只有逻辑备库才会有该进程。LSP进程控制着应用归档日志到逻辑备用数据库。

——角色转换

switchover：Primary数据库和Standby数据库之间的角色切换，如将Primary数据库转换成Standby数据库，将Standby数据库转换成Primary数据库,switchover可以确保不会丢失数据。

failover：当Primary数据库出现故障并且不能恢复时，就需要通过failover将DG配置的其中一个Standby数据库转换为新的Primary数据库。在最大保护模式或最高可用性模式下，failover也可以保证不会丢失数据。

——三种保护模式

最大保护（Maximum Protection)。

这种模式能够确保绝无数据丢失，它要求所有的事物在提交前将redo不仅被写入到本地的Online Redologs，还要同时写入到Standby数据库的Standby Redologs，并确认redo数据至少在一个standby数据库中可用，原后才会在Primary数据库上提交，如果出现了什么问题导致Standby数据库不可用的话，Primary数据库会被shutdown。

最大性能（Maximum performance）。

这种模式在不影响Primary数据库性能前提下，提供最高级别的数据保护策略。事物可以随时提交，当前Primary数据库的redo数据至少需要写入一个Standby数据库，不过这种写入可以是不同步的。如果网络条件理想的话，这种模式能够提供类似最高可用性的数据保护，而仅对Primary数据库的性能有轻微影响。这也是创建Standby数据库时，系统的默认包含模式。

最高可用性（Maximum availability）。

这种模式在不影响Primary数据库可用前提下，提供最高级别的数据保护策略。其实现方式与最大保护模式类似，也是要求本地事物在提交前必须至少写入一台Standby数据库的Standby Redologs中，不过与最大保护模式不同的是，如果出现故障导致Standby数据库无法访问，Primary数据库并不会被Shutdown，而是自动转为最高性能模式，等Standby数据库恢复正常后，Primary数据库又会自动转换成最高可用性模式。

配置Data Guard

PRIMARY database: PROD host：enmoedu1
STANDBY database: ENMO host：enmoedu2
VERSION：11.2.0.4.0

开启归档及最小附加日志
[oracle@enmoedu1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 13 19:04:48 2016

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

SYS@PROD>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SYS@PROD>alter database force logging;

Database altered.

添加3组日志
SYS@PROD>alter database add standby logfile group 4 '/u01/app/oracle/oradata/PROD/standby_redo04.log' size 50m;

Database altered.

SYS@PROD>alter database add standby logfile group 5 '/u01/app/oracle/oradata/PROD/standby_redo05.log' size 50m;

Database altered.

SYS@PROD>alter database add standby logfile group 6 '/u01/app/oracle/oradata/PROD/standby_redo06.log' size 50m;

Database altered.

SYS@PROD>select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/redo03.log
/u01/app/oracle/oradata/PROD/redo02.log
/u01/app/oracle/oradata/PROD/redo01.log
/u01/app/oracle/oradata/PROD/standby_redo04.log
/u01/app/oracle/oradata/PROD/standby_redo05.log
/u01/app/oracle/oradata/PROD/standby_redo06.log

创建参数文件
SYS@PROD>create pfile from spfile;

File created.

[oracle@enmoedu1 ~]$ cd $ORACLE_HOME/dbs
[oracle@enmoedu1 dbs]$ vi initPROD.ora

PROD.__db_cache_size=687865856
PROD.__java_pool_size=16777216
PROD.__large_pool_size=33554432
PROD.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD.__pga_aggregate_target=671088640
PROD.__sga_target=989855744
PROD.__shared_io_pool_size=0
PROD.__shared_pool_size=234881024
PROD.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PROD/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/PROD/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PROD'
*.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=PRODXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1656750080
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME=PROD
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,ENMO)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/fast_recovery_area
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PROD'
LOG_ARCHIVE_DEST_2=
'SERVICE=ENMO ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ENMO'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=ENMO
DB_FILE_NAME_CONVERT='ENMO','PROD'
LOG_FILE_NAME_CONVERT='ENMO','PROD'
STANDBY_FILE_MANAGEMENT=AUTO

[oracle@enmoedu1 dbs]$ sqlplus / as sysdba

SYS@PROD>create spfile from pfile;

File created.

SYS@PROD>startup mount;
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
Database mounted.

传输参数文件和口令文件

[oracle@enmoedu1 dbs]$ scp initPROD.ora oracle@enmoedu2:$ORACLE_HOME/dbs/initENMO.ora

oracle@enmoedu2's password:
initPROD.ora 100% 1488 1.5KB/s 00:00

[oracle@enmoedu1 dbs]$ scp orapwPROD oracle@enmoedu2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwENMO
oracle@enmoedu2's password:
orapwPROD 100% 1536 1.5KB/s 00:00

编辑备库参数文件及目录
[oracle@enmoedu2 dbs]$ vi initENMO.ora
ENMO.__db_cache_size=687865856
ENMO.__java_pool_size=16777216
ENMO.__large_pool_size=33554432
ENMO.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ENMO.__pga_aggregate_target=671088640
ENMO.__sga_target=989855744
ENMO.__shared_io_pool_size=0
ENMO.__shared_pool_size=234881024
ENMO.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ENMO/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/ENMO/control01.ctl','/u01/app/oracle/fast_recovery_area/ENMO/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PROD'
*.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=ENMOXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1656750080
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME=ENMO
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ENMO,PROD)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/fast_recovery_area
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ENMO'
LOG_ARCHIVE_DEST_2=
'SERVICE=PROD ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PROD'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=PROD
DB_FILE_NAME_CONVERT='PROD','ENMO'
LOG_FILE_NAME_CONVERT='PROD','ENMO'
STANDBY_FILE_MANAGEMENT=AUTO

[oracle@enmoedu2 ~]$ mkdir -p /u01/app/oracle/admin/ENMO/adump
[oracle@enmoedu2 ~]$ mkdir -p /u01/app/oracle/oradata/ENMO/
[oracle@enmoedu2 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/ENMO

[oracle@enmoedu2 dbs]$ export ORACLE_SID=ENMO
[oracle@enmoedu2 dbs]$ sqlplus / as sysdba
Connected to an idle instance.

SYS@ENMO>create spfile from pfile;

File created.

SYS@ENMO>startup nomount;
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes

配置监听
[oracle@enmoedu1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu1)(PORT = 1521))
)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = PROD)))
[oracle@enmoedu2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu2)(PORT = 1521))
)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =ENMO)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =ENMO)))
[oracle@enmoedu1 admin]$ cat tnsnames.ora

[oracle@enmoedu2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)

ENMO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ENMO)
)
)

使用duplicate命令生成standby备库
[oracle@enmoedu1 admin]$ rman target / auxiliary sys/oracle@ENMO

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 13 20:46:54 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD (DBID=312886085, not open)
connected to auxiliary database: PROD (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 2016-01-13 20:58:30
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwPROD' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwENMO' ;
}
executing Memory Script

Starting backup at 2016-01-13 20:58:31
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Finished backup at 2016-01-13 20:58:32

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/ENMO/control01.ctl';
restore clone controlfile to '/u01/app/oracle/fast_recovery_area/ENMO/control02.ctl' from
'/u01/app/oracle/oradata/ENMO/control01.ctl';
}
executing Memory Script

Starting backup at 2016-01-13 20:58:32
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_PROD.f tag=TAG20160113T205832 RECID=7 STAMP=901054713
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2016-01-13 20:58:35

Starting restore at 2016-01-13 20:58:35
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2016-01-13 20:58:36

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
"/u01/app/oracle/oradata/ENMO/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/ENMO/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/ENMO/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/ENMO/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/ENMO/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/ENMO/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/ENMO/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/ENMO/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/ENMO/users01.dbf" ;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/ENMO/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 2016-01-13 20:58:42
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD/system01.dbf
output file name=/u01/app/oracle/oradata/ENMO/system01.dbf tag=TAG20160113T205842
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD/sysaux01.dbf
output file name=/u01/app/oracle/oradata/ENMO/sysaux01.dbf tag=TAG20160113T205842
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=/u01/app/oracle/oradata/PROD/undotbs01.dbf
output file name=/u01/app/oracle/oradata/ENMO/undotbs01.dbf tag=TAG20160113T205842
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/users01.dbf
output file name=/u01/app/oracle/oradata/ENMO/users01.dbf tag=TAG20160113T205842
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2016-01-13 20:59:25

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=901054765 file name=/u01/app/oracle/oradata/ENMO/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=901054765 file name=/u01/app/oracle/oradata/ENMO/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=901054765 file name=/u01/app/oracle/oradata/ENMO/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=901054765 file name=/u01/app/oracle/oradata/ENMO/users01.dbf
Finished Duplicate Db at 2016-01-13 20:59:31

查看角色
SYS@PROD>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PRIMARY

SYS@ENMO>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY

启动实时应用
SYS@ENMO>alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SYS@ENMO>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG;

SEQUENCE# APPLIED
---------- ------------------
3 YES
2 YES
4 YES

启用Switchover角色互换
SYS@PROD>select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
TO STANDBY

SYS@PROD>alter database commit to switchover to PHYSICAL STANDBY;

Database altered.
SYS@PROD>conn / as sysdba
Connected to an idle instance.
SYS@PROD>startup mount;
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
Database mounted.

SYS@PROD>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY

SYS@ENMO>select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
RECOVERY NEEDED

SYS@ENMO>alter database recover managed standby database disconnect from session;

Database altered.

SYS@ENMO>select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
TO PRIMARY

SYS@ENMO>alter database commit to switchover to PRIMARY;

Database altered.

SYS@ENMO>alter database open;

Database altered.

SYS@ENMO>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PRIMARY

相关命令

检查归档文件是否完整；

select distinct THREAD#,MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) A FROM V$ARCHIVED_LOG;

检查归档文件是否连续

select * from v$archive_gap;

查看日志是否应用

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG;

查看数据库当前模式

select open_mode from v$database;

查看实例状态

select status from v$instance;

查看数据库当前角色

select DATABASE_ROLE from v$database;

验证是否支持switchover操作

select SWITCHOVER_STATUS from v$database;

将Primary 数据库转换为 Standby角色

alter database commit to switchover to physical standby;

转换Standby数据库为Primary角色

alter database commit to switchover to primary;

查看当前归档日志数量

select max(sequence#) from v$archived_log;

启动redo应用

alter database recover managed standby database disconnect from session；

启动redo实时应用

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

启动failover

alter database recover managed standby database finish force；

转换Standby数据库到Maximize Performance模式

alter database set standby database to maximize performance；

修改Standby数据库到Maximize Availability

alter database set standby database to maximize availability；

ITPub博客

11gDataguard概念及创建

最新文章