文稿成果講稿guard

1、Oracle : 11.2.0OS: redhat 5.564PrimaryIP 10.180.7.40DB_NAME=orclStandby IP :10.180.7.30DB_NAME=orcl一、 Primary操作1、設(shè)置歸檔模式先開啟數(shù)據(jù)庫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數(shù)據(jù)庫已打開No Archive ModeDisabled數(shù)據(jù)庫歸檔模式無自動歸檔USE_DB_RECOVERY_FILE_DEST 810關(guān)閉數(shù)據(jù)庫，啟用掛載模式 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設(shè)置歸檔模式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設(shè)置 forcelogging查看 force logging是否打開SQL FOR-NOselect force_loggingfrom v$database;沒有開啟force loggingalter database force開啟SQLlogging;Database altered.現(xiàn)在看已打開了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 =添加內(nèi)容=(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcl)-配置靜態(tài)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下面為添加的內(nèi)容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 參數(shù)，設(shè)置備份創(chuàng)建 pfile 文件，添加

14、如下內(nèi)容SQLcreate pfile from spfile;（此命令會在/11.2.0/dbhome_1/dbs 生成文件initorcl.ora，打開編輯在最后添加內(nèi)容）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 所以這個目錄需要自己創(chuàng)建oraclePrimary dbs$ mkdir /home/oracle/app/oracle/oradata/archiveStandby 歸檔文件的存放位置按如下規(guī)則來進(jìn)行： （對上述設(shè)置進(jìn)行說明）（1）當(dāng) LOG_ARCHIVE_DEST_n 設(shè)置了 valid_for=(all_logfiles,all_roles)，那么在不定義 standby_archive_dest 參數(shù)時，Oracle 就會選擇LOG_ARCHIVE_DE

17、ST_n 參數(shù)作為歸檔目標(biāo)。（2）如果在第一步設(shè)置的同時，又獨立設(shè)置 LOG_ARCHIVE_DEST_n 參數(shù)為 valid_for=(standby_logfile,*)10.0 的時候，會自動的選擇任意一個 LOG_ARCHIVE_DEST_n 的值。（3）如果 LOG_ARCHIVE_DEST_n 沒有設(shè)置的話，默認(rèn)位置是：$ORACLE_HOME/dbs.屬性，那么當(dāng) compatible 參數(shù)大于不過 valid_for 參數(shù)的默認(rèn)值就是 all_logfiles 和 all_roles.所以只要設(shè)置了本地的歸檔位置，的歸檔文件也會放到這個目錄下面。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數(shù)據(jù)轉(zhuǎn)換寫入：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 端設(shè)置：1. 創(chuàng)建相關(guān)目錄結(jié)構(gòu)oraclestandby dbs $ mkdir -p /home/or

20、acle/app/oracle/oradata/dave-這里創(chuàng)建的目錄和庫不同，在參數(shù)文件里需要轉(zhuǎn)換一下。2. 創(chuàng)建 standby 的口令文件oraclestandby dbs $ orapwd file=?/dbs/orapwdave password=password3. 創(chuàng)建 standby 的初始化參數(shù)：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 =添加內(nèi)容=(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)(SID_NAME=orcl)-配置靜態(tài)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下面為添加的內(nèi)容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 所有操作完成后在進(jìn)行轉(zhuǎn)換，）SQL startup nomount pfile=?/dbs/initorcl.ora報錯，Pfile 文件轉(zhuǎn)換為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下面內(nèi)容自動生成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 創(chuàng)建 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;-創(chuàng)建控制文件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，如果數(shù)據(jù)文件比較大，這個會比較慢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到這一步已經(jīng)操作完成了。 但是還有一些細(xì)節(jié)需要處理。三. 后續(xù)工作1. 主庫已經(jīng)使用了 spfile，但是備庫用的還是之前的 pfile：（前面已說明Standby 還使用的是 pfile 沒有轉(zhuǎn)換為 spfile ）Primary:SQL shorametfileNAMETYPEVALUEspfilestring/home/oracle/app/oracle/produc t/11.2.0/dbhome_1/db

45、s/spfileorcl.oraStandby:SQL shorametfileNAMETYPEVALUEspfilestring所以需要轉(zhuǎn)換 Standby 讓他轉(zhuǎn)換加載 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數(shù)據(jù)轉(zhuǎn)換寫入：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