Difference between Dataguard and Active Dataguard

I  found  people are bit confused between Dataguard and Active Data guard . They assume that Active dataguard is having different configuration or properties .  Here, i have tried to cover the Active dataguard .

Active Data Guard is a new option for Oracle Database 11g Enterprise Edition . Oracle Active Data Guard enables read-only access to a physical standby database . Active Data Guard is the possibility to query  our Physical Standby Database while it is constantly actualized to reflect the state of the Primary Database . It is additional to 11g Data Guard and comes with an extra charge .

Oracle Active Data Guard enhances the Quality of Service (QoS) for production databases by off-loading resource-intensive operations to one or more standby databases, which are synchronized copies of the production database. With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently supports all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups. Oracle Active Data Guard thus is a very effective way to insulate interactive users and critical business tasks on the production system from the impact of such long-running operations. Oracle Active Data Guard provides the additional benefit of high availability and disaster protection by quickly failing over to the standby database in the event of a planned or an unplanned outage at the production site.

The Active Data Guard  contains the following features :

• Physical Standby with Real-time Query
• Fast Incremental Backup on Physical Standby
• Automatic Block Repair

If a physical standby database in a Data Guard configuration has any of the above features enabled, then the Active Data Guard option must be licensed for every such physical standby, and also for the primary database.

Conversion from Physical standby to Active Data Guard :
We can convert the physical standby  into active Data Guard standby . Below are the steps

1.) Stop Apply Services
SQL> alter database recover managed standby database cancel ;

2.) Shut the database and open in mount stage
SQL> shut immediate
SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect ;
SQL> alter database open ;
It enables us to have a physical standby read only open, while redo apply is still done in the background .

How to Check if Active Data Guard is  Enabled  or Not 
Use the following query to confirm that Data Guard is in active mode:

SQL> select  'Using Active Data Guard' ADG   from v$managed_standby m,v$database d where m.process like 'MRP%'  ;
        ADG
-----------------------
Using Active Data Guard

or from standby datbase

SQL> select open_mode,controlfile_type from v$database;
OPEN_MODE                              CONTROLFILE
--------------------                        ------------------
READ ONLY WITH APPLY            STANDBY


If the query does not return the above result, and instead returns : no rows selected, then Active Data Guard is not enabled.


Enjoy     :-) 

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     :-) 

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     :-)