Oracle Import Data Pump common issues ORA-39002: invalid operation , ORA-39001: invalid argument value

Import Data pump and Export Data pump utilitities when used ommonly give following errors.

ORA-39002: invalid operation + ORA-39070: Unable to open the log file. + ORA-29283: invalid file operation + ORA-06512: at "SYS.UTL_FILE", line 475 + ORA-29283: invalid file operation


ORA-39001: invalid argument value + ORA-39000: bad dump file specification + ORA-31640: unable to open dump file "c:\export\GeniePid2_20100828.dmp" for read + ORA-27041: unable to open file

Both of these issue came up when following import dump command was fired
impdp sys/password@tns directory=my_dir dumpfile=GeniePid2_AJ1.dmp remap_schema=GeniePid2:Pid2AJ1 logfile=GeniePid2_AJ1.log TRANSFORM=oid:n

Solution

1. Create a directory or folder by any name like c:\export in Server Machine (Not Client machine).Please make sure this path must reside where Oracle server is running ; nobody told this to me and I kept banging my head for 2 hours.
2. Create a directory which is logical or use by oracle for doing export or import grant the users dba role or exp_full_database or imp_full_database as per your usage

          SQL>create directory my_dir as ‘c:\export\';

          SQL> grant read,write on directory my_dir to public;

Both these points resolved my issue.

Move location of Oracle Data File (.dbf) file

I faced space crunch issue on one of the Oracle Server. Oracle is installed on c: and virtually no space is left in c drive however d: drive have dozens of space left so we decided a simple workaround to change the location of some of the oracle data file from c drive to d drive. Here are the simple steps to perform it.

Shut down the Oracle 
SQL> shutdown immediate;

Manually move the .dbf files
I copied SYSTEM01.DBF file from C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF to D:\Oradata\. However you can copy all the .dbf files depending upon your requirement and space in target directory.

Restart the Oracle 
SQL>startup mount;

Alter the database
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' TO 'D:\ORADATA\SYSTEM01.DBF';

Check the database
Fire the following query to check weather the changes are correct.
SQL>Select name from v$datafile;
Verify your .dbf file ; it must have the changed path now.

Open the altered Database
SQL> alter database open;


This might not be the best approach to solve the space issue but can be handy as a quick workaround.

Stop Crying Your Heart.....

Hold up... hold on... don't be scared


You'll never change what's been and gone

May your smile... Shine on... Don't be scared

Your destiny may keep you warm.



Cos all of the stars are fading away

Just try not to worry you'll see them some day

Take what you need and be on your way

And stop crying your heart out



Get up... Come on... why you scared

You'll never change what been and gone

Future lies in Cloud

Everybody is talking about cloud ; most of the developers have heard of the term cloud but not really know what lies beneath it. The same thing happened to me as well and I decided to devote my precious couple of hours from my weekend to dig this out.
In just couple of hours finding I am pretty sure that the cloud is the next big thing which will revolutionize the world.

 Next big thing, How ?

If we can dwell into history we can easily find out that when electricity first became available and started to become the necessity for industries, those industries started to
generate/produce their own elctricity, soon they started to realize that this was not the best of the solution. The actuall revolution came when electricity was provided as service from centrally located Power Grids and then only its real power was utilized.

The same story can be rewritten now; instead of having our own set of power full processing units inside so called server rooms which is a comon sight in most of our companies.These companies also constantly running in the rat race of
upgrading those microprocessors and RAM units.So instead of doing this individually let have this available as service , centrally located which can be available through high speed internet.So why restrict this concept for companies ; we can think this to happen in our personal space also. Instead of purchasing a high speed laptop / desktop , soon we will opt for a WI- FI enabled hand held device capable of connecting cloud and doing all sort of work that you laptop does. So no need to update those OS/antiviruses and downloading songs/videos as all these will be available on the space allocated to you on cloud.

.In a developing nation like India only 10-15% of the total population have access to high speed internet as of now but surly this got to change very fast.

This is the reason biggies like google, IBM ete investing so much in this.

Another popular term Ubiquitous Cloud

So when the term ubiquitous is prefixed with cloud computing another set of theories start to hold the ground, lets assume we are in world of Ubiquitous Cloud which means only one machine exists .So in the world of seamless computing environment request for data prcessing will flow from any part of world to that one machine so this flow of wave will drive the cost and performance of request to be served.These theories will come into picture

1) Follow the moon : This theory says that put your computational machines where it is most cost saving irrespective of the timezones.For instance California can have their Processing machines located in India ; thus when their day start all their proceesing request follow the moon and being served by sleepy India.

2) Follow the Sun : If your machines are located in the same time zone then you are following the sun.Like commercial and entertainment industry will always follows
the sun.

3) Follow the law: Differences in privacy laws, censorship, and national security fears may restrict places where data can flow freely


So what is this Grid Computing ?

..........................

"Column ambiguously defined" - Difference in Oracle 10g and 11g

Our product is always tested against Oracle 10g and one of our client insisted to move to Oracle 11g database and shockingly the database creation crashed.On studying the logs we came to the conclusions that best coding practices if followed always helps you to make your product stable, scalable.

What we found is that Oracle 11g does not compile queries that have  columns from different table without their corresponding table aliases.

e.g.

This query will work till Oracle 10g only and will fail in 11g and higher versions.This will give "Column ambiguously defined" error message in Oracle 11g and higher versions.

Select Column1,Column2 from Table1 inner join Table2 On Table1.Column1 = Table2.Column2;


However the query below will Work in Oracle 10g ,11g and all coming higher versions as this uses best coding practices

Select Table1.Column1 ,Table2.Column2 from Table1 inner join Table2 On Table1.Column1 = Table2.Column2;


This clearly shows that best practices if followed give slight pain while implementing but are always fruitful in longer run.

Problem with Office Automation (MS-Word) using Asp.Net 3.5 on 64 bit Windows 2008/Vista

Implementing MS-Word Automation using VSTO 3.0 works well with Windows 2008 / XP /2003 all with 32 bit versions. But these office automation Application start to fails once deployed on 64 bit machines.I am still searching for the exact reasons.However this can be termed as bug from Microsoft or not willing to update 64 bit Windows to support Office Applications. However now OpenXML is the new term which they strongly recommended for implementing office automation.

After searching on various forums I came to know that it is still possible to run office automations application on 64 bit machines(I tested on windows 2008 64 bit version). This is possible by

On Windows 2008 Server x64
Please make this folde
C:\Windows\SysWOW64\config\systemprofile\Desktop

Windows 2008 Server x86
Please make this folder
C:\Windows\System32\config\systemprofile\Desktop

There is one more solution available which talks about doing some hack in registry which I am not very comfortable at so i did'n tried that.However I am writing the it for the reference if somebody need that.

First, use regedit (from the SysWOW64 folder) to search the registry for the CLSID(s) related to the command "WINWORD.EXE /Automation" , you might find more than one of them.
(in my case this was: {000209FE-0000-0000-C000-000000000046} and {000209FF-0000-0000-C000-000000000046})
Under those keys in HKEY_CLASSES_ROOT\CLSID\, add a string value AppID = {same value as the IDs}
Then under HKEY_CLASSES_ROOT\AppID\ create a new key (folder) for each of these IDs, and inside each of them add a string value: RunAs = Interactive User
Next go to Dcomcnfg (from the SysWOW64 folder) and search for those IDs. (there might be a third ID related to Winword.exe), in my case this was {00020906-0000-0000-C000-000000000046}.


Note all those IDs, then right click on each of them, Properties, Security, and edit both the launch & access permissions to add and give Network Service and Interactive full permissions.

Task Scheduling in Oracle 10g

I have a working experience in Sql Server 2005 and creating Jobs and scheduling them is just the mater of few clicks in MS SQL Server.

However in Oracle this can be achieved with lot more control

Jobs are creared in Oracle using "DBMS_SCHEDULER" which is internal Oracle package (since Version 10g).I will try to describe about three basic aspects of DBMS_SCHEDULAR which are bare minimum required to create a Job in Oracle.

• Programs
• Schedule
• Jobs

Here Programs and Schedules are individual entities , then Jobs are used to associate a Program with a Schedule.

PROGRAMS

Programs contains the actual task that a scheduler will be required to perform.Oracle supports these tasks implementation in various ways.

DBMS_SCHEDULER.create_program (

program_name => 'plsql_block',

program_type => 'PLSQL_BLOCK',
program_action => 'Any valid pl-sql block.....',
enabled => TRUE,
comments => 'This program will run the above pl-sql block');


Here "program_type" parameter decides how the tasks are written in program. This can be either of these

1. PLSQL_BLOCK
2. EXECUTABLE
3. STORED_PROCEDURE

Here EXECUTABLES can be used to call OS utilities and programs, not just PL/SQL program units. This ability makes it the most comprehensive job management tool for managing Oracle Database and related jobs.

SCHEDULES

Here is the sample sql to create a simple schedular.

DBMS_SCHEDULER.create_schedule (
schedule_name => 'Audit_Purge_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily;byhour=0;byminute=0;bysecond=0',
end_date => NULL,
comments => 'This will run daily');

The most important parameter of Schedular is repeat_interval , some of the mostly used options are
freq
This specifies the type of recurrence. It must be specified. The possible values are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.
bymonth
This specifies which month or months you want the job to execute in. You can use numbers such as 1 for January and 3 for March, as well as three-letter abbreviations such as FEB for February and JUL for July.
byyearday
This specifies the day of the year as a number. Valid values are 1 to 366. An example is 69, which is March 10 (31 for January, 28 for February, and 10 for March). 69 evaluates to March 10 for non-leap years and March 9 in leap years. -2 will always evaluate to December 30th independent of whether it is a leap year.
bymonthday
This specifies the day of the month as a number. Valid values are 1 to 31. An example is 10, which means the 10th day of the selected month. You can use the minus sign (-) to count backward from the last day, so, for example, BYMONTHDAY=-1 means the last day of the month and BYMONTHDAY=-2 means the next to last day of the month.
byday
This specifies the day of the week from Monday to Sunday in the form MON, TUE, and so on. Using numbers, you can specify the 26th Friday of the year, if using a YEARLY frequency, or the 4th THU of the month, using a MONTHLY frequency. Using the minus sign, you can say the second to last Friday of the month. For example, -1 FRI is the last Friday of the month.
byhour
This specifies the hour on which the job is to run. Valid values are 0 to 23. As an example, 10 means 10AM.
byminute
This specifies the minute on which the job is to run. Valid values are 0 to 59. As an example, 45 means 45 minutes past the chosen hour.
bysecond
This specifies the second on which the job is to run. Valid values are 0 to 59. As an example, 30 means 30 seconds past the chosen minute.

JOBS

Jobs are basically the actuall implementation where you combine your task (program) with a time schedule. They can either be made up of predefined parts (programs and schedules) or completely self contained depending on which overload of the CREATE_JOB procedure is used to create them:

The first overload will create job entirely of its own, no programs and Schedules

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'JobswithNoProgramNoSchedule',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END


The second overload will display a Job defined by an existing program and schedule.

This is the best modular approach to create Job.With this approac you can clearly isolate task with schedule and edit both of them separately.

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_prog_sched_job_definition',
program_name => 'test_plsql_block_prog',
schedule_name => 'test_hourly_schedule',
enabled => TRUE,
comments => 'Job defined by an existing program and schedule.');
END


Grant Permissions

If you are planning to use scheduler within a stored procedure, then you must have Create Job privilege to the schema.
Grant Create Job to mySchema

Here myschema is the name schema where schedular is created.
Create Job is the privilege required to execute DBMS_Schedular

Monitoring

Show all schedules
select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules;

Show all jobs and their attributes:
select * from dba_scheduler_jobs

Show all program-objects and their attributes
select * from dba_scheduler_programs;

Show all program-arguments:
select * from dba_scheduler_program_args;