ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

Hi ,


Today i have got a strange issue which i have never encountered.

I have got an issue saying an INDEX Status is in INPROGRS. The errors is as below.

Error: 

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

INDEX_NAME                     STATUS   DOMIDX_STATU DOMIDX
------------------------------ -------- ------------ ------
TEST_INDX                      INPROGRS VALID        VALID


Resolution:

I tried to rebuild the index but didn't helped and not able to drop the index.

SQL> drop index TEST_INDX;
drop index TEST_INDX *
ERROR at line 1:
ORA-29868: cannot issue DDL on a domain index marked as LOADING


DROPPED index with FORCE Option.

SQL> drop index TEST_INDX FORCE;

Index dropped.


ReCREATED the index and got fixed the issue



Thank you !!

SG

MRP0: Background Media Recovery terminated with error ORA-01110/ORA-01157/ORA-17503

Hi,

I have faced a interesting issue which occurs very commonly for everyone and just want to share my experience on this for reference

Please find the steps to be followed if we have an error below on standby due to MRP:

Errors in file /local/oracle/diag/rdbms/test/test_DR_mrp0_11295.trc:

ORA-01111: name for data file 8 is unknown - rename to correct file

ORA-01110: data file 8: '/local/oracle/product/11.1.0/db_1/dbs/UNNAMED00008'

ORA-01157: cannot identify/lock data file 8 - see DBWR trace file

ORA-01111: name for data file 8 is unknown - rename to correct file

ORA-01110: data file 8: '/local/oracle/product/11.1.0/db_1/dbs/UNNAMED00008'

 

Step:

1) Identify which is the file you are missing on standby database from the alert log as above seen.

Primary :  select FILE#,NAME,bytes/1024/1024 from  v$datafile where file#=8;

• Review the tablespace and how much size it has been allocated with the above Query.

Go to Standby:

Standby: select FILE#,NAME,bytes/1024/1024 from  v$datafile where file#=8;

• Review the tablespace and the location and name . In this example the file got created at the home location as below.

         eg: 'local/oracle/product/11.1.0/db_1/dbs/UNNAMED00008'

2) check the standby_file_management parameter on standby and make it to MANUAL from AUTO;

sho parameter standby_file_management

NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------------------
standby_file_management             |string     |MANUAL
alter system set standby_file_management=MANUAL;

3) create a datafile with the following syntax.

alter database create datafile 'local/oracle/product/11.1.0/db_1/dbs/UNNAMED00008' as '< ASM Diskgroup> '  size <xxxm>;

or

alter database create datafile 'local/oracle/product/11.1.0/db_1/dbs/UNNAMED00008' as < specified path> size < xxxm > ;

4) Change the standby_file_management parameter to AUTO .

5) start the mrp as below..

• alter database recover managed standby database disconnect;

Hope this help for all ...

Thank you !!
SG

Datapump IMPDP Fails With ERRORS ORA-39097 ORA-39065 ORA-12801 ORA-1460 In RAC Environment [ID 1427537.1]

Import: Release 11.2.0.2.0 - Production on Lun. Févr. 6 10:07:26 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "AGRT997"."IMPDP_MDHP1_201202006100500" successfully loaded/unloaded
Starting "AGRT997"."IMPDP_MDHP1_201202006100500": AGRT997/******** PARFILE=D:\Batch\script_gen\DP_IMP_MDHP1_pour_S1.PAR
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "AGRMDHS1"."DOCMNIMAGE"          4.455 GB      65505 rows
ORA-39097: Data Pump job encountered unexpected error -12801
ORA-39065: unexpected master process exception in MAIN
ORA-12801: error signaled in parallel query server PZ99, instance QUBC1-SGBDA16N:accp2 (2)
ORA-01460: unimplemented or unreasonable conversion requested
Job "AGRT997"."IMPDP_MDHP1_201202006100500" stopped due to fatal error at 10:19:51

Cause

This issue was investigated in bug 13654760.

Solution

Use the following available workaround to resolve this issue:

- set parameter PARALLEL_DEGREE_POLICY=MANUAL.

Unfortunately there are no patches available for this issue and no other workarounds available.


Regards,
Suresh.G

Datapump NETWORK_LINK Option:

I found good option using datapump Network_link which saves lot of time of time avoiding export and diskspace for the export dumps.

Steps to be followed:

Eg: Source Database is ABC ( Production)
      Target  Database is XYZ ( QA)

Prerequisites :

1) Create DB_LINK from XYZ database on QA to ABC Database on Production.
2) Grant  Roles of EXP_FULL_DATABASE and IMP_FULL_DATABASE to target database (XYZ)  and EXP_FULL_DATABASE to Source Database (ABC)

3) Make sure you provide the username & password of Target Database which is QA here in the example.
4) Start the import using network_link option as below.

Syntax:

From Target Database.


impdp userid= usrename/password@targetdb  network_link=ABC.WORLD parfile= ABC.par

Conclusion:

This option works majorly for larger schemas where we cannt afford time for export & ftp and having space issues on the server.


Regards,
Suresh.G