Orackle 10g 数据库DataGuard配置规范

Orackle 10g 数据库DataGuard配置规范

目录：

第一部分：文档信息

第二部分：配置DataGuard需要的硬件和系统要求

第三部分：Oracle10g的DataGuard的几个概念

第四部分: 创建物理备用数据库和启动DataGuard的具体步骤

第五部分: 对DataGuard日常监控的要求

第六部分: 备用数据库的角色切换步骤

第七部分: 在最大性能模式下的DataGuard配置步骤

第八部分: 在最大可用模式下的DataGuard配置步骤

第九部分: 在最大保护模式下的DataGuard配置步骤

第十部分:  DataGuard下跟踪到的等待事件

第十一部分: 如何做到保护数据

第一部分：文档信息

序号	行动类型	人员	修改日期	修改原因	审核人员	备注
1	创建	彭上坤	2008-2-26
2
3

本文档是针对oracle10g数据库的physical stanby数据库的DataGuard配置的。

 

第二部分：配置数据库DataGuard的硬件和系统要求

 

1、 主数据库服务和备用的数据库服务器需要使用相同或相近的硬件。

2、 主数据库服务和备用的数据库服务器需要使用相同的操作系统（补丁包也相同）。

3、 主数据库服务和备用的数据库服务器需要使用相同的oracle数据库版本（包括补丁包）。

第三部分：Oracle10g的DataGuard的几个概念

1、备用数据库上的RFS进程

物理备用数据库上的RFS进程是接收主数据库上传到的redo data,并写入物理备用数据库的archivelog或standby redo log的进程。

Redo data transmitted from the primary database is received by the remote file server (RFS) process on the standby system where the RFS process writes the redo data to archived log files or standby redo log files.

 

2、Standby redo log的概念

A standby redo log is similar in all ways to an online redo log, except that a standby redo log is used only when the database is running in the standby role to store redo data received from the primary database.

在下列的情况下，必须使用standby redo log文件:

• The maximum protection and maximum availability levels of data protection
• Real-time apply
• Cascaded redo log destinations

使用standby redo log有如下优点:

The archiver (ARCn) process or the log writer (LGWR) process on the primary database can transmit redo data directly to remote standby redo log files, potentially eliminating the need to register a partial archived log file (for example, to recover after a standby database crashes).

如何配置standby redo log

(1)    确保主备数据库上的redo log file的大小相同（或备机的redo log比主机的大）

(2)    确定standby redo log group的数量

(maximum number of logfiles for each thread + 1) * maximum number of threads

(3)    使用alter database add standby logfile 语句在备用数据库上增加standby redo log

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10

    ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

(4) 可以在V$STANDBY_LOG查看了standby redo log

 

3、物理备用数据库上没有online redo log的.

Physical standby databases do not have an associated online redo log, because physical standby databases are never opened for read/write I/O; changes are not made to the database and redo data is not generated.

4、在DataGuard环境下，redo data传到物理备用数据库的机制

(1)          ARCn进程来传送redo data到备用数据库.

ARCn进程来传送redo data只在用在最大性能模式的DataGuard环境(maximum performance), ARCn进程在什么时间传送redo data是一个参数来控制的:这个参数就是: LOG_ARCHIVE_LOCAL_FIRST

在oracle10.2.0.2版本中，缺省情况下:ARC进程如何传送redo data的: (在10.1.版本以前，缺省情况下不是这样的)

Archiving to Local Destinations Before Archiving to Remote Destinations

 

 

Specifying LOG_ARCHIVE_LOCAL_FIRST=FALSE is most useful for faster network connections

Archiving to Local and Remote Destinations at the Same Time

 

 

 

(2)          LGWR进程来传送redo data到备用数据库

LGWR进程传送redo data也分成两种情况: SYNC和A SYNC

LGWR  SYNC (同步模式)  Transactions are not committed on the primary database until the redo data necessary to recover the transactions is received by the destination.

 

LGWR SYNC Archival to a Remote Destination with Standby Redo Log Files

(可以采用类似如下的配置方式: LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR SYNC NET_TIMEOUT=30')

A log switch on the primary database triggers a log switch on the standby database, causing ARCn processes on the standby database to archive the standby redo log files to archived redo log files on the standby database. Then, log apply services use Redo Apply (MRP process) or SQL Apply (LSP process) to apply the redo data to the standby database.

If real-time apply is enabled, Data Guard recovers redo data directly from the current standby redo log file as it is being filled up by the RFS process.

 

 

LGWR ASYNC模式

LGWR ASYNC Archival with Network Server (LNSn) Processes

 

 

5、在DataGuard环境下，redo data应用到物理备用数据库的机制

(1)     Applying Redo Data to a Standby Destination Using Real-Time Apply

As the remote file server (RFS) process writes the redo data to standby redo log files on the standby database, log apply services can recover redo from standby redo log files as they are being filled.

如何设置real-time apply呢?

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE

如何查看是否设置了real-time apply

RECOVERY_MODE column in the V$ARCHIVE_DEST_STATUS view. It will display MANAGED REAL-TIME APPLY when real-time apply is enabled.

 

(2) 管理物理备机上的redo apply ?

a.启动redo apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE [disconnect]

 

b.  启动实时redo apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

 

c.  停止redo apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

 

d.  查看redo apply的状态

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

 

e.  如何提高物理备用数据库上redo apply的效率.

RECOVER STANDBY DATABASE PARALLEL #CPUs * 2;

Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096

Tune Network I/O

DISK_ASYNCH_IO to TRUE (the default).

 

6、在DataGuard环境下，三种保护数据的模式

DataGuard的三种保护模式

Table 5-2  Minimum Requirements for Data Protection Modes

	Maximum Protection	Maximum Availability	Maximum Performance
Redo archival process	LGWR	LGWR	LGWR or ARCH
Network transmission mode	SYNC	SYNC	SYNC or ASYNC when using LGWR process. SYNC if using ARCH process
Disk write option	AFFIRM	AFFIRM	AFFIRM or NOAFFIRM
Standby redo log required?	Yes	Yes	Optional, but recommended

 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE}

 

可以在V$DATABASE中查看到DataGuard的保护模式

SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;

 

下面这个用法

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE ;   (设置real-time apply redo log)

 

7、在DataGuard环境下，几个重要参数的说明

 (1)  LOG_ARCHIVE_CONFIG

By default, the LOG_ARCHIVE_CONFIG parameter enables the database to send and receive redo; after a role transition, you may need to specify these settings again using the SEND, NOSEND, RECEIVE, or NORECEIVE keywords.

 (2) LOG_ARCHIVE_LOCAL_FIRST

The LOG_ARCHIVE_LOCAL_FIRST initialization parameter controls when the archiver processes (ARCn) transmit redo data to remote standby database destinations. The following table describes possible values for this parameter.

(3) LOG_ARCHIVE_MAX_PROCESSES

LOG_ARCHIVE_MAX_PROCESSES initialization parameter (the default setting is 2).

(4)  STANDBY_ARCHIVE_DEST 参数（在物理备用数据库上）

(5) LOG_ARCHIVE_TRACE  (设置这个参数跟踪 redo log的传送过程)

（6）standby_file_management参数（注意这个参数在10.2.0.2中缺省是manaul,需要修改成auto）

SQL> alter system set standby_file_management=auto scope=both;

System altered.

 

8、在DataGuard环境下，管理Archive Gaps

第四部分：创建物理备用数据库和启动DataGuard的具体步骤

1、    以下列的配置为例来说明(单实例的普通数据库)

数据库名称DB_NAME为 oradb (主备数据库的db_name、oracle_sid和instance­_name都是oradb)

数据库类型	DB_UNIQUE_NAME	SERVICE_NAMES	Tnsnames.ora中的net service name
Primary	Dg_pri	Dg_pri	Dg_pri
Physical standby	Dg_sec	Dg_sec	Dg_sec

 

2、    修改主数据库的日志模式为force logging （alter database force logging;）

3、    确认password file文件（如果不存在就用orapwd创建一个）

4、    修改主数据库的参数

SQL> alter system set db_unique_name='dg_pri' scope=spfile;

SQL> alter system set service_names='dg_pri' scope=spfile;

SQL> alter system set log_archive_config='DG_CONFIG=(dg_pri,dg_sec)' scope=both;

 

SQL> alter system set log_archive_dest_1='location=/oradata/oradb/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg_pri' scope=spfile;

SQL> alter system set log_archive_dest_2='service=dg_sec LGWR SYNC AFFIRM NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg_sec'

 scope=spfile;

 

SQL> alter system set standby_file_management=auto scope=both;

System altered.

 

SQL> alter system set fal_server='dg_sec' scope=both;

System altered.

SQL> alter system set fal_client='dg_pri' scope=both;

System altered.

 

5、    对主数据库做一个备份（最好用rman备份或冷备份也可以）

6、    创建物理备用数据库的standby control file

把主数据库启动到mount状态

SQL> alter database create standby controlfile as '/tmp/control01.ctl';

Database altered.

 

7、    为物理备用数据库准备pfile文件

SQL> create pfile='/tmp/init.dg_sec.ora' from spfile;

修改vi /tmp/init.dg_sec.ora

*.db_unique_name='dg_sec'

*.fal_client='dg_sec'

*.fal_server='dg_pri'

*.service_names='dg_sec'

*.log_archive_dest_1='location=/oradata/oradb/sec_archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=dg_sec'

*.log_archive_dest_2='service=dg_pri LGWR SYNC AFFIRM NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg_pri'

还要修改一个参数STANDBY_ARCHIVE_DEST(在Maximum Performance模式，在物理备用数据库上配置的,在备机上接收到主机传来的archivelog日志的接收目录)

8、    把standby control 文件、pfile文件和密码文件拷贝到备用数据库上。

(在备用数据库上创建dump相关的目录)

9、    把物理备用数据库启动到mount状态,创建standby redo log group(group的size与主数据库的完全相同,group的个数是主数据库的的online redo log的2倍或更多).

SQL> start mount

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE

--------------------------------

PHYSICAL STANDBY

 

SQL> alter database add standby logfile group 10 ('/oradata/oradb/oradb/sec_redo01.log' ) size 50M;

Database altered.

SQL> alter database add standby logfile group 11 ('/oradata/oradb/oradb/sec_redo02.log' ) size 50M;

Database altered.

SQL> alter database add standby logfile group 12 ('/oradata/oradb/oradb/sec_redo03.log' ) size 50M;

Database altered.

SQL> alter database add standby logfile group 13 ('/oradata/oradb/oradb/sec_redo04.log' ) size 50M;

Database altered.

SQL> alter database add standby logfile group 14 ('/oradata/oradb/oradb/sec_redo05.log' ) size 50M;

Database altered.

SQL> alter database add standby logfile group 15 ('/oradata/oradb/oradb/sec_redo06.log' ) size 50M;

Database altered.

 

10、  配置主备数据库的listener和tnsnames.ora文件

主数据库上的listener.ora的配置:

SID_LIST_DG =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = oradb)

      (ORACLE_HOME = /u01/oracle/product/db10gr2)

      (SID_NAME = oradb)

    )

  )

 

DG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.14.47)(PORT = 18001))

  )

 

物理备用数据库上的listener.ora文件

SID_LIST_DG =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = oradb)

      (ORACLE_HOME = /u01/oracle/product/db10gr2)

      (SID_NAME = oradb)

    )

  )

 

DG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.14.50)(PORT = 18001))

  )

 

 

 

主备数据库的tnsnames.ora文件:

DG_PRI =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.14.47)(PORT = 18001))

    )

    (CONNECT_DATA =

      (SID = oradb)

      (SERVER = DEDICATED)

    )

  )

 

DG_SEC =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.14.50)(PORT = 18001))

    )

    (CONNECT_DATA =

      (SID = oradb)

      (SERVER = DEDICATED)

    )

  )

 

11、  在主数据库上设置DataGuard的保护模式.把主数据库启动到mount状态设置好DataGuard的保护模式. 设置好保护模式后，再把主数据库启动到open状态。（是否设置好DataGuard的保护模式后再创建standby controlfile?）。

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE  AVAILABILITY;

查看DataGuard的保护模式

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;

 

12、  在物理备用数据库上启动日志传输

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE  AVAILABILITY;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;

 

Database altered.

 

13、   

第五部分： 对DataGuard日常监控的要求

1、   日常查看redo log是否被传送到物理备用数据库

在主数据库上查看，redo log是否被送到归档的位置.

SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#

  2> FROM V$ARCHIVE_DEST_STATUS

  3> WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

  在主数据库上查看，Find out if archived redo log files have been received.

 

  SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM

  2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)

  3> LOCAL WHERE

  4> LOCAL.SEQUENCE# NOT IN

  5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND

  6> THREAD# = LOCAL.THREAD#)