DBA experience: Changing the availability of data files

Data Files in Oracle databases have a feature that allows you to set whether to go offline. If you set the data file to offline, the database cannot read the data file. The database can read contents only when files are online. Therefore, changing the status of this data file is also a necessary means for database maintenance.

I. Why should I change its availability?

1. It is required to change the data file name or location.

During database deployment, if you do not give the data file a proper name, or do not select a proper partition for it. In this case, you may need to change the name or location of the data file during subsequent maintenance. For example, if you want to optimize the performance, some data files with concurrent access will be stored in Different Hard Disk Partitions to reduce I/O conflicts and improve the database performance. In this case, you must move the location of the data file. To achieve this goal, you must first take the data file offline and then start it to a proper location.

2. The system will automatically shut down when a data file fault occurs.

Sometimes, the database system automatically closes data files. For example, when the database system writes data to a data file, if an error occurs during the write process, the data file will be set to offline, and write this information into the log. The database administrator needs to repair the data file after receiving the alarm information. After the restoration, you must manually set the data file to online. In this case, the database system will re-enable the data file. It can be seen that when the data file cannot be correctly written, it is also a reason why the data file is offline.

Setting a data file offline does not affect the availability of other data files. For example, the database administrator plans the data files according to the department. That is, users in other departments can still access their desired data in the system. Therefore, this feature can effectively reduce the time needed to run the database and achieve the maximum availability of the database. Start the database step by step to rename the data file

Ii. How can I change the archive mode and non-archive mode?

Oracle Database work modes are roughly divided into archive mode and non-archive mode. In different operation modes, the data file availability maintenance methods are slightly different. For this reason, database administrators need to understand the maintenance methods in different operation modes. Only in this way can we change the availability of data files in an appropriate way.

If the DATABASE is in archive mode, you only need to use the DATEFILE Statement of alter database to bring a separate data file online or offline. You can set a data file to offline or online as follows. After the following command is successfully executed, the system prompts "the database has been changed ".

Alter database datafile 'data file storage path and name 'OFFLINE/ONLINE;

However, if the database is not in archive mode, an error message is displayed when the preceding statement is executed to change the availability of the data file. If you set a data file to offline, "offline is not allowed unless it is restored using media ". If you set a data file to online, the message "Data File 5, media recovery required" is displayed ". Note that 5 represents the absolute file number of the data file. In Oracle databases, the file number is a tool used by the database system to identify data files. It is like a person's ID card that uniquely identifies a data file. In Oracle, file numbers are classified into absolute and relative file numbers. The absolute file number uniquely identifies a data file in the entire database. The relative file number uniquely identifies a data file in a tablespace. That is to say, the relative file number is unique in the same tablespace, but it is not guaranteed to be unique in the whole database. In this case, the database may need two parameters: tablespace and relative file number to locate the data file. For database systems of medium scale or lower, the relative file number is usually the same as the absolute file number. However, when the database becomes very large, the relative file number may be different from the decision file number. Because the name of the data file is not directly described in the error message, the database administrator has to convert the file number to the name of the corresponding data file before trying to solve the problem. Exclusive to IT expert network: Location of relocated data files

It can be seen that when the database adopts non-archive mode, the data files cannot be brought online or offline in the above mode. In this case, you need to modify the preceding statement slightly. For example, you can change this statement:

Alter database datafile 'data file storage path and name 'offline drop;

You need to add the DROP keyword after the original statement. In this case, the data files in non-archive mode can be taken offline normally. However, you must note that the data files in non-archive mode are set to online in normal mode. Under normal circumstances, data files in non-archive mode can be set to online only when the media is restored. Therefore, if the database adopts the non-archive mode, you must be careful when setting the data file to offline. It is not that easy to set offline data files to online.

1 2 Next Page