Redefining tables online – Oracle 11g

Introduction:

One of the many challenges that we face in production environment is make changes to big tables.
If you consider a case of any OLTP systems, its easy to have tables whose size is beyond 10G.

This again depends on the nature of the database and kind of transactions happening on the table.

So lets consider a case where you have a big table which is also a very hot table having very high number of transactions / sec and something that continues 24X7.
Its difficult to take downtime on such tables and making DDL changes to such tables could be a nightmare.

Oracle has a great feature introudcued since Oracle 9i, but many DBAs doesnt seem to be aware of this feature – Online table redefiniation.

Online table redefinition allows you to make DDL changes to the table definition and requires very little downtime (less than a minute).
Techinically its not the same table that gets modified, but its another copy of the same table which has the required modification made.

You might question if we are making a new copy of the table we can as well use CTAS (Create Table as Select) and make the required changes.
But its not just about creating new object and copying the data. Online redefinition does lot more than that.

I will briefly explain you the features of online redefinition, followed by process and then we will straight way get to the examples which will help you to understand better.

Features – What it can do:

Online table redefinition enables you to:

  • Modify the storage parameters of a table or cluster
  • Move a table or cluster to a different tablespace
  • Add, modify, or drop one or more columns in a table or cluster
  • Add or drop partitioning support (non-clustered tables only)
  • Change partition structure
  • Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
  • Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
  • Re-create a table or cluster to reduce fragmentation

Process – How its done:

To briefly explain the process, it involves following steps

1) Check if the table can be refined by running DBMS_REDEFINITION.CAN_REDEF_TABLE procedure.

We have following restrictions on redefining the table

  • One cannot redefine Materialized Views (MViews) and tables with MViews or MView Logs defined on them.
  • One cannot redefine Temporary and Clustered Tables
  • One cannot redefine tables with BFILE, LONG or LONG RAW columns
  • One cannot redefine tables belonging to SYS or SYSTEM
  • One cannot redefine Object tables
  • Table redefinition cannot be done in NOLOGGING mode (watch out for heavy archiving)

2) Create a new table with all of the desired logical and physical attributes.

If you want to change non-partition table to partition, you can create a new partition table. Structure of the table should be exactly the way you want to convert to.

3) Start redefinition process using DBMS_REDEFINITION.START_REDEF_TABLE procedure.

Be careful before running this command. If you must know, this command will start populating new table from the data in old table.
So if your old table is very big, then you need to have same amount of space available in the tablespace where new table is created.
Also, this command might take very long time if the size is big, so make sure you don’t have any disconnection in between.

If needed you can enable parallel before starting redefinition using following commands

alter session force parallel dml parallel degree-of-parallelism;
alter session force parallel query parallel degree-of-parallelism;

4) Sync new table on regular basis till cut off time

You should use DBMS_REDEFINITION.SYNC_INTERIM_TABLE and keep new table in sync with changes that happens on current production table.
This will reduce the cut off time. Cut off time if when you are going to point everything to new table and services will start writing to new table.
The more you keep new table and current production table in sync, lesser will be cut off time and downtime.

5) Copy dependent objects (such as triggers, indexes, grants, and constraints)

Copy dependent objects (such as triggers, indexes, materialized view logs, grants, and constraints) and statistics from production table to new table.
You should make sure that all dependents are copied.
You can do this manually by creating each dependent object or you can do it automatically using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

6) Complete redefinition process by using DBMS_REDEFINITION.FINISH_REDEF_TABLE procedure

This will complete the redefinition process. This needs exclusive lock on production table which you want to redefine.
So you need to arrange for short downtime. However, FINISH_REDEF_TABLE will wait for all pending DML to commit before completing the redefinition.

Example:

Lets take an example:

We have a table T as shown below.
We have a primary key on OBJECT_ID column.
We have a public synonym for table T.

SQL>desc T
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                            VARCHAR2(30)
 OBJECT_NAME                        VARCHAR2(128)
 SUBOBJECT_NAME                     VARCHAR2(30)
 OBJECT_ID                        NUMBER
 DATA_OBJECT_ID                     NUMBER
 OBJECT_TYPE                        VARCHAR2(19)
 CREATED                        DATE
 LAST_DDL_TIME                        DATE
 TIMESTAMP                        VARCHAR2(19)
 STATUS                         VARCHAR2(7)
 TEMPORARY                        VARCHAR2(1)
 GENERATED                        VARCHAR2(1)
 SECONDARY                        VARCHAR2(1)
 NAMESPACE                        NUMBER
 EDITION_NAME                        VARCHAR2(30)

SQL>

Currently this is not a partitioned table

SQL>select table_name, partitioned from user_tables where table_name = 'T';

TABLE_NAME               PAR
------------------------------ ---
T                   NO

Lets try to convert this into partition table.

You can check the meaning of every parameter supplied to below procedures at – http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_redefi.htm
Step 1) Check if the table can be refined by running DBMS_REDEFINITION.CAN_REDEF_TABLE procedure.

SQL>set serveroutput on
SQL>EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user,'T',DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

SQL>

If the table is not a candidate for online redefinition, an error message is raised.

Step 2) Create a new intrim table with all of the desired logical and physical attributes.

For table T lets try to partition by CREATED which is a date column. I am planning to partition by year so we can get 10 partitions

SQL>select to_char(CREATED,'YYYY') from T group by to_char(CREATED,'YYYY');

TO_C
----
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012

10 rows selected.

CREATE TABLE "T_INTRIM"
   (    "OWNER" VARCHAR2(30),
    "OBJECT_NAME" VARCHAR2(128),
    "SUBOBJECT_NAME" VARCHAR2(30),
    "OBJECT_ID" NUMBER,
    "DATA_OBJECT_ID" NUMBER,
    "OBJECT_TYPE" VARCHAR2(19),
    "CREATED" DATE,
    "LAST_DDL_TIME" DATE,
    "TIMESTAMP" VARCHAR2(19),
    "STATUS" VARCHAR2(7),
    "TEMPORARY" VARCHAR2(1),
    "GENERATED" VARCHAR2(1),
    "SECONDARY" VARCHAR2(1),
    "NAMESPACE" NUMBER,
    "EDITION_NAME" VARCHAR2(30),
     CONSTRAINT "T_PK_INTRIM_01" PRIMARY KEY ("OBJECT_ID")
    )
PARTITION BY RANGE(CREATED)
(
PARTITION T_2003 VALUES LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY')),
PARTITION T_2004 VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY')),
PARTITION T_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')),
PARTITION T_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')),
PARTITION T_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION T_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')),
PARTITION T_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')),
PARTITION T_2010 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY')),
PARTITION T_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')),
PARTITION T_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY'))
);

3) Start redefinition process using DBMS_REDEFINITION.START_REDEF_TABLE procedure.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
   uname        => user,
   orig_table   => 'T',
   int_table    => 'T_INTRIM',
   col_mapping  => NULL,
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID
   );
END;
/

After this both table should have near about same amount of record

SQL>select count(1) from T_INTRIM;

  COUNT(1)
----------
     61536

SQL>select count(1) from T;

  COUNT(1)
----------
     61536

SQL>

If you have continuous inserts going on your original table than you might have little more records in original table than intrim table.

4) Sync new table on regular basis till cut off time

BEGIN 
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
   uname      => user, 
   orig_table => 'T', 
   int_table  => 'T_INTRIM'
   );
END;
/

The way this sync works is, online redefinition will automatically create a MLOG table on original table.
In any of the above step we didn’t create any MLOG table on table T.

But if you check now, you will see MLOG table created automatically.

SQL>select log_table from user_snapshot_logs where master = 'T';

LOG_TABLE
------------------------------
MLOG$_T

This is required for syncing changed made to table T.

5) Copy dependent objects (such as triggers, indexes, grants, and constraints)

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname               => user,
    orig_table          => 'T',
    int_table           => 'T_INTRIM',
    copy_indexes        => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers       => TRUE,
    copy_constraints    => TRUE,
    copy_privileges     => TRUE,
    ignore_errors       => TRUE,
    num_errors          => num_errors);
END;
/

PL/SQL procedure successfully completed.

Before we finish online redefinition you can check if table is partition and data is distributed to all partitions

SQL>select table_name, partitioned from user_tables where table_name in ('T','T_INTRIM');

TABLE_NAME               PAR
------------------------------ ---
T                   NO
T_INTRIM               YES

SQL>select to_char(created,'YYYY'), count(1) from T_INTRIM group by to_char(created,'YYYY');

TO_C   COUNT(1)
---- ----------
2003       7902
2005       1820
2009       2742
2010       6765
2008       2612
2007       1016
2011      10474
2004        756
2012      23474
2006       3975

10 rows selected.

Once we finish redefinition table T will become partition table and T_INTRIM will become non-partition table.
For this it needs exclusive lock.

6) Complete redefinition process by using DBMS_REDEFINITION.FINISH_REDEF_TABLE procedure

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T', 'T_INTRIM');
END;
/

PL/SQL procedure successfully completed.

SQL>select table_name, partitioned from user_tables where table_name in ('T','T_INTRIM');

TABLE_NAME               PAR
------------------------------ ---
T                   YES
T_INTRIM               NO

SQL>

so now table T is partitioned table.

References:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_redefi.htm

Hope this helps !!

Advertisements

Materialized view Concepts – Discussion Series 3

We have seen Materialized view Concepts – Discussion Series 1 and Materialized view Concepts – Discussion Series 2.
This is the third article about Materialized views.

In this article we are going to discuss “How fast refresh works ?”

 

How fast refresh works ?

As we know in case of fast refresh only the changes that happened on master site (or master table) will be applied to MView on target site.
So the changes that happens on master table will be stored in MLOG table created on top of master table.
This is more efficient way than doing complete refresh.

As we know MLOG can be based on primary key or ROWID. ROWID MLOG is uncommon and is not used because if master table gets moved then ROWID will get changed and changes saved in MLOG will be invalid.
So to identify the changes on master table usually primary key based MLOG is created on top of master table.

Only 1 MLOG can be created on master table even if we have multiple sites refreshing from 1 master table.

Before we check on how fast refresh works, lets understand some of the components of fast refresh

MLOG$ table and its important columns

SNAPTIME$$             – This is a date columns and holds the date of 1st Mview refresh time. Example if we have 3 Mviews registered on 1 master table, than this column will hold oldest refresh date among the 3 Mviews
DMLTYPE$$              – This column tells you the type of DML ( U – Update, D – Delete, I – Insert )
OLD_NEW$$             – This column allow the fast-refresh mechanism to distinguish between rows inserted at the mview site and rows with modified primary key values.
CHANGE_VECTOR$$     – Used for subquery and LOB MViews

Apart from above standard columns in MLOG$ table, there will also be primary key columns. These columns are same as primary key columns of master table

AFTER ROW trigger on the master table

From Oracle8 and onward this trigger is kernelized and is no longer visible in the data dictionary. The same trigger can support both ROWID and primary key MViews and will populate the MLOG$_<table_name> with the proper values. It also populates the SNAPTIME$$ column (indicating the latest refresh time so far a particular row) and the DMLTYPE$$ column. The snaptime$$ column is populated based on the value of the snaptime column in snap$ table at the MView site and is not updated until the log is first used by a MView refresh.

Registration of Mview on master site

Oracle automatically tries to register a materialized view at its master site or master materialized view site when you create the materialized view, and unregisters when you drop it. The same applies to materialized view groups. Registration of fast refreshable materialized view logs in the master database (SYS.SLOG$) is needed to perform fast refreshes. This information is also used to maintain the materialized view log of the master table.

Fast refresh operation

Fast refresh operation consists of 3 phases

  1. Setup Phase
  2. Refresh Phase
  3. Wrap-up Phase

1) Setup Phase:

Setup has to check if the Mview being refreshed is ROWID based Mview or Primary key based Mview.
After that it has to verify if fast-refresh can be performed for this MView. An MView can perform a fast refresh only if it can use the MView log. This can be determined by checking entry in SYS.SLOG$ table on master site.


DB1>select MASTER, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPID SNAPTIME
------------------------------ ---------- ----------------
T                    15119 2012-10-14:03:01
T                    15120 2012-10-17:09:47

 

If an entry is present in this table for that SNAPID, then it can be fast refreshed.
Its possible that you have created a complete refreshable MView on some master table which didn’t had MLOG. Later point of time you created MLOG and may be wondering why fast refresh not happening.
You can check the entry in this table and verity. Also in such cases you need to drop and recreate Mview on prebuilt table and make it fast refreshable.

Once its confirmed that fast refresh is possible for MView in question, snaptime$$ column is updated in the MLOG$ table of the altered rows to its own refresh date and time for the first MView that refreshes. This value does not change until the rows are eventually purged from the log.

2) Refresh Operation:

After setup phase, a second check is made to see if fast refresh can be done. This time its the date comparison that is done to ensure their is no mismatch of data.
For fast refresh to work, timestamp of oldest/oldest_pk column in mlog$ must be older than the time of last refresh.

How fast refresh work

Lets check out in detail how the date changes happen.
We will keep an eye on 3 tables

1) SYS.SLOG$ on master
2) SYS.MLOG$ on master
3) MLOG$_<master_table> on master (This is out MLOG$ table)

I will explain you with live example.

Master table name – T (on DB1 database)
MLOG table name – MLOG$_T (no DB1 database)

We have 2 snapshot sites and each has fast refreshable MView created.

MView Name – T_REP (on DB2 database)
MView Name – T_REP (on DB3 database)

On Master site:


DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPSHOT         SNAPID SNAPTIME
------------------------------ ---------------- ---------- ----------------
T                             15119 2012-10-24:01:16
T                             15120 2012-10-24:01:17

Above output represents 2 snapshot site registered on Master table/site

DB1>select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T';

MASTER                   OLDEST_PK    YOUNGEST     LOG                MTIME         LAST_PURGE_DATE
------------------------------ ---------------- ---------------- ------------------------------ ---------------- ----------------
T                   2012-10-24:01:16 2012-10-24:01:17 MLOG$_T            2012-10-13:22:25 2012-10-24:01:17

 

In SYS.MLOG$ table you will have 1 record for each MLOG$ that you create. Since a master table can have only 1 MLOG you will have 1 record for each master table.

There are no records in MLOG$_T table

DB1>select count(1) from MLOG$_T;

  COUNT(1)
----------
     0

Lets make changes in master table T and see what happens in above 3 tables

DB1>update T set DATA_OBJECT_ID = 0 where OBJECT_ID = 2300;

1 row updated.

DB1>commit;

Commit complete.

DB1>

With above modification, no change seen in SYS.SLOG$ table and SYS.MLOG$ table
DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPSHOT         SNAPID SNAPTIME
------------------------------ ---------------- ---------- ----------------
T                             15119 2012-10-24:01:16
T                             15120 2012-10-24:01:17

DB1>
DB1>select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T';

MASTER                   OLDEST_PK    YOUNGEST     LOG                MTIME         LAST_PURGE_DATE
------------------------------ ---------------- ---------------- ------------------------------ ---------------- ----------------
T                   2012-10-24:01:16 2012-10-24:01:17 MLOG$_T            2012-10-13:22:25 2012-10-24:01:17

However we see new entry in MLOG$_T table corresponding to the row changed

DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

 OBJECT_ID SNAPTIME$$        D O
---------- ---------------- - -
      2300 4000-01-01:00:00 U U

OBJECT_ID is the primary key column and value in MLOG$_T table represent the row we changed

Observe the value of SNAPTIME$$ – 4000-01-01. This is a date in future which will not be reached in lifetimes. This date tells us that none of the Mview sites has done a fast refresh of this change.

So lets rewind our statement about second check done during “Refresh Phase”

For fast refresh to work, timestamp of oldest/oldest_pk column in mlog$ must be older than the time of last refresh.

It means oldest_pk <= last refresh time on Mview sites

We can check last refresh time of Mviews by checking DBA_SHAPSHOTS table or DBA_SNAPSHOT_REFRESH_TIMES table on Mview site.  But same thing can be checked on master site using SYS.SLOG$ table

We know oldest_pk timestamp – 2012-10-24:01:16
Min(last_refresh time) – 2012-10-24:01:16

So since oldest_pk <= last refresh time on Mview sites, refresh can proceed

If one of the site does a fast refresh we can see that MLOG$_T.SNAPTIME$$ timestamp gets updated to refresh time

DB2>exec dbms_snapshot.refresh('T_REP','F');

PL/SQL procedure successfully completed.

DB2>

DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

 OBJECT_ID SNAPTIME$$        D O
---------- ---------------- - -
      2300 2012-10-24:01:52 U U

DB1>

We are yet to refresh second site which is registered for this master table and so the record from MLOG$_T is not deleted.

Also, 1 note with respect to SYS.MLOG$ table


DB1>select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T';

MASTER                   OLDEST_PK    YOUNGEST     LOG                MTIME         LAST_PURGE_DATE
------------------------------ ---------------- ---------------- ------------------------------ ---------------- ----------------
T                   2012-10-24:01:17 2012-10-24:01:52 MLOG$_T            2012-10-13:22:25 2012-10-24:01:52

 

YOUNGEST column represent the latest refresh time. If you have 5 sites, the one you refresh latest will have that timestamp updated in YOUNGEST column
OLDEST_PK column represent the oldest refresh time. If you have 5 sites, the one you refresh first will have that timestamp updated in OLDEST_PK column
OLDEST column is used in ROWID based MLOG

3) Wrap-up Phase

In this phase it checks if all Mviews are refreshed and if the changes in MLOG$_T table has gone to all site and if the entries in MLOG$_T table can be purged.

Again Oracle checks dates in above 3 tables to determine which records in MLOG$ table can be purged.

How MLOG$ purge works

Oracle automatically tracks which rows in a MView log have been used during the refreshes of MViews, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple mviews can use the same MView log, rows already used to refresh one MView may still be needed to refresh another MView. Oracle does not delete rows from the log until all MViews have used them. In the wrap-up phase of the refresh process, the master MView log is purged. It deletes all unnecessary rows from the MView log. Rows in the MView log are unnecessary if their refresh timestamps MLOG$_<table_name>.SNAPTIME$$ are older or equal than the oldest entry in SLOG$.SNAPTIME for this log.

It means rows in MLOG$_<master_table> will be deleted whose MLOG$_<master_table>.SNAPTIME$$ <= min(SYS.SLOG$.SNAPTIME)

Lets take an example

We have a live example going where we have updated 1 record in master table and we have refreshed 1 site (out of 2 sites registered)

Here are the outputs of 2 required tables


DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

OBJECT_ID SNAPTIME$$        D O
---------- ---------------- - -
2300 2012-10-24:01:52 U U

DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPSHOT         SNAPID SNAPTIME
------------------------------ ---------------- ---------- ----------------
T                             15119 2012-10-24:01:52
T                             15120 2012-10-24:01:17

As you can see SNAPTIME$$ timestamp has the time when this record was first refresh by any site. If we have 3 sites and 1st site refreshes the records this timestamp will get updated.
But if 2nd site refreshes the record, this timestamp will NOT change. However refresh of second site will change the SNAPTIME column in SYS.SLOG$ table. SNAPTIME column in SYS.SLOG$ table always has the latest refresh time for corresponding site.

So we can see that MLOG$_T.SNAPTIME$$ – 2012-10-24:01:52 (this is the time when 1st site got refreshed)
min(SYS.SLOG$.SNAPTIME) – 2012-10-24:01:17

Since MLOG$_T.SNAPTIME$$ > min(SYS.SLOG$.SNAPTIME), row will not be deleted.

If I refresh 2nd site than SYS.SLOG$.SNAPTIME corresponding to that site will get updated with refresh time and in that case MLOG$_T.SNAPTIME$$ <= MIN(SYS.SLOG$.SNAPTIME). When this condition happens it will delete the record from MLOG$ table.

Logically this means that all the sites registered for this master table is been refreshed.

Lets try to refresh 2nd site. This should purge the record from MLOG. We will also see how the dates are updated in 3 tables on master site

After refreshing second site

No rows in MLOG$_T table

DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

no rows selected

SNAPTIME for second site got updated in SYS.SLOG$ table

DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPSHOT         SNAPID SNAPTIME
------------------------------ ---------------- ---------- ----------------
T                             15119 2012-10-24:01:52
T                             15120 2012-10-24:02:56

In SYS.MLOG$ table, Previous YOUNGEST became OLDEST_PK and new YOUNGEST is the latest refreshed MView timestamp.
Also, LAST_PURGE_DATE gets updated when a record gets purged in MLOG$_T table


DB1>select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T';

MASTER                   OLDEST_PK    YOUNGEST     LOG                MTIME         LAST_PURGE_DATE
------------------------------ ---------------- ---------------- ------------------------------ ---------------- ----------------
T                   2012-10-24:01:52 2012-10-24:02:56 MLOG$_T            2012-10-13:22:25 2012-10-24:02:56

In next article (probably last one of this discussion series), we will understand problems associated with MView Log, Modifying primary key on master table and what is “I am refresh”.

Hope this helps !!

Exchange Partition in Oracle

Some times we faced a need to convert our existing table from non-partition table to a partition table or vice-versa. Or moving a partition of one partition table to another partition. Or make one of the table as a partition of some partition table. Or make a partition of one of the partition table as a separate table.

To deal with this kind of situation, oracle has introduced exchange partition functionality long time a go (I guess from 8i onwards).
We will see a simple example of how to convert a simple non-partition table to a partition table.

1) Create a table, create index on table and gather stats on table and index


ORCL1>create table t as select * from dba_objects;

Table created.

ORCL1>create index t_idx on t(owner, object_name);

Index created.

ORCL1>exec dbms_stats.gather_table_stats('ADVAITD_DBA','T',cascade=>true);

PL/SQL procedure successfully completed.

ORCL1>select count(1) from t;

 COUNT(1)
----------
 14966

2) Create partition table with basic partition


ORCL1>create table pt (
 2  OWNER           VARCHAR2(30),
 3  OBJECT_NAME     VARCHAR2(128),
 4  SUBOBJECT_NAME  VARCHAR2(30),
 5  OBJECT_ID       NUMBER,
 6  DATA_OBJECT_ID  NUMBER,
 7  OBJECT_TYPE     VARCHAR2(19),
 8  CREATED         DATE,
 9  LAST_DDL_TIME   DATE,
 10  TIMESTAMP       VARCHAR2(19),
 11  STATUS          VARCHAR2(7),
 12  TEMPORARY       VARCHAR2(1),
 13  GENERATED       VARCHAR2(1),
 14  SECONDARY       VARCHAR2(1),
 15  NAMESPACE       NUMBER,
EDITION_NAME    VARCHAR2(30)
 16   17  )
 18  partition by range (CREATED)
 19  (PARTITION PT_2011 VALUES less than (MAXVALUE));

Table created.

ORCL1>select count(1) from pt;

 COUNT(1)
----------
 0

3) Exchange the partition with table


ORCL1>alter table pt exchange partition PT_2011 with table T without validation;

Table altered.

ORCL1>select count(1) from pt;

 COUNT(1)
----------
 14966

ORCL1>select count(1) from t;

 COUNT(1)
----------
 0

4) Split the partition and check

ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2006', 'DD-MON-YYYY'))
INTO (PARTITION PT_2006,
 PARTITION PT_2011)

Analyze the table

exec dbms_stats.gather_table_stats('ADVAITD_DBA','PT',cascade=>true);

Check the number of rows in each partition


ORCL1>select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS from dba_tab_partitions where TABLE_NAME = 'PT';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
PT                             PT_2006                        TO_DATE(' 2006-12-31 00:00:00'       8919
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2011                        MAXVALUE                             6047

Like that split again and check the records

ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2007', 'DD-MON-YYYY'))
INTO (PARTITION PT_2007,
 PARTITION PT_2011)

exec dbms_stats.gather_table_stats('ADVAITD_DBA','PT',cascade=>true);

ORCL1>select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS from dba_tab_partitions where TABLE_NAME = 'PT';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
PT                             PT_2006                        TO_DATE(' 2006-12-31 00:00:00'       8919
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2007                        TO_DATE(' 2007-12-31 00:00:00'        110
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2011                        MAXVALUE                             5937

Do for rest of the partitions


ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2008', 'DD-MON-YYYY'))
INTO (PARTITION PT_2008,
 PARTITION PT_2011)

ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2010', 'DD-MON-YYYY'))
INTO (PARTITION PT_2010,
 PARTITION PT_2011)

exec dbms_stats.gather_table_stats('ADVAITD_DBA','PT',cascade=>true);

ORCL1>select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS from dba_tab_partitions where TABLE_NAME = 'PT';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
PT                             PT_2006                        TO_DATE(' 2006-12-31 00:00:00'       8919
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2007                        TO_DATE(' 2007-12-31 00:00:00'        110
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2008                        TO_DATE(' 2008-12-31 00:00:00'        250
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------

PT                             PT_2009                        TO_DATE(' 2009-12-31 00:00:00'        312
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2010                        TO_DATE(' 2010-12-31 00:00:00'        163
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2011                        MAXVALUE                             5212

6 rows selected.

Regarding the index, we can create the similar index on partition table as well. We will create it a local index

ORCL1>create index PT_IDX on PT(owner, object_name) local;
Index created.
ORCL1>

This step can be done before as well, but doesnt matter.

Now lets verify the results


ORCL1>select count(1) from dba_objects where created < to_date('2006-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 8919

ORCL1>select count(1) from dba_objects where created < to_date('2007-12-31','YYYY-MM-DD') and created > to_date('2006-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 110

ORCL1>select count(1) from dba_objects where created < to_date('2008-12-31','YYYY-MM-DD') and created > to_date('2007-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 250

ORCL1>select count(1) from dba_objects where created < to_date('2009-12-31','YYYY-MM-DD') and created > to_date('2008-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 312

ORCL1>select count(1) from dba_objects where created < to_date('2010-12-31','YYYY-MM-DD') and created > to_date('2009-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 163

ORCL1>select count(1) from dba_objects where created < to_date('2011-12-31','YYYY-MM-DD') and created > to_date('2010-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 5220

RCATLTN1>

So this is how we can convert a simple non-partition table to a partition table.
Don’t forgot to gather status again after creating all partitions and indexes.

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 Database 9i Interview Questions

Intention of this post is to get as much interview question for Oracle DBA as possible to help you get the idea about the type of questions you can expect in interviews and exams. This will help you in increasing the knowledge about oracle database 9i as some of the questions may be new to you. This post has questions related to oracle 9i DBA only.
1) Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

2) You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

3) How do you switch from an init.ora file to a spfile?

Issue the create spfile from pfile command.

4) Explain the difference between a data block, an extent and a segment.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object. Extent may not be continuous.

5) Give two examples of how you might determine the structure of the table DEPT.

Use the describe command or use the dbms_metadata.get_ddl package.

6) Where would you look for errors from the database engine?

In the alert log.

7) Compare and contrast TRUNCATE and DELETE for a table.

Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

8) Give the reasoning behind using an index.

Faster access to data blocks in a table.

9) Give the two types of tables involved in producing a star schema and the type of data they hold.

Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

10) What type of index should you use on a fact table?

A Bitmap index.

11) Give two examples of referential integrity constraints.

A primary key and a foreign key.

12) A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

13) Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

14) What command would you use to create a backup control file?

Alter database backup control file to trace.

15) Give the stages of instance startup to a usable state where normal users may access it.

STARTUP NOMOUNT – Instance startup. Control File is read here.

STARTUP MOUNT – The database is mounted. Datafiles are read for the status and checked with control file.

STARTUP OPEN – The database is opened. Normal users can access.

16) What column differentiates the V$ views to the GV$ views and how?

The INST_ID column which indicates the instance in a RAC environment the information came from.

17) How would you go about generating an EXPLAIN plan?

Create a plan table with utlxplan.sql.

Use the explain plan set statement_id = ‘tst1’ into plan_table for a SQL statement

Look at the explain plan with utlxplp.sql or utlxpls.sql

18) How would you go about increasing the buffer cache hit ratio?

Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

19) Explain an ORA-01555

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

20) Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

21) How would you determine the time zone under which a database was operating?

select DBTIMEZONE from dual;

22) Explain the use of setting GLOBAL_NAMES equal to TRUE.

Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.

23) Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

24) Explain the use of table functions.

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

25) Name three advisory statistics you can collect.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics

26) Where in the Oracle directory tree structure are audit traces placed?

In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

27) Explain materialized views and how they are used.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

28) When a user process fails, what background process cleans up after it?

PMON

29) What background process refreshes materialized views?

The Job Queue Processes.

30) What is the Difference between OLTP and OLAP

OLTP is nothing but OnLine Transaction Processing ,which contains a normalised tables and online data,which have frequent insert/updates/delete.

But OLAP(Online Analtical Programming) contains the history of OLTP data, which is, non-volatile ,acts as a Decisions Support System and is used for creating forecasting reports.

31) How would you determine what sessions are connected and what resources they are waiting for?

Use of V$SESSION and V$SESSION_WAIT

32) Describe what redo logs are.

Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.

33) How would you force a log switch?

ALTER SYSTEM SWITCH LOGFILE;

34) Give two methods you could use to determine what DDL changes have been made.

You could use Logminer or Streams

35) What does coalescing a tablespace do?

Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.

36) What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?

A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

37) Name a tablespace automatically created when you create a database.

The SYSTEM tablespace.

38) When creating a user, what permissions must you grant to allow them to connect to the database?

Grant the CONNECT to the user.

39) How do you add a data file to a tablespace?

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>

40) How do you resize a data file?

ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;

41) What view would you use to look at the size of a data file?

DBA_DATA_FILES

42) What view would you use to determine free space in a tablespace?

DBA_FREE_SPACE

43) How would you determine who has added a row to a table?

Turn on fine grain auditing for the table.

44) How can you rebuild an index?

ALTER INDEX <index_name> REBUILD;

45) Explain what partitioning is and what its benefit is.

Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

46) You have just compiled a PL/SQL package but got errors, how would you view the errors?

SHOW ERRORS

47) How can you gather statistics on a table?

The ANALYZE command.

48) How can you enable a trace for a session?

Use the DBMS_SESSION.SET_SQL_TRACE or

Use ALTER SESSION SET SQL_TRACE = TRUE;

49) What is the difference between the SQL*Loader and IMPORT utilities?

These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.

50) Name two files used for network connection to a database.

TNSNAMES.ORA and SQLNET.ORA

References:

The above questions and answers are taken from http://www.dbasupport.com/

 

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

Shared Memory and Semaphores – Oracle Database

Introduction:

Some times if the database shut down is not graceful, shared memory wont be released. In that case if you try to login and start the database you will get following error.

-bash-2.05b$ sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 – Production on Thu Jun 26 12:57:25 2008
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
SQL> connect / as sysdba
ERROR:
ORA-03113: end-of-file on communication channel

To resolve such issues, you can check the shared memory and semaphores that are blocked by or locked by the defunct oracle processes. Before that here is the bried understanding of Shared memory segments and semaphores in UNIX.

Shared Memory Segments

Shared memory allows processes to access common structures and data by placing them in shared memory segments. It’s the fastest form of Interprocess Communication (IPC) available since no kernel involvement occurs when data is passed between the processes. In fact, data does not need to be copied between the processes.

Oracle uses shared memory segments for the Shared Global Area (SGA) which is an area of memory that is shared by Oracle processes. The size of the SGA has a significant impact to Oracle’s performance since it holds database buffer cache and much more.

To see all shared memory settings, execute:

-bash-3.00$ ipcs -lm

—— Shared Memory Limits ——–
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1

The max value of Shared Memory segment is decided by SHMMAX parameter.

ipcs -m will give you the shared memory segments, its size and which user owns the segment.

-bash-3.00$ ipcs -m

—— Shared Memory Segments ——–
key        shmid      owner      perms      bytes      nattch     status
0x0001ffb8 0          root      666        76         3
0x00025990 32769      root      666        8308       3
0x00027cb9 65538      root      666        132256     1
0x00027cba 98307      root      666        132256     1
0xf771930c 1277956    oracle01  660        2166358016 144

Semaphores

You can see semaphores setting using ipcs -ls

-bash-3.00$ ipcs -ls

—— Semaphore Limits ——–
max number of arrays = 1100
max semaphores per array = 1100
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767

SEMMSL parameter determinies the max size of semaphores.

You can see the various semaphores assigned to different processes using

-bash-3.00$ ipcs -s

—— Semaphore Arrays ——–
key        semid      owner      perms      nsems
0x00000000 393216     root      666        1
0x00000000 425985     root      666        1
0x00000000 458754     root      666        1
0x00000000 491523     root      666        1
0x0001ffb8 524292     root      666        1
0x000251c0 557061     root      666        1
0x000255a8 589830     root      666        1
0x00025990 622599     root      666        1
0x000278d1 655368     root      666        1
0x00027cb9 688137     root      666        1
0x000278d2 720906     root      666        1
0x00027cba 753675     root      666        1
0x26e55b68 3244044    oracle01  660        1004

You can also see the semaphores and Shared Memory Segments assigned to oracle processes using Oracle utility sysresv

-bash-3.00$ sysresv

IPC Resources for ORACLE_SID “tkr12r3m2” :
Shared Memory:
ID              KEY
1277956         0xf771930c
Semaphores:
ID              KEY
3244044         0x26e55b68
Oracle Instance alive for sid “tkr12r3m2”

If the instance is not alive you can remove the shared memory segments and semaphores using ipcrm command

ipcrm -m <Shared Memory ID (shmid)>

ipcrm -s <Semaphore ID (semid)>

Hope this helps !!