utl_file operations on remote directory,network shares

I have a project contains database migration from windows to linux enviroment with database upgrade . For years developers have worked on windows database server and done their utl_file operations on windows file system.After migration they have asked to work on windows file server  as so. Due to new database is on linux system i have asked that remote directory on another server is supported during utl_file operations ?

As a result, utl_file script has to create file on remote windows server from linux. I have used cifs protocol for this issue.

1. On linux new directory is created for required mount point
 
     mkdir -p /remotewin

2. With windows user,password ,domain information new mount point is mounted to related windows directory

     Assume you have directory named "remotelin" on windows server has necessary sharing.

     mount -t cifs //<WINDOWS_SERVER_IP>/remotelin /remotewin -o username= <WIN_USER_NAME>,password=<WIN_PASSWORD>,domain=<WIN_DOMAIN_NAME>,uid=<LINUX_ORACLE_USER_ID>,gid=<LINUX_ORACLE_USER_GROUP_ID>

With this command /remotewin directory on linux enviroment is mounted to windows enviroment with oracle user privilige .

After this you can use DBA_DIRECTORY or utl_file parameter  to create files on windows enviroment.

connect / as sysdba

create or replace Directory OUTBOUND_DIR as '/remotewin';

DECLARE 
  filex UTL_FILE.FILE_TYPE;
BEGIN 
  filex := UTL_FILE.fopen('OUTBOUND_DIR','Testfile.txt','W',32767);    
  UTL_FILE.put_line(filex, 'Hello World');    
  UTL_FILE.fclose(filex);                         
EXCEPTION
  WHEN utl_file.invalid_path THEN           
    dbms_output.put_line('Path not found on DB-server.');       
  WHEN utl_file.invalid_mode THEN           
    dbms_output.put_line('Invalid mode.');       
  WHEN utl_file.invalid_operation THEN           
    dbms_output.put_line('Error when opening file');       
  WHEN utl_file.invalid_filehandle THEN           
    dbms_output.put_line('Invalid filehandle');       
  WHEN OTHERS THEN         
    dbms_output.put_line('Others: '|| SQLERRM); 
END;

Sources:
http://microitblog.com/micro-it-blog/2011/04/21/mounting-cifs-shares-through-linux-with-write-permissions
http://www.cyberciti.biz/tips/how-to-mount-remote-windows-partition-windows-share-under-linux.html
http://www.samba.org/samba/docs/man/manpages-3/mount.cifs.8.html

ERROR OGG-01161 Bad column index () specified for table ., max columns = 62 .

Because of mismatch structure between source and target tables, this error can be taken.In my case i am not synchronizing all columns for specific table named TEST1 . First 62 columns of a table are in  synchronization.There was already changes in the source table and these changes were not propagated to target as well.

After abnormal crash of source database ,Golden Gate immediately gave ERROR OGG-01161 error.

2014-03-02 18:19:50  ERROR   OGG-01161  Oracle GoldenGate Delivery for Oracle, REP1.prm:  Bad column index (62) specified for table TEST.TABLE1, max columns = 62.

2014-03-02 18:19:50  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, REP1.prm:  PROCESS ABENDING.

For solution , I have recreated definiton file for specific table and copied def file content to target def file.I have not created absent columns at target side.Updating definition file with new definition of related table solved my issue.

Source : 

$GG_HOME=/gg

1. Created new definition parameter file for TEST.TABLE1

   ## TEST1.prm file

   DEFSFILE /gg/dirdef/TEST1.def

   USERID gg01, password gg01

   TABLE TEST.TABLE1;

2. Using the defgen utility ,new definition file is created

   /gg/defgen paramfile /gg/dirprm/TEST1.prm

   

   This command will create TEST1.def under /gg/dirdef and will contain following information for table1

   

   #######################################################################
   *+- Defgen version 2.0, Encoding UTF-8

*

*  Definitions created/modified  2014-03-02 19:05

*

*  Field descriptions for each column entry:

*

*     1    Name

*     2    Data Type

*     3    External Length

*     4    Fetch Offset

*     5    Scale

*     6    Level

*     7    Null

*     8    Bump if Odd

*     9    Internal Length

*    10    Binary Length

*    11    Table Length

*    12    Most Significant DT

*    13    Least Significant DT

*    14    High Precision

*    15    Low Precision

*    16    Elementary Item

*    17    Occurs

*    18    Key Column

*    19    Sub Data Type

*

Database type: ORACLE

Character set ID: ISO-8859-9

National character set ID: UTF-16

Locale: neutral

Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14

Definition for table TEST.TABLE1

Record length: 204

Syskey: 0

Columns: 4

C1   64     20        0   0  0 1 0     20     20      0 0 0 0 0 1    0 1 0

C2   64     50       26  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2

C3     0      1       82  0  0 1 0      1      1      0 0 0 0 0 1    0 1 0

C4   64     50       86  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2

End of definition

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

Target :

3. Stopped replicat and manager at target

4. Due to more than 1 table exist in GoldenGate replication , only TEST1 table part of target definition file was replaced with above content between "Definition for table" and "End of definition" snippet.

5. Started manager and replicat.

ASM communication error & ORA-21561 : OID generation failed

I have faced an issue which had following db alert log snippet,

WARNING: ASM communication error: op 0 state 0x0 (15055)
ERROR: direct connection failure with ASM
WARNING: ASM communication error: op 0 state 0x0 (15055)
ERROR: direct connection failure with ASM
WARNING: ASM communication error: op 0 state 0x0 (15055)
ERROR: direct connection failure with ASM
WARNING: ASM communication error: op 0 state 0x0 (15055)
ERROR: direct connection failure with ASM

Firstly I thougt that it was ASM crash issue or anything about ASM instance.I was not able to connect ASM instance with sqlplus / as sysasm and there was no entry in ASM alert log related with this issue,and also ASM pmon process was alive.

But "sqlplus system/<PASS>" gave me the following error,

ORA-21561 : OID generation failed

According to Metalink and Google search , the problem was likely client hosts file."/ect/hosts" file had machine fully qualified  hostname and shortname. But i have realized that for some reason machine hostname has been changed :) 

After changing to true name of host , trouble has gone.

Create Expense Report - Unexpected Error java.lang.NullPointerException

A client declared error which occured when create Expense Report button pressed with following detail,

Exception Details.  
 oracle.apps.fnd.framework.OAException: java.lang.NullPointerException
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:133)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:797)
at oracle.apps.ap.oie.entry.header.webui.HeaderKffCO.renderKeyFlexfield(HeaderKffCO.java:81)
at oracle.apps.ap.oie.entry.header.webui.HeaderKffCO.processRequest(HeaderKffCO.java:41)
....

## Detail 0 ##
java.lang.NullPointerException
at oracle.apps.ap.oie.entry.accounting.server.ExpenseAllocationsAMImpl.getFlexStructureCode(ExpenseAllocationsAMImpl.java:412)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:190)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:133)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:797)
at oracle.apps.ap.oie.entry.header.webui.HeaderKffCO.renderKeyFlexfield(HeaderKffCO.java:81)
at oracle.apps.ap.oie.entry.header.webui.HeaderKffCO.processRequest(HeaderKffCO.java:41)
....

Metalink doc 1244614.1 gives detail about this error.In doc , improper setting of security attributes can cause this issue. Normally securing attributes which are ICX_HR_PERSON_ID and TO_PERSON_ID should have per_all_people_f.person_id value for related employee.In this case i have encountered that these values were different from original value.

For solution,

1.From Security--> User-->Define query user.

2.Remove employee name from Person field

3.Save

4.Add employee name to Person field back

5.Save

6.Re-query user.

7.Observe that ICX_HR_PERSON_ID and TO_PERSON_ID attributes should have employee_id of user

Source : Metalink Doc.Id 1244614.1 

REP-0004,REP-0069 and REP-57054 errors while APXAPRVL program running

I would like to give brief information about REP-57054 , REP-0069, REP-0004 error which occur while invoice validation  -  APXAPRVL concurrent program working .

Error:
REP-0004: Warning: Unable to open user preference file.
REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:


Cause of  reports.log file under $APPLCSF/$APPLLOG reached 2GB size limit is the reason of this error.

For solution,

1.Stop concurrent managers
2.Truncate "reports.log" file
3.Restart concurrent managers
4.Observe the results after submitting related concurrent program

I have taken this solution from metalink . But i did not stop managers . Directly truncate reports.log by ">" command has solved my problem.

Oracle Discoverer 11g host name change issues

I had to clone server which contains Oracle Discoverer 11g for a test purpose.Host name change process was done by examining the Rittman following document 

http://www.rittmanmead.com/2010/12/oracle-bi-ee-11g-managing-host-name-changes/

Successfull change process provided Admin Server and Managed server to run without any problem.

But 2 problems have been seemed ,

1.Port Number In Weblogiccluster Parameter Specified In Httpd.Conf Is Not An Integer Less Than 65535

Of course hostname change is not an easy process on 11g Discoverer which has a lot of components.Although changing hostname in related all files , some others can be missed. This problem occured from same manner.

In ORACLE_INSTANCE/config/OHSComponent/ohs-comp-name/moduleconf/module_disco.conf

<Location /discoverer tag contains WeblogicCluster address maps to disco server name and related port.It must be changed to new hostname

2.Discoverer Components Application URL

From Weblogic Enterprise Manager discoverer components have Application URL mapping to old hostname.Following directory contains configuration.xml stores Discoverer settings.

DOMAIN_HOME/config/fmwconfig/servers/WLS_DISCO/applications/discoverer_discoverer_version/configuration/

Head of configuration.xml related applicaton url can bee seen to change.

Simple DNS Configuration for Oracle RAC

While installing RAC on virtualBox guests (linux), related docs declare that SCAN addresses should not be defined in the hosts file.Beacuse of round-robin resolution cannot be simulated using a local host file ,The Single Client Access Name (SCAN) should be defined in the DNS  and round-robin between one of 3 addresses , which are on the same subnet as the public and virtual IPs.

I am using virtualbox for testing purposes on various guests, and sometimes DNS server is needed.Not to struggle with complex DNS configuration , Dnsmasq  is the best solution.Following steps are used to install,configure,start Dnsmasq service.

On seperate linux guest , Dnsmasq  is installed from yum 

# yum install dnsmasq 

Start Dnsmasq  service 

# service dnsmasq  start

To start Dnsmasq  service automatically on reboot 

# chkconfig dnsmasq on

Dnsmasq  will use entries of the "/etc/hosts" to resolve.

"/etc/hosts" file contains following entries on the server running Dnsmasq  service provide acting live DNS server by resolving these entries.

192.168.0.91 testracclusterscan.localdomain testracclusterscan
192.168.0.92 testracclusterscan.localdomain testracclusterscan

192.168.0.93 testracclusterscan.localdomain testracclusterscan

192.168.0.71 node1.localdomain node1

192.168.0.72 node2.localdomain node2

Guests which will use DNS server for name resolution , need to have "/etc/resolve.conf" file configured DNS server

nameserver 192.168.0.10
search localdomain

Detail information about SCAN:
http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf

source : http://www.oracle-base.com/articles/linux/dnsmasq-for-simple-dns-configurations.php