orawin.info

Niall's Oracle Pages – Oracle Opinion since 2004

Installing SQL*Developer on Debian Based Distributions

without comments

Oracle provide a free tool SQL*Developer that I rather like. Recently it has had a significant update to version 4 which is available as a beta/early adopter release at this link. The only slight difficulty is that unlike previous releases there is no zip or tarball of the installation. In my case I run Ubuntu which is a debian derived distribution and doesn’t natively support the RPM format available from Oracle. Never the less installation is fairly straightforward. This post describes how I did this after first downloading the package above – I already had jdk 7 installed.

Obtain Required Utility

The utility Alien is available to convert software packages made for distirbution into one suitable for a different distribution. In my case Alien is available from the ubuntu channels. The first step therefore is to install alien. Fire up a command line and type


sudo apt-get alien

This will install alien and any dependencies – in my case there were a fair few.
Once alien is installed convert the package and its post install scripts as follows.


sudo alien --scripts sqldeveloper-4.0.0.12.27-1.noarch.rpm

This will unpackage the rpm and then repackage a .deb debian package. I obtained sqldeveloper_4.0.0.12.27-2_all.deb. Finally install the new package using the dpkg utility that underlies aptitude.


sudo dpkg -i sqldeveloper_4.0.0.12.27-2_all.deb

This installed the bright shiny new SQL*Developer

Screenshot from 2013-08-09 16:23:15

Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

August 9th, 2013 at 3:25 pm

Posted in Uncategorized

Job Posting

without comments

A job vacancy for an experienced Oracle Database administrator has opened up at our offices in NYC.  The full job ad with details on how to apply is at http://www.dice.com/job/result/wallsond/201309?src=19 We’re looking for an experienced DBA to work in a small global team providing 24/7 operational support for a reasonably large number of databases ranging in size from a few tens of gb to several tb sized databases growing at some tens of gb per day. 
 
Plus sides: You get to work with pretty much up to date technology (11.2, RAC, DG etc) in a nice environment for a significantly sized and rapidly growing financial services organisation. You get to work in a diverse and supportive global team. 
 
Down sides: You’ll get to work with me, but safely the other side of the Atlantic Ocean, this means you must be prepared for bad puns and cricket related IM conversations. 

Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

June 24th, 2013 at 3:54 pm

Posted in Uncategorized

When unused – isn’t.

with 3 comments

So we recently reviewed a table with no fewer than 23 indexes on it. A combination of alter index… monitoring usage and reviewing DBMS_HIST_SQL_PLAN for plans that referenced the indexes found 8 indexes that were never used. As this table is also heavily updated we removed the indexes identified as unused. This was, fortunately, a QA system under change control. See if you can spot when the change was made.

index_aas

Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

March 7th, 2013 at 2:44 pm

Posted in Uncategorized

Friday Afternoon

without comments

This is known in the DBA trade as being “in the brown stuff”

Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

August 10th, 2012 at 2:57 pm

Posted in Uncategorized

Hello Niall…

without comments

… Read an email I received today from Oracle Corporation

Hello Niall,

I hope you are well.

I would like to introduce myself as your Oracle Linux and Oracle Virtual Machine Account representative for <name of employer I left in 2008>

I would like to set up a 5 minutes introduction call with you to discuss the following benefits of Oracle’s support for Red hat Linux:

  • 60% cheaper than Red Hat Support.
  • No Migration required from Red Hat to Oracle Linux
  • Indemnification
  • Free Enterprise Manager (Equivalent to Satellite Server)
  • Free Cluster Ware
  • Life time support
  • 24*7 support
  • Zero downtime on updates. No reboot needed.

· Backporting, with the help of which you can stay the OS version! of your own choice their won’t be any forced upgrade or update sent from us.

· Our support cost is based on the number of Physical servers and Physical Processors, we support all the virtual environments created on top of a Physical machine unlike Red hat where you have to pay extra for Virtual environments.

I would really appreciate if you can please let me know the best time when we can have a discussion.

Once again thanks

Warm Regards,

I’ll not be bothering to schedule the call because:

  • You can’t write in English
  • You have hideously out of date contact info
  • You wish to discuss 10 things in 5 minutes

Fortunately you didn’t mass mail this spam to anyone with a public blog.

Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

July 17th, 2012 at 1:55 pm

Posted in Uncategorized

Tagged with ,

Quick Check for vulnerable accounts

without comments

New to me in 11g is the view DBA_USERS_WITH_DEFPWD which checks both for users with default passwords and those whose password is equal to their username. The query below might be a useful  addition to your support toolbox.

select u.username,u.account_status
from dba_users u inner join dba_users_with_defpwd p
on (u.username = p.username);

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
OUTLN                          LOCKED
DIP                            LOCKED
ORACLE_OCM                     LOCKED
WMSYS                          LOCKED
CTXSYS                         LOCKED
XXXX                           OPEN

Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

June 7th, 2012 at 10:15 am

Posted in Uncategorized

Tagged with

Product Review

without comments

A while ago I said that I’d be posting a product review here. Unfortunately work got in the way of me posting the review as quickly as I would have liked. The nice people at Ucertify sent me a review kit for the OCA Oracle Database 11g: Administration 1 exam.

I very much like the structure of the software which provides a wealth of study aids as shown below.

The notes are generally clearly written and well supplemented with links to, for example, sections from the Oracle 11g Concepts Guide. Each section allows you to add your own notes, discuss with other users online and tag for later review. Overall then the product provides a wealth of different sources of relevant study material and provides tools that allow you to use that material in conjunction with the way you learn.

In addition the product allows you to assess how you are doing against the exam objectives via multiple scheduled tests that can be taken in either learning or timed mode.

Intelligent use of the test system in conjunction with good source material ought therefore to ensure that you arrive at the test prepared properly for the exam. In principle then I think that this is a fine product for preparing for the OCP exams. I do however have some concerns. These are largely to do with content. I didn’t do a thorough walk through all of the hundreds of notes, but I saw enough examples of dubious content to be a little concerned, for example the note “What is the Shared Pool” contains the following statement.

The shared pool contains the following three components:

  • Library Cache
  • Data Dictionary Cache
  • User Global Area

The thing is that the UGA ought really to live in the large pool (and will do so unless it isn’t configured ) Indeed a later note correctly states that the UGA can live in the SGA if so required.

Similarly the note on direct path load states correctly that the buffer cache is not involved and then adds

It does not generate UNDO data and generation of redo can also be switched off

this is not quite correct, and is in  fact a common misunderstanding. It is true that undo and redo generation requirements are reduced, but any recursive SQL required (for example extent allocation) will be protected in the normal way.

Overall then the product probably deserves an 8 out of 10 from me. The structure and approach to test preparation are excellent, but I’d want to review the study notes pretty carefully for accuracy and completeness. That said I’d expect the target audience (those wishing to pass the exam) to get full value from the product.

 

Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

February 15th, 2012 at 10:53 am

Posted in Uncategorized

Fast Recovery Area

without comments

I’d be grateful if anyone who is responsible for Oracle database backups could take a quick (3 question) survey on their use or otherwise of the Fast (Flash) Recovery Area feature. http://www.surveymonkey.com/s/J93L7WT All responses are anonymous.

Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

January 19th, 2012 at 5:39 pm

Posted in Uncategorized

Tagged with ,

Adding Datafiles

without comments

Yesterday I talked about some of the things to consider when adding space to a tablespace using auto-extend for datafiles. This of course isn’t the only way to add space to a tablespace. You could simply add more datafiles. Indeed nearly a decade ago (scary!) I wrote

The final reason for multiple datafiles (to spread IO) depends entirely on
how many disks you have which you don’t tell us, but in general small
datafiles spread across multiple disks will give better performance than
large datafiles on single disks.

At the time this sort of recommendation (use multiple datafiles, manually striped) was pretty common. These days of course with the widespread adoption of SAN technology, ASM, LVM and so on the micromanagement of disk I/O is a disappearing art (and mostly rightly so). The same underlying goal, to spread I/O, is achieved in hardware or o/s level volume management. Never the less, one might wonder how the exercise in growing a table would fare in a database where we added datafiles manually and in a controlled fashion, rather than just allowing auto-extend to do its job with a reasonable value for NEXT

The test script for this is even simpler than the one produced yesterday, because all that is in fact needed is a test that allocates N number of datafiles all of the same size. I reproduce my script for this below:

set timing off
set verify off

accept file_size prompt "Enter file size in MB [default 1)" default 1
accept files_to_add prompt "Enter #files to add [default 100]" default 100

create tablespace ts_&file_size.
datafile 'c:\app\oracle\oradata\niall\ts_&file_size..dbf' size &file_size.m autoextend off;

set timing on

begin
 for i in 1..&files_to_add. loop
   execute immediate 'alter tablespace  ts_&file_size. add datafile ''c:\app\oracle\oradata\niall\ts_&file_size.'||i||'.dbf'' size &file_size.m';
 end loop;
end;
/

drop tablespace ts_&file_size. including contents and datafiles;

This compares per operation with yesterday as follows :- The overhead column is just how much longer it takes to add a datafile.

Size (MB) Extend Time Add File Time Overhead
1 0.27 0.73 63%
10 0.53 1.31 59%
25 1.17 1.65 29%
50 1.74 2.30 24%
100 3.20 4.00 20%


However it is worth considering two more things before concluding that adding a datafile is a complete waste of time.

First, the session waiting on an add datafile event is the DBA session and not a user session. Consider an OLTP application where you know that you will need to add another 1gb of data per day (so we can reuse my figures). Imagine that you’ve decided that you don’t want any session to be delayed more than 1/2 a second by datafile extension. With my data from yesterday that would mean picking an autoextend size of about 10mb (0.52s per extension), all in all we’d waste about 52s of end-user time autoextending. If we were implementing multiple files per database then we’d almost certainly add a single 1gb datafile (in fact we’d likely have a standard size to add). This would end up having 2 advantages.

  • The wait would fall on the administrator and not the user
  • The time waited would be less

To illustrate the second point, and the fact that you can make objective predictions by gathering data like this, I’ve created an equivalent graph to yesterdays for file addition rather than file extension using the data from the table above. The bottom line is the quicker file extension, the top the time taken to add a single file of a specified size. I’ve also included the best fit linear trend line for the data as estimated by Excel.

If we plugin 1000mb for x to the formula given above we get an estimate for the time taken to add this datafile of 32.32 seconds.  When I reran my test script plugging in a single 1gb file, I got an actual time of

PL/SQL procedure successfully completed.

Elapsed: 00:00:32.26

More Conclusions

For nearly all people the strategy of adopting an autoextend size of a few 10s of MB and not worrying about precise file sizes will be perfectly sensible. Bigfile tablespaces with autoextend may indeed be the way for many people who would otherwise have lots of 32gb datafiles, especially now RMAN can back these up in chunks. For some people however, either who have extremely tight performance requirements or who have excellent understanding of data growth patterns the old strategy of adding defined standard size datafiles in order to manage space may still be advantageous.

Possibly Related Posts:

Written by Niall Litchfield

January 5th, 2012 at 3:30 pm

Posted in Uncategorized

Product Review

without comments

I have got a offer from uCertify to review their Exam PrepKit. The terms of this were simply that I write an unbiased review (and you can be assured that no money is changing hands) so now I am reviewing Oracle 1Z0-052 PrepKit from uCertify which is the DBA 11G OCP Kit. I expect to post a full review in the next week or so.

Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

January 4th, 2012 at 5:15 pm

Posted in Uncategorized