Monday, February 27, 2012

Difference Between Unique Indexes and Unique Constraints


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 ...

SQL> create table T1 (id number ) ;
Table created.

SQL> alter table T1 add constraint  T_ID_IDX  unique(id) ;
Table altered.

SQL> select index_name,table_name,uniqueness from dba_indexes where table_name='T1';
INDEX_NAME         TABLE_NAME            UNIQUENES
----------------        -------------------         ---------------
T_ID_IDX                  T1                            UNIQUE

SQL> select constraint_name,constraint_type ,table_name from dba_constraints where table_name='T1' and owner='HR' ;
CONSTRAINT_NAME      C         TABLE_NAME
------------------------      ----       -----------------
T_ID_IDX                      U             T1

Here, we see that when we have created a table unique index get created . Now have another look ..

SQL> create  table  T2 (id number )  ;
Table created.

SQL>  create  unique  index  T2_id_idx  on  T2(id) ;
Index created.

SQL> select index_name,table_name,uniqueness from dba_indexes where table_name='T2'  ;
INDEX_NAME        TABLE_NAME       UNIQUENES
------------------     ------------------      ---------------
T2_ID_IDX                      T2               UNIQUE

SQL>  select constraint_name,constraint_type ,table_name from dba_constraints where table_name='T2' and owner='HR' ;
no rows selected

SQL> alter table T2  add constraint  T2_ID_IDX    unique(id) ;
Table altered.

Now, we expecting  two indexes i.e;  one from the unique index and other from unique constraints . let's look on the below query :

SQL> select constraint_name,constraint_type ,table_name from dba_constraints where table_name='T2' and owner='HR' ;
CONSTRAINT_NAME       C         TABLE_NAME
-------------------------     ----        ------------------
T2_ID_IDX                      U                  T2

SQL> drop index T2_ID_IDX;
drop index T2_ID_IDX
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

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. 


Enjoy     :-) 


Tuesday, February 21, 2012

What is bootstrap?

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
SQL> startup mount ; 
SQL> alter session set events '10046 trace name context forever, level 12 ' ; 
SQL> alter database open ; 
SQL>  alter session set events '10046 trace name context off ' 
SQL> ORADEBUG SETMYPID 
SQL> ORADEBUG TRACEFILE_NAME 
The sql_trace of the above process explains the following operations behind startup. The bootstrap operation happens between MOUNT stage and OPEN stage.
1.)  The first SQL after in the above trace shows the creation of the bootstrap$ table. Something similar to the following:
create table bootstrap$ ( line# number not null, obj# number not null, sql_text varchar2(4000) not null) storage (initial 50K objno 56 extents (file 1 block 377))
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 :

hist_head$
histgrm$
i_hh_obj#_col#
i_hh_obj#_intcol#
i_obj#_intcol#
i_h_obj#_col#
c_obj#_intcol#
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.


Reference : click here


Enjoy     :-) 


Wednesday, February 8, 2012

ORA-01078 : Failure in Processing System Parameters


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 

C:\>set ORACLE_SID=noida  
C:\>sqlplus sys/xxxx  as sysdba 
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 8 14:14:54 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORA-01078 : failure in processing system parameters
LRM-00109 : couldnot open parameter file 'C:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATAB ASE\INITNOIDA.ORA'
SQL> exit
Disconnected


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 

C:\>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Feb 8 14:16:38 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)

RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITNOIDA.ORA'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes
Fixed Size                             1373264 bytes
Variable Size                         75500464 bytes
Database Buffers                   75497472 bytes
Redo Buffers                         6647808 bytes

RMAN> restore spfile from autobackup;
Starting restore at 08-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=97 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/08/2012 14:32:35
RMAN-06495: must explicitly specify DBID with SET DBID command

RMAN> restore spfile from 'E:\rman_backup\cf\C-1523131116-20120208-01';
Starting restore at 08-FEB-12
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP E:\rman_backup\cf\C-1523131116-20120208-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-FEB-12

RMAN> shutdown
Oracle instance shut down

RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area     535662592bytes
Fixed Size                            1375792     bytes
Variable Size                        331350480 bytes
Database Buffers                 197132288  bytes
Redo Buffers                       5804032      bytes

RMAN> exit
Recovery Manager complete.

C:\>set ORACLE_SID=noida
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 8 14:38:11 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select  name , open_mode  from  v$database ;
NAME        OPEN_MODE
---------    --------------------
NOIDA        READ WRITE

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 .

SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile 
C:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILENOIDA.ORA
System parameters with non-default values:
---------->>  copy from here    <<--------------------
  processes                = 150
  memory_target            = 816M
  control_files            = "C:\APP\NEERAJS\ORADATA\NOIDA\CONTROL01.CTL"
  control_files            = "C:\APP\NEERAJS\FLASH_RECOVERY_AREA\NOIDA\CONTROL02.CTL"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  log_archive_dest_1       = "LOCATION=D:\archive\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
DB_UNIQUE_NAME=noida"
  log_archive_dest_2       = "SERVICE=delhi LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=delhi"
  log_archive_dest_state_2 = "defer"
  fal_client               = "NOIDA"
  fal_server               = "DELHI"
  log_archive_format       = "ARCH_%r_%t_%s_%D_%T"
  db_recovery_file_dest    = "C:\app\Neerajs\flash_recovery_area"
  db_recovery_file_dest_size= 3852M
  standby_file_management  = "AUTO"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=noidaXDB)"
  local_listener           = ""
  audit_file_dest          = "C:\APP\NEERAJS\ADMIN\NOIDA\ADUMP"
  audit_trail              = "DB"
  db_name                  = "noida"
  open_cursors             = 300
  diagnostic_dest          = "C:\APP\NEERAJS"
----------------->>     Upto Here   << ---------------
Wed Feb 02/08/2012 14:38:35
PMON started with pid=2, OS id=1456 
Wed Feb  02/08/2012 14:38:35
VKTM started with pid=3, OS id=1484 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Feb 07 17:01:27 2012
GEN0 started with pid=4, OS id=1056 
Wed  Feb  02/08/2012 14:32:35
DIAG started with pid=5, OS id=3524 
Wed Feb  02/08/2012 14:32:35
DBRM started with pid=6, OS id=228

Create the pfile the above parameter and save it(say C:\initnoida.ora')  and startup the database  as

C:\>set ORACLE_SID=noida
C:\>sqlplus sys/xxxx as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile='C:\initnoida.ora' ; 
SQL> startup
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$database;
NAME        OPEN_MODE
---------     -----------------
NOIDA        READ WRITE


We can also get the parameters from the corrupted spfile using utilities like strings available on Unix.
Similar utilities can be found for Windows as well.
$ strings  spfile<SID>.ora     init<SID>.ora 




Enjoy    J J J