I received some emails recently asking were will be my next presentations, and to make it public here we go:

• Insync’10, Melbourne August 16 – Topic: Tips and Best Practices for DBAs @ 9:55AM to 10:40 AM more info at  http://www.insync-conference.com.au/
• NZOUG OTN Days, August 18 in Auckland and August 20 in Wellington – Topic How to Improve your Oracle Career more info at http://www.nzoug.org
• Sangam’10,  Hyderabad September 3-4 – Topics: Making magic with Oracle & Tips and Best Practices for DBAs more info at http://www.aioug.org/sangam10.php

Kind Regards,

Francisco Munoz Alvarez

Hi All,

NZOUG (New Zealand Oracle Users Group) and LAOUC (Latin American Oracle Users Communty) is organizing a serie of webinars to Oracle Community (over 42 Webinars) with the participation of several Oracle Celebrities, and is my desire to share the first 8 webinars recorded with all Oracle Users around the world!

Please find bellow all the webinars recorded in the past 2 months for LAOUC and NZOUG, I want to express a special thank you to: Arup Nanda, Daniel Morgan, Debra Lilley, Alex Gorbachev and Ben Prusinski for their invaluable participation (Alex Webinar recording is coming soon) and  for the incredible desire to share their knowledge with the community!

May was a fantastic month for networking and fun in Europe! Orcan Spring Conference and EMEA Harmony were two great conferences and my sincere congratulations to all the people involved to the success of both events. In these events I had the opportunity to see again some great friends like Joze Senegacnik, Daniel Morgan, Heli Helskyaho and Tom Kyte and also gave me the opportunity to make some new ones like: Piet de Visser, Debra Lilley, Chris J. Date,  Dimitri Gielis,  Mogens Norgaard, Tanel Poder,Tiina Lehtovaara, Patrik Norlander, Jesper Karlsson, Andrejs Vorobjovs, Hüsnü Şensoy, Luca Canali, Stanley and a lot more of great and fantastic people!

Bellow you can see some photos of the trip (more are available in my facebook), you can see some good friends and  moments, also Debra cooking Stanley and later in the day shooting the poor Stanley, he inclusive end the trip looking for some kind of protection against Debra as you can see bellow;)

I really loved these events and hope to be invite again next year! Soon will post my presentations here.

Hope to see everyone soon!

Francisco Munoz Alvarez

Sometimes understanding the Oracle Database Editions is a little confuse, and on my opinion know all the options available in the market is something very important and fundamental for any DBA. The main idea of this post is to help you to understand a little bit more about all available options in the Oracle market, learn some key differences and functionalities of each Edition.

Understanding the Different Editions

All the time I receive questions regarding Oracle Database Editions like:

• What’s the difference between Standard Edition and Enterprise Edition?  
• How complicate is to upgrade a Standard Edition Database to Enterprise Edition and vice versa?   
• Can you explain me the most important Options and Packages available with Oracle?  
• Is Oracle Express Edition really free? What are the limitations and what it can do?

So many people don’t understand the diversity of Editions available with Oracle Databases, most of the time everyone associate it with the Enterprise Edition only, saying “Oracle is so expensive! Let’s look to other brands due that we can’t afford it!”, That’s a huge mistake, if you want the best available in the market you need to pay for it, but you need to understand all the options available for you by Oracle Corporation before take a precipitate and wrong decision, you always can find an edition that is affordable for your budget, and I can guarantee that you will not find the same performance and functionality at any other product in the market.

This is like buy a car, you can always dream to have a top brand vehicle, but you don’t need to buy the most expensive model if you only need a compact one, it will be more affordable and you know that you can trust in the quality of the product. The same happens with Oracle Databases. Oracle have available several editions of their Database product, all are the same product (internal code) but depending of the edition it can have some limitations or additional options to improve the performance, security, availability, and management of your 11g Database.

Oracle Database 11g is available in a choice of editions tailored to meet your business, personal, or IT needs, and also offers several extra options to enhance your Database capabilities depending of your applications requirements. Oracle have basically 5 different editions available for you, all are build using the same reliable database engine architecture and are compatible with each other, making this way an upgrade to be a very easy task to the DBA and this will also help to make your database grow at the same speed of your business.
The Editions available are: Express Edition, Personal Edition, Standard One Edition, Standard Edition and Enterprise Edition, now let’s take a quick look on each one:

• Oracle Express Edition (XE): This edition is an entry-level, small-footprint database based at the Oracle 10g release 2 Enterprise code that is free to develop, deploy, and distribute; due to it, this is an excellent option for developers, students, and very small organizations trying to initiate their journey in the Oracle world. Oracle XE is very easy to download and very simple to administrate, it can be installed on any host machine size, with any number of CPU’s and memory available, but it will have several limitations like: will storage only 4GB of data, will use up to 1GB of memory, and will use only 1 CPU in the host machine. Currently this edition is not supported by My Oracle Support (old Metalink), this means no patches available and support, but you can always refer to the OTN forums to ask your peers for any kind of help relate to this edition.
• Oracle Personal edition (PE): This edition supports single-user development and deployment environments that require full compatibility with Oracle Database Standard Edition One, Oracle Database Standard Edition, and Oracle Database Enterprise Edition. Oracle PE includes all of the components, options and functionalities that are included with Enterprise Edition with the exception of the Oracle Real Application Clusters (RAC) option. The negative side of this edition is that this is only available for Windows environments (Windows 2000, Windows NT, Windows XP, Windows Vista, and Windows Server 2003/32-bit and 64-bit versions), and the Management Packs are not included.
• Oracle Standard Edition One (SEO): Delivers an unprecedented ease to use, power, price/performance for workgroups, department-level, and Web applications. This edition is highly recommended to be used from single-server environments for small business to any highly distributed branch environments. Oracle Database SEO includes all the facilities necessary to build any business-critical applications.
• Oracle Standard Edition (SE): Offers a low cost alternative for small/medium business or departmental applications that want to have the power of Oracle, this Edition has all the benefits of Oracle SEO plus support for larger machines and clustering of services with Real Application Clusters (Free in this Edition); RAC was not included in previous Standard Edition releases prior to Oracle Database version 10g.
• Oracle Enterprise Edition (EE): It’s the top of the Oracle Editions line, provides the best in performance, availability, scalability, and security required for mission-critical applications such as high-volume online transaction processing (OLTP) applications, query-intensive data warehouses, and demanding Internet applications. Oracle Database EE contains all of the components of Oracle Database, and can be further enhanced with the purchase of several available options and packs.

Now let’s take a closer look to all availabilities and restrictions of each Oracle Edition:

Now you have a clear idea of all available Editions of Oracle in the market, in the next post I’ll go thru the most important options and packs available to enhance your database to achieve all your personal or company goals.

Cheers,

Francisco Munoz Alvarez

One of my DBA’s just ask me why he was receiving the following error each time he tried to setup the host information thru the dbconsole (OEM):

- RemoteOperationException: ERROR: NMO not setuid-root (Unix-only)

The answer is very easy, this error occurs when you forgot to run the root.sh after you finished your installation, you can solve this problem easily running the root.sh  which is located in $ORACLE_HOME/root.sh (as root) and the  error will disappear.

Kind Regards,

Francisco Munoz Alvarez

By Francisco Munoz Alvarez

How to find Sessions Generating Lots of Redo

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.

The methods are:

1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 i.block_changes

3 FROM v$session s, v$sess_io i

4 WHERE s.sid = i.sid

5 ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. These view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 t.used_ublk, t.used_urec

3 FROM v$session s, v$transaction t

4 WHERE s.taddr = t.addr

5 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.

You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

Useful Scripts

To see the redo generated since instance started:

col name format a30 heading ‘Statistic|Name’

col value heading ‘Statistic|Value’

start title80 “Redo Log Statistics”

spool rep_out\&db\red_stat

SELECT name, value

FROM v$sysstat

WHERE name like ‘%redo%’

order by name

/

spool off

pause Press enter to continue

ttitle off

The redo generated during my session since the session started:

select value redo_size

from v$mystat, v$statname

where v$mystat.STATISTIC# = v$statname.STATISTIC#

and name = ‘redo size’

/

The redo generated by current user sessions:

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null

order by value

/

Provide a current status for redo logs:

column first_change# format 999,999,999 heading Change#

column group# format 9,999 heading Grp#

column thread# format 999 heading Th#

column sequence# format 999,999 heading Seq#

column members format 999 heading Mem

column archived format a4 heading Arc?

column first_time format a25 heading First|Time

break on thread#

set pages 60 lines 132 feedback off

start title132 ‘Current Redo Log Status’

spool rep_out\&db\log_stat

select thread#, group#, sequence#,

bytes, members,archived,status,first_change#,

to_char(first_time,’dd-mon-yyyy hh24:mi’) first_time

from sys.v_$log

order by thread#, group#;

spool off

pause Press Enter to continue

set pages 22 lines 80 feedback on

clear breaks

clear columns

ttitle off

/

Provide redo log groups and log switch (archive generation) information:

set echo on

set linesize 150

set pagesize 500

column day format a16 heading ‘Dia’

column d_0 format a3 heading ‘00′

column d_1 format a3 heading ‘01′

column d_2 format a3 heading ‘02′

column d_3 format a3 heading ‘03′

column d_4 format a3 heading ‘04′

column d_5 format a3 heading ‘05′

column d_6 format a3 heading ‘06′

column d_7 format a3 heading ‘07′

column d_8 format a3 heading ‘08′

column d_9 format a3 heading ‘09′

column d_10 format a3 heading ‘10′

column d_11 format a3 heading ‘11′

column d_12 format a3 heading ‘12′

column d_13 format a3 heading ‘13′

column d_14 format a3 heading ‘14′

column d_15 format a3 heading ‘15′

column d_16 format a3 heading ‘16′

column d_17 format a3 heading ‘17′

column d_18 format a3 heading ‘18′

column d_19 format a3 heading ‘19′

column d_20 format a3 heading ‘20′

column d_21 format a3 heading ‘21′

column d_22 format a3 heading ‘22′

column d_23 format a3 heading ‘23′

column Total format 9999

column status format a8

column member format a40

column archived heading ‘Archived’ format a8

column bytes heading ‘Bytes|(MB)’ format 9999

Ttitle ‘Log Info’ skip 2

select l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type

from v$log l, v$logfile f

where l.group# = f.group#

/

Ttitle off

prompt =========================================================================================================================

Ttitle ‘Log Switch on hour basis’ skip 2

select to_char(FIRST_TIME,’DY, DD-MON-YYYY’) dia,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0))) d_0,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0))) d_1,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0))) d_2,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0))) d_3,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0))) d_4,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0))) d_6,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0))) d_7,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0))) d_9,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0))) d_10,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0))) d_11,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0))) d_12,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0))) d_13,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0))) d_14,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0))) d_15,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0))) d_16,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0))) d_17,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0))) d_18,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0))) d_19,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0))) d_20,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0))) d_21,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0))) d_22,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0))) d_23,

count(trunc(FIRST_TIME)) Total

from v$log_history

group by to_char(FIRST_TIME,’DY, DD-MON-YYYY’)

order by to_date(substr(to_char(FIRST_TIME,’DY, DD-MON-YYYY’),5,15) )

/

Ttitle off

How to check for LOGGING/NOLOGGING objects in the DB:

Two example methods of querying the database for this information:

select owner , table_name, index_name
from dba_indexes
where logging=’NO’;

select tablespace_name, logging
from dba_tablespaces

/

Kind Regards,

Francisco Munoz Alvarez

Thanks & Regards:

M K SINGH

>>>>>

Dear Singh,

 It’s very hard to tell you the diferrences between each version, it’s a lot more easy for me to show you the new features of each one, here are a small resume (not a full list) of what was new on each version you asked for:

As happened with Oracle 9i, Oracle 10g has many enhancements to support largest database size limits and continues to remove the complexity from the DB to make it easier to manage and to reduce the total cost of ownership of it. 

Cheers,

Francisco Munoz Alvarez

If you are using LINUX/Windows the hostname credentials should be the same user who has installed the oracle  database software, if Windows the user you are using need to be member of the ORA_DBA group and have Local Security privilege ‘Log on as a batch job’.

What you need to do is go to:

 Start—->

Programs—->

Admin tools—->

Local security policy —>

Local policies —>

User rights assignments

In the list to your right, look for ‘Log on as a batch job’, double click and add the same OS local user you using to access OEM web to the group.

I hope this tips will help you  solve your problem

Cheers,

Francisco Munoz Alvarez

Here is the scripts to help you to get this information:

Cheers,

Francisco Munoz Alvarez

This procedure can be used for single instance or RAC (9i to 11g):

In the 32 bit source server :

Start making a full backup of the database including all archivelogs:

RMAN> run  {  

allocate channel c1 type disk;  

allocate channel c2 type disk;  

backup database plus archivelog;  

backup current controlfile;  }

In the target 64 bit server:1) Copy backup files from source server
2) Create the pfile or copy the pfile fromsourceserver
3) Startup nomount the new instance.
4) Connect to rman and set the dbid:

$ rman target / nocatalog RMAN> set dbid=<dbid from the 32 server>;

5) Restore the controlfile:

RMAN> restore controlfile from ‘/disk1/backup_32/backup_piece_name’;

RMAN> alter database mount;

RMAN> restore database;

6) Identify the archivelog till you want to do the recovery and check if you have all the backups you need to do it and run:

RMAN> run {

set until sequence xxxx;

recover database;

}

RMAN> exit

7) Now Migrating to 64 bit:

SQL> recover database until cancel using backup controlfile;

cancel

SQL> alter database open resetlogs migrate;

SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> shutdown immediate;

SQL> startup

Note: Also please take a look in other methods in the following Metalink Note:

• Migration of Oracle Instances Across OS Platforms - Doc ID: Note:733205.1

Cheers,

Francisco Munoz Alvarez