Connecting to CDB & PDB with OS authentication Vs Password Authentication

Connecting to CDB & PDB with OS authentication Vs Password Authentication:

Connecting to CDB with OS authentication:

sqlplus / as sysdba 

show pdbs 

Connecting to CDB with Password authentication:

sqlplus  sys/Mallik123#@CDBDB as sysdba 

show pdbs 

create user c##_mallik identified by mallik;

grant dba to c##_mallik;

sqlplus c##_mallik/mallik@CDBDB 

show pdbs 

Connecting to PDB with OS authentication:

sqlplus / as sysdba 

alter session set container = PDB1;

show pdbs 

export ORACLE_SID=CDBDB 

export ORACLE_PDB_SID=PDB1

sqlplus / as sysdba

show pdbs 

Connecting to PDB with Password authentication:

create user mallik identified by mallik;

grant sysdba to mallik;

export ORACLE_PDB_SID=PDB1

sqlplus sys/Mallik123#@PDB1 as sysdba

show pdbs

sqlplus mallik/mallik@PDB1 as sysdba

show pdbs 

Logs:

[root@oraclelab1 ~]# ps -ef|grep smon

oracle    2533     1  0 Jun09 ?        00:00:00 ora_smon_CDBDB

root     29508 28807  0 10:48 pts/0    00:00:00 grep --color=auto smon

[root@oraclelab1 ~]# su - oracle

Last login: Thu Jun  9 20:34:54 IST 2022 on pts/0

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [oracle] ? CDBDB

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ env |grep ORA

ORACLE_SID=CDBDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:49:21 2022

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 pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

SQL> show user

USER is "SYS"

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ sqlplus  sys/Mallik123#@CDBDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:50:24 2022

Version 19.3.0.0.0

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

Last Successful login time: Thu Jun 09 2022 20:35:13 +05:30

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show user

USER is "SYS"

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ sqlplus  sys/Mallik1@CDBDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:50:40 2022

Version 19.3.0.0.0

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

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name: ^C

[oracle@oraclelab1 ~]$ cd $ORACLE_HOME

[oracle@oraclelab1 dbhome_1]$ cd network/admin/

[oracle@oraclelab1 admin]$ pwd

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

[oracle@oraclelab1 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

CDBDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = CDBDB)

    )

  )

PDB1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PDB1)

    )

  )

PDB2 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PDB2)

    )

  )

[oracle@oraclelab1 admin]$ tnsping CDBDB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2022 10:51:37

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CDBDB)))

OK (0 msec)

[oracle@oraclelab1 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER_CDBDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

  )

ADR_BASE_LISTENER_CDBDB = /u01/app/oracle

[oracle@oraclelab1 admin]$ ps -ef|grep tns

root        22     2  0 Jun09 ?        00:00:00 [netns]

oracle    5647     1  0 Jun09 ?        00:00:00 /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tnslsnr LISTENER_CDBDB -inherit

oracle   29786 29524  0 10:51 pts/0    00:00:00 grep --color=auto tns

[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_CDBDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2022 10:51:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER_CDBDB

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                09-JUN-2022 19:31:39

Uptime                    0 days 15 hr. 20 min. 18 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_cdbdb/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))

Services Summary...

Service "CDBDB" has 1 instance(s).

  Instance "CDBDB", status READY, has 1 handler(s) for this service...

Service "CDBDBXDB" has 1 instance(s).

  Instance "CDBDB", status READY, has 1 handler(s) for this service...

Service "e0f4be2c2a70647be053b404260a606f" has 1 instance(s).

  Instance "CDBDB", status READY, has 1 handler(s) for this service...

Service "e0f4cdbf489e6715e053b404260a1371" has 1 instance(s).

  Instance "CDBDB", status READY, has 1 handler(s) for this service...

Service "pdb1" has 1 instance(s).

  Instance "CDBDB", status READY, has 1 handler(s) for this service...

Service "pdb2" has 1 instance(s).

  Instance "CDBDB", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:53:07 2022

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 pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

SQL> create user c##_mallik identified by mallik;

User created.

SQL> grant dba to c##_mallik;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 admin]$ sqlplus c##_mallik/mallik@CDBDB

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:53:42 2022

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 user

USER is "C##_MALLIK"

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:54:20 2022

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 pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

SQL> alter session set container = PDB1;

Session altered.

SQL> show user

USER is "SYS"

SQL> 

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 PDB1                           READ WRITE NO

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ env |grep ORA

ORACLE_SID=CDBDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 admin]$ export ORACLE_PDB_SID=PDB1

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ env |grep ORA

ORACLE_PDB_SID=PDB1

ORACLE_SID=CDBDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:56:00 2022

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 pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 PDB1                           READ WRITE NO

SQL> show user

USER is "SYS"

SQL> create user mallik identified by mallik;

User created.

SQL> grant sysdba to mallik;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ sqlplus mallik/mallik@PDB1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:57:16 2022

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> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 admin]$ sqlplus mallik/mallik1112@PDB1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:57:31 2022

Version 19.3.0.0.0

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

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name: ^C

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ tnsping PDB1

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2022 10:57:54

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PDB1)))

OK (0 msec)

[oracle@oraclelab1 admin]$ env |grep ORA

ORACLE_PDB_SID=PDB1

ORACLE_SID=CDBDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 admin]$ sqlplus sys/Mallik123#@PDB1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:58:46 2022

Version 19.3.0.0.0

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

Last Successful login time: Fri Jun 10 2022 10:50:24 +05:30

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show user

USER is "SYS"

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 PDB1                           READ WRITE NO

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 admin]$ unset ORACLE_PDB_SID

[oracle@oraclelab1 admin]$ env |grep ORA

ORACLE_SID=CDBDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 admin]$ sqlplus sys/Mallik123#@PDB1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 10:59:35 2022

Version 19.3.0.0.0

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

Last Successful login time: Fri Jun 10 2022 10:58:46 +05:30

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 PDB1                           READ WRITE NO

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 admin]$ env |grep ORA

ORACLE_SID=CDBDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 11:00:20 2022

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 pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 admin]$ export ORACLE_PDB_SID=PDB1

[oracle@oraclelab1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 10 11:00:49 2022

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 pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 PDB1                           READ WRITE NO

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ env |grep ORA

ORACLE_PDB_SID=PDB1

ORACLE_SID=CDBDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 admin]$

Regards,

Mallik