Creating Standby database using Active Duplication – Oracle Database 11g

Introduction:

Oracle database 11g introduced a new feature called Active database duplication. Using this feature you can create a new database (primary/standby) from your current running database. This feature does not needs any backup to be taken, nor we have to do any restore.

Creating active duplication is a RMAN feature and command for creating active duplication comes with various options.

Following is the command used for active duplication

duplicate target database to <DB_NAME> from active database;

Following are the various arguments that we can pass to this command

FROM ACTIVE DATABASE: (This is supplied if we want to do active database duplication)

Specifies that the files for the standby database should be provided directly from the source database and not from a backup of the source database

NOFILENAMECHECK:

Prevents RMAN from checking whether datafiles of the source database share the same names as the standby database files that are in use.

The NOFILENAMECHECK option is required when the standby and primary datafiles and online redo logs have identical filenames. Thus, if you want the duplicate database filenames to be the same as the source database filenames, and if the databases are in different hosts, then you must specify NOFILENAMECHECK

SPFILE:

Copies the server parameter file from the source database to the operating system-specific default location for this file on the standby database.

RMAN uses the server parameter file to start the auxiliary instance for standby database creation. Any remaining options of the DUPLICATE command are processed after the database instance is started with the server parameter file.

If you execute DUPLICATE with the SPFILE clause, then the auxiliary instance must already be started with a text-based initialization parameter file. In this case, the only required parameter in the temporary initialization parameter file is DB_NAME, which can be set to any arbitrary value. RMAN copies the binary server parameter file, modifies the parameters based on the settings in the SPFILE clause, and then restarts the standby instance with the server parameter file. When you specify SPFILE, RMAN never uses the temporary text-based initialization parameter file to start the instance.

If FROM ACTIVE DATABASE is specified on DUPLICATE, then a server parameter file must be in use by the source database instance. If FROM ACTIVE DATABASE is not specified on DUPLICATE, then RMAN restores a backup of the server parameter file to the standby database.

PARAMETER_VALUE_CONVERT:

Replaces the first string with the second string in all matching initialization parameter values. Note that DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are exceptions to this rule and are not affected.

You can use PARAMETER_VALUE_CONVERT to set a collection of initialization parameter values and avoid explicitly setting them all. For example, if the source database uses disk group +ALPHA while the standby database will use +BETA, then you could modify all parameters that refer to these disk groups by specifying SPFILE PARAMETER_VALUE_CONVERT (‘+ALHPA’,’+BETA’).

DORECOVER:

Specifies that RMAN should recover the standby database after creating it. If you specify an untilClause, then RMAN recovers to the specified SCN or time and leaves the database mounted.

RMAN leaves the standby database mounted after media recovery is complete, but does not place the standby database in manual or managed recovery mode. After RMAN creates the standby database, you must resolve any gap sequence before placing it in manual or managed recovery mode, or opening it in read-only mode.

For more details on arguments to be supplied check – http://download.oracle.com/docs/cd/B28359_01/backup.111/b28273/rcmsynta020.htm

Following is one example where I created a standby database using active database duplication.

Pre-requisite:

We should have a primary database up and running
pfile, spfile and listener.ora file should be available for both primary and standby
All the relevent directories for datafiles and diagnostic dest should be present
Password file for standby should be present
Entry should exist in /etc/oratab for both primary and standby

Steps for active database duplication

Step 1) nomount the standby database

sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Wed May 4 06:53:47 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 2321612800 bytes
Fixed Size                  2174128 bytes
Variable Size            1198015312 bytes
Database Buffers         1073741824 bytes
Redo Buffers               47681536 bytes
SQL>


Step 2) Check the unique name for standby database

SQL> show parameters uniq

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      orcl_a

Step 3) Start active duplication

rman target sys/<password>@orcl1 auxiliary=sys/<password>@orcl1_a

Recovery Manager: Release 11.1.0.7.0 - Production on Wed May 4 07:01:22 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL1 (DBID=998984585)
connected to auxiliary database: ORCL1 (not mounted)

RMAN> duplicate target database to orcl1 from active database nofilenamecheck; --> This will create a new primary database again
RMAN> duplicate target database for standby from active  database nofilenamecheck; --> This will create a new standby database

Starting Duplicate Db at 04-MAY-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1122 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   file  '/opt/app/oracle/product/11.1.0.7/A24db/dbs/orapworcl1' auxiliary format
 '/opt/app/oracle/product/11.1.0.7/A24db/dbs/orapworcl1'   ;
}
executing Memory Script

Starting backup at 04-MAY-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1109 device type=DISK
Finished backup at 04-MAY-11

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/ctl-01/databases/orcl1/control.ctl';
   restore clone controlfile to  '/ctl-02/databases/orcl1/control.ctl' from
 '/ctl-01/databases/orcl1/control.ctl';
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 04-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.1.0.7/A24db/dbs/snapcf_orcl1.f tag=TAG20110504T072433 RECID=2 STAMP=750237875
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 04-MAY-11

Starting restore at 04-MAY-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 04-MAY-11

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/fs-b01-a/databases/orcl1/temp-01.dbf";
   set newname for tempfile  2 to
 "/fs-a01-a/databases/orcl1/dba_temp-01.dbf";
   set newname for tempfile  3 to
 "/fs-a01-a/databases/orcl1/temp-02.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/fs-b01-a/databases/orcl1/system-01.dbf";
   set newname for datafile  2 to
 "/fs-a01-a/databases/orcl1/undo_t1-01.dbf";
   set newname for datafile  3 to
 "/fs-a01-a/databases/orcl1/sysaux-01.dbf";
   set newname for datafile  4 to
 "/fs-a01-a/databases/orcl1/administrator-01.dbf";
   set newname for datafile  5 to
 "/fs-b01-a/databases/orcl1/administrator_idx-01.dbf";
   set newname for datafile  6 to
 "/fs-a01-a/databases/orcl1/replication-01.dbf";
   set newname for datafile  7 to
 "/fs-b01-a/databases/orcl1/replication_idx-01.dbf";
   set newname for datafile  8 to
 "/fs-a01-a/databases/orcl1/rcvcat-001.dbf";
   set newname for datafile  9 to
 "/fs-b01-a/databases/orcl1/rcvcat_idx-001.dbf";
   set newname for datafile  10 to
 "/fs-d01-a/databases/orcl1/sysaux-20090310204613.dbf";
   set newname for datafile  11 to
 "/fs-d01-a/databases/orcl1/system-20090421171504.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/fs-b01-a/databases/orcl1/system-01.dbf"   datafile
 2 auxiliary format
 "/fs-a01-a/databases/orcl1/undo_t1-01.dbf"   datafile
 3 auxiliary format
 "/fs-a01-a/databases/orcl1/sysaux-01.dbf"   datafile
 4 auxiliary format
 "/fs-a01-a/databases/orcl1/administrator-01.dbf"   datafile
 5 auxiliary format
 "/fs-b01-a/databases/orcl1/administrator_idx-01.dbf"   datafile
 6 auxiliary format
 "/fs-a01-a/databases/orcl1/replication-01.dbf"   datafile
 7 auxiliary format
 "/fs-b01-a/databases/orcl1/replication_idx-01.dbf"   datafile
 8 auxiliary format
 "/fs-a01-a/databases/orcl1/rcvcat-001.dbf"   datafile
 9 auxiliary format
 "/fs-b01-a/databases/orcl1/rcvcat_idx-001.dbf"   datafile
 10 auxiliary format
 "/fs-d01-a/databases/orcl1/sysaux-20090310204613.dbf"   datafile
 11 auxiliary format
 "/fs-d01-a/databases/orcl1/system-20090421171504.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /fs-b01-a/databases/orcl1/temp-01.dbf in control file
renamed tempfile 2 to /fs-a01-a/databases/orcl1/dba_temp-01.dbf in control file
renamed tempfile 3 to /fs-a01-a/databases/orcl1/temp-02.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 04-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/fs-a01-a/databases/orcl1/rcvcat-001.dbf
output file name=/fs-a01-a/databases/orcl1/rcvcat-001.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/fs-b01-a/databases/orcl1/rcvcat_idx-001.dbf
output file name=/fs-b01-a/databases/orcl1/rcvcat_idx-001.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/fs-d01-a/databases/orcl1/sysaux-20090310204613.dbf
output file name=/fs-d01-a/databases/orcl1/sysaux-20090310204613.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/fs-d01-a/databases/orcl1/system-20090421171504.dbf
output file name=/fs-d01-a/databases/orcl1/system-20090421171504.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/fs-a01-a/databases/orcl1/undo_t1-01.dbf
output file name=/fs-a01-a/databases/orcl1/undo_t1-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/fs-a01-a/databases/orcl1/administrator-01.dbf
output file name=/fs-a01-a/databases/orcl1/administrator-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/fs-b01-a/databases/orcl1/administrator_idx-01.dbf
output file name=/fs-b01-a/databases/orcl1/administrator_idx-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/fs-a01-a/databases/orcl1/replication-01.dbf
output file name=/fs-a01-a/databases/orcl1/replication-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/fs-b01-a/databases/orcl1/replication_idx-01.dbf
output file name=/fs-b01-a/databases/orcl1/replication_idx-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/fs-b01-a/databases/orcl1/system-01.dbf
output file name=/fs-b01-a/databases/orcl1/system-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/fs-a01-a/databases/orcl1/sysaux-01.dbf
output file name=/fs-a01-a/databases/orcl1/sysaux-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 04-MAY-11

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=750238434 file name=/fs-b01-a/databases/orcl1/system-01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=750238434 file name=/fs-a01-a/databases/orcl1/undo_t1-01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=750238434 file name=/fs-a01-a/databases/orcl1/sysaux-01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=750238434 file name=/fs-a01-a/databases/orcl1/administrator-01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=750238434 file name=/fs-b01-a/databases/orcl1/administrator_idx-01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=750238434 file name=/fs-a01-a/databases/orcl1/replication-01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=750238434 file name=/fs-b01-a/databases/orcl1/replication_idx-01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=750238434 file name=/fs-a01-a/databases/orcl1/rcvcat-001.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=750238434 file name=/fs-b01-a/databases/orcl1/rcvcat_idx-001.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=750238434 file name=/fs-d01-a/databases/orcl1/sysaux-20090310204613.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=750238434 file name=/fs-d01-a/databases/orcl1/system-20090421171504.dbf
Finished Duplicate Db at 04-MAY-11

RMAN>

This completes active duplication. Your standby is ready now.
Hope this helps !!

Avoiding “no data found” : Tips

Some time we face issue about no data found depending on selection criteria. And when this happens in PLSQL procedure we get annoying error

ORA-01403: No data found and execution stops.

One way to get around this error is to add exception block in our PLSQL procedure as shown below

EXCEPTION WHEN NO_DATA_FOUND THEN

<Take Action>

But disadvantage of using this approach is that it will not process rest of the code and control will jump to exception block and from there on continue till the end. If we get this error in the middle of FOR loop or WHILE loop, rest of the records will not get processed.

One of the way to deal with this is joining your SQL with dual.

In that case you are sure to get a null value even if the row does not exists.

Example in my case I want to find the memory value and spfile value of one of the parameter in database by querying v$parameter and v$spparameter view.

If I do a simple join as below I am going to get “no data found” if these view does not have parameter

SQL> select a.name, a.value, b.value from v$parameter b, v$spparameter a
  2  where a.name = b.name
  3  and a.name = 'shared_pool_reserved_min_alloc';

no rows selected

SQL>

But if we join with dual we can get the name of the parameter and a null value in front of that

SQL> select c.col, a.value, b.value
  2  from v$parameter b, v$spparameter a, (select 'shared_pool_reserved_min_alloc' col from dual) c
  3  where c.col = a.name (+) and c.col = b.name (+);

COL                            VALUE                VALUE
------------------------------ -------------------- --------------------
shared_pool_reserved_min_alloc

SQL>

This is easier to process.

Hope this helps !!

An insight into Oracle Index rebuild

You must have gone through my previous article on Oracle Index rebuild – 10g Vs 11g – https://avdeo.com/2011/03/17/oracle-index-rebuild-online-10g-vs-11g/

Well, there is another nice article written by a friend of mine Sumit Bhatia.

He has talked about the actions and events happening while rebuilding an index online in oracle database 11g.

Different wait events that we encounter and to make it more practical he has also build a script which gives you the exact status for your index rebuild.

You can refer to the article at http://fordba.wordpress.com/2011/04/05/online-ndx-rebuild/

You can refer to the script at http://fordba.wordpress.com/2011/03/30/ndx-progress-sql/

Just to mention, this script is by no means a complete script and enhancements can be underway. Also this script should not be used in production. If you want to use in production, you can do it at your own risk.

Hope this helps !!

Virual Index and Invisible Index

Oracle has come up with a feature called virtual index in oracle 9i. This feature allow us to test an index on a table without actually adding an index on the table. The table will be visible only in our session and will be used by our queries only (if optimizer decide it to use). So basically the index will be visible to optimizer only in our sessions. Optimizer running query in other sessions won’t be able to see this index.

Virtual Index in Oracle 9i

Utility of using virtual index is that, suppose we have a table having huge number of rows and it is getting joined with other tables. If we see that optimizer is creating a plan which is costly and SQL tuning advisor suggest us to create an index on a column, then in case of production database we cannot simply create an index and test the changes. We need to make sure that creating that index wont have any negative impact on the execution plan of other queries running in this database.

So there is where we can use virtual index. Here is how virtual index works.

1) Creating a table

SQL> create table test as select * from dba_objects;

Table created.

2) Try selecting a value from test table

SQL> select * from test where object_name = 'STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| TEST |
----------------------------------

3) Create a virtual Index on test table

SQL> create index test_idx_1 on test(object_name) nosegment;

Index created.

In order to create a virtual index, we need to give NOSEGMENT at the end of the create index statement. This will just create a index on the object_name column of test table. But it will not create an index segment in database.

You can check this by querying dba_objects and dba_indexes tables.

SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_IDX_1';

no rows selected
SQL> col OBJECT_NAME format a30;
SQL> select object_name, object_type from dba_objects where object_name = 'TEST_IDX_1';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST_IDX_1                     INDEX

So, object exists in database, but we dont have segment for the same.

Now if you try to run the same select command on test table, still optimizer will NOT use virtual index.

SQL> select * from test where object_name = 'STANDARD';
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| TEST |
----------------------------------

In order for optimizer to use virtual index, you need to set a parameter called _USE_NOSEGMENT_INDEXES in your session

SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;

Session altered.

Once you set this hidden parameter, optimizer will start using the virtual index you created on this table

SQL> select * from test where object_name = 'STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1221747299

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     2 |   354 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     2 |   354 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX_1 |    46 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

If you run this query from some other session, it wont use virtual index.

you can analyze virtual indexes

SQL> analyze index TEST_IDX_1 compute statistics;

Index analyzed.

You CANNOT rebuild a virtual index

SQL> alter index TEST_IDX_1 rebuild;
alter index TEST_IDX_1 rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

Finally, if the index doesn’t seem to satisfy your requirement, then you can drop the virtual index.

SQL> drop index TEST_IDX_1;

Index dropped.

Invisible Index in 11g

We have a similar concept in Oracle database 11g called invisible index.

In case of invisible index, we can check if creating a new index is actually going to improve the performance or not. This index will not be visible to any other session and it will not be used by any other existing query run on same table.

SQL>drop table t;

Table dropped.

SQL>create table t as select * from dba_objects;

Table created.

SQL>create index t_ind1 on t(object_name) invisible;

Index created.

You can also make existing index as invisible using alter index command. You can make existing invisible index as visible. As soon as you make index visible, your existing queries will start using new index.

TDCLTN1>alter index t_ind1 visible;
TDCLTN1>alter index t_ind1 invisible;

We have a new column in USER_INDEXES called VISIBILITY. This tells whether an index is visible or not.

SQL>select index_name,VISIBILITY from user_indexes where index_name='T_IND1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
T_IND1                         INVISIBLE

So how does INVISIBLE index works ?

Now that we have created an INVISIBLE index, lets try to run a query on new table T and see if it uses the index.

SQL>explain plan for
 2  select * from t where object_name='STANDARD';

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T    |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 1 - filter("OBJECT_NAME"='STANDARD')

So we can see its doing a full table scan.

In order to use the invisible index, we have a new parameter introduced in 11g – OPTIMIZER_USE_INVISIBLE_INDEXES

This parameter can be set to either TRUE or FALSE

If set to true, we are asking optimizer to use the invisible index if it can make a better plan using that.

Lets try to set this parameter to TRUE and run the same query.

SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     2 |   202 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     2 |   202 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND1 |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='STANDARD')

Now it has used the index and the cost of the query also reduced.

If the index seems to make positive impact on the other query as well, then you can make this index as visible.

Note that if the index is INVISIBLE, then optimizer will ignore the index and 10053 trace will show index as “UNUSABLE”

Difference:

So based on above examples, we can see that the difference is

– In case of virtual index, we dont actually create the index, its just the place holder. In case of invisible index we actually create the index

– In case of virtual index, we can check if the index is getting used or not using explain plan and performance of the query can be mentioned statistically by explain plan. In case of invisible index, we can actually run the query and check the performance benefit.

– We cannot make existing index as virtual, unless we have to drop it and create a no_segment index. We can make any index invisible.

Hope this helps !!

Oracle Index rebuild online – 10g Vs 11g

An index is basically used for faster access to tables. Over a period of time the index gets fragmented because of several DMLs running on table.
When the index gets fragmented, data inside the index is scattered, rows / block reduces, index consumes more space and scanning of index takes more time and more blocks for same set of queries.
To talk in index terminology, we will have a single root block, but as fragmentation increases there will be more number of branch blocks and more leaf blocks. Also the height of index will increase.

To fix the above issue, we go for index rebuild. During index rebuild, the data inside the index is reorganized and compressed to fit in minimum number of blocks, height of the index is reduced to minimum possible level and performance of queries will increase.
Your search becomes faster and your query will read less number of blocks.

There are 2 methods to rebuild the index.

1) Offline index rebuild – alter index <index name> rebuild;
2) Online index rebuild  – alter index <index name> rebuild online;

With offline index rebuild, table and index is locked in exclusive mode preventing any translations on the table. This is most intrusive method and is used rarely in production unless we know for sure that modules are not going to access the table and we have complete downtime.

With online index rebuild, transactions can still access the table and index. Only for very less amount of time the lock is acquired on the table (by index rebuild operation). For rest of the time table and index is available for transactions.
However there is a difference in a way this internal online rebuild mechanism works in 10g and 11g. With 11g things are refined further to minimize the impact.

I will first explain the 10g method of rebuilding the index, after that will see the behavior in 11g

10g behaviour:

The way online index build (OIB) works is by creating an IOT journal table to keep track of changes while the OIB is in progress and merge all the changes from journal table to complete index build operation.

If I have a table with the structure of (A NUMBER, B NUMBER, C CHAR(100), D NUMBER) and to create index on (A, B, C) column, Oracle would create journal table like:

create table “ORACLE”.”SYS_JOURNAL_18155″ (C0 NUMBER, C1 NUMBER, C2 VARCHAR2(100), opcode char(1), partno number, rid rowid, primary key( C0, C1, C2 , rid )) organization index;

Before 11g, OIB will get in the DML queue to lock the table exclusively while preventing the new DML’s to go through, once all the active transactions (ones which were initiated before the OIB) are completed, OIB will create the journal IOT table and release the exclusive table lock (it’ll still keep the share lock on the table to prevent any other DDL’s) for DML’s to continue.

As part of journal table creation, Oracle would create an internal trigger on the primary table to record all the changes to the journal table. Along with using all the index columns, journal table will add “ROWID” to that list to make it as primary key.

“OPCODE” column represents the type of operation like “I” for Insert and “D” for Delete.
“PARTNO” column represents partition number of the underlying table.

Among all the changes to a given row for any of the index key columns, only the most recent change for that record is visible in the journal table. Any update operation of index key columns would be converted to “DELETE” and “INSERT” in the journal table.

While rest of the user sessions populate journal table with the ongoing changes for index key columns, OIB session would be reading the table in consistent mode (as of the time journal table is created) to create the index followed by the final merge operation.

During the merge process, Oracle will read the journal table (IOT) leaf blocks from left to right to merge those changes with the index being built. As the journal table leaf block changes are applied, once a leaf block is fully consumed, it’s reference will be deleted from the branch block.

This process will continue until all leaf blocks are consumed and when it comes to the last leaf block, Oracle would stop all the DML’s again to do the final merge and drop the journal table before releasing the mode 6 exclsuive table lock.

As each leaf block is consumed, Oracle would mark each entry as deleted. If more DML’s happen while Oracle is doing the merge, it’ll do one more pass of walking through the leaf blocks, this process continues until the merge process is all done.

If there is any long running DML before issuing the OIB, Oracle would wait on the long running transaction and it will prevent upcoming DML’s also. This is one of the major problems with OIB. Same thing can happen when it is doing the final merge.

So in the above process lock is acquired 2 times, one during the start of index creation when journal table is created online index rebuild process needs to be take exclusive lock on table to prevent DMLs from changing data. Once journal table is created online index rebuild process will release DML lock and hold a shared lock. Any DMLs happening after this will have entry made into journal table.
Again at the end of the process online index rebuild process will try to take exclusive lock to merge the last block of journal table into the main index.

Following example demonstrate the same:

Create test table and insert huge number of rows

</pre>
SQL>create table test as select * from dba_objects;

Table created.

SQL>insert into test select * from test;

29493 rows created.

SQL>/

58986 rows created.

Like this add more rows till it becomes big

Check the size of table


SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'TEST';

SUM(BYTES)/1024/1024/1024
-------------------------
 .430053711

Create index on the table

SQL>create index I1 on test(OBJECT_NAME);

Index created.

Check the size of index.

SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'I1';

SUM(BYTES)/1024/1024/1024
-------------------------
 .129272461

Now from the session 1, try inserting a record in the TEST table

Session 1 SID: 3204
SQL Text :

SQL>insert into test (owner, object_name) values ('AVDEO','NEW_TEST');
1 row created.
SQL>

From session 2, try rebuilding the index online

Session 2 SID: 3046
SQL text :

alter index I1 rebuild online

From session 3, run another DML command.

Session 3 SID: 3827
SQL Text:

update test set OWNER = ‘DEO2’ where OWNER = ‘DEO’

If we check v$lock table we can see session 2 (online index rebuild) is waiting on session 1 (insert). So unless insert completes, session 2 doing online index rebuild will not get a exclusive lock.


SQL>select
 2      l.SID oracle_id,
 3      decode(TYPE,
 4          'MR', 'Media Recovery',
 5          'RT', 'Redo Thread',
 6          'UN', 'User Name',
 7          'TX', 'Transaction',
 8          'TM', 'DML',
 9          'UL', 'PL/SQL User Lock',
 10          'DX', 'Distributed Xaction',
 11          'CF', 'Control File',
 12          'IS', 'Instance State',
 13          'FS', 'File Set',
 14          'IR', 'Instance Recovery',
 15          'ST', 'Disk Space Transaction',
 16          'TS', 'Temp Segment',
 17          'IV', 'Library Cache Invalidation',
 18          'LS', 'Log Start or Switch',
 19          'RW', 'Row Wait',
 20          'SQ', 'Sequence Number',
 21          'TE', 'Extend Table',
 22          'TT', 'Temp Table', type) lock_type,
 23      decode(LMODE,
 24          0, 'None',
 25          1, 'Null',
 26          2, 'Row-S (SS)',
 27          3, 'Row-X (SX)',
 28          4, 'Share',
 29          5, 'S/Row-X (SSX)',
 30          6, 'Exclusive', lmode) lock_held,
 31      decode(REQUEST,
 32          0, 'None',
 33          1, 'Null',
 34          2, 'Row-S (SS)',
 35          3, 'Row-X (SX)',
 36          4, 'Share',
 37          5, 'S/Row-X (SSX)',
 38          6, 'Exclusive', request) lock_requested,
 39      decode(BLOCK,
 40          0, 'Not Blocking',
 41          1, 'Blocking',
 42          2, 'Global', block) status,
 43      OBJECT_NAME
 44  from    v$locked_object lo,
 45      dba_objects do,
 46      v$lock l
 47  where     lo.OBJECT_ID = do.OBJECT_ID
 48  AND     l.SID = lo.SESSION_ID
 49  /

 ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3204 DML                        Row-X (SX)                               None                                     Blocking             TEST
 3204 Transaction                Exclusive                                None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DML                        Share                                    None                                     Not Blocking         TEST
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3900 DML                        None                                     Row-X (SX)                               Not Blocking         TEST

From the above output we can see that first session is 3204 which is running insert.
Session 2 is 3075 which is running index rebuild and is waiting for “DML share” lock
session 3 is 3900 running update DML and waiting for “DML Row-X” lock

If we see which session is blocking what we see below rows


Logn Ora    SQL/Prev                     OS                                                   Call
><    Sid-Ser-S Time User   Hash       Module            User   Svr-Pgm    Machine      HR Resource           Elap Ctim Locked Object
--------------- ---- ------ ---------- ----------------- ------ ---------- ------------ -- ------------------ ---- ---- --------------------
>  3204,23884-I 0953 ADVAIT 0          SQL*Plus          advait 7924-orac  db-fc-admin- 3  TM:1664558-0       448s 448s 1664558
 < 3075,20427-A 0959 ADVAIT 3645454058 SQL*Plus          advait 15432-orac db-fc-admin-  4                    433s 432s 1664558
 < 3900,30565-A 0959 ADVAIT 4227999514 SQL*Plus          advait 16004-orac db-fc-admin-  3                    408s 408s 1664558

Above output is generated by my custom scripts.

It says that 3204 is parent session and 3204 and 3900 is waiting on 3204.

In case of 10g if we commit session 1 (sid 3204 running insert), it will allow online index rebuild to continue and get the lock.
Session 3 will still continue to wait until session 2 running online index rebuild releases the lock.

After some time session 2 will start with index rebuild, it will take lock for very short period of time and releases the lock.
Session 3 (update DML) will aquire the lock after session 2 (index rebuild) releases the lock.

Session 3 completed while session 2 (index rebuild) does table scan.

SQL>update test set OWNER = 'DEO2' where OWNER = 'DEO'
 2  ;
1 rows updated.
SQL>

Now after session 3 DML completes, we didnt commit session 3. We are waiting for session 2 to complete now.

We can check v$session_longops to see current operation for index rebuild


SQL>select sid, OPNAME, SOFAR, TOTALWORK, TIME_REMAINING, ELAPSED_SECONDS from v$session_longops where sid = 3075;

 SID OPNAME                                                                SOFAR  TOTALWORK TIME_REMAINING ELAPSED_SECONDS
------ ---------------------------------------------------------------- ---------- ---------- -------------- ---------------
 3075 Sort Output                                                           25011      25011              0              15
 3075 Table Scan                                                            56036      56036              0              61

2 rows selected.

So we can see that v$session_longops that table scan for index rebuild completed. But still the session 2 for index rebuild is hanging.

If we run above query to see the locks it gives below output


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         SYS_JOURNAL_1664589
 3075 Temp Segment               Exclusive                                None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DML                        Share                                    None                                     Not Blocking         TEST
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3075 Temp Segment               Exclusive                                None                                     Not Blocking         TEST
 3075 Transaction                Exclusive                                None                                     Not Blocking         TEST
 3900 DML                        Row-X (SX)                               None                                     Blocking             TEST

13 rows selected.

&nbsp;

So sid 3900 is the blocking session. This is session 3 which has run update query but havent commited.
So online index rebuild is waiting to acquire lock second time at the end. This is the time when it has to do the merging.

So after we commit session 3, session 2 doing online index rebuild will acquire the lock and will complete.

11g behaviour:

With 11g, significant changes were introduced to address all these problems.

Oracle will still wait for long running transactions to complete before starting the OIB process, but it will not block the upcoming DML’s. Oracle will not use exclusive table lock (mode 6) for OIB, which will eliminate any DML hang situations.

As soon as OIB process is initiated, Oracle will create IOT journal table and use internal trigger on the underlying table to keep track of the changes.

Once the journal table is created and the long running transactions are completed, Oracle will create the index by reading the table blocks in “CURRENT” mode, which means any committed changes in a block happened before the current SCN are read instead of the old way of accessing the blocks as of journal table creation time. This will virtually eliminate chance of running into rollback segment too small errors.

Oracle will recreate the internal trigger to directly modify the target index structure once the index build is completed (but the merge job is still pending). This means all user sessions will now directly update the target index instead of updating the journal table. Any change coming through would first be checked in the journal table before consolidating the change for the target index (this is best explanied with the example below).

Record “A” was inserted with rowid “RID” before the merge phase and hence it is tracked in the journal table.
Record “A” was deleted during the merge phase. Now the user session will read the journal table by record “A” with rowid “RID” and apply that change to the target index before deleteing the same record. It will also mark that record as consumed (i.e. deleted) in the IOT journal table leaf block.

While the user sessions continue to update the target index with the ongoing changes, OIB session will do the merge of journal table changes into the final index, as there will not be any ongoing DML changes being tracked in the journal table, this process will complete faster than before (i.e. pre 11g).

Lets take an example. In case of 11g we have MYTAB table and MYTAB_IDX index


SQL>select sum(bytes)/1024/1024 from dba_segments where segment_name = 'MYTAB';

SUM(BYTES)/1024/1024
--------------------
 406.5

SQL>select sum(bytes)/1024/1024 from dba_segments where segment_name = 'MYTAB_IDX';

SUM(BYTES)/1024/1024
--------------------
 152.5

In this case also do the following activity

From session 1 run a DML (insert statement) – SID 2213
From session 2 run index rebuild online command – SID 2222
From session 3 run a DML (update statement) – SID 2177

The current status in v$lock shows following


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2213 AE                         Share                                    None                                     Not Blocking         MYTAB
 2213 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2213 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701

Now if you compare it with 10g we see significant differences.

1) We see lot of extra locks in 11g compared to 10g
2) and the most important differences is that in case of 10g if you see last 2 rows in first output of v$lock we see


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3900 DML                        None                                     Row-X (SX)                               Not Blocking         TEST

and same in 11g is


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2213 AE                         Share                                    None                                     Not Blocking         MYTAB
 2213 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2213 Transaction                Exclusive                                None                                     Blocking             MYTAB
 .
 .
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB

In case of 10g if you see 3075 is the one which is doing an index rebuild and is waiting on initial sid 3204. Also 3rd session (3900) running DML is waiting for “Row-X (SX)” lock.
In case of 11g if you see 2222 is the one which is doing an index rebuild and is waiting on initial sid 2213. Also 3rd session (2177) running DML already got “Row-X (SX)” lock and is not waiting for anything.

So in case of 11g if index rebuild is waiting for initial lock to acquire, it does not block incomming DMLs.

After I commit 1st session I see that none of the session is now blocking index rebuild operation and so it can acquire initial lock


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

My session 3 is already complete and I commited that session now. session 3 had no dependency on any session.

After index rebuild start (its doing table scan), I run one more DML from session 3, but I dont commit. In this case index rebuild will hang again at the end waiting for exclusive lock on table.
Unless session 3 gives the lock index rebuild cannot proceed.

Important change here between 10g and 11g is that if we start 4th session while index rebuild waits for 2nd time for lock and if I commit session 3, in case of 10g index rebuild will get precedence and it will acquire lock blocking session 4.
In case of 11g session 4 will get presedence and will acquire lock for DML, where as index rebuild will wait further until all DML sessions are complete and lock is available for it to acquire. So online index rebuild will prioritize all other sessions before him to acquire locks.

From v$session_longops we can see that tablescan operation completed


SQL>select sid, OPNAME, SOFAR, TOTALWORK, TIME_REMAINING, ELAPSED_SECONDS from v$session_longops where sid = 2222 and opname not like 'RMAN%';

 SID OPNAME                              SOFAR  TOTALWORK TIME_REMAINING ELAPSED_SECONDS
---------- ------------------------------ ---------- ---------- -------------- ---------------
 2222 Table Scan                          51736      51736              0              12
 2222 Sort Output                         21422      21422              0               7

Now online index rebuild session is waiting for session 3 (SID 2177), which I started which table scan operation was inprogress


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

Unless I commit that session index rebuild will wait. If I start another session now (session 4) and commit session 3, index rebuild will still wait for session 4 to complete.
Like this it can continue to wait until all transactions are done.

Example I started session 4 (SID 2223 ) and ran DML, commited in session 3

now index rebuild session (SID 2222) is waiting for session 4.


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2223 AE                         Share                                    None                                     Not Blocking         MYTAB
 2223 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2223 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

So main difference in case of online index rebuild procedure is online index rebuild process gives precedence to other DML sessions to acquire locks. The process has become less intrusive now.

Hope this helps !!

Oracle Database 11g new feature – Automatic Memory Management

Automatic Memory Management was a new feature introduced in 10g. With 10g release oracle has come up with anew parameter called sga_target which was used to automatically manage the memeory inside SGA.
The components which were managed by sga_target are db_cache_size, shared_pool_size, large_pool_size, java_pool_size and streams_pool_size

With 11g, Oracle went a step further to manage both SGA as well as PGA automatically. Oracle database 11g introduced 2 new parameters – memory_target and memory_max_target

The memory_target Parameter

The memory_target parameter is somewhat a combination of the sga_target parameter value and the pga_aggregate_target parameter, representing the total amount of memory that Oracle has to allocate between the various SGA and PGA structures. The memory_target parameter is dynamic and can be changed up to and including the value of memory_max_target, which we discuss next.

The memory_max_target Parameter

The memory_max_target parameter allows you to dynamically change the value of the parameter memory_target within the confines of memory_max_target. Thus you can adjust the total amount of memory available to the database as a whole at any time.

NOTE: The memory_target and memory_max_target parameters cannot be used when LOCK_SGA has been set. Also memory_target and memory_max_target cannot be used in conjunction with huge pages on Linux.

Following fig show the hierarchy of memory parameters and components that it tunes. This fig is taken from Robert Freeman book – Oracle database 11g: New features for Administrator.

Also below fig from same book show the effect of setting memroy_target, memroy_max_target and sga_target.

While setting these new parameters (memroy_target and max_memory_target), one has to be careful. A general advice would be to set sga_target and pga_aggregate_target to a minimum fixed value and set memrory_target. Oracle will automatically increase sga_target and pga_aggregate_target to the desired level.

If you are upgrading the existing 10g database to 11g and want to keep the current value of sga_target and pga_aggregate_target, than make sure you set the value of memroy_target >= (sga_target + pga_aggregate_target).

With new hirarchy in memeory management, Oracle has also introduced new memroy advisory. You can view V$MEMORY_TARGET_ADVICE view to get advice on the optimal value of memroy_target parameter. This view will show advisory data only after you set memory_target parameter.

Hope this helps !!

Oracle Certified RAC Expert

Hi All,

Just to inform all my viewers..

I have cleared Oracle Database 10g: RAC Administration exam (1Z0-048). This qualifies me as Oracle Certified RAC Expert.

About Exam:

This Exam (1Z0-048) is one of the Certified Expert Programs. Pre-requisite for this exam is to either have OCP 10g or you can attained any approved Oracle university course before giving the exam. If you are not a OCP 10g and you are attaining the course in OU, then you need to submit a course submission form.

How to prepare for this exam:

I don’t want to give any hard and fast rule on my own about exam preparation. But here is what I did for clearing this exam.

1. Read ILT (Instructor Led Training) materials of the course “Oracle Database 10g: RAC for Administrators Release 2” thoroughly (may be 2-3 times). I read it 2 times

2. If possible read the book “Oracle Database 10g: RAC Handbook” – by K. Gopalkrishnan

3. Oracle online document – 2 Day + Real Application Clusters Guide

Exam Information

You can find more information about this exam on Oracle site

Converting a RAC database to single instance database – Oracle RAC 10g

This post will brief you about steps to convert a RAC database to single instance.
I don’t think this is supported by oracle as I was not able to find any document or metalink note ID which will provide the steps. So please do not try the same in production environment unless you have a conformation from Oracle Support.
However I tried the following steps in test instance an it worked for me.
1) Stop database using srvctl
srvctl stop database -d prod
2) Remove the database entry from crs
bash-3.00$ srvctl remove instance -d prod -i prod1
Remove instance prod1 from the database prod? (y/[n]) y
bash-3.00$ srvctl remove instance -d prod -i prod2
Remove instance prod2 from the database prod? (y/[n]) y
bash-3.00$ srvctl remove database -d prod
Remove the database prod? (y/[n]) y
bash-3.00$
3) Start the database on first instance
SQL> startup
ORACLE instance started.
Total System Global Area  838860800 bytes
Fixed Size                  1222168 bytes
Variable Size             188746216 bytes
Database Buffers          645922816 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> alter system set cluster_database_instances=1 scope=spfile;
System altered.
SQL> alter database disable thread 2;
Database altered.
4) Delete the unwanted thread and redo logfiles
SQL> select thread#, group# from v$log;
THREAD#     GROUP#
---------- ----------
1          1
1          2
1          3
2          4
2          5
2          6
6 rows selected.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
5) Drop the unwanted undo tablespace
SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.
6) Create pfile from spfile
SQL> create pfile from spfile;

File created.

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
7) Remove all references of second instance from the pfile and start the instance using pfile. Later you can again create spfile from pfile.

Oracle EBS R12 is now certified with 11g Database

Today, Oracle has announced that Oracle E-Business Suite R12 is certified with Oracle database 11g R1, the project I was actively involved into.

This announcement for EBS Release 12 version 12.0.4 and up includes:

  • Oracle Database 11gR1 Version 11.1.0.6
  • Oracle Database 11gR1 Version 11.1.0.6  Real Application Clusters (RAC)

Prerequisites & Interoperability

For prerequisites and interoperability, refer to the relevant OracleMetalink Notes listed in the documentation section below.

Platforms certified

  • Linux x86
  • IBM AIX
  • Sun Solaris SPARC
  • HP-UX PA-RISC
  • HP-UX Itanium
  • Linux x86-64

Documentation

  • OracleMetalink Note 735276.1 – Interoperability Notes E-Business Suite R12 with Oracle Database 11gR1 (11.1.0)
  • OracleMetalink Note 466649.1 – Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12

OPATCH Utility (Oracle RDBMS Patching)

Introduction:

This post has been written on request of one of regular visitor of my blog (Altaaf). This post is all about Oracle Patching Utility (OPATCH) and various options that can be used along with Opatch.

We will begin the discussion by knowing what exactly is Opatch?

– OPatch is a java based utility that allow the application and rolling back of interim patches to an Oracle product. The program has sub-commands that may take arguments. The program requires Java(JVM) to be present on the current system.OPatch expects users to have commands such as fuser, jar, ar and make on Unix platforms and jar on Windows platforms to be available in their PATH.

Opatch Details

How to check the Opatch version?

-bash-3.00$ ./opatch version
Invoking OPatch 10.2.0.3.0

OPatch Version: 10.2.0.3.0

OPatch succeeded.

How to know which version is the correct version of Opatch for your RDBMS home?

You can verify whether the Opatch version for your RDBMS release is correct or not using metalink note ID 357221.1.

This note ID gives the copatibilities between OPatch version and RDBMS version.

How to get the latest version of OPatch?

You can download the latest version of OPatch from metalink using following URL.
http://updates.oracle.com/download/6880880.html

In the release dropdown you can select the OPatch release that you want to download based on your RDBMS version. Please read the README.txt before applying the patch.

What is Oracle Database Inventory and where it is located?

Oracle Inventory is the location or the place where all the information about an Oracle Home is stored and maintained. When ever we install an RDBMS Oracle Home, a new inventory gets created. Applying any new patch to Oracle Home will update the invnrtory for that Oracle Home and record the details for the patch applied. This inventory is in the form of XML files.

The location of inventory is defined in a file called oraInst.loc. The path for this file is provided while installing Oracle Home. If we dont supply any path, the is file will be present at central location /etc/oraInst.loc. Also we can have a central inventory if its not used by any other oracle installation. If the central inventory is used by previous Oracle installation we can create local inventory. The content of oraInst.loc file is as shown below.

-bash-3.00$ cd $ORACLE_HOME
-bash-3.00$ cat oraInst.loc
inventory_loc=/slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInventory
inst_group=ems2029
-bash-3.00$

Here “inventory_loc” gives the location of Inventory directory where as inst_group gives the name of the group which is the owner of this inventory.

How to create Local Inventory?

You can create local inventory at any location while installing Oracle Home. You need to use -invPtrLoc variable along with runInstaller command and give the location of oraInst.loc. Otherwise the default path assumed for oraInst.loc is /etc/oraInst.loc.

./runInstaller -invPtrLoc /slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInst.loc

After the installation starts it will ask for location of oraInventory directory and group which should own it. It will make entry of these into oraInst.loc file.

Having know the above information about opatch, now we will move to details about using opatch and various options available.

Opatch help

You can get all the options using opatch -help

-bash-3.00$ ./opatch -help
Invoking OPatch 11.1.0.6.0

Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

Usage: opatch [ -help ] [ -r[eport] ] [ command ]

command := apply
lsinventory
prereq
query
rollback
util
version

<global_arguments> := -help       Displays the help message for the command.
-report     Print the actions without executing.

example:
‘opatch -help’
‘opatch apply -help’
‘opatch lsinventory -help’
‘opatch prereq -help’
‘opatch rollback -help’
‘opatch util -help’

OPatch succeeded.

You can get specific help for any command of opatch using opatch <command> -help. Example opatch apply -help.

Applying single patch using opatch

1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
2.Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory

tar cvf – $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

6. Unzip the patch in $ORACLE_HOME/patches

If you are applying a single intrim patch to oracle home, then change the location to the directory of that patch and then invoke following command.

Example if I need to apply patch 6972343 to RDBMS Oracle Home

-bash-3.00$ cd 6972343
-bash-3.00$ pwd
/slot/ems2029/oracle/db/tech_st/11.1.0/patches/6972343
-bash-3.00$ ls
etc  files  README.txt
$ORACLE_HOME/OPatch/opatch apply

If you have created local inventory then you need to give the correct path for local inventory stored in oraInst.loc. If you have updated /etc/oraInst.loc with correct location of your inventory, then above command will work, If not you can give the correct location by giving local oraInst.loc file.

$ORACLE_HOME/OPatch/opatch apply -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

Remember that when we are applying patch, patch number is NOT provided as an input to opatch command. So we have to to be in the directory of patch since opatch will pick the patch files from current directory.

Rolling back a patch

In case if a patch did not fix the issue or if you has applied wrong patch to oracle home, you can always rollback the patch using following comamnd.

opatch rollback -id <Patch Number>

Applying bundle patches

Some times if you have to apply bundle patch having many patches, example lets say you want to a bundle patch 6778860 (    11.1.0.6 BUNDLED PATCH FOR EBS 11I)) containing many other patches to RDBMS home, OPatch provides a facility to apply many patches to RDBMS home using a single comamnd. Following command should be used for applying bundle patches.

Download the patch and extract the same. You will see many directories (one for each patch) inside the main patch directory. Execute following command to apply all patches.

$ORACLE_HOME/OPatch/opatch util NApply -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

NApply -> Will apply all patches to RDBMS home.

You can also use -phBaseDir to point to the directory where bundle patch is installed. So you can run opatch command from any location

$ORACLE_HOME/OPatch/opatch util NApply -phBaseDir /slot/ems2029/oracle/db/tech_st/11.1.0/patches/6778860 -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

You can also apply specific patches using NApply

opatch util napply <patch_location> -id 1,2,3 -skip_subset -skip_duplicate

This will apply patches 1, 2, and 3 which are under < the patch_location> directory. OPatch will skip duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the ORACLE_HOME)

You can see all the options for NApply using following help command.

$ORACLE_HOME/OPatch/opatch util NApply -help

Query the inventory for patches applied

We can query the inventory to check various components installed for database as well as to check various patches applied to database. Use following command to get a minimum information from inventory about patches applied and components installed.

$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

Patch  5763576      : applied on Wed May 28 03:20:53 PDT 2008
Created on 6 Feb 2008, 02:26:04 hrs PST8PDT
Bugs fixed:
5763576

If you are using central inventory then -invPtrLoc variable is not required.

You can also get the detailed view of inventory using following command. In case of detail information it gives which are the files that this patch have touched.

$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

Patch  6318357      : applied on Wed May 28 03:33:27 PDT 2008
Created on 4 Dec 2007, 22:02:16 hrs PST8PDT
Bugs fixed:
6318357
Files Touched:
udjvmrm.sql –> ORACLE_HOME/javavm/install/udjvmrm.sql
Patch Location in Inventory:
/slot/ems2029/oracle/db/tech_st/11.1.0/inventory/oneoffs/6318357
Patch Location in Storage area:
/slot/ems2029/oracle/db/tech_st/11.1.0/.patch_storage/6318357_Dec_4_2007_22_02_16

Which options are installed in Oracle Home?

You can check the options installed in RDBMS home using above command. The options installed will be listed at the start of output.
Example: $ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

The complete output of this command can be seen at this location.

Opatch Log files

Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch

Advanced Option for OPatch

Here are some of the advanced options of OPatch utility.

UpdateRemoteNodes

It is used to propagate/remove files/directories to/from remote nodes using files under ORACLE_HOME/.patch_storage/<ID>/rac/*.

The directories listed in copy_dirs.txt will be copied to remote nodes.
The files listed in copy_files.txt wil be copied to remote nodes.
The directories listed in remove_dirs.txt will be deleted from remote nodes.
The files listed in remove_files.txt will be deleted from remote nodes.
This is used where RAC setup is present.

Cleanup

It is used to clean up ‘restore.sh, make.txt’ files and ‘rac, scratch, backup’ directories in the ORACLE_HOME/.patch_storage directory. If -ps option is used, then it cleans the above specified areas only for that patch, else for all patches under ORACLE_HOME/.patch_storage. You will be still able to rollback patches after this cleanup.
Example: opatch util cleanup -ps 6121183_Ju _21_2007_04_19_42

CopyListedFiles

It is used to copy all files listed in ORACLE_HOME/.patch_storage/<ID>/rac/copy_files.txt to remote nodes. If -fp option is used, then one can specify the path of the file containing the list of files to be copied. The files mentioned in this file will be copied to the remote nodes.
Example: opatch util copylistedfiles -fp a -remote_nodes ceintcb-a5
This is used where RAC setup is present.

CopyListedFilesTest

It is used to copy a single file to remote nodes. The usage remains the same as CopyListedFiles.
Example: opatch util copylistedfilestest -fp /home/oracle/a -remote_nodes ceintcb-a5
This is used where RAC setup is present.

CopyListedDirs

It is used to recursively copy all directories listed in ORACLE_HOME/.patch_storage/<ID>/rac/copy_dirs.txt to remote nodes. If -dp option is used, then one can specify the path of the file containing the list of directories to be copied. The directories mentioned in this file will be copied to the remote nodes.
This is used where RAC setup is present.

CopyListedDirsTest

It is used to copy a single file to remote nodes. The usage remains the same as CopyListedDirs.
This is used where RAC setup is present.

RemoveListedFiles

It is used to remove files listed in ORACLE_HOME/.patch_storage/<ID>/rac/remove_files.txt on remote nodes. If -fr option is used, then one can specify the path of the file containing the list of files to be removed. The files mentioned in this file will be removed from the remote nodes.
This is used where RAC setup is present.

RemoveListedFilesTest

It is used to remove a single file from remote nodes. The usage remains the same as RemoveListedFiles.
This is used where RAC setup is present.

RemoveListedDirs

It is used to recursively remove directories listed in ORACLE_HOME/.patch_storage/<ID>/rac/remove_dirs.txt from remote nodes. If -dr option is used, then one can specify the path of the file containing the list of directories to be removed. The directories mentioned in this file will be removed from the remote nodes.
This is used where RAC setup is present.

RemoveListedDirsTest

It is used to remove a single directory from remote nodes. The usage remains the same as RemoveListedDirs.
This is used where RAC setup is present.

RunLocalMake

It is used to invoke re-link on the local node. The make commands are stored in ORACLE_HOME/.patch_storage/<ID>/make.txt. You need to use the -ps option to specify the Patch ID with timestamp. A directory by this name will be present under ORACLE_HOME/.patch_storage. The make.txt file present under ORACLE_HOME/.patch_storage/<Patch ID with timestamp>/ will be used to perform the local make operation. This command cannot be run if you have already run Cleanup as it would have removed these make.txt files.

Example: opatch util runlocalmake -ps 6121250_ un_21_2007_04_16_11

RunRemoteMake

It is used to invoke re-link on remote nodes. The make commands are stored in
ORACLE_HOME/.patch_storage/<ID>/rac/makes_cmd.txt. The usage remains the same as RunLocalMake.
This is used where RAC setup is present.

RunAnyCommand

It is used to run any command on remote nodes. The command should be specified using the -cmd option.
Example: opatch util runanycommand -remote_nodes ceintcb-a5 -cmd ls
This is used where RAC setup is present.

LoadXML

It is used to check the validity of an XML file. The -xmlInput option can be used to specify the path of the xml file.

@ Support can use this utility to verify the integrity of XML files contained in the local and central inventory.
Example: opatch util loadxml -xmlInput $ORACLE_HOME/inventory/ContentsXML/comps.xml

Verify

It is used to run the patch verification process to ensure that the patch was applied to the ORACLE_HOME. It uses the defined ORACLE_HOME and the given patch location via -ph, to run the check.

@ Support can use this utility to re-run the OPatch verification stage, if required for patch diagnosis. The patch staging area has to be present or created.

Example: opatch util verify -ph ~/6646853/6121183

Troubleshooting

Some times the inventory get corrupted because of some issues. In that case you need to repair the inventory. Following are the two methods which I know can be used to repair the inventory.

1) Development has released a utility (checkinv) which can be use to repair the  inventory if it becomes corrupted for some reason.

Please note that this version of checkinv works only for 9.2 and 10.1 releases of oracle database. This doesn’t work for 10.2 releases of oracle database.

You can download the checkinv utility from Patch 4121946.

To cleanup the components, you should determine the unwanted or dangling components, then specify those using the option “-remcomp”, to cleanup the component list.

The utility detects and asks user’s permission to repair a bad inventory.  In case you want to repair the inventory, the utility will first back up the files before any modification.

However, it is better that you backup the inventory before running the tool.

For more details, check metalink note ID 298906.1

2) Creating a new inventory using Oracle Universal Installer (OUI).

Check my older post https://advait.wordpress.com/2008/03/21/creating-oracle-home-inventory-oracle-database-10g/ for the same.

Hope this helps !!

References:

Metalink note ID : 298906.1
Metalink note ID : 554417.1
Metalink note ID : 374092.1
Metalink note ID : 357221.1