dbaStreet

Here are some ways to add hours, days, months etc to a given date and get the result from the bash shell

To add seconds to a given date

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 seconds”

Thu Aug 12 17:30:45 EDT 2010

To add minutes to a given date in the bash shell you can

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 minutes”

Thu Aug 12 17:45:30 EDT 2010

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 hours”

Fri Aug 13 08:30:30 EDT 2010

To add days to a given date

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 days”

Fri Aug 27 17:30:30 EDT 2010

Most of oracle’s MAA documentation for transportable tablespaces, seems to recommend that one should put the tablespaces one is transporting, in read-only mode in the source database, before copying the datafiles to the target. This in most cases means application downtime.

In order to minimize the downtime the recommendations seem to be

• Create a dataguard physical standby database and use this standby database as the source for transport
• Create a duplicate (aka clone) of your source and use this new duplicate as the source for transport

If you are only transporting a subset of your tablespaces and you want to minimize your downtime, a 3rd good option is to create and use transportable tablespace sets from your already existing rman backups. This process is documented in the Backup and Recovery users guide, Chapter 26 (11gR2 manual).

Since this process uses an existing rman backup you incur no downtime (ie no need to place tablespaces in read-only mode) on your production systems.

Below are the steps to accomplish this (In my example rk01 is the source database and rk02 is the target database).

• First off, your source database should be running in archivelog mode

• Take a full database backup from your source database

export ORACLE_SID=rk01
rman target /

run {

allocate channel oem_backup_disk1 type disk format ‘/u01/orarch/rk01/backup/%U’;

backup as BACKUPSET tag ‘%TAG’ database;

backup as BACKUPSET tag ‘%TAG’ archivelog all not backed up;

release channel oem_backup_disk1;

}

run {

allocate channel oem_backup_disk1 type disk format ‘/u01/orarch/rk01/backup/%U’ maxpiecesize 1000 G;

backup as BACKUPSET tag ‘%TAG’ current controlfile;

release channel oem_backup_disk1;

}

exit;

• Create a transportable tablespace set for the tablespaces you need to transport

export ORACLE_SID=rk01

rman target /

RMAN>  transport tablespace example

2> tablespace destination ‘/u01/orarch/rk01/datafile’

3> auxiliary destination ‘/u01/orarch/rk01/tmp’;

Once the process is complete rman leaves a copy of the datafile (An operating system file, not a backup set file) for the tablespace example , in the directory /u01/orarch/rk01/datafile. It also leaves a export dump file that has the metadata needed for the transport in the same directory /u01/orarch/rk01/datafile.

• Do endianness conversions on the files, if you need to go cross platform (Use rman convert)
• Attach the tablespace to your target database

export ORACLE_SID=rk02

sqlplus / as sysdba

create directory tts_dir as ‘/u01/orarch/rk01/datafile’

/

grant all on directory tts_dir to public

/

Exit;

Before you run the next import, make sure that you have created the schema’s (with appropriate privileges) that are in the tablespace you are transporting in the target database rk02, also make sure any roles that are required are created in the target. Eg: The schema HR has objects in the example tablespace. use the create user command to create the HR user with appropriate privileges in the database rk02.

impdp system/manager dumpfile=dmpfile.dmp directory=tts_dir transport_datafiles=/u01/oradata/rk02/example01.dbf logfile=tts_import.log

So As you can see the whole process is executed without shutting down the source database rk01.

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.

To identify the number of physical cpu’s you have on a server you can execute the command

-bash-3.2$ grep ‘physical id’ /proc/cpuinfo | sort -u

physical id : 0

physical id : 1

This means you have 2 cpu’s (You can add a | wc -l to the previous command to just get the count)

To identify the number of cores per cpu you can execute the command

-bash-3.2$ grep ‘cores’ /proc/cpuinfo | sort -u

cpu cores : 4

To check if hyperthreading is turned on in the bios

If cpu cores equal the number of siblings then hyperthreading is off. If siblings is a multiple of cores then hyperthreading is turned on.

If you want to see the number of cpu’s being seen by the operating systems then you can

grep ‘processor’ /proc/cpuinfo | wc -l

or

run the top command and once the output display’s you can just press the 1 key and it will show the utilization for each cpu on the top.

On any database server, there is often only a limited amount of resources (read cpu, memory, i/o). Oracle database servers are often subjected to highly concurrent, resource consuming, queries issued by database client programs. This often leads to contention for cpu, memory and i/o resources on a server. Once you have way more processes on a system actively executing, compared to the number of cpu cores you have, at some point the scheduling of these processes becomes inefficient at the operating system level.

The oracle Database resource manager can be used to throttle the Number of oracle database sessions actively running on a database, or to limit the amount of cpu resources a session gets, or to limit the degree of parallelism each session gets among other things.

Below are the steps to setup a resource plan that

Limits, maximum Degree of Parallelism each session can have, at 64.

Limits the maximum number of Concurrent active sessions that can be present for a user to 2. (The other sessions get queued).

Create a Consumer Group

Login to Enterprise Manager, click on the “Server” tab.

Under the “Resource Manager” section, click on “Consumer Groups”

Click on the “Create” Button

Enter a Name and Description for the Consumer group

Click on the “Add” button to add a database user to the consumer group

Check the box to the right of the user you want to add and click on “Select”

Now click on the “Database Instance” bread crumb to return to the “Server” tab

Click on “Consumer Group Mappings”

Click the radio button on the row that has the value “Oracle User” in the “View” column.

Click on “Add Rule for Selected Type”

In the drop down list “Selected consumer group”, choose “DOP_GROUP”.

In the “Available Oracle User” list, click on the user you want to add to this “DOP_GROUP” group and click on “Move”

Click Ok.

Click on Apply

Click on the “Database Instance” Breadcrump to return to the “Server Tab”

Click on “Resource Plans”

Click on “Create” to create a new resource plan

From the table “Available Group/Subplans” choose “DOP_GROUP” and click on Move. Click Ok.

Click on the tab named “Parallelism”

For the DOP_GROUP set the maximum degree of parallelism to be 64

Click on the tab named “Session Pool”

Set the number of active sessions to “2″ (Or any number you want (In the screen i have used 12))

Click “Ok”

In the “Resource Plans” screen, choose the DOP_PLAN, In the actions drop down list, choose “Activate” and click on Go.

Now the resource plan “DOP_PLAN” is active.

Let us say now we submit a bunch of queries (Let us say 20) simultaneously (That use parallel query)as the database user HR then

Only 2 of them will be allowed to run at the same time (The rest are queued)

Each session gets a maximum Degree of parallelism of 64

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

There are two new awr reports in 11gr2, which will be helpful to dba’s in Real Application Clusters Environments (RAC).

awrgrpt.sql

This is a cluster wide awr report, so you can see a lot of the information from all the nodes in the same section, and you can also see aggregated statistics from all the instances at the same time (You can see totals, averages and standard deviations).

awrgdrpt.sql

This is a cluster wide stats diff report (like you had awrddrpt.sql in 11gr1), comparing the stats differences between two different snapshot intervals, across all nodes in the cluster.

These are huge additions to the awr reports, that enable understanding the database performance in real application clusters environments.

The National Institute of Standards and technology has a good, concise  definition of cloud computing. Sushil kumar of Oracle, was using the same language to define cloud computing in an article the current release of the oracle magazine.

Essential Charachteristics

• On Demand Self-Service
• Broad Network Access
• Resource Pooling
• Rapid Elasticity
• Measured Service

Service Models

• Cloud Software as a Service (SaaS)
• Cloud Platform as a Service (PaaS)
• Cloud Infrastructure as a Service (IaaS)

Deployment Models

• Private Cloud
• Community Cloud
• Public Cloud
• Hybrid Cloud

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.