Oracle problem set 2 ORA-01113: file 7 needs media recovery

Note: [Oracle problem set] refers to the problems and solutions encountered by bloggers during database operations.

Statement execution: indicates the database operation process. Error: Indicates An error occurred while operating the database. Error cause: indicates the cause of the error. Solution: indicates the solution to the error. Related Knowledge: indicates the knowledge related to this topic.

========================================================== ======================================Execute the statement:

SQL> alter tablespace ts001 offline;
SQL> alter tablespace ts001 rename datafile '/home/Oracle/db001.df' to '/home/Oracle/dbf001.dbf'
SQL> alter tablespace ts001 online;

Error: ORA-01113: file 7 needs media recovery ORA-01110: data file 7: '/home/Oracle/dbf001.dbf' error cause: Solution: Restore the data file. For example, recover datafile '/home/Oracle/dbf001.dbf'; related knowledge:

In the data file, the following is the third application of the recover command:

(1) recover [automatic] database: This command can only be used when the database is loaded (Mount)

(2) recover [automatis] tablespace "tablespace number" | "tablespace name": This command can only be used when the database is open.

(3) recover [automatic] datafile "data file name" | "data file number"

The automatic option indicates the automatic search and recovery of the data submitted in the archived log files and online redo logs. You can also add the alter database command in front of the recover command (optional)

The following uses the data dictionary dba_data_files, dba_tablespaces, V $ datafile, and frequently used v $ recover_file and V $ recovery_log to demonstrate how to perform full recovery at the data file level.

(1) query dba_data_files to obtain database data files and their corresponding tablespace Information

(2) Use dba_tablespaces to obtain the tablespace and its status information

(3) obtain the data file number and its status information through V $ datafile

(4) query v $ recover_file and V $ recovery_log. Because the database runs normally and does not need to be recovered, no information is recorded in the two data dictionaries.

(5) to demonstrate the difference between tablespace offline and data file offline, we first take the offline tablespace users

(6) The query results show that the tablespace users and their corresponding No. 7 data files are offline.

(7) query the data dictionary v $ recover_file and find a record: file # indicates the file number, column 2 and column 3 both indicate the File status, and column 4 error has two values: offline normal and null, the former table shows that the data file does not need to be restored before it is set to online, and the latter indicates that the reason for the offline data file is unclear. The Fifth Column is the SCN number. This record indicates that the data file No. 7 is offline and does not need to be restored before going online.

(8) Next, query v $ recovery_log. Because the users is taken offline normally, there is still no record in the data dictionary. You can query its attribute information: thread # generally only makes sense in the cluster (real application cluster), sequence # is the serial number of the archive log file, archive_name is the file name of the archive log

(9) online table space users. After query, it is found that the table space users and its 7 data files are online.

(10) Next, query v $ recover_file and V $ recovery_log. Same as (4). Because the database runs normally and does not need to be recovered, no record information is recorded in both data dictionaries.

(11) Next, take the data file no. 7 of users offline in response to the operations on the table space. Then, the query finds that the data file No. 7 is in the recover status, while the table space users is still online.

(12) query v $ recover_file and find that the data file No. 7 is offline, but the offline reason is unknown.

(13) query v $ recovery_log and no record information is found.

(14) when trying to connect the data file no. 7 online, the system reports an error: file 7 needs to be restored by media

(15) use the recover command to restore the media of the No. 7 data file, and then bring it online successfully.