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: 1)

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

    After define HTML links for each columns, we need to change the old XSL template to show links in the pages. We can use "href" HTML tag to show it, change the XSL template file as following.

<xsl:choose>
        <xsl:when test="@href">
                <a target="_blank">
                <xsl:attribute name="href">
                        <xsl:value-of select="@href" />
                </xsl:attribute>
                <xsl:value-of select="." /></a>
        </xsl:when>
        <xsl:otherwise>                       
                <xsl:value-of select="." /> 
        </xsl:otherwise>
</xsl:choose>

    Or else, use the "onClick" mouse event, use java scripts to open the links, which enable you control some properties of the new window. Change the XSL template file as following.

<xsl:choose>
        <xsl:when test="@href">
                <a href="#myanchor">
                <xsl:attribute name="onClick">
                    javascripts:window.open("<xsl:value-of select="@href" />"); return true;
                </xsl:attribute>
                <xsl:value-of select="." /></a>
        </xsl:when>
        <xsl:otherwise>                       
                <xsl:value-of select="." /> 
        </xsl:otherwise>
</xsl:choose>

    For XML technology, I just know something about this, write a note on blog for future remind.

Related Posts

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

Link: http://www.dbatools.net/mytools/webchart-new-xsl-template.html

    It's possible to get ORA-24345 error when you unload data with SQLULDR2, this error is due to no enough memory buffer allocated for column binding, the column data returned exceed the maximum length declared. After a full code review, there are two code bugs in SQLULDR2, but ociuldr doesn't have this bug.

    For RAW data type, when unloading as text file, it will return hex string, each byte of the data will be returned as two hax char (the ascii code), so the memory buffer of this column type should be doubled. Now you can get ORA-24235 error, if you have RAW values larger than half precision size.

    For number data type, when unloading negative values to text file, we should add one byte for the number value sign char, while it's ommited in current SQLULDR2. Somebody reported this problem when unload values stored in "NUMBER(2)" column, I finally found the root cause.

    The ociuldr, previous release of SQLULDR2, doesn't have this bug, because ociuldr does not support fixed length text format, so for number type, I give 130 bytes memory buffer, for raw type, I give a 4000 bytes memory buffer. But in SQLULDR2, to well support of fixed length text format unload, I try to allocate as less buffer as possible.

    However, if you already hit this bug, you can increase the precision of relative columns, or you can use "width" option to manually set the column width. I will get this bug fixed this week, you can update your binary next week. A new command line option ("safe") added to avoid ORA-24345 error.

    It's possible we do some character set conversion when unloading data, from single byte to multiple bytes character set, the maximum length required may be doubled. If you set "safe" option to yes, SQLULDR2 will and 10 bytes for DATE, NUMBER column type, and double the memory bufer for character column type. So if you plan to do character set convertion, please set "safe" to yes, just call it "run in safe mode".

Related Posts

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

Link: http://www.dbatools.net/mytools/sqluldr2-bug-ora-24345.html

    Somebody need to change the date type format (default, "yyyy-mm-dd hh24:mi:ss"), and change the numeric characters. For Oracle, you can run some "alter session set ..." commands to make the changes, so I add a new command line option "alter" for SQLULDR2, which allow you change session level's setting before the data unloading. You can put mulitple alter session lines in a parameter file, such as "testpar.txt".

user=webchart/webchart@localhost:1521/testdb
query=select sysdate from dual
alter=alter session set nls_date_format='yyyy-mon-dd';
      alter session set nls_date_format='yyyy-mm-dd';

    Then run SQLULDR2 with parameter file "testpar.txt".

sqluldr2 parfile=testpar.txt

    Check the data in the out put file, we can see that the data format takes effect. Be attention, the session level's setting will not change the date format of the SQL*Loader control files.

D:\OracleClient>type uldrdata.txt
2009-06-03

    I am not sure how many people really need this feature, as there are already some command line options for session level performance tuning.

Related Posts

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

Link: http://www.dbatools.net/mytools/sqluldr2-alter-session-params.html

    HTML links are very useful for navigation, when we display some summary data in WebChart, we also want to display a href link to display the detail data of relative quater.

QUATER	COUNT
2008Q1	7363835
2008Q2	9146220
2008Q3	10985911
2008Q4	12757707

    In previous release, we have to create a dedicate XSL file to control the output, but now you can simply add a extra line in the WCML file to tell WebChart how to create navigation links in the data grid. For example, we want to create a href link on column quater, and pass the quater value with HTML url parameter "q" to "detail.wcml" page.

WEBCHART.HREF_1=QUATER|detail.wcml?q=$QUATER

    Now there is a HTML link on column quater for navigation.

QUATER	COUNT
2008Q1	7363835
2008Q2	9146220
2008Q3	10985911
2008Q4	12757707

    With this feature, you can build lot's of pages with fewer XSL template files.

Related Posts

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

Link: http://www.dbatools.net/mytools/webchart-href-links.html

    Now, we have more performance alert point than previously, we just had performance alert based on the load average and active sessions. I were wondering that we will receive too much performance alert and it may bother our lifes. But it doesn't happen, because no alert reported by mistake, it really help us find out new problems on database or application layers.

    The oldest performance alert, came from Nagios based on the load average.

WARNING - load average: 9.03, 5.70, 4.36

    With the older version of oramon, new performance alert added with active sessions.

High Active Session on xxxxxx,
05/21-10:41:01 Load 2.73, Act: 45, Enq: 0

    In the current version of oramon, more performance data is reported in alert message.

High Load on xxxxxx, 2009.05.21 10:41:01
Load 2.73, CPU:7/29/19 Swap: 1, Act: 45, Enq: 29, PQ: 0

    We got some enqueue wait sessions alert, which help us get to know the problems exists in out database and application, so we can get it resolved before it really degrade our availability.

Related Posts

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

Link: http://www.dbatools.net/experience/benifit-from-oramon-perf-alert.html

    For critical online transaction process database, it's very important to find out an effective way to monitor the database performance and alert the database performance problem. Because their are many concurrent sessions, any small problem can make the database run into worse situation, if we did not get it resolved in time, the database may hang, the application may hang, and the sevice lost until we restart the database server or application servers. Some companies hire NOC DBAs to perform 7x24 monitoring, some companies install some performance monitoring tools such as Nagios, to monitor the database by server load or CPU usage etc at every 5 minutes, and send an alert to DBAs in time.

    As a matter of fact, the key point is not that you have 7x24 NOC DBAs, you need a good tool to gather database performance data, and then build some alert rules based on these data. Nagios is not good enough, 5 minutes check interval is not effective, it's hard to build good alert rules based on the data that oramon gathered. The oramon utility can gather key performance data for you, why not make some good alert rules on it, and the get the perfromance monitoring done well?

    I rewrite the data gather tool with Perl, to integrite the performance alert rules and support different RDBMS as the performance database. Because oramon gather performance data at every 10 seconds, so the alert rules is not based one point of data, it's based on the recent 5 points of data, it can improve the alert rules quanlity greatly.

    After one days test, I add the following database performance alert rules to my databases. If the active sessions in the last 5 points are greater than given value (different value for different databases), send out the performance alert. If the enqueue wait sesssions in the last 5 points are greater than 10, send out the performance alert, because I think enqueue wait should be avoided in OLTP systems. If the CPU WIO percent in the last 5 points are greater than 40, send out the performance alert. If the CPU USER percent in the last 5 points are greater than 80, send out the performance alert. If the active parallel slave sessions in the last 5 points are greater than 4, send out the performance alert.

    With the data gathered by oramon, we can add more performance alert rules according to swap in and out, physical read, physical writes, the average db file sequential read time, the IOPS (summary of database read and write event waits) of the database. Don't send performance alert just according to the load average or CPU utilization.

    By embed the performance alert rules into the data scan tool, we can get the performance alert quickly, right after it happens, because it's applying the rules at every 10 seconds. By check the last 5 points of data, it can eliminate lot's of unnecessary performance alerts. Last day, we get some performance alert from the enqueue wait rule, and we get it resolved quickly without impaction of the service. With these effective performance alert rules, DBAs can master the database well, and then can sleep well every day.

Related Posts

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

Link: http://www.dbatools.net/experience/oramon-performance-perf-alert.html

    I was changing the code of oramon in recent few weeks, keeping on adding more useful performance data and removing useless performance data, it took me one month to finally release the new version of oramon, and deploy it to our production databases. The most important thing of new version is that we can load more performance data into performance database, and we can show it easily with WebChart. Here is a few charts from the data gathered by the new version.

    The CPU utilization, including SYS, WIO, USER percent value, and display the data of all the core database hosts into one page for comparation.

    The total wait count of db file sequential read and db file scatter read event, and display the data of all the core database hosts into one page for comparation.

    The total wait time of db file sequential read and db file scatter read event, and display the data of all the core database hosts into one page for comparation.

    The total wait count of db file parallel write and log file parallel write event, and display the data of all the core database hosts into one page for comparation.

    The total wait time of db file parallel write and log file parallel write event, and display the data of all the core database hosts into one page for comparation.

    The average wait time of db file sequential read event, an important measure of the IO response time of the storage, and display the data of all the core database hosts into one page for comparation.

    The total wait count of database read and write wait event, include dba file sequential read, db file scatter read, db file parallel write and log file parallel write event, we get an IOPS from Oracle database layer, and display the data of all the core database hosts into one page for comparation.

    Oramon gathered more data than the above, will draw more charts for better performance analyze.

Related Posts

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

Link: http://www.dbatools.net/experience/deploy-new-oramon-version.html

    Oracle give use lots of performance views, not only for database only, but also some important data about the applications. As a DBA we should analyze those data, and show the data to others. For example, how to configure the database connection pool in application, the minimum and the maximum connects?

    It seems DBAs cannot get any data about it, we should analyze the application log and then get the required information . But it's not ture, usually the application use connection pool, so the database connection is permanent to database, we can simply keep a history of the database connections, to see whether it always reachs the configured maximum connection.

    Run the following SQL every 5 minutes, and keep the data into a table for further analyze.

select sysdate day, INSTANCE_NAME,
      machine, username, count(*) conncount
  from v$session, v$instance
  group by machine, username, instance_name

    Then we can display the last three days' data of the application host, it will tell us whether we should change the configuration.

    It's a easy job, but it make us tuning the application connection based on data, not by somebody's personal experience.

Related Posts

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

Link: http://www.dbatools.net/experience/analyze-perf-data-two.html

    Oracle give use lots of performance views, not only for database only, but also some important data about the applications. As a DBA we should analyze those data, and show the data to others. For example, the SQL execute ratio of each application hosts for a database. It may be useful in application capacity plaing.

    It seems DBAs cannot get any data about it, we should analyze the application log and then get the required data. But it's not ture, usually the application use connection pool, so the database connection is permanent to database, sessions are not reconnected between different calls. So we can get the SQL execute count staistics from V$SESSTAT performance view. Create a user with select catalog role and create a table to hold the data under the created user.

create table TMP_SESSION_EXECUTES
(
    ID NUMBER(38),
    SID NUMBER(12),
    serial# number(12),
    machine varchar2(64),
    username varchar2(30),
    execute number(38)
);

    Create a sequence to generate the snap id for each data collect.

create sequence seq_temp_sesstat;

    Run the following PL/SQL codes for few hours, we will get lots of data to analyze the above problem.

declare
  ver number:=0;
begin
loop
  select seq_temp_sesstat.nextval into ver from dual;
  insert into TMP_SESSION_EXECUTES
  select ver, a.sid,
    a.serial#, a.machine, a.username, b.value
  from v$session a, v$sesstat b
  where a.sid=b.sid
    and b.STATISTIC#=238;
  commit;
  dbms_lock.sleep(300);
end loop;
end;
/

    The data we gathered is cumulative data, we need to get the changed value for each snap, and then do a summary by application machine and schema name.

SELECT MACHINE, USERNAME,  SUM(EXECUTE) EXECUTE
FROM (
SELECT A.MACHINE, A.USERNAME,
  A.EXECUTE - NVL(B.EXECUTE,0) EXECUTE
FROM TMP_SESSION_EXECUTES A, TMP_SESSION_EXECUTES B
WHERE A.ID=B.ID+1
  AND A.SID=B.SID
  AND A.SERIAL#=B.SERIAL#
  AND A.MACHINE=B.MACHINE
  AND A.USERNAME=B.USERNAME
  AND A.EXECUTE > NVL(B.EXECUTE,0) )
GROUP BY MACHINE, USERNAME

    Now we get the SQL executions of each application machine in a given duration, and calculate the ratio of each application machine, it's very useful infromation.

Related Posts

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

Link: http://www.dbatools.net/experience/analyze-perf-data-one.html

    I provide one hour consultant service for somebody, and got 75USD consultant fee by PayPal, PayPal charged me 3.23 USD for this payment, it's really expensive! The fee ratio is 3.23USD/75USD, about 4.3 percent. As the global economic crisis, it really charged too much from all of us.

    In China the maximum charge ratio by bank is 1 percent and up to 100RMB maximumly, for online payment service, such as AliPay, unless you exceed the free limit, you are freely for payment transfer usually. Why don't PayPal give us a free payment transfer limit between different account?

    AliPay is very popular in China, about 150 millions registed users, still growing with very rapidly, more than 100% every year since it created. If you want to develop a new market or expand existing market in China, you can choose AliPay as the payment solution, lot's of Chinese will buy your products due to the easy payment with AliPay. While PayPal is not not popular in China at all due to high charge ratio.

    We'd like to start business with you by a easy payment solution. You can sell your products on Taobao and accept payment by AliPay, just hire somebody who can read Chinese.

Related Posts

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

Link: http://www.dbatools.net/support/paypal-charge-too-much.html