Oracle 11g Dataguard Duplicate物理备库配置(一)之物理备库创建配置
# ver:1.5 第五次修改
# modify: 2013.8.16
# author: koumm
相关阅读：配置Oracle 11g的Dataguard测试，创建物理备库(Physical Standby Database)
Oracle 11g DataGuard 物理备库配置及Active DataGuard测试
有关DG的相关概念，可参考：Oracle Data Guard Concepts and Administration
有关配置DG的参数描述，可参考：
基于同一主机配置 Oracle 11g Data Guard
说明：
本文安装配置了Oracle 11g Dataguard通过duplicate方式创建物理备库。
比起上次配置有以下不同点：
1. 主备库目录结构不同。
2. 采用duplicate方式创建物理备库。
一、环境介绍
1. 主数据库环境
操作系统版本 : OEL5.8 x64
数据库版本 : Oracle 11.2.0.3 x64
数据库名 : orcl
数据库SID : orcl
db_unique_name: orcl
instance_name : orcl
DGMGRL : orcl_DGMGRL
2. 备库环境
操作系统版本 : OEL5.8 x64
数据库版本 : Oracle 11.2.0.3 x64 （只安装oracle数据库软件,no netca dbca）
数据库名 : slave
数据库SID : slave
db_unique_name: slave
instance_name : slave
DGMGRL : slave_DGMGRL
3. DataGuard启动顺序
启动顺序：先启备库，后启主库
关闭顺序：先关主库，后关备库
二、主数据库环境准备
1. 主库环境对比
充分利用主数据库原来环境，仅量不对主库参数配置做过多的修改。
重新创建口令文件
# su - oracle
$ orapwd file='/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' password=oracle entries=10 force=y
2. 修改配置lisener监听文件
说明：添加dgmgrl静态监听配置，为后面的dg broker配置打基础。
$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.200)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
#其中的GLOBAL_DBNAME具有固定的格式：_DGMGRL.。
4. 修改配置tnsname.ora文件
说明：ORCL是主库的服务名，DG是备库的服务名。
$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
SLAVE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = slave)
)
)
5. 修改配置成规档模式
1)、检查数据库是否处于归档状态
SQL> archive log list;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;
2)、将主库设置为 FORCE LOGGING 模式
SQL> alter database force logging;
SQL> select force_logging,flashback_on from v$database;
FOR FLASHBACK_ON
--- ------------------
YES YES
6. 修改主库参数文件
SQL>
alter system set instance_name='orcl' scope=spfile;
alter system set db_unique_name='orcl' scope=spfile;
alter system set local_listener='orcl' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(orcl,slave)';
alter system set log_archive_dest_1='LOCATION=/u01/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile;
alter system set log_archive_dest_2='SERVICE=slave lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=slave' scope=spfile;
alter system set log_archive_format='arch_%r_%t_%s.arc' scope=spfile;
alter system set fal_client='orcl' scope=spfile;
alter system set fal_server='slave' scope=spfile;
alter system set standby_file_management=AUTO;
alter database add standby logfile group 4 '/u01/app/oracle/oradata/orcl/standby_redo04.log' size 50M;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/orcl/standby_redo05.log' size 50M;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/orcl/standby_redo06.log' size 50M;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/orcl/standby_redo07.log' size 50M;
SQL> shutdown immediate;
SQL> startup;
三、备库配置
1. 备库环境
操作系统版本 : OEL5.8 x64
数据库版本 : Oracle 11.2.0.3 x64 （只安装oracle数据库软件,no netca dbca）
数据库名 : slave
数据库SID : slave
db_unique_name: slave
instance_name : slave
DGMGRL : slave_DGMGRL
2. 修改配置lisener监听文件
$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.150)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = slave)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = slave)
)
(SID_DESC =
(GLOBAL_DBNAME = slave_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = slave)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
其中的GLOBAL_DBNAME具有固定的格式：_DGMGRL.。
3. 修改配置tnsname.ora文件
说明：ORCL是主库的服务名，DG是备库的服务名。
$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
SLAVE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = slave)
)
)
测试服务名连通性：
tnsping orcl
tnsping slave
3. 创建11g数据库基本目录
# su - oracle
mkdir -p /u01/app/oracle/admin/slave/{adump,dpdump,pfile,scripts}
mkdir -p /u01/app/oracle/oradata/slave
mkdir -p /u01/app/oracle/fast_recovery_area/slave
mkdir -p /u01/archivelog
4. 拷贝主库口令文件并改名
注：10g DG环境只要求密码相同，11g DG则要求与主库完全一致。
否则报无权限错误。
$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
$ scp oracle@192.168.233.150:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl $ORACLE_HOME/dbs/
$ mv orapworcl orapwslave
测试远程登录
$ sqlplus sys/oracle@orcl as sysdba;
$ sqlplus sys/oracle@slave as sysdba;
5. 启动到nomount状态
$ echo 'db_name=slave' > $ORACLE_HOME/dbs/initslave.ora
$ sqlplus /nolog
SQL> conn / as sysdba;
SQL> startup nomount;
四、 开始在RMAN duplicate数据库
1. RMAN同进连接主库与备库
$ rman target sys/oracle@orcl auxiliary sys/oracle@slave
恢复管理器: Release 11.2.0.3.0 - Production on 星期五 8月 16 21:14:10 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: ORCL (DBID=1351417842)
已连接到辅助数据库: SLAVE (未装载)
2. 开始duplicate数据库
RMAN>
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby nofilenamecheck from active database
dorecover
spfile
parameter_value_convert 'orcl','slave'
set instance_name='slave'
set db_unique_name='slave'
set local_listener='slave'
set db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/slave/'
set log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/slave/'
set control_files='/u01/app/oracle/oradata/slave/control01.ctl','/u01/app/oracle/oradata/slave/control02.ctl','/u01/app/oracle/oradata/slave/control03.ctl'
set log_archive_dest_1='LOCATION=/u01/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=slave'
set log_archive_dest_2='SERVICE=orcl lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl'
set log_archive_max_processes='5'
set standby_file_management='AUTO'
set fal_client='slave'
set fal_server='orcl';
release channel c1;
release channel c2;
release channel stby;
}
RMAN> quit
恢复管理器完成。
3. 查看备库状态
说明：duplicate数据库之后，备库只是处于mount状态，查看备库状态。
$ sqlplus / as sysdba
# 查看备库状态
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
MOUNTED PHYSICAL STANDBY slave
4. 将备库置与应用日志模式状态
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
5. 验证物理备库日志应用
1）主库上操作
SQL> conn / as sysdba;
SQL> create user abc identified by abc ;
SQL> grant dba to abc;
SQL> conn abc/abc
SQL> create table abc ( id integer , name char(10));
SQL> insert into abc values ( 0 , 'aaa' );
SQL> commit;
SQL> conn / as sysdba;
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 /u01/archivelog/
最早的联机日志序列 8
下一个存档日志序列 10
当前日志序列 10
2）备库上验证
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 /u01/archivelog/
最早的联机日志序列 9
下一个存档日志序列 0
当前日志序列 10
SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- -------------- -------------- ---------
7 16-8月 -13 16-8月 -13 YES
8 16-8月 -13 16-8月 -13 YES
9 16-8月 -13 16-8月 -13 IN-MEMORY
经过测试，Oracle 11g dataguard物理备库创建成功。