空间管理您的位置: ITPUB个人空间 » paul oracle my love » 日志

学习oracle技术,每天学一点,每天进步一点

oracle 11g在不同平台异构standby 安装文档

上一篇 / 下一篇 2008-07-30 10:13:34 / 个人分类：oracle 11g 新技术

oracle 11g 在windows与linux不同平台下的 DATA GUARD配置ITPUB个人空间}C8^)k#}|#eg
主库 windows 2003 server+oracle 11.1.0.6ITPUB个人空间N'P/w+_Y&JT'N
db_name:primary
"JX.t2EE6P-_2q0sid_name:primary
t/xn#~b!Eu(MS0数据文件存放目录:D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\ITPUB个人空间9g!_g"x#b2@1M `
IP地址:192.168.1.227
Hk&yf k%L@0备库 Red Hat Enterprise Linux AS release 4 (Nahant Update 6) +oracle 11.1.0.6ITPUB个人空间k#rm9_UO
db_name:primaryITPUB个人空间?n(@j#bTw:y%d
sid_name:phy_standby
X}g%B4g_Mb0数据文件存放目录:/oradata/phy_standbyITPUB个人空间!O1Rv:Xbo
IP地址:192.168.1.226

1.验证主库是否是归档模式，而且必须要force loggging

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

ITPUB个人空间ex8h/g+G)[
Connected to:
f0h0L:bDj7RG0Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
r4w+pLcX0With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
3M+c!{\6at0Database log mode              Archive Mode
j.j n0[:?0Automatic archival             EnabledITPUB个人空间4R'Lg?%TO0l3n
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     9
J+s+g"w3NW?0Next log sequence to archive   11ITPUB个人空间p*N!Ob3c }&yl
Current log sequence           11ITPUB个人空间je \,j&j1{Qq wY
SQL> alter database force loggingITPUB个人空间!U2^?&b(rg j'{
2 ;

Database altered.

SQL> exitITPUB个人空间}}/J/mJU q wi#{
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
#~$P-zn2IV%B0oduction
qnOO a0With the Partitioning, OLAP, Data Mining and Real Application Testing options

2.备份主库用于恢复备库
:jEPt.?$nPiV3w!y0C:\Documents and Settings\Administrator>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Jul 29 14:36:23 2008

connected to target database: PRIMARY (DBID=1498839992)

RMAN> backup database format 'd:\db_%U.bak';

Starting backup at 29-JUL-08
ToU.rr4qDf*V|0using target database control file instead of recovery catalog
2?7H}(F:e7}Kk0allocated channel: ORA_DISK_1
-T l*Z^#H7e(us0channel ORA_DISK_1: SID=133 device type=DISKITPUB个人空间r;u Qw-h2};iqH
channel ORA_DISK_1: starting full datafile backup setITPUB个人空间9CH Q_i'FZ4h/x
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\SYSTEITPUB个人空间P*kO([D-}n \
M01.DBF
HI.t3h,s0input datafile file number=00003 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\UNDOT
cN.Ymgl0BS01.DBF
ylcY~%Ek0input datafile file number=00002 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\SYSAU
PG^1B'CHe`#}0dS0X01.DBFITPUB个人空间 u.jQ[%R:c@D
input datafile file number=00004 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\USERS
01.DBF
/l^"kV$M K:t9E(g%eL0channel ORA_DISK_1: starting piece 1 at 29-JUL-08
7iK;p]b t/_!q3R0channel ORA_DISK_1: finished piece 1 at 29-JUL-08
XZ|&H2mnDdEdm c0piece handle=D:\DB_01JMN1K3_1_1.BAK tag=TAG20080729T143651 comment=NONE
Ixh1GF_J0channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
]uAL^Al1KO u)z0channel ORA_DISK_1: starting full datafile backup set
v:Psxy)Al8ZAkK0channel ORA_DISK_1: specifying datafile(s) in backup set
tMfa1f5^;s%ju0~0including current control file in backup setITPUB个人空间4}9C w V*|
including current SPFILE in backup setITPUB个人空间 x z&nIAs0x
channel ORA_DISK_1: starting piece 1 at 29-JUL-08
b6N ~c k0?4nR0channel ORA_DISK_1: finished piece 1 at 29-JUL-08
cq~)Z-Q3b(m0piece handle=D:\DB_02JMN1L6_1_1.BAK tag=TAG20080729T143651 comment=NONEITPUB个人空间M$P!Lo.F.p
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
8TQJ7A?WLU0Finished backup at 29-JUL-08

RMAN>

3.在备库配置standby路径ITPUB个人空间0V)|T_N8t6c
cd $ORACLE_BASE
y#c#} S*rM*[0cd admin
V(GA'S$of#~7V0mkdir phy_standby
i}M%_}1PW;D0cd phy_standby
-h8K6PXA8A2]3_0mkdir adump
9N2mE\,lB%\0mkdir dpdump
Cuj5i-EX:~S-|,OAq0mkdir pfile
p2D[M8K9W/nu9U0cd /oradata
cKtse}*d%@#G5T0mkdir phy_standbyITPUB个人空间3mO`"H:G
cd phy_standbyITPUB个人空间S8T6v.t5?5g1Ny#l&J A
mkdir archivelog --这个用于存放备库归档路径参数

ITPUB个人空间/M8y'gD"WX`K R
4.从主库生成standby初始化参数
3H9H|R5Z]m0SQL> create pfile='d:/init.ora' from spfile;

添加以下几个STANDBY参数：

*.log_archive_config='DG_CONFIG=(primary,phy_standby)'
%Z*C bE-~H J0*.fal_client='phy_standby'
:N,O6S&H+Qf@;h&B0*.fal_server='primary'ITPUB个人空间8Qx r(Ad$F
*.db_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\','/oradata/phy_standby/'
+Tek8k^"\l j:@0*.log_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\','/oradata/phy_standby/'ITPUB个人空间7S,UJ*]R"N
*.standby_file_management='auto'ITPUB个人空间T)_h;XLQ?H
*.log_archive_dest_1='location=/oradata/phy_standby/archivelog VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'

ITPUB个人空间K9Elk)`c?*HT
5、生成STANDBY控制文件：

SQL> alter database create standby controlfile as 'd:\control01.ctl';

Database altered.

并将生成的STANDBY控制文件、初始化参数文件、备份集分别COPY至STANDBY /oradata/phy_standby 目录下ITPUB个人空间0aM4Z)dJd8G.j HWn
，并且copy control01.ctl 重命名为control02.ctl,control03.ctlITPUB个人空间HCEV~_4J
另外注意将$ORACLE_HOME/database下密码文件PWDprimary.ora也需要COPY并在STANDBY机器ITPUB个人空间&q~"}SpG
$ORACLE_HOME/dbs目录下改名为orapwphy_standby，因为主库和备库需要相同的密码文件，否则STANDBY无法配置成功：

完整的standby初始化参数文件如下:ITPUB个人空间'L3`?B}(wiT
primary.__db_cache_size=163577856
+q`7o-]hN0primary.__java_pool_size=4194304
.qz.u/y:denK0primary.__large_pool_size=4194304
w['^j)pzo9o0primary.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
8VYk'bSecH0primary.__pga_aggregate_target=180355072ITPUB个人空间Hy*QsR'Z6Y-R
primary.__sga_target=251658240ITPUB个人空间ZATjg
primary.__shared_io_pool_size=0ITPUB个人空间|7m9Q5c$~/X7p X
primary.__shared_pool_size=75497472
`k0t1k+}"k,^HV9f'@0primary.__streams_pool_size=0
2i+K J,}NDj0*.audit_file_dest='/u01/app/oracle/admin/phy_standby/adump'ITPUB个人空间3P-^`#k"wHm4Q
*.audit_trail='db'ITPUB个人空间 IOek'}z
*.compatible='11.1.0.0.0'ITPUB个人空间#~3r4X:N^^F h.PG
*.control_files='/oradata/phy_standby/CONTROL01.CTL','/oradata/phy_standby/CONTROL02.CTL','/oradata/phy_standby/CONTROL03.CTL'
1H4gT;Q(J Dr O0*.db_block_size=8192
|TZ%`D;S&E0*.db_domain=''ITPUB个人空间` usWw7D L?
*.db_name='primary'
v} A#~k'P;S N0*.db_recovery_file_dest=''
'O d*w?2f4ox:S0*.db_recovery_file_dest_size=536870912ITPUB个人空间]CeAu)M3P
*.diagnostic_dest='/u01/app/oracle'ITPUB个人空间K;D_V&u.K0i h%D#|
*.log_archive_format='ARC%S_%R.%T'
VTk0A;Xb.R/T0*.memory_target=228867584ITPUB个人空间M r~,Q*f2X |!k4K
*.open_cursors=300ITPUB个人空间H[_SUx[+c;@8@f
*.processes=150
$@QB4N+h1b"x)x^j0*.remote_login_passwordfile='EXCLUSIVE'ITPUB个人空间+m(A|7i3?g
*.undo_tablespace='UNDOTBS1'ITPUB个人空间8l8o/zlh$B;n PF
*.log_archive_config='DG_CONFIG=(primary,phy_standby)'
Z7W-UDlB0*.fal_client='phy_standby'
0RSd@4rQ S(T9I'J0*.fal_server='primary'
o)`%?8`'S#yU)s+nU{0*.db_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\','/oradata/phy_standby/'
?in_Zi!z!YQ7_W'{0*.log_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\','/oradata/phy_standby/'ITPUB个人空间 RL G8m/~a[;M M
*.standby_file_management='auto'ITPUB个人空间0~-RxDs#WD
*.log_archive_dest_1='location=/oradata/standby/archivelog VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'

6.启动standby数据库
3j!sf\ u+N;CST"KL0export ORACLE_SID=phy_standby
$Y4I^0KZ IT0sqlplus / as sysdba
;g}5h/BVU7LQ0create spfile from pfile='/oradata/phy_standby/init.ora';ITPUB个人空间%sD`x$s@#^RkO
startup mount;

7.恢复standby数据库ITPUB个人空间^.eB]l F2P"b
export ORACLE_SID=phy_standby
TC~yCgp_z0rman target /ITPUB个人空间n%q2GB1j)b
由于控制文件里面记录的备份信息还是在WINDOWS上主库的备份信息，
K0M%C7w,@oe0需要使用RMAN的catalog命令来使RMAN认出在STANDBY端从WINDOWS主库COPY过来的备份集：

catalog start with '/oradata/phy_standby';
*|wMlV}3~ z(q0crosscheck backup;
e.M3se2ii.C|,FV8|2U0delete expired backup;
{MSJ$x LN.K*s P.x0restore database;

ITPUB个人空间rPlMp0O5~,[[N H:l
8、在主库和备用库端分别更改listener.ora和tnsnames配置，添加主库和备用库的TNS连接字，并确保在主库和STANDBY都能够连接上对方：
b4jym"boh7n#E0 主库listener.ora配置ITPUB个人空间K2\"P-y3Z tE
LISTENER =
*X[!|Q'l7qJ^5m.k0 (DESCRIPTION_LIST =ITPUB个人空间a#b.PG]'f
    (DESCRIPTION =
kMO#b^+H6kyg0      (ADDRESS = (PROTOCOL = TCP)(HOST = paul2003)(PORT = 1521))
} l*^&_:F'E-j|0      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
p.]EB9|E0    )
?fC%ygB0 )

SID_LIST_LISTENER =
b r o KTmg0 (SID_LIST =
*@#dvL5]9s(}0    (SID_DESC =
CF1j7e;_g r0      (SID_NAME = PLSExtProc)
![0?? GQ'a!b*L2y3dI&K a0      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
{2I#S7NwfMc0      (PROGRAM = extproc)ITPUB个人空间&Pc4m'D4G KPc+p
    )ITPUB个人空间T sejmxd X:?
    (SID_DESC =ITPUB个人空间1nN)G#sv&wg
      (GLOBAL_NAME =primary)ITPUB个人空间U;y+oOy
      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)ITPUB个人空间c-YV aD-DAw
      (SID_NAME =primary)ITPUB个人空间+GC.XBcy
    )ITPUB个人空间R"| IS$@%]
   (SID_DESC =
!P([;KgbP0      (GLOBAL_NAME =primary)
d7P*WGO3P0      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)ITPUB个人空间)rPDp-T'o7e(SV6U
      (SID_NAME =phy_standby)ITPUB个人空间F8_8_(SO,W
    )ITPUB个人空间-? r9x7e;U{
)
F;D8M6\!Sys0主库tnsnamea.ora

primary =ITPUB个人空间s \b@ORO4Mn/a
(DESCRIPTION =ITPUB个人空间$w:b(_V iQY)]gn
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.227)(PORT = 1521))
o6qR]Kf pc8b r0    (CONNECT_DATA =ITPUB个人空间s7Nk`oB,r\2Rm
      (SERVER = DEDICATED)ITPUB个人空间b}V.^q|?{
      (SERVICE_NAME = primary)ITPUB个人空间z%x @f3o\7]-X-c!f:C
    )ITPUB个人空间(B*n"S0b3?)U
)
!R9Fq-SY0`b/OWEf0phy_standby =
"Y!EW]cf0 (DESCRIPTION =
t)lgGk0    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.226)(PORT = 1521))ITPUB个人空间ms8|4JHIF2@@
    (CONNECT_DATA =
r#MH*R"I&[gV1y U0      (SERVER = DEDICATED)ITPUB个人空间u,d!I,F#oS
      (SERVICE_NAME =phy_standby)ITPUB个人空间x$Dx.Z:Xi6g9Yp$Q
    )ITPUB个人空间0V|Y6a'b9Z$u)`~W
)

备库listener.ora配置
n1U&Y)i T%Kf0# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.6/db_1/network/admin/listener.oraITPUB个人空间 t1x1U&o?s C)YGT
# Generated by Oracle configuration tools.

LISTENER =ITPUB个人空间 XLy4O'P?2C d:r
(DESCRIPTION_LIST =
_.lr9[|0    (DESCRIPTION =
-F Op,DiApj0oK V0      (ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521))ITPUB个人空间M7Ci!K)j1B}g#Y
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))ITPUB个人空间n!w-{1\K7a {C
    )
:F @SdQBm.i)cP&Q0 )
(q0mHG8wY,jv$}0SID_LIST_LISTENER =
wC&o;i0o%g0 (SID_LIST =
q;V4|j^ @0    (SID_DESC =
#i:C\!o8pK/E0      (SID_NAME = PLSExtProc)ITPUB个人空间+C7W1NQv'e"it5nG8b
      (ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
,bt'xr"D/IW9C:qo(w0      (PROGRAM = extproc)
d%Q9{.F f IG1P0    )ITPUB个人空间J"{S2s$HT&S
    (SID_DESC =ITPUB个人空间H:~] ZKK5W$y$|7c
      (GLOBAL_NAME =asm11g)
&q_ s$r"hYY0      (ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)ITPUB个人空间,Gn8~ a#r"s`o
      (SID_NAME =asm11g)
    )ITPUB个人空间*T%a,F `4L p(C
    (SID_DESC =ITPUB个人空间 OkXi)Di-U
      (GLOBAL_NAME =standby)
ll uSvd_ww0      (ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)ITPUB个人空间:a/@!I{aA0U0Tg
      (SID_NAME =standby)ITPUB个人空间!O2wXGnFm `
    )
[nl\%h+_0    (SID_DESC =
PH^9U;nMQ'V0      (GLOBAL_NAME =phy_standby)
`*~6ndX_2[0      (ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)ITPUB个人空间5v[O.\Qlj^vk
      (SID_NAME =phy_standby)
#U3Duz5[i*\p0    )
rk.ao-Y0)

备库tnsnames.ora配置ITPUB个人空间"uPm4F3g#C;s$U
phy_standby =ITPUB个人空间Of&S&O)JM
(DESCRIPTION =ITPUB个人空间]uu7R.T7bL
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.226)(PORT = 1521))
x?5r7N2n#|${ V0    (CONNECT_DATA =ITPUB个人空间7Oa*KRx|
      (SERVER = DEDICATED)
Vo8A_j@!~4DtO'd0      (SERVICE_NAME =phy_standby)
3K jHj)h0    )
5eYte9a p1B$^2J0 )
$j0c_1U+a_Sv0}7h0primary =ITPUB个人空间L3KeY0}!Hu9{
(DESCRIPTION =
$Q^,F\qThN0    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.227)(PORT = 1521))
-M'D%q&t8eiAR8u0    (CONNECT_DATA =ITPUB个人空间"YC?;B Pz^
      (SERVER = DEDICATED)
@c P\/w6W"r0y3J0      (SERVICE_NAME =primary)
KOC&{2WcSsp-t0    )ITPUB个人空间!XV6H,yzP
)

主库和备库互相tnsping 服务名
phy_standby tnsping primaryITPUB个人空间{;w3D"f[1h[
[oracle@asm11g admin]$ tnsping primary

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 26-JUL-2008 02:11:26

Used parameter files:

\9KOM8BUgP [0IS0Used TNSNAMES adapter to resolve the alias
/JJb5dS^0d"d6J v0Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.227)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =primary)))ITPUB个人空间b:s-w(j1D#zL5f
OK (10 msec)

primary tnsping phy_standbyITPUB个人空间*T&Q vMT*E)m
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-2ITPUB个人空间j4T1D2vM a M*I
008 10:04:38

Used parameter files:
L;xg-~$T },j0D:\app\Administrator\product\11.1.0\db_1\network\admin\sqlnet.ora

9b}Nz]0Used TNSNAMES adapter to resolve the aliasITPUB个人空间-gf)NwL,B8KVA
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1
{M$w7Tq{S2l0.226)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =phy_stan
6E@d0E [4z0dby)))ITPUB个人空间*k`$r8zN
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;ITPUB个人空间$uJMr4dY7F Z
Media recovery complete.

10、主库配置到STANDBY的归档，另外注意主库需要设置log_archive_config这个参数，否则归档将不会从主库传至STANDBY端：

SQL> alter system set log_archive_dest_2='service=phy_standby ASYNC VALID_FOR=(ITPUB个人空间urP;fu1u2Mka
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\
M V]_9K4L"^|9S0VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary';

System altered.
1u4U)~9U8};|i0SQL> alter system set log_archive_dest_state_1=enable;

System altered.

这样异构STANDBY就配置成功了

11 检查主库归档日志是否正常传送到备库
r P.BH.~n0主库:ITPUB个人空间%le.R$}JmCO
alter system switch logfile;
3A!m0c~t+_?%m1o0备库:是否传送过来ITPUB个人空间H})J/sy;STK%v
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
BaOU$nJOH_ qs02> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;ITPUB个人空间7NDh Gxh;B
是否已经apply
J5]6C4~"\(Y b0SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOGITPUB个人空间PJrX Sv
2 ORDER BY SEQUENCE#;

检查主库连到备库的参数文件是否正常ITPUB个人空间zf"pd9]!W
select * from v$archive_dest_status;

导入论坛引用链接收藏分享给好友推荐到圈子管理举报

TAG:

查看全部评论