Windows 环境下配置 oracle 11gR2

Data Guard 详细过程
1 服务器环境：
两台 PC 机，操作系统为 Windows server 2008 R2
数据库准备为 oracle 11.2.0.3 for win32
2 配置前准备：
主库主机名 service1，ip 配置为 192.168.104.11，db_name:orcl，
db_unique_name：primary
备库主机名 service2，ip 配置为 192.168.104.21.db_name:orcl，
db_unique_name：standby
在安装 oracle 时，主库安装数据库，备库只安装软件
安装路径在 D 盘，具体路径为默认 D:\app\Administrator\。
实例名为 orcl
数据文件放在 E:\oracleDB\orcl 文件夹下，归档日志文件放在
E:\oracleDB\flash\orcl\ARCHIVELOG 文件夹下，联机日志文件默认
3 正式配置过程：
3.1 主库配置
3 正式配置过程：
3.1 主库配置
在主库启动数据库到 mount 模式，开启归档模式与 force
logging flashback。

Shutdown immediate
Startup mount
Alter database archivelog;
Alter database force logging;
Alter database flashback on;
3.2 查看当前日志文件组
Select * from v$logfile order by group#;

3.3 为备用库创建日志文件：
这里四组可以简单的理解为在线日志组数+1
Alter database add standby logfile group 4
(‘E:\oracleDB\orcl\redo004.log’) size 50m;
Alter database add standby logfile group 5
(‘E:\oracleDB\orcl\redo005.log’) size 50m;
Alter database add standby logfile group 6
(‘E:\oracleDB\orcl\redo006.log’) size 50m;
Alter database add standby logfile group 7
(‘E:\oracleDB\orcl\redo007.log’) size 50m;

再次查询 v$logfile
已经建好备用日志了。
3.4 修改系统参数 spfile 文件(13 个参数)
1）Alter system set db_unique_name='primary' scope=spfile;
2）Alter system set
log_archive_config='DG_CONFIG=(primary,standby)'
scope=spfile;

3）Alter system set
log_archive_dest_1='location=E:\oracleDB\flash\orcl\ARCHIVE
LOG\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=primary' scope=spfile;
4）Alter system set log_archive_dest_2='SERVICE=standby
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby' scope=spfile;
5）Alter system set log_archive_dest_state_1 = 'enable'
scope=spfile;
6 ）Alter system set log_archive_dest_state_2 = 'enable'
scope=spfile;
7 ）Alter system set fal_server='standby' scope=spfile;
8 ）Alter system set fal_client='primary' scope=spfile;
9 ）Alter system set archive_lag_target=1800 scope=spfile;
10）Alter system set log_archive_format='%t_%s_%r.arc'
scope=spfile;
11）Alter system set standby_file_management=auto
scope=spfile;
12）Alter system set
db_file_name_convert='standby','primary' scope=spfile;
13）Alter system set
log_file_name_convert='standby','primary' scope=spfile;

3.5 启动数据库
Alter database open;
重启数据库将修改的参数加载；
Shutdown immediate
Startup
3.6 创建 pfile 参数文件；
Create pfile from spfile;
默认路径下
D:\app\Administrator\product\11.2.0\dbhome_1\database\INITo
rcl.ORA 。

3.7 创建密码文件
密码文件存放在：
D:\app\Administrator\product\11.2.0\dbhome_1\database\
文件名为：PWDorcl.ora
如果在目录下未能找见，则需要动手创建，创建命令为：
Orapwd
file=D:\app\Administrator\product\11.2.0\dbhome_1\database\
PWDorcl.ora password=123 entries=5

注：在 cmd 命令下输入 orapwd。Orcl 为实例名，123 为主库 sys
的密码
3.8 配置主库网络监听（备用库同理）
文件位置在
D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\
修改 listener.ora 文件，内容如下：
# listener.ora Network Configuration File:
d:\app\Administrator\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 =
d:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS =
"EXTPROC_DLLS=ONLY:d:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME =
d:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.104.11)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = d:\app\Administrator

红色内容为需要修改成的。
修改 tnsnames.ora 文件，内容如下：
# tnsnames.ora Network Configuration File:
d:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\
tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.104.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.104.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.104.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
红色内容为需要修改成的。
3.9 测试监听
重启监听，用网络名连接测试监听是否正常
C:\>lsnrctl stop
C:\>lsnrctl start
重启数据库：
shutdown immediate ---> startup
测试监听
C:\>tnsping primary
C:\>sqlplus sys/syspwd@primary as sysdba

如果不能连接，请检查防火墙。
3.10 关闭注数据库
3.11 在备用建立文件夹
3.10 关闭注数据库
3.11 在备用建立文件夹
A. 将 D:\app\Administrator 目录下的 admin、cfgtoollogs、
diag目录及E:\oracleDB\下的flash目录及密码文件拷贝到备用
库相同的路径下。
B. 将主库的 listener.ora 和 tnsnames.ora 拷贝到备库相同
路径下。
注意修改 listener.or 中 IP 地址为备机 IP
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.104.21)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
在主备机 E 盘建立 E:\oracleDB\tmp 文件夹
3.12 备机新建实例
在备机上注册 oracle 实例到服务中，命令如下：
Oradim -new -sid orcl（实例名）
3.13 在备库启动监听
3.14 备库参数文件
将刚才主库创建的pfile参数文件initorcl.ora文件拷贝至备库
D 盘根目录下修改为如下：
orcl.__db_cache_size=536870912
orcl.__java_pool_size=8388608
orcl.__large_pool_size=8388608
orcl.__oracle_base='d:\app\Administrator'#ORACLE_BASE
set from environment
orcl.__pga_aggregate_target=520093696
orcl.__sga_target=771751936
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=209715200
orcl.__streams_pool_size=0
*.archive_lag_target=1800
*.audit_file_dest='d:\app\Administrator\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='E:\oracleDB\orcl\control01.ctl','E:\ora
cleDB\flash\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='primary','standby'
*.db_name='orcl'
*.db_recovery_file_dest='E:\oracleDB\flash'
*.db_recovery_file_dest_size=10737418240
*.db_unique_name='standby'
*.diagnostic_dest='d:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='standby'
*.fal_server='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=E:\oracleDB\flash\orcl\AR
CHIVELOG\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=primary'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='primary','standby'
*.memory_target=1287651328
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
以上红色标注为需要修改成的
用参数文件建立实例
Startup nomount pfile=’
D:\app\Administrator\product\11.2.0\dbhome_1\database\INITo
rcl.ORA’
3.15 使用 RMAN 复制主库
在主库 startup
3.15.1 用 rman 建立备份
RMAN>Backup full database format=’
E:\oracleDB\tmp\FOR_STANDBY_%u%p%s.RMN’ include
current controlfile for standby;
将当前 archivelog 归档
RMAN>sql’alter system archive log current’;
3.15.2 将 rman 的备份集拷贝到 standby 库相同的路径
3.15.3 复制数据库
3.15.2 将 rman 的备份集拷贝到 standby 库相同的路径
3.15.3 复制数据库
RMAN>connect auxiliary sys/syspwd@standby
RMAN>duplicate target database for standby
nofilenamecheck;
…………………………
3.15.4 备库启动 standby
Alter database mount standby database;
Alter database recover managed standby database
disconnect from session;
至此 DataGuard 的搭建就算完成了
4 DataGuard 验证
下面来验证是否搭建成功
主库执行
Select name,sequence#,applied,completion_time from
v$archived_log order by completion_time desc,sequence# desc;
两边 sequence#一致，说明已经同步
下面在主库切换活动日志，然后查询日志同步情况
Alter system switch logfile;
5 关于 switchover（正常主备切换）
首先查询主库切换状态
Select switchover_status from v$database;
TO STANDBY 表示可以切换，如果是其它的需要根据情况处理后再切换。
在主库将 primary 切换到 physical standby
主库执行以下命令
Alter database commit to switchover to physical standby with
session shutdown;
Shutdown immediate
Startup nomount
Alter database mount standby database;
Alter database recover managed standby database disconnect
from session;
在备库，switchover 到 primary：
Alter database commit to switchover to primary;
Shutdown immediate
Startup
Alter system switch logfile;
在新主库和新备库执行如下命令：
Select name,sequence#,applied,completion_time from
v$archived_log order by completion_time desc,sequence# desc;
在新主库执行：alter system switch logfile;
新主库执行