時(shí)間：2015-06-28 00:00:00 來(lái)源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評(píng)論(0)
Oracle RAC安裝完畢后，監(jiān)聽(tīng)、tnsname.ora按照Oracle默認(rèn)配置好后，在服務(wù)器端用sqlplus連接沒(méi)有問(wèn)題，但是用客戶端連接，會(huì)出現(xiàn)有時(shí)候連不上的錯(cuò)誤，解決方法：
1、修改監(jiān)聽(tīng)文件，增加SID_LIST_LISTENER，兩臺(tái)機(jī)器都要修改，修改完畢后如下：
LISTENER_ORCL1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
)
)
SID_LIST_LISTENER_ORCL1 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.1.0)
(SID_NAME = ora1)
)
)
2、修改tnsname.ora，兩臺(tái)機(jī)器都要修改，例如第一臺(tái)機(jī)器：
用Oracle的netca配置的時(shí)候，只配置了一個(gè)監(jiān)聽(tīng)LISTENERS_ORCL，手工增加另外兩個(gè)監(jiān)聽(tīng)。
LISTENER_ORCL2 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
)
LISTENER_ORCL1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
LISTENERS_ORCL =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
3、修改pfile，將兩個(gè)本地監(jiān)聽(tīng)設(shè)置上。
可以先導(dǎo)出一份spfile，修改完畢后再導(dǎo)入pfile。增加以下三行：
*.local_listener='LISTENER_ORCL'
ora1.local_listener='LISTENER_ORCL1'
ora2.local_listener='LISTENER_ORCL2'
另外，在啟動(dòng)oralce時(shí)，出現(xiàn)ora-32006的錯(cuò)誤，數(shù)據(jù)庫(kù)倒是起來(lái)能用。到網(wǎng)上搜索一通，說(shuō)是oracle10g以后將*.log_archive_start=TRUE 這個(gè)參數(shù)去掉了，把這行從spfile中刪除了，再啟動(dòng)沒(méi)有報(bào)錯(cuò)。是否真的不需要這個(gè)參數(shù)，還需要再確認(rèn)。
4、oracle驅(qū)動(dòng)
經(jīng)測(cè)試發(fā)現(xiàn)，在oracle RAC環(huán)境下，如果down掉一個(gè)實(shí)例，應(yīng)用出現(xiàn)以下錯(cuò)誤：
Io 異常: Software caused connection abort: socket write error
org.apache.cocoon.ProcessingException: error on execute servlet:login: javax.servlet.ServletException: error on execute servlet:login
cause: java.sql.SQLException: Io 異常: Software caused connection abort: socket write error
經(jīng)反復(fù)測(cè)試，確定原因是oracle的驅(qū)動(dòng)ojdbc14.jar沒(méi)有放到WEB-INF/lib下的原因。此啟動(dòng)在公共lib下，必須COPY到當(dāng)前工程下，才能實(shí)現(xiàn)fail-over的自動(dòng)切換.
5、測(cè)試jdbc連接的程序：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class RacTest {
/**
* 測(cè)試RAC
*
* @param args
* @throws ClassNotFoundException
*/
public static void main(String[] args) throws ClassNotFoundException {
String url;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Properties conProps = new Properties();
conProps.put("user", "sys");
conProps.put("password", "XXXX");
conProps.put("internal_logon", "sysdba");
url = "jdbc:oracle:thin:@(description= (ADDRESS_LIST =";
url += "(address=(protocol=tcp)(host=192.168.0.1)(port=1521))"; // 服務(wù)器IP
url += "(address=(protocol=tcp)(host=192.168.0.2)(port=1521))";
url += "(load_balance=yes))"; // 是否負(fù)載均衡
url += "(connect_data =";
url += "(server = dedicated)";// 專用服務(wù)器模式 可以去掉？
url += "(service_name=orcl)"; // 服務(wù)名
//url += "(failover_mode =";
//url += "(type=session)";
//url += "(method=preconnect)";
//url += "(retries=5)";
//url += "(delay=15))";
url += " ))";
/**
* LOAD_BALANCE = yes表示程序走sword連接的時(shí)候，將在下面的地址里面隨機(jī)的選擇一個(gè)，達(dá)到接點(diǎn)間連接均勻
* failover表示開(kāi)啟TAF(Transparent Application Failover)特性，其中TYPE =???? * SESSION表示當(dāng)一個(gè)連接好的會(huì)話 * 的實(shí)例發(fā)生故障，系統(tǒng)會(huì)自動(dòng)將會(huì)話切換到其他可用的實(shí)例，前臺(tái)應(yīng)用無(wú)須再度發(fā)起連接，但會(huì)話正在執(zhí)行的SQL 需要重新執(zhí)行，METHOD = * BASIC表示初始連接就連接一個(gè)接點(diǎn)，他還有個(gè)選項(xiàng)是preconnect，表示初始連接所有的 * 接點(diǎn)，在failover的時(shí)候可以很快就切過(guò)去，這個(gè)基本是資源浪費(fèi) RETRIES:重試次數(shù) DELAY:重試間的延遲(以秒為單位)
* 如果指定了RETRIES，DELAY默認(rèn)為1 如果指定了DELAY，RETRIES默認(rèn)為5
*/
// String sql = "select * from oa_sfm_fb_fwgl t where t.pk_id =
// '17233'";
String sql = "select * from v$instance";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, conProps);
// conn = DriverManager.getConnection(url, username, password);
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()) {
System.out.print(rs.getString("INSTANCE_NAME"));
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (Exception ex) {
ex.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
stmt = null;
} catch (Exception ex) {
ex.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
conn = null;
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
}
第一步就是：$ORACLE_HOME/admin/listerer.ora那個(gè)文件，用netca自動(dòng)生成時(shí)只有LISTENER_ORCL1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
)
)
關(guān)鍵詞標(biāo)簽：Oracle,解決方法
相關(guān)閱讀
熱門文章
Oracle中使用alter table來(lái)增加,刪除,修改列的語(yǔ)法
oracle中使用SQL語(yǔ)句修改字段類型-oracle修改SQL語(yǔ)句案例
誤刪Oracle數(shù)據(jù)庫(kù)實(shí)例的控制文件
為UNIX服務(wù)器設(shè)置Oracle全文檢索
人氣排行 oracle中使用SQL語(yǔ)句修改字段類型-oracle修改SQL語(yǔ)句案例 Oracle中使用alter table來(lái)增加,刪除,修改列的語(yǔ)法 ORACLE SQL 判斷字符串是否為數(shù)字的語(yǔ)句 ORACLE和SQL語(yǔ)法區(qū)別歸納(1) oracle grant 授權(quán)語(yǔ)句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法