Oracle 12C中数据库的连接

最新推荐文章于 2022-04-23 15:59:01 发布

weixin_30902251

最新推荐文章于 2022-04-23 15:59:01 发布

阅读量472

点赞数

文章标签：数据库网络

原文链接：http://www.cnblogs.com/dayu-liu/p/9502300.html

版权

Oracle 12C中引入了PDB（可插拔数据库）的概念，虽然大家都说这是在模拟Mysql、Sqlserver的架构，还没有深入了解过Mysql以及Sqlserver，暂时还不能妄下结论。但是引入PDB之后，那些接触11G时间长的老DBA初次接触12C还是很不适应的。

从我个人目前已经了解到的情况而言，使用PDB这种架构的企业还是很少的，及时使用也是从压力测试这个层面使用。对于那些金融行业的公司而言，虽然使用了12C，但是还没有使用PDB这种方式。不管是从成本角度还是稳定性角度考虑，PDB的使用还是处于低频的这种状态，其优点暂时还是没有体现。个人觉得，Oracle开发12C的PDB是为了方便中小企业，毕竟两台性能不错的Linux服务器上部署几套库，在压力不大的情况下，使用是完全完全没问题的。

闲言少叙，下面来说下12C数据库的用户如何连接到数据库：

******************
当前数据库的/etc/hosts配置
******************
[oracle@12c1 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.8.100 12c1
192.168.8.101 12c1-vip
10.168.8.100 12c1-priv

192.168.8.103 12c2
192.168.8.104 12c2-vip
10.168.8.103 12c2-priv

192.168.8.105 r12c-scan

******************
当前数据库下只有一个pdb
******************

[oracle@12c1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 18 22:18:04 2018

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DAYUPDB1 READ WRITE NO

 为pdb指定双节点的service，关于如何为pdb添加service见（https://www.cnblogs.com/dayu-liu/p/9501732.html）

**************************************************************************************************************

查看监听状态

**************************************************************************************************************

[oracle@12c1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-AUG-2018 22:02:26

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                18-AUG-2018 19:35:40
Uptime                    0 days 2 hr. 26 min. 46 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/12c1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.8.100)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.8.101)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "dayupdb1" has 1 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "r12c_cluster" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "racdb" has 1 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 1 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdb_test" has 1 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdb_test1" has 1 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

**************************************************************************************************************

通过IP地址直接连接到PDB

**************************************************************************************************************

[oracle@12c1 ~]$ sqlplus dayupdb1/dayupdb1@192.168.8.101:1521/racdb_test   --这里注意是service name

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 19 19:24:08 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Aug 19 2018 18:30:36 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> set line 300

SQL> set line 300
SQL> show parameter name;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string                            racdb
db_unique_name                       string                            racdb
global_names                         boolean                           FALSE
instance_name                        string                            racdb1
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string
service_names                        string                            racdb

**************************************************************************************************************

通过net service name连接到数据库（即通过配置tnsnames连接到数据库）

**************************************************************************************************************

[oracle@12c1 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

RACDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.8.101 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb_test)
  )
  )

[oracle@12c1 admin]$ sqlplus dayupdb1/dayupdb1@racdb

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 19 19:33:30 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Aug 19 2018 19:24:59 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show user;
USER is "DAYUPDB1"

注：一般在配置HOST的时候配置为vip，因为如果一旦配置的VIP对应的节点发生了问题，此VIP可以漂移到另一个节点上，如果使用public ip，一但连接的节点出现了问题，连接就会中断。

**************************************************************************************************************

在plsql里，如果tnsnames没有配置的话，可以采用IP:PORT/service name的方式连接到数据库

**************************************************************************************************************

转载于:https://www.cnblogs.com/dayu-liu/p/9502300.html

weixin_30902251

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle 12C中数据库的连接

Oracle 12C中引入了PDB（可插拔数据库）的概念，虽然大家都说这是在模拟Mysql、Sqlserver的架构，还没有深入了解过Mysql以及Sqlserver，暂时还不能妄下结论。但是引入PDB之后，那些接触11G时间长的老DBA初次接触12C还是很不适应的。从我个人目前已经了解到的情况而言，使用PDB这种架构的企业还是很少的，及时使用也是从压力测试这个层面使用...
复制链接

扫一扫