ORA-31623: A Job Is Not Attached To This Session

 ORA-31623: A Job Is Not Attached To This Session Via The Specified Handle




PROBLEM:      
While performing datapump export or import  
getting error like below

impdp dumpfile=TEST.dmp logfile=TEST.log tables=UDAY.TEST_TAB directory=UTEST


Import: Release 12.1.0.2.0 – Production on Sun Apr 10 14:30:23 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real databaselication Testing options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3905
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 5203
ORA-06512: at line 1
 
 
 
ERROR IN ALERT LOG:
Errors in file /u01/database/oracle/diag/rdbms/TEST/TEST/trace/TEST_ora_1432.trc (incident=58353):
ORA-04031: unable to allocate 448 bytes of shared memory (“streams pool”,”unknown object”,”streams pool”,”kwqbsinfy:cco”)
Incident details in: /u01/database/oracle/diag/rdbms/TEST/TEST/incident/incdir_58353/TEST_ora_1432_i58353.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Dec 31 10:38:06 2018
Sweep [inc][58353]: completed
Sweep [inc2][58353]: completed
Mon Dec 31 10:38:07 2018




SOLUTION:
Increase the stream_pool_size

SQL> show parameter stream
 
NAME                                 TYPE        VALUE
———————————— ———– ——————————
streams_pool_size                    big integer 0 
 
 
SQL> alter system set streams_pool_size=100M scope=both;
System altered.
 
Else configure automatic memory management by setting 

memory_target parameter and memory_max_target parameter






IN my case increasing Stream pool size resolved 

Please check the Oracle support , if issue is not resolved.

ORA-02030: can only select from fixed tables/views

 grant SELECT access to v$ objects (exp - V$session) to other users?

we can’t grant direct access V$session because v$session is a synonym.

SQL> GRANT SELECT ON v$session TO uday;
grant select on v$session to uday
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

— V$SESSION is a public synonym
SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$SESSION’;

OWNER OBJECT_TYPE
———————— ——————-
PUBLIC SYNONYM

— the object (table/view) the synonym points to
SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;

TABLE_OWNER TABLE_NAME
————– ——————————
SYS V_$SESSION

you cannot grant any privilege to v$ objects  or any other fixed views.  
however,you can grant directly option the base table:

SQL> GRANT SELECT ON V_$SESSION TO scott;

Grant succeeded.

grant SELECT access to v$ objects (exp - V$session) to other users?

 grant SELECT access to v$ objects (exp - V$session) to other users?

we can’t grant direct access V$session because v$session is a synonym.

SQL> GRANT SELECT ON v$session TO uday;
grant select on v$session to uday
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

— V$SESSION is a public synonym
SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$SESSION’;

OWNER OBJECT_TYPE
———————— ——————-
PUBLIC SYNONYM

— the object (table/view) the synonym points to
SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;

TABLE_OWNER TABLE_NAME
————– ——————————
SYS V_$SESSION

you cannot grant any privilege to v$ objects  or any other fixed views.  
however,you can grant directly option the base table:

SQL> GRANT SELECT ON V_$SESSION TO scott;

Grant succeeded.

find database is in BEGIN/END backup mode

 find database is in BEGIN/END backup mode

ALTER DATABASE BEGIN/END BACKUP
To take backup manually or when cloning a database using the following SQL need to be executed

which will place the database in backup mode

SQL> ALTER DATABASE BEGIN BACKUP;
Database altered.

To find if database or any tablespace is in backup mode, 

check the status column in V$BACKUP is ACTIVE

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 ACTIVE 14323567 01-OCT-20
2 ACTIVE 14323567 01-OCT-20
3 ACTIVE 14323567 01-OCT-20
4 ACTIVE 14323567 01-OCT-20

Here FILE# is the datafile number

now you copy the physical database files along with REDOLOGS.

REDOLOGS, will be used for database recover, incase of any pending transactions

to finish the BACKUP mode, execute below query

SQL> ALTER DATABASE END BACKUP;
Database altered.

then, check if database is in backup mode
SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 14323567 01-OCT-20
2 NOT ACTIVE 14323567 01-OCT-20

If you try to shutdown of database when database is in BACKUP mode, 

you will receive the following error “ORA-01149”

SQL> shutdown immediate;
ORA-01149: cannot shutdown – file 1 has online backup set
ORA-01110: data file 1: ‘/u01/oradata/TESTDB/system_01.dbf’




If try to set the database again to BEGIN BACKUP MODE, that is already in backup mode,

you will receive the following error “ORA-01146”

SQL> alter database begin backup;
alter database begin backup
*
ERROR at line 1:
ORA-01146: cannot start online backup – file 1 is already in backup
ORA-01110: data file 1: ‘/u01/oradata/TESTDB/system_01.dbf’

In 9i to set database is backup mode, with  “ALTER DATABASE BEGIN/END BACKUP” doesn’t exists 

so it has to be done at individual tablespace level by executing below

“ALTER TABLESPACE <tablespace> BEGIN/END BACKUP”.

exp:- 

ALTER TABLESPACE SYSTEM BEGIN BACKUP;

ALTER TABLESPACE SYSTEM END BACKUP;

If try to perform incremental backup using RMAN 

it will skip backing up datafiles as datafiles are in backup

and show warning RMAN-06554

RMAN> backup incremental level 1 database;

Starting backup at 01-OCT-20
using channel ORA_DISK_1
RMAN-06554: WARNING: file 1 is in backup mode
RMAN-06554: WARNING: file 2 is in backup mode
RMAN-06554: WARNING: file 3 is in backup mode
RMAN-06554: WARNING: file 4 is in backup mode
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=/u01/oradata/TESTDB/sysaux_01.dbf
skipping datafile 00003 because it has not changed
input datafile fno=00001 name=/u01/oradata/TESTDB/system_01.dbf
skipping datafile 00001 because it has not changed
input datafile fno=00002 name=/u01/oradata/TESTDB/undo_01.dbf
skipping datafile 00002 because it has not changed
input datafile fno=00004 name=/u01/oradata/TESTDB/test_01.dbf
skipping datafile 00004 because it has not changed

channel ORA_DISK_1: backup cancelled because all files were skipped

OPatch throwing "Unable to lock Central Inventory" on Windows

OPatch throwing "Unable to lock Central Inventory" on Windows


OPatch throwing the following error when trying to apply or rollback a patch:

C:\Users\oracle\Desktop\31654782>G:\oracle\product\12.2.0\dbhome_1\OPatch\opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : G:\oracle\product\12.2.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 12.2.0.1.23
OUI version       : 12.2.0.1.0
Log file location : G:\oracle\product\12.2.0\dbhome_1\cfgtoollogs\opatch\31654782_Jul_12_2020_17_47_25\apply2020-07-12_17-47-24PM_1.log

Applying interim patch '31654782' to OH 'G:\oracle\product\12.2.0\dbhome_1'
Verifying environment and performing prerequisite checks...
OiiolLogger.addFileHandler:Error while adding file handler - C:\Program Files\Oracle\Inventory/logs\OPatch2020-07-12_05-47-32-PM.log
java.io.FileNotFoundException: C:\Program Files\Oracle\Inventory\logs\OPatch2020-07-12_05-47-32-PM.log (Access is denied)
Unable to lock Central Inventory.  OPatch will attempt to re-lock.
Do you want to proceed? [y|n]
n
User Responded with: N
Unable to lock Central Inventory.  Stop trying per user-request?
OPatchSession cannot load inventory for the given Oracle Home G:\oracle\product\12.2.0\dbhome_1. Possible causes are:
   No read or write permission to ORACLE_HOME/.patch_storage
   Central Inventory is locked by another OUI instance
   No read permission to Central Inventory
   The lock file exists in ORACLE_HOME/.patch_storage
   The Oracle Home does not exist in Central Inventory

OPatch failed: ApplySession failed to prepare the system. Unable to lock Central Inventory.  Stop trying per user-request?
Log file location: G:\oracle\product\12.2.0\dbhome_1\cfgtoollogs\opatch\31654782_Jul_12_2020_17_47_25\apply2020-07-12_17-47-24PM_1.log

OPatch failed with error code = 22



SOLUTION
~~~~~~~~~~~~~~~~~~~~
Due to Windows security, OPatch must run in a command window that was opened As Administrator
Open cmd.exe As Administrator
Apply or rollback patch