Databases Reference
In-Depth Information
The first step prepares the scenario. A demonstration table is created. It uses a
conventional path to create the table using a
Create
Table
As
Select
(
CTAS)
syntax
(
A
). Then the index is created (
B
), this command utilizes the
NOLOGGING
clause. This
will only record information about the object creation. Data dictionary information is
always recorded, but it won't record the changes on the data blocks, thus provoking
data corruption.
An error is created on purpose (
C
). The datafile related to the
USERS
tablespace is
removed, thus requiring a recovery process to be started. The commands issued to
bring back the USERS tablespace are regular
rman
commands:
RUN{
SQL 'ALTER TABLESPACE USERS ONLINE' ;
RESTORE TABLESPACE USERS;
RECOVER TABLESPACE USERS;
SQL 'ALTER TABLESPACE USERS ONLINE' ;
}
Once the process has finished, a query against the demonstration table is issued.
According to the execution plan this query utilizes the index, not the table.
Query:
SELECT COUNT(*)
FROM DEMO_SEGMENTS
WHERE OWNER='SCOTT';
Execution Plan:
SELECT STATEMENT
SORT AGGREGATE
INDEX RANGE SCAN (
I_DEMO_SEGMENTS
)
The
I_DEMO_SEGMENTS
index was created using the
NOLOGGING
clause and it was at
the recently recovered
USERS
tablespace. As no other precautions were taken, the
index is rendered corrupt.
The error displayed is:
ORA-01578: ORACLE data block corrupted (file # 4, block # 480)
ORA-01110: data file 4: '/u01/app/oracle/oradata/alpha/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Search WWH ::
Custom Search