风哥教程

培训 . 交流 . 分享
Make progress together!

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

[复制链接]
内容发布:paulyi| 发布时间:2014-1-22 22:02:24
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;



上一篇:Linux平台搭建Oracle 11g单机dataguard容灾的操作文档
下一篇:oracle dataguard Renaming a Datafile in the Primary Database
回复

使用道具 举报

内容发布:mhb2014| 发布时间:2014-7-24 19:19:28
学习了。。。。
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

热门文章教程

  • 风哥Oracle数据库巡检工具V1.0(附2.6网页
  • Oracle 12cR2 九大新功能全面曝光_详解云数
  • 实战PHP与MySQL权威指南PDF电子书下载
  • 大数据技术与应用入门培训教程(电子版下载
  • Oracle OCP认证考试IZ0-053题库共712题数据
  • MySQL5权威指南(第3版)PDF电子版下载
快速回复 返回顶部 返回列表