Verifying the sys password using TNSNAMES and LISTENER:
Wrong way to validate the sys password:
[oracle@oraclelab1 admin]$ sqlplus / as sysdba >>>>>>>>>>>> OS authentication
[oracle@oraclelab1 admin]$ sqlplus sys/Mallik as sysdba >>>>>>>>>>>> OS authentication
[oracle@oraclelab1 admin]$ sqlplus sys/Mallik123 as sysdba >>>>>>>>>>>> OS authentication
[oracle@oraclelab1 admin]$ sqlplus sys/Mallik123$ as sysdba >>>>>>>>>>>> OS authentication
Correct way to validate the sys password:
[oracle@oraclelab1 admin]$ sqlplus sys/Mallik123@DEVDB as sysdba >>>>>>>>>>>> Password authentication
[oracle@oraclelab1 admin]$ sqlplus sys/Mallik123$@DEVDB as sysdba >>>>>>>>>>>> Password authentication
1. Validate the environment:
[oracle@oraclelab1 admin]$ ps -ef|grep smon
oracle 3154 2699 0 22:39 pts/3 00:00:00 grep --color=auto smon
oracle 7961 1 0 May16 ? 00:00:29 ora_smon_DEVDB
[oracle@oraclelab1 admin]$ cat /etc/oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
DEVDB:/u01/app/oracle/product/19.0.0.0/dbhome_1:N
#DB1:/u01/app/oracle/product/12.2.0.1/dbhome_1:N
DB1:/u01/app/oracle/product/19.0.0.0/dbhome_1:N
[oracle@oraclelab1 admin]$
[oracle@oraclelab1 admin]$ . oraenv
ORACLE_SID = [DEVDB] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclelab1 admin]$
[oracle@oraclelab1 admin]$ env |grep ORA
ORACLE_SID=DEVDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab1 admin]$
2. Various method to connect to database as sys user and using OS authentication
[oracle@oraclelab1 admin]$ sqlplus / as sysdba >>>>>>>>>>>>>>>>>>>> Option 1
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 6 22:40:29 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 "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 admin]$
[oracle@oraclelab1 admin]$ sqlplus >>>>>>>>>>>>>>>>>>>> Option 2
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 6 22:41:44 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password: >>>>>>>>>>>>>>>>>>>> Just hit enter
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> 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 sys as sysdba >>>>>>>>>>>>>>>>>>>> Option 3
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 6 22:57:16 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password: >>>>>>>>>>>>>>>>>>>> Just hit enter
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> 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 sys/Mallik as sysdba >>>>>>>>>>>>>>>>>>>> Option 4
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 6 22:42:45 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 "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 admin]$
3. Correct method to connect to database as sys user and using password authentication.
We have to user TNS names while connecting to database so that it will be considered as remote connection and it will user password file for validating username and passwrod for connection to database
[oracle@oraclelab1 admin]$ sqlplus sys/Mallik123$@DEVDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 6 22:45:15 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 "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 admin]$
4. In order to validate the sys password using tnsnames we should make sure tnsnames and listener are up and running
[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.
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB)
)
)
[oracle@oraclelab1 admin]$ tnsping DEVDB
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 06-JUN-2022 22:45:59
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 = 1522))) (CONNECT_DATA = (SERVICE_NAME = DEVDB)))
OK (0 msec)
[oracle@oraclelab1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 6 22:46:08 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 parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string DEVDB
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]$ 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_DEVDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1522))
)
ADR_BASE_LISTENER_DEVDB = /u01/app/oracle
[oracle@oraclelab1 admin]$ ps -ef|grep tns
root 22 2 0 Feb23 ? 00:00:00 [netns]
oracle 2034 1 0 22:28 ? 00:00:00 /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tnslsnr LISTENER_DEVDB -inherit
oracle 3696 2699 0 22:47 pts/3 00:00:00 grep --color=auto tns
[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-JUN-2022 22:48:03
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DEVDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 06-JUN-2022 22:28:24
Uptime 0 days 0 hr. 19 min. 38 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_devdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1522)))
Services Summary...
Service "DEVDB" has 1 instance(s).
Instance "DEVDB", status READY, has 1 handler(s) for this service...
Service "DEVDBXDB" has 1 instance(s).
Instance "DEVDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$
Regards,
Mallik
No comments:
Post a Comment