Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you what the offline and online operations of tablespaces and data files are like. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

1. The difference between tablespace and data file offline

When offline the tablespace, oracle needs to checkpoint the tablespace, refresh the related transactions of the data file in the tablespace, and update the SCN of the control file and the data file to achieve a consistent state.

When the tablespace is online again, oracle will use the SCN of the online moment to update the SCN of each location above (of course, the SCN number at the end of the file will be set to infinity), and open all the data files

When offline a single data file, it is immediately offline, and there is no checkpoint for the data file at this time, so the termination SCN of the data file is infinite and is in an inconsistent state, and the data file also needs to be restored. The information of this file can also be seen in the v$recover_file view. If you want to online the data file, you must first perform the recover operation on the file. Therefore, it is suggested that the recover operation should be performed immediately after the offline data file to make it in a consistent state, so as to avoid unnecessary trouble later.

For offline operations of data files in non-archive mode, you need to add a drop clause. He will not physically delete the file, and specifying the drop clause in archive mode will be ignored.

For offline data files, restarting the database cannot recover him, because his status is offline, and the database startup does not check whether the SCN of the offline file is consistent, so they can still be seen in the v$recover_file view after startup. It needs to be manually recover to recover them. If the applied online log has been switch, it is necessary to recover from the archive, if there is no archive. The file can only be in recover state all the time.

two。 Operation

Archived files or redo files may be required if recovery operations are involved in the following operations, and cannot be recovered if the database is in non-archived mode and the redo file is cut and overwritten

2-1. Offline of the tablespace

2-1-1. Normal (default mode)

Checkpoint all data files in the tablespace. All data files must be in online state. There is no need to restore them when you online again.

Alter tablespace idx offline

Select tablespace_name,status from dba_tablespaces

Select file_name,online_status from dba_data_files

Select * from v$recover_file

2-1-2. Temporary

Only the data files of the current online are checked, and all data files need not be online. When you online again, the data files of the previous offline need to be restored, and other data files are not required.

Alter tablespace idx offline temporary

2-1-3. Immediate/for recover (10g is not recommended)

All data files are not checkpointed, and all data files do not have to be online. All data files need to be restored when online is done again, and are not available when not in archive mode.

Alter tablespace idx offline immediate

2-2. Offline of the data file

2-2-1. Archiving mode

Alter database datafile 5 offline

Recover datafile 5 alter database datafile 5 online

2-2-2. Non-archiving

Alter database datafile 5 offline drop

Recover datafile 5

Alter database datafile 5 online

This is what the offline and online operations of tablespaces and data files shared by Xiaobian are like. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.