DBA Blogs

I learn or relearn something new every day about Oracle.  Just about every day really!

Last week I was in Belgrade Serbia delivering a seminar and an attendee reminded me of something I knew once but had totally forgotten about.  It had to do with foreign keys and the dreaded NULL value.

Many of you might think the following to be not possible, we'll start with the tables:


ops$tkyte%ORA11GR2> create table p
  2  ( x int,
  3    y int,
  4    z int,
  5    constraint p_pk primary key(x,y)
  6  )
  7  /
Table created.


ops$tkyte%ORA11GR2> create table c
  2  ( x int,
  3    y int,
  4    z int,
  5    constraint c_fk_p foreign key (x,y) references p(x,y)
  6  )
  7  /
Table created.

Looks like a normal parent child relationship - a row may exist in C if and only if a parent row exists in P.  If that is true - then how can this happen:
ops$tkyte%ORA11GR2> select count( x||y ) from p;
COUNT(X||Y)-----------          0
ops$tkyte%ORA11GR2> select count( x||y ) from c;
COUNT(X||Y)-----------          1
There are zero records in P - none.  There is at least one record in C and that record has a non-null foreign key.  What is happening?
It has to do with NULLs and foreign keys and the default "MATCH NONE" rule in place.  If your foreign key allows NULLs and your foreign key is a composite key - then you must be careful of the condition where by only SOME of the foreign key attributes are not null.  For example - to achieve the above magic, I inserted:
ops$tkyte%ORA11GR2> insert into c values ( 1, null, 0 );1 row created.
The database cannot validate a foreign key when it is partially null.  In order to enforce the "MATCH FULL" option of a foreign key - you would want to add a constraint to your table:
ops$tkyte%ORA11GR2> alter table c add constraint check_nullness  2  check ( ( x is not null and y is not null ) or  3          ( x is null and y is null ) )  4  /Table altered.
That will ensure either:

• All of the columns are NULL in the foreign key
• None of the columns are NULL in the foreign key

As long as that constraint is in place - your foreign key will work as  you probably think it should work.
See also: http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_constraints.htm#ADFNS273

As published in the 102nd issue of the NoCOUG Journal THE WICKED WITCH OF THE WEST NEEDS HELP BE IT KNOWN BY THESE PRESENTS that the Wicked Witch of the West needs your help to create a magic spell to ensure that the Third Annual Witching & Wizarding Ball is a grand success. A great [...]

Martin Berger suggested to create this page to keep track of blog posts related SLOB IO testing. Here it is Martin ;) If you are seriously interested to get a good understanding of your IO subsystem performance characteristics then one of the tools that you may consider for putting your system IO on stress is SLOB. See Introducing [...]

In my previous post CURRENT_SCN and CHECKPOINT_CHANGE#, I had asked : "In which scenarios could the checkpoint_change# value differ between V$DATAFILE and V$DATAFILE_HEADER ?"




Here's a little demo :

SQL> create tablespace NEWTBS datafile '/tmp/newtbs.dbf' size 50M;

Tablespace created.

SQL> create table hemant.objcopy tablespace newtbs as select * from dba_objects;

Table created.

SQL> select file_id from dba_data_files where tablespace_name = 'NEWTBS';

   FILE_ID
----------
        15

SQL> alter system checkpoint;

System altered.

SQL> select f.checkpoint_change#, h.checkpoint_change#
  2  from v$datafile f, v$datafile_header h
  3  where f.file#=15 and h.file#=15 and f.file#=h.file#;

CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
           5312187            5312187

SQL> 

I then backup the tablespace :

RMAN> backup tablespace newtbs;

Starting backup at 20-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00015 name=/tmp/newtbs.dbf
channel ORA_DISK_1: starting piece 1 at 20-MAY-12
channel ORA_DISK_1: finished piece 1 at 20-MAY-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp tag=TAG20120520T230907 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-MAY-12

Starting Control File Autobackup at 20-MAY-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783817749_7vl28oln_.bkp comment=NONE
Finished Control File Autobackup at 20-MAY-12

RMAN> 

Next, I update the object(s) in the tablespace.

SQL> connect hemant/hemant    
Connected.
SQL> select segment_name from user_segments where tablespace_name = 'NEWTBS';

SEGMENT_NAME
--------------------------------------------------------------------------------
OBJCOPY

SQL> insert into objcopy select * from dba_objects;

76670 rows created.

SQL> update objcopy set owner = owner || '_1';

153301 rows updated.

SQL> commit;

Commit complete.

SQL> 
SQL> alter system switch logfile;

System altered.

SQL>

I then remove and restore the datafile :

SQL> connect / as sysdba
Connected.
SQL> !rm /tmp/newtbs*.dbf

SQL> alter database datafile 15 offline;

Database altered.

SQL> 
RMAN> restore datafile 15;

Starting restore at 20-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00015 to /tmp/newtbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp tag=TAG20120520T230907
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 20-MAY-12

RMAN> 

If I now query the V$DATAFILE and V$DATAFILE_HEADER views, I see :

SQL> select f.checkpoint_change#, h.checkpoint_change#
  2  from v$datafile f, v$datafile_header h
  3  where f.file#=15 and h.file#=15 and f.file#=h.file#
  4  /

CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
           5315901            5313637

SQL> 

I then RECOVER the datafile :

SQL> recover datafile 15;
ORA-00279: change 5313637 generated at 05/20/2012 23:09:08 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_05_20/o1_mf_1_1
3_7vl2f3nf_.arc
ORA-00280: change 5313637 for thread 1 is in sequence #13


Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
SQL> 
SQL> select f.checkpoint_change#, h.checkpoint_change#
  2  from v$datafile f, v$datafile_header h
  3  where f.file#=15 and h.file#=15 and f.file#=h.file#
  4  /

CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
           5321980            5321980

SQL> 

Note : The CHECKPOINT_CHANGE# has been incremented and both the views now show the same value. Bringing the datafile online again increments the CHECKPOINT_CHANGE#.

SQL> select f.checkpoint_change#, h.checkpoint_change#
  2  from v$datafile f, v$datafile_header h
  3  where f.file#=15 and h.file#=15 and f.file#=h.file#
  4  /

CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
           5322278            5322278

SQL> 

I could ask the question : Why is the CHECKPOINT_CHANGE# incremented for a datafile that was OFFLINE and [merely] RECOVERed ?  But I am sure that you know the answer now.

.
.
.

If you are wondering what I am busy with then this post explains it. As you may noticed I am still testing one of the Oracle systems using the SLOB framework and learning on my way. I ran several tests with the same parameters (Readers 24) and I noticed that for one reason or another awr.txt reports [...]

dbm_configurator.xls is needed to generate databasemachine.xml file which is needed by OEM 12c to discover an exadata cluster. Following is a step  by step process as how to generate databasemachine.xml file to be used with OEM 12c: 1) Get the dbm_configurator70.xls file from /opt/oracle.SupportTools/onecommand directory from the first compute node of Exadata cluster. 2) Also [...]

How to make the good design databases? That is the hard question. In my opinion, You were supposed to understand the theory, that can help. Database Design theory, Like Normalization or Normal Form.  Anyway, You can find out about them in the internet. By the way, I suggest to attend "C.J. Date's Database Design and Relational Theory: Normal Forms and All That Jazz Master Class" seminar.
You will learn about.
1. Preamble2. Normalization (= further normalization)a. Generalities b. FDs and BCNF  - informal  - formal  - preserving FDs  - FD axiomatizationc. A remark on denormalizationd. JDs and 5NF- informal- formal- implicit dependencies- the chasee. MVDs and 4NFf. Other normal forms- 6NF and RFNF3. Orthogonality4. Redundancy
However, You can purchase "C.J. Date's Database Design and Relational Theory: Normal Forms and All That Jazz Master Class" videos from O'reilly. You can take your time for learning and understand about Normal Forms and etc.

What will you see in Videos?- Preamble :- What design theory is and why its important. Logical vs. physical design. Redundancy and update anomalies.- A Review of Relational Basics :- Relations vs. relvars. Predicates. The importance of constraints.- Normalization - Preliminaries :- Nonloss decomposition. Normalization serves two purposes. Role of projection and join. The normal form hierarchy.- Normalization - FDs and BCNF (informal) - Part 1 :- What 1NF really is. Functional dependencies (FDs).- Normalization - FDs and BCNF (informal) - Part 2 :- Superkeys and subkeys. 2NF, 3NF. BCNF (the normal form with respect to FDs).- Normalization - FDs and BCNF (formal) :- A crucial mental shift. What FDs and BCNF really are. Heaths Theorem.- Normalization - Preserving FDs :- Good and bad decompositions; why the classical normalization procedure is inadequate. Irreducible covers. 3NF and BCNF algorithms.- Normalization - FD Axiomatization :- Reasoning about FDs. Armstrongs axioms. Soundness and completeness.- Denormalization :- A definition. Denormalization considered harmful. Role of star schemas.- Normalization - JDs and 5NF (informal) :- A surprising fact. Join dependencies (JDs). A relvar in BCNF and not 5NF. Tuple forcing JDs.- Normalization - JDs and 5NF (formal) - Part 1 :- What JDs and 5NF really are. JDs implied by keys.- Normalization - JDs and 5NF (formal) - Part 2 :- A useful theorem. Update anomalies revisited.- Normalization - Implicit Dependencies - Part 1 :- JD equivalence and JD implication. Irreducible JDs. Equivalence proofs.- Normalization - Implicit Dependencies - Part 2 :- Sets of dependencies. Explicit vs. implicit dependencies.- Normalization - The Chase :- Given a set of dependencies, what dependencies are implied by those in that given set?- Normalization - MVDs and 4NF :- Another kind of dependency. Fagins Theorem. Axiomatization. Embedded dependencies.- Normalization - Other Normal Forms - Part 1 :- 6NF. 5NF revisited: another surprising fact. Redundancy free relvars.- Normalization - Other Normal Forms - Part 2 :- RFNF (ETNF); SKNF; DK/NF. The end of the road?- Orthogonality :- Normalization reviewed. A little more science: The Principle of Orthogonal Design. The relationship between orthogonality and normalization.- Redundancy - Part 1 :- Tuples vs. propositions. What is redundancy? Examples of normalized, orthogonal, redundant design.- Redundancy - Part 2 :- Dealing with redundancy: four approaches. Controlled vs. uncontrolled redundancy.- Redundancy - Part 3 :- A proposed definition. Historical notes.- The Primacy of Primary Keys :- Are primary keys necessary? The invoices and shipments example.- Redundancy Revisited :- Vincents definition. Yet another normal form. Further reading.
It's easy, you can choose each topic you need to learn. 
In videos, You will learn a lot of idea. It's helpful to understand easy. Because you will be able to learn from examples in video and you can rewind again and again. Wow! that nice. Anyway, You can upload them to iPad/iPhone or etc. You can watch them while you are on the bus.
If you are students? In Database course for College or University, I believe this video will help students for understand about Normalization and Relational concept... blah blah

One thing, this video was presented by C.J. Date (who has a stature that is unique within the database industry. C.J. is a prolific writer, and is well-known for his best-selling textbook: An Introduction to Database Systems (Addison Wesley). C.J. is an exceptionally clear-thinking writer who can lay out principles and theory in a way easily understood by his audience).

I don't see the reason what makes this video is useless. If you work in database career, or you are studying about Database Design ,Relational Theory or Normal Forms... and you are thinking to get some stuff about them, you should determine for this stuff.  Written By: Surachart Opun http://surachartopun.com

I have a confession to make: I hate webinars. I find it difficult to concentrate on a disembodies voice. I typically get distracted and find myself checking email and blogs even during the best webinars. Watching a webinar is a bit like watching DVD of a live show – not as fun as live show, [...]

I was recently puzzled by the question, “Which query will be faster?”: SELECT * FROM table WHERE pk = x OR pk = x1 OR pk = x2 ... Or SELECT * FROM table WHERE pk IN (x1, x2,...); There are 50k values in both IN and OR clauses and lookup is done via primary [...]

At $work we have a need for a little job daemon that would poll jobs and process them. If there was only one kind of job involved, the solution could be nothing more complicated than while ( my @jobs = poll_jobs() ) { process( $_ ) for @jobs; sleep $a_wee_bit; } But there are more [...]

It is evident and beyond doubt now that the new media technologies like Twitter and Facebook are not going to wipe-out the blogs, rather they are complimenting each other very nicely and it seems they were made for each other. This Log Buffer Edition enhances this match, and presents you Log Buffer #272. Oracle: It [...]

After completing your Oracle VM and Oracle VM Manager installation (see my previous blog posts here) you are ready to start your friendship with Oracle VM technology. However to make your life and experience even more enjoyable I would suggest you to follow a few simple steps listed bellow. Configure Public Oracle YUM and install [...]

dbm_configurator.xls is needed to generate databasemachine.xml file which is needed by OEM 12c to discover an exadata cluster.

Following is a step  by step process as how to generate databasemachine.xml file to be used with OEM 12c:

1) Get the dbm_configurator70.xls file from /opt/oracle.SupportTools/onecommand directory from the first compute node of Exadata cluster.

2) Also get config.dat and dbmachine.params file from /opt/oracle.SupportTools/onecommand directory.

3) Open dbm_configurator70.xls file in Microsoft Excel and enabled the ActiveX content and Macros.

4) Make sure that all three files are in one directory and then click import button in the dbm_configurator excel file.

5) Select the dbmachine.params file and click ok, and it should populate the fields in your Excel sheet.

6) Verify that all the values are ok and if they are not change them and then click on Generate button.

7) This should generate a configuration on the bottom part of your Excel sheet.

8) Review that information and if its fine, then click on Create Config Button and it should generate mutliple files in your C drive and you will get a pop up telling you location of generated files.

9) databasemachine.xml is one of those generated files and you can use it with your OEM12c.

HTH.
 

These days, to craft basic emails, I go with Email::Simple. For the more heavy stuff with attachements and what-nots, I reach out for Email::MIME. Together, they make a pretty awesome duo. But… (come on, admit it, you knew there was going to be a ‘but’) But there is a fairly common use-case that falls pretty [...]

The Third International NoCOUG SQL & NoSQL Challenge sponsored by Pythian—Love Your Data™ will be revealed on Monday, May 21 at 9 AM PST at http://bit.ly/JvJS46. In this challenge, the Wicked Witch of the West needs help in creating a magic spell to ensure that the Third Annual Witching & Wizarding Ball is a grand [...]

For the third consecutive year, NoCOUG is hosting an international SQL challenge. Last year we ran into a small difficulty where the international DBA community proved too clever and solved the challenge before we could properly publicize it. A good DBA learns from her mistakes, and this year we are pre-announcing the challenge: The challenge [...]

This is just a quick post to share my first 3.1.1 Oracle VM Manager (OVMM) troubleshooting experience. After the initial installation I rebooted the server, tried to access OVMM https://ovmmhost:7002/ovm/console and received the following error in a browser screen: Unexpected error during login (com.oracle.ovm.mgr.api.exception.FailedOperationException: OVMAPI_6000E Internal Error: Connection refused Connection refused[[ < date > ), please consult logs [...]

When you have something insightful to say about one of these topics:

Business Intelligence & Data Warehousing
Business Strategy
Development
Fusion
Middleware
Oracle E-Business Suite
Oracle RDBMS
Emerging Technologies
MySQL
Engineered Systems
Infrastructure

Then take the chance to submit your abstract for the UKOUG Conference 2012 – it has a strong international reputation apart from being the the most important educational and networking event in the UK of the year for Oracle topics.

Go here to apply until Friday 1st June 2012.

Tagged: advert

It happens to be very short blog post as installation the Oracle VM Manager 3.1.1 under Dom0 host isn’t different from installing the previous version. For all tricks that you need to use please see my Oracle VM Manager 3.0.3 under Dom0 post. Just to make this post to look like a proper blog post :) I am sharing the [...]

During last couple of month I was seeing some discussion and question in different online conferences and user groups about upgrade RAC and exadata to 11.2.0.3. The questions were mostly about upgrade procedure, timing, what can happen during the upgrade and how a system behaves after upgrade. I’ve recently upgrade couple of exadata to 11.2.0.3 and want [...]