DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, August 31, 2017

EXPDP – ORA-39006,ORA-39213 Metadata processing is not available.

EXPDP – ORA-39006,ORA-39213 Metadata processing is not available

While Taking full database export using “EXPDP” i was getting below mentioned error :

###############################################

$ expdp directory=datapump dumpfile=expdp_hrs92dmo.dmp logfile=expdp_hrs92dmo.log full=y

Export: Release 11.2.0.2.0 – Production on Thu Feb 27 10:48:09 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username : sys@hrs92dmo as sysdba
Password:

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
ORA-39006: internal error
ORA-39213: Metadata processing is not available

###############################################

I tried it many times but the error remains the same.

==> Problem Solution :

(1) Check all the oracle registry entry are in valid state. If not check the issue :

###########################################################

SQL> SELECT comp_id, version, status FROM dba_registry;

COMP_ID VERSION STATUS
—————————— —————————— ———–
EM 11.2.0.4.0 VALID
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 INVALID
###########################################################


(2) Check for Permission :

During dbms_metadata_util.load_stylesheets we read the directory $ORACLE_HOME/rdbms/xml/xsl and load the XSL files using DBMS_LOB package.

If the files cannot be read (most of cases by missing rwx privileges for oracle user), then we raise the errors above.

Re-load the stylesheets using the dbms_metadata_util.load_stylesheets procedure after the permission issue is solved and then retry DataPump export ( expdp ).

###########################################################

SQL> execute dbms_metadata_util.load_stylesheets

PL/SQL procedure successfully completed.

###########################################################



(3) Repair Registry :

In case you have INVALID status in dba_registry. Check it and try to find a solution for it. In my case “CATPROC” was invalid. 
To resolve this i run “CATPROC.sql” file which is present in “$ORACLE_HOME/RDBMS/admin/” directory :

###########################################################

SQL> ?/rdbms/admin/catproc.sql
###########################################################

###########################################################

Add the more space in Temporary tablespace
###########################################################
This solved my issue.

ORA-01276: Cannot add file +ASM..... File has an Oracle Managed Files file name.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------------------------------------------
+DATA/LOCATION/datafile/system.329.926973193
+DATA/LOCATION/datafile/sysaux.324.926973259
+DATA/LOCATION/datafile/undotbs1.307.926973213
+DATA/LOCATION/datafile/xdb_ts.326.926973193
+DATA/LOCATION/datafile/users.383.926973185
+DATA/LOCATION/datafile/db_data1.325.926973191
+DATA/LOCATION/datafile/db_data1.318.926973259
+DATA/LOCATION/datafile/db_data1.336.926973183
+DATA/LOCATION/datafile/db_data1.366.926973213
+DATA/LOCATION/datafile/db_lob1.335.926973185
+DATA/LOCATION/datafile/db_index1.384.926973191
+DATA/LOCATION/datafile/db_index1.315.926973183
+DATA/LOCATION/datafile/db_index1.330.926973257
+DATA/LOCATION/datafile/db_index1.313.926973213
+DATA/LOCATION/datafile/db_audit_data.312.926973259
+DATA/LOCATION/datafile/db_audit_data.376.926973215
/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00017

17 rows selected.

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00017'  as '+DATA';
alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00017'  as '+DATA'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management is automatic.


SQL> show parameter standby;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
standby_archive_dest                 string                            ?/dbs/arch
standby_file_management              string                            AUTO


SQL> alter system set standby_file_management='MANUAL';

System altered.

SQL>  SELECT name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------------------------------------------
+DATA/LOCATION/datafile/system.329.926973193
+DATA/LOCATION/datafile/sysaux.324.926973259
+DATA/LOCATION/datafile/undotbs1.307.926973213
+DATA/LOCATION/datafile/xdb_ts.326.926973193
+DATA/LOCATION/datafile/users.383.926973185
+DATA/LOCATION/datafile/db_data1.325.926973191
+DATA/LOCATION/datafile/db_data1.318.926973259
+DATA/LOCATION/datafile/db_data1.336.926973183
+DATA/LOCATION/datafile/db_data1.366.926973213
+DATA/LOCATION/datafile/db_lob1.335.926973185
+DATA/LOCATION/datafile/db_index1.384.926973191
+DATA/LOCATION/datafile/db_index1.315.926973183
+DATA/LOCATION/datafile/db_index1.330.926973257
+DATA/LOCATION/datafile/db_index1.313.926973213
+DATA/LOCATION/datafile/db_audit_data.312.926973259
+DATA/LOCATION/datafile/db_audit_data.376.926973215
/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00017

17 rows selected.

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00017' as '+DATA';
alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00017' as '+DATA'
*
ERROR at line 1:
ORA-01136: specified size of file 17 (12800 blocks) is less than original size of 1310720 blocks
ORA-01111: name for data file 17 is unknown - rename to correct file
ORA-01110: data file 17: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00017'


SQL>  alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00017' as '+DATA' size 10g  AUTOEXTEND ON  MAXSIZE 20000M;

Database altered.

SQL>  SELECT name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------------------------------------------
+DATA/LOCATION/datafile/system.329.926973193
+DATA/LOCATION/datafile/sysaux.324.926973259
+DATA/LOCATION/datafile/undotbs1.307.926973213
+DATA/LOCATION/datafile/xdb_ts.326.926973193
+DATA/LOCATION/datafile/users.383.926973185
+DATA/LOCATION/datafile/db_data1.325.926973191
+DATA/LOCATION/datafile/db_data1.318.926973259
+DATA/LOCATION/datafile/db_data1.336.926973183
+DATA/LOCATION/datafile/db_data1.366.926973213
+DATA/LOCATION/datafile/db_lob1.335.926973185
+DATA/LOCATION/datafile/db_index1.384.926973191
+DATA/LOCATION/datafile/db_index1.315.926973183
+DATA/LOCATION/datafile/db_index1.330.926973257
+DATA/LOCATION/datafile/db_index1.313.926973213
+DATA/LOCATION/datafile/db_audit_data.312.926973259
+DATA/LOCATION/datafile/db_audit_data.376.926973215
+DATA/LOCATION/datafile/db_index1.308.952006301

17 rows selected.

SQL> show parameter standby;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
standby_archive_dest                 string                            ?/dbs/arch
standby_file_management              string                            MANUAL
SQL> alter system set standby_file_management='AUTO' SCOPE=BOTH;

System altered.

SQL>

ORA-00350 & ORA-00312: ASM

Problem:- 

Get below error while when starting database.



SQL> startup
ORACLE instance started.

Total System Global Area 481259520 bytes
Fixed Size 1337352 bytes
Variable Size 381683704 bytes
Database Buffers 92274688 bytes
Redo Buffers 5963776 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 16265
Session ID: 20 Serial number: 3

Alert LOG:

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘+FRA/orcl/onlinelog/group_1.257.805381689’
ORA-17503: ksfdopn:2 Failed to open file +FRA/orcl/onlinelog/group_1.257.805381689
ORA-15012: ASM file ‘+FRA/orcl/onlinelog/group_1.257.805381689’ does not exist
ORA-00312: online log 1 thread 1: ‘+DATA/orcl/onlinelog/group_1.261.805381679’
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/onlinelog/group_1.261.805381679
ORA-15012: ASM file ‘+DATA/orcl/onlinelog/group_1.261.805381679’ does not exist
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16440.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: ‘+DATA/orcl/onlinelog/group_1.261.805381679’
ORA-00312: online log 1 thread 1:+FRA/orcl/onlinelog/group_1.257.805381689’

USER (ospid: 16440): terminating the instance due to error 313


Solution:-

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 481259520 bytes
Fixed Size 1337352 bytes
Variable Size 381683704 bytes
Database Buffers 92274688 bytes
Redo Buffers 5963776 bytes

SQL> alter database mount;


Database altered.


SQL> l
1* select a.group#, a.sequence#, a.status, a.archived, b.member from v$log a, v$logfile b where a.group#=b.group#
SQL> /

GROUP# SEQUENCE# STATUS ARC MEMBER
———- ———- —————- — ————————————————–
3 0 UNUSED YES +DATA/orcl/onlinelog/group_3.263.805381707
3 0 UNUSED YES +FRA/orcl/onlinelog/group_3.259.805381713
2 0 UNUSED YES +DATA/orcl/onlinelog/group_2.262.805381695
2 0 UNUSED YES +FRA/orcl/onlinelog/group_2.258.805381703
1 1 CURRENT NO +DATA/orcl/onlinelog/group_1.261.805381679

1 1 CURRENT NO +FRA/orcl/onlinelog/group_1.257.805381689


Physically Verification:-

ASMCMD> cd ORCL/
lsASMCMD>
AUTOBACKUP/
BACKUPSET/
CONTROLFILE/
ONLINELOG/
ASMCMD> cd ONLINELOG/
ASMCMD> ls -ltr
WARNING:option ‘r’ is deprecated for ‘ls’
please use ‘reverse’

Type Redund Striped Time Sys Name
ONLINELOG UNPROT COARSE FEB 20 09:00:00 Y group_1.257.840013685
ONLINELOG UNPROT COARSE FEB 20 09:00:00 Y group_2.258.805381703
ONLINELOG UNPROT COARSE FEB 20 09:00:00 Y group_3.259.805381713
ASMCMD> pwd
+FRA/ORCL/ONLINELOG
ASMCMD> cd +DATA/ORCL/ONLINELOG
ASMCMD> ls -ltr
WARNING:option ‘r’ is deprecated for ‘ls’
please use ‘reverse’

Type Redund Striped Time Sys Name
ONLINELOG MIRROR COARSE FEB 20 09:00:00 Y group_1.261.840013679
ONLINELOG MIRROR COARSE FEB 20 09:00:00 Y group_2.262.805381695
ONLINELOG MIRROR COARSE FEB 20 09:00:00 Y group_3.263.805381707

Note:- +DATA/orcl/onlinelog/group_1.261.805381679 Log file is not available on physically. 

SQL> alter database clear unarchived logfile group 1;
SQL> alter database open;









Opatch Inventory curropted error "LsInventorySession failed: Unable to create patchObject"


Error:
=====
opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/optch2017-08-30_17-34-07PM_1.log



Inventory load failed... OPatch cannot load inventory for the given Oracle Home

LsInventorySession failed: Unable to create patchObject
Possible causes are:
   ORACLE_HOME/inventory/oneoffs/23054359 is corrupted. PatchObject constructor Input file "/u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/23054359/ec/config/actions" or "/u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/2054359/etc/config/inventory" does not exist.


OPatch failed with error code 73



Cause:
======
Patch construction input file not exists.



Inventory load failed... OPatch cannot load inventory for the given Oracle Home.

LsInventorySession failed: Unable to create patchObject
Possible causes are:
   ORACLE_HOME/inventory/oneoffs/22502456 is corrupted. PatchObject constructor: Input file "/u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/22502456/etc/config/actions" or "/u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/22502456/etc/config/inventory" does not exist.

Locker::release()
OUISessionManager::unRegister()
Un-Registering the caller : OPatch
==> OUIInventorySession::endSession(): Process ID: 13540. Thread ID: 1

OPatch failed with error code 73

Non-RAC or RAC Solution: 
=========================
Backup the existing central inventory.

2. Detach all the existing Oracle Homes from central inventory.

cd $ORACLE_HOME/oui/bin
./runInstaller -silent -ignoreSysPrereqs -detachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/db_1" ORACLE_HOME_NAME="OraDb11g_home2"
If this operation is successful,

3. Attach all existing Oracle Homes again to the default central inventory.

 cd $ORACLE_HOME/oui/bin
./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/db_1" ORACLE_HOME_NAME="OraDb11g_home2"



Other Possible RAC Solution:
=====================
copy the affected folder from the other node then the issue will get resolved.

Error:
====
opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/optch2017-08-30_17-34-07PM_1.log

Inventory load failed... OPatch cannot load inventory for the given Oracle Home

LsInventorySession failed: Unable to create patchObject
Possible causes are:
   ORACLE_HOME/inventory/oneoffs/23054359 is corrupted. PatchObject constructor Input file "/u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/23054359/ec/config/actions" or "/u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/2054359/etc/config/inventory" does not exist.


OPatch failed with error code 73



Copy the File:
==========
Second node:
cd /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/

scp -rp 23054359/ oracle@xxxxxxxxxxx:/u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/


Check the Inventory again:
====================
opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/optch2017-08-30_17-41-09PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoologs/opatch/lsinv/lsinventory2017-08-30_17-41-09PM.txt

-------------------------------------------------------------------------------
Local Machine Information::
Hostname: xxxxxxxxxxxx
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.


Interim patches (4) :

Patch  25869727     : applied on Wed Aug 30 15:50:33 EDT 2017
Unique Patch ID:  21364644
Patch description:  "Database Patch Set Update : 11.2.0.4.170718 (25869727)"
   Created on 21 Jun 2017, 01:40:19 hrs PST8PDT

Wednesday, August 30, 2017

CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.DATA.dg' is encountered when issuing 'srvctl stop asm -n <nodename>'

Error:
srvctl stop asm -n <nodename>

CAUSE :
The OCR is located on ASM and CRSD is client of ASM thus ASM can't be shutdown

APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1.0 and later

SOLUTION:
One way to remedy this error is to stop Grid Infrastructure / Clusterware on the local node, then restart.  The following command can / should be used to stop Oracle Clusterware as root user.

$GI_HOME/bin/crsctl stop crs

Note that 'crsctl stop crs' acts on the local node only.

REFERENCE:

CRS-2529: Unable To Act On 'Ora.Asm' Because That Would Require Stopping Or Relocating 'Ora.Data.Dg' (Doc ID 1446330.1)
ORA-02449: unique/primary keys in table referenced by foreign keys when drop tablespace
Error:

SQL> DROP TABLESPACE <TABLESPACE_NAME> INCLUDING CONTENTS AND DATAFILES;

CAUSE :
The tablespace still contain dependencies objects (constraints, indexes)

APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later

SOLUTION:
Check the dependencies objects:
SQL> select owner, constraint_name,table_name,index_owner,index_name from dba_constraints where (index_owner,index_name) in (select owner,index_name from dba_indexes where tablespace_name='YOUR_TABLESPACE IN UPPER CASE');
Then follow anyone of the below sql statement:

SQL> ALTER TABLE <table_name> DISABLE CONSTRAINT < constraint_name>;

OR

SQL> Drop tablespace <tablespace_name> including contents and datafiles cascade constraints;

REFERENCE:
ORA-02449: unique/primary keys in table referenced by foreign keys when drop tablespace (Doc ID 1920371.1)

To change Toad's display font to Japanese




1. In Toad, select View -> Option -> Data Grids - Visual -> Fonts Grid.
2. Select "MS P Gothic", which is the Japanese Windows default, or one with Unicode in font name such as 'Arial Unicode MS'.
3. Set script to 'Japanese' in lower right corner of font dialog.


To test to see if you can see Japanese string in sqlplus:

lookup('^PAYM^10:全額振込^^1^0^|','941711');

JAPANESE CHARACTER IN WINDOWS DOS PROMPT / SQLPLUS CLIENT.




The following steps will enable Japanese Language.
The back end Database being used is a UTF8 character set Database.



Control Panel --> Religion and Language --> Administrative -->change system locate

Select Japanese on it and then reboot the system.









2. Click on windows start button and right click on computer .




3.In that select  Advanced system settings.



4. In System properties --->select Advanced tab --->  select environment variables.



Click at bottom New button and add the language as NLS_LANG= American_America.JA16SJIS


Click ok button

Now open the command prompt and right click and select properties.




In that properties  select the FONT tab and Raster Fonts and size is to be 8x18.



Now Japanese character has inserted.