ORA-31623: a job is not attached to this session via the specified handle

Export: Release 11.2.0.4.0 
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

UDE-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 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551

ORA-06512: at line 1

=======================
Found error in the alert log:


Errors in file /u01/app/oracle/admin/DB1/diagnostic/diag/rdbms/DB1/trace/DB1_ora_814.trc
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

=======================
And in the database stream pool size was 0.

SQL> show parameter streams_pool_size


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL>

Resized to 64M.

SQL> alter system set streams_pool_size=64M scope=both;

SQL> show parameter streams

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

streams_pool_size                    big integer 64M

SQL> alter system switch logfile;

System altered.

And now you can start export again. 😀

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

 

My database was working fine, and suddenly one day I got this error:

[oracle@amey ~]$ sp
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 23 19:49:48 2021
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_CHAMP'
SQL> 
SQL> select name, open_mode from v$database;
select name, open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

In tnsname.ora file add following code containing port number and hostname.

LISTENER_CHAMP =

  (ADDRESS = (PROTOCOL = TCP)(HOST = amey.lad.com)(PORT = 1521))

[oracle@amey ~]$ cat /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora 

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

CHAMP =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = amey.lad.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = champ)

    )

  )

LISTENER_CHAMP =

  (ADDRESS = (PROTOCOL = TCP)(HOST = amey.lad.com)(PORT = 1521))

[oracle@amey ~]$ 

[oracle@amey ~]$ sp

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 23 19:58:02 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 4630511616 bytes

Fixed Size     2933592 bytes

Variable Size 1509952680 bytes

Database Buffers 3103784960 bytes

Redo Buffers    13840384 bytes

Database mounted.

Database opened.

SQL> select name, open_mode from v$database;

NAME   OPEN_MODE

--------- --------------------

CHAMP   READ WRITE

SQL> exit

User Management

 
How to create users in the Oracle database?

create user AMEY identified by "Test123$" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
grant connect, resource to AMEY;
grant create session to AMEY;

Creating and assigning profile to user:

CREATE PROFILE AMEYS_PROFILE LIMIT

  FAILED_LOGIN_ATTEMPTS 3  -- Account locked after 3 failed logins.

  PASSWORD_LOCK_TIME 5     -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.

  PASSWORD_LIFE_TIME 30    -- Password expires after 90 days.

  PASSWORD_GRACE_TIME 3    -- Grace period for password expiration.

  PASSWORD_REUSE_TIME 120  -- Number of days until a specific password can be reused. UNLIMITED means never.

  PASSWORD_REUSE_MAX 10    -- The number of changes required before a password can be reused. UNLIMITED means never.

/

ALTER USER AMEY PROFILE AMEYS_PROFILE;

how to get metadata of user?
set lines 200
set long 1000
set pagesize 1000
select dbms_metadata.get_ddl('USER','AMEY')|| '; '  from dual;
select 'grant ' || privilege || ' to ' || grantee ||'; ' from dba_sys_privs where grantee='AMEY' order by grantee;
select 'grant ' || granted_role || ' to ' || grantee ||'; ' from dba_role_privs where grantee='AMEY' order by grantee;
select 'Grant ' || PRIVILEGE  || ' on ' || OWNER || '.' || TABLE_NAME || ' to ' || grantee  ||'; '  from dba_TAB_PRIVS where GRANTEE in ('AMEY');

How to get the Size of database user?

select sum(bytes)/1024/1024 from dba_segments where owner='AMEY';
Or
select sum(bytes)/1024/1024/1024 from dba_segments where owner='&1';

How to get encrypted password of Oracle User?
select name, password from sys.user$ where name='AMEY';

How to put the same password for the user?

CREATE USER AT
  IDENTIFIED BY VALUES '28D48922ED178DA7'
  DEFAULT TABLESPACE AT
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

how to check objects of user?
select OBJECT_TYPE,count(OBJECT_NAME) from dba_objects where owner='&1' group by OBJECT_TYPE;

Object Count for user:

select count(1) from dba_objects where owner='AMEY';

How Grant Read-Only access to one user to Another?
select 'grant select on ' ||OWNER||'.'||TABLE_NAME|| ' to AMIT;' from dba_tables where owner='AMEY';

To Check provided Grants:
select * from dba_tab_privs where grantee='AMIT' and grantor='AMEY';

How to Check Oracle Database and SYNC status ?

 
select name,open_mode,database_role from v$database;

select pr.thread# "Node", pr.primary "Primary",dr.Standby "DR",pr.primary-dr.Standby "Difference"
from (select thread#,max(sequence#) as primary from v$archived_log where
resetlogs_change#=(select resetlogs_change# from v$database) group by thread#) Pr,
(select thread#,max(sequence#) as Standby from v$archived_log where
resetlogs_change#=(select resetlogs_change# from v$database) and applied='YES' group by thread#) dr
where pr.thread#=dr.thread#;

RMAN Backup completion check query in %

 From the below query, you can check how much RMAN backup completed in %.

set echo on timing on
set lines 200
col username        format a10
col OPNAME          format a35
col SOFAR           format 999,999,999
col TOTALWORK       format 999,999,999
col 'Work Done %'   format 999.99
col Start_time      format a20
select SID,username,OPNAME,SOFAR,TOTALWORK,SOFAR/TOTALWORK*100 "Work Done %",
to_char(START_TIME,'DD-MON-YY hh24:mi:ss') "Start_time",
to_char(sysdate + TIME_REMAINING/3600/24,'DD-MON-YY hh24:mi:ss') "End_at"
from  v$session_longops
where sofar!=TOTALWORK and totalwork!=0
      and OPNAME like 'RMAN%';

Query to Check RMAN backup

 Following Query is used to check rman backup:

set lines 200 pages 200
col STATUS format a15
col hrs format 999.99
col start_time for a15
col INPUT_TYPE for a12
col END_TIME for a15
select
SESSION_KEY,SESSION_RECID,SESSION_STAMP ,INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs,
OUTPUT_DEVICE_TYPE
from V$RMAN_BACKUP_JOB_DETAILS
order by 1;

Put session_recid and session_stamp from above query and you will get log for that RMAN backup:

set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid =11038
and session_stamp =1070757497
order by recid;