Table space Status (1)-online and offline

A few days ago, I asked a question about the table space status and exposed my weak basic knowledge. I summarized the following two blog posts to let myself calm down and fill in the relevant knowledge points, and enhance your understanding through practice.

Main reference:

11g Concept

11g Administrator's Guide

Online and offline statuses of tablespaces

1. As long as the database is open, other tablespaces except the system tablespace can be set to online or offline. The reason why the system tablespace cannot be set to offline is that the stored data dictionary must be used all the time. Set the tablespace to offline. Possible reasons include maintenance, backup, and recovery.

3. If the tablespace is offline, Oracle will not allow any SQL Execution ON THE OBJECTS IN THE tablespace. Statements that are still active when the tablespace is set to offline are not affected at the transaction level. Oracle will save the rollback data related to these unfinished statements to the system tablespace. If the tablespace is set to online, Oracle will apply the rollback data if necessary.

3. In addition to Oracle, no application can read or edit the offline tablespace. Therefore, the table space of offline cannot be exported to other databases.

4. Oracle automatically switches the tablespace status from online to offline in some scenarios. For example, when the database writes the process dbwn, you cannot write the data file in the tablespace several times. An error is returned when you access the offline tablespace. If the problem of disk I/O failure is caused by a media error, the tablespace needs to be restored after the problem is solved.

5. Changing the tablespace to offline may include the following reasons:

(1) scenarios where access to some databases is prohibited.

(2) perform an offline tablespace backup (although the tablespace can be backed up online and in use ).

(3) When updating or maintaining an application, the application and the corresponding table must be temporarily inaccessible.

(4) Rename or re-allocate the data files in the table space.

When the tablespace is set to offline, the database sets all associated data files to offline. However, the following tablespace cannot be set to offline:

> System

> Undo tablespace

> Temporary tablespace

6. If a user sets the tablespace as its default tablespace before setting it to offline, consider whether to change its default tablespace. This is because these users cannot access objects in the offline tablespace.

7. Use the alter tablespace... offline statement to add the following parameters:

Normal: If no error occurs in any data file in the tablespace, The tablespace can be set to offline normally. If a write error occurs, the data files in the tablespace are not set to offline. When offline normal is explicitly used, the database performs a checkpoint event on all data files. Normal is the default setting.

Temporary: The tablespace can be temporarily set to offline even if one or more files have an error. When offline temporary is used, the database places data files that are not set to offline and executes the checkpoint event.

If no file is offline but the temporary statement is used, you do not need to restore the media when you set the tablespace to online. However, if one or more files in a tablespace are in the Offline state due to a write error, the tablespace can be temporarily set to the Offline state. You need to restore the tablespace Before restoring it to the online state.

Immediate: The tablespace can be immediately set to the Offline state. The database does not perform any checkpoint events for data files. When offline immediate is used, you need to restore the media to the table space before the tablespace is set to online. However, if the database is in noarchivelog mode, the tablespace cannot be immediately set to offline.

8. If you must set the tablespace to the Offline state, we recommend that you use the default normal statement first. It will ensure that the restoration operation is not required before the tablespace is set to online, even though the redo log sequence is reset using the later database open restlogs statement after Incomplete recovery.

Temporary can be used only when the tablespace cannot be set to offline in the normal mode. After temporary is used, you only need to restore the wrong offline files before the tablespace is set to online.

If both normal and temporary fail, you can use the immediate option.

9. If the tablespace is not "clean" and is set to offline (that is, the offline operation is not executed using the normal statement), the media recovery operation must be performed on the tablespace before it is set to online. Otherwise, the database reports an error and the tablespace is still in the offline status.

Lab:

1. Set the tablespace dcsopen_tbs to offline.:

SQL> alter tablespace dcsopen_tbs offline;

Tablespace altered.

2. query the data file status:

Select File #, name, status from V $ datafile;

File # name status
-------------------------------------------------------------------

11/Oracle/oradata_petest/petest/dcsopen_tbs02.dbf offline

The status of the data file used by dcsopen_tbs has been set to offline.

3. Try to query the offline tablespace:

SQL> select * from test;
Select * from test
*
Error at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/Oracle/oradata_petest/petest/dcsopen_tbs01.dbf'

An error is reported, indicating that the data file cannot be read.

4. Set the tablespace to online.:

SQL> alter tablespace dcsopen_tbs online;

Tablespace altered.

File # name status
-------------------------------------------------------------------

11/Oracle/oradata_petest/petest/dcsopen_tbs02.dbf online

Not complete...

To be continued...