ORA-02022 remote statement has unoptimized view with remote object sqlserver merge command issue

This kind of statement is not supported by the Database Gateway.
Please review the Gateway documentation (http://docs.oracle.com/database/121/GMSWN/ch3.htm#GMSWN200) :
----------------------------------------------------------------------------------------------------------------
Callback Support
SQL statements that require the gateway to callback to Oracle database would not be supported.

The following categories of SQL statements will result in a callback:

Any DML with a sub-select, which refers to a table in Oracle database. For example:
INSERT INTO emp@non_oracle SELECT * FROM oracle_emp;



ORA-02022 remote statement has unoptimized view with remote object

following is not supported in oracle
insert into tblDematDetails@Lnk_Datum (Oowncode) select Clientocode from demat where vallan=2015062 and compcode=17140
;

oracle sql server datetime overflow

use to_char

    select to_char(Markethistory.Dtoftran,'DD-MM-YYYY') from Markethistory;

LD Addon Institutional Contract Document does not exist error

change work process to 7 to 1

ORA-02070 error insert data into SQL Server

insert into vc@odbc ("c1") values (current_timestamp);
ORA-02070: database ODBC does not support operator 293 in this context


insert into vc@odbc ("c1") values (sysdate);
ORA-02070: database ODBC does not support special functions in this context


We worked around this issue in the following way:

We created this SQL Server table:
create table vc ( c0 int IDENTITY(1,1) NOT NULL, c1 datetime, primary key (c0))


Next, we tried to run one of the INSERT statements shown above but got the ORA-02070 error. To work around this, we passed the contents of the function into a data store and then passed the data store to the INSERT statement. For example:

DECLARE
d1 date;
BEGIN
select sysdate into d1 from dual;
INSERT INTO vc@odbc ("c1") values (d1);
END;/

PL/SQL procedure successfully completed.

SQL> select * from vc@odbc;

        c0 c1
---------- ---------
         1 03-MAR-14

ORA-06508 PLSQL could not find program unit being called

select object_name from user_objects where object_type='PACKAGE'
MINUS
select object_name from user_objects where object_type='PACKAGE BODY';

ora-01502 index unusable state

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;'
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE';

Grant role access to all objects in a schema to another user

BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('PROCEDURE','PACKAGE','FUNCTION') )
LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||i.object_name||' TO kshitij';
END LOOP;
END;
/

BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('TABLE') )
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||i.object_name||' TO kshitij';
END LOOP;
END;
/

BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('SEQUENCE') )
LOOP
EXECUTE IMMEDIATE 'SELECT ON '||i.object_name ||' TO kshitij';
END LOOP;
END;
/

Sessions Hang On Wait Event WAIT FOR EMON PROCESS NTFNS ID 1287435.1

Sessions Hang On Wait Event "WAIT FOR EMON PROCESS NTFNS" [ID 1287435.1]



Symptoms

RDBMS 10.2.0.4.0
OCI based DB change notification
EMON spin with high CPU usage
EMN0 locks affected multiple applications for a few hours.
Performance suffers until DBCN Database Change Notification is disabled.


Cause


Bug 7282408: EMN STUCK - OTHER SESSIONS HANG ON "WAIT FOR EMON TO PROCESS NTFNS"



Filename = awr_report_29315_29317.html
See the following error:
In Top 5 Timed Events, "wait for EMON to process ntfns " wait event is listing

Matches Bug 7282408 filed against 10.2.0.4.0 and fix is included in 11.1.0.6.0 RDBMS
WORKAROUND:
SQLNET.SEND_TIMEOUT=3

Values are in seconds. However the send timeout is a tunable parameter and there is no standard value which has been prescribed. The ideal value for this parameter would be such that this does not affect the normal working clients.





Solution


1) Set SQLNET.SEND_TIMEOUT=10

It was found that after the SEND_TIMEOUT parameter was adjusted and resolved the EMN process to stop spinning and de-register the subscribers; if customer tried to re-register, the subscribers were removed as soon as new changes took place and could not re-register successfully until the EMN process was killed.
Continued problem resolution found that unpublished Bug 6750296 'Incorrect handling of Connection Failures' was needed to address the incorrect handling of connection failure issues.

Bug 8605337 - Deadlock between gather stats job and concurrent DDL [ID 8605337.8]

Bug 8605337  Deadlock between gather stats job and concurrent DDL



Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected
11.2.0.1
11.1.0.7
11.1.0.6
Platforms affected Generic (all / most platforms affected)
Fixed:

This issue is fixed in
12.1 (Future Release)
11.2.0.2 (Server Patch Set)
Symptoms:

Related To:

Deadlock
ORA-60
Optimizer
PL/SQL (DBMS Packages)
DBMS_STATS
Description

ORA-60 can occur when gathering stats and some other concurrent DDL
is happening on the same table(s).

The deadlocking sessions will wait on following wait events:
- 'enq: TX - row lock contention' on tab$ table
- 'library cache lock' on a sys owned object

ORA-00439 feature not enabled Deferred Segment Creation 11g enterprise to standard edition downgrade

1293326.1


http://lifeandoracle.blogspot.in/2012/07/ora-00439-feature-not-enabled-deferred.html
http://ocpdba.wordpress.com/2009/10/09/deferred-segment-creation-segmentless-tables-11gr2-new-feature-and-dumb-questions/
http://asanga-pradeep.blogspot.in/2011/01/segment-creation-behavior-change.html
http://www.donotcommit.net/?p=51


------------------
1. Init.ora Parameter "DEFERRED_SEGMENT_CREATION" Reference Note [ID 1216282.1]
2. IMP-00003, ORA-00959 While Importing Data Into Existing Table Of 11gR2 Using Traditional Import [ID 1180873.1]
3. Bug 8795792 - DBMS_METADATA.get_ddl generates wrong keywords for compressed indexes in 11.2 [ID 8795792.8]
4. Exporting Schema In 11.2 Database With 10g Client Gives ORA-1455 [ID 1083330.1]
1293326.1
-----------------

then you must know you have just hit one of the new features of 11gR2. Unfortunately this feature makes the life of the casual DBA a little bit more complicated than necessary.

I found an interesting debate about whether this must be considered a feature or a bug in the OTN forums but I very much appreciated the last comment where Michiel provided a working solution without delving into philosophical matters. I remember doing the same thing when dealing with another EXPDP/IMPDP problem between versions 11.1.0.6 and 11.1.0.7.

---------------------------Solution 1--------------

-- on the source instance
EXPDP ldbo/ldbo@apx1314srv full=Y dumpfile=expdp1314.dmp directory=DATA_DUMP_DIR logfile=expdp1314.log version=10.2

-- on the target instance
IMPDP ldbo/ldbo@apx1314srv full=Y dumpfile=expdp1314.dmp directory=DATA_DUMP_DIR logfile=expdp1314.log version=10.2


---------------------------Solution 2--------------

For 11.2.0.1 one way to avoid the issue is to allocate segment manually before the export. This could be done with

alter table  allocate extent;

select 'alter table '||table_name||' allocate extent;' from user_tables where segment_created = 'NO';



set autocommit on;
set line 124;

select 'ALTER TABLE ' || TABLE_NAME || ' ALLOCATE EXTENT;' from user_tables where SEGMENT_CREATED = 'NO';
select 'ALTER TABLE '|| TABLE_NAME || ' ALLOCATE EXTENT;' from user_lobs where SEGMENT_CREATED = 'NO';
select 'ALTER INDEX '|| INDEX_NAME || ' ALLOCATE EXTENT;' from user_indexes where SEGMENT_CREATED = 'NO';



---------------Solution 3--------

SQL> alter system set deferred_segment_creation=false;



===================
From 11g Release 2 all tables created without rows, do not create any segments (by default). If you want to change this behaviour, tweak the spfile parameter “deferred_segment_creation” to FALSE. Anyway the people installing Peoplesoft, SAP, Siebel and other thousand-table data models, really do thank for this new feature.

How does it work?

If the INITIAL extent of a table is 15 MegaBytes in size, but the table is empty, the table won’t ask for that 15 MB of space. As soon as the first row of data is inserted (even a few bytes), the Oracle space engine will run the storage space settings of the table, before inserting that line, making the table actually owner of that space. You can create a 100GB table onto a 100MB tablespace as long as the table is empty, because the space will only be reclaimed by the table when it receives the first row.

========================

In 11.2 the initialization parameter DEFERRED_SEGMENT_CREATION controls whether segment space is allocated at the time of the segment creation. This is set to true by default both on enterprise edition and standard edition. But on standard edition this parameter has no effect as this feature is not enabled. Initialization parameter behavior could be override at table with
SQL> CREATE TABLE x(a number) SEGMENT CREATION IMMEDIATE;
or
SQL> CREATE TABLE x(a number) SEGMENT CREATION DEFERRED;
But on standard edition this would still give an error.
SQL>  CREATE TABLE x(a number) SEGMENT CREATION DEFERRED;
CREATE TABLE x(a number) SEGMENT CREATION DEFERRED
*
ERROR at line 1:
ORA-00439: feature not enabled: Deferred Segment Creation
Problem happens when exporting some empty tables from an enterprise edition database to a standard edition database. According to the default behavior on enterprise edition when tables have no rows there won't be any segments. But default behavior on standard edition is that, tables are always created with segments.

In 11.2.0.1 when importing tables from enterprise to standard edition tables with no segments throws an error while in 11.2.0.2 tables get created with segments without an error.


========================

Oracle 11g2 does NOT export empty tables when using exp.exe

http://tsells.wordpress.com/2011/02/10/oracle-11g2-does-not-export-empty-tables-when-using-exp-exe/

Out of habit I have been using the older data import / export functions in lieu of using the Data Pump commands that Oracle recommends.  As of 11g2 I can no longer do this.  The exp.exe no longer exports tables that do not have an entry in DBA_SEGMENTS.  This is due to a new feature named “deferred segment creation”.

The syntax for the new Data Pump commands are different and require some additional setup / maneuvering on the Oracle Server itself to complete.  The files when importing now must be placed in the Data Pump Directory.  This can be found by running the following query against the database server.

select owner, directory_name, directory_path from dba_directories

================


The cause is a new feature of Oracle 11.2: deferred segment creation.
All newly created tables that has no rows in it (ever!), has no corresponding row in DBA_SEGMENTS, so somewhy won't get exported with (deprecated) exp utility. Use expdp instead.


================

select 'alter table '||table_name||' allocate extent;' from user_tables where segment_created = 'NO';



set autocommit on;
set line 124;

select 'ALTER TABLE ' || TABLE_NAME || ' ALLOCATE EXTENT;' from user_tables where SEGMENT_CREATED = 'NO';
select 'ALTER TABLE '|| TABLE_NAME || ' ALLOCATE EXTENT;' from user_lobs where SEGMENT_CREATED = 'NO';
select 'ALTER INDEX '|| INDEX_NAME || ' ALLOCATE EXTENT;' from user_indexes where SEGMENT_CREATED = 'NO';

================

Don't forget: the deferred segment creation applies also to indexes and lobs and Indexes inherit table characteristics.
You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes and lobs, this column shows YES, if the segment is created.
However you should not take care of LOBs and LOB indexes extent allocations since they will be allocated automatically by the database once you allocate the corresponding table extent.

================Export was always failing due to the empty tables. Here is my dynamic sql======

DECLARE
CURSOR cur IS
SELECT 'ALTER TABLE ' || table_name || ' ALLOCATE EXTENT ' vSQL
FROM user_tables
where SEGMENT_CREATED = 'NO';
BEGIN
FOR c IN cur LOOP
BEGIN
EXECUTE IMMEDIATE c.vSQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 250));
END;
END LOOP;
END;
/

ORA-24330 OCI INTERNAL ERROR

Solution: alter system set client_result_cache_size=0 scope=spfile;




Effect of client_result_cache_size On Client Applications Or OCI Based Applications [ID 1300727.1]


Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.
Symptoms

- Receiving error 'ORA-24330: OCI INTERNAL ERROR' while running back end Procedures from SQL*Plus and also from the Application connected to the Database - Oracle 11g 11.2.0.2.

- Unable to log in through Application.
Changes

- Database was upgraded from Oracle 11g Release 2 - 11.2.0.1 to 11.2.0.2. The client_result_cache_size was set to 1GB.

- Parameter client_result_cache_size is not on by default and the recommended value is 0.


Cause

client_result_cache_size is supposed to reset once the client process terminates but in cases like transactions with millions of rows the cache gets full very soon and does not reset itself while the client process is still on and fails during that time with errors like 'ORA-24330: OCI INTERNAL ERROR'.

If client_result_cache_size is set to a non zero value, technically it would mean that you have turned on the setting client_result_cache_size and it does have a limit active until that client session that started is active.  For example; if the value is set to 1GB and the process that started client process is still actively processing million of rows then it will for sure get 'ORA-24330: OCI INTERNAL ERROR' when it reaches 1GB cache which is the set hard limit value.

So for higher volume of transactions the recommended value for client_result_cache_size is zero to avoid all such issues.

BUG 8480915 - ORA-24330: OCI INTERNAL ERROR IN ODP.NET STRESS WHEN CLIENT RESULT CACHE ENABLED

Base BUG 8418934 - KPOQGINV: CLIENT CACHING GOT DISABLED AFTER BADREGID ERROR
Solution

'client_result_cache_size' is an internal integer parameter and to change settings, please do:

1. Setting client_result_cache_size = 0 in the initialization (init.ora) parameter files followed by instance restart is the most reliable way.

client_result_cache_size=0 need to be set at the init.ora and bouncing the DB should effectively set that value.

If client_result_cache_size is set to 0 and it would mean that NO client_result_cache_size is on and nothing will be in cache.

(OR)

Client caching can be totally removed by commenting out the parameter setting client_result_cache_size from init.ora (or) by totally removing that entry.

Once the DB comes back on, you can check the value from SQL*Plus by doing:

SQL> show parameter CLIENT_RESULT_CACHE_SIZE;

2. Alternatively;

If spfile is used;

i. connect / as sysdba to database
ii. alter system set client_result_cache_size=64000 scope=SPFILE; -- value set to 64KB, please note that this is just a sample value.
iii. shutdown the database
iv. startup the database

Once the DB comes back on, you can check the value from SQL*Plus by doing:

SQL> show parameter CLIENT_RESULT_CACHE_SIZE;

'client_result_cache_size' is stored on the Client side.  Once that limit have reached to the maximum then all other subsequent connections will fail.  That is why it is recommended to set to 0 for high volume of transactions in which case there will be no caching.  Even if there's a limit it gets reset only when the Database is bounced.
References

Windows 7 64 bit (unable to make connect to sql data source error# -1)

Error in odbc.dbf : Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

Solution:
 I have changed scb1213srv with scb1213 or someother name in directy.dbf, odbcad32, tnsnames.ora Now it is working

Cause:
Client IT team create ODBC using registry batch file, may scb1213srv is present some where.
ODBC is not points to listener address properly

Steps
Check your PATH and ORACLE_HOME environment variables
They should point to the location you installed the client and under which your tnsnames.ora file is located.

LD error money obligation cexist ORA-01422 exact fetch returns more than one requested number of rows

That error occurs in a SELECT ... INTO statement, when your query returns more than one row. Check your where clause, and make the necessary changes in order to ensure just 1 row is returned. There is a predefined exception for that error. It is: too_many_rows. If you know your query could return more than 1 row in some cases, you should put an exception handler, something like this:
<pre>Begin
SELECT xx into l_xx
FROM table_x;
Exception
when too_many_rows then
' do something
End;</pre>





select distinct oowncode,stkcode
  from compexist
 where firmnumber = 'FGSB-00001'
   and stkcode in (select stkcode
                     from (select distinct stkcode, oowncode
                             from compexist
                            where firmnumber = 'FGSB-00001'
                              and exchange = 'BSE ')
                    group by stkcode
                   having count(*) > 1)





[Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-06512: at "LDBO.PK_NSEBI

Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-0651


duplicacy of oowncode in accountaddressdetails

                    SELECT COUNT(*) INTO LNTOTALRECORDS FROM ACCOUNTADDRESSDETAIL ACCOUNTADDRESSDETAIL
                     WHERE ACCOUNTADDRESSDETAIL.FIRMNUMBER=ICFIRMNUMBER AND ACCOUNTADDRESSDETAIL.OOWNCODE=CLEXTRA.OOWNCODE ;

Nse Bill Data Generation cannot take place. ~-1422~ORA-01422 exact fetch returns more than requested number of rows~ORA-06512 at LDBO.PK_NSEBI

[Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-06512: at "LDBO.PK_NSEBI

Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-0651


SPLOGIN IS PRESENT IN LDBO AND SYS USER




duplicacy of oowncode in accountaddressdetails

                    SELECT COUNT(*) INTO LNTOTALRECORDS FROM ACCOUNTADDRESSDETAIL ACCOUNTADDRESSDETAIL
                     WHERE ACCOUNTADDRESSDETAIL.FIRMNUMBER=ICFIRMNUMBER AND ACCOUNTADDRESSDETAIL.OOWNCODE=CLEXTRA.OOWNCODE ;




primary key is from

FIRMNUMBER
CODE


but can be dupicate oowncode

table alias uncommitted changes

http://www.mofeel.net/110-microsoft-public-fox-programmer-exchange/5596.aspx
http://support.microsoft.com/KB/249836


Table buffer for alias "name" contains uncommitted changes. (1545)

The problem manifests itself when I load data, modify it and load the next record. When that happens it throws Error #1545 Table buffer for alias "My_Cursor" contains uncommitted changes. I have been able to solve the problem by closing the cursor before I reload the new data. I missed this required step before.


---solution

create index on that vfp table

---------CHECK FOXPRO VIEW

MAIN.DBC
MODI CONNECTION
MODI VIEW

alter system flush buffer_cache;
alter system flush shared_pool;


----------

seems like you have the table set to Buffering = 5 Table. So when you skip
it will not save the record. either change the buffering to a 3 Record, or
do an implicit Tableupdate(). The record is not being saved, that's all


  SET MULTILOCKS ON
   =CURSORSETPROP("Buffering",3)


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

Appending a record to a view with
buffered changes is allowed and would
just add that buffered, depending on the
buffermode.

Have you SET MULTILOCKS ON?
What buffermode do you use?
If you are using remote views only
buffermode 3 and 5 are allowed, so
only optimistic buffering is possible.

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

When I fill a row/column in the grid ( DETAILBILL Table), display error :

CursorSetProp("buffering",THIS.oldBuffering,aTablesUsed([m.i,1])   && optimistic table buffering
Error: 1545
Table buffer for alias "DETAILBILL" contains uncommitted changes
Line 67

My code in form destroy event :
SELECT DETAILBILL
TABLEUPDATE(.T.)

You cannot change the buffering mode when there are uncommitted changes in the buffer. These changes are saved to the disk when you issue TABLEUPDATE() but your TABLEUPDATE() is issued after the CursorSetProp probably. To save changes made to your data in Destroy event is too late.


In versions prior to Visual FoxPro 9, using a SQL SELECT statement meant that the results were always pulled from disk. This meant that if you wanted to query uncommitted changes from a buffered table, you were forced to use procedural commands. Now it's possible to specify for each table in a SELECT statement whether to read from the disk or from the local buffer using SET SQLBUFFERING and SELECT ... WITH (Buffering = <lexpr>).

Some examples of how to use WITH (BUFFERING ...) include:

SELECT * FROM Customer WITH (BUFFERING = .t.)

SELECT * FROM Orders WITH (BUFFERING = lUseBuffer)

SELECT DISTINCT c.city, o.shipcity ;
  FROM customers C WITH (BUFFERING=.T.) ;
  JOIN orders O WITH (BUFFERING=.T.) ;
    ON c.customerID = o.customerID

ORA-22288 File Or LOB Operation FILEOPEN Failed On Windows2008 R2 OS SAN Attached Drive [ID 1504521.1]

Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Microsoft Windows x64 (64-bit)
Symptoms
Error
ORA-22288: file or LOB operation FILEOPEN failed on Windows2008 R2 OS SAN attached drive
Directory object refers to a directory which is a LUN on the netapp.
Re-pointing the Directory object to a folder on C:\ drive of the server with same permissions as the directory on the
LUN, the problem is resolved
Cause
Same symptoms described in
Bug 12667656 - DBMS_LOB.FILEOPEN() FAILS WITH ORA-22288 ON WINDOWS 2008 R2 LUN
which is closed as not a BUG
The implementation of a LUN may take place by junctions/symlinks in Windows.
It is disabled to use symlink/junctions with BFILE for security reason.
This has been introduced in unpublished Bug 2262685.
Solution
1. Use the new "disable_directory_link_check" access parameter, present in 11.2.0.3 and above versions. This
access parameter will disable additional checks for junctions.
For this, upgrade to the 11.2.0.2 latest bundle patch or 11.2.0.3 to get the new feature before creating an
external table with "disable_directory_link_check" access parameter.
-- OR --
2. Disable the check and allow links by turning on the hidden internal parameter "_kolfuseslf" in a DBA (SYS)
session, i.e. run
6/20/13 Document Display
https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?_adf.ctrl-state=6no1qqwc6_69 2/2
SQL> alter session set "_kolfuseslf" = true;
before executing the previously failing code/scripts





alter session set "_kolfuseslf" = true;


alter system set "_kolfuseslf" = true;

ora-600 [16513] [1403]

http://www.sql.ru/forum/377566/vopros-pro-shutdown-abort

ora-600 [16513] [1403] [20]


SQL > Select count (*) from $ obj;

  COUNT (*)
----------
      6296

SQL > delete  from $ obj;

6296  rows deleted.

SQL > shutdown abort
ORACLE instance shut down.
SQL > Startup;
ORACLE instance started.

Total System Global Area   135760720 bytes
Fixed Size                    731984 bytes
Variable Size              104857600 bytes
 Database Buffers            29360128 bytes
Redo Buffers                  811008 bytes
 Database mounted.
ORA- 01092 : ORACLE instance terminated. Disconnection forced



after all objects are created from the bootstrap $, when the first call
=====================
PARSING IN  CURSOR # 5 len = 52 dep = 1  uid = 0 oct = 3 lid = 0 tim = 2701521679320 hv = 782031521 ad = '84756cb8 '
Select ctime, mtime, stime from obj $ where obj # =: 1
 END  OF STMT
PARSE # 5 : c = 0 , e = 966 , p = 0 , cr = 0 , cu = 0 , mis = 1 , r = 0 , dep = 1 , og = 0 , tim = 2701521679306
BINDS # 5 :
 bind 0 : dty = 2 mxl = 22 ( 22 ) mal = 00 scl = 00 pre = 00 oacflg = 08 oacfl2 = 1  Size = 24 offset = 0
   bfp = ffffffff7cc6a138 bln = 22 avl = 02 flg = 05
   value = 4
EXEC # 5 : c = 10000 , e = 1,340 , p = 0 , cr = 0 , cu = 0 , mis = 0 , r = 0 , dep = 1 , og = 4 , tim = 2701521681024
WAIT # 5 : Nam = 'file db Sequential Read ' ela = 109 p1 = 1 p2 = 94 p3 = 1
WAIT # 5 : Nam = 'db file Sequential Read' ela = 78 p1 = 1 p2 = 1291 p3 = 1
 Begin Cleaning out Block ...
Block header dump: 0x0040050b
 Object ID on Block? Y
 seg / obj: 0x24 csc: 0x00. 2452   itc: 2   flg: O typ: 2 - INDEX
     fsl: 2   fnx: 0x0 ver: 0x01

 Itl Xid Uba Flag Lck Scn / Fsc
0x01 0x000a.00a. 00000004   0x008000ba. 0002 . 01   CB - 0 SCN 0x0000.000021c0
0x02 0x0002. 013 . 00000378   0x00800025. 0075 .0 F   ---- 298 fsc 0x0fd9.00000000

- Poskipal -

----- End of leaf dump Block -----
Found All committed transactions

- Poskipal

Block cleanout Record , SCN: 0x0000.001b3c72 ver: opt 0x01: 0x01, entries follow ...
  itli: 2   flg: 2   scn: 0x0000. 00000001
Block header dump: 0x0040050b
 Object ID on Block? Y
 seg / obj: 0x24 csc: 0x00.1b3c72 itc: 2   flg: O typ: 2 - INDEX
     fsl: 0   fnx: 0x0 ver: 0x01

 Itl Xid Uba Flag Lck Scn / Fsc
0x01 0x000a.00a. 00000004   0x008000ba. 0002 . 01   CB - 0 SCN 0x0000.000021c0
0x02 0x0002. 013 . 00000378   0x00800025. 0075 .0 F C --- 0 scn 0x0000.00000001

- T. E he just commits, no checks, as a result of

... clean out dump

Internal or Fatal error
ORA- 00600 : internal error code, arguments: [ 16513 ], [ 1403 ], [ 4 ], [], [], [], []

- Ora1403 - no data found

ORA-00350 log 3 of instance beb1314 (thread 1) needs to be archived ORA-00312 online log 3 thread 1 'EBEBD1314REDO03.LOG'

ORA-00350 ORA-00312


SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance beb1314 (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: 'E:\BEBD1314\REDO03.LOG'




16:36:22 SYS@eqdd011g SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00350: log 3 of instance eqdd011g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.17
16:38:00 SYS@eqdd011g SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

Elapsed: 00:00:02.69
16:38:13 SYS@eqdd011g SQL> alter database drop logfile group 3;

Database altered.


alter database open;

ORA-16014 ORA-00312

----------ORA-16014 ORA-00312

Archive process error: ORA-16014: log 3 sequence# 29808 not archived, no available destinations
ORA-00312: online log 3 thread 1: 'E:\BEBD1112\REDO03.LOG'



That would happen if the destination filesystem is full (i.e. at 100%) and Oracle cannot write the archivelog to that filesystem.


The general solution for this error is
- check the Disk space
- check where the archive location exists
- Try Clearing the Unarchived log group through the SQL :
   alter database clear unarchived logfile group <<No>>;
- Bounce the Db
- Bounce the Server



---------------------------Archive Log Creation Fails With Errors ORA-16014, ORA-00312 [ID 1373314.1]

Symptoms

When attempting to run "alter system switch logfile;", the session hangs and following errors are reported in the alert log and trace file:

Alert log :
Sun Oct 30 03:01:55 2011
ARC1: Failed to archive log 3 thread 3 sequence 122674
Sun Oct 30 03:01:55 2011
Errors in file /apps/oracle/9.2.0/admin/cwld/bdump/cwld_arc1_889040.trc:
ORA-16014: log 3 sequence# 122674 not archived, no available destinations
ORA-00312: online log 3 thread 3: '/data/oradata/cwld/redo3.log'


Changes

In our example, the database was moved (to new SAN).

Cause

In this case, the issue is caused by an inaccessible/incorrect archiving directory.

SQL> show parameter log_archive_dest_1

NAME                    TYPE    VALUE
-------------------- -------   ------------------------------
log_archive_dest_1  string     location=/data3/oradata/cwld/archive/


However, that location does not exist and the archive log cannot be created.

Solution

Correct the archive path to match the new location, for example;

SQL> alter system set log_archive_dest_1 = 'location=/data1/oradata/archive/';

Or

Remove the old directory path on the operating system and create the new directory path to match the Oracle archiving location.



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

Archive process error: ORA-16014: log 3 sequence# 53538 not archived, no available destinations
ORA-00312: online log 3 thread 1: 'E:\BEBD1314\REDO03.LOG'


alter system set db_recovery_file_dest_size=200m;


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

select Group#,Member from V$logfile;

select group#, status,bytes/1024/1024 from v$log;

 alter database add logfile group 4 ('E:\BEBD1314\REDO04.LOG') size 100m;
 alter database add logfile group 5 ('E:\BEBD1314\REDO05.LOG') size 100m;
 alter database add logfile group 6 ('E:\BEBD1314\REDO06.LOG') size 100m;


ALTER SYSTEM CHECKPOINT GLOBAL;

select group#, status from v$log;

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

alter system switch logfile;

alter system switch logfile;

select group#, status,bytes/1024/1024 from v$log;

Note: Can not drop current logfile should be inactive state;




ORA-00350 ORA-00312


SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance beb1314 (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: 'E:\BEBD1314\REDO03.LOG'




16:36:22 SYS@eqdd011g SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00350: log 3 of instance eqdd011g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.17
16:38:00 SYS@eqdd011g SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

Elapsed: 00:00:02.69
16:38:13 SYS@eqdd011g SQL> alter database drop logfile group 3;

Database altered.


alter database open;