ORA-24327: need explicit attach before authenticating a user - Oracle Database

ORA-24327: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin)

How to resolve ORA-24327? What does ORA-24327 oracle database error signifies?

Oracle Opatch and issues during PSU apply process

ORA-24327 occurred in my TEST environment when I tried to apply the Oracle Critical Security Patch 2018 in my Oracle 12.1.0.2 Database. First step got successfully where I installed the Oracle PSU using Oracle OPatch utility. The second step was to load the modified SQLs into the database and for that I ran the below mentioned command,

F:\app\administrator\product\12.1.0\dbhome_1\OPatch>datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Thu Apr 26 16:00:35 2018
Copyright (c) 2012, 2018, Oracle.  All rights reserved.

Log file for this invocation: F:\app\administrator\cfgtoollogs\sqlpatch\sqlpatch_10488_2018_04_26_16_00_35\sqlpatch_invocation.
log

Connecting to database...Database connect failed with:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin)

Please refer to MOS Note 1609718.1 and/or the invocation log
F:\app\administrator\cfgtoollogs\sqlpatch\sqlpatch_10488_2018_04_26_16_00_35\sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Thu Apr 26 16:00:35 2018

As you can see above it failed with "ORA-24327: need explicit attach before authenticating a user". Took the reference from Oracle Support to fix the same.

Reason:

Mostly the error is caused where you have installed multiple Oracle Database or you have multiple Oracle DB Environments in your system. To fix the same, I used the solution shown below.

Resolution:

To fix the issue, mention the database on which you are going to apply the changes. For that you'll need to set the ORACLE_SID with the command to let it know of the database. See below,

./datapatch -db <SID> -verbose

I used the same and ran the below shown command,

F:\app\administrator\product\12.1.0\dbhome_1\OPatch>datapatch -db vapluat8 -verbose
SQL Patching tool version 12.1.0.2.0 Production on Thu Apr 26 16:03:43 2018
Copyright (c) 2012, 2018, Oracle.  All rights reserved.

Log file for this invocation: F:\app\administrator\cfgtoollogs\sqlpatch\sqlpatch_8240_2018_04_26_16_03_43\sqlpatch_invocation.l
og

Connecting to database...
Connection using O/S authentication failed.
Enter userid that can connect as SYSDBA: sys
Enter password for sys:
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
  ID 180417 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    27440294 (WINDOWS DB BUNDLE PATCH 12.1.0.2.180417(64bit):27440294)

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 27440294 apply: SUCCESS
  logfile: F:\app\administrator\cfgtoollogs\sqlpatch\27440294\22083809/27440294_apply_VAPLUAT8_2018Apr26_16_04_23.log (no error
s)
SQL Patching tool complete on Thu Apr 26 16:05:32 2018

As you can see, the Modified SQLs were successfully loaded into the database without errors this time.

I hope this helps !!