Oracle 11g DataGuard 物理备库配置及Active DataGuard

一、环境介绍
1. 主数据库环境
操作系统版本: Red Hat6.3 x64
数据库版本  : Oracle 11.2.0.3 x64
数据库sid名 : center


2. 备库环境
操作系统版本: Red Hat6.3 x64
数据库版本  : Oracle 11.2.0.3 x64 （只安装oracle数据库软件,no netca dbca）
数据库sid名 : center


3. DataGuard启动顺序
启动顺序：先启备库，后启主库
关闭顺序：先关主库，后关备库


二、主数据库配置
1. 配置hosts文件
# vi /etc/hosts
192.168.22.57 weikeoracle
192.168.22.56 weikestandby


2. 修改配置lisener监听文件
$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC =
  (GLOBAL_DBNAME = weike1)
  (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  (SID_NAME = center)
 )
)
LISTENER =
(DESCRIPTION_LIST =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.57)(PORT = 1521))
 )
)


3. 修改配置tnsname.ora文件
$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
weike1 =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.57)(PORT = 1521))
  )
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = weike1)
  )
 )
weike2 =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.56)(PORT = 1521))
  )
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = weike2)
  )
 )
 
4. 修改配置成规档模式
1)、检查数据库是否处于归档状态
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oradata/center/archive
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42
--------这里数据库的归档已经打开，如果没有打开按下面步骤操作
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;


2)、将主库设置为 FORCE LOGGING 模式
SQL> select force_logging from v$database
FOR
---
YES
--------这里数据库的FORCE LOGGING已经打开，如果没有打开按下面步骤操作
SQL> alter database force logging;


5. 添加standby_redo
1).standby redo log的文件大小与primary 数据库online redo log 文件大小相同
2).standby redo log日志文件组的个数依照下面的原则进行计算
    Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数
    例如在我的环境中，只有一个节点，这个节点有三组redo，所以
    Standby redo log组数公式>=(3+1)*1  == 4
    所以需要创建4组Standby redo log
3).每一日志组为了安全起见，可以包含多个成员文件


alter database add standby logfile group 4 '/u02/oradata/center/primary/redo04.log' size 500M;
alter database add standby logfile group 5 '/u02/oradata/center/primary/redo05.log' size 500M;
alter database add standby logfile group 6 '/u02/oradata/center/primary/redo06.log' size 500M;
alter database add standby logfile group 7 '/u02/oradata/center/primary/redo07.log' size 500M;


alter database add standby logfile member '/u02/oradata/center/primary/redo04_1.log' to group 4;
alter database add standby logfile member '/u02/oradata/center/primary/redo05_1.log' to group 5;
alter database add standby logfile member '/u02/oradata/center/primary/redo06_1.log' to group 6;
alter database add standby logfile member '/u02/oradata/center/primary/redo07_1.log' to group 7;


6. 创建密码文件
orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwcenter password=zdsoft entries=5


7. 修改参数文件
修改参数文件可以直接修改spfile，也可以生成pfile修改
这里我用spfile生成pfile修改
SQL> create pfile from spfile
SQL> shutdown immediate
$ vi /u01/app/oracle/product/11.2.0/db_1/dbs/initcenter.ora
 添加以下内容
 db_unique_name='weike1'
 LOG_ARCHIVE_CONFIG='DG_CONFIG=(weike1,weike2)'
LOG_ARCHIVE_DEST_1='location=/u02/oradata/center/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=weike1'
LOG_ARCHIVE_DEST_2='SERVICE=weike2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=weike2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
#--------for standby
FAL_SERVER=weike2
FAL_CLIENT=weike1
STANDBY_FILE_MANAGEMENT=AUTO


注释掉*.log_archive_dest，因为和LOG_ARCHIVE_DEST_n参数不能同时存在
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcenter.ora'
SQL> create spfile from pfile




三、备库配置
1. 配置hosts文件
# vi /etc/hosts
192.168.22.57 weikeoracle
192.168.22.56 weikestandby


2. 修改配置lisener监听文件
$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC =
  (GLOBAL_DBNAME = weike2)
  (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  (SID_NAME = center)
 )
)
LISTENER =
(DESCRIPTION_LIST =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.56)(PORT = 1521))
 )
)


注意：这里配置的是静态监听，下面在duplicate数据库时启动到nomount状态，nomount状态下数据库不动态去注册监听。
3. 修改配置tnsname.ora文件
$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
weike1 =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.57)(PORT = 1521))
  )
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = weike1)
  )
 )
weike2 =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.56)(PORT = 1521))
  )
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = weike2)
  )
 )


测试服务名连通性：
tnsping weike1
tnsping weike2


4. 创建11g数据库基本目录
mkdir -p /u01/app/oracle/admin/center/adump
mkdir -p /u01/app/oracle/admin/center/bdump
mkdir -p /u01/app/oracle/admin/center/cdump
mkdir -p /u01/app/oracle/admin/center/udump
mkdir -p /u01/app/oracle/admin/center/dpdump
mkdir -p /u01/app/oracle/admin/center/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/center
mkdir -p /u01/app/oracle/product/11.2.0/db_1/dbs
mkdir -p /u02/oradata/flash_recovery_area
mkdir -p /u02/oradata/flash_recovery_area/center
mkdir -p /u02/oradata/center
mkdir -p /u02/oradata/center/archive
mkdir -p /u02/oradata/center/base
mkdir -p /u02/oradata/center/appstore
mkdir -p /u02/oradata/center/passport


5. 修改pfile参数文件
复制主库pfile修改去掉刚才主库加的参数添加一下内容
$ vi /u01/app/oracle/product/11.2.0/db_1/dbs/initcenter.ora
db_unique_name=weike2
LOG_ARCHIVE_CONFIG='DG_CONFIG=(weike1,weike2)'
#LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1='location=/u02/oradata/center/archive/standy VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=weike2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
#---下列参数用于角色切换
LOG_ARCHIVE_DEST_2='SERVICE=weike1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=weike1'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAL_SERVER=weike1
FAL_CLIENT=weike2
STANDBY_FILE_MANAGEMENT=AUTO


6. 创建密码文件
orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwcenter password=zdsoft entries=5


7. 启动到nomount状态
$ sqlplus /nolog
SQL> conn / as sysdba;
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcenter.ora';  
SQL> startup nomount;


四、 开始在RMAN duplicate数据库


1. RMAN同进连接主库与备库
rman target sys/zdsoft@weike1 auxiliary sys/zdsoft@weike2


Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 30 11:27:59 2014


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


connected to target database: CENTER (DBID=4282653214)
connected to auxiliary database: CENTER (not mount)


2. 开始duplicate数据库
RMAN> duplicate target database for standby nofilenamecheck from active database;


3. 查看备库状态
说明：duplicate数据库之后，备库只是处于mount状态，查看备库状态。


SQL> select open_mode,database_role,db_unique_name from v$database;


OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
MOUNTED              PHYSICAL STANDBY weike2


4. 将备库置与应用日志模式状态
SQL> alter database recover managed standby database using current logfile disconnect from session; 
Database altered.  


五、将备库置于Active DataGuard模式


1) 取消备库的自动恢复


SQL> alter database recover managed standby database cancel;


2) OPEN备库为只读模式（Dataguard只能启动到readonly模式）


SQL> alter database open;


3）打开实时应用状态模式


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


SQL> select open_mode,database_role,db_unique_name from v$database;


OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY weike2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26794255/viewspace-1249055/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26794255/viewspace-1249055/