DBA Tools |
|
Good tools make the work easy and improve life quality. |
|
Tips: Add to Google | Oracle DUL? | AUL License | AnySQL.net | asyncdata | ociuldr
Contact: anysql©yahoo.com/anysql©126.com, MSN: loufangxin©msn.com, AIM: loufangxin
Recover the table's index structure information.
AUL just focus on the data recovery, it does not generate the SQL of creating the indexes, but we can recover them. Just recover some kernel table's data and load them into new database, then run query to...
Recover the Oracle stored procedures' source code.
AUL just focus on the data recovery, it does not generate the source code of the stored procedures, but we can recover them. Just recover some kernel table's data and load them into new database, then run query...
Linux Kernel Parmaters for Oracle 10g RAC
When installing Oracle RAC or database software, you need to make some changes to the Linux kernel parameters. # For Oracle 10g RAC kernel.sem=250 32000 100 128 kernel.shmmax=1048576000 kernel.shmmni=4096 kernel.shmall=2097152 fs.file-max=65536 net.core.rmem_default=262144 net.core.rmem_max=262144 net.core.wmem_default=262144 net.core.wmem_max=262144 net.ipv4.ip_local_port_range=1024 65000 Just...
Mini VMWare Oracle RAC (3GB) on RedHat Linux
I created a 4GB disk for the first node, choose the minimum installation of OS (RedHat Advanced Server 4 Update 5), and then installed the development packages and X-Window packages. But for the second node, I just created...
Install the basic Linux X-Window for xterm
When installing Oracle RAC on VMWare hosts, I don't want to install any desktop management system (both KDE and GNOME) for Oracle installer or other Java based configuration tools, it will make the VMWare host too big and...
Get Oracle DDL Script
A lot of DBAs are extracting DDL (table create) script with Toad and PL/SQL Developer utilities. But a lot of database are running on Linux or Unix hosts, and connections directly from desktop are not allowed, then it...
Get Oracle space usage information in AnySQL
You can get the table and it's indexes' or lob segments' size by "ORA SIZE" command easily. ASQL> ora size sh.t_lobtest OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_MB INIEXT MAXEXT ----- ------------------------- ------------ ------- ------ ------ SH T_LOBTEST TABLE 0.0625 65536 SH SYS_IL0000012006C00001$$ LOBINDEX 0.0625 65536 SH SYS_LOB0000012006C00002$$ LOBSEGMENT 0.0625 65536...
Drop 4TB table on dictionary managed tablespace
I got a chance to drop a 4TB size table on dictionary managed tablespace (DMT), this table comes from a very old design, and the partition design is not good for data purge, and delete cannot catch up...
How oramon track Oracle performance? -- V$SESSION
When hit database load problems, I always want to know what the active sessions are doing and waiting. On 10g we can get these information from V$SESSION view, on 8i and 9i, we can join V$SESSION and V$SESSION_WAIT....
Fast Oracle text unload speed with ociuldr by Alibaba
Free Oracle text unload utility (ociuldr) was used to extract Oracle data from OLTP system and then load them into their OLAP system by alibaba company (Taobao). They get a really fast unload speed these days. 0 rows...
How to improve the performance of Oracle exp/imp?
A lot of people complain the speed of Oracle exp/imp utility, seems the only thing we can do is to specify large buffer size. Actually there are some other methods to improve the performance a little. For export...
How oramon track Oracle performance? -- V$SYSSTAT
Oracle's system level statistics is very useful for performance track. Oracle keep on add new statistics from Oracle 8, 8i, 9i, 10g to 11g. The key is how to display the statistics value. SQL> SELECT name, value FROM...
How to deinstall and install Oracle XML database (XMLDB/XDB)?
If you are on database release 10.1.x or 10.2.x the XDB Feature is Mandatory in order to use any of the member functions of the XMLTYPE. This is true even if you are not using the repository ,...
AUL/MyDUL data recovery service models
I provide Oracle data recovery service by internet. When I say data recovery, always means cases without valid backup available, and have to use AUL/MyDUL to read data directly from existing data files. There are four types of...
What's Oracle 11G's compress table support for OLTP system?
Before Oracle 11g, the data will not be compressed by normal insert statement even if the table is created with compress option. If you want to compress a tables data, you need to move table as compressed, or...
AUL 5 Beta version launched, all features under testing.
Oracle compress can save a lot of storage for us, so I believe that more and more companys will start to use it. I spent few days free time on it, and add the compress feature support in...
ALTER TABLE ... MODIFY DEFAULT ATTRIBUTES ...
When doing transportable tablespace, we get the following errors when run the TTS set check procedure. SQL> EXECUTE dbms_tts.transport_set_check('USERS', TRUE, TRUE); PL/SQL procedure successfully completed. VIOLATIONS -------------------------------------------------------------------- Default Partition (Table) Tablespace USERS for ... not contained ... Default...
Oracle Compress Table Block Format (3)
The compress block format seems not so much complex as I expected. After spent few hours, I can get the same dump information from AUL as dump datafile command do. Left side comes from Oracle dump datafile command,...
Source code of Oracle AUL/MyDUL, wroten in Java, but not jDUL copy
Few days ago, somebody (they wrote a MYjDUL utility) post a message on a public forum said that AUL/MyDUL is copied from jDUL open source project. This is not true, when I started to write AUL/MyDUL utility in...
Oracle Compress Table Block Format (2)
In previous test, I just create table with two columns, how about for a table with a lot of columns? The basic block format does not change, the key is how to extract the rows. I get one...
Oracle Compress Table Block Format (1)
Oracle compress table is an new feature since Oracle 9i, it could save you a lot of storage, I have seen this new feature used in data warehouse. In oracle 9i, compress table have some bugs, for example...
Poor SUN CPU speed for Oracle compress table
We are thinking to use Oracle compress table for data archiving via TTS, by creating a single table for every month, and then moving it to a cheaper storage. Since the history data will not be modified any...
When lossing one of the data files for a tablespace
I saw two case recently about losing one of the data files for a tablespace, first case was losing a data file totally, second case is one of the data files has no recent backup, so it's equal...
Compare index structure difference between tables
I will introduce you how to compare index structure between tables with the compare any column utility (Overview / Download). I fixed the table structure difference found in previous case, and create the following indexes for demo. CREATE...
The Oracle data recovery without system is really complex.
A few databases got corrupted recently. Such as, disk holding the system tablespace data files got crashed, tables got dropped by mistakes, schema got dropped by mistakes, tablespace got dropped by mistakes (data files still exist). These are...
Compare column for different tables on different databases.
I will introduce you how to compare structure for different tables in different databases (ALL type) with the compare any column utility (Overview / Download). I create the following tables in three different databases for this demo. --...
Compare column for the same table on different databases.
I will introduce you how to compare structure for the same table in different database (TABLE type) with the compare any column utility (Overview / Download). I create the "SALES" table in three different databases for this demo....
Compare column for different tables on the same database.
I will introduce you how to compare structure for different tables in the same database (HOST type) with the compare any column utility (Overview / Download). I create the three tables in SH schema for this demo. create...
Fastest Oracle data recovery service by AUL/MyDUL utility
I provided an Oracle data recovery service for a US company, it took just one and half hours to get 8GB data recovered, from get in touch with me through MSN to finish the data recovery. Before they...
Track Oracle database performance with oramon utility
I am writing an Oracle performance tracking utility -- oramon. And I have finished the first version of it, to make it more powerful, I compiled a windows version binary for free download. What this utility do is...
Compare column script for tables on different databases.
The ocidiff utility is just a peer-to-peer compare utility, it cannot get all of our problem resolved. For some big tables, for example the sales or the bill invoice tables, we may store the data in separate tables,...
How my DBDiff (ocidiff) utility works for different objects?
ocidiff utility is one of my own version of DBDiff utilities. There are several key tasks in this utility, let's see how it works? How to get the SQLs to create the missing tables? In this...
New command line option for text export utility -- BUFFER
When tuning the performance of Oracle SQL loader (sqlldr), the read size and bind size option are very critical. When ociuldr utility generate the SQL loader control file for you, the default read size and bind size are...
Tuning Oracle SQL performance by rewriting it.
I got a task to run the following two SQLs in 20 site databases, the table is very big, about 100GB size in every database. So it not a easy task. SELECT /*+ FULL(T) PARALLEL(T,4) */ COUNT(*) FROM BIGTABLE...
Performance issue by OR operation because filter at table level.
I create a composite index (DEPTNO and ENAME columns) on table SCOTT.EMP. Then I run the following SQL. SQL> SELECT /*+ first_rows no_expand */ * FROM EMP 2 WHERE deptNO=10 AND (ENAME IS NULL OR ENAME >...
How to perform bulk delete in PL/SQL for better performance?
In Pro*C or OCI, we can use an host array as a bind value to submit array operations, it will reduce the network round trips, user calls and execute count, it can greatly improve the performance. In Java...
How to modify the storage properties of XMLTYPE type?
Few days ago, I told one of my friends that the XMLTYPE column equals to CLOB type, that is not true, it's an object type. Later he ask me how to modify the cache property of XMLTYPE column,...
How to choose the SIZE and HASHKEYS of hash cluster table?
Hash cluster table can get much performance improve for equal access, however proper SIZE and HASHKEYS option is required. In OLTP, we always have some tables, the query use equal condition and with unique or very effective index...
Compute statistics when creating index will analyze table in Oracle 9i
I have a table named "T_OBJECTS" and one index on it named "T_OBJECTS_IX1". They are not analyzed now: TABLE_NAME NUM_ROWS LAST_ANALYZED -------------- ---------- --------------- T_OBJECTS T_OBJECTS_IX1 Now I receive a task to create a second index...
How to handle resource busy (ORA-00054) error in Oracle?
When creating or rebuilding indexes on hot tables, we will use parallel option to make the it quickly, but please remember to disable the parallel after creating or rebuilding, else it will make the relative SQL run in...
Connect to Oracle database without tnsnames.ora configuration?
The following connection format method is supported by 8i/9i/10g: C:\>sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 5 17:40:22 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn anysql/anysql@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=TEST))) Connected. SQL> Yong Huang,...
The steps of Oracle table online redefination
Online redefinition can be used to reduce the database down time in OLTP system. The first step is to check whether we can redefine it. SQL> exec dbms_redefinition.can_redef_table( USER, 'T_DEF1') ; PL/SQL procedure successfully completed. Then we...
How much files can be stored in one directory?
How many files can be stored in on directory? I don't know the answer. But when one directory have a lot of files, it will be very hard to operate, and may slow down the file access speed....
Some LOB recovery related options in AUL/MyDUL utility.
AUL version 4 support LOB data type, including inline LOB, in row LOB and out of row LOB. I need to introduce few new options of AUL, the following two are only related to CLOB data type: ...
How to change the character set of the export dmp file?
I saw somebody were asking how to change the character set id in the Oracle export file. I did once in 1998, we had a export file from a US7ASCII database, and wanted to import it into a...
lslog -- read basic information form archived log file
How to get the first SCN, next SCN, first time, next time of archived log? the first way should be check rows from V$LOG_HISTORY or V$ARCHIVED_LOG, but these view just keep information of few latest archived logs, it...
How does Oracle csscan utility working?
I create the following table in an UTF8 character set Oracle database. create table t_charset(col1 varchar2(10), col2 varchar2(20)); The I insert two rows under different NLS_LANG settings. The first column is the client NLS_LANG setting, the...
How to compile version independent OCI program on Linux/Unix?
OCI is a very effective program interface to access Oracle database, I have wrote several DBA utilities with OCI interface. On Windows the compiled binary can run under Oracle 8i/9i/10g client environment. But on Linux/Unix, it cannot, because...
Analyze or DBMS_STATS, choose one but don't mix them
I am not so familiar with the DBMS_STATS procedure, so I always use the "ANALYZE" command. One day I got the following problem. Let's start a demo, first step is creating demo partitioned table. SQL> CREATE TABLE T_PARTDEMO(...
Oracle Bug : exp-00003 when export tables with LOB columns
When you use old version of exp to export tables with LOB column from Oracle 9.2.0.5 or higher version, you will get an error "EXP-00003 : no storage definition found for segment .....", actually this is an Oracle...
Integrite RMAN data file copy feature into Shell/Perl script
We really like RMAN's data file copy feature, and we like to automate the standby build job with our own script. When we use RMAN script to copy the data file, we got few problems, first is a...
How to compile Oracle Call Interface (OCI) program?
On Linux/Unix we can use gcc to compile OCI source code. Use the following command to compile the text unload utility as 64 bit binary. gcc -m64 -D_LARGEFILE64_SOURCE -D_FILE_OFFSET_BITS=64 -I${ORACLE_HOME}/rdbms/public -I${ORACLE_HOME}/rdbms/demo -L${ORACLE_HOME}/lib -lclntsh -o ociuldr.bin ociuldr.c ...
The LONG RAW data type support of text unload (export) utility
Now you can use ociuldr to move data with LONG RAW type. Just the same as LONG type, the "long=" option define the maximum length ociuldr will read. You may need to set the proper value of this...
How to move Oracle context index to another tablespace?
We cannot use following command to move Oracle context index to another tablespace, because it's a special kind of index. ALTER INDEX ... REBUILD TABLESPACE CTXDATA; You need create new storage setting or modify existing storage...
Convert to Veritas Quick-IO file with shell script.
We moved some Oracle data files from OS file system to Veritas file system, to get the better performance, we need to convert them to Veritas Quick-IO file, then Oracle will treat them as raw devices. $> ls...
How to install or uninstall (remove) Context on Oracle 9i/10g?
The steps of context install on Oracle 10g. 1, create tablespace drsys 2, run @?/ctx/admin/catctx.sql ctxsys drsys temp01 nolock 3, run @?/ctx/admin/defaults/drdefus.sql 4, grant execute on ctxsys.ctx_ddl to public The steps of context uninstall (remove) on...
What does the REPLACE mode of Oracle SQL Loader do?
There is something wrong in my mind about the "REPLACE" mode of Oracle SQL Loader, I have thought that sqlldr will use a merge logic, to replace matched rows if the target table has a primary key. But...
How to specify query option of Oracle export utility.
There is a "query" option in Oracle exp utility, which enables you export filtered rows of a table by providing a where clause with this option. However this option is not widely used, many people do not clearly...
Use MyLOG to fasten the recovery of drop operation
I got some requests of data recovery by drop operation, including drop tables and drop tablespaces and drop schema. For this kind of recovery, the most complex step is to map the table with the data object id...
MyLOG, an offline Oracle log miner to extract the redo SQLs
I spent some time in researching the Oracle log file format. Previous research was done about two years ago for Oracle 9i. Fortunately I did get something for these days research. And wrote a tool called "MyLOG" (8i/9i,10g/11g)...
Operation through Oracle Enterprise Manager (OEM) is dangerous
I heard a bad news from someone that one of his colleagues dropped an Oracle schema by mistake in Oracle Enterprise Manager (OEM), because the OEM seems in hang status, so they did some random mouse clicks or...
Make some changes to the text unload utility -- ociuldr
I made few changes to this free utility, a new option "mode=" is introduced to change the load mode, there are four different modes in sqlldr. INSERT: Load data into empty table, this is the default mode. APPEND:...
Is it dangerous to change the table owner in this way?
SQL Server and Sybase provide a system procedure to do this job, while Oracle hasn't it. And now I receive a task to move few tables (totally 80gb) from one schema to another schema. I tested this using...
Load sybase date type into Oracle -- sqlldr
I get this question from oracle-l groups about how to put "Sep 17 2007 7:00:00:020AM" (values from Sybase database) into an Oracle date column.. After several retries, I am be able to load it now. I create a...
Compare and repair table structure between two schemas
I have been maintaining some QA environments which require all the tables' structure should be same with the core QA environments. So we are required to add column on all the environments when make any table structure changes....
How AUL (MyDUL) recover truncated table's data easily?
I have seems somebody truncated their tables by mistaken several times, because of they connect to wrong database and run the script without careful check. To save data, the first thing they need to do is stop the...
Set the different options value for AUL (MyDUL) utility
To correctly unload data, you need to tell AUL some option values by the "SET" command. Currently 12 options are available. Some are used to tell AUL the datafile format, some to set the output file format, and...
Check your data files with DBVerify utility -- dbv
DBVerify (dbv) is an utility in standard Oracle binary to check whether there is any block corruption in the data file. I use it often in the following case. After building standby, dbv all the files...
sqlldr - Internal error: ulconnect: OCIInitialize [1804]
One of our batch job get the following error when calling sqlldr to load text file into database. % 9205/bin/sqlldr *****/*****@***** control=testsqlldr.ctl data=..... SQL*Loader: Release 9.2.0.5.0 - Production on Tue Aug 7 18:00:35 2007 Copyright (c) 1982, 2002,...
The return (exit) code of ociuldr text unload utility
&n