文稿成果讲稿guard

1、Oracle : 11.2.0OS: redhat 5.564PrimaryIP 10.180.7.40DB_NAME=orclStandby IP :10.180.7.30DB_NAME=orcl一、 Primary操作1、设置归档模式先开启数据库oraclePrimary $ sqlplus /nologSQL*Plus: Copyright SQL connConnectedRelease 11.2.0.1.0 Production(c) 1982, 2009, Oracle./as sysdbato an idle instance.on Fri Apr 1 23:42:28 2011

2、s.SQL startupORACLE instantarted.Total System Global Fixed SizeVariable Size Database BuffersRedo BuffersArea4175462402213936318769104922746884288512bytes bytes bytes bytesbytesDatabase mounted. Database opened.查看归档模式SQL archive log list; Database log mode Automatic archival Archive destination Olde

3、st online log sequenceCurrent log sequence数据库已打开No Archive ModeDisabled数据库归档模式无自动归档USE_DB_RECOVERY_FILE_DEST 810关闭数据库，启用挂载模式 SQL shutdown immediate Database closed.Database dismounted.ORACLE instanhut down.启用挂载模式SQL startup mountORACLE instantarted.Total System Global Fixed SizeVariable Size Databas

4、e Buffers Redo BuffersDatabase mounted.Area4175462402213936314574800964689924288512bytes bytes bytes bytesbytes设置归档模式SQL alter database archivelog;Database altered.查看归档模式SQL archive log list; Database log mode Automatic archival Archive destination Oldest online log sequenceNext log sequence to arch

5、iveCurrent log sequenceArchive Mode EnabledUSE_DB_RECOVERY_FILE_DEST 810102、Primary设置 forcelogging查看 force logging是否打开SQL FOR-NOselect force_loggingfrom v$database;没有开启force loggingalter database force开启SQLlogging;Database altered.现在看已打开了SQL select force_logging FOR-from v$database;YES3、配置 Oracle Ne

6、t在 Primary 库和 Standby 都要修改注：修改 Primary /Standbytnsnames.orainstener.ora文件时请在 oracle 中直接修改，vi 修改有时会出错SQLmes.ora LISTENER =hostvi/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admnsna(DESCRIPTION_LIST =(DESCRIPTION (ADDRESS = (ADDRESS =)= (PROTOCOL(PROTOCOL= IPC)(KEY = EXTPROC1521)= TCP)(HOST

7、 = Primary)(PORT= 1521)ADR_BASE_LISTENER= /home/oracle/app/oracle#alonso SID_LIST_LISTENER(SID_LIST =(SID_DESC =添加内容=(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcl)-配置静态tnsname.oraSQLhost ner.oravi/home/oracle/app/oracle/product/11.2.0/dbhome_1

8、/network/admin/liste/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/adm # Generated by Oracle configuration tools.nsnames.ora#alonso下面为添加的内容ORCL_ST = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = orcl)=Standby)(PORT=1521)ORCL_PD = (DESCRIP

9、TION =(ADDRESS = (PROTOCOL = TCP)(HOST (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = orcl)=Primary)(PORT=1521)注意：修改完后记得重启 listeneroraclePrioraclePri LSNRCTL fordmin$ dmin$Linux: Verlsnrctl stop lsnrctl start11.2.0.1.0 - Production on 02-APR-2011 00:34:26Copyright (c) 1991, 2009, Oracle.s.Start

10、ing /home/oracle/app/oracle/product/11.2.0/dbhome_1/bnslsnr: please wait.TNSLSNR for Linux: Ver11.2.0.1.0 - ProductionSystem parameter file is /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraLog messages written to /home/oracle/app/oracle/diag/tnslsnr/Primary/listener/alert

11、/log.xmlListening on: Listening on:Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Primary)(PORT=1521) (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)LISTENERSUSoftheAlias Ver StartUptimeLISTENERTNSLSNR for Linux: Ver 02-APR-2011 00:

12、34:280 days 0 hr. 0 min. 0 secoff11.2.0.1.0 - ProductionDateTrace Level SecuritySNMPON: Local OS Authentication OFF/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraListenarameterFileListener Log FileListening Endpo/home/oracle/app/oracle/diag/tnslsnr/Primary/listener/alert/l

13、og.xmlmmary.(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Primary)(PORT=1521)ServiSummary.Service orcl has 1 instan).Instance orcl, sus UNKNOWN, has 1 handler(s) for this service.Thed completed sucsfully启动完成，说明没有问题4、添加 dataguard 参数，设置备份创建 pfile 文件，添加

14、如下内容SQLcreate pfile from spfile;（此命令会在/11.2.0/dbhome_1/dbs 生成文件initorcl.ora，打开编辑在最后添加内容）FileSQLcreated.host vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora*.db_name=orcl*.db_unique_name=orcl_pd*.log_archive_config=dg_config=(orcl_pd,orcl_st)*.log_archive_dest_1=location=/home/ora

15、cle/app/oracle/oradata/archive/archivelog db_unique_name=orcl_pdvalid_for=(all_logfiles,all_roles)*.log_archive_dest_2=service=orcl_st reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_st*.log_archive_dest_s*.log_archive_dest_se_1=enablee_2=enable*.standby_file_manag

16、ement=auto*.fal_server=orcl_st*.fal_cnt=orcl_pd注意：由于指定了目录 archive 所以这个目录需要自己创建oraclePrimary dbs$ mkdir /home/oracle/app/oracle/oradata/archiveStandby 归档文件的存放位置按如下规则来进行： （对上述设置进行说明）（1）当 LOG_ARCHIVE_DEST_n 设置了 valid_for=(all_logfiles,all_roles)，那么在不定义 standby_archive_dest 参数时，Oracle 就会选择LOG_ARCHIVE_DE

17、ST_n 参数作为归档目标。（2）如果在第一步设置的同时，又独立设置 LOG_ARCHIVE_DEST_n 参数为 valid_for=(standby_logfile,*)10.0 的时候，会自动的选择任意一个 LOG_ARCHIVE_DEST_n 的值。（3）如果 LOG_ARCHIVE_DEST_n 没有设置的话，默认位置是：$ORACLE_HOME/dbs.属性，那么当 compatible 参数大于不过 valid_for 参数的默认值就是 all_logfiles 和 all_roles.所以只要设置了本地的归档位置，的归档文件也会放到这个目录下面。5、用新的 pfile 重启主库

18、SQL shutdown immediate Database closed.Database dismounted.ORACLE instanhut down.启动新的 pfileSQLstartup pfile=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora把修改好的 initorcl.ora数据转换写入：spfileorcl.ora,SQL create spfile from pfile=/home/oracle/app/oracle/product/11.2.0/dbhome_1/d bs/initor

19、cl.ora; SQL startupORACLE instantarted.Total System Global Fixed SizeVariable Size Database Buffers Redo Buffers Database mounted.Database opened.Area41754624022139362768260641342177284288512bytes bytes bytes bytesbytes到这里 Primary 就完成了操作！二.Standby 端设置：1. 创建相关目录结构oraclestandby dbs $ mkdir -p /home/or

20、acle/app/oracle/oradata/dave-这里创建的目录和库不同，在参数文件里需要转换一下。2. 创建 standby 的口令文件oraclestandby dbs $ orapwd file=?/dbs/orapwdave password=password3. 创建 standby 的初始化参数：SQL create pfile from spfile;*.db_name=orcl*.control_files=/home/oracle/app/oracle/oradata/dave/control01.ctl, /home/oracle/app/oracle/oradat

21、a/dave/control02.ctl, /home/oracle/app/oracle/oradata/dave/control03.ctl*.db_unique_name=orcl_st*.log_archive_config=dg_config=(orcl_pd,orcl_st)*.log_archive_dest_1=location=/home/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl_st*.log_archive_dest_2=service=orcl_pd reopen=1

22、20 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_pd*.log_archive_dest_s*.log_archive_dest_se_1=enablee_2=enable*.standby_file_management=auto*.fal_server=orcl_pd*.fal_cnt=orcl_st*.log_file_name_convert=/home/oracle/app/oracle/oradata/orcl,/home/oracle/app/oracle/oradata/dav

23、e*.db_file_name_convert=/home/oracle/app/oracle/oradata/orcl,/home/oracle/app/oracle/oradata/dave配置 oracle net oraclePri LISTENER =dmin$ vi listener.ora(DESCRIPTION_LIST =(DESCRIPTION (ADDRESS =(ADDRESS =(PROTOCOL = IPC)(KEY = EXTPROC1521)(PROTOCOL = TCP)(HOST = Primary)(PORT = 1521)ADR_BASE_LISTENE

24、R= /home/oracle/app/oracle#alonso SID_LIST_LISTENER(SID_LIST =(SID_DESC =添加内容=(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)(SID_NAME=orcl)-配置静态tnsname.oraoraclePri #dmin$ Vi tnsnames.ora tnsnames.oraNetworkConfigurationFile:/home/oracle/app/oracle/product/11.2

25、.0/dbhome_1/network/admnsnames.ora# Generated byOracle configuration tools.ORCL_ST = (DESCRIPTION(ADDRESS =(PROTOCOL = TCP)(HOST = Standby)(PORT = 1521)(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl)#alonso下面为添加的内容ORCL_PD = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST (CONNECT_DATA =(SER

26、VER = DEDICATED) (SERVICE_NAME = orcl)= Primary)(PORT = 1521)oraclestandby admin$ lsnrctl stoporaclestandby admin$ lsnrctl start4. 用新的 pfile 将 standby 启动 但不启动挂载模式：（说明：由于控制文件等都没有拷过去，所以用挂载模式spfile 文件需要 standby 所有操作完成后在进行转换，）SQL startup nomount pfile=?/dbs/initorcl.ora报错，Pfile 文件转换为Total System Global

27、Fixed SizeVariable Size Database BuffersRedo BuffersArea41754624022139362768260641342177284288512bytes bytes bytes bytesbytes5. 开始 duplicateoraclestandby dbs $ rmansys/passwordorcl_pd auxiliary sys/passwordorcl_stRecovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 8 16:10:252011.Copyright c

28、onnectedconnected(c) 1982, 2009, Oracle and/or its affiliates.stodatabase: ORCL (DBID=1272955137)to auxiliary database: ORCL (not mounted)RMAN duplicate下面内容自动生成database for standby from active database;Starting Duplicate Db at 08-MAR-11usingdatabase control file instead of recoverycatalogallocated c

29、hannel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=20 device type=DISKcontents of Memory Script:backup as copy reusefile/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworclauxiliaryformat/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl;-用 duplicate 创建 standby 时会口令文件executing Mem

30、ory ScriptStarting backup at 08-MAR-11allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=45 devicetype=DISKFinished backup at 08-MAR-11contents of Memory Script:backup as copy current controlfile for standbyauxiliary format/home/oracle/app/oracle/oradata/dave/control01.ctl;restore clone controlfil

31、e to /home/oracle/app/oracle/oradata/dave/control02.ctl from/home/oracle/app/oracle/oradata/dave/control01.ctl;restore clone controlfile to /home/oracle/app/oracle/oradata/dave/control03.ctlfrom/home/oracle/app/oracle/oradata/dave/control01.ctl;-创建控制文件executing Memory ScriptStarting backup at 08-MAR

32、-11using channel ORA_DISK_1channelORA_DISK_1: starting datafile copycopyingstandby control fileoutputfilename=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbapcf_orcl.ftag=TAG20110308T161152RECID=4ST=745258313channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03Finished backup at 08-

33、MAR-11Starting restore at 08-MAR-11using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: copiedcontrolfilecopyFinished restore at 08-MAR-11Starting restore at 08-MAR-11using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: copiedcontrolfilecopyFinished restore at 08-MAR-11contents of Memory Script:sql cl

34、one alter database mount standby database;-将备库启动到 mount standbyexecuting Memory Scriptsql sement: alter databasemount standby databasecontents of Memory Script:set newname for tempfile1 to/home/oracle/app/oracle/oradata/dave/temp01.dbf;switch clone tempfile all;set newname for datafile1 to/home/orac

35、le/app/oracle/oradata/dave/system01.dbf;set newname for datafile2 to/home/oracle/app/oracle/oradata/dave/sysaux01.dbf;set newname for datafile3 to/home/oracle/app/oracle/oradata/dave/undotbs01.dbf;set newname for datafile4 to/home/oracle/app/oracle/oradata/dave/users01.dbf;backup as copy reusedatafi

36、le 1 auxiliary format/home/oracle/app/oracle/oradata/dave/system01.dbfdatafile2 auxiliary format/home/oracle/app/oracle/oradata/dave/sysaux01.dbfdatafile3 auxiliary format/home/oracle/app/oracle/oradata/dave/undotbs01.dbfdatafile4 auxiliary format/home/oracle/app/oracle/oradata/dave/users01.dbf;sql

37、alter system archive logcurrent;-将 datafile convert 到其他目录executing Memory Scriptexecutingd:SET NEWNAMErenamed tempfile 1to /home/oracle/app/oracle/oradata/dave/temp01.dbfin control fileexecutingd:SET NEWNAMEexecutingd:SET NEWNAMEexecutingd:SET NEWNAMEexecutingd:SET NEWNAMEStarting backup at08-MAR-11

38、using channel ORA_DISK_1channel ORA_DISK_1: starting datafilecopy-开始 copy datafile，如果数据文件比较大，这个会比较慢input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbfoutput file name=/home/oracle/app/oracle/oradata/dave/system01.dbf tag=TAG20110308T161204channel ORA_DISK_1:datafi

39、le copy complete, elapsed time: 00:00:55channel ORA_DISK_1:starting datafile copyinput datafile filenumber=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbfoutput file name=/home/oracle/app/oracle/oradata/dave/sysaux01.dbf tag=TAG20110308T161204channel ORA_DISK_1:datafile copy complete, e

40、lapsed time: 00:00:35channel ORA_DISK_1:starting datafile copyinput datafile filenumber=00003 name=/home/oracle/app/oracle/oradata/orcdotbs01.dbfoutput file name=/home/oracle/app/oracle/oradata/dave/undotbs01.dbf tag=TAG20110308T161204channel ORA_DISK_1:datafile copy complete, elapsed time: 00:00:15

41、channel ORA_DISK_1:starting datafile copyinput datafile filenumber=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbfoutput file name=/home/oracle/app/oracle/oradata/dave/users01.dbf tag=TAG20110308T161204channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 0

42、8-MAR-11sql sement: alter system archive log currentcontents of Memory Script:switch clone datafile all;executing Memory Scriptdatafile 1 switchedto datafilecopyinput datafile copyRECID=4 ST=745258432filename=/home/oracle/app/oracle/oradata/dave/system01.dbfdatafile 2 switchedto datafilecopyinput da

43、tafile copyRECID=5 ST=745258432filename=/home/oracle/app/oracle/oradata/dave/sysaux01.dbfdatafile 3 switchedto datafilecopyinput datafile copyRECID=6 ST=745258432filename=/home/oracle/app/oracle/oradata/dave/undotbs01.dbfdatafile 4 switchedto datafilecopyinput datafile copyRECID=7 ST=745258432filena

44、me=/home/oracle/app/oracle/oradata/dave/users01.dbfFinished Duplicate Db at 08-MAR-11RMANDG到这一步已经操作完成了。 但是还有一些细节需要处理。三. 后续工作1. 主库已经使用了 spfile，但是备库用的还是之前的 pfile：（前面已说明Standby 还使用的是 pfile 没有转换为 spfile ）Primary:SQL shorametfileNAMETYPEVALUEspfilestring/home/oracle/app/oracle/produc t/11.2.0/dbhome_1/db

45、s/spfileorcl.oraStandby:SQL shorametfileNAMETYPEVALUEspfilestring所以需要转换 Standby 让他转换加载 pfile 文件到 spfileSQL shutdown immediate Database closed.Database dismounted.ORACLE instanhut down.启动新的 pfile（这会由于控制文件等都已拷贝，所以 没有问题了可以启动，不会报错！）SQLstartup pfile=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/ini

46、torcl.oraORACLE instantarted.Total System Global Fixed SizeVariable Size Database Buffers Redo Buffers Database mounted.Database opened.Area41754624022139362768260641342177284288512bytes bytes bytes bytesbytes把修改好的 initorcl.ora数据转换写入：spfileorcl.ora,SQL create spfile from pfile=/home/oracle/app/oracl

47、e/product/11.2.0/dbhome_1/d bs/initorcl.ora;5. 在备库添加 standby redo log：SQL alter database add standby logfile /home/oracle/app/oracle/oradata/dave/stdbyredo01.logalter database add standby logfile /home/oracle/app/oracle/oradata/dave/stdbyredo01.log sizesize50m50m;ERRORine 1:ORA-01156: recovery or fl

48、ashback in progress may need acs to files-在备库添加 standby redo log 需要先停 MRPSQL alter databaserecover managed standby database cancel;Database altered.在添加SQL alter database Database altered.SQL alter database Database altered.SQL alter database Database altered.SQL alter databaseDatabase altered.addstandbylogfile/home/oracle/app/oracle/oradata/dave/stdbyredo01.logsize50m;addstandbylogfile/home/oracle/app/oracle/oradata/dave/stdbyredo02.logsize50m;addstandbylogfile/home/oracle/app/oracle/oradata/dave/stdbyredo03.logsize50m;addstandbylogfile/home/oracle/app/oracle/oradata/dave/stdbyre