1、环境
|
|
System Version |
IP |
Oracle Version |
DB_NAME |
DB_UNIQUE_NAME |
|
Primary |
W2k8 64 |
192.168.230.129 |
11.2.0.1.0 |
cme |
cme |
|
Standby |
W2k8 64 |
192.168.230.131 |
11.2.0.1.0 |
cme |
cmedg1 |
Primary端数据库处于open状态、归档状态及强制归档状态，通过DBCA创建，已经有密码文件；standby端仅安装数据库软件，没有创建数据库。
2、网络配置
2.1 primary监听配置
# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cme)
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = cme)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.129)(PORT = 1521))
)
ADR_BASE_LISTENER = C:\app\Administrator\product\11.2.0\dbhome_1\log
2.2 standby监听配置
# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cmedg1)
(SID_NAME = cmedg1)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.131)(PORT = 1521))
)
ADR_BASE_LISTENER = C:\app\Administrator\product\11.2.0\dbhome_1\log
2.3 primary网络服务名配置
# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
DG131 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cmedg1)
)
)
DG129 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.129)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cme)
)
)
2.4 standby网络服务名配置
# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
DG131 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cmedg1)
)
)
DG129 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.129)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cme)
)
)
2.5 启动监听并进行测试
注意要进行防火墙放行或者关闭防火墙。
C:\Users\Administrator>tnsping dg131
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-2月 -
2015 11:33:29
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的参数文件:
C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1
68.230.131)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cmedg1)))
OK (0 毫秒)
C:\Users\Administrator>tnsping dg129
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-2月 -
2015 11:33:37
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的参数文件:
C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1
68.230.129)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cme)))
OK (20 毫秒)
3、参数配置
3.1 primary参数配置
修改相关参数，根据spfile.ora生成pfile.ora，具体内容如下：
cme.__db_cache_size=209715200
cme.__java_pool_size=4194304
cme.__large_pool_size=4194304
cme.__oracle_base='C:\app\Administrator'#ORACLE_BASE set from environment
cme.__pga_aggregate_target=297795584
cme.__sga_target=562036736
cme.__shared_io_pool_size=0
cme.__shared_pool_size=331350016
cme.__streams_pool_size=4194304
*.audit_file_dest='C:\app\Administrator\admin\cme\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='C:\APP\ADMINISTRATOR\ORADATA\CME\CONTROLFILE\O1_MF_BF8RJ635_.CTL','C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\CME\CONTROLFILE\O1_MF_BF8RJ69J_.CTL'
*.db_block_size=8192
*.db_create_file_dest='C:\app\Administrator\oradata'
*.db_domain=''
*.db_name='cme'
*.db_recovery_file_dest='C:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='C:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cmeXDB)'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=857735168
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.log_archive_config='dg_config=(cme,cmedg1)'
*.log_archive_dest_2='service=dg131 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=cmedg1'
*.log_archive_dest_state_2='ENABLE'
3.2 standby参数配置
将primary的pfile.ora拷贝到standby，修改后生成spfilecmedg1.ora。
C:\Users\Administrator>set oracle_sid=cmedg1
C:\Users\Administrator>oradim -new -sid cmedg1
实例已创建。
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 2月 10 15:14:56 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
已连接到空闲例程。
SQL> create spfile from pfile='C:\app\Administrator\product\11.2.0\dbhome_1\data
base\pfile.ora';
文件已创建。
修改后的pfile.ora内容如下：
cme.__db_cache_size=209715200
cme.__java_pool_size=4194304
cme.__large_pool_size=4194304
cme.__oracle_base='C:\app\Administrator'#ORACLE_BASE set from environment
cme.__pga_aggregate_target=297795584
cme.__sga_target=562036736
cme.__shared_io_pool_size=0
cme.__shared_pool_size=331350016
cme.__streams_pool_size=4194304
*.audit_file_dest='C:\app\Administrator\admin\cmedg1\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\CONTROLFILE\O1_MF_BF8RJ635_.CTL','C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\CMEDG1\CONTROLFILE\O1_MF_BF8RJ69J_.CTL'
*.db_block_size=8192
*.db_create_file_dest='C:\app\Administrator\oradata'
*.db_domain=''
*.db_name='cme'
*.db_unique_name='cmedg1'
*.db_recovery_file_dest='C:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='C:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cmeXDB)'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=857735168
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.log_archive_config='dg_config=(cme,cmedg1)'
*.log_archive_dest_1='location=C:\app\Administrator\oradata\cmedg1\archivelog valid_for=(all_logfiles,all_roles) db_unique_name=cmedg1'
*.standby_file_management='auto'
4、系统设置
4.1 standby目录创建
根据参数文件创建相关目录。
mkdir C:\app\Administrator\admin\cmedg1\adump
mkdir C:\app\Administrator\admin\cmedg1\bdump
mkdir C:\app\Administrator\admin\cmedg1\cdump
mkdir C:\app\Administrator\admin\cmedg1\pfile
mkdir C:\app\Administrator\admin\cmedg1\udump
mkdir C:\app\Administrator\oradata\cmedg1\controlfile
mkdir C:\app\Administrator\oradata\cmedg1\datafile
mkdir C:\app\Administrator\oradata\cmedg1\onlinelog
mkdir C:\app\Administrator\oradata\cmedg1\archivelog
mkdir C:\app\Administrator\flash_recovery_area
4.2 standby密码文件
将primary端的密码文件复制到standby并重命名为PWDcmedg1.ora。
5、创建standby数据库
将standby数据库启动到nomount状态，在primary端运行如下命令：
C:\Users\Administrator>rman target / auxiliary sys/123456@dg131
恢复管理器: Release 11.2.0.1.0 - Production on 星期二 2月 10 17:43:35 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到目标数据库: CME (DBID=4087846086)
已连接到辅助数据库: CME (未装载)
RMAN> duplicate target database for standby nofilenamecheck from active database;
RMAN> duplicate target database for standby nofilenamecheck from active database
;
启动 Duplicate Db 于 10-2月 -15
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: SID=20 设备类型=DISK
内存脚本的内容:
{
backup as copy reuse
targetfile 'C:\app\Administrator\product\11.2.0\dbhome_1\DATABASE\PWDcme.ORA
' auxiliary format
'C:\app\Administrator\product\11.2.0\dbhome_1\DATABASE\PWDcmedg1.ORA' ;
}
正在执行内存脚本
启动 backup 于 10-2月 -15
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=49 设备类型=DISK
完成 backup 于 10-2月 -15
内存脚本的内容:
{
sql clone "alter system set control_files =
''C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\CONTROLFILE\O1_MF_BF8RJ635_.CTL'', ''C:\
APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\CMEDG1\CONTROLFILE\O1_MF_BF8RJ69J_.CTL'' c
omment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format 'C:\APP\ADMI
NISTRATOR\ORADATA\CMEDG1\CONTROLFILE\O1_MF_BF8RJ635_.CTL';
restore clone controlfile to 'C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\CMEDG
1\CONTROLFILE\O1_MF_BF8RJ69J_.CTL' from
'C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\CONTROLFILE\O1_MF_BF8RJ635_.CTL';
sql clone "alter system set control_files =
''C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\CONTROLFILE\O1_MF_BF8RJ635_.CTL'', ''C:\
APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\CMEDG1\CONTROLFILE\O1_MF_BF8RJ69J_.CTL'' c
omment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
正在执行内存脚本
sql 语句: alter system set control_files = ''C:\APP\ADMINISTRATOR\ORADATA\CME
DG1\CONTROLFILE\O1_MF_BF8RJ635_.CTL'', ''C:\APP\ADMINISTRATOR\FLASH_RECOVERY_ARE
A\CMEDG1\CONTROLFILE\O1_MF_BF8RJ69J_.CTL'' comment= ''Set by RMAN'' scope=spfile
启动 backup 于 10-2月 -15
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件副本
复制备用控制文件
输出文件名=C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFCME.ORA 标
记=TAG20150210T174551 RECID=1 STAMP=871321552
通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:00:03
完成 backup 于 10-2月 -15
启动 restore 于 10-2月 -15
使用通道 ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: 已复制控制文件副本
完成 restore 于 10-2月 -15
sql 语句: alter system set control_files = ''C:\APP\ADMINISTRATOR\ORADATA\CME
DG1\CONTROLFILE\O1_MF_BF8RJ635_.CTL'', ''C:\APP\ADMINISTRATOR\FLASH_RECOVERY_ARE
A\CMEDG1\CONTROLFILE\O1_MF_BF8RJ69J_.CTL'' comment= ''Set by RMAN'' scope=spfile
Oracle 实例已关闭
已连接到辅助数据库 (未启动)
Oracle 实例已启动
系统全局区域总计 855982080 字节
Fixed Size 2180544 字节
Variable Size 503319104 字节
Database Buffers 348127232 字节
Redo Buffers 2355200 字节
内存脚本的内容:
{
sql clone 'alter database mount standby database';
}
正在执行内存脚本
sql 语句: alter database mount standby database
内存脚本的内容:
{
set newname for clone tempfile 1 to new;
set newname for clone tempfile 2 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
datafile 5 auxiliary format new
;
sql 'alter system archive log current';
}
正在执行内存脚本
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
临时文件 1 在控制文件中已重命名为 C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\DATAFILE\O
1_MF_TEMP_%U_.TMP
临时文件 2 在控制文件中已重命名为 C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\DATAFILE\O
1_MF_N6DATASP_%U_.TMP
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
启动 backup 于 10-2月 -15
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件副本
输入数据文件: 文件号=00005 名称=C:\APP\ADMINISTRATOR\ORADATA\CME\DATAFILE\N6_DAT
A01.DBF
输出文件名=C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\DATAFILE\O1_MF_N6DATASP_DATA_D-CM
E_I-4087846086_TS-N6DATASPACE_FNO-5_02PUUJVC_.DBF 标记=TAG20150210T174619
通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:04:36
通道 ORA_DISK_1: 启动数据文件副本
输入数据文件: 文件号=00001 名称=C:\APP\ADMINISTRATOR\ORADATA\CME\DATAFILE\O1_MF_
SYSTEM_BF8RDDTT_.DBF
输出文件名=C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\DATAFILE\O1_MF_SYSTEM_DATA_D-CME_
I-4087846086_TS-SYSTEM_FNO-1_03PUUK80_.DBF 标记=TAG20150210T174619
通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:00:45
通道 ORA_DISK_1: 启动数据文件副本
输入数据文件: 文件号=00002 名称=C:\APP\ADMINISTRATOR\ORADATA\CME\DATAFILE\O1_MF_
SYSAUX_BF8RDDWR_.DBF
输出文件名=C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\DATAFILE\O1_MF_SYSAUX_DATA_D-CME_
I-4087846086_TS-SYSAUX_FNO-2_04PUUK9D_.DBF 标记=TAG20150210T174619
通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:00:36
通道 ORA_DISK_1: 启动数据文件副本
输入数据文件: 文件号=00003 名称=C:\APP\ADMINISTRATOR\ORADATA\CME\DATAFILE\O1_MF_
UNDOTBS1_BF8RDDY7_.DBF
输出文件名=C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\DATAFILE\O1_MF_UNDOTBS1_DATA_D-CM
E_I-4087846086_TS-UNDOTBS1_FNO-3_05PUUKAH_.DBF 标记=TAG20150210T174619
通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:00:15
通道 ORA_DISK_1: 启动数据文件副本
输入数据文件: 文件号=00004 名称=C:\APP\ADMINISTRATOR\ORADATA\CME\DATAFILE\O1_MF_
USERS_BF8RDF1X_.DBF
输出文件名=C:\APP\ADMINISTRATOR\ORADATA\CMEDG1\DATAFILE\O1_MF_USERS_DATA_D-CME_I
-4087846086_TS-USERS_FNO-4_06PUUKB0_.DBF 标记=TAG20150210T174619
通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:00:02
完成 backup 于 10-2月 -15
sql 语句: alter system archive log current
内存脚本的内容:
{
switch clone datafile all;
}
正在执行内存脚本
数据文件 1 已转换成数据文件副本
输入数据文件副本 RECID=1 STAMP=871321955 文件名=C:\APP\ADMINISTRATOR\ORADATA\CME
DG1\DATAFILE\O1_MF_SYSTEM_DATA_D-CME_I-4087846086_TS-SYSTEM_FNO-1_03PUUK80_.DBF
数据文件 2 已转换成数据文件副本
输入数据文件副本 RECID=2 STAMP=871321955 文件名=C:\APP\ADMINISTRATOR\ORADATA\CME
DG1\DATAFILE\O1_MF_SYSAUX_DATA_D-CME_I-4087846086_TS-SYSAUX_FNO-2_04PUUK9D_.DBF
数据文件 3 已转换成数据文件副本
输入数据文件副本 RECID=3 STAMP=871321955 文件名=C:\APP\ADMINISTRATOR\ORADATA\CME
DG1\DATAFILE\O1_MF_UNDOTBS1_DATA_D-CME_I-4087846086_TS-UNDOTBS1_FNO-3_05PUUKAH_.
DBF
数据文件 4 已转换成数据文件副本
输入数据文件副本 RECID=4 STAMP=871321955 文件名=C:\APP\ADMINISTRATOR\ORADATA\CME
DG1\DATAFILE\O1_MF_USERS_DATA_D-CME_I-4087846086_TS-USERS_FNO-4_06PUUKB0_.DBF
数据文件 5 已转换成数据文件副本
输入数据文件副本 RECID=5 STAMP=871321955 文件名=C:\APP\ADMINISTRATOR\ORADATA\CME
DG1\DATAFILE\O1_MF_N6DATASP_DATA_D-CME_I-4087846086_TS-N6DATASPACE_FNO-5_02PUUJV
C_.DBF
完成 Duplicate Db 于 10-2月 -15
6、standby端启动redo应用
C:\Users\Administrator>set oracle_sid=cmedg1
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 2月 10 17:56:22 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
7、验证
7.1 primary端切换日志
SQL> alter system switch logfile;
系统已更改。
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
74
7.2 standby端查看日志
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
74