Oracle files mistakenly deleted replies

Today, a colleague found me that his database is unusable, I hope I can help to study it.

In fact, I do not have a database in-depth study, just when you like to look at the online master of the information, when things are customary to combine master data to try to experience.

Let's study it together.

Colleagues said he wanted to back up the database tablespace file Tnits_bak_2.ora, after backing up the habitual delete the original file, and then want to maintain the server, restart the database server. Results after the server up and then start the database only to remember that the database files have just been deleted, the database will not come up, this is the operation process.

Let him start the monitoring Lsnrctl start, and find that the monitor can start up.

Using Sqlplus/nolog to enter the database and then execute startup, the system prompts the following:

Cannot start already-running Oracle-shut it down first

This indicates that there was a problem at the start. At this point, you think you can step through the database startup process to determine which step occurred.

Start the database is divided into three steps, respectively, Nomount, Mount, open, generally we use startup directly when the automatic implementation of these 3 steps.

Of course, we can also manually perform these 3 steps individually:
Startup Nomount
ALTER DATABASE Mount
ALTER DATABASE Open

The three sentences are executed in the SQL window with the following results:

Sql> Startup Nomount
Sp2-0714:invalid combination of the STARTUP options

Sql> ALTER DATABASE Mount;
ALTER DATABASE Mount
*
ERROR at line 1:
Ora-01100:database already mounted

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 '

Here the third statement in the error caused my attention, he prompted the "need to reply to media files", which indicates that my colleague deleted after the restore back to the Tnits_bak_2.ora this file did not allow the database software to detect.

Looked at a colleague provided by the original file and after the restore file found the file size is not different, but the file modification time is different.

Before modification

After modification

Since the database system does not recognize the file and prompts the need to reply to the media file then I will manually reply to, execute recover datafile 9, prompted as follows:

Sql> Recover DataFile 9
Sp2-0640:not connected
Media recovery complete.

Then open the database again discovery still does not open normally, or prompt cannot find Tnits_bak_2.ora this data file.

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 '

It suddenly occurred to me that Oracle will verify checkpoint SNC at boot time, if checkpoint verification does not pass, then it is possible that the restored file is not recognized, resulting in a file not found on the above prompt.

To verify that I decided to tnits_bak_2.ora the offline state and then start the test 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"

The results do not appear to be successful, try changing the file status directly using the file number without using the filename:

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 seems to be a bit of effect, but still wrong, I remember to read an article on the internet seems to have a database archive and non-archiving is a difference, immediately online to find a document to look at, sure enough there is a difference, the archive database can be used directly offline, non-archiving to use offline drop only line.

operate with 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.

has also been completed.

Execute the Open database command again, and the discovery can be opened successfully.

sql> ALTER DATABASE open;
Database altered.

entered the database to verify that all the data are still in, the application can also run properly, so that the repair is complete.

Summarized as follows:

1, the data file remember not to easily delete, even if you want to delete and do not delete the operating system level, the database itself can be deleted files, have the specified command.

2, when the database starts and online, Oracle will go online a "consistent" table space. At this point, it will find inconsistencies in the tablespace's internal SCN. At the prompt, we need to do file recovery by hand. or Use offline drop and offline to let the database skip consistency monitoring at startup. Refer to http://www.linuxidc.com/Linux/2014-05/101881p2.htm for this point, as well as http://blog.csdn.net/folio/article/details/8156805

3, reply to confirm whether the database is an archive mode, confirm the way to log in to the SQL window with DBA authority, and then execute the Archive log LIST, the results indicate that database log mode No Archive mode is not archived. Or execute select Name,log_mode from V$database, or non-archived if the result is query Noarchivelog.

Oracle files mistakenly deleted replies