SQLULDR2 is a DBA utility to extract Oracle table's data to flat text file, it's based on OCI with excellent performance. Since most of uses like GUI interface, so I released a GUI version of SQLULDR2, let's call it SQLULDR2 for Windows. With the following user interface.

    DataCopy is a DBA utility to move data between different Oracle databases with middle file generation, it's based on OCI with excellent performance. Since most of uses like GUI interface, so I released a GUI version of DataCopy too, let's call it DataCopy for Windows. With the following user interface.

    Any questions or suggestions, please info me, thanks.

Related Posts

Twitter Me? | Leave New Comment(Current: 3)

Link: http://www.dbatools.net/mytools/sqluldr2-datacopy-gui-version.html

    Supposed that we want to unload a huge table (100gb+) to text file, how to do it quickly? The answer is parallel unloading. I will suggest you perform a manual parallel by split the big table into multiple pieces with different where clause. But now I think the manual parallel is too complex, and it may perform multiple times full table scan, which brings extra IO overhead to the storage system, so I add the native parallel feature.

    In parallel SQLULDR2, we can split huge table into multiple piece by rowid range, each parallel slave just take piece of the data with extra IO overhead to the storage system. You just need to tell the parallel degree and the big table name in the SQL to do the parallel. Supposed that we have the following SQL query.

SELECT E.EMPNO, E.ENAME, D.DNAME
  FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO

    Supposed that the EMP is a huge table, we want to parallize the unload process. I will introduce two new options for parallel unloading.

  split  = table name for automatically parallelization.
  degree  = parallelize data copy degree (2-128).

    We also need to change the SQL query for parallel unloading, you can use two bind variables in the SQL, "MINRID" for the lower rowid range and "MAXRID" for the upper rowid range. So I rewrite the SQL as following.

SELECT E.EMPNO, E.ENAME, D.DNAME
  FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND
  E.ROWID >= :MINRID AND E.ROWID < :MAXRID

    If we want to unload the EMP table only, we just need pass the following options to SQLULDR2 for parallel unloading.

user=scott/tiger
query=select * from emp where rowid >= :minrid and rowid < :maxrid
split=emp
degree=4
file=uldrdata.%p.txt
log=log.%p.log

    The "%p" will be replaced by the parallel slave id (start from 1). With the parallel unloading feature, We can unload data very quickly. In a PC server with local disk only, I can unload data to text file with 32MB per second.

Related Posts

Twitter Me? | Leave New Comment(Current: 4)

Link: http://www.dbatools.net/mytools/parallel-inside-sqluldr2.html

    SQLULDR2 is a fast and flexible Oracle text unload utility, but it did not have a good GUI interface, that's because I am not good at writing Microsoft MFC program to create GUI interface. But many and many users like GUI based utility, so I released the SQLULDR2 Windows developer SDK (sqluldr2sdk.zip), with the core SQLULDR2 API function, you can develop your own Oracle text unload utility.

    There is a demo program in the download package, it's a small C program to login to Oracle as SYS schema and unload some rows to text file.

#include <stdio.h>
#include "sqluldr2.h"

void main()
{
  void *h = NULL;
  SQLULDR2HandleAlloc(&h);
  if (h != NULL)
  {
      SQLULDR2HandleSetAttr(h, "USER=SYS");
      SQLULDR2HandleSetAttr(h, "QUERY=SELECT * FROM TAB");
      SQLULDR2HandleExecute(h);
      SQLULDR2HandleFree(h);
  }
}

    If you are good at GUI programing, or you are interesting in GUI programing, please take the lessio to write a GUI interface for SQLULDR2. All the SQLULDR2 attributes can be list by the following command.

sqluldr2 help=yes

    For the DataCopy utility, I will release the Windows deveoper SDK soon.

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mytools/sqluldr2-windows-sdk-released.html

    We can write a Perl or shell script to start multiple datacopy process with TabSplit utility, now you don't need to write such a Perl or shell script, I have combined the code of TabSplit and DataCopy, we can start multiple datacopy thread on Windows or multiple process on Linux/Unix to parallel data copy between different Oracle databases quickly. It's native parallel support now!

    Just need to specify the parallel degree with "DEGREE" command line option.

datacopy user=..... table1=sql_summary table2=sql_summary2 degree=2

    Table SQL_SUMMARY has ten million rows, let's check out the log output.

        0 rows processed at 2010-04-16 13:35:55.
        0 rows processed at 2010-04-16 13:35:55.
  1000000 rows processed at 2010-04-16 13:36:04.
  1000000 rows processed at 2010-04-16 13:36:04.
  2000000 rows processed at 2010-04-16 13:36:14.
  2000000 rows processed at 2010-04-16 13:36:14.
  3000000 rows processed at 2010-04-16 13:36:24.
  3000000 rows processed at 2010-04-16 13:36:24.
  4000000 rows processed at 2010-04-16 13:36:34.
  4000000 rows processed at 2010-04-16 13:36:34.
  5000000 rows processed at 2010-04-16 13:36:43.
  5000000 rows processed at 2010-04-16 13:36:43.
  5207041 rows processed at 2010-04-16 13:36:45.
  5378556 rows processed at 2010-04-16 13:36:47.

    DataCopy now need to access some dictionary views, we should grant select catalog role to the source user. You can download the new version for performance testing, I hope DataCopy to be a very useful data migration utility for Oracle.

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mytools/parallel-datacopy-version.html

    If we want to process huge tables (few hunderds of gigabytes), it will take long time for a single SQLULDR2 or DataCopy process, single process can makde single CPU exhausted. To speedup the process, we need maually partitioned the huge tables, and start multiple SQLULDR2 or DataCopy processes. But manuall partition is too complex for us, we need automate the partition method.

    The key is how to split huge tables into multiple pieces, if we split it manually, then we can only perform manually parallel process, if we can split it automatically, then we can perform automatically parallel process. I wrote a program (TabSplit) to handle the split work by rowid range.

Usage: tabsplit user=... owner=... table=... [part=...] [degree=...]

    There is a big table (EMP_HIS) on my local database, I need to unload it text file with parallel degree 4 to save time. Let's split it with TabSplit utility.

D:\>tabsplit owner=scott table=emp_his degree=4
ROWID>='AAADAUAAEAAAE0xAAA' AND ROWID < 'AAADAUAAEAAAFUIEAA'
ROWID>='AAADAUAAEAAAFUJAAA' AND ROWID < 'AAADAUAAEAAAHEIEAA'
ROWID>='AAADAUAAEAAAHEJAAA' AND ROWID < 'AAADAUAAEAAAHiIEAA'
ROWID>='AAADAUAAEAAAHiJAAA' AND ROWID < 'AAADAUAAEAAAH8IEAA'

    Now we will check the record count of each piece, and then summarize the total record count.

SQL> select count(*) from scott.emp_his where
  2  ROWID>='AAADAUAAEAAAE0xAAA' AND ROWID < 'AAADAUAAEAAAFUIEAA';

    307200

SQL> select count(*) from scott.emp_his where
  2  ROWID>='AAADAUAAEAAAFUJAAA' AND ROWID < 'AAADAUAAEAAAHEIEAA';

    309421

SQL> select count(*) from scott.emp_his where
  2  ROWID>='AAADAUAAEAAAHEJAAA' AND ROWID < 'AAADAUAAEAAAHiIEAA';

    309606

SQL> select count(*) from scott.emp_his where
  2  ROWID>='AAADAUAAEAAAHiJAAA' AND ROWID < 'AAADAUAAEAAAH8IEAA';

    253469

SQL> select 307200+309421+309606+253469 from DUAL;

                    1179696

    And we query the record count by a full table scan in Oracle.

SQL> select count(*) from scott.emp_his;

  1179696

    We found that the record count matched, it's good news for us. Now we can write packer scripts to start multiple SQLULDR2 or DataCopy processes quickly for huge tables.

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mytools/parallel-sqluldr2-datacopy.html

    Supposed that we have few handerds of data stored in US7ASCII database, and we need to switch the system to an UTF8 database, how could we get the system switched in just few minutes without long period outage window?

    Usually we need to unload the data into flat file form US7ASCII database (with NLS_LANG = .US7ASCII), and then change the NLS_LANG to local language character set, for example ZHS16GBK, and load the text file into UTF8 database.

    It seems hard in the past, but now have can use DataCopy & DataSync to get it done. I added two new command line options into them to support character set conversion.

* charset = character set name of the target database.
* ncharset= national character set name of the target database.

    Both DataCopy & DataSync have two database connections, source connection and target connection. The character set of source connection is controled by the NLS_LANG variable, and the character set of target connection can be controled by the two new command line options now. So we can do the character set conversion without flat file generation, we can do it in one command.

datacopy user1=... user2=... table=... charset=ZHS16GBK ncharset=AL32UTF8

    We can use DataSync to do incremental conversion, and then just take few minutes to swithover the system to new database. If you have these kind of data migration jobs, you should use them to relax your works.

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mytools/us7ascii-utf8-data-migration.html

    I want to introduce a new utility for data replication solution based on materialized view log or user defined triggers, I named it as DataSync, suppose that we want to replicate table (SCOTT.EMP)'s data from DB1 to DB2 with low latency (less than 5s). Take a look at the command line help first.

DataSync: Oracle Data Replication Utility, Release 2.0.1
(c) Copyright Lou Fangxin (AnySQL.net) 2010, all rights reserved.

Usage: datasync keyword=value [,keyword=value,...]

Valid Keywords:
  user    = username/password@tnsname for source and target.
  user1  = username/password@tnsname for source database.
  user2  = username/password@tnsname for target database.
* wait    = wait time in microsecond after each array.
  array  = array fetch size
  long    = maximum size for long, long raw, CLOB, BLOB columns.
* crypt  = encrypt the connection info only, no data copy (YES/NO).
  parfile = read command option from parameter file
  config  = config files.
* dbid    = target database flag column of log table.
* dblist  = all target database flag columns of log table.
* init    = fix message out of sync before replication(YES).
  log    = log file name for screen messages.

Notes:
  datasync user1=scott/tiger user2=scott/tiger config=scott.cfg init=yes

Config:
  Source # Primary Key # Log Table # Target # Conflict # Filler # Where

    Then we will start to do the replication of table (SCOTT.EMP), it's very simple.

    1, Create Materilaized View Log on SCOTT.EMP

CREATE MATERILIZED VIEW LOG ON EMP WITH SEQUENCE, PRIMARY KEY;
CREATE INDEX MLOG$_EMP_IX1 ON MLOG$_EMP (SEQUENCE$$);

    2, Prepare a configuration file for replication, one table per line.

D:\>type scott.cfg
EMP  #EMPNO  #MLOG$_EMP  #EMP

    3, Data initialize between tow databases with DataCopy

datacopy user1=scott/tiger@db1 user2=scott/tiger@db2 table=emp

    4, start data replication daemon with init mode

datasync user1=scott/tiger@db1 user2=scott/tiger@db2 config=scott.cfg

    5, Make DML changes on source database, changes will be replicated.

update emp set comm = nvl(comm,0) + 200;

    Now you have a very light replication tools, you can use it to make read database for you application.

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mytools/datasync-data-replication.html

    We always use Oracle exp and imp utilities to move data from one oracle database to another, but the Oracle imp utility is really slow, because it does not support direct path load, and we must prepare lot's of storage to store the dmp file. Another way is to use database link and CTAS command, but the database server must be accessable in network. Is there a tool to copy data between Oracle databases without file genration and no database link required? Yes, we now have the DataCopy utility.

    It's very easy to use, I don't think we need read a detailed document before we start to use it. Just read the command line help and enjoy it.

DataCopy: Fast Oracle Data Copy (Demo Version), Release 2.0.1
(c) Copyright Lou Fangxin (AnySQL.net) 2010, all rights reserved.

Usage: datacopy keyword=value [,keyword=value,...]

Valid Keywords:
  user    = username/password@tnsname for source and target.
  user1  = username/password@tnsname for source database.
  user2  = username/password@tnsname for target database.
  table  = table name for both source and target.
  tables  = table name list for both source and target.
  table1  = source table name to query data from.
  table2  = target table name to insert data into.
  query1  = select SQL for source database.
  query2  = insert SQL for target database.
* wait    = wait time in microsecond after each array.
  read    = set DB_FILE_MULTIBLOCK_READ_COUNT at session level
  sort    = set SORT_AREA_SIZE at session level (UNIT:MB)
  hash    = set HASH_AREA_SIZE at session level (UNIT:MB)
  serial  = set _serial_direct_read to TRUE at session level
  array  = array fetch size
  rows    = print log information for every given rows.
  long    = maximum size for long, long raw, CLOB, BLOB columns.
* crypt  = encrypt the connection info only, no data copy (YES/NO).
  parfile = read command option from parameter file
* direct  = direct mode (YES/NO).
* nolog  = no archive log for direct mode (YES/NO).
* parallel= allow parallel load for direct mode (YES/NO).
* sync    = sync mode (INSERT,UPDATE,DELETE,UPDINS,INSUPD,DELINS).
* unique  = primary key or unique key columns of target table.
* conflict= conflict columns for update on target table.
  log    = log file name for screen messages.

Notes:
  datacopy user1=scott/tiger user2=scott/tiger table=emp
  datacopy user1=scott/tiger user2=scott/tiger table=emp direct=yes
  datacopy user1=scott/tiger user2=scott/tiger table1=emp table2=emp_his

    For any question of this utility, add my MSN for online communication.

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mytools/datacopy-utility.html

    Supposed that we are creating a report of the top 3 highest salary employees for every department (check the Oracle demo table : SCOTT.EMP). If the table is stored in Oracle database, we could generate the report with one Oracle SQL query as following.

SELECT * FROM (
SELECT DEPTNO, EMPNO, ENAME, SAL,
  RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RNK
FROM EMP ) WHERE RNK <= 3

    But if we store this employee table in MySQL or SQLLite database, generating the top n salary employee report will be very complex or difficult. It's hard for us to know different SQL syntax on different database systems. With the feature of the WebChart, you can generate the report with very simple SQL query.

webchart.query_1=select deptno, empno, ename, sal from emp
webchart.express_1=rank|x|rnk::sal|deptno
webchart.filter_1=3.5-x|rank
webchart.sort_1=deptno,rank
webchart.group_1=1

    If the result of filter express for specific row is negative, the specific row is removed from the result set. For example, if the salary rank is large than 3, the result will be negative number, and the employee record is removed by the program, and just keep the top 3 employees for every department as we required, and we will get a result page as following.

deptno	empno	ename	sal	rank
10	7839	KING	5000.0	1
	7782	CLARK	2450.0	2
	7934	MILLER	1300.0	3
20	7788	SSCOTT	3000.0	1
	7902	FORD	3000.0	2
	7566	JONESS	2975.0	3
30	7698	BLAKE	2850.0	1
	7499	ALLEN	1600.0	2
	7844	TURNER	1500.0	3

    By doing the computation at application side, we make the SQL very simple. If the report is accessed very frequently, we also reduce the load of database server.

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mytools/webchart-rows-filter.html

    Some people like to run WebChart demo application on Oracle database, while some other people may like to run WebChart demo application on MySQL database. Seems I have to write two WebChart demo applications, one for Oracle, another one for MySQL database.

    I think I should not ask customer to use Oracle or MySQL, WebChart should support almost all major different database systems. It's possible now to define different SQLs for different databases, for example, we define the following SQLs as the data source of one WebChart page.

WEBCHART.QUERY_1=*

WEBCHART.QUERY_ORACLE_1=select
      to_char(trade_month,'yyyy/mm') month ,
      trade_count count
  from trade_monthly_summary
  where to_char(trade_month,'yyyy')='2008'
 
WEBCHART.QUERY_MYSQL_1=select
      trade_month,
      trade_count+0 as count
  from trade_summary_monthly
  where year(trade_month) = 2008

    When connect to Oracle, it will run the SQL defined for the Oracle database, when connect to MySQL, it will run the SQL defined for the MySQL database. It's possible to create one demo application to work fine for both Oracle and MySQL, and other different types of databases.

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mytools/webchart-multidb-support.html

    Usually our application are bound to one physical database, for example, we always read our data from specific Oracle database or MySQL database, or write business data to specific Oracle database or MySQL database. The physical database is not transparent to the application, many application developers know which physical database the data stores. But that's not so good, we'd better introduce a logical database layer between application and the pnysical database.

    We create some logical database name, and in the application code, we just use the logical database name, then we can intoduce a relation map between the logical database name and physical database, while the appliction doesn't care about the physical database location.

LOGICAL.logical database name=relation type|data source list

    In the WebChart utility, I intorudced 5 types of logical relation between the application and physical database, we may call it logical relation. The first type is equal (FIRST), always choose the first data source.

LOGICAL.WRITEDB=FIRST|masterdb

    The second type is random (RANDOM), choose one data source from the data source list randomly.

LOGICAL.SLAVEDB=RANDOM|slavedb1, slavedb2, slavedb3

    The third type is sequential (FAILOVER), if the first is unavailable (markdown), then get the next data source. We want our application read data from slave first, if no slave available then read data from master database.

LOGICAL.READDB=FAILOVER|slavedb, writedb

    The forth type is get data source by position value, the application will get the data source by a specific hash value, and we get the data source by mod it with available data source count. If we provide a hash value 5, then we will get the get the slavedfb3 connection (5%3=2).

LOGICAL.SLAVEDB=POSITION|slavedb1, slavedb2, slavedb3

    The last type is get data source by a range value, the application will get the data source by a value range. If we provide a range value 150, we will get slavedb2 connection.

LOGICAL.SLAVEDB=RANGE|slavedb1, slavedb2, slavedb3
LOGICAL.SLAVEDB.VALUES=100,200,300

    I found it may be valuable to make the application transparent to the physical databases. How do you think about it?

Related Posts

Twitter Me? | Leave New Comment(Current: 2)

Link: http://www.dbatools.net/experience/logical-database-access-layer.html

    It has been 5 years since I released the first version of AUL utility, and I have helped lots of customers to get their data back from corrupted database, such as lost system tablespace, table dropped or truncated. But every time AUL started, you were required to input a new license code, which I called it "dynamic license model", it becomes obsolete now.

    On windows platform, AUL will not change the register code unless you restall the windows, or move it to another host. So you can buy one real AUL license to recover your data at any time, for any Oracle databased. The fixed license model is comming for you.

    For example, every time I start the AUL utility, it will generate the same register code.

Register Code: DETO-NODT-JETT-DNMX-DDCN
AUL : AnySQL UnLoader(MyDUL) for Oracle 8/8i/9i/10g/11g, release 5.1.1

(C) Copyright Lou Fangxin 2005-2010 (AnySQL.net), all rights reserved.

AUL>

    AUL does not bind it to a physical host, it may change the register code after you reinstall or upgrade the windows version, or replace the disks etc. The newer fixed license mode will give more benifit to AUL customers.

    After all, now you can get a real AUL license, not just a service.

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mydul/fixed-aul-license-is-available.html

    More and more people are moving non-important data out of Oracle to MySQL for lower Oracle license fee. For DBAs, we need to know how to migrate data from Oracle to MySQL quickly. I will introduce you how to migrate data with free SQLULDR2 utility.

    We can export Oracle data to text file, then load it with MySQL "LOAD DATA" command, SQLULDR2 can escape the special character in the column value for MySQL data load. Just run the following SQLULDR2 comman.

sqluldr2 ... escape=0x5c quote=0x22 null=0x5cN field=0x2c record=0x0a

    Then you can load the data in MySQL with following SQL command.

LOAD DATA LOCAL INFILE '...'  INTO TABLE ...
  FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '\"'
  ESCAPED BY '\\' LINES TERMINATED BY '\n';

    We can also export Oracle data to insert SQLs files, and then run it in MySQL. SQLULDR2 can escape the special character in the column value for MySQL SQL syntax. Just run the following SQLULDR2 comman.

sqluldr2 ... escape=0x5c quote=0x27 null=null field=0x2c format=mysql table=table_name

    Then you can run insert SQLs in MySQL as following.

mysql> source insert_sql_files
$ sqluldr2 ... | mysql database_name

    We are planing to migrate one billion rows from Oracle to MySQL with SQLULDR2. If you have data migration jobs to do, try SQLULDR2, it will save you!

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mytools/sqluldr2-oracle-migrate-data-to-mysql.html

    Some departments need to extract rows from the production daily for busines analyze, so we decide to open the standby in read only mode for data extracting with database link. The database link can protect the database user password information well. But now they change the data analyze platform, database link does not work anymore, they need to extract the rows into flat files for further processing with greenpalm.

    SQLULDR2 is a good tool for extracting rows to flat files for Oracle database. But the security team ask DBAs to well protect the database user password, which required DBAs can only give them encrypted password string. So I add the connection inforamtion encryption feature to SQLULDR2.

    By default, SQLULDR2 does not think that the connection information is encrypted, we just put the connection information into the parameter file.

user=scott/tiger@//localhost:1521/db10g
query=select * from emp

    But now we need to protect the connection information by encrypt them in SQLULDR2 with the crypt command line option.

D:\>sqluldr2 user=scott/tiger@//localhost:1521/db10g crypt=create
4899919fa603950b53e639d80245beae8b5d8bb7437bf79e92a473d60377e269
499ee76090faa97f7f043eeae19ffa5445ac5e9d89921dce7f043eeae19ffa54

    Then we put the encrypted connection information into the parameter file, and set the crypt option to "ON".

crypt=on
user=concat the two encrypted lines here
query=select * from emp

    Then we just call SQLULDR2 with this parameter file.

SQLULDR2 parfile=testpar.txt

    You will see that the rows are correctly extracted without providing the real database user name and password, and even the database host information, which maximumly improve the security.

Related Posts

Twitter Me? | Leave New Comment(Current: 1)

Link: http://www.dbatools.net/mytools/sqluldr2-password-protection.html

    We start to introduce MySQL and PostgreSQL into our business system, as a replacement of Oracle, to save the license cost. First we need to move some data from Oracle to MySQL or PostgreSQL for performance testing or business testing. It's not easy work for a Linux & UNIX DBAs to support different types of database systems.

    Perl is very powerful script language for DBAs. I wrote the asyncdata script with Perl, it support different types of databases well, but asyncdata is not good for one time data copy, so we still need a script to copy static data from one database to another database, so I write a new script (copydata.pl) according the source code of asyncdata.

    The table structure of the source table and target table should be the same, the column name must be exactly matched, however the target table can have more columns than source table. But the table name can be different, just put the source table name and target table name into a configuration file.

SOURCE_TABLE1 # TARGET_TABLE1
SOURCE_TABLE2 # TARGET_TABLE3
......

    And them run the copydata script to copy the data, as following.

$ copydata.pl -s test#test#Oracle:test1 -t test#test#Oracle:test2 -c test.cfg
08/10 19:51:19 - Replication started, 2 tables in configuration file.
......

    Of cause, you need to insetall Perl and Perl DBI before you start to use it. Perl is my primary script language for database management tools now.

Related Posts

Twitter Me? | Leave New Comment(Current: 0)

Link: http://www.dbatools.net/mytools/perl-copy-data-scripts.html