Minimizing Down Time

We have a 9.2.0.6 DB with more than 5 terra.Right now we have maintennce every week for 5 Hours.During this hours we normally do changes to schemas(such as rebuilding indexes adding columns to some application tables etc).Now our customer wants to reduce the down time to 0 for this kind of changes.Let me know what is the best practice to make the changes to schemas without affecting the onlines and 0 down time.

Some of you guys might have the db 24/7.How you do the changes without affecting onlines???.


We tried redef package/rebuild online but all this take more time to sync the database.

We do have standby's for the production db's.

Thanks

Most of ONLINE commands as described in Oracle documentation (good for learning) may not be workable in real life production environment.

I believe still You need a maintenance window.

For example:

10:31:50 SQL> create table t1 (id int);

Table created.

Elapsed: 00:00:00.01
10:31:57 SQL> insert into t1 values (10);

1 row created.

Elapsed: 00:00:00.00
10:32:05 SQL>

In another window, I tried this:

10:32:18 SQL> alter table t1 add (c2 int) ;
alter table t1 add (c2 int)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

And if you want to use DBMS_REDEFINITION, then you need twice the space of the table.

What you have to do is:
Speed up your work in production.
Test your script in a test environment before you execute it prod.
Instead of rebuilding indexes, do coalesce.
Change weekly maintenance to Monthly maintenance.
There are plenty of ways.........

Tamil