Oracle Data Pump 10g

Recently i worked on Oracle data pump for second time. Its actually a feature of oracle database that helps to export data in a faster manner, i had earlier worked on this in one of my previous projects, but recently used it to export somewhat bigger table in the database.
This method of exporting data is much faster that simple imp and exp.

I will just outline some simple steps to use this great utility -

First of of all we need to create a Dump directory -

In my case i create this directory at the location /backup/dpump_dir

SQL> Create directory exp_dir as '/backup/dpump_dir';

Then we need to grant read,write access to the users that are exporting data on this directory.

SQL> grant read,write on exp_dir to user1;

Now we are all set to start datapump export , we will use -

$expdp username/password DIRECTORY=exp_dir DUMPFILE=exp1.dmp LOGFILE=exp1l.og FULL=y PARALLEL= 10

Also to export a single table we can use -

$ expdp username/password DIRECTORY=exp_dir DUMPFILE=user1.dmp LOGFILE=user1l.log TABLES=TABLENAME PARALLEL=10

This will export a single table

For more information use EXPDP help=y on dollar prompt

Now to check your jobs you can write-

sql> select * from dba_datapump_jobs

From here you can view the name of your job.

To kill currently running job we can first find session name from above and then use -

expdp username/password attach=JOB_NAME
Afterwards we get a prompt

Export >
Export > Kill_job
Are you sure you wish to stop this job ([y]/n): y

Again we can check with -->

SQL> select * from dba_datapump_jobs
no rows selected

High Version Count In AWR

One day while reading AWR reports, i hav seen a section named Sql by high version count.
This was something new for me and i had cehcked the defination on other sites and found that this is mainly caused due to bind mismatch. Although the application is using Bind variables, but still sqls are not gettinsg shared due to this bind mismatch.
Anyhow we came across a new event that can be set to tackle this problem -
'10503 trace name context forever, level '

spooling sql output to excel

sometime it is required to spool sql output to excel file, i learned a new way to do this and it is very simple and handy. just posing an example below, this was told to me by one on my friends who is a dba and i have also seen such examples on otn website as well.
you just need to connect to sqlplus and use following :-
SQL>set markup html on;
SQL>spool 'c:\test.xls'
SQL>select empno,deptno from scott.emp;

SQL> SPOOL OFF;


SQL> SET MARKUP HTML OFF;


SQL>

Its an easy way...

More About Real Application Clusters

This is in continuation with my previous post , one thing i forgot to mention is that in RAC environment database resides on SAN, hence in the event of failure on any node , database is still available for other nodes to access. Here there are three types of network connection available, first is interconnect (This is the connection that is used as heartbeat and to transfer data blocks), second is VIP(Virtual Ip Address) that is accessed by users, and other is a physical Ip. Interconnects are heartbeats that play an important role in manging the cluster,if primary node is unable to communicate with secondary node through interconnects then the second node is evicted from the cluster or it reboots. Sometime later i will surely put some screenshots of how to install RAC on Vmware, for those who are new to this it is advisable to install on Vmware, and have a feel of the cluster environment.

So what is Oracle Real Application Clusters??

From some time i was thinking of writing about some basic stuff regarding RAC.
RAC stands for Real Application Cluster , which in plain term means a Database working in Cluster mode,now hope most of you are familier with the term CLUSTER, otherwie just do a google, am sure you will find good pages on this. Since this database works in cluster minimum of 2 nodes are required to operate. So what benefits does this provides!! First of all RAC is an active-active cluster, this means both the nodes of Oracle cluster can access the database simultaneously.Hence in the event of failure if one node goes down, the database can still be accessed from the second node. So you can give 99% uptime..:-)
Second benefit is that it also provides for feature such as load balancing, in this case if we have 4000 users accessing a database, they will be evenly distributed on both the nodes, i.e 2000 users on each node. This helps in utilizing system resources of both the nodes and also easing of CPU utilization. In some other post i will surely write about some really good scenario that i have come across in the environments i have managed..and will also try to explain more about RAC..

events in oracle DB

When i was very new to database, i always used to wonder why are there so many events in database and what is the use of all these..:-)..but now i really understand why are they there. In fact most of the times , when you feel like checking health of the database, just check out the event at that point of time and also at peak load.
The view v$session is a great place to start, the column event gives you event being faced by a particular session. This has helped me a lot..:-)