ORA-01017: invalid username/password; logon denied

##########################
## Error
##########################

ORA-01017: invalid username/password; logon denied

### Full Error

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

##########################
#  Error Occurred
##########################

Error occurred while trying to connect a database using RAC service name in 11.2.0.3.0 version

##########################
## Command Executed
##########################

Connect using sqlplus using database service name

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Tnsping & Try Connecting Database over network
#########################################

C:\Users\abc123>tnsping siddb_test
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-AUG-2016 11:06:51
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\Program Files (x86)\Oracle 11g\11g\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sidhost)(PORT = 1521)))
 (CONNECT_DATA = (SERVICE_NAME = siddb_SVC)))
OK (30 msec)
C:\Users\abc123>sqlplus sidusr/sidpass@siddb_test
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:09:27 2016
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

=====================================================================================================================

#########################################
# 2) Connect from DB Server
#########################################

As the Remote connections using service name is failing. I tried logging directly to the database to verify if passwords are working fine.

[oracle@sidhost2 bin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 28 21:26:16 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      siddb
SQL> conn sidusr/sidpassConnected.SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      siddb
SQL>

So the password is the right password. But it doesnt work using remote connection only.

=====================================================================================================================

#########################################
# 3) Try connecting over remote without Service Name
#########################################

By default, every database will have a database service registered with the same database name. For eg:, IN siddb database, we have created a service called siddb_SVC. But a default service named "siddb" will be available in database and can be connected.

So i've modified the tnsnames to use default database service "siddb"

C:\Users\abc123>tnsping siddb_test
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-AUG-2016 11:40:31
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\Program Files (x86)\Oracle 11g\11g\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sidhost)(PORT = 1521)))
 (CONNECT_DATA = (SERVICE_NAME = siddb)))
OK (10 msec)
C:\Users\abc123>sqlplus sidusr/sidpass@siddb_test
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:40:35 2016
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
C:\Users\abc123>

Now i can connect database using default service "siddb" but not using "siddb_SVC"

=====================================================================================================================

#########################################
# 4) Check Database Listener
#########################################

As the remote connections happens via listener, lets check the listener services.

lsnrctl status service

[oracle@sidhost2 dbs]$ . oraenv_+ASM2
The Oracle base remains unchanged with value /ofa/u01/app/oracle/admin
[oracle@sidhost2 dbs]$ lsnrctl status service
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-AUG-2016 11:35:32
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
TNS-01101: Could not find service name service
[oracle@sidhost2 dbs]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-AUG-2016 11:35:38
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-AUG-2016 14:42:45
Uptime                    4 days 20 hr. 52 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ofa/u01/app/oracle/grid/11.2.0.4/network/admin/listener.ora
Listener Log File         /ofa/u01/app/oracle/admin/diag/tnslsnr/sidhost2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=111.22.333.444)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=111.22.333.555)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "siddev" has 1 instance(s).
...
Service "siddb" has 1 instance(s).
  Instance "siddb2", status READY, has 1 handler(s) for this service...
Service "siddb_SVC" has 2 instance(s).  Instance "SIDNEW", status READY, has 1 handler(s) for this service...  Instance "siddb2", status READY, has 1 handler(s) for this service...
[oracle@sidhost2 dbs]$

=====================================================================================================================

#########################################
# 5) Reason for Failure
#########################################

From the above listener service output we can see that "siddb_SVC" service has 2 instances registered "SIDNEW" & "siddb2".

siddb2 seems to be right service. But SIDNEW is another database instance.

=====================================================================================================================

#########################################
# 6) Check Database Service
#########################################

[oracle@sidhost2 dbs]$ srvctl status service -d SIDNEW
[oracle@sidhost2 dbs]$ srvctl config database -d SIDNEW
Database unique name: SIDNEW
Database name: SIDNEW
Oracle home: /ofa/u001/app/oracle/product/11.2.0.4
Oracle user: oracle
Spfile: +SHAREDDATA07/SIDNEW/spfileSIDNEW.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: SIDNEW
Database instances: SIDNEW1,SIDNEW,SIDNEW3
Disk Groups: SHAREDDATA07,SHAREDFRA01,SHAREDREDO01,SHAREDREDO02,SHAREDREDO03
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@sidhost2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 23 11:38:57 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> sho parameter service
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      siddb_SVC
SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      SIDNEW
SQL>

From above output, we can clearly see that service name was registered wrongly in a different database.

One of our member in the DBA team, has created SIDNEW database using "siddb" template but didn't change all the parameters which caused this confusion.

=====================================================================================================================

##########################
## Solution
##########################

#### Modify the database service name.

SQL> Alter system set service_names='SIDNEW_SVC' sid='*';
System altered.
SQL> sho parameter service
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      SIDNEW_SVC
SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      SIDNEW
SQL>

=====================================================================================================================
After modifying the service name, the connections are working good.
=====================================================================================================================

C:\Users\abc123>sqlplus sidusr/sidpass@siddb
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:43:27 2016
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
C:\Users\abc123>tnsping siddb
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-AUG-2016 11:43:34
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\Program Files (x86)\Oracle 11g\11g\network\admin\sqlnet.ora
Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sidhost)(PORT=1521))(LOAD_BALANCE
=yes)(FAILOVER=on))(CONNECT_DATA=(SERVICE_NAME=siddb_SVC)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=60)(DELAY=10))))
OK (20 msec)
C:\Users\abc123>

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

We have a created a NFS mountpoint using Isilon storage which is shared in HP-UX and Linux Cluster servers. Creating file is working fine and also datapump also works good.

But when we try to store oracle database file (Datafiles) we are receiving the error.

##########################
## Error
##########################

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

### Full Error

channel ORA_DISK_4: starting datafile copy
input datafile file number=00008 name=/ofa/u001/SIDNAME/oradata/datafiles1/SIDNAME_tbs_ts1_01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_4 channel at 08/16/2016 23:46:02
ORA-19504: failed to create file "/nfs_mount_name/SIDNAME/bkp/tbs_TS1_8.tf"ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
Additional information: 12

##########################
#  Error Occurred
##########################

Error occured while performing prepare phase using XTTS method in 11.2.0.4.0 version

##########################
## Command Executed
##########################

$ORACLE_HOME/perl/bin/perl xttdriver.pl -p

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check the file system mount Options
#########################################

cat "/etc/fstab"| grep -i mig

# cat "/etc/fstab"| grep -i mig
#----Oracle Migration-----
NASHOST:/ifs/nfspace /nfs_mount_name nfs defaults 0 0
#

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

The file system is mounted with the default options which will work for a normal file system usage. But to store oracle database datafiles, we need to add specific mount options.

=====================================================================================================================

##########################
## Solution
##########################

Update /etc/fstab with the below options for HP-UX servers.

rw,bg,vers=3,proto=tcp,noac,forcedirectio,hard,nointr,timeo=600,rsize=32768,wsize=32768

# cat "/etc/fstab"| grep -i mig
#----Oracle Migration-----
#NASHOST:/ifs/nfspace /nfs_mount_name nfs defaults 0 0
NASHOST:/ifs/nfspace /nfs_mount_name nfs rw,bg,vers=3,proto=tcp,noac,forcedirectio,hard,nointr,timeo=600,rsize=32768,wsize=32768 0 0#

=====================================================================================================================
Unmount and Remount the File system. After the Prepare script worked good.
=====================================================================================================================

# umount /nfs_mount_name
# mount /nfs_mount_name
# bdf /nfs_mount_name
Filesystem          kbytes    used   avail %used Mounted on
NASHOST:/ifs/nfspace
                   5368709120 2783611400 2585097720   52% /nfs_mount_name
# 

$ pwd
/nfs_mount_name/SIDNAME/bkp
$ ls -ltr
total 352044008
-rw-r-----   1 oracle     dba        10485768192 Aug 17 11:21 tbs_TS1_23.tf
-rw-r-----   1 oracle     dba        31457288192 Aug 17 11:37 tbs_TS1_9.tf
$

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

Diagnostic Collection for a RAC server

When we raise a SR to oracle, they will require Diagnostic collection of log files for troubleshooting for that specific timelines when the error has occurred.

To collect diag we need to run the below procedure on all the nodes. So oracle recommends to use TFA. This note covers only diag collection on each node.

Below note is for 11.2 onwards.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Info before start the diag collection
#########################################

User : root (As this collect OS diag logs, its recommended to run as root user else we will be able to capture all the info's)
ORACLE_HOME : ASM Grid Home
Script Used : diagcollection.sh (Inbuilt Script comes as a part of Grid software Installation)
Temporary Directory : /tmp/srini (To Store the generated files.)
Time Stamp when the
Error has occurred : 08/16/201614:20:00 ( Format is MM/DD/YYYY24HH:MM:SS)
Duration until you
want to capture : 2 Hours (Capture 2 hours from the time of error occurred)

=====================================================================================================================

#########################################
# 2) Run the Diag Collection
#########################################

Below Command need to run on all the nodes of the cluster as root user.

cd /tmp/srini

/ofa/u001/app/oracle/grid/11.2.0.4/bin/diagcollection.sh --chmos --incidenttime 08/16/201614:20:00 --incidentduration 02:00

Where,

GRID_HOME=/ofa/u001/app/oracle/grid/11.2.0.4

=====================================================================================================================

#########################################
# 3) Generated Files
#########################################

[root@SIDHOST srini]# ls -ltr
total 29148
-rw-r----- 1 root root   290141 Aug 16 16:41 sysconfig_SIDHOST_20160816_1639.txt
-rw-r----- 1 root root 29514879 Aug 16 16:41 osData_SIDHOST_20160816_1639.tar.gz
[root@SIDHOST srini]#

=====================================================================================================================

#########################################
# 4) Change OWnership
#########################################

As the files are generated as root user, i need it to change as oracle to FTP and upload to ORacle support.

chmod -R oracle:dba /tmp/srini/

[root@SIDHOST srini]# ls -ltr
total 29148
-rw-r----- 1 oracle dba   290141 Aug 16 16:41 sysconfig_SIDHOST_20160816_1639.txt
-rw-r----- 1 oracle dba 29514879 Aug 16 16:41 osData_SIDHOST_20160816_1639.tar.gz
[root@SIDHOST srini]# 

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

##########################
## Error
##########################

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

### Full Error

SQL> Truncate table SCOTT.EMP;
Truncate table SCOTT.EMP
                                *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

##########################
#  Error Occurred
##########################

Error occured while trying to truncate a table in 11.2.0.4.0 version

##########################
## Command Executed
##########################

Truncate table SCOTT.EMP;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check who is using the object
#########################################

set lines 200
col owner for a15
col object_name for a25
col ORACLE_USERNAME for a15
select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID, lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.PROCESS, lo.LOCKED_MODE
from   dba_objects do, v$locked_object lo where  do.OBJECT_ID = lo.OBJECT_ID and    do.OWNER = 'SCOTT' and    do.OBJECT_NAME = 'EMP';

OWNER           OBJECT_NAME                OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME                   PROCESS                  LOCKED_MODE
--------------- ------------------------- ---------- ---------- --------------- ------------------------------ ------------------------ -----------
SCOTT EMP             16036        105 SYS             oracle                         29849                              3
SCOTT EMP             16036        513 SYS             oracle                         18361                              3

=====================================================================================================================

#########################################
# 2) Session Details
#########################################

set lines 200
col username for a25
col osuser for a15
col program for a40
col machine for a20
col status for a10
select inst_id,sid,serial#,username,osuser,program,machine,status,floor(last_call_et/60) "AIM",logon_time from gv$session where sid in ('105','513') order by last_call_et desc;

INST_ID     SID    SERIAL# USERNAME                  OSUSER          PROGRAM                                  MACHINE              STATUS            AIM LOGON_TIM
------- ------- ---------- ------------------------- --------------- ---------------------------------------- -------------------- ---------- ---------- ---------
      1     513       5889 SYS                       oracle          sqlplus@SIDHOST (TNS V1-V3)             SIDHOST             INACTIVE         6983 11-AUG-16
      1     105      12623 SYS                       oracle          sqlplus@SIDHOST (TNS V1-V3)             SIDHOST             INACTIVE         6902 11-AUG-16

=====================================================================================================================

#########################################
# 3) See what queries they are running
#########################################

Select sql_text from v$sqlarea where (address,hash_value) in (select sql_Address,sql_hash_value from v$session where sid=105);

SQL> Select sql_text from v$sqlarea where (address,hash_value) in (select sql_Address,sql_hash_value from v$session where sid=513);

no rows selected

SQL> Select sql_text from v$sqlarea where (address,hash_value) in (select sql_Address,sql_hash_value from v$session where sid=105);

no rows selected

SQL>

#########################################
# 4) Reason for Failure
#########################################

From output of Step 1), we can see that those two sessions have tried accessing that object. Possibly modifying that table but its INACTIVE. While checking found that one of the developer logged in and tried some delete statement and didnt log off.

=====================================================================================================================

##########################
## Solution
##########################

Kill those sessions if not required, or request the users to logoff from the sessions.

In my case i've killed the sessions as its not required.

Alter system kill session '513,5889' immediate;
Alter system kill session '105,12623' immediate;

SQL> Alter system kill session '513,5889' immediate;
System altered.
SQL> Alter system kill session '105,12623' immediate;
System altered.

=====================================================================================================================
Truncate command worked good after clearing the session.
=====================================================================================================================

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================