Load2Test by DBA InfoPower is a the best for MySQL load testing - they can record actual MySQL production traffic and replay it in staging multiple ways, like "as is", "load parallelization", "think time reduction", etc and generate performance issues root cause analysis on a spot.

I used it with number of my customers and it takes no time to do load testing and get analytics and root cause analysis back (like gathering slow-log data without any impact on a system and more), compare runs, etc

They are small startup that operates "under the radar". I got introduced to this product through the person who works there - and I really like the tool!!!

Load2Test official page - load2test.com

So Oracle is to buy Sun now. Well, that makes sense. I guess I jumped ship just in time then. People started congratulating me yesterday that I avoided being an Oracle employee. :-) Although I was de facto a MySQL employee for a while; not sure which is worse.

John Dvorak, possibly of of the most tracked-back men these days, apparently knew all along that the acquisition of MySQL by Sun was just a proxy deal for Oracle. Now with IBM nearly snatching up Sun, I guess Oracle had to react.

The question inevitably arose, what will this mean for PostgreSQL. Probably not much. For one thing, you'd be overestimating the impact that Sun has on anything these days. ;-) The acquisition of MySQL by Sun eventually led to the (very small) PostgreSQL business at Sun being phased out, and it is possible that whatever is left will now fade away even more. But it really hasn't impacted the PostgreSQL community much, and won't do so now.

On the contrary, open-source databases, both MySQL and PostgreSQL, have always (at least since they became credible) been considered the anti-Oracle proposition. Now with MySQL actually owned by Oracle, this makes PostgreSQL the primary alternative. Considering the sometimes irrational but not insignificant ill-will that the Sun acquisition has spread among open-source database enthusiasts, it is easy to imagine how even worse this will impact MySQL's reputation on perception alone.

One might of course also think that Oracle now has a devilish plan to corner the database market from both sides. But it would be too presumptuous to assume that Oracle spends $7 billion on an anti-PostgreSQL plan. In some way, this basically only continues the path that Oracle started on when it bought Sleepycat and InnoDB a few years ago, and the impact of that on the open-source database field was pretty minimal.

I don't expect that MySQL will be "killed" either. It is too valuable as a "foot in the door" in any case. Moreover, much of the MySQL momentum already lies outside of Sun anyway, in the hands of Percona, Open Query, the Monty Program, Drizzle, and others, so killing MySQL is already impossible for a single company. Which is probably a good situation for the extended open-source database community.

What about the rest of Sun? I assume Oracle will be quite interested in Solaris and Java, of course, also Open Storage, Glassfish, and some of the other pieces of middleware that no one outside Sun really knows (yay, Sun marketing). Some of these pieces nicely fill the gaps in Oracle's offerings, reduce the interdependencies of Oracle with IBM and/or solidify the already existing relationships with Sun. The cloud computing initiatives might also be of interest, as Oracle has on occasion tried to become an Internet-enabled company no matter how silly the approach (remember the Network Computer or Oracle 8i/9i?). And as many have commented, keeping OpenOffice.org around just to annoy Microsoft is probably going to be worthwhile even if it doesn't make any money. I won't be surprised, however, if the hardware business, meaning SPARC on the one side, and StorageTek on the other, will be sold to someone else.

What I will be interested in seeing is whether Oracle can do a better job exploiting the vertical integration story that Sun has been been pushing for basically two decades but has repeatedly failed to deliver on. (Like MySQL not building on SPARC (at some point), and JavaFX not being available for OpenSolaris.)

I do feel sorry for the people at MySQL, who will have to readjust to a new employer and probably a new strategy and a few more sets of reorgs for the second time in less than two years. And all this a day before the MySQL Conference, which will now probably be completely distracted by this news. Add the conspicuous absence of any mention of MySQL in the official announcements, and the uncertainty couldn't be greater.

This articles shows how SQL*Loader is used to load CLOB and BLOB data, enabling parallel load operations of large quanities of data. There are several methods for SQL*Loader to load LOBs, but here we will focus on the LOBFILE method, using one LOB per file.

Download the following documents and place them on the server filesystem. In this example we will use the "/tmp" directory for all files:

clob_test1.txt
clob_test2.txt
blob_test1.doc
blob_test2.doc

The first two documents are plain text files, while the second two are Microsoft Word documents. The documents contain the CLOB and BLOB data to be loaded by SQL*Loader. If you prefer you can create your own documents, but make sure the names are reflected in the datafile below.

The lob_test_data.txt file contains regular data and references to the files holding the CLOB and BLOB data.


1,one,01-JAN-2006,clob_test1.txt,blob_test1.doc
2,two,02-JAN-2006,clob_test2.txt,blob_test2.doc

Now we have all the data, we need a table to load the data into.


DROP TABLE lob_tab;

CREATE TABLE lob_tab (
number_content NUMBER(10),
varchar2_content VARCHAR2(100),
date_content DATE,
clob_content CLOB,
blob_content BLOB
);


Next, we define the SQL*Loader controlfile, called lob_test.ctl, that will allow us to load the data.


LOAD DATA
INFILE 'lob_test_data.txt'
INTO TABLE lob_tab
FIELDS TERMINATED BY ','
(number_content CHAR(10),
varchar2_content CHAR(100),
date_content DATE "DD-MON-YYYY" ":date_content",
clob_filename FILLER CHAR(100),
clob_content LOBFILE(clob_filename) TERMINATED BY EOF,
blob_filename FILLER CHAR(100),
blob_content LOBFILE(blob_filename) TERMINATED BY EOF)


Notice that the filename columns in the datafile are marked as FILLERs, so they are not loaded into the table, but they are used in the LOBFILE definition to identify the loacation of the LOB information.

The data is then loaded using the following SQL*Loader command, run from the command line in the same directory as files.


sqlldr userid=test/test@db10g control=lob_test.ctl log=lob_test.log bad=lob_test.bad


The following query shows both the regular data and the LOB data have been loaded successfully.


COLUMN varchar2_content FORMAT A16
COLUMN date_content FORMAT A12
COLUMN clob_content FORMAT A20

SELECT number_content,
varchar2_content,
TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,
clob_content,
DBMS_LOB.getlength(blob_content) AS blob_length
FROM lob_tab;

NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT BLOB_LENGTH
-------------- ---------------- ------------ -------------------- -----------
1 one 01-JAN-2006 This is a clob test1 24064
2 two 02-JAN-2006 This is a clob test2 24064

2 rows selected.

SQL>


In this article I'll describe the installation of Oracle 10g Release 2 Grid Control on Red Hat Enterprise Linux and CentOS. The article is based on a server installation with a minimum of 2G swap, secure Linux disabled and the following package groups installed:

X Window System
GNOME Desktop Environment
Editors
Graphical Internet
Text-based Internet
Server Configuration Tools
Development Tools
Administration Tools
System Tools

The Oracle 10g Grid Control allows you to monitor all aspects of your infrastructure including database and application servers. Like the Oracle9i Management Server it replaces, the grid control requires a database repository. A complete installation includes a repository in a 10.1.0.4.0 database.

The grid control uses agents on each server to enable monitoring and interaction. As a result, once the grid control is installed it is necessary to install an agent on each server you wish to monitor. The installation of an agent is described in it's owner section. All other tasks relate only to the grid control.

Download Software
Download the following software:

Enterprise Manager Downloads
For convenience you may want to download the agent distribution separately. If you are monitoring servers on different platforms the relevant agents must be downloaded.

Unpack Files
First unzip the grid control installation files:

unzip Linux_Grid_Control_full_102010_disk1.zip
unzip Linux_Grid_Control_full_102010_disk2.zip
unzip Linux_Grid_Control_full_102010_disk3.zip

Hosts File
The /etc/hosts file must contain a fully qualified name for the server:

IP-address fully-qualified-machine-name machine-name

Set Kernel Parameters
Modify the /etc/sysctl.conf file to include the lines appropriate to your operating system:

# Red Hat Enterprise Linux 3.0 and CentOS 3.x
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmin = 1
kernel.shmseg = 10
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536


# Red Hat Enterprise Linux 4.0 and CentOS 4.x
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.shmall = 2097152
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144

Run the following command to change the current kernel parameters:

/sbin/sysctl -p

Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows:

SELINUX=disabled
Alternatively, this alteration can be done using the GUI tool (Applications > System Settings > Security Level). Click on the SELinux tab and disable the feature.

Setup
The documentation states that the following packages are required by the relevant operating systems:

Read more »

In this article I'll describe the installation of Oracle Database 10g (10.1.0.2) on Red Hat Enterprise Linux 3. The article is based on a server installation with a minimum of 2G swap, secure Linux disabled and the following package groups installed:

X Window System
GNOME Desktop Environment
Editors
Graphical Internet
Text-based Internet
Server Configuration Tools
Development Tools
Administration Tools
System Tools

Alternative installations may require more packages to be loaded, in addition to the ones listed below.

Download Software
Download the following software:

Oracle Database 10g (10.1.0.2) Software

Unpack Files
First unzip the files:

gunzip ship.db.cpio.gz

Next unpack the contents of the files:

cpio -idmv < ship .db.cpio

You should now have a single directory (Disk1) containing installation files.

Hosts File
The /etc/hosts file must contain a fully qualified name for the server:

ip-address fully-qualified-machine-name machine-name

Set Kernel Parameters
Add the following lines to the /etc/sysctl.conf file:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

Read more »

Basic procedure for a hot backup

The following PL/SQL demonstrates how a hot backup is taken on Oracle:


for ts in tablespaces
alter tablespace $ts begin backup
for df in datafiles of $ts
cp $df $SAVE/$df
alter database datafile '$df' end backup
end
alter tablespace end backup
end
alter system switch logfile
alter database backup controlfile to trace



The second last step:
alter system switch logfile

Forces a checkpoint which in turn updates the datafile headers with a new SCN that is the same for all datafile headers.
Note: Online redo log files should never be backed up!

Differences while hot backup and normal db operation
If the database is hot backuped, the operation is different from normal db operation in only two ways:

When a database block is modified the first time since the backup started, the entire block is written to the online redo logs.
In normal operation, only the changed bytes are written.
The datafile headers are not updated with the SCN when a checkpoint is performed.

Prerequisites:
Two prerequisites must be met for hot backups to make sense:

1) The database must be in archive log mode
2) All archived redo logs since the last backup are available when a database must be recovered.

Find actual size of database

Calculate the size of Datafiles

select name,sum(bytes)/1024/1024 "Size in MB" from dba_data_files;

Calculate the size of Tempfiles

select name,sum(bytes)/1024/1024 "Size in MB" from dba_temp_files;

Calculate the size of Redo log files

select sum(bytes)/1024/1024 "Size in MB" from v$log;

Calculate the size of Control files

select name from v$controlfile;

From OS (Unix, for example)

Go to the location of control files

du -ah

Calculate the size of Archive logs

From sql*plus

SQL> archive log list

Go to destination

du -ah will give you total file size in human readable format.

Small query to list Oracle tablespaces, that are in backup mode:

SELECT d.tablespace_name, b.time
FROM dba_data_files d, v$­backup b
WHERE
d.file_id = b.FILE#
AND b.STATUS = 'ACTIVE';

#sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 20 05:08:20 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> connect / as sysdba
Connected.

SQL> SELECT d.tablespace_name, b.time
2 FROM dba_data_files d, v$­backup b
3 WHERE
4 d.file_id = b.FILE#A
5 ND b.STATUS = 'ACTIVE';

TABLESPACE_NAME TIME
------------------------------ ---------
USERS 02-JAN-08
SYSAUX 02-JAN-08
UNDOTBS1 02-JAN-08
SYSTEM 02-JAN-08
EXAMPLE 02-JAN-08

SQL>

List Oracle Listeners

pgrep -fl -u oracle 'tnslsnr'

List Oracle Databases

pgrep -fl -u oracle 'ora_pmon'

List Shadow Processes

pgrep -fl -u oracle 'LOCAL'

The article is based on a server installation with a minimum of 2G swap and the following package groups installed:
X Window System
GNOME Desktop Environment
Editors
Server Configuration Tools
Development Tools
Administration Tools
System Tools

The Oracle 10g Grid Control allows you to monitor all aspects of your infrastructure including database and application servers. Like the Oracle9i Management Server it replaces, the grid control requires a database repository. Oracle prefer you to consider the grid control as a separate product and as such advise you to install the Oracle 9.0.1 database it comes shipped with. The reason for this unusual server version is that the grid control is built using Oracle Application Server 10g components which still rely on the 9i database for a metadata repository. Version 10.1.0.3.0 of the grid control can use a 10g database as a repository, but for most platforms there is no natural install path directly to 10g so I think it's better to stick with the product as it is shipped.

The grid control uses agents on each server to enable monitoring and interaction. As a result, once the grid control is installed it is necessary to install an agent on each server you wish to monitor. The installation of an agent is described in it's owner section. All other tasks relate only to the grid control.

Read more »

In this article I'll describe the installation of Oracle Database 10g (10.1.0.2) on Fedora Core 1. The article is based on a Fedora Core 1 Server Installation with a minimum of 2G swap and the following package groups installed:

X Window System
GNOME Desktop Environment
Editors
Server Configuration Tools
Development Tools
Administration Tools
System Tools

Download Software
Download the following software:
Oracle Database 10g (10.1.0.2) Software

Read more »

In this article I'll describe the installation of Oracle Database 10g (10.1.0.2) on Fedora Core 4. The article is based on a Fedora Core 4 Server Installation with a minimum of 2G swap, secure Linux disabled and the following package groups installed:

X Window System
GNOME Desktop Environment
Editors
Graphical Internet
Text-based Internet
Server Configuration Tools
Development Tools
Administration Tools
System Tools

Alternative installations may require more packages to be loaded, in addition to the ones listed below.

Download Software
Download the following software:

Oracle Database 10g (10.1.0.2) Software

Unpack Files
First unzip the files:

gunzip ship.db.cpio.gz

Next unpack the contents of the files:

cpio -idmv < ship .db.cpio

You should now have a single directory (Disk1) containing installation files.

Hosts File
The /etc/hosts file must contain a fully qualified name for the server:

ip-address fully-qualified-machine-name machine-name

Set Kernel Parameters
Add the following lines to the /etc/sysctl.conf file:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

Read more »

In this article I'll describe the installation of Oracle Database 10g (10.1.0.2) on RedHat Advanced Server 2.1. The article is based on a server installation with a minimum of 2G swap, secure Linux disabled and the following package groups installed:

X Window System
GNOME Desktop Environment
Editors
Graphical Internet
Text-based Internet
Server Configuration Tools
Development Tools
Administration Tools
System Tools

Alternative installations may require more packages to be loaded, in addition to the ones listed below.

Download Software
Download the following software:

Oracle Database 10g (10.1.0.2) Software

Unpack Files
First unzip the files:

gunzip ship.db.cpio.gz

Next unpack the contents of the files:

cpio -idmv < ship .db.cpio

You should now have a single directory (Disk1) containing installation files.

Hosts File
The /etc/hosts file must contain a fully qualified name for the server:

ip-address fully-qualified-machine-name machine-name

Set Kernel Parameters
Add the following lines to the /etc/sysctl.conf file:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

Read more »

In this article I'll describe the installation of Oracle Database 10g (10.1.0.2) on Fedora Core 2. The article is based on a Fedora Core 2 Server Installation with a minimum of 2G swap and the following package groups installed:

X Window System
GNOME Desktop Environment
Editors
Graphical Internet
Text-based Internet
Server Configuration Tools
Development Tools
Administration Tools
System Tools

Alternative installations may require more packages to be loaded, in addition to the ones listed below.

Download Software
Download the following software:

Oracle Database 10g (10.1.0.2) Software

Unpack Files
First unzip the files:

gunzip ship.db.cpio.gz

Next unpack the contents of the files:

cpio -idmv < ship .db.cpio

You should now have a single directory (Disk1) containing installation files.

Hosts File
The /etc/hosts file must contain a fully qualified name for the server:

ip-address fully-qualified-machine-name machine-name

Set Kernel Parameters
Add the following lines to the /etc/sysctl.conf file:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

Read more »