Oracle 11g DataGuard Physical Standby 测试环境搭建 For windows

参考了官方文档和网上资料，自己也做了一下在windows平台上关于Oracle 11g的测试环境搭建，把其中的过程和碰见问题后怎么解决的记录下来跟大家分享。

测试环境：

操作系统平台：Windows7 旗舰版64位

Oracle版本：11.2.0.1.0 32bit （32位版本装在64位平台日志文件会出现很多O/S-Error的错误）

机器：Primary（sid：orcldb）、Standby（sid：orcldb)

DataGuard类型：Physical STANDBY

官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#SBYDB00200

一、搭建步骤：

  1，安装数据库软件

  2，主库primary配置

  3，备库standby配置

  4，测试DG

  5，测试Active DataGuard

  6，测试switchover

1.1 安装数据库软件

  使用安装包就行了，安装完数据库软件以后，使用dbca建立主库Primary。此处略。

1.2 主库primary配置

配置监听和tnsnames，使用netca新建listener并编辑listener.ora和tnsnames.ora，文件路径Oracle_home\NETWORK\ADMIN。

listener.ora

# listener.ora Network Configuration File: e:\app\admin\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = e:\app\admin\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:e:\app\admin\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
       (GLOBAL_DBNAME = orcldb)
       (ORACLE_HOME = e:\app\admin\product\11.2.0\dbhome_1)  
       (SID_NAME = orcldb)
      )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.110)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

配置完后在dos下重启监听：

lsnrctl stop

lsnrctl start

listener.ora配置完成。

tnsnames.ora

用Net manager添加主库和备库的tns。然后主库备库互相tnsping测试是否相同：

PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldb)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.110)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldb)
    )
  )

ADR_BASE_LISTENER = E:\app\admin

tnsping primary

tnsping standby

tnsnames配置完成。

设置主库归档模式，查看是否为归档：

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\app\admin\oradata\archive
Oldest online log sequence     73
Next log sequence to archive   75
Current log sequence           75

如果为Disabled，执行：
SQL> alter database archivelog;

查看并设置设置强制归档：

SQL> alter database force logging;

SQL> select force_logging from v$database;
 
FORCE_LOGGING
-------------
YES

主库归档配置完成。

添加主库standby联机日志，Oracle建议比在线日志多一组，这里我只添加了3组：

SQL>alter database add standby logfile group 4'E:\app\admin\oradata\ORCL\ONLINELOG\redo04.log' size 50m;

SQL>alter database add standby logfile group 5'E:\app\admin\oradata\ORCL\ONLINELOG\redo05.log' size 50m;

SQL>alter database add standby logfile group 6'E:\app\admin\oradata\ORCL\ONLINELOG\redo06.log' size 50m;

编辑主库参数文件：

创建主库pfile

SQL> create pfile='e:\primary.ora' from spfile;

File created.

打开primary.ora添加以下参数：

*.db_name='orcl'

*.log_archive_format='ARC_%T%S%r.ARC'

*.DB_UNIQUE_NAME='primary'

*.log_archive_config='DG_CONFIG=(primary,standby)'

*.log_archive_dest_1='location=E:\app\admin\oradata\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'

*.log_archive_dest_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='standby'

*.FAL_CLIENT='primary'

*.STANDBY_FILE_MANAGEMENT=AUTO

注意这里log_archive_dest_2.后service是为OracleNet的tnsnames。

注：其中可能需要修改控制文件的数量等，在配置的时候我新增了2组控制文件，但是在参数文件忘记修改控制文件路径了，启动的时候oracle会报错，如果要修改控制文件的话记得把参数文件的路径也一并修改了。

保存退出。

Oracle官网关于参数的解释：

Parameter	Recommended Setting
DB_NAME	On a primary database, specify the name used when the database was created. On a physical standby database, use theDB_NAME of the primary database.
DB_UNIQUE_NAME	Specify a unique name for each database. This name stays with the database and does not change, even if the primary and standby databases reverse roles.
LOG_ARCHIVE_CONFIG	The DG_CONFIG attribute of this parameter must be explicitly set on each database in a Data Guard configuration to enable full Data Guard functionality. SetDG_CONFIG to a text string that contains the DB_UNIQUE_NAME of each database in the configuration, with each name in this list separated by a comma.
CONTROL_FILES	Specify the path name for the control files on the primary database.Example 3-1 shows how to do this for two control files. It is recommended that a second copy of the control file is available so an instance can be easily restarted after copying the good control file to the location of the bad control file.
LOG_ARCHIVE_DEST_n	Specify where the redo data is to be archived on the primary and standby systems. InExample 3-1: LOG_ARCHIVE_DEST_1 archives redo data generated by the primary database from the local online redo log files to the local archived redo log files in /arch1/chicago/. LOG_ARCHIVE_DEST_2 is valid only for the primary role. This destination transmits redo data to the remote physical standby destinationboston. Note: If a fast recovery area was configured (with theDB_RECOVERY_FILE_DEST initialization parameter) and you have not explicitly configured a local archiving destination with theLOCATION attribute, Data Guard automatically uses the LOG_ARCHIVE_DEST_1 initialization parameter (if it has not already been set) as the default destination for local archiving. Also, seeChapter 15 for complete LOG_ARCHIVE_DEST_n information.
LOG_ARCHIVE_DEST_STATE_n	Specify ENABLE to allow redo transport services to transmit redo data to the specified destination.
REMOTE_LOGIN_PASSWORDFILE	This parameter must be set toEXCLUSIVE orSHARED if a remote login password file is used to authenticate administrative users or redo transport sessions.
LOG_ARCHIVE_FORMAT	Specify the format for the archived redo log files using a thread (%t), sequence number (%s), and resetlogs ID (%r).
FAL_SERVER	Specify the Oracle Net service name of the FAL server (typically this is the database running in the primary role). When the Chicago database is running in the standby role, it uses the Boston database as the FAL server from which to fetch (request) missing archived redo log files if Boston is unable to automatically send the missing log files.
DB_FILE_NAME_CONVERT	Specify the path name and filename location of the standby database datafiles followed by the primary location. This parameter converts the path names of the primary database datafiles to the standby datafile path names. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site, then this parameter is required. Note that this parameter is used only to convert path names for physical standby databases. Multiple pairs of paths may be specified by this parameter.
LOG_FILE_NAME_CONVERT	Specify the location of the standby database online redo log files followed by the primary location. This parameter converts the path names of the primary database log files to the path names on the standby database. If the standby database is on the same system as the primary database or if the directory structure where the log files are located on the standby system is different from the primary system, then this parameter is required. Multiple pairs of paths may be specified by this parameter.
STANDBY_FILE_MANAGEMENT	Set to AUTO so when datafiles are added to or dropped from the primary database, corresponding changes are made automatically to the standby database.

使用新编辑的primary.ora启动数据库

SQL> shutdown immediate;

SQL> startup pfile='e:\primary.ora' mount;

SQL> create spfile from pfile=‘e:\primary.ora';  -- 写入spfile

再关闭重启实例，重建多一次pfile

SQL> create pfile='e:\std.ora' from spfile; 

修改std.ora

*.db_name='orcl'

*.log_archive_format='ARC_%T%S%r.ARC'

*.DB_UNIQUE_NAME='standby'

*.log_archive_config='DG_CONFIG=(primary,standby)'

*.log_archive_dest_1='location=E:\app\admin\oradata\archiveVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

*.log_archive_dest_2='SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='primary'

*.FAL_CLIENT=standby''

*.STANDBY_FILE_MANAGEMENT=AUTO

注意这里log_archive_dest_2.后service是为OracleNet的tnsnames。

保存退出。

创建standby controlfile

SQL> alter database create standby controlfile as 'e:\std.ctl';

改名并复制3份，control01.ctl  control02.ctl  control03.ctl

启动主数据库

SQL> alter database open;

1.3，备库standby配置

准备主库的standby控制文件、std.ora参数文件、密码文件（ORACLE_HOME\database\PWDsid.ora）复制到备库，所有数据文件、联机日志文件复制到备库（需关闭主库实例后手工复制，如不停机需要用rman进行备库恢复），保证主库备库的目录结构一致。

Rman恢复参考：

主库备份：

rman target /

RMAN> backup full format 'E:/FULL_%d_%T_%s.bak' database include current controlfile for standby;

RMAN> sql 'alter system archive log current';

RMAN> Backup ArchiveLog all format='E:/arch_%d_%T_%s.bak'; 

恢复：

把备份文件拷贝到备库E盘

$rman target sys/pwd@primary auxiliary /

RMAN> duplicate target database for standby dorecover nofilenamecheck;

注：之后再测试环境主库执行open resetlogs后重建DG用了RMAN恢复备库的方法，很效率，推荐使用！rman恢复就重建完毕了，可以测试。

配置listener和tnsnames，和主库一致就可以了。

DOS下备库实例建立：

oradim.exe -new -sid orcldb -startmode m

注意：查看系统Oracle服务的listener和实例服务是否为自动启动，本人疏忽了这2个地方每次在重启都会出现一些莫名其妙的问题查了半天原来是自己没设置好。

使用std.ora启动备库

SQL> startup pfile='e:\std.ora' nomount;

SQL> create spfile from pfile='e:\std.ora';

SQL> shutdown immediate;

SQL> startup nomount;

SQL> alter database mount standby database;

查看日志路径：

SQL> select * from v$log;

设置应用归档

SQL> alter database recover managed standby database disconnect from session;

取消应用归档

SQL> alter database recover managed standby database cancel;

备库配置完成。

1.4 测试DG

DG 测试环境搭建完成，测试是否正常：

在主库查看是否有错误，有错误可能是参数文件没有配置正确，我之前试过一次是备库参数文件没有修改好(主要是一些TNS的问题)：

SQL> SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;

在主库切换日志，查看备库归档路径是否有最新的归档日志传送过来。

SQL> Alter system switch logfile;

主库备库是否一致：

SQL> select max(sequence#) from v$log_history;
 
MAX(SEQUENCE#)
--------------
            58

SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
            58

查看日志传输情况：

SQL> SELECT PROCESS,CLIENT_PROCESS,SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

查看归档应用情况：

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG;

 SEQUENCE# APPLIED
---------- ---------
        56 YES
        57 YES
        57 YES
        58 YES

监控进程归档应用情况：

SQL>  SELECT APPLIED_SCN,LATEST_SCN FROM V$LOGSTDBY_PROGRESS;

PROCESS   CLIENT_PROCESS  SEQUENCE# STATUS
--------- -------------- ---------- ------------
ARCH      ARCH                   77 CLOSING
ARCH      ARCH                   78 CLOSING
ARCH      ARCH                    0 CONNECTED
ARCH      ARCH                    0 CONNECTED
RFS       N/A                     0 IDLE
RFS       UNKNOWN                 0 IDLE
MRP0      N/A                    79 WAIT_FOR_LOG
RFS       UNKNOWN                 0 IDLE

DG测试成功。

1.5，测试Active DataGuard（11g新功能，能打开DB进行实时查询）

备库执行，取消应用归档

SQL>alter database recover managed standby database cancel;

打开数据库Read only模式（Oracle会检测到备库为物理standby，会启动备库ReadOnly模式）

SQL>alter database open;

修改备库模式为收到日志后完成恢复

SQL>alter database recover managed standby database using current logfile disconnect;

查看数据库打开状态

SQL>select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

测试实时查询时在主库执行建立Tablespace和数据文件，然后再建立表和表数据等，提交完了之后再备库查询是否能实施查询到，如果可以就成功了。在执行的时候可以查看主库的alert.log文件，在备库也查询此文件可以看出变化。

测试执行DML语句后在备库实时查询，这里我测试的情况不是实时查询的，一定要主库切换日志备库应用归档之后才能查询到，如果遇到这个问题解决了的话留言分享一下(问题解决了，是我在配置参数文件的时候没有添加standby_file_management的auto值,导致执行创建表空间和数据文件同步到备库时出错具体可以看http://blog.csdn.net/seulk_q/article/details/42092357)。

 

在主库验证归档目录是否有效：

SQL> SELECT STATUS,DESTINATION, ERRORFROM V$ARCHIVE_DEST;

ACTIVE DATAGUARD测试成功。

DG搭建常用命令：

select max(sequence#) from v$log_history;

select max(sequence#) from v$archived_log;

SELECT STATUS,DESTINATION,ERROR FROM V$ARCHIVE_DEST;

alterdatabase recover managed standby database using current logfile disconnect;

alterdatabase recover managed standby database cancel;

SELECT PROCESS,CLIENT_PROCESS,SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG;

SELECT APPLIED_SCN,LATEST_SCN FROM V$LOGSTDBY_PROGRESS;

alter system switch logfile;

6，测试switchover和failover

switchover

主库上查看角色切换是否能够满足

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

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

注意：备库在主库做切换之前SWITCHOVER_STATUS表现为not_allowed

 

主库执行物理备库切换

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICALSTANDBY WITH SESSION SHUTDOWN;

SQL> SHUTDOWN ABORT;

SQL> STARTUP MOUNT;

查看原主库状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

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

取消备库应用归档

SQL> alter database recover managedstandby database cancel;

 Database altered.

 

SQL> SELECT SWITCHOVER_STATUS FROMV$DATABASE;

 SWITCHOVER_STATUS

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

SWITCHOVER PENDING

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVERTO PRIMARY WITH SESSION SHUTDOWN;

ALTER DATABASE COMMIT TO SWITCHOVER TOPRIMARY WITH SESSION SHUTDOWN

*

ERROR at line 1:

ORA-16139: 需要介质恢复

 

SQL> alter database recover managedstandby database disconnect from session;

SQL> alter database recover managedstandby database cancel;

SQL> SELECT SWITCHOVER_STATUS FROMV$DATABASE;

 SWITCHOVER_STATUS

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

TO PRIMARY

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVERTO PRIMARY WITH SESSION SHUTDOWN;

 Database altered.

 

SQL> alter database open;

 Database altered.

 

SQL> SELECT SWITCHOVER_STATUS FROMV$DATABASE;

 SWITCHOVER_STATUS

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

TO STANDBY

主库备库切换成功。

小结：虽然在官方文档上步骤上说明上都写得很清楚，但是具体为什么要这样做还是值得去考究的，比如说standby联机日志为什么要比联机日志多一组等，还是要通过不断学习和做实验来增加经验才是可行的办法。