首页 » Cloud, ORACLE 9i-23ai » Oracle多租户环境，如何直接登录到PDB, 不指定TNS？ 18c、 19c、20c

Oracle多租户环境，如何直接登录到PDB, 不指定TNS？ 18c、 19c、20c

  • 2020/05/09
  • Cloud, ORACLE 9i-23ai
  • 708 views
  • Oracle多租户环境，如何直接登录到PDB, 不指定TNS？ 18c、 19c、20c已关闭评论

Oracle 20C non-CDB将不再支持，多租户架构（multitenant architecture ）已深入Oracle内核， 没有理由再拒绝CDB，有时计划核心业务库上在CDB中只有一个PDB,  DBA在主机本地登录数据库，感觉每次都要sqlplus 直接登录cdb$root后，还要再alter session set container=xx切换到PDB，或者使用pdb的service_name,很麻烦，那有没有办法可以像非多租一样直接登录到PDB呢？可以。

SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> ho lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-MAY-2020 10:24:57
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel7db1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                09-MAY-2020 10:24:03
Uptime                    0 days 0 hr. 0 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oel7db1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel7db1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "anbob19c", status READY, has 1 handler(s) for this service...
Service "a1478fd0fed91a9ee05536b79bc2729a" has 1 instance(s).
  Instance "anbob19c", status READY, has 1 handler(s) for this service...
Service "anbob19c" has 1 instance(s).
  Instance "anbob19c", status READY, has 1 handler(s) for this service...
Service "anbob19cXDB" has 1 instance(s).
  Instance "anbob19c", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "anbob19c", status READY, has 1 handler(s) for this service...
The command completed successfully

1, TWO_TASK

[oracle@oel7db1 admin]$ tnsping cdb1pdb1
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 09-MAY-2020 10:25:49
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel7db1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))
OK (0 msec)

# login as non-sys
[oracle@oel7db1 admin]$ export TWO_TASK=cdb1pdb1
[oracle@oel7db1 admin]$ sqlplus anbob/anbob
SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 9 10:26:42 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Fri May 08 2020 23:43:47 -04:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name
CON_NAME
------------------------------
PDB1

# login as sys
[oracle@oel7db1 admin]$ export TWO_TASK=cdb1pdb1
[oracle@oel7db1 admin]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 9 10:31:56 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name
CON_NAME
------------------------------
PDB1

# login as / 
[oracle@oel7db1 admin]$ export TWO_TASK=cdb1pdb1
[oracle@oel7db1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 9 10:32:13 2020
Version 19.3.0.0.0

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

ERROR:
ORA-01017: invalid username/password; logon denied

# login as sys, listener not runing 
[oracle@oel7db1 admin]$ export TWO_TASK=cdb1pdb1
[oracle@oel7db1 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-MAY-2020 10:41:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel7db1)(PORT=1521)))
The command completed successfully
[oracle@oel7db1 admin]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 9 10:41:04 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener


# login as / , with not exists TNS alias
[oracle@oel7db1 admin]$ export TWO_TASK=cdbxxx
[oracle@oel7db1 admin]$ sqlplus sys/oracle1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 9 10:39:37 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Note:
使用环境变量TWO_TASK配置tnsnames.ora alias_name， 并在监听服务正常的情况下，使用no-sys和sys密码的方式是可以登录的，但是 sqlplus / as sysdba是不允许的， 那有没有方法使用sqlplus / as sysdba直接登录到PDB呢？ 可以

2， ORACLE_PDB_SID

[oracle@oel7db1 admin]$ unset TWO_TASK
-- not use listener

[oracle@oel7db1 admin]$ export ORACLE_PDB_SID=pdb1
[oracle@oel7db1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 9 10:47:58 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show user
USER is "SYS"

[oracle@oel7db1 ~]$ export ORACLE_PDB_SID=pdb1
[oracle@oel7db1 ~]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 9 11:06:31 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name
CON_NAME
------------------------------
PDB1

# login as non-sys
[oracle@oel7db1 ~]$ sqlplus anbob/anbob

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 9 11:06:47 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

#　ORACLE_PDB_SID　not exists pdb
[oracle@oel7db1 admin]$ export ORACLE_PDB_SID=pdb1_anbob
[oracle@oel7db1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 9 10:50:28 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

NOTE:
配置ORACLE_PDB_SID环境变量，指定对应的PDB name,就可以使用”/ as sysdba” or “sys/xxx as sysdba”直接登录到对应的PDB, 对于一个CDB中只有一个PDB的可以考虑配置该环境变量， 但是ORACLE_PDB_SID是not documented，没有在公开的官方文档中找到描述，但是从MOS中的EBS相关有该参数的使用。但是需要注意有该环境变量也要确认连接的PDB是否正确，因为如果ORACLE_PDB_SID指定了一个不存在的PDB,连上后默认是CDB$ROOT，而且不会提示任何错误，小心跑错了库删错了表！另个需要注意该变量测试在18c开始支持的，至少在我的18c 19c 20c环境是可以的，但是在12c环境中不起作用。

ORACLE_PDB_SID 连接 CLOSED PDB BUG

SQL> alter pluggable database pdb1 close;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED

[oracle@oel7db1 ~]$ export ORACLE_PDB_SID=pdb1
[oracle@oel7db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 9 11:15:03 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected.
select
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

# db alert log

2020-05-09 11:15:03.345000 -04:00
Errors in file /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_10057.trc  (incident=53204) (PDBNAME=PDB1):
ORA-00600: internal error code, arguments: [kkae_switch_svc: get cached default], [3], [], [], [], [], [], [], [], [], [], []

Note:
不应该使用直接连close的PDB, 当19.3环境中使用ORACLE_PDB_SID 连接 CLOSED PDB时会异常中断，出现ORA-600 [kkae_switch_svc: get cached default], 这个ORA-600错误确认为Bug 29615824在19.6 2020年的RU和20C中修复。

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：