All the Views expressed here are my own and do not reflect opinions or views of the anyone else.All the views are tested on my testing environment and kindly test the post before applying anything on production.You can reach to me at neeraj.vishen@gmail.com .

Monday, February 27, 2012

There is a very general confusion that whenever we create a unique key constraint or primary key then a corresponding index is created . Primary key and Unique key creates the unique indexes , but this is not always true . Lets have a look ...

Above query show only one indexes . Hence from the above demo, we can only say that " a unique constraint does not necessarily create an index or a unique constraint does not necessarily create a UNIQUE index " .

If we want a unique index in place, it is suggested we should explicitly create it by using CREATE UNIQUE INDEX . A primary key or unique constraint is not guaranteed to create a new index, nor is the index they create guaranteed to be a unique index. Therefore, if we desire a unique index to be created for query performance issues, we should explicitly create one.

A question may arises that why do we need a unique constraint when we already have a unique index?
The reason are

1. ) The difference between a unique index and a unique constraint starts with the fact that the constraint is a rule while the index is a database object that is used to provide improved performance in the retrieval of rows from a table. It is a physical object that takes space and is created with the DDL command .

2.) we can use either a unique OR non-unique index to support a unique constraint. Constraints are metadata, more metadata is good. We can define a foreign key to a unique constraint, not so a unique index.

3.) A constraint has different meaning to an index. It gives the optimiser more information and allows us to have foreign keys on the column whereas a unique index doesn't. But most importantly because it is the right way to do it.

Tuesday, February 21, 2012

Bootstrap is a technique for loading the first few instructions of a computer program into active memory and then using them to bring in the rest of the program.

What is bootstrap in Oracle ?

In Oracle, Bootstrap refers to loading of metadata (data dictionary) before we OPEN the database.Bootstrap objects are classified as the objects (tables / indexes / clusters) with the object_id below 56 as bootstrap objects. These objects are mandatory to bring up an instance, as this contains the most important metadata of the database.

What happens on database startup?

This shall be explained by setting the SQL_TRACE while opening the database.Connect as sysdba and do the following

This sys.bootstrap$ table contains the DDL’s for other bootstrap tables (object_id below 56). Actually these tables were created internally by the time of database creation (by sql.bsq), The create DDL passed between MOUNT and OPEN stage will be executed through different driver routines. In simple words these are not standard CREATE DDLs.

While starting up the database oracle will load these objects into memory (shared_pool), (ie) it will assign the relevant object number and refer to the datafile and the block associated with that. And such operations happen only while warm startup.

@ The internals of the above explained in ‘kqlb.c’.

2.) Now a query executed against the sys.bootstrap$ table, which holds the create sql’s for other base tables.

select line#, sql_text from bootstrap$ where obj# != :1 (56)

Subsequently it will create those objects by running those queries.

Object number 0 – (System Rollback Segment)

Object number 2 to 55 (Other base tables)

Object number 1 is NOT used by any of the objects.

3.) Performs various operations to keep the bootstrap objects in consistent state.Upon the successful completion of bootstrap the database will do the other tasks like recovery and will open the database.

Which objects are classified as bootstrap objects in oracle database?

Objects with data_object_id less than 56 are classified as core bootstrap objects.The objects are added to the bootstrap. The objects affected are :

From 10.1 the following objects have been added:
fixed_obj$
tab_stats$
ind_stats$
i_fixed_obj$_obj#
i_tab_stats$_obj#
i_ind_stats$_obj#
object_usage

These additional objects shall be re-classified (or) ignored by following methods.
1. Opening the database in migrate mode
2. Using event 38003

Event 38003 affects the bootstrap process of loading the fixed cache in kqlblfc(). Per default certain objects are marked as bootstrap objects (even though they are not defined as such in sys.bootstrap$) but by setting the event they will be left as non-bootstrapped.

What is bootstrap process failure? or ORA-00704

This ORA-00704 error SERIOUS if reported at startup. This error refers to some problem during bootstrap operation. Any ORA-00704 error on STARTUP / RECOVER is serious, this error normally rose due to some inconsistency with the bootstrap segments (or) data corruption on bootstrap$ (or) any of the base tables below object_id 56. After this error it might not allow to open that database.

When ORA-00704 shall occur?

1. There is a probable of this error when any unsupported operations are tried to force open the database.2. This error can also occur when system datafile has corrupted blocks. (ORA-01578)3. In earlier releases of oracle (prior to 7.3.4 and 8.0.3) this issue shall arise due to Bug 434596

The option is to restore it from a good backup and recover it.

-> If the underlying cause is physical corruption that is due to hardware problems then do complete recovery.

-> If the issue is not relating to any physical corruption, then the problem could be due some unsupported actions on Bootstrap, and a Point In Time Recovery would be an option in such cas.

Wednesday, February 8, 2012

ORA-01078 is generally occuring when a corrupt spfile file is being used or the parameter file (spfile/pfile) may be missing from the default or specified location . The spfile is present in the default location $ORACLE_HOME/dbs for Unix and %ORACLE_HOME%\database for window .We may get ORA-01078 when starting the database as

There is no way the spfile can be repaired or modified manually . The only solution is to either restore the spfile from the rman backup or recreate a new pfile and then create spfile . If a SPFILE backup exists , restore it to the original location . The SPFILE backup could be a copy of the spfile or from a RMAN backup. Restore the spfile from backup as

If no spfile backup exists,we can use an existing pfile and startup the database , but this pfile may or may not contain all the non default parameter because the spfile is updated dynamically . So it is better to obtain the list of all the non-default parameter list in the alert log file and then create the pfile . Once the pfile is created we can create the spfile from pfile . Below is some contained of the my alert log file .

Tuesday, February 7, 2012

ORA-27101 and ORA-01034 combined may occurs in various scenarios .One of the reason for this error is that the database is not up. When we try to connect with oracle database or sometimes during installation we may get this error . We may face this error in some other scenario's too . Here are few possible scenario's ( some scenario's are from window platform and some are from Linux platform ) .

Case 2 : On a system with multiple IP addresses, when we connect with sqlplus locally on the server (i.e. "sqlplus user/password") everything is OK, but connecting through a TNS alias, either from the network or locally on the server, we get the the following errors :

We need to Manually startup the database or check whether the listener is started or not . So either connect as
$export ORACLE_SID=noida
$ sqlplus sys/password as sysdba
SQL>startup
or
check the status of listener if not started then start the listener as
$lsnrctl
lsnrctl> stop
lsnrctl>start
lsnrctl>exit
$export ORACLE_SID=noida
$sqlplus sys/password@noida as sysdba
SQL> startup

Case 3 : Make sure while connecting, that the ORACLE_SID and ORACLE_HOME is correctly set . There should not be trailing trash in ORACLE_HOME path . Remove the extra "/" from the end of ORACLE_HOME

Friday, February 3, 2012

SQL*Plus is the commonly used tools by the DBAs . Sql*Plus Error Logging is one of the new useful feature in Oracle 11g .It provides additional methods of trapping errors . When error logging is enabled, it records sql , pl/sql and sql*plus errors and associated parameters in an error log table(SPERRORLOG by default) and we can then query the log table to review errors resulting from a query.

Note : It is a 11g SQL*Plus feature not with database engine.

Why Error Logging ?

We normally spool the syntax to capture the errors from the scripts and track the spool logs for the error output . This is work fine for single or few script but cumbersome when multiple scripts are involved.Secondly we need the OS path to store the scripts,permission and all . To overcome from this scenario's Error Logging is useful feature to capture and locate the errors in the database table rather than the OS files .

Steps to Activate the Error Logging :

1.) Check the status of Error Logging : To check the status of error logging , fire the below command

SQL> show errorloggingerrorlogging is OFFNote: Error logging is set OFF by default.

2.) Enable the Error Logging : Whenever we enable the error loging the default table SPERRORLOG is created . Enable by using the below command

SQL> set errorlogging onSQL> show errorloggingerrorlogging is ON TABLE SCOTT.SPERRORLOGAs, we see that the default table "SPERRORLOG" is created in scott schemas, since the current user is scott . Hence, sperrorlog table is created current user .

Creating a User Defined Error Log Table :

We can create one or more error log tables to use other than the default . Before specifying a user defined error log table , let's have look on default errorlog

For each error, the error logging feature logs the following bits of information. To use a user defined log table, we must have permission to access the table, and we must issue the SET ERRORLOGGING command with the TABLE schema.tablename option to identify the error log table and the schema if applicable. Here is syntax to create user-defined table..

Step 4 : Check the error logging from the user-defined errorlog SQL> select * from hr.Error_log_table ;
SQL> commit ;Without commit, other sessions won’t see this information. Here i have commit it and taken the output from other session for the sake of proper formatting purpose.

We can truncate to clear all existing rows in the error log table and begins recording errors from the current session. as

Search This Blog

Translate My Page

About Me

I have started this blog to share my knowledge and experience with other Oracle DBA enthusiasts.I have experience on database architecture ,design and administrating ranging from 9i,10g,11g on various platforms.My main interests are high availability and disaster recovery solutions for mission critical 24×7 systems.