ORACLE File deleted by mistake

ORACLE File deleted by mistake

Today, a colleague found me and said that his database could not be used. I hope I can help with the research.

In fact, I do not have any in-depth research on the database, but I like to take a look at the online master's information when I am fine. When I encounter things, I am used to combining the master's information for continuous attempts to sum up experience.

Let's take a closer look.

My colleague said that he wanted to back up the table space file tnits_BAK_2.ORA in the database. After the backup, he habitually deleted the original file and tried to maintain the server, so He restarted the database server. After the server is started, the database file is deleted and cannot be started. This is the operation process.

Let him start the listener lsnrctl start and find that the listener can start.

Use sqlplus/nolog to enter the database and execute startup. The system prompts the following:

Cannot start already-running ORACLE-shut it down first

This indicates a problem occurred during startup. In this case, you can perform step-by-step operations on the database startup process to determine which step has the problem.

There are three steps to start a database: nomount, mount, and Z testing? Http://www.bkjia.com/kf/ware/vc/ "target =" _ blank "class =" keylink "> Principal + CjxwPr2r1eLI/b7k1NpzcWy0sL/Principal + csf-vcd4kpha + Principal + principal ="/DataExt/tnits_BAK_2.ORA'

The error in the third statement has aroused my attention. The prompt "media file needs to be replied" indicates that the tnits_BAK_2.ORA file that my colleague deleted and restored cannot be detected by the database software.

I checked that the size of the original file provided by my colleagues is no different from that of the restored file, but the modification time of the file is different.

Before Modification

After modification

Since the database system does not recognize the file and prompts you to reply to the media file, I will reply to it manually and execute recover datafile 9. The prompt is as follows:

SQL> recover datafile 9
SP2-0640: Not connected
Media recovery complete.

After opening the database again, the system still fails to open the database normally. The system still prompts that the tnits_BAK_2.ORA data file cannot be found.

SQL> alter database open;
Alter database open
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: "/DataExt/tnits_BAK_2.ORA'

I suddenly thought that oracle will verify the checkpoint SNC at startup. If the checkpoint verification fails, is it possible that the restored file cannot be identified, resulting in the failure to find a file as prompted above?

In order to verify that I decided to set tnits_BAK_2.ORA to offline state and then start it again.

SQL> alter database datafile '/DataExt/tnits_BAK_2.ORA' offline;
Alter database datafile 'dataext/tnits_BAK_2.ORA 'offline
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"DataExt/tnits_BAK_2.ORA"

As a result, it still fails. If the file name is not used, use the file number to change the File status again:

SQL> alter database datafile 9 offline;
Alter database datafile 9 offline
*
ERROR at line 1:
ORA-01145:
Offline immediate disallowed unless media recovery enabled

This time it seems a bit effective, but it is still not correct. I remember seeing an article on the Internet. It seems that there is a difference between database archiving and non-archiving. I immediately went online and found a document to read it, there is indeed a difference. archive databases can use offline directly, and non-archive databases must use offline drop.

Run the following command:

SQL> alter database datafile '/DataExt/tnits_BAK_2.ORA' offline drop;
Database altered.

Completed successfully.

Alter database datafile '/DataExt/tnits_BAK_2.ORA' offline;
Database altered.

Also completed.

Run the "Open Database" command again and find that the database can be opened successfully.

SQL> alter database open;
Database altered.

After entering the database for verification, we found that all the data is still in progress and the application can run properly. Now the repair is complete.

Summary:

1. Do not delete data files easily. Even if you want to delete data files, do not delete them at the operating system level. The database itself can delete files with specified commands.

2. When the database is started and online, Oracle will definitely go online to a "consistent" tablespace. At this time, it will find that the internal scn of the tablespace is inconsistent. As prompted, We need to manually restore the file. You can also use offline drop and offline to skip consistency monitoring during database startup. For more information, see

3. When replying, check whether the database is in ARCHIVE mode. Use the dba permission to LOG on to the SQL window and run ARCHIVE LOG LIST, the Database log mode No Archive Mode is not archived. Or run select name, log_mode from v $ database; if the result is query noarchivelog, It is not archived.