Wednesday, September 30, 2015

ORA-39213: Metadata processing is not available

Issue: Export: Release 11.2.0.3.0 - Production on Wed Sep 30 19:44:23 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39213: Metadata processing is not available



Solution:

Verify if all the components in the database are valid.

SQL> SELECT comp_id, version, status FROM dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ -----------
OWB                            11.2.0.1.0                     VALID
APEX                           3.2.1.00.10                    VALID
EM                             11.2.0.3.0                     VALID
AMD                            11.2.0.3.0                     VALID
SDO                            11.2.0.3.0                     VALID
ORDIM                          11.2.0.3.0                     VALID
XDB                            11.2.0.3.0                     VALID
CONTEXT                        11.2.0.3.0                     VALID
EXF                            11.2.0.3.0                     VALID
RUL                            11.2.0.3.0                     VALID
OWM                            11.2.0.3.0                     VALID
CATALOG                        11.2.0.3.0                     VALID
CATPROC                        11.2.0.3.0                     VALID
JAVAVM                         11.2.0.3.0                     VALID
XML                            11.2.0.3.0                     VALID
CATJAVA                        11.2.0.3.0                     VALID
APS                            11.2.0.3.0                     VALID
XOQ                            11.2.0.3.0                     VALID

Run the below statement and retest the issue.

SQL> execute dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

SQL> exit

 expdp parfile=expdp_cltp_tables.par

Export: Release 11.2.0.3.0 - Production on Wed Sep 30 19:47:32 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."CLTB_TABLES_EXP":  /******** AS SYSDBA parfile=expdp_cltp_tables.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.375 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "GFCPROD"."CLTB_EVENT_ENTRIES":"SYS_P369"   1.278 MB    4788 rows
. . exported "GFCPROD"."CLTP_ACCOUNT_SCHEDULES":"SYS_P378"  327.8 KB    2383 rows
. . exported "GFCPROD"."CLTB_ACCOUNT_APPS_MASTER":"FUNDED"  296.3 KB     281 rows
. . exported "GFCPROD"."CLTP_ACCOUNT_COMP_BALANCES":"SYS_P371"  53.75 KB     785 rows
. . exported "GFCPROD"."CLTP_ACCOUNT_COMP_SCH":"SYS_P374"  63.08 KB     418 rows
. . exported "GFCPROD"."CLTP_ACCOUNT_COMP_SCH":"SYS_P375"  61.03 KB     398 rows

The issue should resolve.

ORA-39065: unexpected master process exception in DISPATCH

Issue while running the export command:

Export: Release 11.2.0.3.0 - Production on Wed Sep 30 19:34:01 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-00600: internal error code, arguments: [kokle_lob2lob13:input mismatch], [1], [], [], [], [], [], [], [], [], [], []

ORA-39097: Data Pump job encountered unexpected error -600


Cause: JOB_QUEUE_PROCESSES was  set to 0

show parameter JOB_QUEUE_PROCESSES

job_queue_processes                  integer
0

Solution: Modified the parameter and restarted the database.

alter system set JOB_QUEUE_PROCESSES=2 scope=spfile;

show parameter JOB_QUEUE_PROCESSES

job_queue_processes                  integer
2

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 4710043648 bytes
Fixed Size                  2236248 bytes
Variable Size            3758096552 bytes
Database Buffers          838860800 bytes
Redo Buffers              110850048 bytes
Database mounted.
Database opened.
SQL> 

Retest the issue it should resolve the expdp error.




Tuesday, September 15, 2015

Delete Histograms on a specific Schema

Step 1: Script to Check Histograms on Schema 

Following script would have to be executed in the schema as the schema owner.


select distinct table_name
from
(
select table_name from user_tab_columns where histogram!='NONE'

)

If the above script return rows, then proceed to run the below step 2.


Step 2: Script to Remove Histograms on a Schema 

Following script would have to be executed in the schema as the schema owner, if there are any rows returned from the step 1.

declare
cursor cur_tables is
select distinct table_name
from
(
select table_name from user_tab_columns where histogram!='NONE'
);
begin
for rec_tables in cur_tables
loop
dbms_stats.gather_table_stats(ownname=>'<SCHEMA_OWNER>',tabname=>rec_tables.table_name,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',CASCADE=>TRUE,DEGREE=>2,ESTIMATE_PERCENT=>NULL);
end loop;
end; 

Thursday, September 10, 2015

Spool SqlPlus Output to Microsoft Excel Format

SET PAGESIZE 50000
SET FEEDBACK OFF
SET MARKUP HTML ON SPOOL ON
SET NUM 24
SPOOL example.xls

Run the query.

SPOOL OFF
SET MARKUP HTML OFF SPOOL OFF

Example:

SET PAGESIZE 50000
SET FEED OFF MARKUP HTML ON SPOOL ON
SET NUM 24
SPOOL online_accouting.xls

select * from fnd_log_messages where log_sequence between 35644336 and 35644404;

SPOOL OFF
SET MARKUP HTML OFF SPOOL OFF

Thursday, September 3, 2015

Moving Spfile From File-System To ASM

1. Create spfile in ASM "+DATA" disk group :
 
SQL> connect / as sys
SQL> show parameter spfile
 
NAME TYPE VALUE
------- ------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora
 
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora';

SQL> create pfile from spfile
File created.
 
SQL> create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora';
File created.

SQL> exit
 
2. Modify inittestpk1.ora on rac1 and inittestpk2.ora on rac2 files to point to location in ASM
 
[oracle@rac1 dbs]$ echo "SPFILE='+RACDB_DATA/racdb1/spfileracdb1.ora'" > /u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora
[oracle@rac1 dbs]$ ssh rac2 "echo \"SPFILE='+RACDB_DATA/racdb1/spfileracdb1.ora'\" > /u01/app/oracle/product/11.2.0/db_1/dbs/initracdb12.ora"
 
3. Update OCR with new SPFILE location
 
[oracle@rac1 dbs]$ srvctl modify database -d racdb1 -p +RACDB_DATA/racdb1/spfileracdb1.ora 

4. Rename any existing spfiles in $ORACLE_HOME/dbs
5. Restart all instances to switch to new SPFILE 
6. Check New Location For Spfile .