DataGuard環(huán)境搭建詳細(xì)步驟(老方法)

1、一：實(shí)驗(yàn)環(huán)境介紹*鑒于生產(chǎn)環(huán)境均采用64位系統(tǒng)和數(shù)據(jù)庫軟件，本實(shí)驗(yàn)也將采用這個(gè)標(biāo)準(zhǔn)*采用vmware workstation 10（版本隨意，我用的10）虛擬機(jī)軟件作為安裝平臺對于真機(jī)的要求：CPU：INTEL：CORE I3級別或以上（推薦） AMD ：4核心CPU或以上（推薦）內(nèi)存：建議4G以上系統(tǒng)：WIN7 /8 64位/WINDOWS SERVER 2003/2008 64位硬盤：虛擬機(jī)所在分區(qū)剩余空間50G以上，固態(tài)硬盤最好虛擬機(jī)系統(tǒng)：Oracle Enterprise Linux 5 update 5（64位）數(shù)據(jù)庫版本：Oracle 11gR2 （64位）基礎(chǔ)要

2、求：（1）安裝兩臺虛擬機(jī)分別作為主庫和備庫，硬盤大小隨意。但要保證根分區(qū)有20G，別給太小了就行。物理內(nèi)存1G，SWAP 2G 二：安裝數(shù)據(jù)庫軟件前的系統(tǒng)配置（主庫和備庫端都要做下列操作）1基本配置：一定要用新裝的系統(tǒng)（這里以紅帽企業(yè)版5.5為例），系統(tǒng)要求最低配置1G內(nèi)存，2Gswap分區(qū)，根分區(qū)20G以上，裝好系統(tǒng)后先配置好yum，IP地址，/etc/sysconfig/network文件中的主機(jī)名，以及/etc/hosts文件中的IP地址和主機(jī)名的對應(yīng)關(guān)系。主庫IP：01主庫主機(jī)名：pri備庫IP：02備庫主機(jī)名：std2裝包yum insta

3、ll compat-libstdc+-33 ksh gcc gcc-c+ libgomp elfutils-libelf-devel glibc-devel glibc-headers libaio-devel libstdc+-devel sysstat unixODBC unixODBC-devel -y 3查看共享內(nèi)存大小，要求最低為1GB# df -h /dev/shmFilesystem Size Used Avail Use% Mounted ontmpfs 506M 0 506M 0% /dev/shm發(fā)現(xiàn)不夠，修改# vim /etc/fstab如圖，在tmpfs那一行的def

4、aults后面加上,size=1024m保存退出后重新掛載：# mount -o remount /dev/shm再次查看共享內(nèi)存：# df -h /dev/shmFilesystem Size Used Avail Use% Mounted ontmpfs 1.0G 0 1.0G 0% /dev/shm修改成功4創(chuàng)建相關(guān)的組與用戶：groupadd oinstallgroupadd dbagroupadd operuseradd -g oinstall -G dba,oper oraclepasswd oracle5創(chuàng)建所需的目錄并賦予特定的屬主和屬組mkdir /u01/app/oracl

5、e -pvchown oracle.oinstall /u01 -R建議：最好為單獨(dú)分一個(gè)區(qū)，然后將u01掛載到該分區(qū)上（可不做，看情況）6編輯limits.conf 文件vim /etc/security/limits.conf添加下面5行內(nèi)容：oracle soft nproc 2047oracle hard nproc 16384oracle soft nofile 1024oracle hard nofile 65536oracle soft stack 102407編輯sysctl.conf文件，設(shè)置相關(guān)參數(shù)vim /etc/sysctl.conf添加/修改下列內(nèi)容：（注意！下面的參

6、數(shù)，若是已經(jīng)存在，則直接修改數(shù)值，不要再添加同樣的參數(shù)，相同的參數(shù)只能有一個(gè)！如果需要修改的參數(shù)已經(jīng)大于下面的數(shù)字，則不用修改，請仔細(xì)核對?。﹌ernel.shmall = 2097152 kernel.shmmax = 536870912 kernel.shmmni = 4096kernel.sem =250 32000 100 128fs.file-max = 6815744fs.aio-max-nr = 1048576net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_m

7、ax = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576保存退出后，別忘了用sysctl -p命令使參數(shù)生效！8編輯vim /home/oracle/.bash_profile文件，配置相關(guān)環(huán)境變量添加下列幾行：export TMP=/tmpexport TMPDIR=/tmpexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1export ORACLE_SID=pri(備庫端設(shè)置為std)

8、export PATH=$ORACLE_HOME/bin:$PATH至此，安裝環(huán)境相關(guān)配置結(jié)束。三：開始安裝oracle11g數(shù)據(jù)庫軟件（主庫和備庫端都要做下列操作）1.掛載oracle11g的鏡像到/mnt/cdrom 目錄下，并把3個(gè)包解壓到/usr/local/src 目錄下cd /mnt/cdrom/unzip p10404530_112030_LINUX_1of7.zip -d /usr/local/srcunzip p10404530_112030_LINUX_2of7.zip -d /usr/local/srcunzip p10404530_112030_LINUX_3of7.z

9、ip -d /usr/local/src(只裝數(shù)據(jù)庫的話，解壓前兩個(gè)包即可，第三個(gè)包是grid)2. root身份執(zhí)行xhost +，然后切換到oracle用戶進(jìn)入/usr/local/src/database目錄中，執(zhí)行runInstaller 開始安裝 (database是默認(rèn)解壓完的目錄名)# xhost + # su - oracle$ ./runInstaller 注意：安裝oracle11g要求分辨率最低為1024x768,不然無法完整顯示安裝過程安裝過程：略注意：主庫在安裝完軟件后需要進(jìn)行DBCA建庫操作，推薦不要啟用歸檔模式，會(huì)節(jié)省時(shí)間，備庫端不要建庫！四：開始搭建Datagu

10、ard1：將數(shù)據(jù)庫改為強(qiáng)制日志模式（此步驟只在主庫上做）oraclepri $ sqlplus / as sysdba查看當(dāng)前是否強(qiáng)制日志模式：SYSpri> select name,log_mode,force_logging from v$database;NAME LOG_MODE FOR- - -PRI NOARCHIVELOG NOSYSpri> alter database force logging;Database altered.SYSpri> select name,log_mode,force_logging from v$database;NAME L

11、OG_MODE FOR- - -PRI NOARCHIVELOG YES2：創(chuàng)建密碼文件（此步驟只在主庫上做）注意：兩端分別創(chuàng)建自己的密碼文件好像有問題，備庫的密碼文件需要跟主庫一致，否則導(dǎo)致日志傳輸不到備庫，有待驗(yàn)證。我最后是將主庫的密碼文件直接copy到備庫，重命名后使用。oraclepri $ cd $ORACLE_HOME/dbsoraclepri dbs$ lshc_racdb1.dat init.ora initracdb1.ora orapwpri已經(jīng)有一個(gè)密碼文件了oraclepri dbs$ orapwd file=orapwpri password=oracle force

12、=y這條命令可以手動(dòng)生成密碼文件，force=y的意思是強(qiáng)制覆蓋當(dāng)前已有的密碼文件oraclepri dbs$ lshc_pri.dat init.ora initpri.ora lkPRI orapwpri spfilepri.ora將主庫的密碼文件copy給備庫，并重命名oraclepri dbs$ scp orapwpri 02:$ORACLE_HOME/dbs/orapwstdorapwpri 100% 1536 1.5KB/s 00:00 3：創(chuàng)建standby redolog日志組（此步驟只在主庫上做）原則：1：standby redo log的文件大小與pr

13、imary 數(shù)據(jù)庫online redo log 文件大小相同2：standby redo log日志文件組的個(gè)數(shù)依照下面的原則進(jìn)行計(jì)算：Standby redo log組數(shù)公式>=(每個(gè)instance日志組個(gè)數(shù)+1)*instance個(gè)數(shù)假如只有一個(gè)節(jié)點(diǎn)，這個(gè)節(jié)點(diǎn)有三組redolog，所以Standby redo log組數(shù)>=(3+1)*1 = 4 所以至少需要?jiǎng)?chuàng)建4組Standby redo log查看當(dāng)前線程與日志組的對應(yīng)關(guān)系及日志組的大小：SYSpri> select thread#,group#,bytes/1024/1024 from v$log; THREA

14、D# GROUP# BYTES/1024/1024 - - - 1 1 50 1 2 50 1 3 50如上，我現(xiàn)在的環(huán)境有三組redolog，每個(gè)日志組的大小都是50M，所以Standby redo log組數(shù)>=(3+1)*1= 4所以至少需要?jiǎng)?chuàng)建4組Standby redo log，大小均為50M（thread：線程，只有在多實(shí)例數(shù)據(jù)庫才有用的參數(shù)，例如RAC環(huán)境，單實(shí)例不考慮）查看當(dāng)前有哪些日志組及其成員：SYSpri> col member for a50SYSpri> select group#,member from v$logfile; GROUP# MEMB

15、ER- - 3 /u01/app/oracle/oradata/pri/redo03.log 2 /u01/app/oracle/oradata/pri/redo02.log 1 /u01/app/oracle/oradata/pri/redo01.log先手動(dòng)創(chuàng)建standby log日志組所需的目錄：（創(chuàng)建新目錄只是為了便于區(qū)分，并非必須）oraclepri dbs$ cd /u01/app/oracle/oradata/oraclepri oradata$ lsprioraclepri oradata$ mkdir standbylogoraclepri oradata$ lspri s

16、tandbylog新建4個(gè)日志組作為standby redolog日志組，大小與原來的日志組一致：由于已經(jīng)存在group1-3,，所以group號只能從4開始SYSpri> alter database add standby logfile group 4 2 '/u01/app/oracle/oradata/standbylog/std_redo04.log' 3 size 50m;Database altered.SYSpri> alter database add standby logfile group 5 2 '/u01/app/oracle/

17、oradata/standbylog/std_redo05.log' 3 size 50m;Database altered.SYSpri> alter database add standby logfile group 6 2 '/u01/app/oracle/oradata/standbylog/std_redo06.log' 3 size 50m;Database altered.SYSpri> alter database add standby logfile group7 2 '/u01/app/oracle/oradata/stand

18、bylog/std_redo07.log' 3 size 50m;Database altered.查看standby 日志組的信息：SYSpri> select group#,sequence#,status, bytes/1024/1024 from v$standby_log; GROUP# SEQUENCE# STATUS BYTES/1024/1024- - - - 4 0 UNASSIGNED 50 5 0 UNASSIGNED 50 6 0 UNASSIGNED 50 7 0 UNASSIGNED 50查看當(dāng)前有哪些日志組及其成員：SYSpri> set pa

19、gesize 100 SYSpri> col member for a60SYSpri> select group#,member from v$logfile order by group#; GROUP# MEMBER - 1 /u01/app/oracle/oradata/pri/redo01.log 2 /u01/app/oracle/oradata/pri/redo02.log 3 /u01/app/oracle/oradata/pri/redo03.log 4 /u01/app/oracle/oradata/standbylog/std_redo04.log 5 /u0

20、1/app/oracle/oradata/standbylog/std_redo05.log 6 /u01/app/oracle/oradata/standbylog/std_redo06.log 7 /u01/app/oracle/oradata/standbylog/std_redo07.log4：修改主庫的pfile參數(shù)文件（此步驟只在主庫上做）查看spfile的路徑：SYSpri> show parameter spfile;NAME TYPE VALUE- - -spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfi

21、lepri.ora用spfile創(chuàng)建一個(gè)pfile,用于修改：SYSpri> create pfile from spfile; 修改主庫的pfile：oraclepri $ cd $ORACLE_HOME/dbsoraclepri dbs$ vim initpri.orapri._db_cache_size=125829120pri._java_pool_size=4194304pri._large_pool_size=4194304pri._oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentpr

22、i._pga_aggregate_target=146800640pri._sga_target=272629760pri._shared_io_pool_size=0pri._shared_pool_size=125829120pri._streams_pool_size=4194304*.audit_file_dest='/u01/app/oracle/admin/pri/adump'*.audit_trail='db'*.compatible='.0'*.control_files='/u01/app/oracle/

23、oradata/pri/control01.ctl','/u01/app/oracle/fast_recovery_area/pri/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='pri'DG主庫和備庫的db_name必須一致，db_unique_name不一致*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'*.db_recovery_file_dest_size=42278584

24、32*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=priXDB)'*.memory_target=419430400*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1'下面幾行是需要手動(dòng)添加的：*.db_unique_name='pri'*.log_archive_co

25、nfig='dg_config=(pri,std)'*.log_archive_dest_1='location=/u01/app/arch/pri valid_for=(all_logfiles,all_roles) db_unique_name=pri'*.log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=std'*.log_archive_dest_state_1=enable*.log_archive_dest_s

26、tate_2=enable*.log_archive_max_processes=4*.fal_server='std'*.fal_client='pri'*.db_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri'*.log_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri'*.standby

27、_file_management='auto'修改完畢，保存退出手工創(chuàng)建/u01/app/arch/pri：oraclepri dbs$ mkdir /u01/app/arch/pri -pvmkdir: created directory /u01/app/arch'mkdir: created directory /u01/app/arch/pri'5：用修改過的pfile重新創(chuàng)建一個(gè)spfile，用于啟動(dòng)數(shù)據(jù)庫（此步驟只在主庫上做）關(guān)閉數(shù)據(jù)庫：SYSpri> shutdown immediate;Database closed.Database di

28、smounted.ORACLE instance shut down.SYSpri> create spfile from pfile;此時(shí)把數(shù)據(jù)庫改為歸檔模式：（如果當(dāng)初建庫時(shí)選擇了啟用歸檔，則此步驟忽略）由于當(dāng)前數(shù)據(jù)庫已關(guān)閉，首先需要把數(shù)據(jù)庫啟動(dòng)到mount狀態(tài)SYSpri> startup mount;ORACLE instance started.Database mounted.SYSpri> alter database archivelog;啟用歸檔模式Database altered.SYSpri> alter database open;OPEN數(shù)據(jù)庫

29、Database altered.SYSpri> archive log list;查看是否啟用歸檔模式Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/app/arch/priOldest online log sequence 6Next log sequence to archive 8Current log sequence 8如上，歸檔路徑已經(jīng)改為/u01/app/arch/pri，證明對pfile的修改已生效查看當(dāng)前數(shù)據(jù)庫是否使用spfile啟動(dòng)：SYSpri>

30、; show parameter spfile;NAME TYPE VALUE- - -spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfilepri.ora如上，若能看到spfile的路徑，則證明數(shù)據(jù)庫是使用spfile啟動(dòng)的，若沒有值，則說明是用pfile啟動(dòng)的。確認(rèn)數(shù)據(jù)庫已經(jīng)啟用歸檔模式和強(qiáng)制日志模式：SYSpri> select name,log_mode,force_logging from v$database;NAME LOG_MODE FOR- - -PRI ARCHIVELOG YES5：對主庫進(jìn)行RMAN

31、全備創(chuàng)建備份所需的目錄并賦予相應(yīng)權(quán)限：（主庫和備庫端都要?jiǎng)?chuàng)建）rootpri # mkdir /rman_backuprootpri # chown oracle.oinstall /rman_backup/rootstd # mkdir /rman_backuprootstd # chown oracle.oinstall /rman_backup/以oracle用戶身份登錄，進(jìn)行備份操作：（只在主庫上做）rootpri # su - oracleoraclepri $ rman target /RMAN> backup database format '/rman_backu

32、p/Full_%T_%U.bak'備份數(shù)據(jù)庫Starting backup at 14-JAN-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=41 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinp

33、ut datafile file number=00001 name=/u01/app/oracle/oradata/pri/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/pri/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/pri/example01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/pri/un

34、dotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/pri/users01.dbfchannel ORA_DISK_1: starting piece 1 at 14-JAN-14channel ORA_DISK_1: finished piece 1 at 14-JAN-14piece handle=/rman_backup/Full_20140114_01ou2791_1_1.bak tag=TAG20140114T143721 comment=NONEchannel ORA_DISK_1: ba

35、ckup set complete, elapsed time: 00:00:45channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 14-JAN-14channel ORA_DISK_1:

36、 finished piece 1 at 14-JAN-14piece handle=/rman_backup/Full_20140114_02ou27ae_1_1.bak tag=TAG20140114T143721 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 14-JAN-14RMAN> list archivelog all;查看當(dāng)前的歸檔日志有哪些List of Archived Log Copies for database with

37、db_unique_name PRI=Key Thrd Seq S Low Time - - - - -1 1 6 A 13-JAN-14 Name: /u01/app/arch/pri/1_6_836760649.dbf2 1 7 A 14-JAN-14 Name: /u01/app/arch/pri/1_7_836760649.dbfRMAN> backup archivelog all format'/rman_backup/ARC_%T_%U.bak'備份歸檔日志Starting backup at 14-JAN-14current log archivedusi

38、ng channel ORA_DISK_1channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=6 RECID=1 STAMP=836835599input archived log thread=1 sequence=7 RECID=2 STAMP=836835600input archived log thread=1 sequence=8 RECI

39、D=3 STAMP=836836802channel ORA_DISK_1: starting piece 1 at 14-JAN-14channel ORA_DISK_1: finished piece 1 at 14-JAN-14piece handle=/rman_backup/ARC_20140114_03ou27e3_1_1.bak tag=TAG20140114T144002 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 14-JAN-14R

40、MAN> backup current controlfile for standby format'/rman_backup/std_con.ctl'備份standby控制文件并命名為std_con.ctlStarting backup at 14-JAN-14using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding standby contro

41、l file in backup setchannel ORA_DISK_1: starting piece 1 at 14-JAN-14channel ORA_DISK_1: finished piece 1 at 14-JAN-14piece handle=/rman_backup/std_con.ctl tag=TAG20140114T144036 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 14-JAN-14RMAN> list back

42、up;查看都有哪些備份List of Backup Sets=BS Key Type LV Size Device Type Elapsed Time Completion Time- - - - - - -1 Full 1.04G DISK 00:00:35 14-JAN-14 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140114T143721 Piece Name: /rman_backup/Full_20140114_01ou2791_1_1.bak List of Datafiles in backup set 1 Fi

43、le LV Type Ckp SCN Ckp Time Name - - - - - - 1 Full 1064545 14-JAN-14 /u01/app/oracle/oradata/pri/system01.dbf 2 Full 1064545 14-JAN-14 /u01/app/oracle/oradata/pri/sysaux01.dbf 3 Full 1064545 14-JAN-14 /u01/app/oracle/oradata/pri/undotbs01.dbf 4 Full 1064545 14-JAN-14 /u01/app/oracle/oradata/pri/use

44、rs01.dbf 5 Full 1064545 14-JAN-14 /u01/app/oracle/oradata/pri/example01.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time- - - - - - -2 Full 9.36M DISK 00:00:01 14-JAN-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140114T143721 Piece Name: /rman_backup/Full_20140114_02ou27ae_

45、1_1.bak SPFILE Included: Modification time: 14-JAN-14 SPFILE db_unique_name: PRI Control File Included: Ckp SCN: 1064580 Ckp time: 14-JAN-14BS Key Size Device Type Elapsed Time Completion Time- - - - -3 16.06M DISK 00:00:00 14-JAN-14 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20140114T144002

46、 Piece Name: /rman_backup/ARC_20140114_03ou27e3_1_1.bak List of Archived Logs in backup set 3 Thrd Seq Low SCN Low Time Next SCN Next Time - - - - - - 1 6 1051084 13-JAN-14 1062515 14-JAN-14 1 7 1062515 14-JAN-14 1062557 14-JAN-14 1 8 1062557 14-JAN-14 1064725 14-JAN-14BS Key Type LV Size Device Typ

47、e Elapsed Time Completion Time- - - - - - -4 Full 9.33M DISK 00:00:02 14-JAN-14 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20140114T144036 Piece Name: /rman_backup/std_con.ctl Standby Control File Included: Ckp SCN: 1064792 Ckp time: 14-JAN-14RMAN>quit6：修改監(jiān)聽文件，添加靜態(tài)監(jiān)聽（主庫、備庫都要做）主庫：oraclepri

48、 $ cd $ORACLE_HOME/network/adminoraclepri admin$ vim listener.ora 添加如下內(nèi)容：SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = pri) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = pri) ) )主庫修改后最終效果如下圖：備庫：oraclepri $ cd $ORACLE_HOME/network/adminoraclepri admin$ vim listener.or

49、a 添加如下內(nèi)容：SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = std) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = std) ) )備庫修改后最終效果如下圖：使新增加的監(jiān)聽生效：（主庫和備庫端都要做）oraclepri admin$ lsnrctl stoporaclepri admin$ lsnrctl start 確認(rèn)新增加的靜態(tài)監(jiān)聽有效：主庫：oraclepri $ lsnrctl status.(N行省略)Service "pri" has 2 instance(s). Instance "pri", status UNKNOWN, has 1 handler(s) for this service. Instance "pri", status READY, has 1 handler(s) for this service.Service "priXDB" has 1 instance(s). Instanc