dbaStreet

Once i built a oracle enterprise linux virtual machine, using oracle virtual box (opensource destop virtualization software), i wanted to increase capacity by adding a new virtual disk to the configuration. My original configuration was built using only one virtual disk and this was mounted as the / file system. Now i want to add a new file system /u01 on a new virtual disk. Below are the steps i performed to accomplish this.

First create a new virtual disk using virtual box
Applications -> System Tools -> Oracle VM Virtual Box
File -> Virtual Media Manager
You will see the window below

Click on New to create a New disk. The New Disk creation wizard starts up.

Click on Next

Choose Dynamically expanding storage, Click Next

Choose the location and file name for the virtual box, vdi file. I chose the same directory, where my first vdi file was located (/u02/vimage/oel55). Also choose the size of the new disk you need. Click Next.

The Summary Screen displays summary information regarding the new disk you are configuring. click next

Now add the new virtual disk to your operating system image

Applications -> System Tools -> Oracle VM Virtual Box

Click on the image that you want to add the virtual disk to.

On the right hand side click on Storage

Choose the SATA controller and click on the + button to add a new disk

You can edit the new disk it creates by click on the disk Name and editing the disks properties on the right most window. (Use the properties to pick the new disk you created in the previous step, and give it a new name).

Now once you boot up your image you will see a new disk at your operating system level. In my case the original disk i had was /dev/sda. The new disk i added showed up as /dev/sdb. I partitioned the disk, formatted it, mounted it and added an entry to /etc/fstab so that every time i startup the image this file system will be mounted.

fdisk /dev/sdb

mkfs -t ext3 /dev/sdb1

mkdir /u01

Add the following entry into /etc/fstab

/dev/sdb1 /u01 ext3 defaults 0 2

Then run the command “mount” from the prompt and /u01 will be mounted.

exec dbms_session.set_identifier(‘px_test’);

alter session set events=’10046 trace name context forever,level 1′;

sqlplus / as sysdba

SQL> sho parameter user_dump_directory

cd /u01/udump

mkdir tmp2

find . -name ‘*.trc’ -mmin 5

trcsess output=prog9.trc clientid=px_test *.trc

tkprof prog9.trc prog9.out sort=exeela sys=no

It’s been a while since i wrote anything on my blog. Not because I am lazy, but because I’ve been doing a bunch of proof of concepts for various customers. database machine, audit vault, data masking, rac and securefiles etc. Its been loads of fun.

I wanted to write about a a couple of neat things i came across .

Exporting from an oracle database in parallel

Imagine that you have a fairly large database and you want to export the database onto two different devices in parallel (Let us say you have two usb devices attached to the server and you want to leverage the write throughput you get to both simultaneously). You can do this in two steps

• Define 2 different oracle directories
  • Let us say for eg: the drives you want to use are mounted at /u01/firstusb and /u01/secondusb
  • create directory exp1 as ‘/u01/firstusb’;
  • create directory exp2 as ‘/u01/secondusb’;
• While exporting use the directories in the dumpfile keyword
  • expdp system/manager directory=exp1 dumpfile=exp1:exp_test_%U.dmp,exp2:exp_test_%U.dmp schemas=AAA,BBB,CCC,DDD parallel=8  logfile=exp.log

So since you are using exp1:exp_test_%U.dmp,exp2:exp_test_%U.dmp  and a parallel=8, datapump creates 4 dump files each on exp1 and exp2 which points to /u01/firstusb and /u01/secondusb respectively.

Importing and Enabling Compression (OLTP or Exadata Hybrid Columnar Compression)

Let us say you want to export from a database that does not have compression turned on, and want to import into one with compression turned ON. Since the table is created with the NOCOMPRESS (defautlt) keyword, the expdp statement actually gathers this info and uses it to create the “create table” statement when it creates the table during the import. So the default is for the imported table also to be NOCOMPRESS.

If you only have a hand full of tables you want to enable compression on, you can pre-create the table (And its indexes and such) using the Compress for Oltp clause and then run the datapump import specifying the parameter table_exists_option=APPEND

If you want to do it for all the tables in a tablespace.

• Create the tablespace with compression enabled at the tablespace level.
• Then while importing using datapump specify the transform=SEGMENT_ATTRIBUTES:n:table parameter.

This causes import to ignore the segment attributes for the table while creating it, which will cause the table to inherit the attributes specified at the tablespace level and will be created with OLTP compression enabled.