3.4. Recovering an Index Tablespace

Recovering the database with a missing index tablespace is another non-critical recovery technique that you need to understand. An index tablespace is a tablespace that should contain only indexes. Indexes are objects that can be created from the underlying database tables. Rebuild index scripts can be rerun to build the indexes in the index tablespace.

Let's walk through the process of recovering an index tablespace:

  1. The first step is to start and mount the database if it is not already running:

    C:Documents and Settingsdstuns> sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Sat Aug 21 19:08:48 2004

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

SQL> connect / as sysdba
Connected.
SQL> startup mount
ORACLE instance started.

    Total System Global Area    88080384 bytes
Fixed Size                    787588 bytes
Variable Size               78642044 bytes
Database Buffers             8388608 bytes
Redo Buffers                  262144 bytes
Database mounted.
SQL>

  2. Next, drop the tablespace.

    SQL> drop tablespace indexes including contents;

  3. Finally, re-create the indexes tablespace.

    SQL> create tablespace indexes
    
    datafile 'C:oracleoradataora101tindex01.dbf' size 20m;

Tablespace created.

3.4.1. Re-creating Indexes

Re-creating indexes is required after rebuilding the index tablespace. The existing indexes will be gone because the tablespace was rebuilt from scratch. As long as you have the create index scripts, this is a non-critical recovery process.

It is very common to have copies of most of the indexes in your database to perform maintenance activities such as rebuilding a fragmented database for performance reasons. Rebuilding an index is a basic procedure.

Let's walk through rebuilding an index:

  1. In this example, you hsave an index called INDEX NAME OF_EXAMPLE_INDEX_PK.SQL, which you are viewing through an operating system editor. This index is a primary key index, which is built with the NOLOGGING and PARALLEL options to improve the build time. The NOLOGGING option will not create redo information, so once the indexes are built, a backup should be performed shortly after the indexes are created:

    CREATE UNIQUE INDEX example_index_pk
ON example_table
( column_one,
  column_two,
  column_three,
  column_four)
PCTFREE 10
INITRANS  2
MAXTRANS  255

    TABLESPACE indexes
STORAGE (
  INITIAL     1M
  NEXT       1M
  PCTINCREASE  0
  MINEXTENTS   1
  MAXEXTENTS   8192
  )
nologging
parallel (degree 4)
/

  2. Now you can just run this create index script using the following command:

    SQL> @create_example_index_pk

Rebuilding indexes can be a more difficult process if there is significant referential integrity in the database. You should discover the proper order or procedures to rebuild indexes in your database in the test or development database environment.


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset