orawin.info

When its time to start booking hotel rooms and planning your agenda for the UKOUG conference in Birmingham (UK) for the 4th to the 7th December. Yep, all the cool sessions from Oracle Open World, minus an awful lot of the pure marketing. So just to wet your appetite here are my highlights in advance .

• Oak Table Sunday. A whole day of  wit, wisdom, argument and education from World Renowned database experts. Probably beer as well.
• International Speakers who rarely come to Europe.  Connor McDonald, Greg Rahn, Maria Colgan, Iggy Fernandez, Cary Millsap, Marc Fielding
• UK speakers who’ve been there, done that and wear the scars. (too many to mention)
• Key Notes worth attending – Ray Wang, Andrew Mendelsohn, that man Cary Millsap again.
• The Balti Triangle
• The Oak Table Challenge

Possibly Related Posts:

• No Related Posts found

One of the major announcements at Oracle Open World last week was the launch of Oracle Enterprise Manager 12c, though I’m going to refer to the product as em for the rest of this blog. EM is a product that I both love and which completely infuriates me from time to time. Its worth understanding my reasons for this attitude before we look at the new release. First up then why I love it.

• Database Performance Pages
  The database performance pages, also available in database control of course, by and large focus on exactly the right things, namely response time and throughput. Moreover they provide a clear picture of database performance that is straightforward for humans to understand. A good picture almost always gives clarity and understanding more quickly and to a wider audience than text, as Florence Nightingale so eloquently understood about 150 years ago. The problem with many data visualisations is that they hide the core message, for example here. The em performance pages nearly always show the right thing clearly.
• Central Repository. So many infrastructure management tasks benefit from a central repository of observations. I’d hazard a guess though that most database infrastructure management is still done via scripts. This approach means that items like capacity planning, comparison of time periods and so on are not readily available or rely on the knowledge of the individual administrator both of the product and the environment that they are administering.
• Management templates. In my experience many environments end up with either different versions of common scripts monitoring their infrastructure or else different subsets of scripts monitoring the infrastructure.

This release brings a number of significant architecture and UI changes. I thought it would be useful to evaluate the new release, and especially compare it to the lists above. My usual approach when looking at a new Oracle product is to fire up a new CentOS vm via Virtual Box  . Then in conjunction with the documentation available at the OTN docs site. For EM10 this is a perfectly reasonable approach. With em11 I also was able to get away with it. For em12 the minimum specs haven’t changed much from 11, but you really do need them. That means you will want

• A database server with at least 2g ram available for the db.
• An application server with at least 4gb ram available.

For the purposes of this exercise therefore I setup an Amazon AWS VPC environment as follows.

• em repository machine – type m1.large which means 7.5g ram and high i/o capacity.
• em app server – type m1.large
• db target server – also type m1.large though I could have got away with a small server here.

Possibly Related Posts:

• Not So Smart
• Using a Windows Authenticated Proxy Server with EM11g
• Metric Collection Error
• Mass Agent Deployments
• Backward Compatability

Well here’s a blog entry I didn’t think I’d write. One that comes as a direct result of a request from Oracle Corp, specifically Oracle Education. The email I received is reproduced below.

Today we are releasing an Oracle Database Job Task Analysis Survey to determine what tasks are important and relevant to Oracle Database Administrators as we look to define future Oracle Database Certification and Curriculum Offerings.

We would really appreciate if if you could help us by posting this information on your Database related Blog sites.

Take the Oracle DBA Job Task Survey!…
Are you an Oracle Database Administrator? Would you like to help define the depth and scope of future Oracle Database training and certification? Join with other Oracle experts to take an online survey and tell us what tasks are important to you.
Learn More

I took the survey which you can find here last night. Overall I’m happy to help publicize the survey for the following reasons.

• Practitioners who actually do the job are probably a better judge of task importance than educators.
• Practitioners who actually do the job are probably a better judge of task importance than consultants like me.
• The OCP needs improvement and focus to remain of value.
• The OCP needs in my opinion anyway to provide proof of practical expertise and not book learning.

All of these issues can be addressed to some extent by actually asking practitioners what they do and don’t find useful in the real world. One thing I wasn’t expecting, but only because I didn’t read the welcome page given how I’d got there, was the fact that you get a download of the 11g Interactive Reference guide at the end of the survey. Anyway please do take the survey, its short, focussed and worthwhile. If like me you occasionally whine about the OCP here’s your chance to contribute at least something.

Possibly Related Posts:

• No Related Posts found

It’s been a few months since I did an install of Oracle Enterprise Manager 11g. I am however talking about some experiences from a real world implementation that I performed a while back at the UKOUG’s Management and Infrastructure SIG on the 27th. (you can book for this event here ) I thought therefore that it made sense to revisit the install again.

For those that don’t know EM 11.1 is built upon Weblogic Server 10.3.2. This is a new technology for Oracle DBA staff and is updated by means of the Smart Update Utility. This utility has to be invoked during the installation of a base weblogic server for Grid control because there are some mandatory patches. The installation guide and various guides you’ll find on the internet talk about using smart update to download those patches from Oracle Support. This isn’t too much of a hassle in my view since you’ll want to allow internet communications with Oracle Support for your OMS server anyway to get the most out of the product.

I duly downloaded WebLogic Server, installed the base product and then ran Smart Update. The very first dialog box presented to me asked me to update the updater (and that happens far too frequently these days in my opinion). This I did. I then reran the updater, but there is now no option for obtaining patches from Oracle Support. It turns out that this is a new feature in Smart Updater 3.3.0, rather than download patches through the updater, all patching is essentially offline. The process is now.

• log into MOS via a web browser
• find and download the patches you require
• copy them to the patch cache directory for each instance of Weblogic Server you have (this can be on shared storage)
• manually unzip the patches
• run the patch updater and it will detect the patches and allow application.

Possibly Related Posts:

• First Impressions of EM12C
• oracle.com inaccessible
• Intellisense
• Using a Windows Authenticated Proxy Server with EM11g
• Metric Collection Error

A somewhat recursive post here. There are a number of forums around the internet which Oracle professionals increasingly use to research various issues, discover new features and diagnose problems. One such is the relatively new Database Administrators stack exchange site. For various reasons I came across this thread and in particular a piece of advice on logging long running queries using an autonomous transaction.

Personally, I suggest using autonomous transactions to enable this feature — not on the transaction itself, but as a logging mechanism letting you know what is going on. For example, you could have PROCEDURE LONG_ACTION call PROCEDURE WRITE_LOG_ENTRY (defined as an autonomous transaction) that would write a VARCHAR2 to another table. Autonomous transactions do NOT interfere with your current transaction and so you can see what’s going on via your logging entries regardless of a COMMIT or ROLLBACK in your current transaction. That said, you can’d do that with one massive DML statement; you’d have to use a loop.

Now it seems to me at least that the caveats I have highlighted above are somewhat underplayed. There are 2 issues here:

• The original transaction is affected – in fact it is re-written entirely as a series of single row updates in a loop (a.k.a slow by slow) rather than a single update loop.
• The result of this is that you will get logs, but the runtime will grow potentially orders of magnitude. This is unlikely to be what you want.

So there you are: 2 pieces of advice on the Internet. How to choose between them? I suggest testing them out.  The script below tests the scenario above. I have a ‘hairy calculation’ namely a function to tell whether a given positive integer is prime, I want to run it through the first ten thousand positive integers and update a table of primes accordingly. As sieving through primes is computationally intensive I expect this to take a measurable amount of time. Lets see shall we.

/*

Script to demonstrate problems with

http://dba.stackexchange.com/questions/2994/oracle-any-way-to-view-uncommited-changes-to-a-particular-table/3051#3051

*/

-- create a table of all numbers from 1 to 1m
drop table primes purge;

create table primes (real,is_prime)
as select rownum,0 from dual
connect by level <= 10000;

-- create a function to test 'primeness'
create or replace function is_prime(p in number) return number
is
        l_limit number;
        i       number := 3;
        retval  number := 1;  -- returns 0 if false, 1 if true
begin
        l_limit := sqrt(p);
        if p = 1 then
                retval:=1;
        end if;
        if p = 2 then
                retval :=1;
        end if;

        if mod(p,2) = 0 then
                retval := 0;
        end if;

        while (i <= l_limit) loop
           if mod(p,i) = 0 then
                        retval := 0;
           end if;
           i := i + 2; -- (no need to test even numbers)
        end loop;
        return retval;
end;
/

-- Create proposed function
drop table log_entries purge;

CREATE TABLE LOG_ENTRIES(
    activity_date  date,
    log_entry varchar2(2000)
);

create or replace PROCEDURE WRITE_LOG_ENTRY
                        ( str VARCHAR2 )
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO LOG_ENTRIES VALUES ( SYSDATE, str );
    COMMIT;
END;
/

-- now do the tests

create or replace PROCEDURE LONG_ACTION_SUGGESTED IS
    c NUMBER;
BEGIN
    FOR r IN ( SELECT real FROM PRIMES )
    LOOP
       c := c + 1;
       UPDATE PRIMES z
          SET IS_PRIME = IS_PRIME(real)
        WHERE z.real = r.real;
       IF MOD(c,500) = 0 THEN
           WRITE_LOG_ENTRY ( c || ' rows processed.' );
       END IF;
    END LOOP;
    COMMIT;
END;
/	

create or replace PROCEDURE LONG_ACTION IS
BEGIN
    UPDATE PRIMES
          SET IS_PRIME = IS_PRIME(real);
    COMMIT;
END;
/	

set timing on
prompt ' run the update in sql without logging'
update primes set is_prime = is_prime(real);
commit;

prompt ' run the suggested logging approach'
exec LONG_ACTION_SUGGESTED;

prompt ' run from pl/sql approach'
exec LONG_ACTION;

In this example I time how long my update takes when run from sql, how long when run from pl/sql and how long when modified in the manner suggested by the article. My results are below.

' run the update in sql without logging'

10000 rows updated.

Elapsed: 00:00:01.08

Commit complete.

Elapsed: 00:00:00.00
' run the suggested logging approach'

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.98
' run from pl/sql approach'

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.26

In other words what took just a second without my autonomous transaction took 8 seconds with it.  The test is easily reconfigurable though – running with 100000 rows in the table produced the following results.

Elapsed: 00:00:00.01
' run the update in sql without logging'

100000 rows updated.

Elapsed: 00:00:21.55

Commit complete.

Elapsed: 00:00:00.01
' run the suggested logging approach'

PL/SQL procedure successfully completed.

Elapsed: 00:07:26.05
' run from pl/sql approach'

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.50

That is we jumped from 20 seconds or so to 7 and half minutes.

So next time you see some advice on the internet, it might well be worth your while testing it out first, especially if the author provides you with the tools to allow you to do this.

Possibly Related Posts:

• Instrumentation Overhead
• Proof by extension..
• Distinctly Odd (Update)
• Distinctly Odd

This post is by way of reply to Richard Foote’s latest quiz available here Richard wants to know how many columns a histogram will be collected on given certain conditions. I believe that the answer depends on the database version (so 9i will behave differently from 10.2 and above for example). For my tests I’m using the script below:

The aim of the script is to run the suggested gather stats routine with no workload, with workloads where each query only queries one column (as per Richard’s example where clause) and where different queries query each of the columns, as per Richard’s description. I then add a single row and see what happens to the histograms.

Update

It is worth comparing the script below with a script that populates data using the mod function.

/*

File :      Histograms.sql
Date :		Aug 2011 (just)
Purpose:	Demo the quiz in http://richardfoote.wordpress.com/2011/08/31/method_opt-size-auto-quiz-automatic-for-the-people/

*/

-- create the table
drop table afp purge;

create table afp(
	c1	number
,	c2	number
,	c3 	number);

-- now populate
begin
	for i in 1..1000000 loop
		insert into afp(c1,c2,c3) values (i,mod(i,254),mod(i,254));
	end loop;
	commit;
end;
/

prompt 'Print Version'
select banner from v$version;

exec dbms_stats.gather_table_stats(user,'AFP',method_opt => 'FOR ALL COLUMNS SIZE AUTO');
col column_name format a30
prompt 'Histogram Info with no queries'
select
	column_name
,	count(*)
from user_histograms
where
	table_name = 'AFP'
group by
	column_name
order by
	column_name
;
-- run my sample multicolumn queries
-- use a pl/sql routine so as I get bind vars
-- doesn't have to be

declare
   q1count number := 10;
   q2count number := 10;
   q3count number := 10;
   retval  number;
begin
-- query 1
for i in 1..q1count loop
	select count(*) into retval
	from afp where
	c1 = round(dbms_random.value(1,100000));
end loop;

for i in 1..q2count loop
	select count(*) into retval
	from afp where
	c2 = round(dbms_random.value(1,100000));
end loop;

for i in 1..q3count loop
	select count(*) into retval
	from afp where
	c3 = round(dbms_random.value(1,100000));
end loop;

end;
/

exec dbms_stats.gather_table_stats(user,'AFP',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

prompt 'Histogram Info with single col queries'
select
	column_name
,	count(*)
from user_histograms
where
	table_name = 'AFP'
group by
	column_name
order by
	column_name
;

-- run my sample multicolumn queries
-- use a pl/sql routine so as I get bind vars
-- doesn't have to be

declare
   q1count number := 10;
   q2count number := 10;
   q3count number := 10;
   retval  number;
begin
-- query 1
for i in 1..q1count loop
	select count(*) into retval
	from afp where
	c1 = round(dbms_random.value(1,100000)) and c2 = i*i and c3 = round(dbms_random.normal()*i,0);
end loop;

for i in 1..q2count loop
	select count(*) into retval
	from afp where
	c3 = round(dbms_random.value(1,100000)) and c1 = i*i and c2 = round(dbms_random.normal()*i,0);
end loop;

for i in 1..q3count loop
	select count(*) into retval
	from afp where
	c2 = round(dbms_random.value(1,100000)) and c3 = i*i and c1 = round(dbms_random.normal()*i,0);
end loop;

end;
/

exec dbms_stats.gather_table_stats(user,'AFP',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

prompt 'Histogram Info with queries on all columns'
select
	column_name
,	count(*)
from user_histograms
where
	table_name = 'AFP'
group by
	column_name
order by
	column_name
;

prompt 'add data '

insert into afp VALUES (1000001, 42, 99999999);
commit;

exec dbms_stats.gather_table_stats(user,'AFP',method_opt => 'FOR ALL COLUMNS SIZE AUTO');
col column_name format a30
prompt 'Histogram Info with no queries'
select
	column_name
,	count(*)
from user_histograms
where
	table_name = 'AFP'
group by
	column_name
order by
	column_name
;
-- run my sample multicolumn queries
-- use a pl/sql routine so as I get bind vars
-- doesn't have to be

declare
   q1count number := 10;
   q2count number := 10;
   q3count number := 10;
   retval  number;
begin
-- query 1
for i in 1..q1count loop
	select count(*) into retval
	from afp where
	c1 = round(dbms_random.value(1,100000));
end loop;

for i in 1..q2count loop
	select count(*) into retval
	from afp where
	c2 = round(dbms_random.value(1,100000));
end loop;

for i in 1..q3count loop
	select count(*) into retval
	from afp where
	c3 = round(dbms_random.value(1,100000));
end loop;

end;
/

exec dbms_stats.gather_table_stats(user,'AFP',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

prompt 'Histogram Info with single col queries'
select
	column_name
,	count(*)
from user_histograms
where
	table_name = 'AFP'
group by
	column_name
order by
	column_name
;

-- run my sample multicolumn queries
-- use a pl/sql routine so as I get bind vars
-- doesn't have to be

declare
   q1count number := 10;
   q2count number := 10;
   q3count number := 10;
   retval  number;
begin
-- query 1
for i in 1..q1count loop
	select count(*) into retval
	from afp where
	c1 = round(dbms_random.value(1,100000)) and c2 = i*i and c3 = round(dbms_random.normal()*i,0);
end loop;

for i in 1..q2count loop
	select count(*) into retval
	from afp where
	c3 = round(dbms_random.value(1,100000)) and c1 = i*i and c2 = round(dbms_random.normal()*i,0);
end loop;

for i in 1..q3count loop
	select count(*) into retval
	from afp where
	c2 = round(dbms_random.value(1,100000)) and c3 = i*i and c1 = round(dbms_random.normal()*i,0);
end loop;

end;
/

exec dbms_stats.gather_table_stats(user,'AFP',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

prompt 'Histogram Info with queries on all columns'
select
	column_name
,	count(*)
from user_histograms
where
	table_name = 'AFP'
group by
	column_name
order by
	column_name
;

My Results are as follows
11.2.0.2

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

PL/SQL procedure successfully completed.

'Histogram Info with no queries'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                      2
C3                                      2

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

'Histogram Info with single col queries'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                    254
C3                                    254

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

'Histogram Info with queries on all columns'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                    254
C3                                    254

'add data '

1 row created.

Commit complete.

PL/SQL procedure successfully completed.

'Histogram Info with no queries'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                    254
C3                                    235

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

'Histogram Info with single col queries'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                    254
C3                                    235

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

'Histogram Info with queries on all columns'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                    254
C3                                    236

10.2.0.4

SQL> @test
drop table afp purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist 

Table created.

PL/SQL procedure successfully completed.

'Print Version'

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE	10.2.0.4.0	Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production                                          

PL/SQL procedure successfully completed.

'Histogram Info with no queries'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                      2
C3                                      2                                       

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

'Histogram Info with single col queries'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                    254
C3                                    254                                       

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

'Histogram Info with queries on all columns'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                    254
C3                                    254                                       

'add data '

1 row created.

Commit complete.

PL/SQL procedure successfully completed.

'Histogram Info with no queries'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                    254
C3                                    254                                       

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

'Histogram Info with single col queries'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                    254
C3                                    254                                       

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

'Histogram Info with queries on all columns'

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
C1                                      2
C2                                    254
C3                                    254                                       

So in 11.2.0.2 the addition of the single column tips the optimizer over from a frequency histogram to a height balanced histogram, just by virtue of performing the insert. For 10.2.0.4 no difference is seen even after running my sample queries.

Possibly Related Posts:

• No Related Posts found

Quick post primarily for Jeff Smith Web|Twitter (and Gary Myers Web|Twitter who already knows this stuff). It arose from this tweet.

hillbillyToad @syd_oracle just seriously freaked me the heck out http://bit.ly/niyXo9 Does /* +APPEND */ automatically suspend logging? #oracle

To which my answer was the classic “It Depends”, namely

nlitchfield @hillbillyToad @syd_oracle It *should* depend. /*+ APPEND */ implies direct path, & direct path logging is determined by segment attribute.

Here’s a quick demo of what happens in a normal db with force logging disabled.

SYS @ NIALL1 >conn niall/niall
Connected.
NIALL @ NIALL1 >drop table log_obj;

Table dropped.

NIALL @ NIALL1 >drop table nolog_obj;

Table dropped.

NIALL @ NIALL1 >create table log_obj as select * from all_objects where 1=2;

Table created.

NIALL @ NIALL1 >create table nolog_obj as select * from all_objects where 1=2;

Table created.

NIALL @ NIALL1 >alter table nolog_obj nologging;

Table altered.

NIALL @ NIALL1 >set autotrace on statistics
NIALL @ NIALL1 >insert /*+ APPEND */ into log_obj select * from all_objects;

72693 rows created.

Statistics
----------------------------------------------------------
       1280  recursive calls
       1744  db block gets
      50108  consistent gets
       1941  physical reads
    8573084  redo size
        822  bytes sent via SQL*Net to client
        811  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       1576  sorts (memory)
          0  sorts (disk)
      72693  rows processed

NIALL @ NIALL1 >commit;

Commit complete.

NIALL @ NIALL1 >insert /*+ APPEND */ into nolog_obj select * from all_objects;

72693 rows created.

Statistics
----------------------------------------------------------
        522  recursive calls
       1742  db block gets
      49039  consistent gets
       1065  physical reads
      50280  redo size
        823  bytes sent via SQL*Net to client
        813  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       1548  sorts (memory)
          0  sorts (disk)
      72693  rows processed

NIALL @ NIALL1 >commit;

Commit complete.

NIALL @ NIALL1 >

In summary, if you specify the APPEND hint and the underlying segment has the NOLOGGING attribute set (in the case of an index or if the underlying table has the nologging attribute set) you won’t get redo on the data generation (though there is some for the data dictionary changes). You’ll also need to rebuild indexes as well.

Possibly Related Posts:

• No Related Posts found

Those of you who were here earlier will have seen a post questioning a story on the BBC by Pallab Gosh, though its main thrust was to question the blind use of ratios. I used as my source the 2003-2010 figures available at http://www.ucas.com/about_us/stat_services/stats_online/annual_datasets_to_download/ which seem to show Physics had 4300 or so applicants in 2010. Pallab used the Press release for 2011 (so a different year) available at http://www.ucas.com/about_us/media_enquiries/media_releases/2011/20110531 which seem to show Physics had 24000 applicants this year up 17% from 2010. Clearly these aren’t consistent so until I discover who is wrong (clue probably me) I have removed this post from public view.

Possibly Related Posts:

• No Related Posts found

So another My Oracle Support Update at the weekend. Today I get the following results when searching the knowledge base:

Thanks a bunch, Oracle! We pay for this stuff and you continually screw it up in basic ways. And no Chrome is not an unusual browser. And yes flash is up to date. It isn’t good enough.

Possibly Related Posts:

• More on oracle.com
• oracle.com inaccessible
• Geographical Revisionism

I’m all alone, so are we all
We’re all clones
All are one and one are all

~ Alice Cooper

Two separate discussions, one at work and one around the virtual OakTable , have made me aware that maybe not as many Oracle professionals as I imagined are aware that Oracle provides a facility to clone Oracle Software homes, either to a new home on the same machine, or to a new home on a brand new machine with the same build. This process is far quicker than the graphical installation routine, or even than a straightforward silent install using OUI. It also lends itself very neatly to quickly provisioning identical builds of database servers. I thought therefore I’d jot down my notes on how I recently did just that for a data guard demo environment.

Servers Involved

I had 2 servers involved here, their setup is as follows:

The servers are both running my favourite RHEL clone CentOS, in this case the Release 5.6.

Initial Setup

After installing the O/S, updating the kernel for security patches I installed the following products in /u01/app/oracle/product/11.2.0/dbhome_1 on node1.

• Oracle Database 11.2.0.2
• Oracle July PSU (12419331 )

The above process took approximately 2 hours including media download and basic server documentation.

Gold Image

At this point I could have simply repeated the process on node 2. However cloning is much easier and repeatable.  First I made a Gold Image by running the following as root on node 1. /media/dsl/database/linux/11.2 is a mount point containing my gold images.

cd /u01/app/oracle/product/11.2.0/dbhome_1
tar -cpvzf /media/dsl/database/linux/11.2/db112023.tgz .

Cloning

On the second machine I also mounted my DSL as above and then ran the following script

./installOracle.sh /media/dsl/database/linux/11.2/db112023.tgz /u01/app/oracle/product/11.1.0/dbhome_1 /u01/app/oracle OracleHome1

where installOracle.sh reads as follows

#!/usr/bin/ksh
#
# Install gold image of Oracle using clone home functionality of OUI.
#
# Expects parameters <name of image> <install_loc> <Oracle Home Name>
# Assumes Pre-Reqs are met -- TODO add pre-req fixing
# assumes unzip installed
#
if [[ $# -ne 4 ]]; then
 echo "Usage : installOracle.sh imageName instLoc oracle_base HomeName";
 echo " : eg installOracle.sh /media/dbhome111.tgz /u01/app/oracle/product/11.1.0/db_1 /u01/app/oracle OracleHome11g"
 exit 1;
fi

IMAGE=$1
INSTLOC=$2
ORABASE=$3
HOMENAME=$4

echo "Making Oracle Home Directory"
mkdir -p ${INSTLOC}
chown -R oracle:oinstall ${ORABASE}
cd ${INSTLOC}
echo "Extracting Archive at $(date +%Y%M%d%m)"
tar -xvzf ${IMAGE}
cd clone/bin
echo "Cloning Home........."
sudo -u oracle perl clone.pl ORACLE_HOME="${INSTLOC}" ORACLE_HOME_NAME="${HOMENAME}" ORACLE_BASE="${ORABASE}" -ignoreSysPrereqs
${INSTLOC}/root.sh
echo "Install Finished at $(date +%Y%M%d%m)"
echo "Please run any non root.sh scripts as instructed above. root.sh has been run"

This code has to be run as root. It does the following

• creates the home directory
• extracts the gold image from the tarball
• runs a perl script as the oracle user
• runs root.sh

This script completed in less than 10 minutes on an identical virtual machine to the one that had taken 2 hours to perform the complete install and patching process.

Metalink Reference : How to clone an Oracle Database Home using OUI 

Possibly Related Posts:

• No Related Posts found