This parameter indicates when the database manager attempts
to rebuild invalid indexes, and whether or not any index build is
redone during rollforward or high availability disaster recovery
(HADR) log replay on the standby database.
- Configuration type
- Database and Database Manager
- Applies to
-
- Database server with local and remote clients
- Database server with local clients
- Partitioned database server with local and remote clients
- Parameter type
- Configurable Online
- Propagation class
- Immediate
- Default [range]
-
- UNIX Database Manager
- restart [ restart; restart_no_redo; access; access_no_redo ]
- Windows Database Manager
- restart [ restart; restart_no_redo; access; access_no_redo
]
- Database
- Use system setting [system; restart; restart_no_redo; access;
access_no_redo ]
There are five possible settings for this parameter:
- SYSTEM
- use system setting specified in the database manager configuration
file to decide when invalid indexes will be rebuilt, and whether any
index build log records are to be redone during rollforward or HADR
log replay.
Note: This setting is only valid for database configurations.
- ACCESS
- Invalid
indexes are rebuilt when the underlying table is first accessed. Any
fully logged index builds are redone during rollforward or HADR log
replay. When HADR is started and an HADR takeover occurs, any invalid
indexes are rebuilt after takeover when the underlying table is first
accessed.
- ACCESS_NO_REDO
- Invalid indexes are
rebuilt when the underlying table is first accessed. Any fully logged
index build is not redone during rollforward or HADR log replay and
those indexes are left invalid. When HADR is started and an HADR takeover
takes place, any invalid indexes are rebuilt after takeover when the
underlying table is first accessed. Access to the underlying tables
on the new primary cause index rebuild, which causes log records to
be written and then sent to the new standby, which in turn causes
the indexes to be invalidated on the standby.
- RESTART
- The default value for indexrec. Invalid indexes
are rebuilt when a RESTART DATABASE command is
either explicitly or implicitly issued. Any fully logged index build
is redone during rollforward or HADR log replay. When HADR is started
and an HADR takeover takes place, any invalid indexes are rebuilt
at the end of takeover.
When
a database terminates abnormally while applications are connected
to it, and the autorestart parameter is enabled, a RESTART
DATABASE command is implicitly issued when an application
connects to a database. If the command is not issued, the invalid
indexes are rebuilt the next time the underlying table is accessed.
- RESTART_NO_REDO
- Invalid
indexes are rebuilt when a RESTART DATABASE command
is either explicitly or implicitly issued. Any fully logged index
build is not redone during rollforward or HADR log replay and instead
those indexes are rebuilt when rollforward completes or when HADR
takeover takes place. Takeover causes index rebuild on underlying
tables on the new primary, which causes log records to be written
and then sent to the new standby, which in turn causes the indexes
to be invalidated on the standby.
When a database terminates abnormally
while applications are connected to it, and the autorestart parameter
is enabled, a RESTART DATABASE command is implicitly
issued when an application connects to a database. If the command
is not issued, the invalid indexes are rebuilt the next time the underlying
table is accessed.
Indexes can become invalid when fatal disk problems
occur. If this happens to the data itself, the data could be lost.
However, if this happens to an index, the index can be recovered by
re-creating it. If an index is rebuilt while users are connected to
the database, two problems could occur:
- An unexpected degradation in response time might occur as the
index file is re-created. Users accessing the table and using this
particular index would wait while the index was being rebuilt.
- Unexpected locks might be held after index re-creation, especially
if the user transaction that caused the index to be re-created never
performed a COMMIT or ROLLBACK.
Recommendation: The best choice for this option
on a high-user server and if restart time is not a concern, would
be to have the index rebuilt at DATABASE RESTART time as part of the
process of bringing the database back online after a crash.
Setting
this parameter to "ACCESS" or to "ACCESS_NO_REDO" will result
in a degradation of the performance of the database manager while
the index is being re-created. Any user accessing that specific index
or table would have to wait until the index is recreated.
If
this parameter is set to "RESTART", the time taken to restart
the database will be longer due to index re-creation, but normal processing
would not be impacted once the database has been brought back online.
The
difference between the RESTART and the RESTART_NO_REDO values, or
between the ACCESS and the ACCESS_NO_REDO values, is only significant
when full logging is activated for index build operations, such
as CREATE INDEX and REORG INDEX operations, or for an index rebuild.
You can activate logging by enabling the logindexbuild
database configuration parameter or by enabling LOG INDEX BUILD
when altering a table. By setting indexrec to
either RESTART or ACCESS, operations involving a logged index build
can be rolled forward without leaving the index object in an invalid
state, which would require the index to be rebuilt at a later time.