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
This blog is related with mostly Oracle database , Oracle EBS and other Oracle tools . I will also try to share my experiences about other databases. Thanks for reading this blog and your feedback is appreciated.
Monday, March 17, 2014
Monday, March 3, 2014
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
*+- 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 :
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.
Subscribe to:
Posts (Atom)