圖文解說DG災(zāi)備環(huán)境搭建步驟

1、前言為了學(xué)習(xí)DG，我們需要有學(xué)習(xí)環(huán)境來支持，因此搭建DG環(huán)境是學(xué)習(xí)DG的第一步。在搭建GC的文檔里，有介紹虛擬機(jī)模板的搭建，這里就直接用這個虛擬機(jī)模板來搭建DG，搭建之前先下載11201的數(shù)據(jù)庫軟件，為了后面的滾動升級實驗。下面就來介紹怎么搭建DG學(xué)習(xí)環(huán)境！ 第一章 制作DG虛擬機(jī)模板下載11201數(shù)據(jù)庫軟件點擊接受，下載32bit或者64bit軟件本人下載的是64bit的軟件把兩個壓縮包都下載下來還原虛擬機(jī)模板把在搭建GC環(huán)境時的虛擬機(jī)模板還原出來改名為prod裝載虛擬機(jī)右鍵rename為prod啟動虛擬機(jī)先登錄進(jìn)去cd /etc/sysconfig/network-scriptslsvi

2、ifcfg-eth0.bak把HWADDR刪除掉，然后改一下IPADDR保存退出mv ifcfg-eth0.bak ifcfg-eth0lsservice network restartifconfigexit用secureCRT創(chuàng)建新連接這個提示用同樣的方法解決設(shè)置好顏色字體，prod的會話連接創(chuàng)建完畢vi /etc/sysconfig/network把主機(jī)名改成prod.localdomain修改好之后保存退出hostname prod.localdomain退出后再登錄修改hostsvi /etc/hosts127.0.0.1 localhost.localdomain localhos

3、t:1 localhost6.localdomain localhost6192.168.75.61 prod.localdomain prod192.168.75.62 standby.localdomain standby保存退出安裝oracle軟件先安裝10201的軟件來搭建DG后面有滾動升級成11201的方法把10201的oracle軟件傳送到/u01目錄下傳輸好之后修改軟件屬于者為oraclecd /u01ls -lrtchown -R oracle:oinstall *ls -lrt設(shè)置oracle環(huán)境變量su - oraclevi .bash_profile把下面內(nèi)容復(fù)制到.ba

4、sh_profile最后export PATHexport TMP=/tmpexport TMPDIR=$TMPexport ORACLE_BASE=/u01export ORACLE_SID=prodexport ORACLE_HOME=/u01/oracle/db_1export ORACLE_TERM=xtermexport PATH=$PATH:$ORACLE_HOME/bin:$HOME/binexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin/export CLASS

5、PATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibexport DISPLAY=192.168.75.1:0.0stty erase halias sqlplus='rlwrap sqlplus'alias rman='rlwrap rman'保存后退出執(zhí)行. .bash_profile讓環(huán)境變量生效解壓縮oracle軟件cd /u01ls -lrtgzip -dc 10201_database_linux_x86_64.cpio.gz | cpio -icvdBumls -lrtcd

6、database在windows端打開xmanager的passive來接收遠(yuǎn)程圖形雙擊它就可以了看到右下角有圖標(biāo)且是:0.0端口就行./runInstaller另開一個窗口用root用戶執(zhí)行上面腳本10201軟件安裝完成！把安裝軟件刪除，節(jié)省空間cd .ls -lrtrm -f 10201_database_linux_x86_64.cpio.gzrm -fR databasels -lrtdf -h備份復(fù)制模板至此，完成了虛擬機(jī)模板的制作，可以把虛擬機(jī)目錄拷貝一份保存起來關(guān)機(jī)，然后復(fù)制一份作為備庫使用第二章 搭建物理備庫配置standby服務(wù)器把復(fù)制好的虛擬機(jī)改名為standby裝載虛擬機(jī)

7、右鍵rename為standby打開虛擬機(jī)先登錄進(jìn)去cd /etc/sysconfig/network-scriptsls因為prod虛擬機(jī)沒開，所以IP地址沒有發(fā)生沖突，但是還是要改一下vi ifcfg-eth0改成192.168.75.62保存退出service network restartifconfigexit用secureCRT創(chuàng)建新連接這個提示用同樣的方法解決設(shè)置好顏色字體，standby的會話連接創(chuàng)建完畢vi /etc/sysconfig/network把主機(jī)名改成standby.localdomain修改好之后保存退出hostname standby.localdomain退

8、出后再登錄查看hostscat /etc/hosts一切正常！修改ORACLE_SIDsu - oraclevi .bash_profile把環(huán)境變量ORACLE_SID的值修改成standby修改好之后保存退出. .bash_profile讓環(huán)境變量生效創(chuàng)建主數(shù)據(jù)庫prod啟動prod虛擬機(jī)然后用netca命令配置監(jiān)聽su - oraclenetca這個就設(shè)置成默認(rèn)監(jiān)聽，用1521端口看一下信息用dbca命令創(chuàng)建prod數(shù)據(jù)庫要和.bash_profile里配置的ORACLE_SID參數(shù)一樣不安裝DB Control的em設(shè)置sys用戶的密碼不使用flash recovery area字符集

9、選ZHS16GBK，當(dāng)然你選AL32UTF8也可以開始創(chuàng)建數(shù)據(jù)庫解鎖幾個用戶prod數(shù)據(jù)庫創(chuàng)建完畢！設(shè)置prod庫登陸到sqlplus檢查一下歸檔模式和force loggingsqlplus / as sysdbaselect log_mode,force_logging from v$database;如果是非歸檔就要關(guān)閉數(shù)據(jù)庫修改shutdown immediatestartup mount干凈地關(guān)閉數(shù)據(jù)庫再啟動到mount狀態(tài)改成歸檔模式和強(qiáng)制loggingalter database archivelog;alter database force logging;select log

10、_mode,force_logging from v$database;archive log list更改歸檔目錄!mkdir /u01/oradata/prod/archivelogls -l /u01/oradata/prodexitalter system set log_archive_dest_1='location=/u01/oradata/prod/archivelog'archive log listalter database open;創(chuàng)建pfilecreate pfile from spfile;alter database create standby

11、 controlfile as '/u01/oracle/standby.ctl'把pfile文件、口令文件和備控制文件傳輸?shù)絪tandbyexitcd /u01/oracle/db_1/dbsls -lrtscp orapwprod 192.168.75.62:/u01/oracle/db_1/dbsscp initprod.ora 192.168.75.62:/u01/oracle/db_1/dbsscp /u01/oracle/standby.ctl 192.168.75.62:/u01/oracle需要輸入oracle用戶密碼備控制文件只要復(fù)制到與prod庫的路徑相同就

12、行，兩邊都是/u01/oracle/standby.ctlprod設(shè)置先告一段落設(shè)置standby數(shù)據(jù)庫cd /u01/oracle/db_1/dbsls -lrt把口令文件和pfile名字改成standby的mv orapwprod orapwstandbymv initprod.ora initstandby.oravi initstandby.ora把最上面幾行去掉，然后把上面標(biāo)注的prod都改成standby注意db_name的prod值不要修改在最后添加以下參數(shù)*.db_unique_name='standby'*.db_file_name_convert='

13、/u01/oradata/prod','/u01/oradata/standby'*.log_file_name_convert='/u01/oradata/prod','/u01/oradata/standby'*.log_archive_config='dg_config=(prod,standby)'*.log_archive_dest_1='location=/u01/oradata/standby/archivelog valid_for=(all_logfiles,all_roles) db_uniqu

14、e_name=standby'*.log_archive_dest_2='service=prod lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prod'*.log_archive_dest_state_1=enable*.log_archive_dest_state_2=enable*.log_archive_max_processes=10*.fal_server=prod*.fal_client=standby*.standby_file_management=auto保存退

15、出創(chuàng)建一些目錄mkdir -p /u01/admin/standby/adumpmkdir -p /u01/admin/standby/bdumpmkdir -p /u01/admin/standby/cdumpmkdir -p /u01/admin/standby/udumpmkdir -p /u01/admin/standby/dpdumpls -l /u01/admin/standbymkdir -p /u01/oradata/standby/archivelogls -l /u01/oradata/standby配置standby庫的listener和靜態(tài)服務(wù)名netmgr再添加靜態(tài)服

16、務(wù)名選擇save network configuration，保存一下關(guān)閉lsnrctl start配置連接到prod的網(wǎng)絡(luò)服務(wù)名netcatnsping prod用pfile啟動到nomount狀態(tài)env | grep ORA登陸sqlplus之前先檢查一下環(huán)境變量sqlplus / as sysdbacreate spfile from pfile='?/dbs/initstandby.ora'startup nomountstandby庫的配置告一段落，重新回到prod庫的配置配置prod數(shù)據(jù)庫再次回到prod庫的設(shè)置配置連接到standby的網(wǎng)絡(luò)服務(wù)名netca備庫的地

17、址，備庫監(jiān)聽用的是1521端口tnsping standby配置prod庫參數(shù)sqlplus / as sysdbaalter system set log_archive_config='dg_config=(prod,standby)'alter system set log_archive_dest_1='location=/u01/oradata/prod/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=prod'alter system set log_archive_dest_

18、2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby'alter system set log_archive_dest_state_1=enable;alter system set log_archive_dest_state_2=enable;alter system set log_archive_max_processes=10;alter system set fal_server=standby;alter system set f

19、al_client=prod;alter system set standby_file_management=auto;還有三需要重啟才生效的參數(shù)也設(shè)置一下alter system set db_unique_name='prod'scope=spfile;alter system set db_file_name_convert='/u01/oracle/standby','/u01/oracle/prod' scope=spfile;alter system set log_file_name_convert='/u01/oracl

20、e/standby','/u01/oracle/prod' scope=spfile;備份prod庫exitmkdir -p /u01/backupsetls -l /u01創(chuàng)建了備份目錄登陸到rman開始備份rman target /runallocate channel d1 type disk format '/u01/backupset/%U'backup database;release channel d1;把備份集目錄傳輸?shù)絪tandby的/u01目錄下exitscp -r /u01/backupset 192.168.75.62:/u01

21、可以在standby上檢查一下ls -l /u01開始克隆standby庫，在prod上運(yùn)行rman target / auxiliary sys/oraclestandbyrunallocate auxiliary channel d1 type disk;duplicate target database for standby nofilenamecheck;release channel d1;還原了standby庫！配置standby數(shù)據(jù)庫查詢standby庫狀態(tài)select status from v$instance;現(xiàn)在數(shù)據(jù)庫為mount狀態(tài)創(chuàng)建備庫的standby日志alte

22、r database add standby logfile ('/u01/oradata/standby/standby01.log') size 50m;alter database add standby logfile ('/u01/oradata/standby/standby02.log') size 50m;alter database add standby logfile ('/u01/oradata/standby/standby03.log') size 50m;alter database add standby logf

23、ile ('/u01/oradata/standby/standby04.log') size 50m;日志大小和主庫的一樣都是50M，數(shù)量比主庫多一組在prod庫切換幾個日志exitsqlplus / as sysdbaalter system switch logfile;在standby庫查看一下是否有歸檔日志了exitls -l /u01/oradata/standby/archivelog日志可以傳輸了就ok！備庫執(zhí)行日志應(yīng)用同步語句，開始運(yùn)行物理DGsqlplus / as sysdbaalter database recover managed standby d

24、atabase using current logfile disconnect from session;因為有備重做日志，所以可以加using current logfile語句,實現(xiàn)實時應(yīng)用select sequence#, applied from v$archived_log where applied='YES' order by sequence#;yes說明日志同步了在prod庫上執(zhí)行:conn scott/tigercreate table t(i int);insert into t values(1);commit;conn / as sysdbaalte

25、r system switch logfile;在standby庫上執(zhí)行:alter database recover managed standby database cancel;alter database open;conn scott/tigerselect * from t;數(shù)據(jù)已經(jīng)同步，物理DG搭建成功！看一下數(shù)據(jù)庫的open狀態(tài)conn / as sysdbaselect open_mode from v$database;注意：物理備庫open是默認(rèn)read only模式的alter database recover managed standby database usin

26、g current logfile disconnect from session;select open_mode from v$database;啟動數(shù)據(jù)同步語句后，數(shù)據(jù)庫又變?yōu)閙ounted狀態(tài)注意：在測試數(shù)據(jù)同步的時候請不要用sys用戶，不然可能同步不了，這里測試都是用scott用戶切換物理備庫在prod庫上執(zhí)行:select switchover_status from v$database;alter database commit to switchover to physical standby;這個命令要執(zhí)行蠻長時間，請耐心等待注意:上面switchover_status的值

27、如果是TO STANDBY，可以直接switchover，如果是sessions active，則需要在switchover的命令后面加上with session shutdown，比如alter database commit to switchover to physical standby with session shutdown;select status from v$instance;檢查一下數(shù)據(jù)庫狀態(tài)shutdown immediatestartup nomountalter database mount standby database;這條命令就是把主庫切成備庫在stand

28、by庫上執(zhí)行:select sequence#, applied from v$archived_log where applied='YES' order by sequence#;已經(jīng)和剛才的主庫同步完日志select switchover_status from v$database;alter database commit to switchover to primary;這條命令就是把備庫切成主庫select status from v$instance;查看一下數(shù)據(jù)庫狀態(tài)shutdown immediatestartup在prod庫上執(zhí)行:創(chuàng)建備日志alter d

29、atabase add standby logfile ('/u01/oradata/prod/standby01.log') size 50m;alter database add standby logfile ('/u01/oradata/prod/standby02.log') size 50m;alter database add standby logfile ('/u01/oradata/prod/standby03.log') size 50m;alter database add standby logfile ('/u

30、01/oradata/prod/standby04.log') size 50m;執(zhí)行同步語句alter database recover managed standby database using current logfile disconnect from session;select sequence#, applied from v$archived_log where applied='YES'order by sequence#;在standby庫上執(zhí)行:conn scott/tigerinsert into t values(2);commit;con

31、n / as sysdbaalter system switch logfile;在prod庫上執(zhí)行:alter database recover managed standby database cancel;alter database open;conn scott/tigerselect * from t;物理DG切換成功！conn / as sysdbaselect open_mode from v$database;alter database recover managed standby database using current logfile disconnect fro

32、m session;select open_mode from v$database;恢復(fù)成同步模式再切換一次，切換來原來的狀態(tài)，步驟是一樣的這里只是為了驗證是否ok在standby庫上執(zhí)行:select switchover_status from v$database;alter database commit to switchover to physical standby;shutdown immediatestartup nomountalter database mount standby database;在prod庫上執(zhí)行:select sequence#, applied

33、from v$archived_log where applied='YES' order by sequence#;select switchover_status from v$database;alter database commit to switchover to primary;select status from v$instance;查看一下數(shù)據(jù)庫狀態(tài)shutdown immediatestartup在standby庫上執(zhí)行:alter database recover managed standby database using current logfil

34、e disconnect from session;在prod庫上執(zhí)行:conn scott/tigerinsert into t values(3);commit;conn / as sysdbaalter system switch logfile;在standby庫上執(zhí)行:alter database recover managed standby database cancel;alter database open;conn scott/tigerselect * from t;conn / as sysdbaalter database recover managed standb

35、y database using current logfile disconnect from session;select open_mode from v$database;恢復(fù)到了原來的狀態(tài),即prod是主庫，standby是備庫第三章 搭建邏輯備庫配置邏輯備庫 邏輯備庫是在物理備庫的基礎(chǔ)上配置的，所以要搭建邏輯備庫先要創(chuàng)建好物理備庫才行在standby庫上執(zhí)行:停止備庫的applyalter database recover managed standby database cancel;注意:一定要先停止日志應(yīng)用，不然下面build的時候會有問題在prod庫上執(zhí)行:創(chuàng)建prod庫作

36、為邏輯備庫時的在線日志歸檔路徑exitmkdir -p /u01/oradata/prod/lgarchivels -l /u01/oradata/prod修改log_archive_dest_3參數(shù)，當(dāng)prod切換成備庫時起作用，即在線日志歸檔的路徑sqlplus / as sysdbaalter system set log_archive_dest_3='location=/u01/oradata/prod/lgarchive valid_for=(standby_logfiles,standby_role) db_unique_name=prod'alter syste

37、m set log_archive_dest_state_3=enable; 然后執(zhí)行execute dbms_logstdby.build;在standby庫上執(zhí)行:創(chuàng)建standby庫作為邏輯備庫時的在線日志歸檔路徑exitmkdir -p /u01/oradata/standby/lgarchivels -l /u01/oradata/standbysqlplus / as sysdbaalter system set log_archive_dest_3='location=/u01/oradata/standby/lgarchive valid_for=(standby_lo

38、gfiles,standby_role) db_unique_name=standby'alter system set log_archive_dest_state_3=enable; 檢查standby庫的臨時表空間數(shù)據(jù)文件select * from v$tempfile;有臨時文件信息，如果沒有，要添加臨時文件alter tablespace temp add tempfile xxxxxx.select status from v$instance;查看一下數(shù)據(jù)庫角色select database_role from v$database;startup mount force

39、alter database recover to logical standby standby;把備庫轉(zhuǎn)換成邏輯備庫，后一個standby是備庫名select status from v$instance;startup mount forceselect database_role from v$database;alter database open resetlogs;show parameter db_name此時備庫的db_name已經(jīng)改成standby是一個新庫了稍等片刻后在prod庫上執(zhí)行:alter system switch logfile;查看prod庫的alert信息

40、查看standby的alert信息在standby庫上執(zhí)行:查看歸檔日志是否傳輸?shù)絺鋷靍xitls -l /u01/oradata/standby/archivelog啟用實時日志應(yīng)用sqlplus / as sysdbaalter database start logical standby apply immediate;此時standby庫的alert日志里會產(chǎn)生大量信息prod庫的alert日志信息:配置完成，開始驗證在prod庫上執(zhí)行:conn scott/tigercreate table a(a varchar2(10);insert into a values('a&#

41、39;);commit;在standby庫上執(zhí)行:conn scott/tigerselect * from a;邏輯備庫配置成功！切換邏輯備庫 先把prod庫和standby庫的undo_retention參數(shù)都設(shè)置成3600conn / as sysdbaalter system set undo_retention=3600;開始切換邏輯備庫在prod庫上執(zhí)行:select switchover_status from v$database;alter database prepare to switchover to logical standby;select switchover_

42、status from v$database;在standby庫上執(zhí)行:select switchover_status from v$database;alter database prepare to switchover to primary;select switchover_status from v$database;在prod庫上執(zhí)行:這個時候觀察prod庫的switchover_status會發(fā)現(xiàn)select switchover_status from v$database;如果上述查詢的返回結(jié)果不是TO LOGICAL STANDBY 的話，你可能就需要取消此次轉(zhuǎn)換，檢查原

43、因，然后再重新操作了取消轉(zhuǎn)換的命令為:alter database prepare to switchover cancel;prod庫繼續(xù)執(zhí)行alter database commit to switchover to logical standby;在standby庫上執(zhí)行:再檢查standby庫的switchover_status會發(fā)現(xiàn)select switchover_status from v$database;alter database commit to switchover to primary;在prod庫上執(zhí)行:alter database start logical s

44、tandby apply immediate;在standby庫上執(zhí)行:conn scott/tigerinsert into a values('b');commit;conn / as sysdba在prod庫上執(zhí)行:conn scott/tigerselect * from a;conn / as sysdba邏輯備庫切換成功！再切換回去在standby庫上執(zhí)行:select switchover_status from v$database;alter database prepare to switchover to logical standby;select sw

45、itchover_status from v$database;在prod庫上執(zhí)行:select switchover_status from v$database;alter database prepare to switchover to primary;select switchover_status from v$database;在standby庫上執(zhí)行:select switchover_status from v$database;alter database commit to switchover to logical standby;select switchover_s

46、tatus from v$database;在prod庫上執(zhí)行:select switchover_status from v$database;alter database commit to switchover to primary;select switchover_status from v$database;在standby庫上執(zhí)行:alter database start logical standby apply immediate;開始實時應(yīng)用日志在prod庫上執(zhí)行:conn scott/tigerinsert into a values('c');commi

47、t;conn / as sysdba在standby庫上執(zhí)行:conn scott/tigerselect * from a;conn / as sysdba恭喜又一次轉(zhuǎn)換成功！如果數(shù)據(jù)沒有同步要稍等會，大概在10秒鐘，起決于機(jī)器的性能第四章 使用邏輯備庫滾動升級升級邏輯備庫到10204把10204和11201的數(shù)據(jù)庫軟件傳輸?shù)絪tandby的/u01目錄用root用戶執(zhí)行cd /u01ls -lrtchown oracle:oinstall *ls -lrtsu - oraclecd /u01ls -lrt解壓10204升級包軟件unzip p6810189_10204_Linux-x86-

48、64.zipls -lrt刪除軟件壓縮包，節(jié)省空間rm -f p6810189_10204_Linux-x86-64.zip停止邏輯備庫同步sqlplus / as sysdbaalter database stop logical standby apply;修改一下數(shù)據(jù)庫的SGA大小為512M，不然可能在升級過程中一直會報4031錯誤說內(nèi)存不夠，這個記錄報錯信息的trc文件會把磁盤撐滿導(dǎo)致升級失敗alter system set sga_max_size=512M scope=spfile;alter system set sga_target=512M scope=spfile;關(guān)閉數(shù)據(jù)

49、庫shutdown immediate升級數(shù)據(jù)庫軟件在standby庫上執(zhí)行exitcd /u01/Disk1./runInstaller另開一個會話把listener關(guān)了su - oraclelsnrctl stop在root用戶下執(zhí)行刪除安裝軟件，節(jié)省空間cd .rm -fR Disk1rm -f README.htmlls -lrt開始升級數(shù)據(jù)庫sqlplus / as sysdbastartup upgrade?/rdbms/admin/catupgrd.sql這個步驟要持續(xù)很久，請耐心等待，此時可以另開一個會話查看歸檔日志信息，因為升級過程中會產(chǎn)生大量的日志，所以要定時刪除歸檔日志防

50、止磁盤空間不夠cd /u01/oradata/standby/archivelogls -lrtrm -f *_768842401.dbfls -lrt上面這些是prod傳送過來的歸檔日志也關(guān)注一下磁盤剩余空間df -h等到sqlplus顯示如下信息sqlplus / as sysdbaselect * from v$version;10204升級完畢！升級邏輯備庫到11201解壓11gr2軟件exitunzip linux.x64_11gR2_database_1of2.zipunzip linux.x64_11gR2_database_2of2.zip刪除軟件壓縮包節(jié)省空間rm -f li

51、nux.x64_11gR2_database_*ls -lrtrm -fR backupsetls -lrtdf -h配置11g的環(huán)境變量文件cdcp .bash_profile .bash_profile11vi .bash_profile11把ORACLE_HOME改成db_2，還有下面3個參數(shù)都要$ORACLE_HOME寫在最前面，不然sqlplus無法登陸保存退出到root用戶下修改適合11g的內(nèi)核參數(shù)exitvi /etc/sysctl.confkernel.core_uses_pid = 1fs.aio-max-nr = 1048576fs.file-max = 6815744ke

52、rnel.shmall = 2097152kernel.shmmax = 536870912kernel.shmmni = 4096kernel.sem = 250 32000 100 128net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576保存退出sysctl -p讓它生效vi /etc/security/limits.conforac

53、le soft stack 10240在最后添加這行參數(shù)保存退出su - oracle. .bash_profile11使用11g的環(huán)境變量，開始安裝11201軟件cd /u01/database./runInstaller如果提示空間不夠就清除一點內(nèi)容騰出空間來內(nèi)存大小問題忽略它11201軟件安裝完畢刪除安裝軟件，節(jié)省空間cd .rm -fR databasels -lrtdf -h復(fù)制參數(shù)文件到11201的dbs下sqlplus / as sysdbacreate pfile from spfile='/u01/oracle/db_1/dbs/spfilestandby.ora'exitcd $ORACLE_HOME/dbsllvi initstandby.ora把上面幾個參數(shù)去掉，保存退出sqlplus / as sysdbacreate spfile from pfile;另開一個窗口，用10204軟件登陸sqlplussu - oracleenv | grep ORAsqlplus / as sysdbaselect status from v$instance;備庫還是在升級狀態(tài),給備庫打上升級到11gr2之前