OS Authentication Vs Password Authentication
How to connect to CDB?
How to connect to PDB?
How to connect to CDB with OS authentication?
How to connect to CDB with Password authentication?
How to connect to PDB with OS authentication?
How to connect to PDB with Password authentication?
Solution:
export ORACLE_PDB_SID=TESTPDB
1. Verify the environment
[root@oraclelab2 ~]# ps -ef|grep smon
oracle 651 1 0 Oct20 ? 00:00:10 ora_smon_TESTCDB
oracle 4833 1 0 Oct21 ? 00:00:08 ora_smon_DEVCDB
root 12108 12034 0 01:48 pts/0 00:00:00 grep --color=auto smon
oracle 32650 1 0 Oct19 ? 00:00:09 asm_smon_+ASM
[root@oraclelab2 ~]#
[root@oraclelab2 ~]# su - oracle
Last login: Tue Oct 26 09:01:29 IST 2021 on pts/0
[oracle@oraclelab2 ~]$ . oraenv
ORACLE_SID = [oracle] ? TESTCDB
The Oracle base has been set to /u01/app/oracle
[oracle@oraclelab2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 01:57:03 2021
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 "SYS"
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TESTCDB READ WRITE
SQL>
[oracle@oraclelab2 ~]$ ps -ef|grep tns
root 23 2 0 Oct04 ? 00:00:00 [netns]
oracle 7813 1 0 Oct22 ? 00:00:08 /u01/app/19.0.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle 8020 1 0 Oct22 ? 00:00:00 /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tnslsnr LISTENER_DB -inherit
oracle 13152 12948 0 01:58 pts/0 00:00:00 grep --color=auto tns
[oracle@oraclelab2 ~]$
2. How to connect to CDB with OS authentication?
[oracle@oraclelab2 ~]$ . oraenv
ORACLE_SID = [oracle] ? TESTCDB
The Oracle base has been set to /u01/app/oracle
[oracle@oraclelab2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 01:57:03 2021
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 "SYS"
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TESTCDB READ WRITE
SQL>
3. How to connect to CDB with Password authentication?
[oracle@oraclelab2 ~]$ sqlplus sys/Mallik123@TESTCDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 01:58:57 2021
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 "SYS"
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TESTCDB READ WRITE
SQL>
4. Try connecting to CDB with wrong password it will not allow
[oracle@oraclelab2 ~]$ sqlplus sys/Mallik12345@TESTCDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 01:59:33 2021
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: [oracle@oraclelab2 ~]$
[oracle@oraclelab2 ~]$
5. Its mandatory to user TNS service named along with username and password in order to verify the password. If you don't use TNS service name then default it will user OS authentication to connect to database irrespective of correct password or wrong password you enter.
[oracle@oraclelab2 ~]$ sqlplus sys/Mallik12345 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:00:12 2021
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 "SYS"
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TESTCDB READ WRITE
SQL>
[oracle@oraclelab2 ~]$ sqlplus sys/Mallik123 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:01:00 2021
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 parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string TESTCDB
SQL>
6. Verify your TNS service name for CDB and PDB valid from your tnsnames.ora file and using tnsping
[oracle@oraclelab2 dbhome_1]$ cd network/admin/
[oracle@oraclelab2 admin]$ pwd
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
[oracle@oraclelab2 admin]$ ll
total 16
-rw-r--r--. 1 oracle oinstall 148 Oct 22 09:44 listener.ora
drwxr-xr-x. 2 oracle oinstall 64 Apr 17 2019 samples
-rw-r--r--. 1 oracle oinstall 1536 Feb 14 2018 shrept.lst
-rw-r--r--. 1 oracle oinstall 79 Oct 22 10:41 sqlnet.ora
-rw-r-----. 1 oracle oinstall 1117 Oct 22 10:46 tnsnames.ora
[oracle@oraclelab2 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.
TESTCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab2.localdomain.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTCDB)
)
)
TESTPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab2.localdomain.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTPDB)
)
)
[oracle@oraclelab2 admin]$ tnsping TESTCDB
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 27-OCT-2021 02:02:34
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab2.localdomain.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTCDB)))
OK (0 msec)
[oracle@oraclelab2 admin]$
[oracle@oraclelab2 admin]$ tnsping TESTPDB
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 27-OCT-2021 02:14:36
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab2.localdomain.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTPDB)))
OK (10 msec)
[oracle@oraclelab2 admin]$
7. How to connect to PDB? Connect to PDB via CDB
[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$
[oracle@oraclelab2 admin]$ ps -ef|grep smon
oracle 651 1 0 Oct20 ? 00:00:10 ora_smon_TESTCDB
oracle 12206 1 0 01:49 ? 00:00:00 ora_smon_DEVCDB
oracle 13566 12948 0 02:03 pts/0 00:00:00 grep --color=auto smon
oracle 32650 1 0 Oct19 ? 00:00:09 asm_smon_+ASM
[oracle@oraclelab2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:03:11 2021
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 TESTPDB READ WRITE NO
5 PDB2 MOUNTED
6 PDB3 MOUNTED
SQL> alter session set container=TESTPDB;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 TESTPDB READ WRITE NO
SQL>
8. How to connect to PDB with OS authentication?
[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ export ORACLE_PDB_SID=TESTPDB
[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_PDB_SID=TESTPDB
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:05:55 2021
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 "SYS"
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 TESTPDB READ WRITE NO
SQL>
9. Can I able to start PDB using startup command which is down by connecting to PDB via OS authentication
--- Not possible since PDBs does not have any init file/pfile or spfile
[oracle@oraclelab2 admin]$ export ORACLE_PDB_SID=PDB2
[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_PDB_SID=PDB2
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:07:00 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected.
SQL> show pdbs
ERROR:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
SP2-1545: This feature requires Database availability.
SQL> exit
Disconnected
[oracle@oraclelab2 admin]$
[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_PDB_SID=PDB2
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:11:49 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected.
SQL> startup;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
SQL>
10. How to start PDBs which are down:
Only way to start your PDBs is to connect to CDB and start them from CDB
[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:07:38 2021
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 TESTPDB READ WRITE NO
5 PDB2 MOUNTED
6 PDB3 MOUNTED
SQL> alter pluggable database PDB2 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB READ WRITE NO
5 PDB2 READ WRITE NO
6 PDB3 MOUNTED
SQL>
11. How to connect to PDB using normal user account?
[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$
[oracle@oraclelab2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:12:16 2021
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 TESTPDB READ WRITE NO
5 PDB2 READ WRITE NO
6 PDB3 MOUNTED
SQL>
SQL> alter session set container=TESTPDB;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 TESTPDB READ WRITE NO
SQL>
SQL> select USERNAME,ACCOUNT_STATUS from dba_users where USERNAME='MALLIK';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
MALLIK OPEN
[oracle@oraclelab2 admin]$ sqlplus mallik/mallik@TESTPDB
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:15:43 2021
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
SP2-0382: The SHOW PDBS command is not available
SQL> show user
USER is "MALLIK"
SQL>
12. Connect to PDB using sys account via password authentication
[oracle@oraclelab2 admin]$ sqlplus sys/Mallik123@TESTPDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:16:31 2021
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 "SYS"
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 TESTPDB READ WRITE NO
SQL>
Regards,
Mallik
No comments:
Post a Comment