rkorclappsdbastuff’s: September 2015

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

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

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

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 .