java 使用瘦驱动程序的 Oracle 12c 数据库连接抛出 IO 错误

Oracle 12c database connection using thin driver throws IO error

提问by corporateWhore

I'm following the JDBC Developer's Guide and trying to test the JDBC thin driver connection using a short java program.

我正在遵循 JDBC 开发人员指南并尝试使用一个简短的 java 程序测试 JDBC 瘦驱动程序连接。

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
class JDBCVersion
{
public static void main (String args[]) throws SQLException
{
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:hr/hr@localhost:1522:orcl");
        Connection conn = ods.getConnection();
        // Create Oracle DatabaseMetaData object
        DatabaseMetaData meta = conn.getMetaData();
        // gets driver info:
        System.out.println("JDBC driver version is " + meta.getDriverVersion());
}
} //<host>:<port>:<service>

I've tried every possible <host>:<port>:<service>combination but still get a java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

我已经尝试了所有可能的<host>:<port>:<service>组合，但仍然得到一个java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

I've successfully tested the OCI driver using another program included in the tutorial....but can't get this one to work. My application will be using the thin driver to connect to the database so my frustration level is....climbing.

我已经使用教程中包含的另一个程序成功测试了 OCI 驱动程序......但无法让这个程序工作。我的应用程序将使用瘦驱动程序连接到数据库，所以我的挫败感是......攀登。

Any help is appreciated.

任何帮助表示赞赏。

回答by SubOptimal

Maybe following comments could explain why you need the sevice name instead of the SID in the URL.

也许以下评论可以解释为什么您需要服务名称而不是 URL 中的 SID。

• the Oracle JDBC FAQmention that SIDs will be cease to be supported in one of the next few releases of the database

• the Oracle JDBC devolopers guidemention Always connect to a service. Never use instance_name or SID because these do not direct to known good instances and SID is deprecated

• the Oracle 2 day + Java developer tutorialmention the syntax jdbc:oracle:driver_type:[username/password]@//host_name:port_number:SIDwhich seems to be a mixture of SID and service name URL (following the other documents and your working example)

• in contrast the javadoc for OracleDrivermention only the SID syntax

• the Oracle FAQ wikimention both syntax

• 在甲骨文JDBC FAQ提到，SIDs will be cease to be supported in one of the next few releases of the database

• 在甲骨文JDBC devolopers指南提Always connect to a service. Never use instance_name or SID because these do not direct to known good instances and SID is deprecated

• 在甲骨文两日速成和Java开发者教程提到的语法jdbc:oracle:driver_type:[username/password]@//host_name:port_number:SID似乎是SID和服务名URL的混合物（以下的其他文件，你的工作示例）

• 相比之下，OracleDriver的 javadoc只提到了 SID 语法

• 在甲骨文常见问题维基同时提及语法

.

.

jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID
jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE

回答by corporateWhore

I'm able to connect to my container DB (containing my tables, packages, etc.) using the username/password.

我可以使用用户名/密码连接到我的容器数据库（包含我的表、包等）。

Returns:

返回：

JDBC driver version is 12.1.0.2.0

Still can't connect to the tutorial "HR" PDB that comes with the oracle 12c install and which the JDBC tutorial uses.

仍然无法连接到oracle 12c 安装附带的教程“HR” PDB 和JDBC 教程使用的教程。

Edit:

编辑：

Got it to work using the following:

使用以下方法让它工作：

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
class JDBCVersion
{
public static void main (String args[]) throws SQLException
{       
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:@//localhost:1522/pdborcl.global.XXXXXXXX.com");
        ods.setUser("hr");
        ods.setPassword("hr");
        Connection conn = ods.getConnection();
        // Create Oracle DatabaseMetaData object
        DatabaseMetaData meta = conn.getMetaData();
        // gets driver info:
        System.out.println("JDBC driver version is " + meta.getDriverVersion());
}
}

Still don't understand why I need the full global name instead of the instance name.

仍然不明白为什么我需要完整的全局名称而不是实例名称。

回答by Jean de Lavarene

When connecting to a PDB you should always use the PDB's service name in the connection string. It looks like your PDB's service is "pdborcl.global.XXXXXXXX.com" so that's what you need to use to connect the PDB directly.

连接到 PDB 时，您应该始终在连接字符串中使用 PDB 的服务名称。看起来您的 PDB 的服务是“pdborcl.global.XXXXXXXX.com”，因此您需要使用它来直接连接 PDB。

Personally I find it easier to use the long URL format:

我个人觉得使用长 URL 格式更容易：

"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=pdborcl.global.XXXXXXXX.com)))"

"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=pdborcl.global.XXXXXXXX.com)))"

It makes it obvious that you're using a Service name instead of an SID.

很明显，您使用的是服务名称而不是 SID。

The beauty of it is that you can also easily test your connection string with sqlplus:

它的美妙之处在于您还可以使用 sqlplus 轻松测试您的连接字符串：

sqlplus "hr/hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=pdborcl.global.XXXXXXXX.com)))"

sqlplus "hr/hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=pdborcl.global.XXXXXXXXX.com)))"

If sqlplus works there is not reason why the JDBC Thin driver wouldn't.

如果 sqlplus 工作，那么 JDBC Thin 驱动程序就没有理由不工作。

Finally you can also connect the root database using a privilege user and then execute "ALTER SESSION SET CONTAINER=pdb" to switch to the PDB. Should you decide to do so you would have to modify your connection string to connect to the root container first. It should have its own service name.

最后，您也可以使用特权用户连接根数据库，然后执行“ALTER SESSION SET CONTAINER=pdb”切换到 PDB。如果您决定这样做，则必须先修改连接字符串以连接到根容器。它应该有自己的服务名称。