1、前言為了學習DG，我們需要有學習環境來支持，因此搭建DG環境是學習DG的第一步。在搭建GC的文檔里，有介紹虛擬機模板的搭建，這里就直接用這個虛擬機模板來搭建DG，搭建之前先下載11201的數據庫軟件，為了后面的滾動升級實驗。下面就來介紹怎么搭建DG學習環境！有什么問題的可以聯系我。作者：will QQ：185954265第一章 制作DG虛擬機模板下載11201數據庫軟件點擊接受，下載32bit或者64bit軟件本人下載的是64bit的軟件把兩個壓縮包都下載下來還原虛擬機模板把在搭建GC環境時的虛擬機模板還原出來改名為prod裝載虛擬機右鍵rename為prod啟動虛擬機先登錄進去cd /etc

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

3、 localhost.localdomain localhost: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設置oracle環境變量su - or

4、aclevi .bash_profile把下面內容復制到.bash_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/

5、bin:/usr/X11R6/bin/export CLASSPATH=$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'保存后退出執行. .bash_profile讓環境變量生效解壓縮oracle軟件cd /u01ls -lrtgzip -dc 10201_database_linux_x86_64.cp

6、io.gz | cpio -icvdBumls -lrtcd database在windows端打開xmanager的passive來接收遠程圖形雙擊它就可以了看到右下角有圖標且是:0.0端口就行./runInstaller另開一個窗口用root用戶執行上面腳本10201軟件安裝完成！把安裝軟件刪除，節省空間cd .ls -lrtrm -f 10201_database_linux_x86_64.cpio.gzrm -fR databasels -lrtdf -h備份復制模板至此，完成了虛擬機模板的制作，可以把虛擬機目錄拷貝一份保存起來關機，然后復制一份作為備庫使用第二章 搭建物理備庫配置st

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

8、退出hostname standby.localdomain退出后再登錄查看hostscat /etc/hosts一切正常！修改ORACLE_SIDsu - oraclevi .bash_profile把環境變量ORACLE_SID的值修改成standby修改好之后保存退出. .bash_profile讓環境變量生效創建主數據庫prod啟動prod虛擬機然后用netca命令配置監聽su - oraclenetca這個就設置成默認監聽，用1521端口看一下信息用dbca命令創建prod數據庫要和.bash_profile里配置的ORACLE_SID參數一樣不安裝DB Control的em設置sy

9、s用戶的密碼不使用flash recovery area字符集選ZHS16GBK，當然你選AL32UTF8也可以開始創建數據庫解鎖幾個用戶prod數據庫創建完畢！設置prod庫登陸到sqlplus檢查一下歸檔模式和force loggingsqlplus / as sysdbaselect log_mode,force_logging from v$database;如果是非歸檔就要關閉數據庫修改shutdown immediatestartup mount干凈地關閉數據庫再啟動到mount狀態改成歸檔模式和強制loggingalter database archivelog;alter da

10、tabase force logging;select log_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;創建pfilecreate pfile from spfil

11、e;alter database create standby controlfile as '/u01/oracle/standby.ctl'把pfile文件、口令文件和備控制文件傳輸到standbyexitcd /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需要輸入o

12、racle用戶密碼備控制文件只要復制到與prod庫的路徑相同就行，兩邊都是/u01/oracle/standby.ctlprod設置先告一段落設置standby數據庫cd /u01/oracle/db_1/dbsls -lrt把口令文件和pfile名字改成standby的mv orapwprod orapwstandbymv initprod.ora initstandby.oravi initstandby.ora把最上面幾行去掉，然后把上面標注的prod都改成standby注意db_name的prod值不要修改在最后添加以下參數*.db_unique_name='standby&#

13、39;*.db_file_name_convert='/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=(a

14、ll_logfiles,all_roles) db_unique_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*.

15、standby_file_management=auto保存退出創建一些目錄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配置stan

16、dby庫的listener和靜態服務名netmgr再添加靜態服務名選擇save network configuration，保存一下關閉lsnrctl start配置連接到prod的網絡服務名netcatnsping prod用pfile啟動到nomount狀態env | grep ORA登陸sqlplus之前先檢查一下環境變量sqlplus / as sysdbacreate spfile from pfile='?/dbs/initstandby.ora'startup nomountstandby庫的配置告一段落，重新回到prod庫的配置配置prod數據庫再次回到prod

17、庫的設置配置連接到standby的網絡服務名netca備庫的地址，備庫監聽用的是1521端口tnsping standby配置prod庫參數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'alt

18、er system set log_archive_dest_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_se

19、rver=standby;alter system set fal_client=prod;alter system set standby_file_management=auto;還有三需要重啟才生效的參數也設置一下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_fi

20、le_name_convert='/u01/oracle/standby','/u01/oracle/prod' scope=spfile;備份prod庫exitmkdir -p /u01/backupsetls -l /u01創建了備份目錄登陸到rman開始備份rman target /runallocate channel d1 type disk format '/u01/backupset/%U'backup database;release channel d1;把備份集目錄傳輸到standby的/u01目錄下exitscp -r /u

21、01/backupset 192.168.75.62:/u01可以在standby上檢查一下ls -l /u01開始克隆standby庫，在prod上運行rman target / auxiliary sys/oraclestandbyrunallocate auxiliary channel d1 type disk;duplicate target database for standby nofilenamecheck;release channel d1;還原了standby庫！配置standby數據庫查詢standby庫狀態select status from v$instance;

22、現在數據庫為mount狀態創建備庫的standby日志alter 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;

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

24、abase recover managed standby database using current logfile disconnect from session;因為有備重做日志，所以可以加using current logfile語句,實現實時應用select sequence#, applied from v$archived_log where applied='YES' order by sequence#;yes說明日志同步了在prod庫上執行:conn scott/tigercreate table t(i int);insert into t values

25、(1);commit;conn / as sysdbaalter system switch logfile;在standby庫上執行:alter database recover managed standby database cancel;alter database open;conn scott/tigerselect * from t;數據已經同步，物理DG搭建成功！看一下數據庫的open狀態conn / as sysdbaselect open_mode from v$database;注意：物理備庫open是默認read only模式的alter database recove

26、r managed standby database using current logfile disconnect from session;select open_mode from v$database;啟動數據同步語句后，數據庫又變為mounted狀態注意：在測試數據同步的時候請不要用sys用戶，不然可能同步不了，這里測試都是用scott用戶切換物理備庫在prod庫上執行:select switchover_status from v$database;alter database commit to switchover to physical standby;這個命令要執行蠻長時

27、間，請耐心等待注意:上面switchover_status的值如果是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;檢查一下數據庫狀態shutdown immediatestartup nomountalter database mount stand

28、by database;這條命令就是把主庫切成備庫在standby庫上執行:select sequence#, applied 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;查看一下數據庫狀態shutdown immedia

29、testartup在prod庫上執行:創建備日志alter database 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 databa

30、se add standby logfile ('/u01/oradata/prod/standby04.log') size 50m;執行同步語句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庫上執行:conn scott/tigerins

31、ert into t values(2);commit;conn / as sysdbaalter system switch logfile;在prod庫上執行: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

32、 current logfile disconnect from session;select open_mode from v$database;恢復成同步模式再切換一次，切換來原來的狀態，步驟是一樣的這里只是為了驗證是否ok在standby庫上執行:select switchover_status from v$database;alter database commit to switchover to physical standby;shutdown immediatestartup nomountalter database mount standby database;在prod

33、庫上執行:select sequence#, applied 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;查看一下數據庫狀態shutdown immediatestartup在standby庫上執行:alter database recover managed standb

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

35、database recover managed standby database using current logfile disconnect from session;select open_mode from v$database;恢復到了原來的狀態,即prod是主庫，standby是備庫第三章 搭建邏輯備庫配置邏輯備庫 邏輯備庫是在物理備庫的基礎上配置的，所以要搭建邏輯備庫先要創建好物理備庫才行在standby庫上執行:停止備庫的applyalter database recover managed standby database cancel;注意:一定要先停止日志應用，不然下

36、面build的時候會有問題在prod庫上執行:創建prod庫作為邏輯備庫時的在線日志歸檔路徑exitmkdir -p /u01/oradata/prod/lgarchivels -l /u01/oradata/prod修改log_archive_dest_3參數，當prod切換成備庫時起作用，即在線日志歸檔的路徑sqlplus / as sysdbaalter system set log_archive_dest_3='location=/u01/oradata/prod/lgarchive valid_for=(standby_logfiles,standby_role) db_u

37、nique_name=prod'alter system set log_archive_dest_state_3=enable; 然后執行execute dbms_logstdby.build;在standby庫上執行:創建standby庫作為邏輯備庫時的在線日志歸檔路徑exitmkdir -p /u01/oradata/standby/lgarchivels -l /u01/oradata/standbysqlplus / as sysdbaalter system set log_archive_dest_3='location=/u01/oradata/standby/

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

39、 v$database;startup mount forcealter database recover to logical standby standby;把備庫轉換成邏輯備庫，后一個standby是備庫名select status from v$instance;startup mount forceselect database_role from v$database;alter database open resetlogs;show parameter db_name此時備庫的db_name已經改成standby是一個新庫了稍等片刻后在prod庫上執行:alter system

40、 switch logfile;查看prod庫的alert信息查看standby的alert信息在standby庫上執行:查看歸檔日志是否傳輸到備庫exitls -l /u01/oradata/standby/archivelog啟用實時日志應用sqlplus / as sysdbaalter database start logical standby apply immediate;此時standby庫的alert日志里會產生大量信息prod庫的alert日志信息:配置完成，開始驗證在prod庫上執行:conn scott/tigercreate table a(a varchar2(10

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

42、ical standby;select switchover_status from v$database;在standby庫上執行:select switchover_status from v$database;alter database prepare to switchover to primary;select switchover_status from v$database;在prod庫上執行:這個時候觀察prod庫的switchover_status會發現select switchover_status from v$database;如果上述查詢的返回結果不是TO LOGI

43、CAL STANDBY 的話，你可能就需要取消此次轉換，檢查原因，然后再重新操作了取消轉換的命令為:alter database prepare to switchover cancel;prod庫繼續執行alter database commit to switchover to logical standby;在standby庫上執行:再檢查standby庫的switchover_status會發現select switchover_status from v$database;alter database commit to switchover to primary;在prod庫上執行

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

45、er to logical standby;select switchover_status from v$database;在prod庫上執行:select switchover_status from v$database;alter database prepare to switchover to primary;select switchover_status from v$database;在standby庫上執行:select switchover_status from v$database;alter database commit to switchover to logi

46、cal standby;select switchover_status from v$database;在prod庫上執行:select switchover_status from v$database;alter database commit to switchover to primary;select switchover_status from v$database;在standby庫上執行:alter database start logical standby apply immediate;開始實時應用日志在prod庫上執行:conn scott/tigerinsert i

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

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

49、a_target=512M scope=spfile;關閉數據庫shutdown immediate升級數據庫軟件在standby庫上執行exitcd /u01/Disk1./runInstaller另開一個會話把listener關了su - oraclelsnrctl stop在root用戶下執行刪除安裝軟件，節省空間cd .rm -fR Disk1rm -f README.htmlls -lrt開始升級數據庫sqlplus / as sysdbastartup upgrade?/rdbms/admin/catupgrd.sql這個步驟要持續很久，請耐心等待，此時可以另開一個會話查看歸檔日志

50、信息，因為升級過程中會產生大量的日志，所以要定時刪除歸檔日志防止磁盤空間不夠cd /u01/oradata/standby/archivelogls -lrtrm -f *_768842401.dbfls -lrt上面這些是prod傳送過來的歸檔日志也關注一下磁盤剩余空間df -h等到sqlplus顯示如下信息sqlplus / as sysdbaselect * from v$version;10204升級完畢！升級邏輯備庫到11201解壓11gr2軟件exitunzip linux.x64_11gR2_database_1of2.zipunzip linux.x64_11gR2_datab

51、ase_2of2.zip刪除軟件壓縮包節省空間rm -f linux.x64_11gR2_database_*ls -lrtrm -fR backupsetls -lrtdf -h配置11g的環境變量文件cdcp .bash_profile .bash_profile11vi .bash_profile11把ORACLE_HOME改成db_2，還有下面3個參數都要$ORACLE_HOME寫在最前面，不然sqlplus無法登陸保存退出到root用戶下修改適合11g的內核參數exitvi /etc/sysctl.confkernel.core_uses_pid = 1fs.aio-max-nr =

52、 1048576fs.file-max = 6815744kernel.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讓它生效v

53、i /etc/security/limits.conforacle soft stack 10240在最后添加這行參數保存退出su - oracle. .bash_profile11使用11g的環境變量，開始安裝11201軟件cd /u01/database./runInstaller如果提示空間不夠就清除一點內容騰出空間來內存大小問題忽略它11201軟件安裝完畢刪除安裝軟件，節省空間cd .rm -fR databasels -lrtdf -h復制參數文件到11201的dbs下sqlplus / as sysdbacreate pfile from spfile='/u01/oracle/db_1/dbs/spfilestandby.ora'exitcd $ORACLE_HOME/dbsllvi initstandby.ora把上面幾個參數去掉，保存退出sqlplus / as sysdbacreate spfile from pfile;另開一個窗口，用10204軟件登陸sqlplussu - oracleenv | grep ORAsqlplus / as sysdbaselect status from v$insta