My Oracle World

Review: uCertify PrepKit 1Z0-047

2009-01-25T21:09:00.000-08:00

I was asked by uCertify to review their preparation kit product.

I am currently preparing myself for Oracle 1Z0-047 Oracle Database: SQL Certified Expert Exam and I thought this could be a good chance for me test my current knowledge and chose to review their preparation kit for exactly this exam. (Link)

To be honest, this is my first GUI based prep kit I’ve ever tried. So far I was using the Oracle official documentation as my primary source, and some of the books (Exam Study Guides) that were available.

My study strategy is read the official documentation and practice, practice, practice.

From my experience, the toughest part during preparation for an exam is when it comes to answer the question: “Am I ready enough?”

Usually I go through the questions available in some of the preparation books plus making even more questions based on different scenarios. I also use OTN forums to find discussions with challenging topics where I can test my knowledge.

With the last in mind, the uCertify Prep Kit is just another handy resource that one can use to test his/her knowledge before he/she actually takes the exam.

It is a solid product that provides more than three hundred questions combined into one diagnostic, one final, four practice tests and a quiz. The user also has an ability to create custom tests using those questions he/she finds interesting.

There are also number of questions that are pretty challenging. For all questions, the users have an option to read explanations about the answers including references to the Oracle documentation or other resources.

For each question, there is an option to start a discussion with other users, to send feedback to uCertify and to write personal notes and tags.

The feedback and discussion features are really nice. I found some answers that I did not agree with and used this feature to provide feedback to uCertify. I tested the discussion feature as well and it works nice.

The exam objectives are well covered. There are also questions specific to 11g version of the database.

The software has an update option so the user can download the latest updates and fixes.

The Graphic User Interface is very nice and the navigation is great. Each of the test results can be saved and used for later reference. Also there are features like Flash cards, a Quiz, Study notes and Articles. There are number of notes and articles available.

Finally there is a readiness report that can help the user to find the answer to the question: “Am I ready for the real thing?”

In general, this is a nice product that can be very helpful for one to prepare for an exam (they have various prep kits available (Link)). However, I don’t want anybody to get an impression that I think this is the only resource that one could use to prepare and pass an exam. Whenever I was asked for an advice on what resources should (must) one use to prepare for an exam, my answer was always straight, The Official Documentation, period. (The official trainings are also very valuable, but sometimes they are a must-do regardless of someone’s recommendations :-))

This product and all the other available out there (books, prep kits, study guides) are just another resource for learning and practicing that one may consider to use when preparing for an exam.

Save some disk space - One Reminder and a Cool Windows Command

2009-01-16T22:13:00.000-08:00

CPU Reminder

It’s the time of the year when the January CPU is released. I’d like to remind you to think about cleaning up your obsolete backups from $ORACLE_HOME/.patch_storage directory (the ones that are not needed anymore for rollback purposes).

Starting from 10g R2 Oracle backs up the affected libraries along with the rest of the affected files. Some of the libraries can be pretty big and thus after several CPUs the amount of disk space consumed by can be significant.

In order to prevent from unnecessary wasting the disk space, you could use Opatch utility using util cleanup option to remove those backups that are not needed anymore.

Below is the syntax and the options available when using Opatch tool to clean up old backups:



SYNTAX
opatch util cleanup  [-invPtrLoc  ]
                     [-jre  ] [-oh  ]
                     [-silent] [-report]
                     [-ps , this will
                       be located under ORACLE_HOME/.patch_storage/]

OPTIONS
       -invPtrLoc
              Used to locate the oraInst.loc file. Needed when the
              installation used the -invPtrLoc flag. This should be
              the path to the oraInst.loc file.

       -jre
              This option tells OPatch to use JRE (java) from the
              specified location instead of the default location
              under Oracle Home. Both -jdk and -jre options cannot
              be specified together. OPatch will display error in
              that case.

       -oh
              The oracle home to work on. This takes precedence over
              the environment variable ORACLE_HOME.

       -ps
              This option is used to specify the Patch ID with timestamp.
              This Patch ID with timestamp should be the same as in
              .patch_storage directory.

              A directory by this name will be present under
              ORACLE_HOME/.patch_storage. If this directory is specified
              and is valid, then the contents specified in the description
              will be cleaned up only for this patch. Otherwise, all patch
              related directories will be acted upon by this utility.

      -silent
              In silent mode, the cleanup always takes place.

      -report
              Prints the operations without actually executing them.

Make sure you specify the patch id (ps parameter) of the patch you want to remove.

Cool Windows Command

Few days ago I learned about forfiles command that can be used to select a set of files using some criteria (like date modified) and run command against each of them (like delete). (Something like find –exec in Unix)

I found this very useful for cleaning up the trace and log files that are not needed anymore.

It is very handy and I am really surprised that Microsoft finally came up with something like this. Before I used to write batch scripts to implement the logic that forfiles provides.

It is available on Windows Vista, Windows 2003 Server and Windows 2008 Server.

You should really take a look if you didn’t know about this one already.
Find more (the syntax and few examples) about forfiles from this TechNet Note .

Example:

Task: Delete all the trace files that are older more than 30 days


c:\app\oracle\diag\rdbms\db11g\db11g\trace>forfiles /s /m *.trc /d -30 /c "cmd /c
echo @FILE @FDATE"

"db11g_ora_13200.trc" 12/18/2008
"db11g_ora_18716.trc" 12/18/2008
"db11g_ora_18768.trc" 12/18/2008
"db11g_ora_18892.trc" 12/18/2008
"db11g_ora_3004.trc" 12/18/2008
"db11g_ora_4428.trc" 12/18/2008
"db11g_ora_6256.trc" 12/18/2008
"db11g_ora_6444.trc" 12/18/2008
"db11g_ora_6480.trc" 12/18/2008
"db11g_ora_6504.trc" 12/18/2008
"db11g_ora_6844.trc" 12/18/2008
"db11g_ora_6912.trc" 12/18/2008
"db11g_ora_6928.trc" 12/18/2008
"db11g_ora_7044.trc" 12/18/2008

c:\app\oracle\diag\rdbms\db11g\db11g\trace>forfiles /s /m *.trc /d -30 /c "cmd /c
del @FILE"


c:\app\oracle\diag\rdbms\db11g\db11g\trace>forfiles /s /m *.trc /d -30 /c "cmd /c
echo @FILE @FDATE"
ERROR: No files found with the specified search criteria.

Pretty handy, isn’t it?

ORA-12514 during switchover using Data Guard Broker

2009-01-14T20:45:00.000-08:00

I’ve seen, on several occasions, questions being asked about data guard broker being unable to automatically start the databases during a switchover, failing with ORA-12514 - TNS:listener does not currently know of service requested in connect descriptor?

This is most likely because the special service db_unique_name_DGMGRL has not been registered properly with the listener.

This is one of the requirements when configuring Data Guard broker.

From the Oracle documentation (Reference Oracle® Data Guard Broker 10g Release 2 (10.2)):

To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance. The value for the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain. For example, in the LISTENER.ORA file:
LISTENER = (DESCRIPTION =
   (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)
   (PO1RT=port_num))))
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
   (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
   (ORACLE_HOME=oracle_home)))

I think the main reason for overlooking this prerequisite is because nothing about this is mentioned in Oracle 10g Data Guard Concepts and Administration Guide, the chapters for standby databases configuration.

So once the initial configuration is set up and works fine, the listener prerequisites for setting up Data Guard Broker are probably overlooked.

Of course, this applies only if Data Guard Broker is configured manually. If one uses OEM Grid Control to set up and configure the Data Guard Configuration, OEM will make the necessary changes.

Another point I want to make here is the importance of practicing various switchover and failover scenarios. It is not only that you will gain more experience and fill more comfortable doing the same thing in real situation, but you also will learn about any hidden configuration wholes that you may have overlooked or missed in your configuration steps.

Here is an example of this problem and its solution:

For this example I used two Oracle 10.2.0.1 databases, dg1db running as a primary database and dg2db running as a physical standby database.


[oracle@dg1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/***
Connected.
DGMGRL> show configuration

Configuration
  Name:                dg-test
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    dg1db - Primary database
    dg2db - Physical standby database

Current status for "dg-test":
SUCCESS

Now lets try the switchover. As you can see the role transition was done successfully, but the database startup that should happened at the end failed with ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.


DGMGRL> switchover to dg2db;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "dg1db" on database "dg1db"
Shutting down instance "dg1db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "dg2db" on database "dg2db"
Shutting down instance "dg2db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dg1db" on database "dg1db"
Starting instance "dg1db"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "dg1db"
You must start instance "dg1db" manually
Operation requires startup of instance "dg2db" on database "dg2db"
You must start instance "dg2db" manually
Switchover succeeded, new primary is "dg2db"
DGMGRL>

After starting the databases manually, I checked the status of the Data Guard configuration and it was SUCCESS.


DGMGRL> show configuration

Configuration
Name:                dg-test
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
  dg1db - Physical standby database
  dg2db - Primary database

Current status for "dg-test":
SUCCESS

DGMGRL>

Now I will make the necessary changes to the listeners and try the switchover in the opposite direction. After the change listener.ora should look like (pay attention to GLOBAL_NAME=dg1db_DGMGRL.localdomain).


[oracle@dg1 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/ora10g/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = dg1db_DGMGRL.localdomain)
    (ORACLE_HOME = /u01/app/oracle/product/ora10g)
    (SID_NAME= dg1db)
  )
)

LISTENER =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521))
)

[oracle@dg1 admin]$ lsnrctl reload
[oracle@dg1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-JAN-2009 17:02:35

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                13-JAN-2009 08:08:17
Uptime                    0 days 8 hr. 54 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/ora10g/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/ora10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1.localdomain)(PORT=1521)))
Services Summary...
Service "dg1db.localdomain" has 1 instance(s).
Instance "dg1db", status READY, has 1 handler(s) for this service...
Service "dg1db_DGB.localdomain" has 1 instance(s).
Instance "dg1db", status READY, has 1 handler(s) for this service...
Service "dg1db_DGMGRL.localdomain" has 1 instance(s).
Instance "dg1db", status UNKNOWN, has 1 handler(s) for this service...
Service "dg1db_XPT.localdomain" has 1 instance(s).
Instance "dg1db", status READY, has 1 handler(s) for this service...
The command completed successfully

I did the same thing with the second listener.
Now the switchover will complete without any problems.
DGMGRL> show configuration

Configuration
Name:                dg-test
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
  dg1db - Physical standby database
  dg2db - Primary database

Current status for "dg-test":
SUCCESS

DGMGRL> switchover to dg1db
Performing switchover NOW, please wait...
Operation requires shutdown of instance "dg2db" on database "dg2db"
Shutting down instance "dg2db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "dg1db" on database "dg1db"
Shutting down instance "dg1db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dg2db" on database "dg2db"
Starting instance "dg2db"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "dg1db" on database "dg1db"
Starting instance "dg1db"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dg1db"
DGMGRL>

DGMGRL> show configuration

Configuration
Name:                dg-test
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
  dg1db - Primary database
  dg2db - Physical standby database

Current status for "dg-test":
SUCCESS

DGMGRL>

Cheers,
Mihajlo Tekic

HAPPY NEW YEAR !!!

2009-01-01T14:31:00.000-08:00

HAPPY NEW YEAR TO EVERYONE !!!

I WISH YOU AND YOUR FAMILIES HEALTHY, HAPPY AND SUCCESSFUL NEW YEAR !!!

The Picture above is of my daughter Monika.

She is four months old and is the best thing that happened to me in 2008.

ORA-23375 when adding new master site in multi-master environment

2008-08-31T15:58:00.000-07:00

If your database global name contains a word that belongs to the list of Oracle Database Reserved Words and you are planning to add this database as a new master site in multi-master environment, you may want to consider changing the database global name so you can avoid ORA-23375 when executing DBMS_REPCAT.ADD_MASTER_DATABASE procedure.

This is especially important for those databases that have their global name containing country internet code of any of the following countries: Austria (AT), Belarus (BY), India (IN), Iceland (IS), etc. AT, BY, IN, IS are among the database reserved words.

You may experience problems even before you try to add the new master site. The creation of the database link to the new master site may fail with:

ORA-02084: database name is missing a component

However, you may not get this error in some cases, for instance when AT keyword is used; or if you put the database link name in double-quotes.

(the environment used in the examples below is DB1.MYDOMAIN.COM(master definition site, release 10.2.0.2) and DB11G.MYDOMAIN.IN (master site, release 11.1.0.6))


SQL>  create database link DB11G.MYDOMAIN.IN 
  2  connect to rep_admin
  3  identified by “rep_password”
  4  using 'DB11G'; 
 create database link DB11G.MYDOMAIN.IN 
                      * 
ERROR at line 1: 
ORA-02084: database name is missing a component

SQL>  create database link "DB11G.MYDOMAIN.IN" 
  2  connect to rep_admin
  3  identified by "rep_password" 
  4  using 'db11g'; 

Database link created.

SQL> create database link DB11G.MYDOMAIN.AT             
  2  connect to rep_admin
  3  identified by "rep_password" 
  4  using 'DB11G'; 

Database link created.

But, even if the database link creation succeeds, the attempt to add the new master site would definitely fail with:

ORA-23375: feature is incompatible with database version at global database name


SQL> begin 
  2    dbms_repcat.add_master_database( 
  3    gname=>'TEST_GROUP', 
  4    master=>'"DB11G.MYDOMAIN.IN"', 
  5    use_existing_objects =>true, 
  6    copy_rows =>false, 
  7    propagation_mode=>'ASYNCHRONOUS'); 
  8  end;  
  9  / 
begin 
* 
ERROR at line 1: 
ORA-23375: feature is incompatible with database version at DB11G.MYDOMAIN.IN 
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2159 
ORA-06512: at "SYS.DBMS_REPCAT", line 146 
ORA-06512: at line 2 


SQL>     

SQL> begin 
  2 dbms_repcat.add_master_database( 
  3 gname=>'TEST_GROUP', 
  4 master=>'DB11G.MYDOMAIN.AT', 
  5 use_existing_objects =>true, 
  6 copy_rows =>false, 
  7 propagation_mode=>'ASYNCHRONOUS'); 
  8 end; 
  9  / 

begin 
* 
ERROR at line 1: 
ORA-23375: feature is incompatible with database version at DB11G.MYDOMAIN.AT 
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2159 
ORA-06512: at "SYS.DBMS_REPCAT", line 146 
ORA-06512: at line 2

If you turn 10046 trace event you can notice that the error occurs when DBMS_REPCAT_RPC.REPLICATION_VERSION_RC and DBMS_REPCAT_UTL2.REPLICATION_VERSION procedures are invoked against the new master database.


PARSE ERROR #39:len=73 dep=1 uid=61 oct=47 lid=61 tim=1191209094695404 err=6550 
BEGIN sys.dbms_repcat_rpc.replication_version_rc@DB11G.MYDOMAIN.IN (:rv);END; 
===================== 
PARSE ERROR #39:len=72 dep=1 uid=61 oct=47 lid=61 tim=1191209094702737 err=6550 
BEGIN  sys.dbms_repcat_utl2.replication_version@DB11G.MYDOMAIN.IN (:rv);END;

To solve this problem the global name of the new master database should be changed. Once the global name is changed to value that does not contain any reserved word, you should be able to add the database to the replication environment.

In the example below, I will try to add a new master site which global name was changed to DB11G.FOO.BAR


SQL>conn sys/***@DB11G as sysdba
Connected.
SQL> alter database rename global_name to DB11G.FOO.BAR; 

Database altered.

SQL> conn rep_admin/rep_password@DB1
Connected.
SQL>
SQL> create database link DB11G.FOO.BAR 
  2  connect to rep_admin
  3  identified by "rep_password" 
  4  using 'DB11G'; 

Database link created.

SQL> begin 
  2    dbms_repcat.add_master_database( 
  3    gname=>'TEST_GROUP', 
  4    master=>'DB11G.FOO.BAR', 
  5    use_existing_objects =>true, 
  6    copy_rows =>false, 
  7    propagation_mode=>'ASYNCHRONOUS'); 
  8  end; 
  9  / 

PL/SQL procedure successfully completed. 


SQL> column dblink format a30 
SQL> select DBLINK, MASTERDEF, MASTER             
  2  from dba_repsites; 

DBLINK                         M M 
------------------------------ - - 
DB1.MYDOMAIN.COM               Y Y 
DB11G.FOO.BAR                  N Y

There are few ways to find Oracle PL/SQL reserved words:

V$RESERVED_WORDS

Oracle documentation (PL/SQL Reserved Words and Keywords). Check your version specific documentation available on http://tahiti.oracle.com

Issue HELP RESERVED WORDS (PL/SQL) from Sql*Plus (if Sql*Plus help is installed).

Fast-Start Failover - It is reliable

2008-08-10T18:45:00.000-07:00

Last Friday there was one post on OTN forums that brought my attention. The OP was wondering “Is Data Guard Buggy” with attention to Fast-Start Failover (FSFO) feature that provides an ability of automatic failover to the standby database if the primary database is not available for certain time.

He had some concerns about FSFO being unreliable, very difficult to be implemented and actually doesn't work properly.

The OP got some immediate response from the OTN users. I couldn't agree more with Joseph Meeks's comment on the topic.

In my opinion, FSFO is very nice feature that plays big role in Oracle's Maximum Availability Architecture. There might be some valid reasons not to implemented it, but if the automatic failover is a requirement, FSFO is the way to go. Should one have any problems implementing it, the best way to go is to get Oracle Support involved.

In this post, I'd like to show that implementation of the FSFO should not be a difficult task once you have the Data Guard environment set up.

I configured data guard physical standby environment on my laptop. Due to hardware limitations, I'll have the observer running on the same machine with the standby databases. Keep in mind this is just an example. In practice, the primary database, the standby database and the observer should run on different hosts. This example also answers one of the questions OP asked: Will it be possible to set it up on one machine? The answer would be it is possible, as shown in the example :-), but it is not the right way to go.

I use DGMGRL utility in the example..

So, I configured a Data Guard environment where DB1.MYDOMAIN.COM is primary database and STDB.MYDOMAIN.COM is physical standby. Both databases are 10.2.x
By default, protection mode of the Data Guard configuration is set to MAXIMUM PERFORMANCE


DGMGRL> connect sys/***@db1 
Connected. 
DGMGRL> show configuration 

Configuration 
  Name:                DRTest 
  Enabled:             YES 
  Protection Mode:     MaxPerformance 
  Fast-Start Failover: DISABLED 
  Databases: 
    db1  - Primary database 
    stdb - Physical standby database 

Current status for "DRTest": 
SUCCESS

Enabling Fast-Start Failover requires the following pre-requisites to be met:
Flashback Database feature is enabled on both the primary and the standby database.
The protection mode of the configuration must be set to MAXIMUM AVAILABILITY
tnsnames.ora in the ORACLE_HOME where the observer runs must be set to see both databases, the primary and the standby.
DGMGRL must be available on the observer host.

Enable Flashback Database
I will enable flashback database feature on both databases. This assumes that the flash recovery area is configured.
The flashback database feature provides an ability for an easy reinstatement of the failed primary database to new standby database.


SQL> shutdown immediate 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 
SQL> 

SQL> startup mount 
ORACLE instance started. 

Total System Global Area  285212672 bytes 
Fixed Size                  1261348 bytes 
Variable Size             100663516 bytes 
Database Buffers          176160768 bytes 
Redo Buffers                7127040 bytes 
Database mounted. 
SQL> alter database flashback on; 

Database altered. 

SQL> alter database open; 

Database altered.

Set the protection mode to MAXIMUM AVAILABILITY
The Fast-Start Failover can be enabled only if the protection mode is set to MAXIMUM AVAILABILITY.
LogXptMode has to be set to 'SYNC'


DGMGRL> edit database 'db1' 
> set property LogXptMode='SYNC'; 
Property "logxptmode" updated 
DGMGRL> edit database 'stdb' 
> set property LogXptMode='SYNC'; 
Property "logxptmode" updated 

DGMGRL> edit configuration set protection mode as MaxAvailability; 
Succeeded. 
DGMGRL> show configuration 

Configuration 
  Name:                DRTest 
  Enabled:             YES 
  Protection Mode:     MaxAvailability 
  Fast-Start Failover: DISABLED 
  Databases: 
    db1  - Primary database 
    stdb - Physical standby database 

Current status for "DRTest": 
SUCCESS

SQL> conn sys/***@db1 as sysdba 
Connected. 
SQL> select protection_mode, protection_level from v$database; 

PROTECTION_MODE      PROTECTION_LEVEL 
-------------------- -------------------- 
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY 

SQL> conn sys/***@stdb as sysdba 
Connected. 
SQL> select protection_mode, protection_level from v$database; 

PROTECTION_MODE      PROTECTION_LEVEL 
-------------------- -------------------- 
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY 

SQL>

I ensured that tnsnames.ora are set correctly as well DGMGRL is installed.

Now, since all the prerequisites are met, lets move forward and enable the FSFO.
Before enabling it, make sure each of the databases in the configuration has set a fast start failover target. This is achieved by setting the FastStartFailoverTarget parameter.


DGMGRL> edit database 'db1' set property faststartfailovertarget='stdb'; 
Property "faststartfailovertarget" updated 
DGMGRL> edit database 'stdb' set property faststartfailovertarget='db1'; 
Property "faststartfailovertarget" updated

Another important parameter that has to be set is FastStartFailoverThreshold. This parameter specifies the amount of time (in seconds) the observers attempts to reconnect to the primary database before starting the fast-start failover to the standby database. The default value is set to 30 seconds. In the example I set this parameter to 120 seconds.


DGMGRL> edit configuration set property FastStartFailoverThreshold=120; 
Property "faststartfailoverthreshold" updated

Now lets enable the fast start failover:


DGMGRL> ENABLE FAST_START FAILOVER; 
Enabled.

If you check the configuration at this moment you may find the following warning: ORA-16608: one or more databases have warnings.
If you check the status of one of the databases, you can see this warning: ORA-16819: Fast-Start Failover observer not started


DGMGRL> show configuration 

Configuration 
  Name:                DRTest 
  Enabled:             YES 
  Protection Mode:     MaxAvailability 
  Fast-Start Failover: ENABLED 
  Databases: 
    db1  - Primary database 
    stdb - Physical standby database 
         - Fast-Start Failover target 

Current status for "DRTest": 
Warning: ORA-16608: one or more databases have warnings 


DGMGRL> show database verbose 'db1' 

Database 
  Name:            db1 
  Role:            PRIMARY 
  Enabled:         YES 
  Intended State:  ONLINE 
  Instance(s): 
    db1 

  Properties: 
    InitialConnectIdentifier        = 'db1.mydomain.com' 
    LogXptMode                      = 'SYNC' 
    Dependency                      = '' 
    DelayMins                       = '0' 
    Binding                         = 'OPTIONAL' 
    MaxFailure                      = '0' 
    MaxConnections                  = '1' 
    ReopenSecs                      = '300' 
    NetTimeout                      = '180' 
    LogShipping                     = 'ON' 
    PreferredApplyInstance          = '' 
    ApplyInstanceTimeout            = '0' 
    ApplyParallel                   = 'AUTO' 
    StandbyFileManagement           = 'auto' 
    ArchiveLagTarget                = '0' 
    LogArchiveMaxProcesses          = '2' 
    LogArchiveMinSucceedDest        = '1' 
    DbFileNameConvert               = 'stdb, db1' 
    LogFileNameConvert              = 'stdb, db1' 
    FastStartFailoverTarget         = 'stdb' 
    StatusReport                    = '(monitor)' 
    InconsistentProperties          = '(monitor)' 
    InconsistentLogXptProps         = '(monitor)' 
    SendQEntries                    = '(monitor)' 
    LogXptStatus                    = '(monitor)' 
    RecvQEntries                    = '(monitor)' 
    HostName                        = 'miki-laptop' 
    SidName                         = 'db1' 
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=miki-laptop)(PORT=1521))' 
    StandbyArchiveLocation          = 'dgsby_db1' 
    AlternateLocation               = '' 
    LogArchiveTrace                 = '0' 
    LogArchiveFormat                = '%t_%s_%r.dbf' 
    LatestLog                       = '(monitor)' 
    TopWaitEvents                   = '(monitor)' 

Current status for "db1": 
Warning: ORA-16819: Fast-Start Failover observer not started

So lets start the observer. I will repeat again, the observer should run on a different host, however for the sake of this example it will run on the same machine as the databases.
In order to start the observer one should start DGMGRL utility and login to the data guard configuration.
Once logged in, issue START OBSERVER command. This will start the observer.
Optionally you can set a log file destination while invoking DGMGRL utility and specify name for the observer configuration file (for more information check Data Guard Command-Line Interface Reference).
Once the observer is started, the control is not returned to the user until the observer is stopped.


DGMGRL for Linux: Version 10.2.0.3.0 - Production 

Copyright (c) 2000, 2005, Oracle. All rights reserved. 

Welcome to DGMGRL, type "help" for information. 
DGMGRL> connect sys/***@db1 
Connected. 
DGMGRL> start observer; 
Observer started

So lets check the configuration now.


$ dgmgrl 
DGMGRL for Linux: Version 10.2.0.3.0 - Production 

Copyright (c) 2000, 2005, Oracle. All rights reserved. 

Welcome to DGMGRL, type "help" for information. 
DGMGRL> connect sys/***@db1 
Connected.
DGMGRL> show configuration verbose; 

Configuration 
  Name:                DRTest 
  Enabled:             YES 
  Protection Mode:     MaxAvailability 
  Fast-Start Failover: ENABLED 
  Databases: 
    db1  - Primary database 
    stdb - Physical standby database 
         - Fast-Start Failover target 

Fast-Start Failover 
  Threshold: 120 seconds 
  Observer:  miki-laptop 

Current status for "DRTest": 
SUCCESS 

DGMGRL>

As of this moment my configuration has FSFO enabled.

Now lets test if the FSFO really works.
One should be aware of the conditions that must be satisfied for the observer to attempt FSFO.
The full list of conditions can be found in Data Guard Broker user guide under 5.5.2.1 What Happens When Fast-Start Failover and the Observer Are Running?

I will issue SHUTDOWN ABORT to the primary database (SHUTDOWN NORMAL/IMMEDIATE/TRANSACTIONAL would not trigger the failover).
Once I crash the database, the status of the configuration will return Error: ORA-16625: cannot reach the database. Be aware that since the primary database is down, the only way to check the configuration is to connect using the standby database credentials.


SQL> conn sys/***@db1 as sysdba 
Connected. 
SQL> shutdown abort 
ORACLE instance shut down. 
SQL> 

DGMGRL> connect sys/***@stdb 
Connected. 
DGMGRL> show configuration 

Configuration 
  Name:                DRTest 
  Enabled:             YES 
  Protection Mode:     MaxAvailability 
  Fast-Start Failover: ENABLED 
  Databases: 
    db1  - Primary database 
    stdb - Physical standby database 
         - Fast-Start Failover target 

Current status for "DRTest": 
Error: ORA-16625: cannot reach the database

After waiting for two minutes (FSFO threshold was set to 120 seconds), I checked the observer log file and found out that it started the failover.


[W000 08/09 17:58:49.75] Observer started. 

18:05:38.31  Saturday, August 09, 2008 
Initiating fast-start failover to database "stdb"... 
Performing failover NOW, please wait... 
Failover succeeded, new primary is "stdb" 
18:06:16.82  Saturday, August 09, 2008


DGMGRL> show configuration verbose

Configuration

  Name:                DRTest
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: ENABLED
  Databases:
    db1  - Physical standby database (disabled)
         - Fast-Start Failover target
    stdb - Primary database

Fast-Start Failover

  Threshold: 120 seconds
  Observer:  miki-laptop

Current status for "DRTest":

Warning: ORA-16608: one or more databases have warnings

DGMGRL>

So the observer started the fast-start failover and has successfully converted the former standby to new primary database.

Once the failover is done the observer will attempt to automatically reinstate the former primary database to new physical standby. Certain requirements have to be met for the automatic reinstatement to take place. If not, the database can be manually reinstated. Once the reinstatement is successful you can see that the former primary database became new physical standby.


DGMGRL> show configuration

Configuration

  Name:                DRTest
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: ENABLED
  Databases:

    db1  - Physical standby database
         - Fast-Start Failover target
    stdb - Primary database

Current status for "DRTest":

SUCCESS

DGMGRL>

Finally, you can do switchover to perform role change and to place the configuration in its initial state.

The example above shows that enabling fast-start failover is pretty straight forward. There are many good documents with information how to enable FSFO feature. Some of them are listed below:

Oracle® Data Guard Broker 10g Release 2 (10.2) - 5.5 Fast-Start Failover
What Happens When Fast-Start Failover and the Observer Are Running?

7.6 Scenario 5: Enabling Fast-Start Failover and the Observer

Metalink Note#359555.1 IMPLEMENTING FAST-START FAILOVER IN 10GR2 DATAGUARD BROKER ENVIRONMENT

Oracle Maximum Availability Architecture – MAA

I hope I didn't miss something.

Cheers!

Metalink, SCM, the error and the good stuff

2008-07-31T21:45:00.000-07:00

Yesterday I got an e-mail from Oracle Support letting me know about the new Metalink interface which is supposed to go live this Fall:

Dear MetaLink Customer,

Oracle is committed to consistently improving your customer support experience. In the fall of 2008, MetaLink will have a new user interface. To help you prepare for the transition, you may now preview MetaLink's new user interface and provide valuable feedback about its features.

******

I clicked on the link, that was supposed to redirect me to Metalink, and I got one very fancy schmancy login page. Well, actually, it is the Software Configuration Manager (SCM).

I put in my login credentials and ... I got an error message "IO Error Error #2032"

WOW ... :-)

Well, OK the problem was fixed latter that day.

Regardless of the problem I experienced, I must say that I've been using SCM for quite a while and I am pretty impressed with its functionality. It is really much easier to create and manage service requests using the configurations you I have registered with SCM. Oracle Support engineers have all the information they need about the configuration of the server and the database.

Searching the Knowledge base looks improved as well. Now you can have your search results visible on the left panel of the screen, while, at the same time, you can read the content of the selected note. This makes navigation much easier.

Service Requests part has new design too.

All in all, improved functionality, better navigation, good design, some new features too ... Good job !!!

Useful Links:

http://csm.oracle.com/
http://www.oracle.com/support/premier/software-configuration-manager.html

1Z0-043 ... Passed

2008-07-27T21:32:00.000-07:00

I passed the 1Z0-043 exam a week ago. While waiting on Oracle to process my hands-on course requirement I am thinking what should be the next step. Certainly OCM is on my list, but in the mean time I'd like to go for SQL Expert Certificate.

I have been studying very hard, especially the last month. I started with Oracle 10g Certification and Oracle Database 10g OCP Certification All-In-One Exam Guide, but the most of the time I spent reading from the Official Documentation and of course, practicing a lot.

The books I mentioned are good, they cover some basics, but I found they are not quite sufficient to prepare you for the exam. Some of the material is not up to date with the exam topics. The sample tests that come with the books contain some questions out of the scope of the exam. Some of the questions contain answers that are wrong or arguable.

It is very important that while preparing for the exam you practice a lot. It is not very difficult to set up a test environment for each of the topics. It was slightly complicated to create a test environment for ASM on my Ubuntu Linux, but the post from Grégory Guillou How To Set Up Oracle ASM on Ubuntu Gutsy Gibbon has helped me with that.

Finally, while preparing for the exam, I have improved my skills significantly and learned some new stuff that I didn't have any experience with before (Resource Manager for instance).

Blocked Patch files cause OPatch to fail with error code = 170

2007-11-01T10:28:00.000-07:00

I had to apply a CPU patch to 9.2.0.7 Oracle Home on Windows 2003 Server box.

Opatch utility was failing with the following error:

The patch directory area must be a number.

ERROR: OPatch failed because of problems in patch area.
OPatch returns with error code = 170

Well, there are quite few reasons why you may get this error when you try to apply a patch using Opatch.
Many of them are published in some of the following Metalink notes:

Note:369555.1
Note:294350.1
Note:418479.1

But, I couldn't find anything about the one that I came across recently.

I tried everything described in the notes above. Nothing worked out, until I realized that all of the patch files have been somehow blocked.

Although the patch files had all the necessary permissions, especially read, the opatch utility couldn't read them because of the lock set by the OS.

If you check the file properties you can see the message:
"This file came from another computer and might be blocked to help protect this computer".

The reason why these files were blocked was because I copy them from a different machine.

A blocked file can be unlocked by pressing Unlock button.
The odd thing was that only one file can be unlocked at a time.

So I just deleted the patch directory and unlocked the patch zip before I extract it again.

I haven't had similar issue before. I believe this is a new "security" feature of Windows 2003 Server.

Query to determine the clustering factor before you create the index

2007-09-16T08:33:00.000-07:00

I found the following query useful to determine the clustering factor for the indexes that has not been created yet.

select count(1) clustering_factor
from
(
select dbms_rowid.rowid_block_number(rowid) block_no,
list of the indexed columns,
count(1) num_rows,
LAG(dbms_rowid.rowid_block_number(rowid))
over (order by list of the indexed columns) prev_block
from table_name
group by dbms_rowid.rowid_block_number(rowid),
list of the indexed columns
order by list of the indexed columns
)
where block_no<>prev_block or prev_block is null

Let's take a look at the following example:

SQL> create table test
2 tablespace example
3 as select
4 mod(rownum,8) col1
5 lpad('x',1000) col2
6 from all_objects
7 where rownum<=1000;

Using the following query, we can see that there are about 7 rows per block (block size 8192) that makes the records with same COL1 to be scattered across different blocks:

SQL> select num_rows_in_block, count(1) blocks
2 from
3 (
4 select block_no, count(1) num_rows_in_block
5 from
6 (
7 select dbms_rowid.rowid_block_number(rowid) block_no
8 from test
9 )
10 group by block_no
11 )
12* group by num_rows_in_block
SQL> /

NUM_ROWS_IN_BLOCK BLOCKS
----------------- ----------
6 1
7 142

Let see how big would be the clustering factor of an index built on TEST(COL1).

SQL> select count(1) clustering_factor
2 from
3 (
4 select dbms_rowid.rowid_block_number(rowid) block_no,
5 col1,
6 count(1) num_rows,
7 LAG(dbms_rowid.rowid_block_number(rowid))
8 over (order by col1) prev_block
9 from test
10 group by dbms_rowid.rowid_block_number(rowid), col1
11 order by col1
12 )
13* where block_no<>prev_block or prev_block is null
SQL> /

CLUSTERING_FACTOR
-----------------
1000

The result was pretty obvious.
Now let's add another column COL3 in the table TEST and set its value to mod(col1, 4).
This will make the blocks to contain at most two records with the same value of COL3.

SQL> alter table test
2 add col3 number;

Table altered.

SQL> update test set col3=mod(col1,4);

1000 rows updated.

SQL> commit;

Commit complete.

SQL>

Now let see what would be the value of the clustering factor of the index created on COL3 column:

SQL> select count(1) clustering_factor
2 from
3 (
4 select dbms_rowid.rowid_block_number(rowid) block_no,
5 col3,
6 count(1) num_rows,
7 LAG(dbms_rowid.rowid_block_number(rowid))
8 over (order by col3) prev_block
9 from test
10 group by dbms_rowid.rowid_block_number(rowid), col3
11 order by col3
12 )
13* where block_no<>prev_block or prev_block is null
SQL> /

CLUSTERING_FACTOR
-----------------
572

Now let's create the actual indexes and see what their clustering factor would be:

SQL> create index test_col1_idx on test(col1);

Index created.

SQL> create index test_col3_idx on test(col3);

Index created.

SQL> select index_name, clustering_factor
2 from user_indexes
3* where table_name='TEST'
SQL> /

INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
TEST_COL1_IDX 1000
TEST_COL3_IDX 572

Perfect insights about the importance of the clustering factor for calculating the cost I found in Jonathan Lewis's book Cost-Based Oracle Fundamentals.

Let's go back to the very first query in this post. Why I think this query is useful?
Because sometime, even though I think some columns are perfect combination to create index on, the index might not be used by the optimizer because the clustering factor is big.
It can give you an initial clue how effective some index could be.

Run Flashback commands only from Sql*Plus 10.1.x or newer

2006-12-15T21:24:00.000-08:00

getting ORA-08186: invalid timestamp specified each time I tried to run a FVQ.

ORA-08186: invalid timestamp specified
Well ... take a look at the following example

First I wanted to make sure, that the format I use is the correct one.

1* select to_char(systimestamp,'DD-MON-RR HH.MI.SSXFF AM') from dual
SQL> /

TO_CHAR(SYSTIMESTAMP,'DD-MON-RR
-------------------------------
14-DEC-06 10.27.26.622829 AM

Now, when I tried to run FVQ, I got "ORA-30052: invalid lower limit snapshot expression". That was an expected result, since my lower limit did not belong in (SYSDATE-UNDO_RETENTION, SYSDATE] range. (UNDO_RETENTION parameter was set to 900).
But you can agree with me that Oracle successfully processed timestamp values that I used in this query.

SQL> ed
Wrote file afiedt.buf

1 select comm
2 from scott.emp
3 versions between timestamp
4 to_timestamp('14-DEC-06 09.45.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM') and
5 to_timestamp('14-DEC-06 10.00.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM')
6 where
7* empno = 7369
SQL> /
from scott.emp
*
ERROR at line 2:
ORA-30052: invalid lower limit snapshot expression

So I modified the lower limit to fit in the right range, and I got ORA-08186: invalid timestamp specified. !?!?!?

SQL> ed
Wrote file afiedt.buf

1 select comm
2 from scott.emp
3 versions between timestamp
4 to_timestamp('14-DEC-06 10.20.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM') and
5 to_timestamp('14-DEC-06 11.00.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM')
6 where
7* empno = 7369
SQL> /
from scott.emp
*
ERROR at line 2:
ORA-08186: invalid timestamp specified

After some time that I spent trying to resolve this issue (I couldn't dare to open SR about it:-)) I remembered I have had similar problems while trying to test some flashback features (flashback table to before drop) on Sql*Plus 9.2.x while ago….and I was using Sql*Plus 9.2 again.

I tried the same example on Sql*Plus 10.1.0.2

… and everything worked well.

Some books might make you think wrong

2006-11-11T08:59:00.000-08:00

It is amazing how confusable some of the books could be.
You better check any information you think is different than you know.
Currently I am reading a book that is suppose to prepare me for OCP exam.
There are some questions and answers after each Chapter. I found few answers that I think are not correct.
Don’t get me wrong I still think that the book I read is a good one.

One Example: (I changed usernames and sequence name used in the original question):
Question:

The following SQL statement will allow user test_usr which operations on sequence scott.test_sq?
GRANT ALL ON scott.test_sq to test_usr;

A) Select the next value from scott.test_sq
B) Alter sequence scott.test_sq to change the next value
C) Change the number of sequence numbers that will be cached in memory
D) Both A and C
E) All of the above

The answer provided in the book is D.

But, I think it is E. Why?

SQL> conn scott/*****
Connected.
scott@ora10g> create sequence test_sq
2 start with 1
3 increment by 1;

Sequence created.

scott@ora10g> select test_sq.nextval from dual;

NEXTVAL
----------
1
scott@ora10g> conn sys as sysdba
Connected.
sys@ora10g> create user test_usr identified by test_usr
2 default tablespace users
3 temporary tablespace temp;

User created.

sys@ora10g> grant connect, resource to test_usr;

Grant succeeded.

sys@ora10g> grant all on scott.test_sq to test_usr;

Grant succeeded.

sys@ora10g> conn test_usr/test_usr
Connected.
test_usr@ora10g> select scott.test_sq.nextval from dual;

NEXTVAL
----------
2

test_usr@ora10g> alter sequence scott.test_sq
2 increment by 10;

Sequence altered.

test_usr@ora10g> select scott.test_sq.nextval from dual;

NEXTVAL
----------
12

test_usr@ora10g> alter sequence scott.test_sq
2 increment by 1;

Sequence altered.

test_usr@ora10g> conn scott/*****
Connected.
scott@ora10g> select test_sq.nextval from dual;

NEXTVAL
----------
13

scott@ora10g>

Domain index not used after migrating from 8.1.7.4 to 10.2.0.2

2006-10-28T09:16:00.000-07:00

If you have application running on Oracle 8.1.7.4 and you are massively using CONTEXT indexes, you better analyse the queries where context indexes are involved, before you decide to migrate to 10.2.0.x.
This is because execution paths might be way different in 10.2.0.x that may lead to performance degradation.
Here is one example where you should consider query modification in order to avoid bad performances.
Let assume that you have a query that has OR-condition where OR-predicates use CONTEXT operators.

Example:

select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)>0
or
CONTAINS(b.text,:b,1)>0
or
CONTAINS(c.text,:c,2)>0
)

This query in 8.1.7.4 most likely will have execution plan similar to this one:

Plan Table
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 42 | 8K| 93 |
| CONCATENATION | | | | |
| NESTED LOOPS | | 14 | 1K| |
| NESTED LOOPS | | 14 | 2K| 17 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 135K| 1 |
| DOMAIN INDEX |ALL_OBJEC | 2K| | |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_B | 2K| | |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 1K| 2 |
| INDEX UNIQUE SCAN |ALL_OBJ_A | | | 2 |
| NESTED LOOPS | | 14 | 1K| |
| NESTED LOOPS | | 14 | 2K| 31 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |
| DOMAIN INDEX |ALL_OBJEC | 2K| | |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 135K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 2K| | |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 1K| 2 |
| INDEX UNIQUE SCAN |ALL_OBJ_A | | | 2 |
| NESTED LOOPS | | 14 | 2K| 31 |
| NESTED LOOPS | | 14 | 2K| 17 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_B | 2K| | |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 2K| 135K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 2K| | |
----------------------------------------------------------------

where domain indexes are used to support CONTAINS function.
Well, in 10.2.0.2, you better be prepared to have at least twice slower response time, since for this type of query no domain indexes can be used:
The execution plan for my 10.2.0.2 database looks like:

Plan Table
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 42 | 8K| 1650 |
| HASH JOIN | | 42 | 8K| 1650 |
| TABLE ACCESS FULL |ALL_OBJEC | 51K| 4M| 234 |
| HASH JOIN | | 51K| 6M| 835 |
| TABLE ACCESS FULL |ALL_OBJEC | 51K| 2M| 234 |
| TABLE ACCESS FULL |ALL_OBJEC | 51K| 3M| 234 |
----------------------------------------------------------------

All tables were identical in both databases.

I have opened a SR with Oracle Support on this one, and the answers that I got were very interesting and make sense.
The key reason for such a behaviuor is how Oracle optimizer handles OR-expansion condition where OR-predicates use CONTEXT operators.
Oracle optimizer usually probes OR-expansion to see if a cheaper index based plan can be produced. But, Or-expansion is not legitimate if OR-predicate has a CONTEXT operator, because some of the new "concatenated" query blocks will loose context of this operator.
So, generally, the plan generated in 8.1.7.4 is not legitimate, even though it is very good one, performance wise.
Anyway, if you want to use plan similar to the one you used to have, then you better transform your queries.
I modified the query shown above, to this one:

select * from
(
select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)>0
)
)
UNION ALL
(
select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)<=0 and CONTAINS(b.text,:b,1)>0
)
)
UNION ALL
(select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)<=0 and CONTAINS(b.text,:b,1)<=0 and CONTAINS(c.text,:c,2)>0
)
)

Execution plan has been changed to this one:

Plan Table
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 42 | 6K| 31 |
| UNION-ALL | | | | |
| NESTED LOOPS | | 14 | 1K| 19 |
| NESTED LOOPS | | 14 | 1K| 19 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 5 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 1 | 21 | 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_B | 1 | | 0 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 1 | 4 | 0 |
| NESTED LOOPS | | 14 | 2K| 5 |
| HASH JOIN | | 14 | 2K| 5 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 994 | 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 1 | 4 | 0 |
| HASH JOIN | | 14 | 2K| 7 |
| HASH JOIN | | 14 | 2K| 5 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 994 | 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 756 | 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
----------------------------------------------------------------

end the response time was much better compared to both previous plans.

Now, the questions are:
How this can be implemented in the application?
How many queries similar to this one are being used in the application?
Is it worth the effort?
Can your application live with the execution paths based on full table scan?

You better answer to these questions before you decide what your next step would be.

On the road again

2006-10-27T20:51:00.000-07:00

It’s been a long time since my last post on this blog.
Ok, one of the reasons for this “absenteeism” was my new job.
Honestly, I didn’t have too much time to write anything except reports, research papers, proof of concepts, etc.
Although I commute more than I used to, I don’t mind since I use every chance to read something interesting while riding on CTA trains.
In three months, only in train cars I’ve read three books, two for the first time and one for the second time (just because it is perfect).

1. Oracle High-performance SQL tuning - Don Burleson believe or not, I found it for $9.99 in Borders Outlet Store.

Oracle Replication: Snapshot, Multi-master & Materialized Views Scripts - John Garmany & Robert Freeman

3. Effective Oracle by Design by Tom Kyte I’ve read this book once, and I read it again, just because it is perfect.

Who said that riding in CTA trains is boring? Actually it is sometimes when train cars are so crowded.

Anyways, I am on the road again, hopefully I’ll be more active in the future writing on this blog.

The Tao of Oracle

2006-03-24T09:11:00.000-08:00

VI.
The Oracle Masters have no mind of their own.
They are aware of the needs of others.
They are good to users who are good.
They are also good to users who are not good.
Because Virtue is goodness.
They have faith in servers that are faithful.
They also have faith in servers that are not faithful.
Because Virtue is faithfulness.
The Master is shy and humble - to the world he seems confusing.
Others look to him and listen.
He behaves like a little child.

more about The Tao of Oracle by Roby Sherman

New Oracle Q&A site

2006-01-21T10:00:00.000-08:00

I saw many posts about new Eddie Awad’s site Oracle Questions and Answers. Some of them criticize it some of them recognized it as a new Oracle Q&A site.
It was interesting to read what Tom Kyte and Howard Rogers wrote about it. One simple solution presented on Oracle Q&A produced big and strong discussion about possibility of one table to be read-only.
Howard Rogers did research about this possibility and as a result he got that one Oracle table could not be read-only in real meaning of the phrase “read-only”. So, his standing point was that this site does not provide real and true information.

From my perspective, this is good and useful site. You can find very useful information there. I agree that some of the answers are short, but sometimes it is better to get the idea, so you can extend it and adapt it to your needs.

Recently, I found very good solution how to generate sequence numbers between two numbers. For this purpose I was always using one table filled with sequential numbers from x to y. I was creating new rows whenever I needed it. But, the solution presented at Oracle Q & A, is so simple and practical.

select (lvl + &v_from - 1) myseq
from (
select *
from (
select level lvl
from dual
connect by level <= (&v_to - &v_from) + 1
)
)
order by myseq

This is very good place where you can find useful tips.

Sincerely,
Mihajlo Tekic

Oracle Streams problems ... Check the Alert Log first!

2006-01-20T15:49:00.000-08:00

Some days I just don’t have luck.
Whatever I tried to do, I couldn’t achieve it. But, fortunately it is only one day and not whole day :-).
I was investigating Oracle Streams whole month. I got good results and everything seemed to be going so well until, I decide to add new destination site in my Oracle Streams environment.
I was wondering why all those people on forums have issues with Oracle Streams, it is much better than Advanced Replication, and with Oracle 10g R2 you can use OEM to monitor your Streams environment.
One thing what bothered me, while I was learning and investigating streams, was lack of resources and practice examples how to setup streams environment. There is only one book about Oracle Streams available “Oracle Streams - High Speed Replication and Data Sharing” by Madhu Tumma., and off course I bought it. I found some good information there, but most of the scripts what I found inside were not so helpful, because the information what I got from them can be found very easy using some dictionary views.
Also, there is no chapter about how the existing Oracle Streams environment can be extended with new destination database.
No, don’t get me wrong, I don’t like to criticize this book, it is quite good for a beginner to understand Oracle Streams.
So, I continued to find the way to add another database to my destination databases set.
I created new propagation process in the source database.
I crated streams administrator in the destination database, instantiated all tables, created apply handlers and apply process.
But, when I started the apply process, the changes from the source database were not being applied to the target tables.
The first thing what I got in mind was to check whether some errors occurred in some of the processes (capture, propagation, apply). But, I didn’t find any errors there. I thought, here comes the difficult part.
I checked the propagation jobs aq$_schedules. I notice that all propagation jobs have LAST_RUN value, except the propagation job for the new database. Oh, there you go, I said, there my problem is.
I checked everything related to the propagation process, I was dropping and re-creating it several times. Nothing happened…still nothing.
I was searching on the net, trying to find something related to the problem I have, but unfortunately except many old posts (most of them related to Oracle Streams for Oracle 9i) I didn’t find anything useful.
I open the OEM, and tried to monitor the Streams process from there. I notice that the propagation process is propagating messages. Then I decide to check and monitor Apply process. I saw messages are arriving, but nothing being applied.
This is good, this is progress, I thought and continued to check all components of the apply process (queue, queue tables, handlers, etc).
I spent almost whole day checking, researching, investigating, and trying to figure out what is the reason for my issue. During that time, I notice many OEM “bugs” related to Streams monitoring features: if one propagation process is disabled, there is no way to enable it again if it is not first listed in the web page :-); you can not create propagation rule if some of the global names are longer than 30 characters :-):-) .

Finally, I decide to check the alert log of the destination database, and I found this:

knllgobjinfo: MISSING Streams multi-version data dictionary!!!

I found that I that the tables at the source database are not instantiated.
I did it using DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION, after that re-instantiated the tables in the destination database, and that was it … It worked.

TIP:
Check your alert log first, anytime when you have a problem with your Oracle Streams environment, and that problem is not a recognized error stored in DBA_CAPTURE, DBA_PROPAGATION or DBA_APPLY_ERROR views.

However, these are the steps what should be taken if you want to add new destination site in your Oracle Streams environment:

1. Stop Capture Process
2. Create Propagation Process, add propagation rules for all tables which are going to be replicated.
3. Create Streams Queue for receiving changes from source queue
4. Create AQ Agent and enable its privileges on STREAM ADMINISTRATOR
5. Add the AQ Agent as subscriber to Streams Queue
6. Create a dml handler procedure for DML statements issued on the tables
7. Set the DML Handlers for each table in the environment
8. Create apply process for the destination database
9. Set instantiation SCN for tables at the source database
10. Set instantiation SCN for tables at the destination database
11. Run apply process
12. Run capture process

For the end, I must say that from my perspective Oracle Streams are much better and easier to work with than Materialized Views (Advanced Replication).

But, nothing is perfect …

Thanks,
Mihajlo Tekic

About this blog ...

2006-01-17T14:31:00.001-08:00

This blog is dedicated to Oracle database and Oracle products.
I will try to cover topics related to Oracle technologies and address possible advantages and disadvantages of certain Oracle solutions.
I am looking for contributors who are willing to write and share their own experience about Oracle database and Oracle products.

Sincerely,
Mihajlo Tekic