|
1. 什么是讀寫分離？
2. 為什么要做讀寫分離？
3. ORACLE讀寫分離的方式，各有優(yōu)缺點(diǎn)？
4. 詳解其中一種ORACLE讀寫分離方式。
如圖：
為了確保數(shù)據(jù)庫產(chǎn)品的穩(wěn)定性，很多數(shù)據(jù)庫擁有雙機(jī)熱備功能。也就是，第一臺 數(shù)據(jù)庫服務(wù)器，是對外提供增刪改業(yè)務(wù)的生產(chǎn)服務(wù)器；第二臺數(shù)據(jù)庫服務(wù)器，主要進(jìn) 行讀的操作。
2臺數(shù)據(jù)庫服務(wù)器，其中一臺數(shù)據(jù)庫服務(wù)器進(jìn)行數(shù)據(jù)寫入操作，另一臺數(shù)據(jù)庫服 務(wù)器進(jìn)行數(shù)據(jù)的讀取。2臺數(shù)據(jù)庫之間數(shù)據(jù)要進(jìn)去快速的數(shù)據(jù)同步。
因?yàn)閿?shù)據(jù)庫的“寫”（寫10000條數(shù)據(jù)到oracle可能要3分鐘）操作是比較耗時的。
但是數(shù)據(jù)庫的“讀”（從oracle讀10000條數(shù)據(jù)可能只要5秒鐘）。
所以讀寫分離，解決的是，數(shù)據(jù)庫的寫入，影響了查詢的效率。
1) 增加冗余
2) 增加了機(jī)器的處理能力
3) 對于讀操作為主的應(yīng)用，使用讀寫分離是最好的場景，因?yàn)榭梢源_保寫的服務(wù)器壓力更小，而讀又可以接受點(diǎn)時間上的延遲。
1) 物理服務(wù)器增加，負(fù)荷增加
2) 主從只負(fù)責(zé)各自的寫和讀，極大程度的緩解X鎖和S鎖爭用①
3) 從庫同步主庫的數(shù)據(jù)和主庫直接寫還是有區(qū)別的，通過主庫發(fā)送來的binlog恢復(fù)數(shù)據(jù)，但是，最重要區(qū)別在于主庫向從庫發(fā)送binlog是異步的，從庫恢復(fù)數(shù)據(jù)也是異步的
4) 讀寫分離適用與讀遠(yuǎn)大于寫的場景，如果只有一臺服務(wù)器，當(dāng)select很多時，update和delete會被這些select訪問中的數(shù)據(jù)堵塞，等待select結(jié)束，并發(fā)性能不高。 對于寫和讀比例相近的應(yīng)用，應(yīng)該部署雙主相互復(fù)制
5) 可以在從庫啟動是增加一些參數(shù)來提高其讀的性能，當(dāng)然這些設(shè)置也是需要根據(jù)具體業(yè)務(wù)需求來定得，不一定能用上
6) 分?jǐn)傋x取。假如我們有1主3從，不考慮上述1中提到的從庫單方面設(shè)置，假設(shè)現(xiàn)在1 分鐘內(nèi)有10條寫入，150條讀取。那么，1主3從相當(dāng)于共計40條寫入，而讀取總數(shù)沒變，因此平均下來每臺服務(wù)器承擔(dān)了10條寫入和50條讀?。ㄖ鲙觳?承擔(dān)讀取操作）。因此，雖然寫入沒變，但是讀取大大分?jǐn)偭耍岣吡讼到y(tǒng)性能。另外，當(dāng)讀取被分?jǐn)偤?，又間接提高了寫入的性能。所以，總體性能提高了，說白了就是拿機(jī)器和帶寬換性能。
7) 復(fù)制另外一大功能是增加冗余，提高可用性，當(dāng)一臺數(shù)據(jù)庫服務(wù)器宕機(jī)后能通過調(diào)整另外一臺從庫來以最快的速度恢復(fù)服務(wù)，因此不能光看性能，也就是說1主1從也是可以的。
讀寫分離的重點(diǎn)其實(shí)就是數(shù)據(jù)同步，能實(shí)現(xiàn)數(shù)據(jù)實(shí)時同步的技術(shù)很多，基于OS層(例如VERITAS VVR)，基于存儲復(fù)制(中高端存儲大多都支持)，基于應(yīng)用分發(fā)或者基于數(shù)據(jù)庫層的技術(shù)。因?yàn)閿?shù)據(jù)同步可能并不是單一的DB整庫同步，會涉及到業(yè)務(wù)數(shù)據(jù)選擇以及多源整合等問題，因此OS復(fù)制和存儲復(fù)制多數(shù)情況并不適合做讀寫分離的技術(shù)首選。
基于日志的Oracle復(fù)制技術(shù)，Oracle自身組件可以實(shí)現(xiàn)，同時也有成熟的商業(yè)軟件。選商業(yè)的獨(dú)立產(chǎn)品還是Oracle自身的組件功能，這取決于多方面的因素。比如團(tuán)隊的相應(yīng)技術(shù)運(yùn)維能力、項(xiàng)目投入成本、業(yè)務(wù)系統(tǒng)的負(fù)載程度等。
無外乎Logical Standby、Stream以及11g的Physical Standby(Active Data Guard)，對比來說，Stream最靈活，但最不穩(wěn)定，11g Physical Standby支持恢復(fù)與只讀并行，但由于并不是日志的邏輯應(yīng)用機(jī)制，在讀寫分離的場景中最為局限。如果技術(shù)團(tuán)隊對相關(guān)技術(shù)掌握足夠充分，而選型方案的處理能力又能支撐數(shù)據(jù)同步的要求，采用Oracle自身的組件完全可行。
a.1.1、DG方案
DG方案也叫ADG方案，英語全稱Physical Standby(Active DataGuard)。支持恢復(fù)與只讀并行，但由于并不是日志的邏輯應(yīng)用機(jī)制，在讀寫分離的場景中最為局限 ，將生產(chǎn)機(jī)的logfiles傳遞給容災(zāi)機(jī)，通過Redo Apply技術(shù)來保障數(shù)據(jù)鏡像能力，物理上提供了與生產(chǎn)數(shù)據(jù)庫在數(shù)據(jù)塊級的一致性鏡像，也叫physical方式。Physical方式支持異步傳輸方式，但容災(zāi)機(jī)處在恢復(fù)狀態(tài)，不可用；
a.1.2、Logical Standby
通過SQL Apply（即Log Miner）技術(shù)，將接收到的日志文件還原成SQL語句，并在邏輯備份數(shù)據(jù)庫上執(zhí)行，從而達(dá)到數(shù)據(jù)一致性的目的，也叫logical 方式。logical方式只支持同步傳輸方式，但容災(zāi)機(jī)可以處在read-only狀態(tài)
a.1.3、Stream
最靈活，但最不穩(wěn)定
更多出于穩(wěn)定性、處理能力等考慮。市面上成熟的Oracle復(fù)制軟件也無外乎幾種，無論是老牌的Shareplex，還是本土DSG公司的RealSync和九橋公司的DDS，或是Oracle新貴GoldenGate，都是可供選擇的目標(biāo)。隨著GoldenGate被Oracle收購和推廣，個人認(rèn)為GoldenGate在容災(zāi)、數(shù)據(jù)分發(fā)和同步方面將大行其道。
b.1.1、下載mycat
wget https://github.com/MyCATApache/Mycat-download/blob/master/1.4-RELEASE/Mycat-server-1.4-release-20151019230038-linux.tar.gz |
b.1.2、解壓
tar -zxvf Mycat-server-1.4-release-20151019230038-linux.tar.gz |
b.1.3、配置環(huán)境變量：
vi /etc/profile 末尾加上 MYCAT_HOME=/MyCat/mycat //mycat安裝路徑 PATH=$PATH:$MYCAT_HOME/bin export MYCAT_HOME PATH |
b.1.4、修改配置文件
/MyCat/mycat/conf/wrapper.conf 修改 wrapper.java.command= /usr/java/jdk1.7.0_80/bin/java //修改wrapper.java.command 為jdk的bin目錄下的java |
b.1.5、設(shè)置mycat用戶名和密碼
修改配置文件/MyCat/mycat/conf/server.xml
<user name="root"> //用戶名和密碼隨意設(shè)置 <property name="password">root</property> <property name="schemas">ORACLEDB</property>//schemas對應(yīng)schema.xml中的schema </user> <user name="admin"> <property name="password">admin</property> <property name="schemas">ORACLEDB</property> <property name="readOnly">true</property> </user> |
b.1.6、修改配置文件/MyCat/mycat/conf/schema.xml
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://org.opencloudb/"> <schema name="ORACLEDB" checkSQLschema="false" dataNode="dn1"> // </schema> <dataNode name="dn1" dataHost="localhost1" database="db1" /> <!—database表空間名稱 --> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="oracle" dbDriver="jdbc"②> <heartbeat>select 1 from dual</heartbeat> <writeHost host="hostM1" url="jdbc:oracle:thin:@10.16.13.23:1521/orcl" user="test_user" password="test_user" > //寫入的數(shù)據(jù)庫 <readHost host="hostS1" url="jdbc:oracle:thin:@10.16.12.9:1521/orcl" user="strmadmin" password="strmadmin"/> //讀取的數(shù)據(jù)庫 </writeHost> </dataHost> </mycat:schema> |
配置結(jié)束
b.1.7、啟動mycat
可以查看日志文件看是否報錯/MyCat/mycat/logs/mycat.log
啟動報錯，導(dǎo)入ojdbc7到mycat的lib目錄下
b.1.8、測試
用navicat客戶端連接mycat (ip為mycat所在的服務(wù)器ip，用戶名和密碼是在server.xml中配置的user)
navcat for mysql客戶端 可能會連接不上報2003-can't connect to mysql server on 10038
需要配置防火墻開啟8066端口
vi /etc/sysconfig/iptables #編輯防火墻配置文件,添加以下內(nèi)容 -A INPUT -m state --state NEW -m tcp -p tcp --dport 8066 -j ACCEPT /etc/init.d/iptables restart #重啟防火墻使配置生效 |
b.1.9、程序中配置如下
<hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> //驅(qū)動 我測試的mysql oracle貌似都可以 <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property> <!-- <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property> <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property> --> <property name="hibernate.connection.password">root</property> <property name="hibernate.connection.url">jdbc:mysql://10.16.1.2:8066/ORACLEDB</property> //這你沒看錯 就是mysql ，換成oracle是不可以的 <property name="hibernate.connection.username">root</property> <property name="hibernate.format_sql">true</property> <property name="hibernate.hbm2ddl.auto">update</property> <mapping resource="org/crazyit/app/domain/Customer.hbm.xml"/> </session-factory> </hibernate-configuration> |
b.1.10、相關(guān)命令
關(guān)閉mycat mycat stop 查看mycat狀態(tài) mycat status |
2個oracle數(shù)據(jù)庫服務(wù)器，做單例數(shù)據(jù)庫的讀寫分離。安裝配置略
2個rac數(shù)據(jù)庫服務(wù)器。做集群環(huán)境的讀寫分離。安裝配置略
單例數(shù)據(jù)庫的讀寫分離，檢測2個oracle服務(wù)器宿主機(jī)是否安裝oracle數(shù)據(jù)庫，是否配置和版本等等一致。安裝配置略
Rac集群數(shù)據(jù)庫的讀寫分離，分別檢測2個rac集群數(shù)據(jù)庫是環(huán)境配置、oracle版本等一致。安裝配置略
相關(guān)信息
| IP地址規(guī)劃： | SID： | db_name: | db_unique_name | 主數(shù)據(jù)庫 | 192.168.11.120 | pri | pri | pri | 備份數(shù)據(jù)庫 | 192.168.11.121 | std | pri | std |
注意：dbname要配置成一樣的，并且關(guān)閉防火墻。
環(huán)境變量：指向oracle安裝的目錄
export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export ORACLE_SID=pri (備庫端設(shè)置為std) export LD_LIBRARY_PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin: export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib export PATH=$ORACLE_HOME/bin:$PATH |
注意：主庫需要建好庫（執(zhí)行dbca），備庫不需要。
[oracle@pri ~]$sqlplus / as sysdba
查看當(dāng)前是否強(qiáng)制日志模式：
SYS@pri>select name,log_mode,force_logging from v$database;
NAME LOG_MODE FOR --------- ------------ --- PRI NOARCHIVELOG NO |
SYS@pri>alter database force logging;
成功后再次查看
SYS@pri> select name,log_mode,force_logging fromv$database;
NAME LOG_MODE FOR --------- ------------ --- PRI NOARCHIVELOG YES |
注意：兩端分別創(chuàng)建自己的密碼文件好像有問題，備庫的密碼文件需要跟主庫一致，否則導(dǎo)致日志傳輸不到備庫，有待驗(yàn)證。我最后是將主庫的密碼文件直接copy到備庫，重命名后使用。
[oracle@pri~]$ cd $ORACLE_HOME/dbs
[oracle@pridbs]$ ls
hc_pri.dat init.ora initpri.ora lkPRI orapwpri snapcf_pri.f spfilepri.ora |
已經(jīng)有一個密碼文件了
[oracle@pridbs]$ orapwd file=orapwpri password=oracle force=y
這條命令可以手動生成密碼文件，force=y的意思是強(qiáng)制覆蓋當(dāng)前已有的密碼文件（如果有可以不建立） file= ：文件名 password=：數(shù)據(jù)庫超級管理員即sys用戶的密碼
將主庫的密碼文件copy給備庫，并重命名
[oracle@pri dbs]$ scp orapwpri192.168.11.121:$ORACLE_HOME/dbs/orapwstd
注意：standby redo log的文件大小與primary 數(shù)據(jù)庫online redo log 文件大小相同。standby redo log日志文件組的個數(shù)依照下面的原則進(jìn)行計算：Standby redo log組數(shù)公式>=(每個instance日志組個數(shù)+1)*instance個數(shù)，假如只有一個節(jié)點(diǎn)，這個節(jié)點(diǎn)有三組redolog，所以Standby redo log組數(shù)>=(3+1)*1 == 4，所以至少需要創(chuàng)建4組Standby redo log
查看當(dāng)前線程與日志組的對應(yīng)關(guān)系及日志組的大?。?span lang="en-us">
SYS@pri>select thread#,group#,bytes/1024/1024 from v$log;
THREAD# GROUP# BYTES/1024/1024 --------------- ------------- ------------------------ 1 1 50 1 2 50 1 3 50 |
如上，我現(xiàn)在的環(huán)境有三組redolog，每個日志組的大小都是50M，所以Standby redo log組 數(shù)>=(3+1)*1== 4所以至少需要創(chuàng)建4組Standby redo log，大小 均為50M （thread：線 程，只有在多實(shí)例數(shù)據(jù)庫才有用的參數(shù)，例如RAC環(huán)境，單 實(shí)例不考慮）
查看當(dāng)前有哪些日志組及其成員：
SYS@pri>col member for a50
SYS@pri>select group#,member from v$logfile;
GROUP# MEMBER ---------- -------------------------------------------------- 3 /u01/app/oracle/oradata/pri/redo03.log 2 /u01/app/oracle/oradata/pri/redo02.log 1 /u01/app/oracle/oradata/pri/redo01.log |
先手動創(chuàng)建standby log日志組所需的目錄：創(chuàng)建新目錄只是為了便于區(qū)分，并非必須
[oracle@prioradata]$ cd /u01/app/oracle/oradata/
[oracle@prioradata]$ ls
新建4個日志組作為standby redolog日志組，大小與原來的日志組一致：由于已經(jīng)存在 group1-3,，所以group號只能從4開始
SYS@pri>alter database add standby logfile group 4'/u01/app/oracle/oradata/standbylog/std_redo04.log' size 50m;
SYS@pri>alter database add standby logfile group 5 '/u01/app/oracle/oradata/standbylog/std_redo05.log'size 50m;
SYS@pri>alter database add standby logfile group 6'/u01/app/oracle/oradata/standbylog/std_redo06.log' size 50m;
SYS@pri>alter database add standby logfile group 7'/u01/app/oracle/oradata/standbylog/std_redo07.log' size 50m;
查看standby 日志組的信息：
SYS@pri>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)前有哪些日志組及其成員：
SYS@pri>set pagesize 100
SYS@pri>col member for a60
SYS@pri>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 /u01/app/oracle/oradata/standbylog/std_redo05.log 6 /u01/app/oracle/oradata/standbylog/std_redo06.log c /u01/app/oracle/oradata/standbylog/std_redo07.log |
查看spfile的路徑：
SYS@pri>show parameter spfile;
NAME TYPE VALUE ----------- ----------- ----------------------------------------------------------------- spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfilepri.ora |
用spfile創(chuàng)建一個pfile,用于修改：
SYS@pri>create pfile from spfile;
修改主庫的pfile：/u01/app/oracle/product/11.2.0/db_1/dbs/ initpri.ora
[oracle@pri~]$ cd $ORACLE_HOME/dbs
[oracle@pri~]$ vim initpri.ora
pri.__db_cache_size=318767104 pri.__java_pool_size=4194304 pri.__large_pool_size=4194304 pri.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment pri.__pga_aggregate_target=335544320 pri.__sga_target=503316480 pri.__shared_io_pool_size=0 pri.__shared_pool_size=163577856 pri.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/pri/adump' *.audit_trail='db' *.compatible=' 11.2.0.4.0 ' *.control_files='/u01/app/oracle/oradata/pri/control01.ctl','/u01/app/oracle/flash_recovery_area/pri/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='pri' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4070572032 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=priXDB)' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=836763648 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' |
以下內(nèi)容是需要新增加的：
*.db_unique_name='pri' DG主庫和備庫的db_name必須一致，db_unique_name不一致 *.log_archive_config='dg_config=(pri,std)' pri主數(shù)據(jù)庫SID，std備份數(shù)據(jù)庫SID *.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=pri' 主數(shù)據(jù)庫的歸檔日志路徑和SID *.log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=std' 備份數(shù)據(jù)庫的SID *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.log_archive_max_processes=4 *.fal_server='std' 備份數(shù)據(jù)庫的SID *.fal_client='pri' 主數(shù)據(jù)庫的SID *.db_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri' 第一個目錄是備份數(shù)據(jù)庫數(shù)據(jù)文件路徑（備份服務(wù)器上有此目錄），第二個是主數(shù)據(jù)庫數(shù)據(jù)文件路徑 *.log_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri' 第一個目錄是備份數(shù)據(jù)庫數(shù)據(jù)文件路徑（備份服務(wù)器上有此目錄），第二個是主數(shù)據(jù)庫數(shù)據(jù)文件路徑 *.standby_file_management='auto' |
修改完畢，保存退出
手工創(chuàng)建/u01/app/oracle/arch：
[oracle@pridbs]$ mkdir –p /u01/app/oracle/arch
關(guān)閉數(shù)據(jù)庫：
SYS@pri>shutdown immediate;
Database closed. Database dismounted. ORACLE instance shut down. |
用修改過的pfile重新創(chuàng)建一個spfile：
SYS@pri>create spfile from pfile;
此時把數(shù)據(jù)庫改為歸檔模式：如果當(dāng)初建庫時選擇了啟用歸檔，則此步驟忽略
由于當(dāng)前數(shù)據(jù)庫已關(guān)閉，首先需要把數(shù)據(jù)庫啟動到mount狀態(tài)
SYS@pri> startup mount;
ORACLE instance started. Database mounted. |
SYS@pri> alter database archivelog; 啟用歸檔模式
SYS@pri>alter database open; OPEN數(shù)據(jù)庫
SYS@pri>archive loglist; 查看是否啟用歸檔模式
SQL>archive log list;
Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 22 Next log sequence to archive 24 Current log sequence 24 |
如上，歸檔路徑已經(jīng)改為/u01/app/oracle/arch，證明對pfile的修改已生效
查看當(dāng)前數(shù)據(jù)庫是否使用spfile啟動：
SYS@pri>show parameter spfile;
NAME TYPE VALUE ----------- ----------- ----------------------------------------------------------------- spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfilepri.ora |
如上，若能看到spfile的路徑，則證明數(shù)據(jù)庫是使用spfile啟動的，若沒有值，則說明是用 pfile啟動的。
確認(rèn)數(shù)據(jù)庫已經(jīng)啟用歸檔模式和強(qiáng)制日志模式：
SYS@pri>select name,log_mode,force_logging from v$database;
NAME LOG_MODE FOR --------- ------------------- ------- PRI ARCHIVELOG YES |
主庫：
[oracle@pri~]$ cd $ORACLE_HOME/network/admin
[oracle@priadmin]$ vim listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.120)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = pri) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = pri) ) ) ADR_BASE_LISTENER = /u01/app/oracle |
主庫修改后最終效果如下圖：
備庫：
[oracle@pri~]$ cd $ORACLE_HOME/network/admin
[oracle@priadmin]$ vim listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.121)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = std) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = std) ) ) ADR_BASE_LISTENER = /u01/app/oracle |
備庫修改后最終效果如下圖：
使新增加的監(jiān)聽生效： （主庫和備庫端都要做）
[oracle@priadmin]$ lsnrctl stop
[oracle@priadmin]$ lsnrctl start
確認(rèn)新增加的靜態(tài)監(jiān)聽有效：
主庫：
[oracle@pri~]$ lsnrctl status
..........................................(N行省略) Services Summary... 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). Instance "pri", status READY, has 1 handler(s) for this service... The command completed successfully |
備庫：
[oracle@std~]$ lsnrctl status
..........................................(N行省略) Services Summary... Service "std" has 2 instance(s). Instance "std", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
如上，靜態(tài)監(jiān)聽添加成功
[oracle@priadmin]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@priadmin]$ ls
listener.ora samples tnsnames.ora listener.ora_bak shrept.lst tnsnames.ora_bak |
vimtnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. pri = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.120)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pri) ) ) std = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.121)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = std) ) ) |
編輯結(jié)果如下圖：
保證主庫和備庫的tnsnames.ora文件中的內(nèi)容完全相同，可以把修改后的文件直接傳給備庫。
[oracle@priadmin]$ scp tnsnames.ora 192.168.11.121:$ORACLE_HOME/network/admin
tnsnames.ora 100% 925 0.9KB/s 00:00 |
首先，在主庫端把pfile拷貝給備庫端的$ORACLE_HOME/dbs目錄下，并重命名：
[oracle@std ~]$ cd $ORACLE_HOME/dbs
[oracle@ std dbs]$ ls
hc_std.dat init.ora initstd.ora lkSTD orapwstd spfilestd.ora |
從主庫拷貝，主庫執(zhí)行命令
[oracle@pridbs]$ scp initpri.ora 192.168.2.253:$ORACLE_HOME/dbs/initstd.ora
然后在備庫端進(jìn)行修改：
[oracle@std~]$ cd $ORACLE_HOME/dbs
[oracle@stddbs]$ vim initstd.ora
pri.__db_cache_size=318767104 pri.__java_pool_size=4194304 pri.__large_pool_size=4194304 pri.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment pri.__pga_aggregate_target=335544320 pri.__sga_target=503316480 pri.__shared_io_pool_size=0 pri.__shared_pool_size=163577856 pri.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/std/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/std/control01.ctl','/u01/app/oracle/oradata/std/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='pri' DG主庫和備庫的db_name必須一致，db_unique_name不一致 *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4070572032 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=stdXDB)' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=836763648 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' |
以下需要手工添加：
*.db_unique_name='std' *.log_archive_config='dg_config=(pri,std)' *.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_ roles) db_unique_name=std' *.log_archive_dest_2='service=pri valid_for=(online_logfiles,primary_role) db _unique_name=pri' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.log_archive_max_processes=4 *.fal_server='pri' *.fal_client='std' *.db_file_name_convert='/u01/app/oracle/oradata/pri','/u01/app/oracle/oradata /std' *.log_file_name_convert='/u01/app/oracle/oradata/pri','/u01/app/oracle/oradat a/std' *.standby_file_management='auto' |
修改完畢，保存退出
注意：整個搭建過程最需要留意的就是主庫和備庫的PFILE配置，建議修改完后仔細(xì)對照主備庫PFILE的區(qū)別
mkdir -pv /u01/app/oracle/admin/std/adump
mkdir -pv /u01/app/oracle/diag/rdbms/std/std/trace
mkdir -pv /u01/app/oracle/arch
mkdir -pv /u01/app/oracle/oradata/std
mkdir -pv /u01/app/oracle/oradata/standbylog
mkdir -pv /u01/app/oracle/flash_recovery_area
[oracle@std~]$ sqlplus / as sysdba
SYS@std>create spfile from pfile;
將數(shù)據(jù)庫啟動到nomount狀態(tài)：
SYS@std>startup nomount;
ORACLE instance started. Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 482348376 bytes Database Buffers 352321536 bytes Redo Buffers 2379776 bytes |
[oracle@std~]$ rman target sys/oracle@pri auxiliary sys/oracle@std
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 15 16:39:28 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PRI (DBID=775616459) connected to auxiliary database: PRI (not mounted) |
RMAN>duplicate target database for standby from active database nofilenamecheck;
這條命令可以直接恢復(fù)數(shù)據(jù)文件，standby控制文件，standby日志組，非常霸道
Starting Duplicate Db at 16-MAR-16 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=134 device type=DISK contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwpri' auxiliary format '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstd' ; } executing Memory Script Starting backup at 16-MAR-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=140 device type=DISK Finished backup at 16-MAR-16 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/std/control01.ctl'; } executing Memory Script Starting backup at 16-MAR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_pri.f tag=TAG20160316T110737 RECID=2 STAMP=906635257 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 16-MAR-16 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/oradata/std/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/std/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/std/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/std/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/std/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/std/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/std/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/std/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/std/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/std/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 16-MAR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/pri/system01.dbf output file name=/u01/app/oracle/oradata/std/system01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:38 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/pri/sysaux01.dbf output file name=/u01/app/oracle/oradata/std/sysaux01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:09 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/pri/undotbs01.dbf output file name=/u01/app/oracle/oradata/std/undotbs01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/pri/users01.dbf output file name=/u01/app/oracle/oradata/std/users01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 16-MAR-16 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=2 STAMP=906635463 file name=/u01/app/oracle/oradata/std/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=3 STAMP=906635463 file name=/u01/app/oracle/oradata/std/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=4 STAMP=906635463 file name=/u01/app/oracle/oradata/std/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=5 STAMP=906635463 file name=/u01/app/oracle/oradata/std/users01.dbf Finished Duplicate Db at 16-MAR-16 |
恢復(fù)數(shù)據(jù)庫結(jié)束
登陸并查看數(shù)據(jù)庫當(dāng)前狀態(tài)：
[oracle@std ~]$ sqlplus / as sysdba
SYS@std>startup
SYS@std>select status from v$instance;
STATUS ------------ MOUNTED （RMAN恢復(fù)完直接就是mount狀態(tài)） |
SYS@std>alter database recover managed standby database disconnect from session;
Database altered. （停止日志應(yīng)用的命令是：alter database recover managed standby database cancel;） |
查看日志應(yīng)用情況：
SYS@std>set pagesize 100
SYS@std>select sequence#,applied from v$archived_log order by 1;
SEQUENCE# APPLIED ---------- --------- 8 YES 9 YES 10 YES |
如上，如果發(fā)現(xiàn)有個NO的，也是正常的，說明該日志在主庫上還沒有歸檔，可以在主庫上運(yùn)行alter system switch logfile;命令來進(jìn)行日志切換，再到備庫查看日志應(yīng)用情況
先在主庫手動切換一下日志：
SYS@pri> alter system switch logfile;
然后查看主庫：
SYS@pri>archive log list;
SQL>archive log list;
Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 22 Next log sequence to archive 24 Current log sequence 24 |
備庫：
SQL>archive log list;
Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 22 Next log sequence to archive 0 Current log sequence 24 |
結(jié)果完全一致，至此，DataGuard的搭建成功！
SQL> selectprocess,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 23 CLOSING ARCH ARCH 0 CONNECTED //歸檔進(jìn)程 ARCH ARCH 21 CLOSING ARCH ARCH 0 CONNECTED RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 24 IDLE //歸檔傳輸進(jìn)程 RFS UNKNOWN 0 IDLE MRP0 N/A 24 APPLYING_LOG //日志應(yīng)用進(jìn)程 d rows selected. |
SQL> selectdatabase_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE ---------------- -------------------- -------------------- -------------------- PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE |
#standby 端查看，也是一樣的。
SQL> selectdatabase_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE ---------------- -------------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED |
查看DG的日志信息
SQL> select *from v$dataguard_status;
SQL>shutdown immediate
ORA-01109: database not open Database dismounted. ORACLE instance shut down. |
SQL>startup
ORACLE instance started. Total System Global Area 1188511744 bytes Fixed Size 1364228 bytes Variable Size 754978556 bytes Database Buffers 419430400 bytes Redo Buffers 12738560 bytes Database mounted. Database opened. |
SQL>select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE ---------------- -------------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY |
SQL>select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 26 CLOSING RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 27 IDLE 7 rows selected. |
SQL>recover managed standby database using current logfile disconnect from session;
SQL>select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 26 CLOSING RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 27 IDLE MRP0 N/A 27 APPLYING_LOG 8 rows selected. |
SQL>select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 19 CLOSING ARCH ARCH 20 CLOSING ARCH ARCH 0 CONNECTED ARCH ARCH 21 CLOSING MRP0 N/A 22 WAIT_FOR_LOG RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 22 IDLE 9 rows selected. |
注解：
② S鎖和X鎖：基本的封鎖類型有兩種:排它鎖(X鎖)和共享鎖(S鎖)。所謂X鎖，是事務(wù)T對數(shù)據(jù)A加上X鎖時，只允許事務(wù)T讀取和修改數(shù)據(jù)A，...所謂S鎖，是事務(wù)T對數(shù)據(jù)A加上S鎖時，其他事務(wù)只能再對數(shù)據(jù)A加S鎖，而不能加X鎖，直到T釋放A上的S鎖，若事務(wù)T對數(shù)據(jù)對象A加了S鎖，則T就可以對A進(jìn)行讀取，但不能進(jìn)行更新(S鎖因此又稱為讀鎖)，在T釋放A上的S鎖以前，其他事務(wù)可以再對A加S鎖，但不能加X鎖，從而可以讀取A，但不能更新A。
③ balance="0",不開啟讀寫分離機(jī)制，所有讀操作都發(fā)送到當(dāng)前可用的 writeHost 上。balance="1"，全部的 readHost 與 stand by writeHost 參與 select 語句的負(fù)載均衡，簡單的說，當(dāng)雙主雙從模式(M1 ->S1 ， M2->S2，并且 M1 與M2 互為主備)，正常情況下， M2,S1,S2 都參與 select 語句的負(fù)載均衡。balance="2"，所有讀操作都隨機(jī)的在 writeHost、 readhost 上分發(fā)。balance="3"， 所有讀請求隨機(jī)的分發(fā)到wiriterHost 對應(yīng)的 readhost 執(zhí)行,writerHost不負(fù)擔(dān)讀壓力，注意 balance=3 只在 1.4 及其以后版本有， 1.3 沒有。writeType="0", 所有寫操作發(fā)送到配置的第一個 writeHost，第一個掛了切到還生存的第二個writeHost，重新啟動后已切換后的為準(zhǔn)，切換記錄在配置文件中:dnindex.properties .writeType="1"，所有寫操作都隨機(jī)的發(fā)送到配置的 writeHost。writeType="2"，沒實(shí)現(xiàn)。不寫入switchType 屬性,主mysql掛了,從mysql是否提升為主，-1 表示不自動切換，1 默認(rèn)值，自動切換，2 基于MySQL主從同步的狀態(tài)決定是否切換
|
