Oracle index tablespace data file loss and Reconstruction

Multiple processes can work together to create indexes. Compared with an index created by a single server process in sequence, you need to allocate the work required to create an index between multiple server processes,

Multiple processes can work together to create indexes. Compared with an index created by a single server process in sequence, you need to allocate the work required to create an index between multiple server processes,

Re-create index:
Use the following options to shorten the time required to create an index:

PARALLEL NOLOGGING

PARALLEL (NOPARALLEL is the default value): multiple processes can work together to create indexes. Compared with an index created by a single server process in sequence, the Oracle server can quickly create an index by assigning the tasks required to create an index between multiple server processes. Sample the table randomly and find a set of index keywords. These index keywords divide the index into the same number of segments based on the specified degree of parallelism. The first group of query processes will scan the table, extract the key and row ID pairs, and send each pair to a process in the second group of query processes based on the key words. In the second group, each process sorts the keywords and builds indexes in the conventional way. After all index fragments are built, the parallel Coordination Program cascade these fragments (sorted) to form the final index.

SQL> CREATE INDEX rname_idx
ON hr. regions (region_name)
PARALLEL 4;

NOLOGGING: using this keyword will speed up index creation, because there are very few redo log entries created by the creation process. This type of workload is greatly reduced.

It is also applicable to Direct path insertion and Direct Loader (SQL * Loader) insertion. This is a permanent attribute and will be displayed in the data dictionary. Available at any time

Alter index nologging/LOGGING command to update.
Note: If the database is in the nologging status, the operated data should be backed up and then changed back to nologging to prevent major errors from being recovered.

Restore the table space after the index is lost:
When an index is lost, you can create a new index instead of trying to restore the index.

Indexes are calculated objects, because they do not provide any raw data, but are another representation of existing data. Therefore, you can easily recreate an index in most cases.
If your tablespace only contains an index, You can simplify the recovery after data files belonging to the tablespace are lost.
If this type of data file is lost, you can

Perform the following steps:

1. Delete the data file.

2. Delete the tablespace.
3. Recreate the index tablespace.

4. Recreate the index contained in the tablespace.
1. You can restore tablespaces that only contain indexes without executing the RECOVER task.

2. If data files in tablespaces that only contain indexes are lost, it is easier to recreate the tablespace and re-create the index.

Experiment: Index tablespace data file loss

1. Create lab data;

Create tablespace INT_TBS datafile
'/U01/app/oracle/oradata/PROD/ind_tbs.dbf' size 20 m;
Create user ind_user indentified by user
Default tablespace ind_tbs;

Grant dba to ind_user;

Conn ind_user/user

Create table t1 tablespace user
As select * from dba_objects
Where rownum: <= 100;

Create index ind_t1_id on t1 (object_id );
Create index ind_t1_name on t1 (upper (object_name ));
Create index ind_t1_com on t1 (object_id, object_name );

Select index_name, tablespace_name, status from ind;
-- View index status

2. delete data files in all tablespaces

! Rm/u01/app/oracle/oradata/PROD/ind_tbs.dbf
! Ls/u01/app/oracle/oradata/PROD/ind_tbs.dbf

3. Trigger the problem;
Set autot traceonly
-- Enable tracking
Select * from t1 where object_id = 2;
-- Error. index file not found
Set autot off

Alter index ind_t1_id rebuild online;
-- Error: index file not found

4. Rebuild the index and specify it to another tablespace.

Sets long 99999
Select dbms_metadata.get_ddl ('index', 'ind _ T1_ID ') from dual;

IND_USER @ PROD> select dbms_metadata.get_ddl ('index', 'ind _ T1_ID ') from dual;

Create index "IND_USER". "IND_T1_ID" ON "IND_USER". "T1" ("OBJECT_ID ")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 freelist groups 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "IND_TBS"
-- Follow compute statistics

Alter index ind_t1_id rebulid online tablespace users;
Alter index ind_t1_name rebulid online tablespace users;
Alter index ind_t1_com rebulid online tablespace users;

Select index_name, tablespace_name, status from ind;
-- View the reconstruction result

5. delete or recreate the index tablespace lost in the data file

Drop tablespace ind_tbs including contents and datafiles;
-- Delete a tablespace
Alter system datafile 7 offline;
-- If an error is returned when the tablespace is deleted and the data file is being opened, the table is offline 7 and then deleted;

Create tablespace ind_tbs datafile '/u01/app/oracle/oradata/PROD/ind_tbs.dbf' size 20 m;

Alter index ind_t1_id rebulid online tablespace ind_tbs;
Alter index ind_t1_name rebulid online tablespace ind_tbs;
Alter index ind_t1_com rebulid online tablespace ind_tbs;

Select index_name, tablespace_name, status from ind;
-- View the reconstruction status;

Oracle 11g tablespace dbf File migration

Solutions for moving Oracle tablespace data files

Deleting temporary tablespace ORA-25152 Error

Oracle tablespace growth exception solution: Another example

Three parameters of Oracle tablespace Offline

Use of encrypted tablespace under Oracle 11 GB