双机热备实战完全手册

1、13双机热备实战完全手册10.0.0.11是我主服务器的ip ,10.0.0.111是我从服务器的ip, testdbase是数据库的sid号， 操作系统的版本是 RedHat Linux 8.0 数据库版本是 Oracle 9.2.0.11 . 建立oracle用户roottest2 etc#groupadd oinstallroottest2 etc#groupadd dbaroottest2 etc#useradd-g oinstall -G dba oracle2 .设置oracle用户环境变量 #主从服务器的环境变量最好一致以下是一个oracle用户环境变量范本oracletest2

2、 oracle$ more .bash_profileexport LD_ASSUME_KERNEL=2.4.1 #RedHat AS 3.0 必须加此参数export ORACLE_BASE=/opt/oracle #根据实际情况设定 ORACLE_BASEexport ORACLE_HOME=/opt/oracle/product/9.2.0 # 根据实际情况设定 ORACLE_HOMEexport ORACLE_SID=testdbaseexport ORACLE_TERM=xtermexport NLS_LANG=american_america. ZHS16GBK; #繁体中文字符集

3、是 ZHT16BIG5export ORA_NLS33=$OR ACLE_HOME/ocommon/nls/admin/dataLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libLD_LIBRARY_PATH=$LD_LIBRAR Y_PATH:/usr/local/libexport LD_LIBRARY_PATHexport PATH=$PATH:$ORACLE_HOME/binCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibCLASSPATH=$CLASSPA

4、TH:$ORACLE_HOME/network/jlib:$ORACLE_HOME/jdbc/lib/classes12.jarexport CLASSPATHJAVA_HOME=/usr/java/j2sdk1.4.1_02 #根据实际情况设定 JAVA_HOME 的路径export JAVA_HOMEPATH=$PATH:$JAVA_HOME:$JAVA_HOME/bin:$JAVA_HOME/jre:$ORACLE_HOME/binexport PATHumask 0223. 安装java4 . 建立.bash_profile 相关文件夹roottest2 etc#chown oracl

5、e.oinstall-Rf /opt # 将/opt 目录的属主改为 oracle.oinstallroottest2 etc# su - oracleoracletest2 oracle$ mkdir -p /opt/oracle/product/9.2.0/ocommon/nls/admin/dataoracletest2 oracle$ mkdir -p /opt/oracle/product/9.2.0/liboracletest2 oracle$ mkdir -p /opt/oracle/product/9.2.0/network/jliboracletest2 oracle$ mk

6、dir -p /opt/oracle/product/9.2.0/jdbc/lib/5 .在备用服务器上安装 oracle备用服务器在安装的时候不需要建库，只需安装Software如果安装的是 oracle9201 版本，在安装到84%的时候，会有一个编译错误 Error in invoking target install ofmakefile /opt/oracle/product/9.2.0/ctx/lib/ins_ctx.mk，此时打开这个文件：$ORACLE_HOME/ctx/lib/env_ctx.mk ，把$(LDLIBFLAG)dl加到如下位置：INSO_LINK = -L$(

7、CTXLIB) $(LDLIBFLAG)m $(LDLIBFLAG)dl $(LDLIBFLAG)sc_ca$(LDLIBFLAG)sc_fa $(LDLIBFLAG)sc_ex $(LDLIBFLAG)sc_da $(LDLIBFLAG)sc_ut$(LDLIBFLAG)sc_ch $(LDLIBFLAG)sc_fi $(LLIBCTXHX) $(LDLIBFLAG)c-WI,-rpath,$(CTXHOME)lib $(CORELIBS) 然后按重试，就可以继续安装下去了。如果主服务器的操作系统版本和目录结构和备用服务器一模一样，并且主服务器的ORACLE没有建库，可以直接将主服务器的 $

8、ORACLE_BASE、$ ORACLE_HOME拷贝到备用服务器6 .查看主服务器数据库是否使用spfile #这一步可有可无oraclecqcncdb oracle$ sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Wed Jul 21 11:54:42 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL conn / as sysdba;Connected.SQL show parameter spfileNAME TYPE

9、VALUEspfile string ?/dbs/spfile.ora7 .在主服务器上将主数据库的数据文件、redo log、temp文件拷贝到备用服务器上(冷备份方式)oraclecqcncdb oracle$ sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Wed Jul 21 12:03:58 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL conn / as sysdba;Connected.SQL select *

10、from v$dbfile;FILE#NAME10/opt/oracle/oradata/testdbase/xdb01.dbf9/opt/oracle/oradata/testdbase/users01.dbf2/opt/oracle/oradata/testdbase/undotbs01.dbfFILE#NAME8/opt/oracle/oradata/testdbase/tools01.dbf1/opt/oracle/oradata/testdbase/system01.dbf7/opt/oracle/oradata/testdbase/odm01.dbfFILE#NAME6 /opt/

11、oracle/oradata/testdbase/indx01.dbf5/opt/oracle/oradata/testdbase/example01.dbf4/opt/oracle/oradata/testdbase/drsys01.dbfFILE#NAME3/opt/oracle/oradata/testdbase/cwmlite01.dbf11/opt/oracle/oradata/testdbase/TONG.dbf12/opt/oracle/oradata/testdbase/SPAPP .dbfFILE#NAME13/opt/oracle/oradata/testdbase/WTS

12、PALL.dbf14/opt/oracle/oradata/testdbase/SZJLT .dbf15/opt/oracle/oradata/testdbase/JL TGAME.dbf15 rows selected.SQL select * from v$logfile;GROUP# STATUS TYPEMEMBER3 ONLINE/opt/oracle/oradata/testdbase/redo03 .log2 ONLI NE/opt/oracle/oradata/testdbase/redo02 .log1 ONLI NE/opt/oracle/oradata/testdbase

13、/redo01 .logSQL select * from v$tempfile;FILE# CREATION_CHANGE# CREATION TS# RFILE# STATUS ENABLEDBYTES BLOCKS CREATE_BYTES BLOCK_SIZENAME1 0 2 1 ONLINE READ WRITE225443840 27520 41943040 8192/opt/oracle/oradata/testdbase/temp01.dbfSQL shutdown immediate;Database closed.Database dismounted.ORACLE in

14、stance shut down.SQL exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer R elease 9.2.0.1.0 - Production将以上列出的文件拷贝到备用服务器相应的目录下，或者直接将/opt/oracle/oradata/testdbase/ 目录下所有的文件直接拷贝到备用服务器的/opt/oracle/oradata/test

15、dbase/ 目录下8 .在主服务器打开主库数据库，修改为归档方式（如果主数据库已经是归档方式，则不用修改了）手工在主服务器创建归档目录$cd $ORACLE_BASE #$ORACLE_BASE 所指向的目录是/opt/oracle/，可参看第 4步的ORACLE用户环境变量的具体设置$mkdir -p oradata/testdbase/archiveoraclecqcncdb testdbase$ sqlplus /nologSQL conn / as sysdba;Connected.SQL startup mount;SQL alter database archivelog;SQL

16、 archive log start;SQL archive log list;SQL alter database open;SQL alter system set LOG_ARCHIVE_DEST_1=LOCATION= /opt/oracle/oradata/testdbase/archive;SQL alter system set log_archive_format=%t_%s.dbf scope=spfile;SQL alter system set log_archive_start=true scope=spfile;重新启动数据库，使修改结果生效SQL shutdown

17、immediate;察看归档模式SQL startupSQL archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /opt/oracle/oradata/testdbase/archiveOldest online log sequence 565Next log sequence to archive 567Current log sequence 5679 .在主库上制作从库control fileSQLALTERDATABASECREATESTANDBYC

18、ONTROLFILEAS7opt/oracle/product/9.2.0/dbs/control01.ctl：Database altered.目录下,将 /opt/oracle/product/9.2.0/dbs/control01.ctl拷贝到备用服务器的 /opt/oracle/oradata/testdbase/10 .配置主库和从库的 tnsnames.ora将主库的tnsnames.ora拷贝到从库相应的目录下oracletest2 admin$ vi tnsnames.ora #编辑从库 tnsnames.ora我的主库和从库的 tnsnames.ora如下：# TNSNAME

19、S.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/tnsnames.ora# Generated by Oracle configuration tools.DBSTANDBY =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.111)(P0R T = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = testdbase)DBPRIMARY =

20、(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.11)(POR T = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = testdbase)TESTDBASE = (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.11)(POR T = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME =

21、testdbase) )10.0.0.11是我主服务器的ip，10.0.0.111是我从服务器的ip, testdbase是数据库的sid号,11 . 配置从库的listener.ora将主库的listener.ora拷贝到从库相应的目录下我的从库的listener.ora文件如下：.ora# LISTENER.OR A Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener# Generated by Oracle configuration tools.LISTENER =(DESCRIPTI

22、ON_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.111)(P0R T = 1521)SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = /opt/oracle/product/9.2.0)(PROGRAM = extproc)(SID_DESC =(GLOBAL_DB

23、NAME = testdbase)(ORACLE_HOME = /opt/oracle/product/9.2.0)(SID_NAME = testdbase)主库和从库的listener .ora内容除了 ip不同以外，另U的都是一模一样的12 .启动从库的监听oracletest2 admin$ lsnrctl startLSNRCTL for Linux: Version 9.2.0.1.0 - Production on 21-JUL-2004 14:30:46Copyright (c) 1991,2002, Oracle Corporation. All rights reserve

24、d.Starting /opt/oracle/product/9.2.0/bin/tnslsnr: please wait.TNSLSNR for Linux: Version 9.2.0.1.0 - ProductionSystem parameter file is /opt/oracle/product/9.2.0/network/admin/listener.oraLog messages written to /opt/oracle/product/9.2.0/network/log/listener.logListening on: (DESCRIPTION=(ADDRESS=(P

25、ROTOCOL=ipc)(KEY=EXTPROC)Listening on: (DESCRIPTI0N=(ADDRESS=(PR0T0C0L=tcp)(H0ST=10.0.0.111)(P0R T=1521)Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)STATUS of the LISTENERAlias l_l STENERVersion TNSLSNR for Linux: Version 9.2.0.1.0 - ProductionStart Date 21-JUL-2004 14:30:46Uptime

26、0 days 0 hr . 0 min. 0 secTrace Level offSecurity OFFSNMP OFFListener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.oraListener Log File /opt/oracle/product/9.2.0/network/log/listenerogListening Endpoints Summary.(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)(DESCRIPTION=(ADDRES

27、S=(PROTOCOL=tcp)(HOST=10.0.0.111)(POR T=1521)Services Summary.Service PLSExtProc has 1 instance(s).Instance PLSExtProc, status UNKNOWN, has 1 handler(s) for this service.Service testdbase has 1 instance(s).Instance testdbase, status UNKNOWN, has 1 handler(s) for this service.The command completed su

28、ccessfully13 . 在主库上创建 pfile，因为9i缺省使用spfilesqlplus /nologSQL conn / as sysdba;Connected.SQL create pfile=/opt/inittestdbase.ora from spfile;14 . 将主服务器生成的/opt/inittestdbase.ora文件拷贝到从库的$ORACLE_HOME目录15 .修改从库 $ORACLE_HOME/dbs/inittestdbase.ora 文件在从库$ORACLE_HOME/dbs/inittestdbase.ora文件的末尾添加以下参数*.standby_

29、file_management=AUTO*.remote_archive_enable=TRUE*.standby_archive_dest=/opt/oracle/oradata/testdbase/standbyarchive *.fal_server=DBPRIMARY*.fal_client=DBSTANDBY16 . copy 主库的orapwtestdbase 到从库相应的目录17 .启动从库，将从库加载为standby database 模式oracletest2 standbyarchive$ sqlplus /nologSQL*Plus: Release 920.1.0 -

30、Production on Wed Jul 21 15:00:49 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL conn / as sysdbaConnected to an idle instance.SQL startup nomount pfile=?/dbs/inittestdbase.oraORACLE instance started.Total System Global Area 235999352 bytesFixed Size 450680 bytesVariable S

31、ize 201326592 bytesDatabase Buffers 33554432 bytesRedo Buffers 667648 bytesSQL alter database mount standby database;Database altered.SQL alter database recover managed standby database disconnect from session;Y REOPEN=60：18 .在主服务器上设置主库到从库的归档SQL alter system set log_archive_dest_2=SERVICE=dbstandby

32、MANDATOR19 .验证主库的日志是否传送从库在主库上操作SQL select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM1 1 572 104857600 1 NO CURRENT128255951 21-JUL-042 1 571 104857600 1 YES ACTI VE128255339 21-JUL-043 1 570 104857600 1 YES INACTIVE128255209 21-JUL-04SQL alter system switch

33、 logfile;System altered.此时在从服务器查看从库的日志oracletest2 bdump$ tail -f /opt/oracle/admin/testdbase/bdump/alert_testdbase.logCompleted: alter database recover managed standby database diWed Jul 21 15:36:39 2004Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_566.dbf Media Recovery Log /opt

34、/oracle/oradata/testdbase/standbyarchive/1_567.dbfMedia Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_568.dbfMedia Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_569.dbfMedia Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_570.dbfMedia Recovery Log /opt/oracle/

35、oradata/testdbase/standbyarchive/1_571.dbfMedia Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_572.dbfMedia Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_573.dbfMedia Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_574.dbfMedia Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_575.dbfMedia Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_576.dbfMedia Recovery Waiting for thread 1 seq# 57720 .采用Arch进程传送