DATA PUMP JOB ERROR :

ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.EXPDPFULL"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-00955: name is already used by an existing object


/bin/grep: /datapump/full_backup/expdp_FULL.log: No such file or directory
/bin/grep: /datapump/full_backup/expdp_FULL.log: No such file or directory

Problem Symptoms:
The log shows:

create_customer_data.log:ORA-31626: job does not exist
create_customer_data.log:ORA-31633: unable to create master table "ALEPH_ADMIN.ABC01_JOB"
create_customer_data.log:ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
create_customer_data.log:ORA-06512: at "SYS.KUPV$FT", line 863
create_customer_data.log:ORA-00955: name is already used by an existing object

Cause:
The error is caused by a stopped job that remained in the dba_datapump_jobs. The new expdp job has the same name as the old expdp job.

Resolution:
Clear the old expdp job or specify a different jobname

1. Connect to SQL*Plus as ALEPH_ADMIN:
sqlplus $ALEPH_ADMIN

2. Determine in SQL*Plus which Data Pump jobs exist in the database:
-- locate Data Pump jobs:
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

3. Ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be 'NOT RUNNING'.

4. Check with the job owner that the job with status 'NOT RUNNING' in dba_datapump_jobs is not an export/import Data Pump job that has been temporary stopped, but is actually a job that failed.

5. Determine in SQL*Plus the related master tables:
SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

6. For jobs that were stopped in the past and won't be restarted anymore, delete the master table. E.g.:
DROP TABLE scott.sys_export_table_02;

Note: In the case of an Upgrade Express export which was getting this error, the following worked:

drop table ALEPH_ADMIN.ABC50_JOB;

                 Thread 1 Cannot Allocate New Log" Message in the Alert Log

Refer to -   Message in the Alert Log (Doc ID 435887.1)

CAUSE:

When you switch logs, all private strands have to be flushed to the current log before the switch is allowed to proceed.

SOLUTION

This is expected behavior according to development.  Following is an extract from unpublished Bug 5241081 on why this can occur during manual log switch.

"Technically all the strands need to be flushed when the log switch is being initiated and hence the messages for checkpoint not complete and private strand flush not complete are synonymous. The crux is that the strand still have transactions active which need to be flushed before this redo can be overwritten, would recommend letting Oracle perform the checkpointing by default and tune fast_start_mttr_target to achieve what one is looking for."

This issue is very easy to duplicate on any database running in archivelog mode.  When the online logs are manually switched with no sessions connected, the problem messages are not displayed in the alert log.

SQL> alter system switch logfile;
System altered.

Alert log shows the following.

Thread 1 advanced to log sequence 206
Current log# 3 seq# 206 mem# 0: +GROUP01/oasm/onlinelog/group_3.258.609176403
Current log# 3 seq# 206 mem# 1: +GROUP01/oasm/onlinelog/group_3.263.609176613

Now connect to another session (using scott/tiger schema object in the example) and leave an update TX active.

SQL> update emp set sal = sal * 1;
14 rows updated.
... do not commit and keep session active

Then manually switch the logs in the original session, and now the alert log shows the following messages.

Thread 1 cannot allocate new log, sequence 207
Checkpoint not complete
Current log# 3 seq# 206 mem# 0: +GROUP01/oasm/onlinelog/group_3.258.609176403
Current log# 3 seq# 206 mem# 1: +GROUP01/oasm/onlinelog/group_3.263.609176613

Or the following.

Thread 1 cannot allocate new log, sequence 85
Private strand flush not complete
Current log# 3 seq# 84 mem# 0: C:\ORACLE\ORA11\ORADATA\ORCL11\REDO03.LOG

This is not a problem, but expected behavior. So when you manually switch logs on a DB with any activity at all, you will likely see these messages written to the alert log.  This can also occur when have archive_lag_target set.

Fixed - ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

how to drop and recreate undo tablespace in oracle 11g

/dev/mapper/vg_root-lv_oracle           6.8G  6.3G  155M  98% /u01

[root@oracle-db-server11 XE]# pwd
/u01/app/oracle/oradata/XE
[root@oracle-db-server11 XE]# ls -ltrh
total 4.8G
-rw-r-----. 1 oracle dba  21M Sep  6 00:23 temp.dbf
-rw-r-----. 1 oracle dba 581M Sep  6 04:25 system.dbf
-rw-r-----. 1 oracle dba 101M Sep  6 04:25 users.dbf
-rw-r-----. 1 oracle dba 791M Sep  6 04:30 sysaux.dbf
-rw-r-----. 1 oracle dba 3.3G Sep  6 04:32 undotbs1.dbf
-rw-r-----. 1 oracle dba 9.4M Sep  6 04:33 control.dbf

SQL>  drop tablespace UNDOTBS1 including contents and datafiles;
 drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/app/oracle/oradata/XE/users.dbf

SYSAUX
/u01/app/oracle/oradata/XE/sysaux.dbf

UNDOTBS1
/u01/app/oracle/oradata/XE/undotbs1.dbf

SQL> alter tablespace undotbs1  offline;
alter tablespace undotbs1  offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/XE/undotbs2.dbf' size 100M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> alter system set undo_management=MANUAL scope=spfile;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
[root@oracle-db-server11 XE]# /etc/init.d/oracle-xe stop
Stopping oracle-xe (via systemctl):                        [  OK  ]
[root@oracle-db-server11 XE]# /etc/init.d/oracle-xe start
Starting oracle-xe (via systemctl):                        [  OK  ]

[root@oracle-db-server11 XE]# sqlplus system/oracle@xe;

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 6 05:12:15 2017

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> show parameter undo_tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2


SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER  SEGMENT_NAME                   TABLESPACE_NAME
------ ------------------------------ ------------------------------
STATUS
----------------
SYS    SYSTEM                         SYSTEM
ONLINE

PUBLIC _SYSSMU10_2569484742$          UNDOTBS1
OFFLINE

PUBLIC _SYSSMU1_3789641169$           UNDOTBS1
OFFLINE


If any one the above segment is online then change it to offline 
SQL>alter rollback segment "_SYSSMU10_2569484742$" offline;


Now Drop 

SQL>  drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.


SQL> exit

FIND BACKUP RMAN HISTORY:

1. SQL> col STATUS format a9  
2. SQL> col hrs format 999.99  
3. SQL> select SESSION_KEY, INPUT_TYPE, STATUS, 
4. to_char(START_TIME,'mm/dd/yy hh24:mi')
5. start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
6.  elapsed_seconds/3600 hrs
7.  from V$RMAN_BACKUP_JOB_DETAILS order by session_key;  

RMAN SIZE AND TIMINGS:

col "Total Time" for a30;

col size for a30;

set lines 999;

select session_key "Session",

       input_type "Type",

       status "Status",

       to_char(start_time,'yyyy-mm-dd hh24:mi') "Start Time",

       to_char(end_time,'yyyy-mm-dd hh24:mi')  "End Time",

       output_bytes_display "Size",

       time_taken_display "Total Time"

from v$rman_backup_job_details

where to_char(end_time,'yyyy-mm-dd hh24:mi') >= to_char(sysdate-7,'yyyy-mm-dd hh24:mi') 

and STATUS='COMPLETED'

order by session_key asc;

col TYPE for a20 Heading "Backup Type"
col completion_time Heading "Completion Time"
col MB for 99999999999  Heading "Size (MB)"
col BCPTIME for 99999999999 Heading "Backup Time (minutes)"

SELECT TO_CHAR (completion_time, 'YYYY-MON-DD') completion_time
       , TYPE
       , ROUND (SUM (bytes) / 1048576)          MB
       , ROUND (SUM (elapsed_seconds) / 60)     BCPTIME
    FROM (SELECT CASE
                    WHEN s.backup_type = 'L' THEN 'Archive Log'
--                    WHEN s.controlfile_included = 'YES' THEN 'Control File'
                 WHEN s.backup_type = 'D' THEN 'Full (Level ' || NVL (s.incremental_level, 0) || ')'
                    WHEN s.backup_type = 'I' THEN 'Incemental (Level ' || s.incremental_level || ')'
                    ELSE s.backup_type
                 END
                    TYPE
               , TRUNC (s.completion_time) completion_time
               , p.tag
               , p.bytes
               , s.elapsed_seconds
            FROM v$backup_piece p, v$backup_set s
           WHERE status = 'A' AND p.recid = s.recid
          UNION ALL
          SELECT 'Datafile Copy' TYPE, TRUNC (completion_time), tag, output_bytes, 0 elapsed_seconds FROM v$backup_copy_details)
GROUP BY tag, TO_CHAR (completion_time, 'YYYY-MON-DD'), TYPE
ORDER BY 1 ASC, 2, 3;

DATA PUMP: EXCLUDE / INCLUDE TABLE IMPORT

EXCLUDING TABLES DURING DATA_PUMP IMPORT:

impdp USERNAME/PASSWORD schemas=USERNAME directory=backup dumpfile=full.dmp EXCLUDE=TABLE:\"IN \(\'TABLE1\',\'TABLE2\',\'TABLE3\',\'TABLE4\',\'TABLE5\',\'TABLE6\'\)\"

impdp ultimus/ultimus schemas=ultimus directory=db_back dumpfile=AFT_EOD.dmp EXCLUDE=TABLE:\"IN \(\'test_table_name\'\)\"

EXCLUDING TABLES DURING DATA_PUMP IMPORT – USING “LIKE ” COMMAND:


impdp USERNAME/PASSWORD schemas=USERNAME directory=backup dumpfile=full.dmp EXCLUDE=TABLE:\"like 'IMG_%%'\" EXCLUDE=TABLE:\"IN \(\'EMP\',\'DEPT\'\)\"

INCLUDING TABLES DURING DATA_PUMP IMPORT:

impdp USERNAME/PASSWORD schemas=USERNAME directory=backup dumpfile=full.dmp INCLUDE=TABLE:\"IN \(\’EMP\’, \’DEP\’\)\"

Example:


SQL> alter user system identified by manager;

User altered.

SQL> conn system/manager
Connected.
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> create user test identified by test;

User created.


SQL> conn / as sysdba
Connected.
SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test;
Enter password:
Connected.


SQL> create table A(name varchar2(10));

Table created.

SQL> create table B(name varchar2(10));

Table created.


SQL> create table C(name varchar2(10));

Table created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 17 02:53:08 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create user test2 identiified by test;
create user test2 identiified by test
                  *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> conn system/manager
Connected.
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.


SQL> create user test2 identified by test;

User created.

SQL> grant connect,resource to test2;

Grant succeeded.

SQL> conn test2
Enter password:
Connected.



SQL> conn / as sysdba
Connected.
SQL> select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME like '%DATA%PUMP%';

DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
DATA_PUMP_DIR
/u01/app/oracle/admin/Mango/dpdump/


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$ cd /u01/app/oracle/admin/Mango/dpdump/
[oracle@localhost dpdump]$
[oracle@localhost dpdump]$ expdp "'/ as sysdba'" DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_test_%u.dmp LOGFILE=expdp_test_.log SCHEMAS=test &
[1] 3938
[oracle@localhost dpdump]$
Export: Release 12.2.0.1.0 - Production on Fri Aug 17 03:01:41 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_test_%u.dmp LOGFILE=expdp_test_.log SCHEMAS=test
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TEST"."A"                                      0 KB       0 rows
. . exported "TEST"."B"                                      0 KB       0 rows
. . exported "TEST"."C"                                      0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/Mango/dpdump/expdp_test_01.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 17 03:02:33 2018 elapsed 0 00:00:48


[1]+  Done                    expdp "'/ as sysdba'" DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_test_%u.dmp LOGFILE=expdp_test_.log SCHEMAS=test
[oracle@localhost dpdump]$
[oracle@localhost dpdump]$ impdp "'/ as sysdba'" DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_test_01.dmp LOGFILE=impdp_test.log remap_schema=test:test2 EXCLUDE=TABLE:\"IN \(\'C\'\)\"

Import: Release 12.2.0.1.0 - Production on Fri Aug 17 03:10:31 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_test_01.dmp LOGFILE=impdp_test.log remap_schema=test:test2 EXCLUDE=TABLE:"IN ('C')"
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST2"."A"                                     0 KB       0 rows
. . imported "TEST2"."B"                                     0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Fri Aug 17 03:10:40 2018 elapsed 0 00:00:07

[oracle@localhost dpdump]$ sqlplus test2/test

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 17 03:17:03 2018

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

Last Successful login time: Fri Aug 17 2018 02:55:25 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL> select count(*) from user_tables;

  COUNT(*)
----------
2

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
A
B

SQL> sho user
USER is "TEST2"
SQL>

Happy Working !! :)

ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semget failed with status: 28 ORA-27301: OS failure message: No space left on device ORA-27302: failure occurred at: sskgpcreates

ISSUE:

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates

CAUSE:

So it turns out, the max number of arrays have been reached:
#check limits of all IPC
root@doxer# ipcs -al

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 1024000
max ops per semop call = 100
semaphore max value = 32767

------ Messages: Limits --------
max queues system wide = 16
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536

#check summary of semaphores
root@doxer# ipcs -su

------ Semaphore Status --------
used arrays = 127
allocated semaphores = 16890

FIX:

To resolve this, we need increase value of max number of semaphore arrays:

root@doxer# cat /proc/sys/kernel/sem
250 1024000 100 128
                 ^---needs to be increased

Invalid specification for system parameter LOCAL_LISTENERORA-00119: invalid specification ORA-00132: syntax error

ISSUE: 

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu 12 07:37:51 2018

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'
SQL>




CAUSE




Your INIT file by default will look for an ALIAS name of Listener in

TNSNAMES.ora which doesnt exist in the file.




SOLUTION




#OPTION:1

Hence create a same entry for LISTENER in tnsnames.ora file.




LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

 #OPTION 2:

You can directly insert the entry of Local Listener in the INIT file like below.

*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))'