ORA-609 : OPIODR ABORTING PROCESS UNKNOWN OSPID

ORA-609 : OPIODR ABORTING PROCESS UNKNOWN OSPID
ORA-609 : opiodr aborting process unknown ospid

WARNING: inbound connection timed out (ORA-3136)


Cause:

The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection
process before the server process was completely spawned.
Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds.

This is also triggered, when a DB session is killed/aborted manually from the OS prompt.

Solution:

Increase the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure.If the problem  is due to connection timeouts,an increase in the following parameters should eliminate or reduce the occurrence of the ORA-609s.

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

ORA-20001: SYS_NTIJR7UZJGXWFGRAAUTZQHMQ== is an invalid identifier

When Using Gather Auto Option Errors - Is an Invalid Identifier (Doc ID 1363044.1)


APPLIES TO:

Oracle EBS Applications Performance - Version 12.1.2 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

SYMPTOMS

On Oracle Applications 12.1.2 version, when attempting to run concurrent program "Gather Schema Statistics" with
GATHER AUTO option, the following error occurs:

ORACLE error 20001 in FDPSTP

Cause: FDPSTP failed due to ORA-20001: SYS_NTIJR7UZJGXWFGRAAUTZQHMQ== is an invalid identifier
ORA-06512: at "APPS.FND_STATS", line 774
ORA-06512: at line 1
.

The SQL statement being executed at the time of the error was: a
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP
ORA-20001: SYS_NTIJR7UZJGXWFGRAAUTZQHMQ== is an invalid identifier
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
CP-GENERIC ORACLE ERROR (ERROR=ORA-20001: SQL_PLSQL_ERROR: N, ROUTINE, MO_GLOBAL.INIT, N, ERRNO, -20001, N, REA)
(ROUTINE=FND_CONCURRENT.INIT_REQUEST)
Concurrent request completed


CAUSE

This is explained in the following bug:

Bug 9542112 - ERROR IN R12 FOR GATHER SCHEMA STATS WITH 'GATHER AUTO' OPTION.

As per bug 9542112, Issue is happening when running Gather schema stats with gather auto option.
actually in Gather auto option we have another cursor ( empty stats cursor)which selects tables with stale stats
( tables whose last analyzed date is NULL).

Empty cursor will be executed after completing gather auto option, that means stats will be gathered on all the  tables before going to empty cursor( where it is throwing this error). It will throw error when gathering stats  on tables with stale stats( that too only for tables which are not there in dba_tables).

SOLUTION

To implement the solution, please

1. Apply patch 9542112 ERROR IN R12 FOR GATHER SCHEMA STATS WITH GATHER AUTO OPTION to a non critical environment.

2. Retest the issue.

3. Check the file versions on file system and database

AFSTATSB.pls120.12.12010000.14

AFSTATSS.pls120.7.12010000.10


[appl@sujeet ~]$ sqlplus apps/****** 

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Dec 13 05:01:49 2017

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select fnd_release.minor_version from dual; 

MINOR_VERSION
-------------
1

SQL> select fnd_release.major_version from dual; 

MAJOR_VERSION
-------------
12

SQL> !
[appl@sujeet ~]$ adident Header $FND_TOP/patch/115/sql/AFSTATSB.pls 
/apps/apps_st/appl/fnd/12.0.0/patch/115/sql/AFSTATSB.pls:
$Header AFSTATSB.pls 120.12.12010000.14 2009/12/17 11:59:49 msaleem ship $ 

[appl@sujeet ~]$ adident Header $FND_TOP/patch/115/sql/AFSTATSS.pls 

/apps/apps_st/appl/fnd/12.0.0/patch/115/sql/AFSTATSS.pls:
$Header AFSTATSS.pls 120.7.12010000.10 2009/12/16 12:32:58 msaleem ship $ 

4. Migrate the solution as appropriate to other environments.

REFERENCES

BUG:9542112 - ERROR IN R12 FOR GATHER SCHEMA STATS WITH GATHER AUTO OPTION

ORA-20005: object statistics are locked

ORA-20005 WHEN GATHERING STATISTICS FOR EBS APPS 11i or R12 IN Database 10G or 11G (Doc ID 375351.1)

APPLIES TO:
Oracle EBS Applications Performance - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.
10g or 11g Database with Apps 11i or R12


SYMPTOMS
Gathering statistics using concurrent jobs "Gather Schema Statistics / Gather Table Statistics" or
running FND_STATS fails with "ORA-20005: object statistics are locked".

Example 1:

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=AR.AR_REV_REC_QT***ORA-20005: object statistics are locked (stattype = ALL)***

Example 2:

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP stats on table GAT_REQ_QTBL is locked

CAUSE

This can happen with Advance Queue tables. In 10g, if a queue is created or recreated during the upgrade, automatic statistics
gather is locked (disabled) on these queue.

The following statement can be used to check the tables which have statistics locked:

    select owner, table_name, stattype_locked
    from dba_tab_statistics
    where stattype_locked is not null;

SOLUTION
Unlock statistics gathering on those queues running the commands below. To unlock all the tables in a schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner');

OR

To unlock individual tables (need to run for all tables individually):

exec dbms_stats.unlock_table_stats('table_owner','table_name');

Examples:

SQL> exec dbms_stats.unlock_schema_stats ('AR');
SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT');


NOTE:

1. For Applications Ebusiness Suite, stats SHOULD be collected in AQ tables.
 Therefore, stats should be unlocked in all AQ tables owned by any Apps schema

2. The current recommended version of FND_STATS  is provided in the patch

Patch 8452962 - FND_STATS SHOULD NOT GATHER STATS ON INTERNAL TEXT INDEX TABLES:$I, $R, $K,$N,$P

3. Is using EXEC FND_STATS.LOAD_XCLUD_TAB a valid workaround for this issue?

Although forcing the queue tables to be excluded will workaround the problem, it will eventually lead to performance
problems when those queue tables are used. The baseline support recommendation is that stats should be gathered on
 E-Business suite queue tables. Stats only normally become locked on E-Business suite queue tables when the queues
are dropped and recreated. This can happy manually via SQL and during patching. However the correct procedure here is
 to always unlock the stats in the queues are recreated. One will need to add this check to any post patching document you run onsite.

4. Can one force Gather Schema Statistics to continue if it hits an object with locked stats?

Forcing Gather Schema Statistics to continue if it hits an object with locked stats is not a good idea due to the performance
problems described in point 1. However as a DBA, it is ones database and ones choice about how to maintain your database.
 What one needs to do is monitor the amount of data being stored in the queue tables that are being locked.
If they consistently contain no rows then there is no use in including them in the GSS run so you could use the workaround above.

However the problem now is that GSS will fail if a NEW queue table has it's stats locks,
 this will lead one to a constant monitoring process until all of the queue tables have been excluded.
However for completeness, investigate the issue a little further and while it is possible to force
 DBMS_STATS.GATHER_SCHEMA_STATS to ignore locked stats by passing the "force=>TRUE" parameter.

How to restore dropped table in oracle 12c


Recovering a Dropped Table Using Oracle Flashback Drop 

Oracle Flashback Drop enables you to reverse the effects of dropping (deleting) a table, returning the dropped table to the database along with dependent objects such as indexes and triggers. This feature stores dropped objects in a recycle bin, from which they can be retrieved until the recycle bin is purged, either explicitly or because space is needed.

As with Flashback Table, you can use Flashback Drop while the database is open. Also, you can perform the flashback without undoing changes in objects not affected by the Flashback Drop operation. Flashback Table is more convenient than forms of media recovery that require taking the database offline and restoring files from backup.

Note:

For a table to be recoverable using Flashback Drop, it must reside in a locally managed tablespace. Also, you cannot recover tables in the SYSTEM tablespaces with Flashback Drop, regardless of the tablespace type.


Here is an example of recovering a dropped table with flashback.  
To recover the table we first check to see that it resides in the recyclebin,
and then we issue the "flashback table to before drop" command to recover the table:

SQL> drop table tarauni;

Table dropped.

SQL> SHOW RECYCLEBIN;

ORIGINAL NAME  RECYCLEBIN NAME                OBJECT TYPE   DROP TIME
-------------- ------------------------------ ------------  -----------
tarauni       BIN$ANbliLHaSiu02xI+zbvDvQ==$0             TABLE      2012-03-26:16:51:54

SQL> select original_name from dba_recyclebin;

ORIGINAL_NAME
--------------------------------
tarauni

SQL> flashback table tarauni to before drop;

Flashback complete.

SQL> select * from tarauni;

ID
----------
1000

SQL> select count(*) from BIHAR;

  COUNT(*)
----------
    132172

SQL> drop table BIHAR;

Table dropped.

SQL> CREATE table BIHAR;

Table dropped.

SQL> ALTER TABLE BIHAR RENAME TO BIHAR_bkp;

Table altered.

SQL> flashback table BIHAR to before drop;

Flashback complete.

SQL> select count(*) from BIHAR;

  COUNT(*)
----------
    132172


Now flashback table to time 08:00:00

SQL> SHOW RECYCLEBIN;

ORIGINAL NAME    RECYCLEBIN NAME                            OBJECT TYPE            DROP TIME
---------------- ------------------------------ ------------ -------------------
   BIHAR               BIN$X5W4sitiSZ3gUyqsxAob1Q==$0          TABLE                      2017-12-05:10:13:41


restore from recyclebin with drop time.

SQL> FLASHBACK TABLE BIHAR to timestamp TO_TIMESTAMP( ‘2017-12-05 10:13:41′,’YYYY-MM-DD HH24:MI:SS’);

Flashback complete.

SQL> select count(*) from BIHAR;

  COUNT(*)
----------
    132172

You cannot run any DML or DDL on dropped tables ….

SQL> Insert into tempp values(20);
Insert into tempp values(20)
*
ERROR at line 1:
ORA-00942: table or view does not exist

FLASHBACK TABLE TO A TIME IN THE PAST.

Firstly enable row movement for table test..

SQL> ALTER TABLE TEST ENABLE ROW MOVEMENT;
TIME : 08:00:00

SQL> select * from BIHAR;

SALARY
———-
5000

TIME :08:00:01

SQL> update BIHAR set salary =6000;
1 row updated.

SQL> select * from BIHAR;
SALARY
———-
6000

SQL> commit;
Commit complete.

Now flashback table to time 08:00:00

SQL> FLASHBACK TABLE BIHAR to timestamp TO_TIMESTAMP( ‘2005-09-13 08:00:00′,’YYYY-MM-DD HH24:MI:SS’);

Flashback complete.

SQL> SELECT * FROM BIHAR;
SALARY
———-
5000

Using Flashback Drop and Managing the Recycle Bin
When you drop a table, the database does not immediately remove the space associated with the table.
The database renames the table and places it and any associated objects in a recycle bin, where,
in case the table was dropped in error, it can be recovered at a later time. This feature is called Flashback Drop, and the FLASHBACK TABLE statement is used to restore the table. Before discussing the use of the FLASHBACK TABLE statement for this purpose, it is important to understand how the recycle bin works, and how you manage its contents.


What Is the Recycle Bin?
The recycle bin is actually a data dictionary table containing information about dropped objects.
 Dropped tables and any associated objects such as indexes, constraints, nested tables,
and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

Each user can be thought of as having his own recycle bin, since unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns.
 A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty.

Likewise:

When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.

When you drop a cluster, its member tables are not placed in the recycle bin and any former member
 tables in the recycle bin are purged.

When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and
any former dependent objects in the recycle bin are purged.

Object Naming in the Recycle Bin

When a dropped table is moved to the recycle bin, the table and its associated objects are given
system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name.

This could occur under the following circumstances:

A user drops a table, re-creates it with the same name, then drops it again.

Two users have tables with the same name, and both users drop their tables.

The renaming convention is as follows:

BIN$unique_id$version
where:

unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases

version is a version number assigned by the database

Enabling and Disabling the Recycle Bin

You can enable and disable the recycle bin with the recyclebin initialization parameter.
When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin.
When the recycle bin is disabled, dropped tables and their dependent objects are not placed in the recycle bin;
they are just dropped, and you must use other means to recover them (such as recovering from backup).

The recycle bin is enabled by default.

To disable the recycle bin:

Issue one of the following statements:

ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF;

To enable the recycle bin:

Issue one of the following statements:

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON;

Enabling and disabling the recycle bin with an ALTER SYSTEM or ALTER SESSION statement takes effect immediately.

 Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin.

Like any other initialization parameter, you can set the initial value of the recyclebin parameter in
 the text initialization file initSID.ora:

recyclebin=on


Viewing and Querying Objects in the Recycle Bin

Oracle Database provides two views for obtaining information about objects in the recycle bin:

View                                                      Description
USER_RECYCLEBIN This view can be used by users to see their own dropped objects in the recycle bin.  It has a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN This view gives administrators visibility to all dropped objects in the recycle bin One use for these views is to identify the name that the database has assigned to a dropped object, as shown in the following example:

SELECT object_name, original_name FROM dba_recyclebin
   WHERE owner = 'HR';

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 EMPLOYEES

You can also view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLEBIN.

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES        BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE        2003-10-27:14:00:19

You can query objects that are in the recycle bin, just as you can query other objects. However,
 you must specify the name of the object as it is identified in the recycle bin. For example:

SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";

Purging Objects in the Recycle Bin

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space.

You need the same privileges as if you were dropping the item.

When you use the PURGE statement to purge a table, you can use the name that the table is known by in the recycle bin or the original name of the table. The recycle bin name can be obtained from either the DBA_ or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin".

The following hypothetical example purges the table hr.int_admin_emp, which was renamed to BIN$jsleilx392mk2=293$0 when it was placed in the recycle bin:

PURGE TABLE BIN$jsleilx392mk2=293$0;

You can achieve the same result with the following statement:

PURGE TABLE int_admin_emp;

You can use the PURGE statement to purge all the objects in the recycle bin that are from a specified
 tablespace or only the tablespace objects belonging to a specified user, as shown in the following examples:

PURGE TABLESPACE example;
PURGE TABLESPACE example USER oe;
Users can purge the recycle bin of their own objects, and release space for objects,
by using the following statement:

PURGE RECYCLEBIN;

If you have the SYSDBA privilege, then you can purge the entire recycle bin by specifying DBA_RECYCLEBIN, instead of RECYCLEBIN in the previous statement.

You can also use the PURGE statement to purge an index from the recycle bin or to purge from the recycle bin all objects in a specified tablespace.


Restoring Tables from the Recycle Bin

Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name.
An optional RENAME TO clause lets you rename the table as you recover it.
The recycle bin name can be obtained from either the DBA_ or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". To use the FLASHBACK TABLE ...
TO BEFORE DROP statement, you need the same privileges you need to drop the table.

The following example restores int_admin_emp table and assigns to it a new name:

FLASHBACK TABLE int_admin_emp TO BEFORE DROP RENAME TO int2_admin_emp;

The system-generated recycle bin name is very useful if you have dropped a table multiple times.
 For example, suppose you have three versions of the int2_admin_emp table in the recycle bin and
you want to recover the second version. You can do this by issuing two FLASHBACK TABLE statements, or you can query the recycle bin and then flashback to the appropriate system-generated name, as shown in the following example. Including the create time in the query can help you verify
that you are restoring the correct table.

SELECT object_name, original_name, createtime FROM recyclebin; 

OBJECT_NAME                    ORIGINAL_NAME   CREATETIME
------------------------------ --------------- -------------------
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP  2006-02-05:21:05:52
BIN$yrMKlZaVMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP  2006-02-05:21:25:13
BIN$yrMKlZaQMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP  2006-02-05:22:05:53

FLASHBACK TABLE BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TO BEFORE DROP;

Restoring Dependent Objects

When you restore a table from the recycle bin, dependent objects such as indexes
 do not get their original names back; they retain their system-generated recycle bin names.
 You must manually rename dependent objects if you want to restore their original names.
If you plan to manually restore original names for dependent objects, ensure that you make
note of each dependent object's system-generated recycle bin name before you restore the table.

The following is an example of restoring the original names of some of the indexes of the dropped
table JOB_HISTORY, from the HR sample schema. The example assumes that you are logged in as the HR user.

After dropping JOB_HISTORY and before restoring it from the recycle bin, run the following query:

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;

OBJECT_NAME                    ORIGINAL_NAME             TYPE
------------------------------ ------------------------- --------
BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX              INDEX
BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX         INDEX
BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX       INDEX
BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK   INDEX
BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY               TABLE


Restore the table with the following command:

FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;

Run the following query to verify that all JOB_HISTORY indexes retained their system-generated recycle bin names:

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';

INDEX_NAME
------------------------------
BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
BIN$DBo9UChvZSbgQFeMiAdCcQ==$0

Restore the original names of the first two indexes as follows:

ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;
ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_EMPLOYEE_IX;

Note that double quotes are required around the system-generated names.

ORA-01950: no privileges on tablespace 'SYSTEM'



ORA-01950: no privileges on tablespace "string"
 
Cause: User does not have privileges to allocate an extent in the specified tablespace.Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.



Solution :-

  • ALTER USER <username> QUOTA 100M ON <tablespace name>
     
  • GRANT UNLIMITED TABLESPACE TO <username>
And to also make sure the user has been granted Connect, Resources roles incase the user was not given Create table privileges.


ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:- IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1) SYMPTOMS:- Yo...