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

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

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

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

    Lot's of DBAs may know the ociuldr utility, to extract rows from Oracle to text file. Now I rewrite ociuldr with OCI 8, and rename it to SQLULDR2 with better performance (almost double) and some good new features, and still free of charge.

    Write the connect and SQL information in a parameter file to improve the security, and tell you the data volume information in the log output, and the batch option now specify a size for each text file if you don't want to generate one big text file. Such as follwoing.

D:\OracleClient>sqluldr2 parfile=testpar.txt batch=100 file=uldrdata_%b.txt
      0 rows exported at 2009-05-05 17:54:59, size 0 MB.
        output file uldrdata_1.txt closed at 327329 rows, size 104 MB.
        output file uldrdata_2.txt closed at 283306 rows, size 204 MB.
  128024 rows exported at 2009-05-05 17:55:18, size 248 MB.
        output file uldrdata_3.txt closed at 128024 rows, size 250 MB.

    Another important thing is that SQLULDR2 can be compile into 64 bit binary file, which ociuldr cannot. so you don't need to create a shell script to wrap the SQLULDR2 utility to use the 32 bit Oracle client library. Somebody told me that they cannot compile ociuldr into 64bit program before, now it's resolved.

    However if you feel SQLULDR2 really give you great help, you can make a donation with paypal.

Related Posts

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

Link: http://www.dbatools.net/mytools/sqluldr2-free-of-charge.html

    It has been four years since AUL help it's first customer from Hongkong to get the data back. In the last four years, AUL does help a lot of custoemrs in disaster data recovery situation without valid backup. I draw a world map with Google chart API to identify out the countries of AUL customers, it looks great.

    The most important is AUL get the data back correctly and quickly than other methods. It's still helping people. For my other tools, the same target with AUL, just try to help people.

Related Posts

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

Link: http://www.dbatools.net/mydul/aul-customer-wold-map.html

    I always get some questions about my utilities via email boxes, due to that I have several email boxes, and some email boxes are not checked frequiently enough, so I may not answer quickly. To improve this situation, I deploy an "Ask Me" channel, you can ask question there, I will check the new topics more frequiently than email boxes, and other people can see what we talked too.

    "Ask Me" is based on phpBB, a famious forum program. However it's not a forum, I don't want a forum system. Lot's of forum features are disabled, to make it just like a question and answer system. No registration is required to post a question (topic) or a answer (reply).

    I am still creating a suitable theme for it, but now the user interface is not so friendly, it still need some time to get it better. The standard features are ready, I am ready for your questions. If you have any questions of the utilities on this site, just "Ask Me" to make them valuable.

Related Posts

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

Link: http://www.dbatools.net/support/post-question-by-askme.html