To connect as sys or other elevated local users, use the same methods as below, but use ‘as sysdba’ wherever necessary.
Method 1
alter session set container = pdbName;
Method 2
For other methods, you need to know the service-name for the PDB:
– Get the service name for the PDB:
SELECT name || ' '|| pdb FROM v$services ORDER BY name;
or by doing “lsnrctl services LISTENER” and locating service for the PDB.
$ lsnrctl services LISTENER
Method 3
– Using host and port without SCAN:
connect user/pass@//host:port/pdbServiceName
or
sqlplus user/pass@//host:port/pdbServiceName
Method 4
Using host and port with SCAN:
Unix$> sqlplus localuser/password@//cluster-scan:scanPort/pdbServiceName SQL> connect localuser/password@//cluster-scan:scanPort/pdbServiceName
Method 5
First, confugure the tns entry with hostname and scan or without scan:
Without Scan:
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdbServiceName)
)
)
With Scan:
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-name)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdbServiceName)
)
)
Then connect as follows:
connect user/pass@PDB1
or
sqlplus user/pass@PDB1
where PDB1 is the TNS alias.
How To Connect to PDB By OS Authentication In 19c Container Database Environment
Because there is no bequeath connection available in PDB, it cannot connect to PDB directly by OS authentication. But through logon trigger SYS or other external user can connect to PDB without password.
1. SYS user can connect to PDB by predefined trigger DBMS_SET_PDB and environment variable ORACLE_PDB_SID. For example:
$ export ORACLE_PDB_SID=PDB1 $ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 13:25:04 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
$ unset ORACLE_PDB_SID $ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 13:27:49 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 SQL> quit
2. For Non SYS user, connect to PDB by new defined trigger SET_PDB and environment variable ORACLE_PDB. For example:
sqlplus / as sysdba
– Modify initial parameter os_authent_prefix to the same value of common_user_prefix:
show parameter os_authent_prefix alter system set os_authent_prefix='C##' scope=spfile;
– Create common user and grant necessary privilege, e.g create session,set container, etc.
create user c##[OS_USER_NAME] identified externally container=all; grant create session,set container,[Other Privilege] to c##[OS_USER_NAME];
– Restart the database:
shutdown immediate startup
– Create logon trigger for this common user.
sqlplus / as sysdba
CREATE OR REPLACE TRIGGER SYS.set_pdb
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
pdb_name varchar(64);
BEGIN
sys.DBMS_SYSTEM.get_env('ORACLE_PDB',pdb_name);
if(pdb_name is not null) then
EXECUTE IMMEDIATE 'alter session set container='||'"'||pdb_name||'"';
end if;
EXCEPTION when others then
null;
END set_pdb;
/
– sqlnet.ora:
SQLNET.AUTHENTICATION_SERVICES = (ALL)
– Connection test:
export ORACLE_PDB=[Target PDB name] sqlplus / show con_name show user
Test log:
$ export ORACLE_PDB=$ sqlplus / SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 14:20:25 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Tue Nov 17 2020 14:19:40 +00: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 ------------------------------ [Target PDB name] SQL> show user USER is "C##[OS_USER_NAME]"