Exception :: ORA-00904: "NEED_BAL_FLAG": invalid identifier

Issue: Issue occurred while running the 9651687 fix script.

SQL> @xla_mulent_fix.sql;
Exception occured :: ORA-20001:
DECLARE
*
ERROR at line 1:
ORA-20001:
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at line 468

Log Details

Exception :: ORA-00904: "NEED_BAL_FLAG": invalid identifier
in side Print1
Exception occured When Creating xla_ae_headers Backup Table :: ORA-20001:
Exception occured :: ORA-20001: 

Cause:

The backup table is not having the column NEED_BAL_FLAG

Solution:

ALTER TABLE XAH_9651687_BKP ADD(NEED_BAL_FLAG VARCHAR2(1));


SQL> select column_name from ALL_TAB_COLUMNS where table_name='XAH_9651687_BKP';

COLUMN_NAME
------------------------------
NEED_BAL_FLAG
CLOSE_ACCT_SEQ_VERSION_ID
COMPLETION_ACCT_SEQ_VALUE


.....

If the column appears in the output then run the fix script it should complete now.

SQL> @xla_mulent_fix.sql;
-------------------------------------------------------------------------------
Please Note the Following information. Kindly open the Out file to proceed
further
Out file Location would be  /usr/tmp/9651687-fix-15:23:56.html
Log file Location would be  /usr/tmp/9651687-fix.log
-------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL> exit

You have insufficient privileges for the current operation. Please contact your System Administrator

Issue: "You have insufficient privileges for the current operation. Please contact your System Administrator" ,the URL changed from https (SSL) to http (non-SSL) . 

Cause: The issue occurred after configuring the SSL on R12 applications, this caused in the WORKFLOW notification approvals, whenever they click the approve button in the notification the issue occurred,

-in the $CONTEXT_FILE we did not enable the flag for ssl terminated environments (ie. SSL is enabled on the load balancer/SSL accelerator): 
<sslterminator oa_var="s_enable_sslterminator">#</sslterminator> 


Solution:

As per the document for SSL : 

Enabling SSL or TLS in Oracle E-Business Suite Release 12 
( Doc ID 376700.1 ) --------->Section 3--->step 8->table "Changes when using an SSL Accelerator" 

1. Check the httpd.conf to confirm that ssl_terminator.conf is included as this is required for SSL Accelerators. 

grep ssl_terminator.conf $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf 

2. Check that the "s_enable_sslterminator" autoconfig parameter is not disable with #. 

grep s_enable_sslterminator $INST_TOP/appl/admin/$CONTEXT_FILE 

If this shows # as value, it must be removed and Autoconfig must be run to update the httpd.conf to include the ssl_terminator.conf. 

3. start the applications so the changes to the httpd.conf is seen by the web server. 

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.

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; 

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

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 .