oracle 11g数据库在不同平台异构搭建dataguard安装文档

oracle 11g 在windows与linux不同平台下的 DATA GUARD配置
主库 windows 2003 server+oracle 11.1.0.6
db_name:primary
sid_name:primary
数据文件存放目录:D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\
IP地址:192.168.1.227
备库 Red Hat Enterprise Linux AS release 4 (Nahant Update 6) +oracle 11.1.0.6
db_name:primary
sid_name:phy_standby
数据文件存放目录:/oradata/phy_standby
IP地址:192.168.1.226

1.验证主库是否是归档模式，而且必须要force loggging
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 29 14:35:31 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
SQL> alter database force logging
2 ;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2.备份主库 用于恢复备库
C:\Documents and Settings\Administrator>rman target /
Recovery Manager: Release 11.1.0.6.0 - Production on Tue Jul 29 14:36:23 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: PRIMARY (DBID=1498839992)
RMAN> backup database format 'd:\db_%U.bak';
Starting backup at 29-JUL-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\SYSTE
M01.DBF
input datafile file number=00003 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\UNDOT
BS01.DBF
input datafile file number=00002 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\SYSAU
X01.DBF
input datafile file number=00004 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\USERS
01.DBF
channel ORA_DISK_1: starting piece 1 at 29-JUL-08
channel ORA_DISK_1: finished piece 1 at 29-JUL-08
piece handle=D:\DB_01JMN1K3_1_1.BAK tag=TAG20080729T143651 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-08
channel ORA_DISK_1: finished piece 1 at 29-JUL-08
piece handle=D:\DB_02JMN1L6_1_1.BAK tag=TAG20080729T143651 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JUL-08
RMAN>

3.在备库配置standby路径
cd $ORACLE_BASE
cd admin
mkdir phy_standby
cd phy_standby
mkdir adump
mkdir dpdump
mkdir pfile
cd /oradata
mkdir phy_standby
cd phy_standby
mkdir archivelog --这个用于存放备库归档路径参数

4.从主库生成standby初始化参数
SQL> create pfile='d:/init.ora' from spfile;
添加以下几个STANDBY参数：
*.log_archive_config='DG_CONFIG=(primary,phy_standby)'
*.fal_client='phy_standby'
*.fal_server='primary'
*.db_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\','/oradata/phy_standby/'
*.log_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\','/oradata/phy_standby/'
*.standby_file_management='auto'
*.log_archive_dest_1='location=/oradata/phy_standby/archivelog VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'

5、生成STANDBY控制文件：
SQL> alter database create standby controlfile as 'd:\control01.ctl';
Database altered.
并将生成的STANDBY控制文件、初始化参数文件、备份集分别COPY至STANDBY /oradata/phy_standby 目录下
，并且copy control01.ctl 重命名为control02.ctl,control03.ctl
另外注意将$ORACLE_HOME/database下密码文件PWDprimary.ora也需要COPY并在STANDBY机器
$ORACLE_HOME/dbs目录下改名为orapwphy_standby，因为主库和备库需要相同的密码文件，否则STANDBY无法配置成功：
完整的standby初始化参数文件如下:
primary.__db_cache_size=163577856
primary.__java_pool_size=4194304
primary.__large_pool_size=4194304
primary.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
primary.__pga_aggregate_target=180355072
primary.__sga_target=251658240
primary.__shared_io_pool_size=0
primary.__shared_pool_size=75497472
primary.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/phy_standby/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/oradata/phy_standby/CONTROL01.CTL','/oradata/phy_standby/CONTROL02.CTL','/oradata/phy_standby/CONTROL03.CTL'
*.db_block_size=8192
*.db_domain=''
*.db_name='primary'
*.db_recovery_file_dest=''
*.db_recovery_file_dest_size=536870912
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=228867584
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.log_archive_config='DG_CONFIG=(primary,phy_standby)'
*.fal_client='phy_standby'
*.fal_server='primary'
*.db_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\','/oradata/phy_standby/'
*.log_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\','/oradata/phy_standby/'
*.standby_file_management='auto'
*.log_archive_dest_1='location=/oradata/standby/archivelog VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'

6.启动standby数据库
export ORACLE_SID=phy_standby
sqlplus / as sysdba
create spfile from pfile='/oradata/phy_standby/init.ora';
startup mount;

7.恢复standby数据库
export ORACLE_SID=phy_standby
rman target /
由于控制文件里面记录的备份信息还是在WINDOWS上主库的备份信息，
需要使用RMAN的catalog命令来使RMAN认出在STANDBY端从WINDOWS主库COPY过来的备份集：
catalog start with '/oradata/phy_standby';
crosscheck backup;
delete expired backup;
restore database;

8、在主库和备用库端分别更改listener.ora和tnsnames配置，添加主库和备用库的TNS连接字，并确保在主库和STANDBY都能够连接上对方：
主库listener.ora配置
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = paul2003)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME =primary)
(ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
(SID_NAME =primary)
)
(SID_DESC =
(GLOBAL_NAME =primary)
(ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
(SID_NAME =phy_standby)
)
)
主库tnsnamea.ora
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.227)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)
phy_standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.226)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =phy_standby)
)
)
备库listener.ora配置
# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.6/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME =asm11g)
(ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
(SID_NAME =asm11g)
)
(SID_DESC =
(GLOBAL_NAME =standby)
(ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
(SID_NAME =standby)
)
(SID_DESC =
(GLOBAL_NAME =phy_standby)
(ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
(SID_NAME =phy_standby)
)
)
备库tnsnames.ora配置
phy_standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.226)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =phy_standby)
)
)
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.227)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =primary)
)
)

主库和备库互相tnsping 服务名
phy_standby tnsping primary
[oracle@asm11g admin]$ tnsping primary
TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 26-JUL-2008 02:11:26
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.227)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =primary)))
OK (10 msec)
primary tnsping phy_standby
C:\Documents and Settings\Administrator>tnsping phy_standby
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 30-JUL-2
008 10:04:38
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
D:\app\Administrator\product\11.1.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1
.226)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =phy_stan
dby)))
OK (10 msec)

9、添加STANDBY LOGFILE，启动STANDBY至恢复管理模式：
idle> alter database add standby logfile '/oradata/phy_standby/redo04.log' size 50M;
Database altered.
idle> alter database add standby logfile '/oradata/phy_standby/redo05.log' size 50M;
Database altered.
idle> alter database add standby logfile '/oradata/phy_standby/redo06.log' size 50M;
Database altered.
idle> recover managed standby database disconnect from session;
Media recovery complete.

10、主库配置到STANDBY的归档，另外注意主库需要设置log_archive_config这个参数，否则归档将不会从主库传至STANDBY端：
SQL> alter system set log_archive_dest_2='service=phy_standby ASYNC VALID_FOR=(
ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=phy_standby' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system set log_archive_config='DG_CONFIG=(primary,phy_standby)';
System altered.
SQL>alter system set log_archive_dest_1='location=d:\app\
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary';
System altered.
SQL> alter system set log_archive_dest_state_1=enable;
System altered.
这样异构STANDBY就配置成功了

11 检查主库归档日志是否正常传送到备库
主库:
alter system switch logfile;
备库:是否传送过来
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
是否已经apply
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
2 ORDER BY SEQUENCE#;
检查主库连到备库的参数文件是否正常
select * from v$archive_dest_status;